Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Part 6 - Export data from sql server database tables to csv file using 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
export data to csv from sql server

export data from sql server to csv

End user should be able to select the format to export from the dropdownlist as shown below.
export data from sql server to comma delimited file

After exporting, the comma delimited data in the notepad should look as shown below
export data from sql server to csv c#

After exporting, the pipe delimited data in the notepad should look as shown below
export data from sql server to pipe delimited file

Step 1 : Create the tables (Departments & Employees
Create Table Departments
     ID int primary key,
     Name nvarchar(50),
     Location nvarchar(50)

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

Create Table Employees
     ID int primary key,
     Name nvarchar(50),
     DepartmentID int foreign key references Departments(ID)

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)

Step 2: Create a new empty 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 Text="PIPE DELIMITED" Value="PIPE DELIMITED">
    <asp:Button ID="btnExport" runat="server" Text="Export"
    OnClick="btnExport_Click" />

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();

                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);
                    foreach (DataRow employeeDR in ds.Tables["Employees"]
                        .Select("DepartmentId = '" + departmentId.ToString() + "'"))
                        sb.Append(employeeDR["ID"].ToString() + strDelimiter);
                        sb.Append(employeeDR["Name"].ToString() + strDelimiter);

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

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

Step 4 : Include the connection string in web.config file
  <add name="DBCS"
        connectionString="server=.;database=Sample;integrated security = SSPI"/>

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

Run the application and test it.

dot net written test questions answers

1 comment:

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.