将数据从Excel附加到SQL Server现有表 [英] Append data from Excel to SQL Server existing table
问题描述
附加Excel工作表数据SQL 2008服务器现有表
参考此
使用ASP中的C#将数据从excel导入SQL服务器。 NET [ ^ ]
使用C#将MS Excel数据导入SQL Server表 [ ^ ]
使用系统;
使用 System.Collections.Generic;
使用 System.Linq;
使用 System.Web;
使用 System.Web.UI;
使用 System.Web.UI.WebControls;
使用 System.IO;
使用 System.Data.OleDb;
使用 System.Diagnostics;
使用 System.Data.SqlClient;
protected void btnImport_Click(object sender,EventArgs e)
{
lblmessage.Text =;
string subPath =ImportDocument; //你的代码在这里
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 =找不到文件;
返回;
}
其他
{
fileName = Path.Combine(服务器。 MapPath(〜/ ImportDocument),Guid.NewGuid()。ToString()+ Path.GetExtension(FileUpload1.PostedFile.FileName)); //使用System.IO;
FileUpload1.PostedFile。 SaveAs(fileName);
}
试试
{
//创建我们的连接字符串
string sexcelconnectionstring = @provider = microsoft.jet.oledb.4.0; data s ource =+ fileName +;扩展属性=+\excel 8.0; hdr = yes; \;
string ssqlconnectionstring =server = Software-10; user id = sa; password = sanllp; database = RLMemberDB; connection reset = false;
//执行查询以删除临时表
string sclearsql1 =删除表CSR_CU_ACNTTemp;
SqlConnection sqlconn1 = new SqlConnection(ssqlconnectionstring);
SqlCommand sqlcmd1 = new SqlCommand(sclearsql1,sqlconn1);
sqlconn1.Open();
sqlcmd1.ExecuteNonQuery();
sqlconn1.Close();
//使用Jet Provider导入。
//插入
strSQL =SELECT * INTO [odbc; Driver = {SQL Server}; +
Server = Software-10; Database = RLMemberDB; +
UID = sa; PWD = sanllp] .CSR_CU_ACNTTemp+
FROM [CSR_CU_ACNT
;
//将数据从excel文件批量复制到我们的sql表中的一系列命令
OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
OleDbCommand oledbcmd = new OleDbCommand(strSQL,oledbconn);
oledbconn.Open();
Debug.Print(strSQL);
lngRecsAff = oledbcmd.ExecuteNonQuery();
Debug.Print(受影响的记录:,lngRecsAff);
oledbconn.Close();
//执行查询以附加临时表中的数据
string sclearsql = 插入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;
}
}
Append Excel sheet datato SQL 2008 server existing table
Refer this
Import Data from excel to SQL server using C# in ASP. NET[^]
Import MS Excel data to SQL Server table using C#[^]
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 ;
}
}
这篇关于将数据从Excel附加到SQL Server现有表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!