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();
}
}
}