Export data from SQL Server to Text file in C# (Saving to a specific folder)

Question

The data will be first fetched into a DataTable and then the DataTable will be exported to a Text file which can be viewed in Notepad.

But, i dont know how to make the code work for save the work to a specific folder

P.S.I want to give to the file a dynamic name too (YEARmonthDAYhour.txt)

this is my code so far:

        protected void ExportTextFile(object sender, EventArgs e)
    {
        string constr = ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("Select * from details"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        string txt = string.Empty;
                        txt += "#";
                        foreach (DataRow row in dt.Rows)
                        {
                            foreach (DataColumn column in dt.Columns)
                            {
                                txt += row[column.ColumnName].ToString() + "$";
                            }
                        }
                        txt += "%";
                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition", "attachment;filename=AAAAMM-aaaammddhhmmss.txt");
                        Response.Charset = "";
                        Response.ContentType = "application/text";
                        Response.Output.Write(txt);
                        Response.Flush();
                        Response.End();
                    }


                }
            }
        }
    }

expected output:

'#InfofromSQL$InfofromSQl$InfofromSQL$...%' (without " ' ")

data separated by $.

Does it need to be a text file? If not, I have a CSV solution (CSV files can be opened in both notepad and excel). CSV is also easier to read. — Paul
– Paul, Commented Jan 18, 2016 at 11:36
expected output: '#InfofromSQL$InfofromSQl$InfofromSQL$...%' (without " ' ") data separated by $. — Bigby
– Bigby, Commented Jan 18, 2016 at 13:00

live-love · Accepted Answer · 2018-04-01 18:48:29Z

    static string connString = @"Server=myServerName;Database=myDbName;Trusted_Connection=True;";
    static string fileName = @"C:\CODE\myfile.txt";

    public static void WriteFile(string fileName)
    {
        SqlCommand comm = new SqlCommand();
        comm.Connection = new SqlConnection(connString);
        String sql = @"select col1, col2 from myTable";

        comm.CommandText = sql;
        comm.Connection.Open();

        SqlDataReader sqlReader = comm.ExecuteReader();

        // Change the Encoding to what you need here (UTF8, Unicode, etc)
        using (System.IO.StreamWriter writer = new System.IO.StreamWriter(fileName, false, Encoding.UTF8))
        {
            while (sqlReader.Read())
            {
                writer.WriteLine(sqlReader["col1"] + "\t" + sqlReader["col2"]);
            }
        }

        sqlReader.Close();
        comm.Connection.Close();
    }

Bigby · Accepted Answer · 2016-01-18 14:48:23Z

1

I did it finally:

StreamWriter file = new StreamWriter(@"C:\test");
                        file.WriteLine(txt.ToString());
                        file.Close();

Instead using response. , this works like a charm.

answered Jan 18, 2016 at 14:48

Bigby

1392 gold badges3 silver badges11 bronze badges

1 Comment

Jack Andersen Over a year ago

Small advice, remember to seperate your logic, in case you're gonna need a different target fx a network stream, memory buffer etc..

Collectives™ on Stack Overflow

Export data from SQL Server to Text file in C# (Saving to a specific folder)

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related