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

Suggested Videos:
Part 3 - How to bind a computed column to GridView
Part 4 - Subtract the largest even number and smallest odd number in the given array elements
Part 5 - Program to check if the user input is a string or an integer



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.