从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());
        }
    }
}

您会希望将一些资源看看：

Some resources you will want to look into:

• SqlConnection类; http://msdn.microsoft.com/en -us /库/ 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屋！