将数据从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屋！