Excel File data upload and insert into table. using asp.net c# and vb

C#

webconfig file:

<connectionStrings>

       <add name ="Excel03ConString"  connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

    <add name ="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

  </connectionStrings>

---------------------

.cs page

using System.Data;

using System.IO;

using System.Data.OleDb;

using System.Data.SqlClient;

using System.Configuration;

protected void btn_submit_Click(object sender, EventArgs e)

    {

        //Upload and save the file

        string excelPath = Server.MapPath("~/FileUpload/") + Path.GetFileName(FileUpload1.PostedFile.FileName);

        FileUpload1.SaveAs(excelPath);

        string conString = string.Empty;

        string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

        switch (extension)

        {

            case ".xls": //Excel 97-03

                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;

                break;

            case ".xlsx": //Excel 07 or higher

                conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;

                break;

        }

        conString = string.Format(conString, excelPath);

        using (OleDbConnection excel_con = new OleDbConnection(conString))

        {

            excel_con.Open();

            string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();

            DataTable dtExcelData = new DataTable();

            //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.

            dtExcelData.Columns.AddRange(new DataColumn[3]

            {

                new DataColumn("Waybill_NO", typeof(string)),

                new DataColumn("CLIENTID", typeof(string)),

                new DataColumn("ORDERID", typeof(string)),

                new DataColumn("PRODUCT_AMOUNT", typeof(decimal)),

               

                });

            using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))

            {

                oda.Fill(dtExcelData);

            }

            excel_con.Close();

            string consString = ConfigurationManager.ConnectionStrings[""].ConnectionString;

            using (SqlConnection con = new SqlConnection(consString))

            {

                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))

                {

                    //Set the database table name

                    sqlBulkCopy.DestinationTableName = "dbo.tablename";

                    //[OPTIONAL]: Map the Excel columns with that of the database table

                    sqlBulkCopy.ColumnMappings.Add("Waybill_NO", "Waybill_NO");

                    sqlBulkCopy.ColumnMappings.Add("CLIENTID", "CLIENTID");

                    sqlBulkCopy.ColumnMappings.Add("ORDERID", "ORDERID");

                    sqlBulkCopy.ColumnMappings.Add("PRODUCT_AMOUNT", "PRODUCT_AMOUNT");

                   

                    con.Open();

                    sqlBulkCopy.WriteToServer(dtExcelData);

                    con.Close();

                }

            }

        }

        lbl_msg.Text = "Excel file upload successfully";

        lbl_msg.ForeColor = Color.Green;

    }

    

Vb.

Imports System.IO

Imports System.Data

Imports System.Data.OleDb

Imports System.Data.SqlClient

Imports System.Configuration

<connectionStrings>

    

    <add name ="Excel03ConString"  connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

    <add name ="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

  </connectionStrings>

-------------------------------

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim excelPath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)

        FileUpload1.SaveAs(excelPath)

        Dim connString As String = String.Empty

        Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)

        Select Case extension

            Case ".xls"

                'Excel 97-03

                connString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString

                Exit Select

            Case ".xlsx"

                'Excel 07 or higher

                connString = ConfigurationManager.ConnectionStrings("Excel07+ConString").ConnectionString

                Exit Select

        End Select

        connString = String.Format(connString, excelPath)

        Using excel_con As New OleDbConnection(connString)

            excel_con.Open()

            Dim sheet1 As String = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString()

            Dim dtExcelData As New DataTable()

            '[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.

            dtExcelData.Columns.AddRange(New DataColumn(2) {New DataColumn("fname", GetType(String)), _

                                                            New DataColumn("mname", GetType(String)), _

                                                            New DataColumn("lname", GetType(String))})

            Using oda As New OleDbDataAdapter((Convert.ToString("SELECT * FROM [") & sheet1) + "]", excel_con)

                oda.Fill(dtExcelData)

            End Using

            excel_con.Close()

            Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

            Using con As New SqlConnection(conString)

                Using sqlBulkCopy As New SqlBulkCopy(con)

                    'Set the database table name

                    sqlBulkCopy.DestinationTableName = "dbo.test"

                    '[OPTIONAL]: Map the Excel columns with that of the database table

                    sqlBulkCopy.ColumnMappings.Add("fname", "fname")

                    sqlBulkCopy.ColumnMappings.Add("mname", "mname")

                    sqlBulkCopy.ColumnMappings.Add("lname", "lname")

                    con.Open()

                    sqlBulkCopy.WriteToServer(dtExcelData)

                    con.Close()

                End Using

            End Using

        End Using

    End Sub

<script type="text/javascript">
function validateCheckBoxes()
{
//debugger;
var isValid = false;
var gridView = document.getElementById('<%=GRD_viewoverview.ClientID%>');
for (var i = 1; i < gridView.rows.length; i++) {
var inputs = gridView.rows[i].getElementsByTagName('input');
if (inputs != null) {
if (inputs[0].type == "checkbox") {
if (inputs[0].checked) {
isValid = true;
return true;
}
}
}
}
alert("Please select atleast one checkbox for Item Transfer");
return false;
}
</script>


<asp:Button ID="btn_rentalcon" runat="server" Text="Create Rental Contract" Width="153px" OnClientClick="return validateCheckBoxes();" OnClick="btn_rentalcon_Click" /></td>