C# Execute a Stored Procedure


How do you execute a SQL Server stored procedure from within a C# program? The Microsoft Developer Network has some sample code shown below. The application requires permission to access the database and execute the stored procedure. You will need to build a connection string and know the stored procedure name. You could make a separate class in your program called ExecStoredProc or whatever makes sense. You could have private string variables that have public properties for your connection string and the stored procedure name. The calling routine instantiates the class and sets the properties. It then calls the method that you create that will be similar to the code shown below.

SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;

cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

reader = cmd.ExecuteReader();
// Data is accessible through the DataReader object here.

sqlConnection1.Close();

Parameters

cmd.Parameters["au_lname"].Value = "Porter";

Leave a comment

Your email address will not be published. Required fields are marked *