Заполнение таблицы данных из устройства чтения данных

Я делаю основную вещь в С# (MS VS2008) и задаю вопрос о правильном дизайне, чем конкретный код.

Я создаю datatable, а затем пытаюсь загрузить datatable из datareader (который основан на хранимой процедуре SQL). Меня интересует, является ли самый эффективный способ загрузки данных - делать инструкции while или, если есть лучший способ.

Для меня единственный недостаток - мне нужно вручную вводить поля, которые я хочу добавить в мой оператор while, но я также не знаю, как это автоматизировать, так как я не хочу, чтобы все поля из SP просто выберите их, но это не огромная сделка в моих глазах.

Я включил фрагменты кода ниже всего, что я делаю, хотя для меня сам код не примечателен или даже о том, о чем я прошу. Moreso интересуется моей методологией, я буду приставать к кодовой помощи позже, если моя стратегия неверна/неэффективна.

var dtWriteoffUpload = new DataTable();
dtWriteoffUpload.Columns.Add("Unit");
dtWriteoffUpload.Columns.Add("Year");
dtWriteoffUpload.Columns.Add("Period");
dtWriteoffUpload.Columns.Add("Acct");
dtWriteoffUpload.Columns.Add("Descr");
dtWriteoffUpload.Columns.Add("DEFERRAL_TYPE");
dtWriteoffUpload.Columns.Add("NDC_Indicator");
dtWriteoffUpload.Columns.Add("Mgmt Cd");
dtWriteoffUpload.Columns.Add("Prod");
dtWriteoffUpload.Columns.Add("Node");
dtWriteoffUpload.Columns.Add("Curve_Family");
dtWriteoffUpload.Columns.Add("Sum Amount");
dtWriteoffUpload.Columns.Add("Base Curr");
dtWriteoffUpload.Columns.Add("Ledger");  

cmd = util.SqlConn.CreateCommand();
cmd.CommandTimeout = 1000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "proc_writeoff_data_details";
cmd.Parameters.Add("@whoAmI", SqlDbType.VarChar).Value = 

WindowsIdentity.GetCurrent().Name;

cmd.Parameters.Add("@parmEndDateKey", SqlDbType.VarChar).Value = myMostRecentActualDate;
cmd.Parameters.Add("@countrykeys", SqlDbType.VarChar).Value = myCountryKey;
cmd.Parameters.Add("@nodekeys", SqlDbType.VarChar).Value = "1,2";
break;


dr = cmd.ExecuteReader();
while (dr.Read())                    
{
    dtWriteoffUpload.Rows.Add(dr["country name"].ToString(), dr["country key"].ToString());
}

Ответ 1

Вы можете загрузить DataTable непосредственно из устройства чтения данных, используя метод Load(), который принимает IDataReader.

var dataReader = cmd.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(dataReader);

Ответ 2

Пожалуйста, проверьте приведенный ниже код. Автоматически он преобразуется как DataTable

private void ConvertDataReaderToTableManually()
    {
        SqlConnection conn = null;
        try
        {
            string connString = ConfigurationManager.ConnectionStrings["NorthwindConn"].ConnectionString;
            conn = new SqlConnection(connString);
            string query = "SELECT * FROM Customers";
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            DataTable dtSchema = dr.GetSchemaTable();
            DataTable dt = new DataTable();
            // You can also use an ArrayList instead of List<>
            List<DataColumn> listCols = new List<DataColumn>();

            if (dtSchema != null)
            {
                foreach (DataRow drow in dtSchema.Rows)
                {
                    string columnName = System.Convert.ToString(drow["ColumnName"]);
                    DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
                    column.Unique = (bool)drow["IsUnique"];
                    column.AllowDBNull = (bool)drow["AllowDBNull"];
                    column.AutoIncrement = (bool)drow["IsAutoIncrement"];
                    listCols.Add(column);
                    dt.Columns.Add(column);
                }
            }

            // Read rows from DataReader and populate the DataTable
            while (dr.Read())
            {
                DataRow dataRow = dt.NewRow();
                for (int i = 0; i < listCols.Count; i++)
                {
                    dataRow[((DataColumn)listCols[i])] = dr[i];
                }
                dt.Rows.Add(dataRow);
            }
            GridView2.DataSource = dt;
            GridView2.DataBind();
        }
        catch (SqlException ex)
        {
            // handle error
        }
        catch (Exception ex)
        {
            // handle error
        }
        finally
        {
            conn.Close();
        }

    }

