C＃SQL导出（OutofMemory问题）

Question

我试图批量导出到sql表中，代码工作正常。完全没错，问题在于如何以270万行导出50列。有人可以帮我解决OUtofMemory异常。我知道为什么会这样，因为它试图将所有内容读取到内存中然后写入，但是我不确定如何将它分解成一行一行地读取然后写出来而不会花费很长时间。

string contents = File.ReadAllText(textBox3.Text);

string tableid = tableid = Regex.Match(contents, @"CoreDBCaseID=(?<id>\d+)").Groups["id"].Value;
string server = server = Regex.Match(contents, @"Server=(?<Server>[^;]+)").Groups["Server"].Value;
string security = security = Regex.Match(contents, "Security=(?<Security>[^;]+)").Groups["Security"].Value;
string database = database = Regex.Match(contents, "Database=(?<Database>[^\r]+)").Groups["Database"].Value;

string[] data = new string[] {
    string.Format("Table={0}", tableid),
    string.Format("Server={0}", server),
    string.Format("Security={0}", security),
    string.Format("Database={0}", database),
};
string sqltable = ("TDS_C" + tableid + "_table");
String cols = String.Join(",", listBox1.Items.Cast<String>().ToArray());
string sql = "select " + cols + " from " + sqltable + " where *;
SqlConnection con = new SqlConnection("Data Source=" + server + ";Initial Catalog=" + database + ";Integrated Security=" + security);
con.Open();
SqlDataAdapter tabadapter = new SqlDataAdapter(sql, con);
DataSet dataset = new DataSet();
tabadapter.FillSchema(dataset, SchemaType.Source, sqltable);
tabadapter.Fill(dataset, sqltable);
DataTable tbltarget = dataset.Tables[0];
string output_text =
    tbltarget.Columns.Cast<DataColumn>().ToList()
    .Select(col => col.ColumnName)
    .Aggregate((current, next) => current + "|" + next) + "\r\n"
+
    tbltarget.Rows.Cast<DataRow>().ToList()
    .Select(row => row.ItemArray.Aggregate((current, next) => current.ToString() + "|" + next.ToString().Replace("\n", "")))
    .Cast<string>().Aggregate((current, next) => current + "\r\n" + next);

File.WriteAllText(@"C:\Export.txt);

con.Close();

Answer 1

不要填写数据集。取而代之的是，使用数据读取器，然后您可以在读入数据时将其流式传输出去，这意味着内存占用空间要小得多。

您可以考虑从数据库进行异步读取，并触发对文件的异步写入。这将停止每次读写操作是完全阻塞的操作。

简单的例子：

using (SqlConnection connection = new SqlConnection(connectionString)){
connection.Open();
using (FileStream strm = new FileStream(filePath)){
using (TextWriter wrt = new TextWriter(strm)){
SqlCommand cmd = new SqlCommand(sql, connection);
IDataReader rdr = cmd.ExecuteReader();
while rdr.Read()
{
    wrt.Write(rdr[0].ToString() + "|" + rdr[1].ToString(); // change for your manipulation of the columns
}
}}}