Export data from SQL Server database to Text file in using C#

Tutorials PanelPosted by

Below is an example of how to export data from SQL Server database table in ASP.NET using C#.

Let’s create a table in the SQL server database as shown below:

Design View

Table-Programming-Language-Design

Now add few records to that table:

Data View

Table-Programming-Language-Data-full

Now create a new Web Application using C#. Then add a button to the default web form as shown below:

Export to Text File

HTML

<asp:Button ID="btnExport" Text="Export to Text File" runat="server" OnClick="btnExport_Click" Width="223px" />

Now add the code below to the button click event:

C#

protected void btnExport_Click(object sender, EventArgs e)
        {
            string strConn = "Data Source=TutorialsPanel-PC\\SQLEXPRESS;Initial Catalog=TutorialsPanel;Integrated Security=True;Pooling=False";

            using (SqlConnection conn = new SqlConnection(strConn))

            {
                using (SqlCommand command = new SqlCommand("SELECT * FROM Employee Order by Name"))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter())
                    {
                        command.Connection = conn;
                        adapter.SelectCommand = command;
                        using (DataTable dtEmployee = new DataTable())
                        {
                            adapter.Fill(dtEmployee);
                            string str = string.Empty;
                            foreach (DataColumn column in dtEmployee.Columns)
                            {
                                // Add the header to the text file
                                str += column.ColumnName + "\t\t";
                            }
                            // Insert a new line.
                            str += "\r\n";

                            foreach (DataRow row in dtEmployee.Rows)
                            {
                                foreach (DataColumn column in dtEmployee.Columns)

                                {
                                    // Insert the Data rows.
                                    str += row[column.ColumnName].ToString() + "\t\t";
                                } 
                                // Insert a  new line.
                                str += "\r\n";
                            }
                            // Download the Text file.

                            Response.Clear();
                            Response.Buffer = true;
                            Response.AddHeader("content-disposition", "attachment;filename=ExportFromSQL.txt");
                            Response.Charset = "";
                            Response.ContentType = "application/text";
                            Response.Output.Write(str);
                            Response.Flush();
                            Response.End();
                        }
                    }
                }
            }
        }
    }

Run the application. Click on the button.