private void RunStoredProcedure(string procedureName, Action <IDataReader> loadDataAction, Dictionary <string, object> parameters = null) { using (var connection = CDCHelper.GetConnection("Data Source=WIN-JEEQO9D602;Initial Catalog=CDC;Integrated Security=True;")) using (var command = connection.CreateCommand()) { command.CommandText = procedureName; command.CommandType = CommandType.StoredProcedure; if (parameters != null) { foreach (var p in parameters) { var param = command.Parameters.AddWithValue(p.Key, p.Value); if (p.Value is DataTable) { param.SqlDbType = SqlDbType.Structured; } } } connection.Open(); if (loadDataAction != null) { var reader = command.ExecuteReader(); loadDataAction(reader); } else { command.ExecuteNonQuery(); } } }
/// <summary> /// Function to hide the details about running an SP /// </summary> /// <param name="procedureName">the name of the SP to run</param> /// <param name="connectionString">a connection string to the SQL Server database</param> /// <param name="loadDataAction">a function to call that will extract the data from the reader. The data items will likely be in a closure for this to work</param> private void RunStoredProcedure(string procedureName, string connectionString, Action <IDataReader> loadDataAction, Dictionary <string, object> parameters = null) { try { using (var connection = CDCHelper.GetConnection(ConnectionString)) using (var command = connection.CreateCommand()) { command.CommandText = procedureName; command.CommandType = CommandType.StoredProcedure; if (parameters != null) { foreach (var p in parameters) { var param = command.Parameters.AddWithValue(p.Key, p.Value); if (p.Value is DataTable) { param.SqlDbType = SqlDbType.Structured; } } } connection.Open(); if (loadDataAction != null) { var reader = command.ExecuteReader(); loadDataAction(reader); } else { command.ExecuteNonQuery(); } } } catch (Exception ex) { //TODO:handle errors here and decide what to do throw; } }