Как экспортировать большую таблицу SQL Server в файл CSV с помощью библиотеки FileHelpers?
Я ищу, чтобы экспортировать большую таблицу SQL Server в файл CSV, используя C# и библиотеку FileHelpers. Я мог бы также рассмотреть C# и bcp, но я думал, что FileHelpers будет более гибким, чем bcp. Скорость не является особым требованием.OutOfMemoryException брошен на storage.ExtractRecords() когда приведенный ниже код выполняется (какой-то менее важный код был опущен):
SqlServerStorage storage = new SqlServerStorage(typeof(Order));
storage.ServerName = "SqlServer";
storage.DatabaseName = "SqlDataBase";
storage.SelectSql = "select * from Orders";
storage.FillRecordCallback = new FillRecordHandler(FillRecordOrder);
Order[] output = null;
output = storage.ExtractRecords() as Order[];
Когда приведенный ниже код запускается, время ожидания истекло link.ExtractToFile():
SqlServerStorage storage = new SqlServerStorage(typeof(Order));
string sqlConnectionString = "Server=SqlServer;Database=SqlDataBase;Trusted_Connection=True";
storage.ConnectionString = sqlConnectionString;
storage.SelectSql = "select * from Orders";
storage.FillRecordCallback = new FillRecordHandler(FillRecordOrder);
FileDataLink link = new FileDataLink(storage);
link.FileHelperEngine.HeaderText = headerLine;
link.ExtractToFile("file.csv");
Выполнение SQL-запроса занимает больше 30 секунд по умолчанию и, следовательно, исключение тайм-аута. К сожалению, я не могу найти в документации FileHelpers, как установить более высокое значение времени ожидания для команды SQL.
Я мог бы рассмотреть цикл выбора SQL для небольших наборов данных, пока вся таблица не будет экспортирована, но процедура будет слишком сложной. Есть ли простой способ использовать FileHelpers для экспорта больших таблиц БД?
5 ответов
FileHelpers имеет асинхронный движок, который лучше подходит для обработки больших файлов. К сожалению, FileDataLink класс не использует его, поэтому нет простого способа использовать его с SqlStorage,
Также не очень легко изменить время ожидания SQL. Самый простой способ - скопировать код для SqlServerStorage создать свой собственный альтернативный поставщик услуг хранения и обеспечить замену ExecuteAndClose() а также ExecuteAndLeaveOpen() который установил тайм-аут на IDbCommand, (SqlServerStorage это закрытый класс, поэтому вы не можете просто создать его подкласс).
Возможно, вы захотите проверить ReactiveETL, который использует асинхронный движок FileHelpers для обработки файлов, а также переписать Ayende RhinoETL с использованием ReactiveExtensions для обработки больших наборов данных.
Ответ Рей Сиван на правильном пути, так как он будет хорошо масштабироваться с большими файлами, потому что он избегает чтения всей таблицы в память. Тем не менее, код может быть очищен.
Решение shamp00 требует внешних библиотек.
Вот простой экспортер таблиц в CSV-файлы, который хорошо масштабируется для больших файлов и не требует каких-либо внешних библиотек:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
public class TableDumper
{
public void DumpTableToFile(SqlConnection connection, string tableName, string destinationFile)
{
using (var command = new SqlCommand("select * from " + tableName, connection))
using (var reader = command.ExecuteReader())
using (var outFile = File.CreateText(destinationFile))
{
string[] columnNames = GetColumnNames(reader).ToArray();
int numFields = columnNames.Length;
outFile.WriteLine(string.Join(",", columnNames));
if (reader.HasRows)
{
while (reader.Read())
{
string[] columnValues =
Enumerable.Range(0, numFields)
.Select(i => reader.GetValue(i).ToString())
.Select(field => string.Concat("\"", field.Replace("\"", "\"\""), "\""))
.ToArray();
outFile.WriteLine(string.Join(",", columnValues));
}
}
}
}
private IEnumerable<string> GetColumnNames(IDataReader reader)
{
foreach (DataRow row in reader.GetSchemaTable().Rows)
{
yield return (string)row["ColumnName"];
}
}
}
Я написал этот код и объявил его CC0 (публичное достояние).
Я включаю 2 Код выше. Я использую этот код. Я использую VS 2010.
//this is all lib that i used|||||||||||||||
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using UsbLibrary;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Globalization;
//cocy in a button||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
SqlConnection _connection = new SqlConnection();
SqlDataAdapter _dataAdapter = new SqlDataAdapter();
SqlCommand _command = new SqlCommand();
DataTable _dataTable = new DataTable();
_connection = new SqlConnection();
_dataAdapter = new SqlDataAdapter();
_command = new SqlCommand();
_dataTable = new DataTable();
//dbk is my database name that you can change it to your database name
_connection.ConnectionString = "Data Source=.;Initial Catalog=dbk;Integrated Security=True";
_connection.Open();
SaveFileDialog saveFileDialogCSV = new SaveFileDialog();
saveFileDialogCSV.InitialDirectory = Application.ExecutablePath.ToString();
saveFileDialogCSV.Filter = "CSV files (*.csv)|*.csv|All files (*.*)|*.*";
saveFileDialogCSV.FilterIndex = 1;
saveFileDialogCSV.RestoreDirectory = true;
string path_csv="";
if (saveFileDialogCSV.ShowDialog() == DialogResult.OK)
{
// Runs the export operation if the given filenam is valid.
path_csv= saveFileDialogCSV.FileName.ToString();
}
DumpTableToFile(_connection, "tbl_trmc", path_csv);
}
//end of code in button|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
public void DumpTableToFile(SqlConnection connection, string tableName, string destinationFile)
{
using (var command = new SqlCommand("select * from " + tableName, connection))
using (var reader = command.ExecuteReader())
using (var outFile = System.IO.File.CreateText(destinationFile))
{
string[] columnNames = GetColumnNames(reader).ToArray();
int numFields = columnNames.Length;
outFile.WriteLine(string.Join(",", columnNames));
if (reader.HasRows)
{
while (reader.Read())
{
string[] columnValues =
Enumerable.Range(0, numFields)
.Select(i => reader.GetValue(i).ToString())
.Select(field => string.Concat("\"", field.Replace("\"", "\"\""), "\""))
.ToArray();
outFile.WriteLine(string.Join(",", columnValues));
}
}
}
}
private IEnumerable<string> GetColumnNames(IDataReader reader)
{
foreach (DataRow row in reader.GetSchemaTable().Rows)
{
yield return (string)row["ColumnName"];
}
}
Попробуй это:
private void exportToCSV()
{
//Asks the filenam with a SaveFileDialog control.
SaveFileDialog saveFileDialogCSV = new SaveFileDialog();
saveFileDialogCSV.InitialDirectory = Application.ExecutablePath.ToString();
saveFileDialogCSV.Filter = "CSV files (*.csv)|*.csv|All files (*.*)|*.*";
saveFileDialogCSV.FilterIndex = 1;
saveFileDialogCSV.RestoreDirectory = true;
if (saveFileDialogCSV.ShowDialog() == DialogResult.OK)
{
// Runs the export operation if the given filenam is valid.
exportToCSVfile(saveFileDialogCSV.FileName.ToString());
}
}
* Exports data to the CSV file.
*/
private void exportToCSVfile(string fileOut)
{
// Connects to the database, and makes the select command.
string sqlQuery = "select * from dbo." + this.lbxTables.SelectedItem.ToString();
SqlCommand command = new SqlCommand(sqlQuery, objConnDB_Auto);
// Creates a SqlDataReader instance to read data from the table.
SqlDataReader dr = command.ExecuteReader();
// Retrives the schema of the table.
DataTable dtSchema = dr.GetSchemaTable();
// Creates the CSV file as a stream, using the given encoding.
StreamWriter sw = new StreamWriter(fileOut, false, this.encodingCSV);
string strRow; // represents a full row
// Writes the column headers if the user previously asked that.
if (this.chkFirstRowColumnNames.Checked)
{
sw.WriteLine(columnNames(dtSchema, this.separator));
}
// Reads the rows one by one from the SqlDataReader
// transfers them to a string with the given separator character and
// writes it to the file.
while (dr.Read())
{
strRow = "";
for (int i = 0; i < dr.FieldCount; i++)
{
switch (Convert.ToString(dr.GetFieldType(i)))
{
case "System.Int16":
strRow += Convert.ToString(dr.GetInt16(i));
break;
case "System.Int32" :
strRow += Convert.ToString(dr.GetInt32(i));
break;
case "System.Int64":
strRow += Convert.ToString(dr.GetInt64(i));
break;
case "System.Decimal":
strRow += Convert.ToString(dr.GetDecimal(i));
break;
case "System.Double":
strRow += Convert.ToString(dr.GetDouble(i));
break;
case "System.Float":
strRow += Convert.ToString(dr.GetFloat(i));
break;
case "System.Guid":
strRow += Convert.ToString(dr.GetGuid(i));
break;
case "System.String":
strRow += dr.GetString(i);
break;
case "System.Boolean":
strRow += Convert.ToString(dr.GetBoolean(i));
break;
case "System.DateTime":
strRow += Convert.ToString(dr.GetDateTime(i));
break;
}
if (i < dr.FieldCount - 1)
{
strRow += this.separator;
}
}
sw.WriteLine(strRow);
}
// Closes the text stream and the database connenction.
sw.Close();
dr.Close();
// Notifies the user.
MessageBox.Show("ready");
}
Очень благодарен за ответ Джея Салливана - был очень полезен для меня.
Опираясь на это, я заметил, что в его решении строковое форматирование типов данных varbinary и string было не очень хорошим - поля varbinary получались бы буквально "System.Byte" или что-то подобное, в то время как поля даты и времени будут отформатированы MM/dd/yyyy hh:mm:ss tt, что не желательно для меня.
Ниже приведено мое взломанное решение, которое по-разному преобразуется в строку в зависимости от типа данных. Он использует вложенные троичные операторы, но работает!
Надеюсь, это будет полезно для кого-то.
public static void DumpTableToFile(SqlConnection connection, Dictionary<string, string> cArgs)
{
string query = "SELECT ";
string z = "";
if (cArgs.TryGetValue("top_count", out z))
{
query += string.Format("TOP {0} ", z);
}
query += string.Format("* FROM {0} (NOLOCK) ", cArgs["table"]);
string lower_bound = "", upper_bound = "", column_name = "";
if (cArgs.TryGetValue("lower_bound", out lower_bound) && cArgs.TryGetValue("column_name", out column_name))
{
query += string.Format("WHERE {0} >= {1} ", column_name, lower_bound);
if (cArgs.TryGetValue("upper_bound", out upper_bound))
{
query += string.Format("AND {0} < {1} ", column_name, upper_bound);
}
}
Console.WriteLine(query);
Console.WriteLine("");
using (var command = new SqlCommand(query, connection))
using (var reader = command.ExecuteReader())
using (var outFile = File.CreateText(cArgs["out_file"]))
{
string[] columnNames = GetColumnNames(reader).ToArray();
int numFields = columnNames.Length;
Console.WriteLine(string.Join(",", columnNames));
Console.WriteLine("");
if (reader.HasRows)
{
Type datetime_type = Type.GetType("System.DateTime");
Type byte_arr_type = Type.GetType("System.Byte[]");
string format = "yyyy-MM-dd HH:mm:ss.fff";
int ii = 0;
while (reader.Read())
{
ii += 1;
string[] columnValues =
Enumerable.Range(0, numFields)
.Select(i => reader.GetValue(i).GetType()==datetime_type?((DateTime) reader.GetValue(i)).ToString(format):(reader.GetValue(i).GetType() == byte_arr_type? String.Concat(Array.ConvertAll((byte[]) reader.GetValue(i), x => x.ToString("X2"))) :reader.GetValue(i).ToString()))
///.Select(field => string.Concat("\"", field.Replace("\"", "\"\""), "\""))
.Select(field => field.Replace("\t", " "))
.ToArray();
outFile.WriteLine(string.Join("\t", columnValues));
if (ii % 100000 == 0)
{
Console.WriteLine("row {0}", ii);
}
}
}
}
}
public static IEnumerable<string> GetColumnNames(IDataReader reader)
{
foreach (DataRow row in reader.GetSchemaTable().Rows)
{
yield return (string)row["ColumnName"];
}
}