check boxvalidation in gridview in javascript: May 2018

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

webconfig file:

</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>

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

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 Sub

Tuesday, 8 May 2018