Dynamic SQL handling, and edit-grids?

[topmind]

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]

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" <to*****@technologist.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

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-

[Flinky Wisty Pomm]

Just out of curiosity, are you the topmind of TableOrientedProgramming
fame, or am I accusing an innocent man of RelationalWeenieship?

[topmind]

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" <to*****@technologist.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

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-