使用SQL查询通过excel文件在SQL Server中插入数据 [英] Insert data in SQL server through excel file using SQL query
本文介绍了使用SQL查询通过excel文件在SQL Server中插入数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
您好可以帮助我，我正在开发Asp.Net应用程序，我想通过Excel工作表在数据库中插入数据。为此我使用下面的代码，工作正常。但它的插入数据直接在data base.i想要添加SQL查询，指定我插入的那些数据..但我不知道我在哪里以及如何使用SQL查询在数据库中插入数据
Hello can any one help me i am develop Asp.Net application where i want to insert data in data base through excel sheet.For this i am using the following code that is work nice.but its insert data directly in to the data base.i want to add SQL query that specifies those data that i am insert..but i don't know where and how i use SQL query that insert data in database
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button Text="Upload" OnClick = "Upload" runat="server" />
<asp:Label Text="" ID="label1" runat="server" />
</div>
</form>
</body>
按钮代码
Code for button
protected void Upload(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string path = string.Concat((Server.MapPath("~/temp/" + FileUpload1.FileName)));
FileUpload1.PostedFile.SaveAs(path);
OleDbConnection oleDbcon = new OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0;Data Source ="+path+";Extended Properties=Excel 12.0;");
OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", oleDbcon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
oleDbcon.Open();
DbDataReader dr = cmd.ExecuteReader();
string con_str =@"Data Source =.\SQLEXPRESS; Initial Catalog = CR_SYS; Integrated Security = True";
SqlBulkCopy bulkInsert = new SqlBulkCopy(con_str);
//this code insert data in database directly but i want sql query fro this to add data in StudentBulk table
bulkInsert.DestinationTableName = "StudentBulk";
bulkInsert.WriteToServer(dr);
oleDbcon.Close();
Array.ForEach(Directory.GetFiles((Server.MapPath("~/temp/"))), File.Delete);
label1.ForeColor = Color.Blue;
label1.Text = "Succesful";
}
else
{
label1.ForeColor = Color.Red;
label1.Text = "slelct the file";
}
}
我的尝试：
我想为插入添加一个sql查询
What I have tried:
I want to add a sql query for insertion
推荐答案
，oleDbcon）;
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter（cmd）;
oleDbcon.Open（）;
DbDataReader dr = cmd.ExecuteReader（）;
string con_str = @ 数据源= .\SQLEXPRESS;初始目录= CR_SYS; Integrated Security = True;
SqlBulkCopy bulkInsert = new SqlBulkCopy（con_str）;
// 此代码直接在数据库中插入数据，但我希望从此查询sql以在StudentBulk表中添加数据
bulkInsert .DestinationTableName = StudentBulk;
bulkInsert.WriteToServer （dr）;
oleDbcon.Close（）;
Array.ForEach（Directory.GetFiles（（Server.MapPath（ 〜/ temp /））），File.Delete）;
label1.ForeColor = Color.Blue;
label1.Text = 成功;
}
其他
{
label1.ForeColor = Color.Red;
label1.Text = slelct the file;
}
}
", oleDbcon); OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd); oleDbcon.Open(); DbDataReader dr = cmd.ExecuteReader(); string con_str =@"Data Source =.\SQLEXPRESS; Initial Catalog = CR_SYS; Integrated Security = True"; SqlBulkCopy bulkInsert = new SqlBulkCopy(con_str); //this code insert data in database directly but i want sql query fro this to add data in StudentBulk table bulkInsert.DestinationTableName = "StudentBulk"; bulkInsert.WriteToServer(dr); oleDbcon.Close(); Array.ForEach(Directory.GetFiles((Server.MapPath("~/temp/"))), File.Delete); label1.ForeColor = Color.Blue; label1.Text = "Succesful"; } else { label1.ForeColor = Color.Red; label1.Text = "slelct the file"; } }
我的尝试：
我想为插入添加一个SQL查询
What I have tried:
I want to add a sql query for insertion
在这种情况下： -
数据表中的Excel工作表值如
In that case:-
First take Excel sheet value in a datatable like
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1
，OleDbcon） ;
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter（cmd）;
ds = new DataSet（）;
objAdapter1.Fill（ds）;
Dt = ds.Tables [ 0 ];
", OleDbcon); OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd); ds = new DataSet(); objAdapter1.Fill(ds); Dt = ds.Tables[0];
之后使用for循环你可以实现你的目标如
After that using for loop you can achieve your goal like as
private void InsertData()
{
for (int i = 0; i < Dt.Rows.Count; i++)
{
DataRow row = Dt.Rows[i];
int columnCount = Dt.Columns.Count;
string[] columns = new string[columnCount];
for (int j = 0; j < columnCount; j++)
{
columns[j] = row[j].ToString();
}
conn.Open();
string sql = "INSERT INTO TestTable(A,B,C,D,E)";
sql += "VALUES('" + columns[0] + "','" + columns[1] + "','" + columns[2] + "',Convert(varchar(10),'" + columns[3] + "',103),'" + columns[4] + "')";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
希望这是你的答案。
Hope this will be your answer.
这篇关于使用SQL查询通过excel文件在SQL Server中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
