Building SqlParameters: Design Time or Runtime ?

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

I have never been a fan of building Input SqlParameters dynamically. I know
ADO.NET does a pretty good job figuring out datatype and size. However, I
like having the control and knowing I can affect the performance by building
them myself.

When I initially wrote our DAL, I built the SqlParameters dynamically
because this is how my clients pretty much demand I do it in the past. It
worked well except for one or two fields. So, I redesigned the DAL to build
the SqlParameters with specific code for each parameter. I added a simple
type that built the commands, as needed, for me:

Public Shared Function BuildUpdateCommand(ByVal tableName As String) As

SqlCommand

If tableName Is Nothing Then Throw New

ArgumentNullException("tableName")

Dim updateCommand As New SqlCommand

Select Case tableName.ToLower

Case "names"

updateCommand.CommandText = "[usp_update_names]"

updateCommand.CommandType =

System.Data.CommandType.StoredProcedure

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.VarChar, 6,

"names_id"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@me", System.Data.SqlDbType.VarChar, 1,

"names_me"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@ty", System.Data.SqlDbType.VarChar, 2,

"names_ty"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@lname", System.Data.SqlDbType.VarChar,

20, "names_lname"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@fname", System.Data.SqlDbType.VarChar,

15, "names_fname"))

Case "addr"

etc....

Return updateCommand

My prior, dynamic code, looked something like this:

' handler for DataAdapter RowUpdating event

Protected Shared Sub OnRowUpdating(ByVal sender As Object, ByVal e As

SqlRowUpdatingEventArgs)

Dim col As DataColumn

Dim drw As DataRow = e.Row

Dim myParam As SqlParameter

For Each col In drw.Table.Columns

Try

' check the original row against the current row to see if they are
different

If Not (drw(col, DataRowVersion.Current).Equals(drw(col,

DataRowVersion.Original))) Then

' build a parameter for the changed column

myParam = New SqlParameter("@" & col.ColumnName.Remove(0,

col.ColumnName.IndexOf("_") + 1), drw.Item(col).ToString)

e.Command.Parameters.Add(myParam)

Again, the 'dynamic' code worked nice except for 2-3 fields. I was not happy
giving control over to ADO.NET, but it seems to do the job nicely.

So, now my client has informed me there are cases where our client change
the type and size in their sql server tables. Each client maintains there
own sql server database.

Now, I need to go back to the 'dynamic' approach. I am resisting though as I
once again will lose control and will have to trust ADO.NET to build the
correct type and size.

Is there happy middle ground here? Is there a way to have a little more
control over the definition of a SqlParameter but still build it dynamically
or at design time? I will not use the CommandBuilder nor will I query the
sprocs to get this info. Too much overhead.





Greg Robinson

Custom Data Systems, Inc.

www.cds-am.net
 
Greg:

I tend to agree that things should be as explicit as possible. However, if
you pass in a parameter declared with the minimal constructor
cmd.Parameters.Add("@ParamName", ValueForParam)

you should be good to go. I've been told by many folks that I trust that
ADO.NET is very good at determing the correct type and that the performance
hit is not worth mentioning b/c it figures it out very quickly. So as long
as the value you send it is legitimate, you're in good shape.

The other option though is to query the table and use somethign like
DataReader.GetSchemaTable. Then you'll know the type (DataType) of the .NET
framework type (and you can easily convert this to the SQL Server equivalent
SqlDbType...) as well as the length. You will know the column mapping if
you need it but basically at this point you'll know the paramater name
(because you created it), the SqlDbType and the length:
cmd.Parameters.Add("@ParamName", SqlDbType.VarChar, 50).Value =
ValueForParam //where SqlDbType.Type will change depending on the schema
information and the 50 will change as well).

With that info, you can query the table at the initiation of the program or
right before you need it (which done over and over is wasteful) and build
the parameters with all the precise info you need. You'll have a small bit
of overhead with the getschematable, but that's negligible ,and you'll know
have they type and size for each param as soon as you need it. The only
possible problem is if the user changes the type in the middle of a session
when the program is running, but if you've hard coded the param information
(which you have) then that problem would exist anyway. If that's going to
happen, you can requery the schematable before you fire each command.
However, I really doubt the user is going to change the type/sizes that
frequently to warrant running the same command over and over.

