public static DataSet ExecuteStoredProcedure(
ISQLDataConnection sqlDataConnection,
string storedProcedureName,
List <SQLParameter> listOfSQLParameter)
{
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection(sqlDataConnection.GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(storedProcedureName, con))
{
cmd.CommandType = CommandType.StoredProcedure;
foreach (var parameter in listOfSQLParameter)
{
if (parameter.Value == null)
{
cmd.Parameters.Add(parameter.ParamaterName, parameter.ParameterType).Value = DBNull.Value;
continue;
}
cmd.Parameters.Add(parameter.ParamaterName, parameter.ParameterType).Value = parameter.Value;
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
}
}
return(ds);
}
public static string GetConnectionString(this ISQLDataConnection sqlDataConnection)
{
if (sqlDataConnection.IsSQLServerAuthentication)
{
return("Data Source=" + sqlDataConnection.Server + ";Initial Catalog=" + sqlDataConnection.Database + ";User ID=" + sqlDataConnection.userName + ";Password="******";");
}
return("Data Source=" + sqlDataConnection.Server + ";Initial Catalog=" + sqlDataConnection.Database + ";Integrated Security=True");
}
