Introduction
In this post I am explain explain how to import / export database data from / to csv using ASP.NET and C#.Import/export data form/to some common format is very useful technique for transfer data between two other programs/ system on a different platform.
Part 1 : Steps for import / export database data from/to CSV file.
Steps :
Here I explain Part 1, import / export database data from/to CSV file.
Step - 1 : Create New Project.
Go to File > New > Project > Select asp.net web forms application > Entry Application Name > Click OK.Step-2: Add a Database.
Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.Step-3: Create table for import / export data.
Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.
In this example, I have used one tables as below
Step-4: Add Entity Data Model.
Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.
Step-5: Add a Webpage and Design for import, show and export data.
Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select web form/ web form using master page under Web > Enter page name > Add.HTML Code
<h3>Import / Export database data to / from csv/xml/excel format.</h3>
<div>
<h3>Import / Export data from csv.</h3>
<div>
<table>
<tr>
<td>Select File : </td>
<td>
<asp:FileUpload ID="FileUpload1" runat="server" /></td>
<td>
<asp:Button ID="btnImportFromCSV" runat="server" Text="Import Data to Database" OnClick="btnImportFromCSV_Click" />
</td>
</tr>
</table>
<div>
<br />
<asp:Label ID="lblMessage" runat="server" Font-Bold="true" />
<br />
<asp:GridView ID="gvData" runat="server" AutoGenerateColumns="false">
<EmptyDataTemplate>
<div style="padding:10px;">No Data Found!</div>
</EmptyDataTemplate>
<Columns>
<asp:BoundField HeaderText="Employee ID" DataField="EmployeeID" />
<asp:BoundField HeaderText="Company Name" DataField="CompanyName" />
<asp:BoundField HeaderText="Contact Name" DataField="ContactName" />
<asp:BoundField HeaderText="Contact Title" DataField="ContactTitle" />
<asp:BoundField HeaderText="Address" DataField="EmployeeAddress" />
<asp:BoundField HeaderText="Postal Code" DataField="PostalCode" />
</Columns>
</asp:GridView>
<br />
<asp:Button ID="btnExportToCSV" runat="server" Text="Export Data to CSV" OnClick="btnExportToCSV_Click" />
</div>
</div>
</div>
Step-6: Write code into page load event for show data.
Write below code into Page_Load event for show data from database.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
populateDatabaseData();
lblMessage.Text = "Current Database Data.";
}
}
And here is the functioin populateDatabaseData
private void populateDatabaseData()
{
using (MuDatabaseEntities dc = new MuDatabaseEntities())
{
gvData.DataSource = dc.EmployeeMasters.ToList();
gvData.DataBind();
}
}
Step-7: Write code for import Data from csv to database
Write below code into button click event for import Data from csv to database. protected void btnImportFromCSV_Click(object sender, EventArgs e)
{
if (FileUpload1.PostedFile.ContentType == "text/csv" || FileUpload1.PostedFile.ContentType == "application/vnd.ms-excel")
{
string fileName = Path.Combine(Server.MapPath("~/UploadDocuments"), Guid.NewGuid().ToString() + ".csv");
try
{
FileUpload1.PostedFile.SaveAs(fileName);
string[] Lines = File.ReadAllLines(fileName);
string[] Fields;
//Remove Header line
Lines = Lines.Skip(1).ToArray();
List<EmployeeMaster> emList = new List<EmployeeMaster>();
foreach (var line in Lines)
{
Fields = line.Split(new char[] { ',' });
emList.Add(
new EmployeeMaster
{
EmployeeID = Fields[0].Replace("\"",""), // removed ""
CompanyName = Fields[1].Replace("\"", ""),
ContactName = Fields[2].Replace("\"", ""),
ContactTitle = Fields[3].Replace("\"", ""),
EmployeeAddress = Fields[4].Replace("\"", ""),
PostalCode = Fields[5].Replace("\"", ""),
});
}
// Update database data
using (MuDatabaseEntities dc = new MuDatabaseEntities())
{
foreach (var i in emList)
{
var v = dc.EmployeeMasters.Where(a => a.EmployeeID.Equals(i.EmployeeID)).FirstOrDefault();
if (v != null)
{
v.EmployeeID = i.EmployeeID;
v.CompanyName = i.CompanyName;
v.ContactName = i.ContactName;
v.ContactTitle = i.ContactTitle;
v.EmployeeAddress = i.EmployeeAddress;
v.PostalCode = i.PostalCode;
}
else
{
dc.EmployeeMasters.Add(i);
}
}
dc.SaveChanges();
// populate updated data
populateDatabaseData();
lblMessage.Text = "Successfully Done. Now upto data is following.....";
}
}
catch (Exception)
{
throw;
}
}
}
Step-8: Write code for export Data from database to csv.
Write below code into button click event for export Data from database to csv. protected void btnExportToCSV_Click(object sender, EventArgs e)
{
List<EmployeeMaster> emList = new List<EmployeeMaster>();
using (MuDatabaseEntities dc = new MuDatabaseEntities())
{
emList = dc.EmployeeMasters.ToList();
}
if (emList.Count > 0)
{
string header = @"""Employee ID"",""Company Name"",""Contact Name"",""Contact Title"",""Employee Address"",""Postal Code""";
StringBuilder sb = new StringBuilder();
sb.AppendLine(header);
foreach (var i in emList)
{
sb.AppendLine(string.Join(",",
string.Format(@"""{0}""", i.EmployeeID),
string.Format(@"""{0}""", i.CompanyName),
string.Format(@"""{0}""", i.ContactName),
string.Format(@"""{0}""", i.ContactTitle),
string.Format(@"""{0}""", i.EmployeeAddress),
string.Format(@"""{0}""", i.PostalCode)));
}
// Download Here
HttpContext context = HttpContext.Current;
context.Response.Write(sb.ToString());
context.Response.ContentType = "text/csv";
context.Response.AddHeader("Content-Disposition", "attachment; filename=EmployeeData.csv");
context.Response.End();
}
else
{
lblMessage.Text = "Data not Found!";
}
}
