C#

Friday, February 21, 2014

How to Upload CSV File Data into Database Table (Employee) and Display Data in Asp.net Grid view

In Previous tutorial
Export Asp.net Grid view Data In to CSV File: Using Asp.net, C#, Entity Data Model
For That  I Create a Employee table and Added That Table to Ado.Net EntityDataModel
Now, I will Explain

How to Upload CSV File Data into Database Table (Employee) and Display Data in Asp.net Grid view

Step:1 

Add Web form in visual studio project name as Test.aspx:      
<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="Id" DataField="Id" />
                        <asp:BoundField HeaderText="EmployeeName" DataField="EmployeeName" />
                        <asp:BoundField HeaderText="Salary" DataField="Salary" />                      
                    </Columns>
                </asp:GridView>
                <br />
               
            </div>
        </div>

Step:2

Note:
Add UploadDocuments folder to solution

Step:3

In Code Behind Write Following Code:
Test.aspx.cs:
In Page Load:
protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                populateData();
                lblMessage.Text = "Current Database Data.";
            }
        }
Implement a Method populateData():

        private void populateData()
        {
            using (TestEntities dc = new TestEntities())
            {
                gvData.DataSource = dc.Employees.ToList();
                gvData.DataBind();
            }
        }

 Step:4


Write Code For ImportData to data base Button Click Event:
        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<Employee> emList = new List<Employee>();
                    foreach (var line in Lines)
                    {
                        Fields = line.Split(new char[] { ',' });
                        emList.Add(
                            new Employee
                            {
                                Id = Convert.ToInt32(Fields[0].Replace("\"", "")), // removed ""
                                EmployeeName = Fields[1].Replace("\"", ""),
                                Salary = Convert.ToDecimal(Fields[2].Replace("\"", "")),                              
                            });
                    }

                    // Update database data
                    using (TestEntities dc = new TestEntities())
                    {
                        foreach (var i in emList)
                        {
                            var v = dc.Employees.Where(a => a.Id.Equals(i.Id)).FirstOrDefault();
                            if (v != null)
                            {
                                v.Id = i.Id;
                                v.EmployeeName = i.EmployeeName;
                                v.Salary = i.Salary;
                            }
                            else
                            {
                                dc.Employees.AddObject(i);
                            }
                        }

                        dc.SaveChanges();

                        // populate updated data
                        populateDatabaseData();
                        lblMessage.Text = "Successfully Done. Now upto data is following.....";
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }

 Note: This Code Tested Several Time Working Perfect Go throw Above Code.If Any Queries Post Below