Wednesday, July 24, 2013

Labels: ,

How to Import Data From Excel file to Database in Asp.net with C# - Example

Posted by Abhay Makadiya at 7/24/2013

Introduction:

Hello Friends, in previous post we learned HOW TO EXPORT DATA FROM GRID TO EXCEL SHEET. Now in this post we will learn How to Import data from Excel file to Gridview and store it to MS SQL Server or Database.


Many times we face a situation where we have readily available excel sheets having large data that needs to be imported in database.

In the following example First of all we upload an Excel Sheet from FileUpload Control then this records will be displayed in Gridview. when we press the Import Button all the records will we inserted into Database.

Here is my Excel Sheet:-


Database Table (SQL Server 2008).:-


Now we have table and excel file so first we will import data from excel sheet for that i have used System.Data.OleDb library now we create connection to Excel sheet and than check the extension of uploaded file because connection string for .xls and .xlsx are different.

Create OLE DB connection and command which is used to extract data from Excel File. we will store this data to data table then bind to gridview in final step will insert all the records of Gird into Database using SqlBulkCopy. 

SqlBulkCopy allows us to insert multiple records in Database Table in just few lines of code.

Here Is the Screen Shots:-

click on Brows Button and Choose Excel File.

Excel Sheet data Records are binded to Gridview. When you click "Store To Database" button all the records in gridview will be inserted to database table.

Source Code in Asp.net:-

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

</head>
<body>
    <form id="form1" runat="server">
        <center>
            <table>
                <tr>
                    <td class="auto-style2">
                        <asp:FileUpload ID="FileUpload1" runat="server" /></td>
                </tr>
                <tr>
                    <td class="auto-style5">
                        <asp:Button ID="btnUpload" runat="server" Height="30px"
                            Text="Upload" Width="92px" OnClick="btnUpload_Click" BackColor="#41B7D8" ForeColor="White" Font-Bold="true" BorderStyle="None" /></td>
                </tr>
                <tr>
                    <td class="auto-style4">
                        <asp:GridView ID="GridView1" runat="server" CellPadding="4" EnableModelValidation="True" ForeColor="#333333" GridLines="None" Height="177px" Width="217px">
                            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />

                            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                            <HeaderStyle BackColor="#41B7D8" Font-Bold="True" ForeColor="White" />
                            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" HorizontalAlign="Center" />
                        </asp:GridView>
                    </td>
                </tr>
                <tr>
                    <td class="auto-style6">
                        <asp:Button ID="btn_store" runat="server" Text="Store To Database" BackColor="#41B7D8" ForeColor="White" Font-Bold="true" OnClick="btn_store_Click" BorderStyle="None" Height="32px" /></td>
                </tr>
            </table>
        </center>
    </form>
</body>
</html>

Code Behind in C#:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        string connectionString = "";
        if (FileUpload1.HasFile)
        {
            DataTable dtExcelRecords = new DataTable();
            string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string fileLocation = Server.MapPath("~/App_Data/" + fileName);
            FileUpload1.SaveAs(fileLocation);

            //Check whether file extension is xls or xslx

            if (fileExtension == ".xls")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (fileExtension == ".xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }

            //Create OleDB Connection and OleDb Command

            OleDbConnection con = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = con;
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);

            con.Open();
            DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
            cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
            dAdapter.SelectCommand = cmd;
            dAdapter.Fill(dtExcelRecords);
            Session["sdt"] = dtExcelRecords;
            con.Close();
            GridView1.DataSource = dtExcelRecords;
            GridView1.DataBind();

        }
    }
    protected void btn_store_Click(object sender, EventArgs e)
    {
        String conStr = "Data Source=SQLDB;Initial Catalog=Demo;Persist Security Info=True;User ID=Demoh;Password=Demo1@";


        SqlConnection con = new SqlConnection(conStr);
        con.Open();
        SqlBulkCopy sqlBulk = new SqlBulkCopy(con);

        DataTable dt = new DataTable();
        dt = (DataTable)Session["sdt"];

        //Give your Destination table name

        sqlBulk.DestinationTableName = "aby_temp";
        
        //map the columns if you have differant no.of columns in Destination 

        sqlBulk.ColumnMappings.Add("fname", "fname");
        sqlBulk.ColumnMappings.Add("category","category");
        sqlBulk.WriteToServer(dt);

        con.Close();
    }
}


No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )