using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.OleDb;
using System.Diagnostics;
using System.Data.SqlClient;
protected void btnImport_Click(object sender, EventArgs e)
{
lblmessage.Text="";
string subPath = "ImportDocument"; // your code goes here
string fileName = string.Empty;
string strSQL= string.Empty;
int lngRecsAff=0;
string ssqltable = "CSR_CU_ACNTTemp";
bool isExists = System.IO.Directory.Exists(Server.MapPath(subPath));
if (!isExists)
{
lblmessage.Text= "No file found";
return ;
}
else
{
fileName = Path.Combine(Server.MapPath("~/ImportDocument"), Guid.NewGuid().ToString() + Path.GetExtension(FileUpload1.PostedFile.FileName));//using System.IO;
FileUpload1.PostedFile.SaveAs(fileName);
}
try
{
//create our connection strings
string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + fileName + ";extended properties=" + "\"excel 8.0;hdr=yes;\"";
string ssqlconnectionstring = "server=Software-10;user id=sa;password=sanllp;database=RLMemberDB;connection reset=false";
//execute a query to drop temp table
string sclearsql1 = "Drop table CSR_CU_ACNTTemp";
SqlConnection sqlconn1 = new SqlConnection(ssqlconnectionstring);
SqlCommand sqlcmd1 = new SqlCommand(sclearsql1, sqlconn1);
sqlconn1.Open();
sqlcmd1.ExecuteNonQuery();
sqlconn1.Close();
//Import by using Jet Provider.
//Insert
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" +
"Server=Software-10;Database=RLMemberDB;" +
"UID=sa;PWD=sanllp].CSR_CU_ACNTTemp " +
"FROM [CSR_CU_ACNT$]";
//series of commands to bulk copy data from the excel file into our sql table
OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
OleDbCommand oledbcmd = new OleDbCommand(strSQL, oledbconn);
oledbconn.Open();
Debug.Print(strSQL);
lngRecsAff = oledbcmd.ExecuteNonQuery();
Debug.Print("Records affected: ", lngRecsAff);
oledbconn.Close();
//execute a query to append data from temp table
string sclearsql = "Insert INTO CSR_CU_ACNT Select * from " + ssqltable + "";
SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
}
catch (Exception ex)
{
lblmessage.Text = ex.Message ;
}
}