Concatenate read & Insert Sql Excel Sheet Name Field.

Jayakumars
Posted by Jayakumars under ASP.NET AJAX category on | Points: 40 | Views : 1534
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;

public partial class Default3 : System.Web.UI.Page
{
string strFileType = @"D:\Test.xls";
string strFpath = ".xls";
DataTable dt=new DataTable ();
OleDbConnection con;
OleDbCommand cmd;
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
string query = null;
string connString = string.Empty;
if (strFpath.Trim() == ".xls")
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileType + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
else if (strFpath.Trim() == ".xlsx")
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileType + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
OleDbConnection con = new OleDbConnection(connString);
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter da = new OleDbDataAdapter();


query = "SELECT FirstName+LastName as Name,Empno FROM [" + "Sheet1" + "$]";
try
{
con = new OleDbConnection(connString);
if (con.State == ConnectionState.Closed) con.Open();
cmd = new OleDbCommand(query, con);
da = new OleDbDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
}
catch (Exception ex)
{

}
finally
{
da.Dispose();
con.Close();
con.Dispose();
}


for (int i = 0; i < dt.Rows.Count; i++)
{
con.Open();
string query1="Insert into Ta values('"+ dt.DefaultView[i][0].ToString() +"','"+ dt.DefaultView[i][1].ToString() +"')";
cmd = new OleDbCommand(query1, con);
cmd.ExecuteNonQuery();
}


}
}

Comments or Responses

Login to post response