Execute a Stored Procedure: Quick and Dirty
Ok, I usually try to only talk about best practices but sometimes we all need a quick an dirty few lines to execute a stored procedure, no Enterprise Data Access, no LINQ, just System.Data and the managed provider for our given DB. Well here goes.
1 //get the config info from the app.config file
2 ConnectionStringSettings myConnectionSettings = ConfigurationManager.ConnectionStrings["ConnectionKey"];
3 DbProviderFactory myProvider = DbProviderFactories.GetFactory(myConnectionSettings.ProviderName);
4
5 using (DbConnection myConnection = myProvider.CreateConnection())
6 using (DbCommand myCommand = myProvider.CreateCommand())
7 {
8 // Get the connection string from the connectionsettings
9 // This gets us the specific provider by which we will connect
10 myConnection.ConnectionString = myConnectionSettings.ConnectionString;
11 myCommand.Connection = myConnection;
12 myCommand.CommandType = System.Data.CommandType.StoredProcedure;
13
14 DbParameter parm = myProvider.CreateParameter();
15 parm.ParameterName = "Your parameter Name";
16 parm.Value = id;
17 myCommand.Parameters.Add(parm);
18
19 // Open the connection
20 myConnection.Open();
21
22 myCommand.CommandText = "Name of the stored proc";
23 myCommand.ExecuteNonQuery();
24 }