Пример #1
0
        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);
        }