Export data from SQL and write to etxt file (No BCP or SP can be used)

So I'm looking for a开发者_如何学运维n 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.

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 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.

This is no different in C# than in any other language with the tools you need.

1) Query DB and store data in collection
2) Write collection to file in CSV format

Are you doing this in a Windows Form application? If you have bound data to a control such as a DataGridView this is pretty easy to do. You can just loop through the control and write the file that way. I like this because if you implemented filtering mechanisms in your application, whatever a user filtered can be written to the file. This is how I have done this before. You should be able to tweak this if you are using some sort of collection without much trouble.

private void exportCsv()
    {
        SaveFileDialog saveFile = new SaveFileDialog();
        createSaveDialog(saveFile, ".csv", "CSV (*csv)|*.csv)");
        TextWriter writer = new StreamWriter(saveFile.FileName);
        int row = dataGridView1.Rows.Count;
        int col = dataGridView1.Columns.Count;

        try
        {
            if (saveFile.FileName != "")
            {
                for (int i = 0; i < dataGridView1.Columns.Count; i++)
                {
                    writer.Write(dataGridView1.Columns[i].HeaderText + ",");
                }

                writer.Write("\r\n");
                for (int j = 0; j < row - 1; j++)
                {
                    for (int k = 0; k < col - 1; k++)
                    {
                        writer.Write(dataGridView1.Rows[j].Cells[k].Value.ToString() + ",");
                    }
                    writer.Write("\r\n");
                }
            }
            MessageBox.Show("File Sucessfully Created!", "File Saved", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }
        catch
        {
            MessageBox.Show("File could not be created.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        finally
        {
            writer.Close();
            saveFile.Dispose();
        }

    }