Ответ 3

Если вы пытаетесь загрузить DataTable, используйте вместо этого SqlDataAdapter:

DataTable dt = new DataTable();

using (SqlConnection c = new SqlConnection(cString))
using (SqlDataAdapter sda = new SqlDataAdapter(sql, c))
{
    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
    sda.SelectCommand.Parameters.AddWithValue("@parm1", val1);
    ...

    sda.Fill(dt);
}

Вам даже не нужно определять столбцы. Просто создайте DataTable и Fill.

Здесь cString - ваша строка соединения, а sql - это команда хранимой процедуры.

Ответ 4

Как сказал Саги в своем ответе DataTable.Load - хорошее решение. Если вы пытаетесь загрузить несколько таблиц из одного считывателя, вам не нужно вызывать DataReader.NextResult. Метод DataTable.Load также продвигает читателя к следующему набору результатов (если есть).

// Read every result set in the data reader.
while (!reader.IsClosed)
{
    DataTable dt = new DataTable();
    // DataTable.Load automatically advances the reader to the next result set
    dt.Load(reader);
    items.Add(dt);
}

Ответ 5

Я также изучил это, и, сравнив метод SqlDataAdaptor.Fill с функциями SqlDataReader.Load, я обнаружил, что метод SqlDataAdaptor.Fill более чем в два раза быстрее с наборами результатов, которые я использовал

