How to import /export database data from /to csv/XML/excel using ASP.NET and C#.

Introduction

In this post, I am going to explain how to import / export database data from / to XML using ASP.NET and C#.
Import/export data from/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.
Part 2 : Steps for import / export database data from/to XML file.

Part 3 : Steps for import / export database data from/to EXCEL file.

Steps :

Steps for import / export database data from/to XML 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

IMAGE 1

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 from/to XML.</h3>
    <div>
        <table>
            <tr>
                <td>Select File : </td>
                <td>
                    <asp:FileUpload ID="FileUpload1" runat="server" />
                    </td>
                <td>
                    <asp:Button ID="btnImport" runat="server" Text="Import Data" OnClick="btnImport_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">
                        Data not 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="btnExport" runat="server" Text="Export Data" OnClick="btnExport_Click" />
        </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)
            {
                PopulateData();
                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 XML to database

Write below code into button click event for import Data from XML to database.

            protected void btnImport_Click(object sender, EventArgs e)
        {
            
            if (FileUpload1.PostedFile.ContentType == "application/xml" || FileUpload1.PostedFile.ContentType == "text/xml")
            {
                try
                {
                    string fileName = Path.Combine(Server.MapPath("~/UploadDocuments"), Guid.NewGuid().ToString() + ".xml");
                    FileUpload1.PostedFile.SaveAs(fileName);
 
                    XDocument xDoc = XDocument.Load(fileName);
                    List<EmployeeMaster> emList = xDoc.Descendants("Employee").Select(d =>
                        new EmployeeMaster
                        {
                             EmployeeID = d.Element("EmployeeID").Value,
                             CompanyName = d.Element("CompanyName").Value,
                             ContactName = d.Element("ContactName").Value,
                             ContactTitle = d.Element("ContactTitle").Value,
                             EmployeeAddress = d.Element("EmployeeAddress").Value,
                             PostalCode = d.Element("PostalCode").Value
                        }).ToList();
 
                    // Update Data Here
                    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 update data
                    PopulateData();
                    lblMessage.Text = "Import Done successfully!";
                }
                catch (Exception)
                {
                    
                    throw;
                }
            }
        }

Step-8: Write code for export Data from database to XML.

Write below code into button click event for export Data from database to XML.

         protected void btnExport_Click(object sender, EventArgs e)
        {
            using (MuDatabaseEntities dc = new MuDatabaseEntities())
            {
                List<EmployeeMaster> emList = dc.EmployeeMasters.ToList();
                if (emList.Count > 0)
                {
                    var xEle = new XElement("Employees",
                        from emp in emList
                        select new XElement("Employee",
                            new XElement("EmployeeID", emp.EmployeeID),
                            new XElement("CompanyName", emp.CompanyName),
                            new XElement("ContactName", emp.ContactName),
                            new XElement("ContactTitle", emp.ContactTitle),
                            new XElement("EmployeeAddress", emp.EmployeeAddress),
                            new XElement("PostalCode", emp.PostalCode)
                            ));
                    HttpContext context = HttpContext.Current;
                    context.Response.Write(xEle);
                    context.Response.ContentType = "application/xml";
                    context.Response.AppendHeader("Content-Disposition", "attachment; filename=EmployeeData.xml");
                    context.Response.End();
                }
            }
        }

Step-9: Run Application.

Related Post: