从sql数据库中检索数据 [英] Data retrieving from sql database

问题描述

我从sql数据表中检索数据时从索引中获取内存，在代码中我调用strored过程并将数据获取到数据读取器但是在将数据加载到datatable时我因为大而导致内存超出索引错误数据，但我必须将检索到的数据导出到excel，所以我正在读取表中的所有数据，任何替代是将数据导入数据表，我使用下面的代码检索数据...

I am getting memory out of index while retrieving data from sql data table, In code I am calling strored procedure and Getting data to the data reader but while loading that data into datatable I am getting memory out of index error due to large data but i have to export that retrieved data into excel, so i am reading all the data from the table, any alternate is there to get data into datatable, i am using below code for retrieving data...

DataTable dt = new DataTable();              
               SqlDataReader dr = null;
               SqlCommand cmd = new SqlCommand("Sp_callingprocedure", con);
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.Parameters.AddWithValue("@date_from",date_from);
               cmd.Parameters.AddWithValue("@date_to", date_to);
               dr = cmd.ExecuteReader();
              dt.Load(dr); here I am getting error

如果有替代品请分享







提前致谢

If any alternate is there please share



Thanks in advance

推荐答案

使用的主要原因DataReader而不是DataAdapter是因为整个数据集不会在一个块中返回。

你用它做什么？尝试将整个数据读入DataTable ......：叹气：

不要从DataReader填充DataTable，而是使用循环从读取器中依次​​读取每一行，然后再处理它阅读下一篇：

The main reason for using a DataReader instead of a DataAdapter is so that the whole dataset is not returned in one block.
And what do you do with it? Try to read the whole data into a DataTable anyway... :sigh:
Instead of filling a DataTable from the DataReader, use a loop to read each row from the reader in sequence, and process it before reading the next:

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("Sp_callingprocedure", con))
        {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@date_from",date_from);
        cmd.Parameters.AddWithValue("@date_to", date_to);
        using (SqlDataReader reader = cmd.ExecuteReader())
            {
            while (reader.Read())
                {
                int id = (int) reader["Id"];
                string desc = (string) reader["description"];
                Console.WriteLine("ID: {0}\n    {1}", id, desc);
                }
            }
        }
    }

这篇关于从sql数据库中检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！