SQL Server Stored Procedure without SqlParameter

SQL Server Stored Procedure without SqlParameter

Here's a quick snippet demonstrating the proper syntax for calling a SQL Server stored procedure that returns a SqlDataReader without using SqlParameter objects (which are a pain).

It shows how to pass string parameters as well as integers. It also shows how to reference the data points by column .vs index. Plus, you'll want to take notice of
the FINALLY clause which ensures that if the connection is still open should an error occur, that it gets closed.

Also notice that I've added the procedure owner to the EXEC string to avoid Cache Misses when your procedure is executed. Keep in mind, each instance of the EXEC string is cached. So, if the parameters change, another instance is cached.
It doesn't need to recompile your stored procedure and regenerate execution plans. However, it does recompile the small EXEC string. In my opinion, this is an extremely small performance overhead and would not show itself unless you have extremely heavy traffic against your database.


public void LoadMyData()
{

string sPrm1="stuff";
int nPrm2=5

SqlConnection oConn = new SqlConnection();

try
{

oConn.ConnectionString = "your connection string";
oConn.Open();

SqlCommand oCmd = new SqlCommand("EXEC procedureowner.MyProc '" + sPrm1 + "'," + nPrm2.ToString(),oConn);
oCmd.CommandType = CommandType.Text;
SqlDataReader oReader = oCmd.ExecuteReader();

while (oReader.Read())
{
System.Console.WriteLine("My col1: " + oReader["COL1"].ToString());
System.Console.WriteLine("My col2: " + oReader["COL2"].ToString());
}

oReader.Close();
oConn.Close();

}
catch (Exception e) { System.Console.WriteLine(e.Message); }
finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); } }
}






Submission Date:  9/23/2005 2:53:31 PM
Submitted By:  Robbe Morris
My Home Page:  http://www.robbemorris.com

By Robbe Morris   Popularity  (430 Views)
Picture
Biography - Robbe Morris
Robbe has been a Microsoft MVP in C# since 2004. He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.  Robbe also loves to scuba dive and go deep sea fishing in the Florida Keys or off the coast of Daytona Beach. Microsoft MVP