I use this approach to dynamically determine the types for all cases where I
can't use Stored procs but I want to still use parameterized queries. in
each case, I query the tables at the beginning and use the datatable that I
got the schema table into in a module or static property of a class so I can
access it over and over. Since schema changes happen here but are
infrequent, getting the info at the begining of a session is good enough
(and there's no alternative other than constantly re-querying, even with
stored procs). This allows me to safely use 'dynamic sql' but still take
advantage of params for performance and security.

HTH,

Bill

HTH,

Bill
Greg said:
I have never been a fan of building Input SqlParameters dynamically. I know
ADO.NET does a pretty good job figuring out datatype and size. However, I
like having the control and knowing I can affect the performance by building
them myself.

When I initially wrote our DAL, I built the SqlParameters dynamically
because this is how my clients pretty much demand I do it in the past. It
worked well except for one or two fields. So, I redesigned the DAL to build
the SqlParameters with specific code for each parameter. I added a simple
type that built the commands, as needed, for me:

Public Shared Function BuildUpdateCommand(ByVal tableName As String) As

SqlCommand

If tableName Is Nothing Then Throw New

ArgumentNullException("tableName")

Dim updateCommand As New SqlCommand

Select Case tableName.ToLower

Case "names"

updateCommand.CommandText = "[usp_update_names]"

updateCommand.CommandType =

System.Data.CommandType.StoredProcedure

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.VarChar, 6,

"names_id"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@me", System.Data.SqlDbType.VarChar, 1,

"names_me"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@ty", System.Data.SqlDbType.VarChar, 2,
"names_ty"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@lname",
Click to expand...
System.Data.SqlDbType.VarChar,

20, "names_lname"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@fname", System.Data.SqlDbType.VarChar,

15, "names_fname"))

Case "addr"

etc....

Return updateCommand

My prior, dynamic code, looked something like this:

' handler for DataAdapter RowUpdating event

Protected Shared Sub OnRowUpdating(ByVal sender As Object, ByVal e As

SqlRowUpdatingEventArgs)

Dim col As DataColumn

Dim drw As DataRow = e.Row

Dim myParam As SqlParameter

For Each col In drw.Table.Columns

Try

' check the original row against the current row to see if they are
different

If Not (drw(col, DataRowVersion.Current).Equals(drw(col,

DataRowVersion.Original))) Then

' build a parameter for the changed column

myParam = New SqlParameter("@" & col.ColumnName.Remove(0,

col.ColumnName.IndexOf("_") + 1), drw.Item(col).ToString)

e.Command.Parameters.Add(myParam)

Again, the 'dynamic' code worked nice except for 2-3 fields. I was not happy
giving control over to ADO.NET, but it seems to do the job nicely.

So, now my client has informed me there are cases where our client change
the type and size in their sql server tables. Each client maintains there
own sql server database.

Now, I need to go back to the 'dynamic' approach. I am resisting though as I
once again will lose control and will have to trust ADO.NET to build the
correct type and size.

Is there happy middle ground here? Is there a way to have a little more
control over the definition of a SqlParameter but still build it dynamically
or at design time? I will not use the CommandBuilder nor will I query the
sprocs to get this info. Too much overhead.





Greg Robinson

Custom Data Systems, Inc.

www.cds-am.net
Click to expand...
 
Bill, what about output params? Any clever way to get this set at runtime
vs. design time? I have not come up with a way.


William Ryan eMVP said:
Greg:

I tend to agree that things should be as explicit as possible. However, if
you pass in a parameter declared with the minimal constructor
cmd.Parameters.Add("@ParamName", ValueForParam)

you should be good to go. I've been told by many folks that I trust that
ADO.NET is very good at determing the correct type and that the performance
hit is not worth mentioning b/c it figures it out very quickly. So as long
as the value you send it is legitimate, you're in good shape.

The other option though is to query the table and use somethign like
DataReader.GetSchemaTable. Then you'll know the type (DataType) of the ..NET
framework type (and you can easily convert this to the SQL Server equivalent
SqlDbType...) as well as the length. You will know the column mapping if
you need it but basically at this point you'll know the paramater name
(because you created it), the SqlDbType and the length:
cmd.Parameters.Add("@ParamName", SqlDbType.VarChar, 50).Value =
ValueForParam //where SqlDbType.Type will change depending on the schema
information and the 50 will change as well).

