从SQL导出数据并写入etxt文件（不能使用BCP或SP） [英] Export data from SQL and write to etxt file (No BCP or SP can be used)

问题描述

所以我正在寻找一种从SQL Server 2000数据库导出数据的简单方法，并将其写入逗号分隔的文本文件。它的一张桌子只有大约1000行。我是C＃的新手，请原谅我，如果这是一个愚蠢的问题。

So I'm looking for an easy way to export data from a SQL Server 2000 database and write it to a comma delimited text file. Its one table and only about 1,000 rows. I'm new to C# so please excuse me if this is a stupid question.

推荐答案

这是一个非常简单的任务，但是您需要学习SqlClient命名空间和您拥有的不同对象。您将需要注意的是，对于SQL Server 2000和较低的异步方法不支持，所以它们将被阻止。

This is a very easy task, but you need to learn the SqlClient namespace and the different objects you have at your disposal. You will want to note though that for SQL Server 2000 and lower asynchronous methods are not supported, so they will be all blocking.

请注意，这是一个非常粗略的示例，我没有测试这个，但这是一般的方法。

Mind you this is a very sketchy example, and I did not test this, but this would be one general approach.

string connectionString = "<yourconnectionstringhere>";
using (SqlConnection connection = new SqlConnection(connectionString)) {
    try {
        connection.Open();
    }
    catch (System.Data.SqlClient.SqlException ex) {
        // handle
        return;
    }
    string selectCommandText = "SELECT * FROM <yourtable>";
    using (SqlDataAdapter adapter = new SqlDataAdapter(selectCommandText, connection)) {
        using (DataTable table = new DataTable("<yourtable>")) {
            adapter.Fill(table);
            StringBuilder commaDelimitedText = new StringBuilder();
            commaDelimitedText.AppendLine("col1,col2,col3"); // optional if you want column names in first row
            foreach (DataRow row in table.Rows) {
                string value = string.Format("{0},{1},{2}", row[0], row[1], row[2]); // how you format is up to you (spaces, tabs, delimiter, etc)
                commaDelimitedText.AppendLine(value);
            }
            File.WriteAllText("<pathhere>", commaDelimitedText.ToString());
        }
    }
}

查看：

• SqlConnection类; http：// msdn。 microsoft.com/en-us/library/system.data.sqlclient.sqlconnection(v=VS.90).aspx


• SqlDataAdapter类; http://msdn.microsoft.com/en- us / library / ds404w5w（v = VS.90）.aspx


• SqlDataAdapter.Fill方法; http://msdn.microsoft.com/en- us / library / 905keexk（v = VS.90）.aspx


• DataTable类; http://msdn.microsoft.com/en-us/library /system.data.datatable.aspx

• SqlConnection Class; http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection(v=VS.90).aspx
• SqlDataAdapter Class; http://msdn.microsoft.com/en-us/library/ds404w5w(v=VS.90).aspx
• SqlDataAdapter.Fill Method; http://msdn.microsoft.com/en-us/library/905keexk(v=VS.90).aspx
• DataTable Class; http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx

我也不知道你的要求是什么，或者你为什么要做这个任务，但也可能有很多工具可以为你做这个工作（如果这是一次性的事情），因为这不是一件不寻常的任务。

I'm also not sure what your requirements are, or why you have to do this task, but there are also probably quite a lot of tools out there that can already do this for you (if this is a one time thing), because this is not an uncommon task.

这篇关于从SQL导出数据并写入etxt文件（不能使用BCP或SP）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！