How to import to insert Customized fields.

Jayakumars
Posted by Jayakumars under ASP.NET AJAX category on | Points: 40 | Views : 816
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)
{

WebService wb1=new WebService ();
string Empame = "AAA";
if (Empame == "AAA")
{
string Rec1 = wb1.HelloWorld("Asp.net");
}
else
{
string Rec2 = wb1.HelloWorld("Asp.net", "Dotnet");
}


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 ID,Name,Address,Mob,FirstName+LastName as FullName, City,State,Country 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 tblProduct values('" + dt.DefaultView[i]["ID"].ToString() + "','" + dt.DefaultView[i]["Name"].ToString() + "','" + dt.DefaultView[i]["Address"].ToString() + "')";
cmd = new OleDbCommand(query1, con);
cmd.ExecuteNonQuery();

string query2 = "Insert into tblCustomer values('" + dt.DefaultView[i]["Name"].ToString() + "','" + dt.DefaultView[i]["FullName"].ToString() + "','" + dt.DefaultView[i]["Mob"].ToString() + "')";
cmd = new OleDbCommand(query1, con);
cmd.ExecuteNonQuery();

string query3 = "Insert into tbltransact values('" + dt.DefaultView[i]["City"].ToString() + "','" + dt.DefaultView[i]["State"].ToString() + "','" + dt.DefaultView[i]["Country"].ToString() + "')";
cmd = new OleDbCommand(query1, con);
cmd.ExecuteNonQuery();

}


}
}

Comments or Responses

Login to post response