importing excel to sql c#
本文关键字:sql to excel importing | 更新日期: 2023-09-27 18:04:32
我一直试图导入一个excel表到我的sql数据库。我试过这个例子:
void importdata(string excelfilepath)
{
//declare variables - edit these based on your particular situation
string ssqltable = "tTableExcel";
// make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have
string myexceldataquery = "select student,rollno,course from [sheet1$]";
try
{
//create our connection strings
string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath +
";extended properties=" + "'"excel 8.0;hdr=yes;'"";
string ssqlconnectionstring = "server=mydatabaseservername;userid=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();
//series of commands to bulk copy data from the excel file into our sql table
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)
{
//handle exception
}
}
我的程序不是很好,我不能使它工作。SQL连接没有任何问题(运行程序后,数据库被清除)。但是我无法将任何信息输入数据库。我的excel文件名为test.xlsx,存储在D:'Users'Haners中。我的数据库表名为Test。我怎样才能使我的程序导入excel文件中的信息到数据库??
前代码:
SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
bulkcopy.DestinationTableName = ssqltable;
while (dr.Read())
{
bulkcopy.WriteToServer(dr);
}
新代码:
SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
bulkcopy.DestinationTableName = ssqltable;
bulkcopy.BatchSize=100;
bulkcopy.WriteToServer(dr);
希望这有助于解决您的问题。快乐的编码。欢呼声