News
* IBM to offer mobile security as a service IBM will start delivering mobile security-as-a-service (MaaS) through its own cloud infrastructure in India, a move that is expected to better address in-country data requirements... IBM Spectrum Computing: IBM has enlarged its portfolio of software-defined infrastructure solutions with cognitive features for workload management.  * LiFi internet technology has been introduced, the new type of wireless internet connection that gives 100 times faster than traditional WiFi -- Invented by Professor Harald Haas from the University of Edinburgh. * Sci. Rachid Yazami has developed a smart chip that charges smartphones in less than 10 minutes. BenQ has launched BlueCore projector - Consumer electronics major BenQ has launched its first BlueCore laser light source projector. For those unaware, devices with BlueCore laser technology have a high contrast output of 80000:1 with an extended lamp life and efficiency. *** 
  Dec 14 2014 8:24AM     Michael Ryan
  0 Comments    3382 Views  
Here Michael Ryan provided a short tutorial with example, how to create CSV file from Database in C#/VB.
Database
Consider the structure of Table as shown below,
Table Structure
Set the table name as shown below.
Page Design
Image belows shows page design for Creating CSV file.
HTML
Below is the sample HTML code,
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h2>
            CSV CREATION (EMPLOYEE DETAILS)</h2>
        <hr >
        <br >
        <div>
            <span>Enter Employee ID: </span>
            <asp:TextBox ID="txtEmpID" runat="server"></asp:TextBox>&nbsp;&nbsp;<asp:Button ID="btnCreateCSV"
                runat="server" Text="Create CSV" OnClick="btnCreateCSV_Click" />

        </div>
        <br >
        <div>
            <asp:Label ID="lblMessage" runat="server"></asp:Label></div>
    </div>
    </form>
</body>
</html>
Namespaces
You will need to import the following namespace.
C#

using System.Data.SqlClient;
using System.Data;
using System.Text;
using System.IO;

VB

Imports System.Data.SqlClient
Imports System.Data
Imports System.Text
Imports System.IO

Creating CSV from Database
Below is the sample code for creating CSV from Database in C#/VB.
GetData() method will get Employee data from Database and return it as Datatable.
For CreateCSV() method takes the parameter Datatable and Path to save CSV file.
C#

public DataTable GetData()
{
            string conString = " server=YourServerName; database=DBName; uid=**;   password=***;";
            SqlConnection conn = new SqlConnection(conString);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM Employee", conn);
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds);                
            }
            catch (Exception ex)
            {
                //Exception Message
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return ds.Tables[0];
}

VB

Public Function GetData() As DataTable

            Dim conString As String =  " server=YourServerName; database=DBName; uid=**;   password=***;" 
            Dim conn  SqlConnection =  New (conString) 
            Dim ds  DataSet =  New () 
            Try
                conn.Open()
                Dim cmd  SqlCommand =  New ("SELECT * FROM Employee",conn) 
                Dim da  SqlDataAdapter =  New () 
                da.SelectCommand = cmd
                da.Fill(ds)
            Catch ex As Exception
                'Exception Message
            Finally
                conn.Close()
                conn.Dispose()
            End Try
            Return ds.Tables(0)

End Function

Code for Create CSV
C#

protected void CreateCSV(DataTable dt, string filePath)
{
            System.Data.DataView view = new System.Data.DataView(dt);
            dt = view.ToTable(false"EmployeeName","EmployeeDesc","EmployeeAddress","EmployeeExperience");

            //DataTable to StringBuilder
            StringBuilder sb = new StringBuilder();

            string[] columnNames = dt.Columns.Cast<DataColumn>().
                                              Select(column => column.ColumnName).
                                              ToArray();
            sb.AppendLine(string.Join(",", columnNames));
            foreach (DataRow row in dt.Rows)
            {
                string[] fields = row.ItemArray.Select(field => field.ToString()).ToArray();
                sb.AppendLine(string.Join(",", fields));
            }

            //Passing StringBuilder to Create CSV
            var bytes = Encoding.GetEncoding("iso-8859-1").GetBytes(sb.ToString());
            MemoryStream stream = new MemoryStream(bytes);

            string filename = "Employee.csv";
            
            string fileLocation = filePath + filename;
            using (var fileStream = new FileStream(fileLocation, FileMode.Create, FileAccess.Write))
            {
                stream.CopyTo(fileStream);
            }
}

VB

Protected  Sub CreateCSV(ByVal dt As DataTable, ByVal filePath As String)
            Dim view As System.Data.DataView =  New System.Data.DataView(dt) 
            dt = view.ToTable(False"EmployeeName","EmployeeDesc","EmployeeAddress","EmployeeExperience")
 
            'DataTable to StringBuilder
            Dim sb  StringBuilder =  New () 
 
            String() columnNames = dt.Columns.Cast<DataColumn>().
                                              Select(column => column.ColumnName).
                                              ToArray()
            sb.AppendLine(String.Join(",", columnNames))
            Dim row As DataRow
            For Each row In dt.Rows
                Dim fields() As String =  row.ItemArray.Select(field  = > field.ToString()).ToArray() 
                sb.AppendLine(String.Join(",", fields))
            Next
 
            'Passing StringBuilder to Create CSV
            Dim bytes As var =  Encoding.GetEncoding("iso-8859-1").GetBytes(sb.ToString()) 
            Dim stream  MemoryStream =  New (bytes) 
 
            Dim filename As String =  "Employee.csv" 
 
            Dim fileLocation As String =  filePath + filename 
            Imports ( fileStream = New (fileLocation, FileMode.Create, FileAccess.Write))

                stream.CopyTo(fileStream)

End Sub

Code for "Create CSV" Button Click Event
C#

protected void btnCreateCSV_Click(object sender, EventArgs e)
{
            int EmpID = int.Parse(txtEmpID.Text);
            DataTable dtEmployee = GetData(EmpID);
            CreateCSV(dtEmployee, "D:/");
}

VB


Protected  Sub btnCreateCSV_Click(ByVal sender As ObjectByVal e As EventArgs)

            Dim EmpID As Integer =  Integer.Parse(txtEmpID.Text) 
            Dim dtEmployee As DataTable =  GetData(EmpID) 
            CreateCSV(dtEmployee, "D:/")

End Sub

Result
Finally, the Employee.csv will be stored on the filepath(D:/Employee.csv)
I hope this page will helps to create CSV from Database in ASP.Net application. Thanks.
BackToTop
Comments



 
Search
Recent Posts
Create Amazon ElasticCache Using Memcached in CSharp
Oct 29 2018 12:09PM Posted By Amose
Get Google Map Lat Lng (Geo Point) By Pincode in C#
Oct 28 2018 12:09PM Posted By Pranav
Google URL Shortener in C#
Oct 20 2018 12:09PM Posted By Sanjay
Bind Gridview from CSV file in Asp.Net C#
Oct 5 2018 12:09PM Posted By Michael
Call WebService method from jQuery in every 1 minute
Sep 26 2018 12:09PM Posted By John
Gridview custom CSS in ASP.Net
Sep 14 2018 12:09PM Posted By Micheal Ryan
Read excel file and bind to Gridview in C#
Sep 10 2018 12:09PM Posted By Micheal
Tags
Follow us on Facebook
Follow us on Google +
Recent post in your Email inbox.
Enter your email address: