Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider

Damir Dobric Posts

Next talks:

 

    

Follow me on Twitter: #ddobric



 

 

Archives

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
Filed under:

Comments

Andreas Erben wrote re: Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider
on 02-04-2007 1:15

Good practical introduction to the topic.

"Do you want to know more?":

http://msdn2.microsoft.com/en-us/library/ms379620(VS.80).aspx

Generic Coding with the ADO.NET 2.0 Base Classes and Factories

Rajesh A wrote re: Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider
on 10-05-2007 14:38

Project background:

------------------

I am using FreeTDS and UnixODBC on RH linux 4.0. We are connecting to Microsoft SQL Server 8.0 from linux. We are using C++ on Linux.

Requirement:

-----------

I am calling Stored Procedure(SP) with input and output parameter and I am also expecting return value as well.

I am calling stored procedure as follows:

{? = CALL stp_returnRow(?, ?, ?)}

In above case I am expecting both return value and output parameter. The first parameter is input and 2nd and 3rd parameter is output parameter.

We are not getting the required values.

please suggest or give us sample code

Rajesh A wrote re: Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider
on 10-05-2007 14:39

Project background:

------------------

I am using FreeTDS and UnixODBC on RH linux 4.0. We are connecting to Microsoft SQL Server 8.0 from linux. We are using C++ on Linux.

Requirement:

-----------

I am calling Stored Procedure(SP) with input and output parameter and I am also expecting return value as well.

I am calling stored procedure as follows:

{? = CALL stp_returnRow(?, ?, ?)}

In above case I am expecting both return value and output parameter. The first parameter is input and 2nd and 3rd parameter is output parameter.

We are not getting the required values.

please suggest or give me solution if possible.

My email id: rajesh.akolu@ftindia.com

Willson Gary wrote re: Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider
on 03-04-2009 9:47

Superblog

developers.de is a .Net Community Blog powered by daenet GmbH.