Dynamic SQL handling, and edit-grids?

Discussion in 'ASP .Net' started by topmind, May 2, 2006.

  1. topmind

    topmind Guest

    I am generally new to dot.net, coming from "scriptish" web languages
    such as ColdFusion and Php.

    I have a few questions if you don't mind.

    First, how does one go about inserting dynamic SQL during run-time
    without lots of quotes? For example, adding "AND" clauses that may or
    may not be present based on the query criteria form.

    Some of the asp.net examples use the one-line append approach which
    seems awkward to work with (see 2nd example below).

    For example, in ColdFusion one can do something like:

    <CFquery settings="foo"> // query block - approx syntax only
    SELECT *
    FROM myTable
    WHERE 1=1 #andClause# // insert variable string
    <CFif x equals 1> // IF statement
    ORDER BY zip, zap
    <CFelse>
    ORDER BY norg
    </CFif> // end-if
    </CFquery> // end-query

    Here "andClause" is an "embedded" variable that is inserted into the
    SQL string at run-time. It may contain something like "and x = y and k
    = 2". It can also be blank (which is why the query has "1=1"). This is
    important because the parameters cannot be hard-wired ahead of time
    unlike what some template-based SQL preprocessors use. A sample
    "SqlDataSource" ASPX tag I looked into had this problem. It assumed all
    the "slots" will always have a usable value. Optional
    parameters/clauses is a common need for non-trivial forms and reports.

    I remember the old-style ASP required something like:

    sql = "SELECT * "
    sql = sql & "FROM myTable "
    sql = sql & "WHERE 1=1 " & andClause
    if x = 1 then
    sql = sql & " ORDER BY zip, zap "
    else
    sql = sql & " ORDER BY norg "
    end if
    rs = db.execute(sql) ...

    The quoting and string appending can get annoying and error-prone for
    longer SQL, especially if one has to copy and paste back and forth to
    test the SQL in a query processor (such as Toad or SQL-Server edit
    window).

    I am wondering what techniques one uses to simplify dynamic SQL
    handling in asp.net.

    Second, does asp.net have a decent editable grid? This would be a
    spreadsheet-like grid interface that can accept new data. ColdFusion
    has CFgrid for this. It is a bit clunky and crashy, but good enough for
    internal or small stuff.

    I have seen a lot of half-@ss implementations that don't measure up to
    what a user expects from client-server or desktop type apps
    (spreadsheets, MS-Access, VB6, etc.). For example, some web grid
    widgets don't have scroll-bars but Prev/Next buttons instead; others
    don't recognize the Down-Arrow key to move between rows; others have
    the column title row disappear when you scroll down; and many just
    crash a lot. Apparently it is a tough problem to solve given how few
    solutions there are. There are some JavaScript solutions that cost
    bucks.

    Thanks in advance for your response.

    -T-
     
    topmind, May 2, 2006
    #1
    1. Advertising

  2. Topmind,

    I prefer to use stored procedures myself, but the answer to your question is
    that Sql Commands are easy to form in asp.net and have built in parameter
    checking. You can now specify a parameter right in the string and then add
    the parameter to the sql command and the parameter will be checked and then
    inserted into the string:

    using System.Data;
    using System.Data.SqlClient;

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
    DataSet userDataset = new DataSet();
    SqlDataAdapter myDataAdapter = new SqlDataAdapter(
    "SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id",
    connection);
    myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
    myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;
    myDataAdapter.Fill(userDataset);
    }

    The answer to your second question is that you can use a datagrid in asp.net
    1.1 or a gridview in asp.net 2.0.

    Regards,

    --
    S. Justin Gengo
    Web Developer / Programmer

    Free code library:
    http://www.aboutfortunate.com

    "Out of chaos comes order."
    Nietzsche


    "topmind" <> wrote in message
    news:...
    >I am generally new to dot.net, coming from "scriptish" web languages
    > such as ColdFusion and Php.
    >
    > I have a few questions if you don't mind.
    >
    > First, how does one go about inserting dynamic SQL during run-time
    > without lots of quotes? For example, adding "AND" clauses that may or
    > may not be present based on the query criteria form.
    >
    > Some of the asp.net examples use the one-line append approach which
    > seems awkward to work with (see 2nd example below).
    >
    > For example, in ColdFusion one can do something like:
    >
    > <CFquery settings="foo"> // query block - approx syntax only
    > SELECT *
    > FROM myTable
    > WHERE 1=1 #andClause# // insert variable string
    > <CFif x equals 1> // IF statement
    > ORDER BY zip, zap
    > <CFelse>
    > ORDER BY norg
    > </CFif> // end-if
    > </CFquery> // end-query
    >
    > Here "andClause" is an "embedded" variable that is inserted into the
    > SQL string at run-time. It may contain something like "and x = y and k
    > = 2". It can also be blank (which is why the query has "1=1"). This is
    > important because the parameters cannot be hard-wired ahead of time
    > unlike what some template-based SQL preprocessors use. A sample
    > "SqlDataSource" ASPX tag I looked into had this problem. It assumed all
    > the "slots" will always have a usable value. Optional
    > parameters/clauses is a common need for non-trivial forms and reports.
    >
    > I remember the old-style ASP required something like:
    >
    > sql = "SELECT * "
    > sql = sql & "FROM myTable "
    > sql = sql & "WHERE 1=1 " & andClause
    > if x = 1 then
    > sql = sql & " ORDER BY zip, zap "
    > else
    > sql = sql & " ORDER BY norg "
    > end if
    > rs = db.execute(sql) ...
    >
    > The quoting and string appending can get annoying and error-prone for
    > longer SQL, especially if one has to copy and paste back and forth to
    > test the SQL in a query processor (such as Toad or SQL-Server edit
    > window).
    >
    > I am wondering what techniques one uses to simplify dynamic SQL
    > handling in asp.net.
    >
    > Second, does asp.net have a decent editable grid? This would be a
    > spreadsheet-like grid interface that can accept new data. ColdFusion
    > has CFgrid for this. It is a bit clunky and crashy, but good enough for
    > internal or small stuff.
    >
    > I have seen a lot of half-@ss implementations that don't measure up to
    > what a user expects from client-server or desktop type apps
    > (spreadsheets, MS-Access, VB6, etc.). For example, some web grid
    > widgets don't have scroll-bars but Prev/Next buttons instead; others
    > don't recognize the Down-Arrow key to move between rows; others have
    > the column title row disappear when you scroll down; and many just
    > crash a lot. Apparently it is a tough problem to solve given how few
    > solutions there are. There are some JavaScript solutions that cost
    > bucks.
    >
    > Thanks in advance for your response.
    >
    > -T-
    >
     
    S. Justin Gengo, May 2, 2006
    #2
    1. Advertising

  3. Just out of curiosity, are you the topmind of TableOrientedProgramming
    fame, or am I accusing an innocent man of RelationalWeenieship?
     
    Flinky Wisty Pomm, May 2, 2006
    #3
  4. topmind

    topmind Guest

    S. Justin Gengo wrote:
    > Topmind,
    >
    > I prefer to use stored procedures myself,


    As a side note, Stored Procedures tend to slow development and
    maintenence because you have to make many changes in two places instead
    of just one. That will cost tens of thousands of dollars over the long
    run. They do have their advantages, but there are tradeoffs.

    > but the answer to your question is
    > that Sql Commands are easy to form in asp.net and have built in parameter
    > checking. You can now specify a parameter right in the string and then add
    > the parameter to the sql command and the parameter will be checked and then
    > inserted into the string:
    >
    > using System.Data;
    > using System.Data.SqlClient;
    >
    > using (SqlConnection connection = new SqlConnection(connectionString))
    > {
    > DataSet userDataset = new DataSet();
    > SqlDataAdapter myDataAdapter = new SqlDataAdapter(
    > "SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id",
    > connection);
    > myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
    > myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;
    > myDataAdapter.Fill(userDataset);
    > }



    Okay, but this does not seem to address the *optional* parameters
    and/or clauses that I was concerned about.


    >
    > The answer to your second question is that you can use a datagrid in asp.net
    > 1.1 or a gridview in asp.net 2.0.



    But the only asp.net examples I have seen suffer from one or more of
    the grid problems listed.


    >
    > Regards,
    >
    > --
    > S. Justin Gengo
    > Web Developer / Programmer


    Thanks for your feedback.

    >
    > Free code library:
    > http://www.aboutfortunate.com
    >
    > "Out of chaos comes order."
    > Nietzsche
    >
    >
    > "topmind" <> wrote in message
    > news:...
    > >I am generally new to dot.net, coming from "scriptish" web languages
    > > such as ColdFusion and Php.
    > >
    > > I have a few questions if you don't mind.
    > >
    > > First, how does one go about inserting dynamic SQL during run-time
    > > without lots of quotes? For example, adding "AND" clauses that may or
    > > may not be present based on the query criteria form.
    > >
    > > Some of the asp.net examples use the one-line append approach which
    > > seems awkward to work with (see 2nd example below).
    > >
    > > For example, in ColdFusion one can do something like:
    > >
    > > <CFquery settings="foo"> // query block - approx syntax only
    > > SELECT *
    > > FROM myTable
    > > WHERE 1=1 #andClause# // insert variable string
    > > <CFif x equals 1> // IF statement
    > > ORDER BY zip, zap
    > > <CFelse>
    > > ORDER BY norg
    > > </CFif> // end-if
    > > </CFquery> // end-query
    > >
    > > Here "andClause" is an "embedded" variable that is inserted into the
    > > SQL string at run-time. It may contain something like "and x = y and k
    > > = 2". It can also be blank (which is why the query has "1=1"). This is
    > > important because the parameters cannot be hard-wired ahead of time
    > > unlike what some template-based SQL preprocessors use. A sample
    > > "SqlDataSource" ASPX tag I looked into had this problem. It assumed all
    > > the "slots" will always have a usable value. Optional
    > > parameters/clauses is a common need for non-trivial forms and reports.
    > >
    > > I remember the old-style ASP required something like:
    > >
    > > sql = "SELECT * "
    > > sql = sql & "FROM myTable "
    > > sql = sql & "WHERE 1=1 " & andClause
    > > if x = 1 then
    > > sql = sql & " ORDER BY zip, zap "
    > > else
    > > sql = sql & " ORDER BY norg "
    > > end if
    > > rs = db.execute(sql) ...
    > >
    > > The quoting and string appending can get annoying and error-prone for
    > > longer SQL, especially if one has to copy and paste back and forth to
    > > test the SQL in a query processor (such as Toad or SQL-Server edit
    > > window).
    > >
    > > I am wondering what techniques one uses to simplify dynamic SQL
    > > handling in asp.net.
    > >
    > > Second, does asp.net have a decent editable grid? This would be a
    > > spreadsheet-like grid interface that can accept new data. ColdFusion
    > > has CFgrid for this. It is a bit clunky and crashy, but good enough for
    > > internal or small stuff.
    > >
    > > I have seen a lot of half-@ss implementations that don't measure up to
    > > what a user expects from client-server or desktop type apps
    > > (spreadsheets, MS-Access, VB6, etc.). For example, some web grid
    > > widgets don't have scroll-bars but Prev/Next buttons instead; others
    > > don't recognize the Down-Arrow key to move between rows; others have
    > > the column title row disappear when you scroll down; and many just
    > > crash a lot. Apparently it is a tough problem to solve given how few
    > > solutions there are. There are some JavaScript solutions that cost
    > > bucks.
    > >
    > > Thanks in advance for your response.
    > >
    > > -T-
    > >
     
    topmind, May 2, 2006
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ken North
    Replies:
    0
    Views:
    289
    Ken North
    Apr 3, 2004
  2. Ken North
    Replies:
    0
    Views:
    312
    Ken North
    Apr 3, 2004
  3. Simon Cropper
    Replies:
    4
    Views:
    310
    Simon Cropper
    Sep 6, 2011
  4. Iain Porter

    multiple grids on one page - use single Update/Edit/Cancel commands

    Iain Porter, Dec 15, 2003, in forum: ASP .Net Datagrid Control
    Replies:
    3
    Views:
    160
    Iain Porter
    Dec 16, 2003
  5. eagle

    Grids, dropdown, sql queries and extra spaces

    eagle, Aug 22, 2005, in forum: ASP .Net Datagrid Control
    Replies:
    2
    Views:
    154
    eagle
    Aug 23, 2005
Loading...

Share This Page