By using of ADO.NET you can very easy execute stored procedures which expect some parameters. The interesting thing at this point is that the command syntax by executing of the same stored procedure by using of ODBC driver is different. This sounds hard, but the good thing is that the difference is relative easy to handle. The bad thing is that you have to handle it.
Following two examples shows the test stored procedure and illustrate how to execute it by passing two parameters and by using of SqlClient (not ODBC).
ALTER PROCEDURE dbo.StoredProcedure2
(
@parameter1 nvarchar(50) = 'default val',
@parameter2 nvarchar(50) = 'default val2',
@parameter3 nvarchar(50) = 'default val3'
) AS
Insert into Table1 (col1,col2,col3)
values(@parameter1, @parameter2, @parameter3) RETURN |
Note that in the following example the SqlClient connection is used. Red-bold parts will be different by using of ODBC.
string cnStr =
"Data Source=dado-nb1;Initial
Catalog=TestDb;Integrated Security=True";
DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.SqlClient"); DbConnection dbCon = factory.CreateConnection();
dbCon.ConnectionString = cnStr;
dbCon.Open(); for (int n = 0; n < 10000; n++) {
DbCommand cmd2 = factory.CreateCommand();
cmd2.Connection = dbCon;
cmd2.CommandType = System.Data.CommandType.StoredProcedure;
cmd2.CommandText = "StoredProcedure2"; DbParameter par1 = factory.CreateParameter();
par1.ParameterName = "@parameter1";
par1.DbType = System.Data.DbType.String;
par1.Value = DateTime.Now.ToUniversalTime();
par1.Direction = System.Data.ParameterDirection.Input; DbParameter par2 = factory.CreateParameter();
par1.ParameterName = "@parameter2";
par2.DbType = System.Data.DbType.String;
par2.Value = DateTime.Now.ToString();
par2.Direction = System.Data.ParameterDirection.Input;
cmd2.Parameters.Add(par1);
cmd2.Parameters.Add(par2);
cmd2.ExecuteNonQuery();
} |
This example shows how to insert 10000 rows in the table Table1 by using of the stored procedure StoredProcedure2. If the provider is changed now to ODBC, the same code will not work. To be honest, the same code will work, but not as expected. For example if you execute it new records will be inserted in the database, but parameterized values will be ignored.
Next example shows how to do the same thing by using of ODBC provider:
string cnStr =
"Driver={SQL Server};
description=Test ODBC to SQL Server;server=dado-nb1;uid=xxx;
trusted_connection=Yes;
app=Microsoft® Visual Studio® 2005;
wsid=DADO-NB1;database=TestDb"; DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.Odbc");
DbConnection dbCon = factory.CreateConnection();
dbCon.ConnectionString = cnStr;
dbCon.Open(); for (int n = 0; n < 10000; n++)
{
DbCommand cmd2 = factory.CreateCommand();
cmd2.Connection = dbCon;
cmd2.CommandType = System.Data.CommandType.StoredProcedure;
cmd2.CommandText = "{CALL StoredProcedure2 (?, ?)}"; DbParameter par1 = factory.CreateParameter();
par1.ParameterName = "@parameter1";
par1.DbType = System.Data.DbType.String;
par1.Value = DateTime.Now.ToUniversalTime();
par1.Direction = System.Data.ParameterDirection.Input; DbParameter par2 = factory.CreateParameter();
par1.ParameterName = "@parameter2";
par2.DbType = System.Data.DbType.String;
par2.Value = DateTime.Now.ToString();
par2.Direction = System.Data.ParameterDirection.Input; cmd2.Parameters.Add(par1);
cmd2.Parameters.Add(par2);
cmd2.ExecuteNonQuery();
} |
Note that in this example I provided two parameters only, although the procedure expects three. In this case the third's default one value is used.
Last but not least, following example shows the syntax, which should be used by executing of stored procedures, which returns one parameter.
{ ? = CALL PROCEDURE_NAME (?, ?) } |
Now, very last, but really not least. The ODBC .NET managed provider, like the OLE DB .NET data provider, processes parameters by ordinal position (zero-based) and not by name.
Posted
Feb 03 2007, 01:19 PM
by
Damir Dobric