SQL Parameterized Command versus Custom String

[henry.lee.jr]

Quick question all,

I have a bad habit where I still write my SQL command code like so:

string sql;
sql = Properties.Settings.Default.PROC_MyProc;
sql += "@Param1='" + param1 + "'";
sql += " ,@Param2=" + param2.ToString();
sql += "@Param3='" + param3 + "'";
sql += " ,@Param4=" + param4.ToString();
if([some coondition]) { sql += "@Param5='" + param5 + "'"; }

// Execute sql

I always have a library function that I use to handle special cases
(strings with a single quote, etc), but the main reason I do this is
support. I realize lots of fancy articles warn against this old
fashioned approach, but (A) it has never once caused me any problems
and (B) it may even resolve some of the below issues:

I've had some trouble with using the Parameter objects in the past. In
some scenarios, when there was a very long list of parameters, it
would just fail with an exception. I was able to research and find out
that when you have a high number of parameters, sometimes SQL Server
and ADO.NET "just couldn't handle it". We proved this by removing some
parameters, and to be sure it wasn't a bad parameter at the end, we
even changed the order of the parameters around. It seemed to be the
number of them, not the content or type. This concerned me quite a bit
(and maybe with more recent versions of SQL Server and ADO it has been
handled). We're not talking hundreds of parameters either, more like
20 or so. This was .NET 2.0 and SQL Server 2000.

The other reason is so that I can easily debug/output my exact SQL and
run it in query analyzer to see why it may be failing. My question is
a simple one: Is there a way to see exactly what SQL the parameterized
command object is trying to execute so you can test it against SQL
Server? If not, I really dislike the black box concept, where you
can't see what M$ may be doing wrong behind the scenes.

 

[Gregory A. Beamer]

Is there a way to see exactly what SQL the parameterized
command object is trying to execute so you can test it against SQL
Server?

SQL Profiler? That is the tool I would use in development. Just run and
watch the profiler spit out the incorrect SQL. ;-)

I don't like the way you are calling sprocs, however. Attaching the
params is so much cleaner than attempting to make an "exec" string.

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Sam]

Hi Greg,

I am only new to C#
I would use the EXEC String
What do you mean by

Attaching the params is so much cleaner

Do you have a eg or link to eg

TIA

Samf

 

[henry.lee.jr]

Check out this link for an example of using the SqlParameter option:

http://www.codeproject.com/KB/database/NET_Data_Access.aspx

 

[Sam]

Thank you Henry

Samf

Check out this link for an example of using the SqlParameter option:

http://www.codeproject.com/KB/database/NET_Data_Access.aspx

 

[Gregory A. Beamer]

Hi Greg,

I am only new to C#
I would use the EXEC String
What do you mean by
Do you have a eg or link to eg

TIA

Samf

string connString = ConfigurationManager
.ConnectionStrings["myConn"].ConnectionString;
string sql = "{stored proc name}";

SqlConnection connection = new SqlConnection(connString);
SqlCommand command = new SqlCommand(conn, sql);
command.CommandType = CommandType.StoredProcedure;

//Add parameters without EXEC (option 1)
command.AddWithValue("@paramname1", param1);

//Option 2 (short verison, consult help file for more
// explicit versions)
SqlParameter param = new SqlParameter("@paramname");
param.Vlaue = param2;

Calling in this way avoids the possibility of SQL injection and is much
smoother.

As for running a command, it is generally like this:

try
{
connection.Open();
//Run command here, one option is DataSet
adapter.Fill(myDataSet);
}
finally
{
connection.Dispose();
}

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************