SQL Parameters Question

  • Thread starter Thread starter Micheal
  • Start date Start date
M

Micheal

Hi,

I was wondering if I could manually loop through a data table and create SQL
Parameters from it. Instead of harding coding it like this:

new SqlParameter[] {
new SqlParameter( "@OrderID", orderID),
new SqlParameter( "@ProductCatagoryID", productCatagoryID),
new SqlParameter( "@EmployeeName", employeeName),
....

I understand I can use the data adapter, however I was looking for a manual
way to do this.

Thanks
 
Sure, you could have a loop, create a new SqlParameter with the "@" symbol
and the ColumnName of the column, and define the type based on the Column's
type (although since it's SqlDbType vs System.Type, you'd need to do a
conversion on it which woudln't be difficult)

foreach(DataColumn dc in myDataTable.Columns){
// Append @ + dc.ColumnName;
}
 
Hi Michael,

It depends on what you need those parameters for. Are you trying to generate
some SQL statements or you need to pass values to the stored procedure?
 
In the loop, there are a few ways depending on which constructor. At the
simplest (assuming our command object is named 'cmd')

foreach(DataColumn dc in myDataTable.Columns){
string ParamName = "@" + dc.ColumnName;'
cmd.Parameters.Add[ParamName];
}
You may opt for the more precise constructor at which point you'd need a
switch statement for instance, to determine the datatype of the given column
and convert it to its SqlDbType or OleDbType equivalent. You could use a
HashTable for instance, preloaded with the .NET type as the key and the Sql
type as the value. Then in the switch, find the .NET type which you get
from the dc.DataType and then that value will be what you add as the second
parameter in the constructor. Length could be grabbed from the dc (I didn't
double check but I"m almost 99% sure it's one of the properties) and you can
use the ColumnName for the tableMapping Column if you need this feature.

Does that answer it for you?
Michael said:
Thanks for the post. I understand how too loop through the datatable but how
do add to theSqlParameter[].

Thanks

William Ryan eMVP said:
Sure, you could have a loop, create a new SqlParameter with the "@" symbol
and the ColumnName of the column, and define the type based on the Column's
type (although since it's SqlDbType vs System.Type, you'd need to do a
conversion on it which woudln't be difficult)
Click to expand...
Micheal said:
Hi,

I was wondering if I could manually loop through a data table and
Click to expand...
create
SQL
Parameters from it. Instead of harding coding it like this:

new SqlParameter[] {
new SqlParameter( "@OrderID", orderID),
new SqlParameter( "@ProductCatagoryID", productCatagoryID),
new SqlParameter( "@EmployeeName", employeeName),
...

I understand I can use the data adapter, however I was looking for a manual
way to do this.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the post. I understand how too loop through the datatable but how
do add to theSqlParameter[].

Thanks

William Ryan eMVP said:
Sure, you could have a loop, create a new SqlParameter with the "@" symbol
and the ColumnName of the column, and define the type based on the Column's
type (although since it's SqlDbType vs System.Type, you'd need to do a
conversion on it which woudln't be difficult)

foreach(DataColumn dc in myDataTable.Columns){
// Append @ + dc.ColumnName;
}
Micheal said:
Hi,

I was wondering if I could manually loop through a data table and create SQL
Parameters from it. Instead of harding coding it like this:

new SqlParameter[] {
new SqlParameter( "@OrderID", orderID),
new SqlParameter( "@ProductCatagoryID", productCatagoryID),
new SqlParameter( "@EmployeeName", employeeName),
...

I understand I can use the data adapter, however I was looking for a manual
way to do this.

Thanks
Click to expand...
Click to expand...
 
I need to pass values to a stored procedure.

Thanks

Val Mazur said:
Hi Michael,

It depends on what you need those parameters for. Are you trying to generate
some SQL statements or you need to pass values to the stored procedure?

--
Val Mazur
Microsoft MVP


Micheal said:
Hi,

I was wondering if I could manually loop through a data table and create
SQL
Parameters from it. Instead of harding coding it like this:

new SqlParameter[] {
new SqlParameter( "@OrderID", orderID),
new SqlParameter( "@ProductCatagoryID", productCatagoryID),
new SqlParameter( "@EmployeeName", employeeName),
...

I understand I can use the data adapter, however I was looking for a
manual
way to do this.

Thanks
Click to expand...
Click to expand...
 
Thanks for the post. I am not attaching these to a command object but
rather I am trying to create an array of SqlParameters for my data access
layer for a stored procedure. My data access layer is similiar to microsoft
data access application block but with my personal tweeks.

Thanks

William Ryan eMVP said:
In the loop, there are a few ways depending on which constructor. At the
simplest (assuming our command object is named 'cmd')

foreach(DataColumn dc in myDataTable.Columns){
string ParamName = "@" + dc.ColumnName;'
cmd.Parameters.Add[ParamName];
}
You may opt for the more precise constructor at which point you'd need a
switch statement for instance, to determine the datatype of the given column
and convert it to its SqlDbType or OleDbType equivalent. You could use a
HashTable for instance, preloaded with the .NET type as the key and the Sql
type as the value. Then in the switch, find the .NET type which you get
from the dc.DataType and then that value will be what you add as the second
parameter in the constructor. Length could be grabbed from the dc (I didn't
double check but I"m almost 99% sure it's one of the properties) and you can
use the ColumnName for the tableMapping Column if you need this feature.

Does that answer it for you?
Michael said:
Thanks for the post. I understand how too loop through the datatable but how
do add to theSqlParameter[].

Thanks

William Ryan eMVP said:
Sure, you could have a loop, create a new SqlParameter with the "@" symbol
and the ColumnName of the column, and define the type based on the Column's
type (although since it's SqlDbType vs System.Type, you'd need to do a
conversion on it which woudln't be difficult)
Click to expand...
Hi,

I was wondering if I could manually loop through a data table and create
SQL
Parameters from it. Instead of harding coding it like this:

new SqlParameter[] {
new SqlParameter( "@OrderID", orderID),
new SqlParameter( "@ProductCatagoryID", productCatagoryID),
new SqlParameter( "@EmployeeName", employeeName),
...

I understand I can use the data adapter, however I was looking for a
manual
way to do this.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Ok, but the logic is still the same. Add them to an array instead of adding
them to the Parameters collection. The only difference is that you don't
need to know the number of items in advnance for the Params collection.
WIth an array you do, or you at least need to make sure you've allocated
enough space or you'll have to redim it.
Micheal said:
Thanks for the post. I am not attaching these to a command object but
rather I am trying to create an array of SqlParameters for my data access
layer for a stored procedure. My data access layer is similiar to microsoft
data access application block but with my personal tweeks.

Thanks

William Ryan eMVP said:
In the loop, there are a few ways depending on which constructor. At the
simplest (assuming our command object is named 'cmd')

foreach(DataColumn dc in myDataTable.Columns){
string ParamName = "@" + dc.ColumnName;'
cmd.Parameters.Add[ParamName];
}
You may opt for the more precise constructor at which point you'd need a
switch statement for instance, to determine the datatype of the given column
and convert it to its SqlDbType or OleDbType equivalent. You could use a
HashTable for instance, preloaded with the .NET type as the key and the Sql
type as the value. Then in the switch, find the .NET type which you get
from the dc.DataType and then that value will be what you add as the second
parameter in the constructor. Length could be grabbed from the dc (I didn't
double check but I"m almost 99% sure it's one of the properties) and you can
use the ColumnName for the tableMapping Column if you need this feature.

Does that answer it for you?
Michael said:
Thanks for the post. I understand how too loop through the datatable
Click to expand...
but
how
do add to theSqlParameter[].

Thanks

Sure, you could have a loop, create a new SqlParameter with the "@" symbol
and the ColumnName of the column, and define the type based on the
Column's
type (although since it's SqlDbType vs System.Type, you'd need to do a
conversion on it which woudln't be difficult)


Hi,

I was wondering if I could manually loop through a data table and create
SQL
Parameters from it. Instead of harding coding it like this:

new SqlParameter[] {
new SqlParameter( "@OrderID", orderID),
new SqlParameter( "@ProductCatagoryID", productCatagoryID),
new SqlParameter( "@EmployeeName", employeeName),
...

I understand I can use the data adapter, however I was looking for a
manual
way to do this.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top