Используемый код:

    [TestMethod]
    public void SQLCommandVsAddaptor()
    {
        long adaptorFillLargeTableTime, readerLoadLargeTableTime, adaptorFillMediumTableTime, readerLoadMediumTableTime, adaptorFillSmallTableTime, readerLoadSmallTableTime, adaptorFillTinyTableTime, readerLoadTinyTableTime;

        string LargeTableToFill = "select top 10000 * from FooBar";
        string MediumTableToFill = "select top 1000 * from FooBar";
        string SmallTableToFill = "select top 100 * from FooBar";
        string TinyTableToFill = "select top 10 * from FooBar";

        using (SqlConnection sconn = new SqlConnection("Data Source=.;initial catalog=Foo;persist security info=True; user id=bar;password=foobar;"))
        {
            // large data set measurements
            adaptorFillLargeTableTime = MeasureExecutionTimeMethod(sconn, LargeTableToFill, ExecuteDataAdapterFillStep);
            readerLoadLargeTableTime = MeasureExecutionTimeMethod(sconn, LargeTableToFill, ExecuteSqlReaderLoadStep);
            // medium data set measurements
            adaptorFillMediumTableTime = MeasureExecutionTimeMethod(sconn, MediumTableToFill, ExecuteDataAdapterFillStep);
            readerLoadMediumTableTime = MeasureExecutionTimeMethod(sconn, MediumTableToFill, ExecuteSqlReaderLoadStep);
            // small data set measurements
            adaptorFillSmallTableTime = MeasureExecutionTimeMethod(sconn, SmallTableToFill, ExecuteDataAdapterFillStep);
            readerLoadSmallTableTime = MeasureExecutionTimeMethod(sconn, SmallTableToFill, ExecuteSqlReaderLoadStep);
            // tiny data set measurements
            adaptorFillTinyTableTime = MeasureExecutionTimeMethod(sconn, TinyTableToFill, ExecuteDataAdapterFillStep);
            readerLoadTinyTableTime = MeasureExecutionTimeMethod(sconn, TinyTableToFill, ExecuteSqlReaderLoadStep);
        }
        using (StreamWriter writer = new StreamWriter("result_sql_compare.txt"))
        {
            writer.WriteLine("10000 rows");
            writer.WriteLine("Sql Data Adapter 100 times table fill speed 10000 rows: {0} milliseconds", adaptorFillLargeTableTime);
            writer.WriteLine("Sql Data Reader 100 times table load speed 10000 rows: {0} milliseconds", readerLoadLargeTableTime);
            writer.WriteLine("1000 rows");
            writer.WriteLine("Sql Data Adapter 100 times table fill speed 1000 rows: {0} milliseconds", adaptorFillMediumTableTime);
            writer.WriteLine("Sql Data Reader 100 times table load speed 1000 rows: {0} milliseconds", readerLoadMediumTableTime);
            writer.WriteLine("100 rows");
            writer.WriteLine("Sql Data Adapter 100 times table fill speed 100 rows: {0} milliseconds", adaptorFillSmallTableTime);
            writer.WriteLine("Sql Data Reader 100 times table load speed 100 rows: {0} milliseconds", readerLoadSmallTableTime);
            writer.WriteLine("10 rows");
            writer.WriteLine("Sql Data Adapter 100 times table fill speed 10 rows: {0} milliseconds", adaptorFillTinyTableTime);
            writer.WriteLine("Sql Data Reader 100 times table load speed 10 rows: {0} milliseconds", readerLoadTinyTableTime);

        }
        Process.Start("result_sql_compare.txt");
    }

    private long MeasureExecutionTimeMethod(SqlConnection conn, string query, Action<SqlConnection, string> Method)
    {
        long time; // know C#
        // execute single read step outside measurement time, to warm up cache or whatever
        Method(conn, query);
        // start timing
        time = Environment.TickCount;
        for (int i = 0; i < 100; i++)
        {
            Method(conn, query);
        }
        // return time in milliseconds
        return Environment.TickCount - time;
    }

    private void ExecuteDataAdapterFillStep(SqlConnection conn, string query)
    {
        DataTable tab = new DataTable();
        conn.Open();
        using (SqlDataAdapter comm = new SqlDataAdapter(query, conn))
        {
            // adaptor fill table function
            comm.Fill(tab);
        }
        conn.Close();
    }

    private void ExecuteSqlReaderLoadStep(SqlConnection conn, string query)
    {
        DataTable tab = new DataTable();
        conn.Open();
        using (SqlCommand comm = new SqlCommand(query, conn))
        {
            using (SqlDataReader reader = comm.ExecuteReader())
            {
                // IDataReader Load function
                tab.Load(reader);
            }
        }
        conn.Close();
    }

результаты:

10000 строк:
Sql Data Adapter 100-кратная скорость заполнения таблицы 10000 строк: 11782 миллисекунды
Sql Data Reader 100 раз скорость загрузки таблицы 10000 строк: 26047 миллисекунд
1000 строк:
Sql Data Adapter 100-кратная скорость заполнения таблицы 1000 строк: 984 миллисекунды
Sql Data Reader 100-кратная скорость загрузки таблицы 1000 строк: 2031 миллисекунды
100 строк:
Sql Data Adapter 100-кратная скорость заполнения таблицы 100 строк: 125 миллисекунд
Sql Data Reader 100 раз скорость загрузки таблицы 100 строк: 235 миллисекунд
10 строк:
Sql Data Adapter 100-кратная скорость заполнения таблицы 10 строк: 32 миллисекунды
Sql Data Reader 100 раз скорость загрузки таблицы 10 строк: 93 миллисекунды

Для проблем с производительностью использование метода SqlDataAdaptor.Fill намного эффективнее. Поэтому, если вы не хотите стрелять в ногу, используйте это. Он работает быстрее для небольших и больших наборов данных.