With that info, you can query the table at the initiation of the program or
right before you need it (which done over and over is wasteful) and build
the parameters with all the precise info you need. You'll have a small bit
of overhead with the getschematable, but that's negligible ,and you'll know
have they type and size for each param as soon as you need it. The only
possible problem is if the user changes the type in the middle of a session
when the program is running, but if you've hard coded the param information
(which you have) then that problem would exist anyway. If that's going to
happen, you can requery the schematable before you fire each command.
However, I really doubt the user is going to change the type/sizes that
frequently to warrant running the same command over and over.

I use this approach to dynamically determine the types for all cases where I
can't use Stored procs but I want to still use parameterized queries. in
each case, I query the tables at the beginning and use the datatable that I
got the schema table into in a module or static property of a class so I can
access it over and over. Since schema changes happen here but are
infrequent, getting the info at the begining of a session is good enough
(and there's no alternative other than constantly re-querying, even with
stored procs). This allows me to safely use 'dynamic sql' but still take
advantage of params for performance and security.

HTH,

Bill

HTH,

Bill
Greg said:
I have never been a fan of building Input SqlParameters dynamically. I know
ADO.NET does a pretty good job figuring out datatype and size. However, I
like having the control and knowing I can affect the performance by building
them myself.

When I initially wrote our DAL, I built the SqlParameters dynamically
because this is how my clients pretty much demand I do it in the past. It
worked well except for one or two fields. So, I redesigned the DAL to build
the SqlParameters with specific code for each parameter. I added a simple
type that built the commands, as needed, for me:

Public Shared Function BuildUpdateCommand(ByVal tableName As String) As

SqlCommand

If tableName Is Nothing Then Throw New

ArgumentNullException("tableName")

Dim updateCommand As New SqlCommand

Select Case tableName.ToLower

Case "names"

updateCommand.CommandText = "[usp_update_names]"

updateCommand.CommandType =

System.Data.CommandType.StoredProcedure

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.VarChar, 6,

"names_id"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@me", System.Data.SqlDbType.VarChar, 1,

"names_me"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@ty", System.Data.SqlDbType.VarChar, 2,
"names_ty"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@lname",
Click to expand...
System.Data.SqlDbType.VarChar,

20, "names_lname"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@fname", System.Data.SqlDbType.VarChar,

15, "names_fname"))

Case "addr"

etc....

Return updateCommand

My prior, dynamic code, looked something like this:

' handler for DataAdapter RowUpdating event

Protected Shared Sub OnRowUpdating(ByVal sender As Object, ByVal e As

SqlRowUpdatingEventArgs)

Dim col As DataColumn

Dim drw As DataRow = e.Row

Dim myParam As SqlParameter

For Each col In drw.Table.Columns

Try

' check the original row against the current row to see if they are
different

If Not (drw(col, DataRowVersion.Current).Equals(drw(col,

DataRowVersion.Original))) Then

' build a parameter for the changed column

myParam = New SqlParameter("@" & col.ColumnName.Remove(0,

col.ColumnName.IndexOf("_") + 1), drw.Item(col).ToString)

e.Command.Parameters.Add(myParam)

Again, the 'dynamic' code worked nice except for 2-3 fields. I was not happy
giving control over to ADO.NET, but it seems to do the job nicely.

So, now my client has informed me there are cases where our client change
the type and size in their sql server tables. Each client maintains there
own sql server database.

Now, I need to go back to the 'dynamic' approach. I am resisting though
Click to expand...
as
I
once again will lose control and will have to trust ADO.NET to build the
correct type and size.

Is there happy middle ground here? Is there a way to have a little more
control over the definition of a SqlParameter but still build it dynamically
or at design time? I will not use the CommandBuilder nor will I query the
sprocs to get this info. Too much overhead.





Greg Robinson

Custom Data Systems, Inc.

www.cds-am.net
Click to expand...
Click to expand...
 
I've started dragging the SP from the server explorer to the form (or
component) window. This generates functional Command object code.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Greg said:
Bill, what about output params? Any clever way to get this set at runtime
vs. design time? I have not come up with a way.


William Ryan eMVP said:
Greg:

I tend to agree that things should be as explicit as possible. However, if
you pass in a parameter declared with the minimal constructor
cmd.Parameters.Add("@ParamName", ValueForParam)

you should be good to go. I've been told by many folks that I trust that
ADO.NET is very good at determing the correct type and that the performance
hit is not worth mentioning b/c it figures it out very quickly. So as long
as the value you send it is legitimate, you're in good shape.

