Using System.Data.OLEDB;
Using System.Data.SqlClient;
public void ExcelToSQL(string excelpath)
{
string ssqltable = "tableName";
string myexceldataquery = "select student,rollno,course from [sheet1$]";
try
{
string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath +
";extended properties=" + "\"excel 8.0;hdr=yes;\"";
string ssqlconnectionstring = "server=mydatabaseservername;user
id=dbuserid;password=dbuserpassword;database=databasename;connection reset=false";
string sclearsql = "delete from " + ssqltable;
sqlconnection sqlconn = new sqlconnection(ssqlconnectionstring);
sqlcommand sqlcmd = new sqlcommand(sclearsql, sqlconn);
sqlconn.open();
sqlcmd.executenonquery();
sqlconn.close();
oledbconnection oledbconn = new oledbconnection(sexcelconnectionstring);
oledbcommand oledbcmd = new oledbcommand(myexceldataquery, oledbconn);
oledbconn.open();
oledbdatareader dr = oledbcmd.executereader();
sqlbulkcopy bulkcopy = new sqlbulkcopy(ssqlconnectionstring);
bulkcopy.destinationtablename = ssqltable;
while (dr.read())
{
bulkcopy.writetoserver(dr);
}
oledbconn.close();
}
catch (exception ex)
{
}
}
Using System.Data.SqlClient;
public void ExcelToSQL(string excelpath)
{
string ssqltable = "tableName";
string myexceldataquery = "select student,rollno,course from [sheet1$]";
try
{
string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath +
";extended properties=" + "\"excel 8.0;hdr=yes;\"";
string ssqlconnectionstring = "server=mydatabaseservername;user
id=dbuserid;password=dbuserpassword;database=databasename;connection reset=false";
string sclearsql = "delete from " + ssqltable;
sqlconnection sqlconn = new sqlconnection(ssqlconnectionstring);
sqlcommand sqlcmd = new sqlcommand(sclearsql, sqlconn);
sqlconn.open();
sqlcmd.executenonquery();
sqlconn.close();
oledbconnection oledbconn = new oledbconnection(sexcelconnectionstring);
oledbcommand oledbcmd = new oledbcommand(myexceldataquery, oledbconn);
oledbconn.open();
oledbdatareader dr = oledbcmd.executereader();
sqlbulkcopy bulkcopy = new sqlbulkcopy(ssqlconnectionstring);
bulkcopy.destinationtablename = ssqltable;
while (dr.read())
{
bulkcopy.writetoserver(dr);
}
oledbconn.close();
}
catch (exception ex)
{
}
}
No comments:
Post a Comment