Friday 26 October 2012

Insert Excel file data into database in asp.net


Introduction

Here I will explain how to Import or insert data into SQL database from Excel spreadsheet using Sqlbulkcopy method.

Description

 I have searched for so many posts that explain static manner some of the posts are not clearly some of the posts are not supporting for latest excel files so many problems i faced by using those examples I have done application that will support for all excel versions and it will work for you without having any problems and it will dynamically you can upload excel sheet from anywhere from your computer.

I want to copy this exeltable data into a SQL Server Database Table, called Excel_table, with the same schema.


Design your aspx page like this

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

<!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>
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
 
        <table class="style1">
            <tr>
                <td>
                    Table Name</td>
                <td>
                    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    Excel File Upload</td>
                <td>
                    <asp:FileUpload ID="FileUpload1" runat="server" />
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
                    <asp:Label ID="Label1" runat="server"></asp:Label>
                </td>
            </tr>
        </table>
 
    </div>
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
    </form>
</body>
</html>

After that write the following code in codebehind button click 

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

public partial class ExcelExample2 : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(@"Data                                      Source=.\SQLEXPRESS;AttachDbFilename=D:\WebSite1
                        \WebSite1\App_Data\EmployeeDatabase.mdf;Integrated
                                                 Security=True;User Instance=True");
    protected void Page_Load(object sender, EventArgs e)
    {
       // form_lolll();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            SqlDataAdapter da = new SqlDataAdapter(@"SELECT TABLE_NAME
                                                           FROM INFORMATION_SCHEMA.TABLES ", con);

            DataTable dt = new DataTable();

            da.Fill(dt);

            bool flag;

            if (dt.Rows.Count > 0)
            {
                string tableName_Txt = TextBox1.Text;

                flag = false;

                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    string tablename_DB = dt.Rows[j]["TABLE_NAME"].ToString();
                 
                    //compare database table and excel table
                    if (tableName_Txt == tablename_DB)
                    {
                        flag = true;
                        break;
                    }
                }
                if (flag)
                {
                    string connectionString = "";

                    if (FileUpload1.HasFile)
                    {
                        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 ;
                              HDR=Yes;IMEX=2\"";
                        }

                        //Create OleDB Connection and OleDb Command

                        OleDbConnection conn = new OleDbConnection(connectionString);

                        OleDbCommand cmd = new OleDbCommand();

                        cmd.CommandType = System.Data.CommandType.Text;

                        cmd.Connection = conn;

                        OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);

                        DataTable dt1 = new DataTable();

                        conn.Open();

                        DataTable dtExcelSheetName = conn.GetOleDbSchemaTable
                                                                                            (OleDbSchemaGuid.Tables, null);

                        string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();

                        cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";

                        dAdapter.SelectCommand = cmd;

                        dAdapter.Fill(dt1);

                        conn.Close();


                        SqlDataAdapter da1 = new SqlDataAdapter(@"SELECT  *  FROM  "
                                                                                                      + tableName_Txt + "  ", con);
                        DataTable dt2 = new DataTable();

                        da1.Fill(dt2);
                       
                        //compare database table colums count and excel file colums count
                        if (dt1.Columns.Count == dt2.Columns.Count)
                        {
                            flag = false;

                            for (int i = 0; i < dt1.Columns.Count; i++)
                            {
                                  //compare database table colums names and excel file colum names

                                if (dt1.Columns[i].ColumnName == dt2.Columns[i].ColumnName
                                                          &    dt1.Columns[i].GetType() == dt2.Columns[i].GetType())
                                {
                                    flag = true;
                                }
                                else
                                {
                                    flag = false;
                                    Response.Write("Both Column Names are Not Equal \n");
                                    break;
                                }
                            }
                            if (flag)
                            {
                                con.Open();

                                conn.Open();

                                string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;
                                           Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;
                                                HDR=Yes;IMEX=2\"";

                                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

                                excelConnection.Open();

                                OleDbCommand cmd1 = new OleDbCommand("Select *  from
                                                                                [" + getExcelSheetName + "]", excelConnection);

                                OleDbDataReader dr;

                                dr = cmd.ExecuteReader();

                                SqlBulkCopy sqlBulk = new SqlBulkCopy(con);

                                sqlBulk.DestinationTableName = tableName_Txt;

                                sqlBulk.WriteToServer(dr);

                                 excelConnection.Close();

                                 conn.Close();

                                 con.Close();

                                Label1.Text = "Insert successfully";

                            }
                        }
                        else
                        {
                            Label1.Text = "  Both Datatables Columns are Not Equal";
                        }
                    }
                }
                else
                {
                    Label1.Text = "Required Table Not Exits in DataBase";

                    TextBox1.Text = "";

                    TextBox1.Focus();
                }
            }
        }
        catch
        {
            throw;
        }
    }
}

No comments:

Post a Comment