The other option though is to query the table and use somethign like
DataReader.GetSchemaTable. Then you'll know the type (DataType) of the .NET
framework type (and you can easily convert this to the SQL Server equivalent
SqlDbType...) as well as the length. You will know the column mapping if
you need it but basically at this point you'll know the paramater name
(because you created it), the SqlDbType and the length:
cmd.Parameters.Add("@ParamName", SqlDbType.VarChar, 50).Value =
ValueForParam //where SqlDbType.Type will change depending on the schema
information and the 50 will change as well).

With that info, you can query the table at the initiation of the program or
right before you need it (which done over and over is wasteful) and build
the parameters with all the precise info you need. You'll have a small bit
of overhead with the getschematable, but that's negligible ,and you'll know
have they type and size for each param as soon as you need it. The only
possible problem is if the user changes the type in the middle of a session
when the program is running, but if you've hard coded the param information
(which you have) then that problem would exist anyway. If that's going to
happen, you can requery the schematable before you fire each command.
However, I really doubt the user is going to change the type/sizes that
frequently to warrant running the same command over and over.

I use this approach to dynamically determine the types for all cases
Click to expand...
where
I
can't use Stored procs but I want to still use parameterized queries. in
each case, I query the tables at the beginning and use the datatable
Click to expand...
that
I
got the schema table into in a module or static property of a class so I can
access it over and over. Since schema changes happen here but are
infrequent, getting the info at the begining of a session is good enough
(and there's no alternative other than constantly re-querying, even with
stored procs). This allows me to safely use 'dynamic sql' but still take
advantage of params for performance and security.

HTH,

Bill

HTH,

Bill
Click to expand...
However,
I
like having the control and knowing I can affect the performance by building
them myself.

When I initially wrote our DAL, I built the SqlParameters dynamically
because this is how my clients pretty much demand I do it in the past. It
worked well except for one or two fields. So, I redesigned the DAL to build
the SqlParameters with specific code for each parameter. I added a simple
type that built the commands, as needed, for me:

Public Shared Function BuildUpdateCommand(ByVal tableName As String) As

SqlCommand

If tableName Is Nothing Then Throw New

ArgumentNullException("tableName")

Dim updateCommand As New SqlCommand

Select Case tableName.ToLower

Case "names"

updateCommand.CommandText = "[usp_update_names]"

updateCommand.CommandType =

System.Data.CommandType.StoredProcedure

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@id",
Click to expand...
System.Data.SqlDbType.VarChar,
6,
"names_id"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@me",
Click to expand...
System.Data.SqlDbType.VarChar,
1,
"names_me"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@ty",
Click to expand...
System.Data.SqlDbType.VarChar,
2,
"names_ty"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@lname", System.Data.SqlDbType.VarChar,

20, "names_lname"))

updateCommand.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@fname", System.Data.SqlDbType.VarChar,

15, "names_fname"))

Case "addr"

etc....

Return updateCommand

My prior, dynamic code, looked something like this:

' handler for DataAdapter RowUpdating event

Protected Shared Sub OnRowUpdating(ByVal sender As Object, ByVal e As

SqlRowUpdatingEventArgs)

Dim col As DataColumn

Dim drw As DataRow = e.Row

Dim myParam As SqlParameter

For Each col In drw.Table.Columns

Try

' check the original row against the current row to see if they are
different

If Not (drw(col, DataRowVersion.Current).Equals(drw(col,

DataRowVersion.Original))) Then

' build a parameter for the changed column

myParam = New SqlParameter("@" & col.ColumnName.Remove(0,

col.ColumnName.IndexOf("_") + 1), drw.Item(col).ToString)

e.Command.Parameters.Add(myParam)

Again, the 'dynamic' code worked nice except for 2-3 fields. I was not happy
giving control over to ADO.NET, but it seems to do the job nicely.

So, now my client has informed me there are cases where our client change
the type and size in their sql server tables. Each client maintains there
own sql server database.

Now, I need to go back to the 'dynamic' approach. I am resisting
Click to expand...
Click to expand...
though
as
Click to expand...
Click to expand...
 
Bill, I do this too as a time saver.

It still means I have to write SqlParameter code at design time for any
kind of sproc parameter other than an Input parameter. I have never
found a clever way to build a SqlParameter as Output at runtime.
 
Bill, using your suggestion

cmd.Parameters.Add("@ParamName", ValueForParam)

Our values for the param are in the sourceColumn, which is what I used when
I build the parameters at design time. I'd like to take advantage of this
at runtime vs. manually pulling out the value and then setting it.

Any ideas how?
 
Back
Top