Exporting all table in SQL Server to .text using C#

Question

I have an application that trying to extract all data in different table with 1 Database. First, I stored all the query in a .txt file to retrieve the table name and stored it in List.

[Here's my .txt file]

string script = File.ReadAllText(@"D:\Schooldb\School.txt");

            List strings = new List();
            strings.Add(script);
            

            using (SqlConnection connection = new SqlConnection(constring))
            {
                foreach (string x in strings)
                {
                        using (SqlCommand cmd = new SqlCommand(x, connection))
                        {
                            using (SqlDataAdapter adapter = new SqlDataAdapter())
                            {
                                cmd.Connection = connection;
                                adapter.SelectCommand = cmd;
                                using (DataTable dt = new DataTable())
                                {
                                    adapter.Fill(dt);

                                    string txt = string.Empty;

                                    foreach (DataColumn column in dt.Columns)
                                    {
                                        //Add the Header row for Text file.
                                        txt += column.ColumnName + "		";
                                    }

                                    //Add new line after Column Name.
                                    txt += "
";

                                    foreach (DataRow row in dt.Rows)
                                    {
                                        foreach (DataColumn column in dt.Columns)
                                        {
                                            //Add the Data rows.
                                            txt += row[column.ColumnName].ToString() + "***";
                                        }

                                        //Add new line.
                                        txt += "
";
                                    }
                                    int y = 0;
                                    StreamWriter file = new StreamWriter($@"D:\SchoolOutput\{x}_{DateTime.Now.ToString("yyyyMMdd")}.txt");
                                    file.WriteLine(txt.ToString());
                                    file.Close();
                                    y++;
                                }
                            }
                        }
                }

Expected:

teachers_datetoday
students_datetoday
subjects_datetoday

But reality my output is just

datetoday txt

Can someone tell me, where part did I go wrong?

Thanks in advance!

user8nf1 · Answer

There are other approaches for extracting data directly using SSMS.

In this case, your code reads the entire text as a single string, and the for loop runs only once.

Instead of reading the entire file as a string, you can have each line as one command and read the commands like the following.

foreach (string line in System.IO.File.ReadLines(@"D:\Schooldb\School.txt"))
{  
    //Each line contains one command
    //Write your logic here

}

Exporting all table in SQL Server to .text using C#

1 Answers