Export data from sql server database tables to csv file using asp.net and c#

In this video we will discuss exporting data from SQL Server to a notepad in the following 2 formats

1. Comma delimited format
2. Pipe delimited format 



The data from the following 2 tables (Departments & Employees) need to be exported
 

 

End user should be able to select the format to export from the dropdownlist as shown below. 
 

After exporting, the comma delimited data in the notepad should look as shown below 
 

After exporting, the pipe delimited data in the notepad should look as shown below 
 

Step 1 : Create the tables (Departments & Employees) 

Create Table Departments

(

     ID int primary key,

     Name nvarchar(50),

     Location nvarchar(50)

)

GO

Insert into Departments values (1, 'IT', 'New York')

Insert into Departments values (2, 'HR', 'London')

GO

Create Table Employees

(

     ID int primary key,

     Name nvarchar(50),

     DepartmentID int foreign key references Departments(ID)

)

GO

Insert into Employees values (1, 'Mike', 1)

Insert into Employees values (2, 'John', 1)

Insert into Employees values (3, 'Josh', 1)

Insert into Employees values (4, 'Mary', 2)

Insert into Employees values (5, 'Rosy', 2)

GO

Step 2: Create a new empty asp.net empty web application. Name it ExportData. Add WebForm1.aspx to the project. Copy and paste the following HTML in WebForm1.aspx.

<div style="font-family: Arial">

    Format :

    <asp:DropDownList ID="ddlExportFormat" runat="server">

        <asp:ListItem Text="COMMA DELIMITED" Value="COMMA DELIMITED">

        </asp:ListItem>

        <asp:ListItem Text="PIPE DELIMITED" Value="PIPE DELIMITED">

        </asp:ListItem>

    </asp:DropDownList>

    <asp:Button ID="btnExport" runat="server" Text="Export"

    OnClick="btnExport_Click" />

</div>

Step 3: Copy and paste the following code in WebForm1.aspx.cs

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Text;

namespace ExportData

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void btnExport_Click(object sender, EventArgs e)

        {

            string strDelimiter = ddlExportFormat.SelectedValue == "COMMA DELIMITED"

                                  ? "," : "|";

            string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

            StringBuilder sb = new StringBuilder();

            using (SqlConnection con = new SqlConnection(cs))

            {

                string strQuery = "SELECT [ID] ,[Name],[Location] FROM [Departments];";

                strQuery += "SELECT [ID],[Name],[DepartmentID] FROM [Employees];";

                SqlDataAdapter da = new SqlDataAdapter(strQuery, con);

                DataSet ds = new DataSet();

                da.Fill(ds);

                ds.Tables[0].TableName = "Departments";

                ds.Tables[1].TableName = "Employees";

                foreach (DataRow depratmentDR in ds.Tables["Departments"].Rows)

                {

                    int departmentId = Convert.ToInt32(depratmentDR["ID"]);

                    sb.Append(departmentId.ToString() + strDelimiter);

                    sb.Append(depratmentDR["Name"].ToString() + strDelimiter);

                    sb.Append(depratmentDR["Location"].ToString());

                    sb.Append("\r\n");

                    foreach (DataRow employeeDR in ds.Tables["Employees"]

                        .Select("DepartmentId = '" + departmentId.ToString() + "'"))

                    {

                        sb.Append(employeeDR["ID"].ToString() + strDelimiter);

                        sb.Append(employeeDR["Name"].ToString() + strDelimiter);

                        sb.Append(departmentId.ToString());

                        sb.Append("\r\n");

                    }

                }

            }

            string strFileName = strDelimiter == "," ? "Data.csv" : "Data.txt";

            StreamWriter file = new StreamWriter(@"C:\ExportedData\" + strFileName );

            file.WriteLine(sb.ToString());

            file.Close();

        }

    }

}

Step 4 : Include the connection string in web.config file

<connectionStrings>

  <add name="DBCS"

        connectionString="server=.;database=Sample;integrated security = SSPI"/>

</connectionStrings>

Step 5 : In C:\ drive, create a folder and name it ExportedData.

Run the application and test it.