如何使用sql查询的输出创建csv文件?
问题描述:
如何在C#中使用sql(SQLServer)查询的输出创建csv文件?如何使用sql查询的输出创建csv文件?
这是到目前为止,我已经试过代码:
public static void CreateManifestFile(string SQLFileToExecute, string ConnectionString, StreamWriter logfile)
{
int success = 0;
List<string> results = new List<string>();
string script = File.ReadAllText(@SQLFileToExecute);
Logging.WriteToLog(" ", logfile);
Logging.WriteToLog(" ", logfile);
Logging.WriteToLog("=== Processing file: [" + SQLFileToExecute + "] ===", logfile);
// split script on GO command
IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
SqlConnection Connection = new SqlConnection(ConnectionString);
Connection.Open();
Connection.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
{
Logging.WriteToLog("Msg: " + e.Message, logfile);
success = 1;
};
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.Connection = Connection;
foreach (string commandString in commandStrings)
{
if (commandString.Trim() != "")
{
success = 0;
Console.WriteLine(commandString.ToString());
logfile.WriteLine(commandString.ToString());
results.Add(sqlcmd.ExecuteReader(commandString));
if (success == 0)
{
Logging.WriteToLog("Command executed successfully.", logfile);
}
}
}
Connection.Close();
int length = results.Count;
string delimter=",";
using(System.IO.TextWriter writer = File.CreateText("manifest.csv"))
{
Logging.WriteToLog("manifest count:" + length, logfile);
for(int index = 0; index < length; index++)
{
writer.WriteLine(string.Join(delimter,results[index]));
}
}
}
但我上线得到错误:
results.Add(sqlcmd.ExecuteReader(commandString));
错误:
错误1最佳重载方法匹配 'System.Collections.Generic.List.Add(string)'有一些无效的 参数
错误2参数1:不能从 'System.Data.SqlClient.SqlDataReader' 到 '字串'
错误3为 “System.Data.SqlClient.SqlCommand.ExecuteReader最好重载的方法匹配(转换的System.Data.CommandBehavior)” 有一些无效参数
错误4参数1:不能从转换 '字符串' 到 '的System.Data.CommandBehavior'
我FOL低于this post来做到这一点。
答
你还没有尝试过任何东西,所以这里有一点动机让你开始。
SqlServerStorage storage = new SqlServerStorage(typeof(Order));
string sqlConnectionString ="Server=SqlServer;Database=SqlDataBase;Trusted_onnection=True";
storage.ConnectionString = sqlConnectionString;
storage.SelectSql = "select * from Yourtable";
storage.FillRecordCallback = new FillRecordHandler(FillRecordOrder);
FileDataLink link = new FileDataLink(storage);
link.FileHelperEngine.HeaderText = headerLine;
link.ExtractToFile("file.csv");
这是给你包含大量数据的表的超时时间,但嘿,我不会比你懒惰。所以弄清楚。希望它有助于好运。
答
public string ExportToCSVFile(DataTable dtTable)
{
StringBuilder sbldr = new StringBuilder();
if (dtTable.Columns.Count != 0)
{
foreach (DataColumn col in dtTable.Columns)
{
sbldr.Append(col.ColumnName.Replace(",", "") + ',');
}
sbldr.Append("\r\n");
foreach (DataRow row in dtTable.Rows)
{
foreach (DataColumn column in dtTable.Columns)
{
sbldr.Append(row[column].ToString().Replace(",", "").Trim() + ',');
}
sbldr.Append("\r\n");
}
}
return sbldr.ToString();
}
你尝试过什么吗? – 2014-09-24 07:22:38
对不起,但'-1'为零努力。 – 2014-09-24 07:23:11
如果您有任何问题,请发布您的代码或您迄今为止所尝试的内容。 – Taosique 2014-09-24 07:23:32