如何编写C＃代码将Excel数据上传到数据库表？ [英] How to write C# code to upload excel data to database table?

问题描述

试图尽可能清楚地解释这个案例。



让我们说，我有数据库连接字符串代码（见下文），工作正常。 



我们说，数据库是  xyz ，
并且有一个名为  客户  在数据库  xyz



我想做什么 ：将Excel数据上传到表格 客户 ，
请参阅excel数据截图。



现在我想编写代码  btnUpload_Click  ，
我该怎么写呢？还是其他任何评论？



谢谢。

Trying to explain the case as clearly as I can.

Let us say, I have database connection string code (see below), which works fine. 

Let us say, the database is xyz, and there is table called Customer under database xyz

What I want to do: Upload the excel data into table Customer, see excel data screenshot.

Now I would like to write code for btnUpload_Click , how should I write it? Or any other comments?

Thanks.

OdbcConnection Cn; 
Username = txtUsername.Text;
Password = txtPassword.Text;
ConnectionString = "Driver={Adaptive Server Enterprise};server=*****;port=*****;db=xyz;uid=" + Username + ";pwd=" + Password + ";";

Cn = new OdbcConnection(ConnectionString);
//The code works fine for this part

private void btnUpload_Click(object sender, EventArgs e)   //Don't know how to write the code for this part.
        {
            //Click the button to browse excel file, no hard code for file path.
            //The excel file has only one sheet, no hidden sheet
            //The excel filename could be anything, not necessarily same as database table name Customer.
            //Excel column order may not be same as database table Customer field order
            //Excel title row names (CustomerName,Address,City,PostalCode,Country) are same as database table Customer filed names, but may not be in the same order.
        }

推荐答案

您好，

以下代码显示如何通过OleDb读取Excel工作表。文件名是硬编码的，通过使用OpenFileDialog允许用户选择Excel文件，可以动态进行一些工作。

The following code shows how to read an Excel WorkSheet via OleDb. The file name is hard coded yet with a little work could be dynamic by using a OpenFileDialog to allow the user to select the Excel file.

用于创建连接字符串的类  （SmartConnection）可以
在这里找到，复制和粘贴到您的项目中。

The class for creating a connection string  (SmartConnection) can be found here, copy and pasted into your project.

如果有可能有多个工作表名称，您可以使用以下代码获取工作表名称

SheetNames （与SmarConnection在同一个存储库中），向用户显示名称以供选择或在代码中执行。

If there is a chance there is more than one sheet name you can get sheet names using the following code SheetNames (in the same repository as SmarConnection), present the names to the user to select or do it in code.

SELECT语句中的注1我使用SELECT *，这通常不明智但在这种情况下很好。

Note 1 in the SELECT statement I use SELECT * which is normally not wise but is fine in this case.

注意2将数据读入DataTable后，您可以通过dt.Columns访问列名，以确保所有列都存在。

Note 2 Once the data is read into the DataTable you have access to column names via dt.Columns to ensure all columns exists that are needed.

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace ReadSheetWithColumnHeadersOldDb
{
    public class Operations
    {
        public DataTable ReadCustomersDataTable()
        {
            // this could be passed in where a OpenFileDialog was used to select the file
            var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,"Customers.xlsx");
            var dt = new DataTable();
            var con = new SmartConnection();

            using (var cn = new OleDbConnection(con.ConnectionString(fileName, 0, ExcelHeader.Yes)))
            {
                using (var cmd = new OleDbCommand {Connection = cn, CommandText = "SELECT * FROM [Customers

"}）
{
cn.Open（）;
dt.Load（cmd.ExecuteReader（CommandBehavior.CloseConnection）??抛出新的InvalidOperationException（））;
}
}


返回dt;
}
}
}

" }) { cn.Open(); dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection) ?? throw new InvalidOperationException()); } } return dt; } } }

调用上述内容（请记住，您可以传递文件名而不是被硬编码）

Calling the above (and keep in mind you can pass the file name in rather than being hard coded)

var ops = new Operations();
var dt = ops.ReadCustomersDataTable();

要将DataTable推送到SQL-Server数据库表，请使用
SqlBulkCopy 。上面的类不会使用NuGet包
BaseConnectionLibrary （这是我的）来创建与SQL的连接。-server 如果连接库不适合您的连接，请参阅
以下页面。

For pushing the DataTable to SQL-Server database table use SqlBulkCopy. The class above will not use a NuGet package BaseConnectionLibrary (this is mine) to create a connection to SQL-Server.  If by chance the connection library does not suit your connection then see the following page.

连接字符串在构造函数中设置。 BatchBulkCopy方法接受从ReadCustomersDataTable方法读入的DataTable，并将DataTable复制到参数destinationTable中指示的数据库表。你不应该需要
map，但是如果你这样做，SqlBulkCopy对象会进行列映射，例如sbc。 ColumnMappings.Add 。

The connection string is setup in the constructor. The method BatchBulkCopy accepts the DataTable read in from ReadCustomersDataTable method and copies the DataTable to the database table indicated in the argument destinationTable. You should not need to map but if you do the SqlBulkCopy object does column mapping e.g. sbc.ColumnMappings.Add.

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using BaseConnectionLibrary.ConnectionClasses;

namespace ReadSheetWithColumnHeadersOldDb
{
    public class Operations : SqlServerConnection
    {
        public Operations()
        {
            // server name, in this case my server name, could be .\SQLEXPRESS or a name instance
            DatabaseServer = "KARENS--PC";
            // database name 
            DefaultCatalog = "NorthWind";
        }
        public DataTable ReadCustomersDataTable()
        {
            // this could be passed in where a OpenFileDialog was used to select the file
            var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,"Customers.xlsx");
            var dt = new DataTable();
            var con = new SmartConnection();

            using (var cn = new OleDbConnection(con.ConnectionString(fileName, 0, ExcelHeader.Yes)))
            {
                using (var cmd = new OleDbCommand {Connection = cn, CommandText = "SELECT * FROM [Customers

"}）
{
cn.Open（）;
dt.Load（cmd.ExecuteReader（CommandBehavior.CloseConnection）??抛出新的InvalidOperationException（））;
}
}


返回dt;
}

public void BatchBulkCopy（DataTable dataTable，string destinationTable）
{
//获取DataTable
var dtInsertRows = dataTable;

using（var sbc = new SqlBulkCopy（ConnectionString，SqlBulkCopyOptions.KeepIdentity））
{
sbc.DestinationTableName = destinationTable;
//写入服务器
sbc.WriteToServer（dtInsertRows）;
}
}
}
}

" }) { cn.Open(); dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection) ?? throw new InvalidOperationException()); } } return dt; } public void BatchBulkCopy(DataTable dataTable, string destinationTable) { // Get the DataTable var dtInsertRows = dataTable; using (var sbc = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.KeepIdentity)) { sbc.DestinationTableName = destinationTable; // write to server sbc.WriteToServer(dtInsertRows); } } } }

这篇关于如何编写C＃代码将Excel数据上传到数据库表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！