动态地将记录从Excel导入到数据库 [英] Dynamically import records from excel to database

问题描述

在我的应用程序中，我有超过160个字段，我想让用户选择几个字段，然后他可以将数据从excel导入数据库。

我怎样才能达到相同的效果我不想创建一个包含160个参数的过程，然后在存储过程中管理转换。



有没有动态的方法呢？通过它我可以管理事物而不给出160个参数。



我尝试过的事情：



我能想到的解决方案是创建一个包含160个参数的过程，并有1个表来映射参数和excel列，然后将数据插入数据库。

In my application, I have more than 160 fields, I want to enable user to select few of fields and then he can import data from excel to database.
How can I achieve the same as I don't want to create a procedure with 160 parameters and then managing transformation within stored proc.

Is there any dynamic way of doing this? Through which I can manage things without giving 160 parameters.

What I have tried:

The solution which I can think of is to create a procedure with 160 parameters and have 1 table which will map the parameters and excel column and then insert data into database.

推荐答案

Office通过C＃和vb.net示例将Excel电子表格数据导入Sql Server表在C＃，VB.NET，SQL for Visual Studio 2013中 [ ^ ]示例代码和项目



使用System.Data.OleDb;

使用System.Data.SqlClient;





public void ImportDataFromExcel（string excelFilePath）

{

//声明变量 - 根据您的具体情况编辑这些变量

string ssqltable =Table1;

//确保您的工作表名称正确，此处工作表名称为sheet1，因此您可以更改工作表名称，如果有不同的

字符串myexceldataquery =select student，rollno ，当然来自[Sheet1

Office Import Excel Spreadsheet data into Sql Server table via C# and vb.net sample in C#, VB.NET, SQL for Visual Studio 2013[^] sample code and project

using System.Data.OleDb;
using System.Data.SqlClient;


public void ImportDataFromExcel(string excelFilePath)
{
//declare variables - edit these based on your particular situation
string ssqltable = "Table1";
// make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
string myexceldataquery = "select student,rollno,course from [Sheet1

;

尝试

{

//创建我们的连接字符串

string sexcelconnectionstring = @provider = microsoft.jet .oledb.4.0; data source =+ excelFilePath +

;扩展属性=+\excel 8.0; hdr = yes; \;

string ssqlconnectionstring =Data Source = SAYYED; Initial Catalog = SyncDB; Integrated Security = True;

//执行查询以清除目标表中的所有先前数据

string sclearsql =delete from+ ssqltable;

SqlConnection sqlconn = new SqlConnection（ssqlconnectionstring）;

SqlCommand sqlcmd = new SqlCommand（sclearsql，sqlconn）;

sqlconn.Open（）;

sqlcmd.ExecuteNonQuery（）;

sqlconn.Close（）;

//将数据从excel文件批量复制到我们的sql表中的一系列命令

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）;

}

dr.Close（）;

oledbconn.Close（）;

Label1.Text =文件导入到sql server。;

}

catch（exception ex）

{

//处理异常

}

}

";
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 = "Data Source=SAYYED;Initial Catalog=SyncDB;Integrated Security=True";
//execute a query to erase any previous data from our destination table
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);
}
dr.Close();
oledbconn.Close();
Label1.Text = "File imported into sql server.";
}
catch (Exception ex)
{
//handle exception
}
}

这篇关于动态地将记录从Excel导入到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！