public static List <DataTable> MsSqlClient(this Ado ado, ref SqlParameter[] sqlParameters)
{
List <DataTable> dataTables = new List <DataTable>();
using (SqlConnection con = new SqlConnection(ado.ConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(ado.CommandText(), con))
{
cmd.CommandType = ado.CommandType();
cmd.Parameters.AddRange(sqlParameters);
try
{
con.Open();
switch (ado.Execute.ToLowerInvariant())
{
case Constants.QMsg.Ado.Execute.Reader:
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dataTables = reader.GetDataTables();
reader.Close();
break;
case Constants.QMsg.Ado.Execute.Scalar:
DataSet ds = new DataSet();
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{ adapter.Fill(ds); }
foreach (DataTable table in ds.Tables)
{
dataTables.Add(table);
}
break;
case Constants.QMsg.Ado.Execute.NonQuery:
int rows = cmd.ExecuteNonQuery();
DataTable dt2 = new DataTable();
dt2.Columns.Add(new DataColumn("Number Of Affected Rows", typeof(int)));
dt2.Rows.Add(rows);
dataTables.Add(dt2);
break;
}
}
catch (SqlException ex)
{
dataTables = null;
Console.WriteLine($"{ex.ToString()}");
throw;
}
finally
{
cmd.Cancel();
con.Close();
}
}
}
return(dataTables);
}