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;
}
}
