C#使用SqlBulkCopy快速导入Excel文件(xls/xlsx)


C#使用SqlBulkCopy快速导入Excel文件(xls/xlsx)

一、导入数据界面

图片

 

图片

二、扩展【导入关键词】按钮

图片

 

按钮事件:

 

C# 全选
     public virtual void DoImport(IButtonInfo sender)
{
    var count = new frmKeywordImporter().Execute();
    Msg.ShowInformation($"成功导入{count}条记录!");
}

 

三、ImportExcel.cs

 

C# 全选
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSFrameworkV5.Library.CommonClass
{
    public class ImportExcel
    {
        protected DbConnection _DbConnection = null;

        private string _FileName = "";

        private bool _IsConnected = false;

        public string FileName { get { return _FileName; } }

        public ImportExcel(string fileName)
        {
            _FileName = fileName;

            if (File.Exists(fileName))
            {
                _DbConnection = CreateConnection();
                TestConnection();
            }
            else
                throw new Exception("文件不存在!" + fileName);
        }

        public int Import(string tableName, Action<DataRow> row)
        {
            var dt = GetData(tableName);
            foreach (DataRow R in dt.Rows) row(R);
            return dt.Rows.Count;
        }

        public bool TestConnection()
        {
            try
            {
                _IsConnected = this.GetTables().Count > 0;

                //excel 格式

                return _IsConnected;
            }
            catch
            {
                return false;
            }
        }

        public DbConnection CreateConnection()
        {
            string conn = "";
            string fileExt = Path.GetExtension(_FileName);

            if (fileExt == ".xls")
            {
                // IMEX=1 可把混合型作为文本型读取,避免null值         
                //xls
                conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _FileName + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
            }

            if (fileExt == ".xlsx")
            {
                //xlsx格式的OLEDB链接
                conn = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + _FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
            }

            if (String.IsNullOrWhiteSpace(conn)) throw new Exception("不能识别的文件格式!");

            return new OleDbConnection(conn);
        }


        /// <summary>
        /// 获取Sheet数据
        /// </summary>
        /// <returns></returns>
        public virtual DataTable GetData(string tableName)
        {
            string sql = "SELECT * FROM [" + tableName + "] ";
            return this.GetDataBySQL(sql);
        }

        public DataTable GetDataBySQL(string sql)
        {
            DataTable dt = null;
            try
            {
                if (_DbConnection.State == ConnectionState.Closed) _DbConnection.Open();
                OleDbCommand cmd = _DbConnection.CreateCommand() as OleDbCommand;
                cmd.CommandText = sql;
                dt = new DataTable();
                DbDataAdapter adp = new OleDbDataAdapter(cmd);
                adp.Fill(dt);
            }
            finally
            {
                if (_DbConnection.State != ConnectionState.Closed) _DbConnection.Close();
            }
            return dt;
        }

        public void CloseConnection()
        {
            //
        }


        /// <summary> 
        /// 取XLS文件所有表名(SHEET)
        /// </summary> 
        /// <returns></returns>       
        public List<string> GetTables()
        {
            List<string> list = new List<string>();
            try
            {
                if (_DbConnection.State == ConnectionState.Closed) _DbConnection.Open();
                DataTable dt = _DbConnection.GetSchema("Tables");
                foreach (DataRow row in dt.Rows)
                {
                    if ((row[3].ToString() == "TABLE") || (row[3].ToString() == "BASE TABLE"))
                        list.Add(row[2].ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_DbConnection.State != ConnectionState.Closed) _DbConnection.Close();
            }
            return list;
        }


    }
}

四、导入数据按钮事件

 

C# 全选
private void btnImport_Click(object sender, EventArgs e)
{
    if (!File.Exists(txtExcel.Text)) return;
    Assertion.AssertEditorEmpty(txtTableName, "请选择表名!", true);

    try
    {
        btnImport.Enabled = false;
        Application.DoEvents();

        ImportExcel import = new ImportExcel(txtExcel.Text);

        DataTable target = GetTargetTable();

        string keyword = "";

        import.Import(txtTableName.Text, (row) =>
        {
            keyword = row[1].ToStringEx().Trim();

            //关键词不为空,导入记录
            if (keyword != "")
            {
                DataRow R = target.Rows.Add();
                R["CustomerCode"] = txtCustomerCode.EditValue;
                R["Domain"] = txtDomain.EditValue;
                R["Keyword"] = keyword;
                R["IndexNo"] = ConvertEx.ToInt(row[5]);//数字类型
                R["IndexType"] = "百度";
                R["FlagSEO"] = row[6].ToStringEx() == "1" ? "Y" : "N";//FlagSEO=1/0/空
                R["InUse"] = "Y";
                R["CreationDate"] = DateTime.Now;
                R["CreatedBy"] = "admin";
                R["LastUpdateDate"] = DateTime.Now;
                R["LastUpdatedBy"] = "admin";
            }
        });

        long count = new bllCustomerKeywords().BulkImport(target);

        _ImportCount = count;

        if (count > 0)
            this.Close();
        else
            btnImport.Enabled = true;
    }
    catch (Exception ex)
    {
        btnImport.Enabled = true;
        Msg.Warning(ex.Message);
    }
}

五、DAL层使用SqlBulkCopy快速导入数据

C# 全选
public long SyncRows { get; set; } = 0;

public long BulkImport(DataTable dt)
{
    if (dt.Rows.Count == 0) return 0;
    int batchSize = 10;//每个批次提交的数据(单个事务)

    try
    {
        //目标数据库,连接字符串
        string conn = _Database.ConnectionString;
        SqlBulkCopy bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default);

        bulk.BatchSize = batchSize;
        bulk.BulkCopyTimeout = 5 * 60;
        bulk.DestinationTableName = dt.TableName;
        bulk.SqlRowsCopied += Bulk_SqlRowsCopied;
        bulk.NotifyAfter = 1;
        bulk.WriteToServer(dt);

        return this.SyncRows;
    }
    catch (Exception ex)
    {

        //计算导入成功的记录数
        if (this.SyncRows > 0 && batchSize > 0)
        {
            if (this.SyncRows % batchSize > 0)
                this.SyncRows = this.SyncRows - SyncRows % batchSize;
            if (this.SyncRows == batchSize)
                this.SyncRows = 0;
        }

        return this.SyncRows;
    }
}

private void Bulk_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
    SyncRows = e.RowsCopied;
}

 

 快速开发框架

C/S架构快速开发平台-旗舰版V5.1 (2021最新版)

https://www.cscode.net/archive/csv5/1630505048.html

 

版权声明:本文为开发框架文库发布内容,转载请附上原文出处连接
C/S框架网
上一篇:最强效率!3个小时完成一个C/S软件系统开发
下一篇:C# FormTool.SetCursor设置光标为等待状态,提高用户体验
评论列表

发表评论

评论内容
昵称:
关联文章

C#使用SqlBulkCopy快速导入Excel文件(xls/xlsx)
C# OleDbConnection读取加装Excel2003(.xls),Excel2007(.xlsx)文件
C# Excel xls文件导入程序Excel数据库通用类XlsFileDB
Winform框架通用导入Excel文件源码下载
C#操作Excel合并多个Excel文件
原创文档-Winform开发框架提供通用导入Excel功能界面|C/S框架网
DevExpress导出不同的格式的文件Excel文件(*.xls)|Word文件(*.doc)|PDF文件(*.pdf)
在基类窗体扩展一个通用导出Excel文件的按钮(支持导出所有表格的数据到Xlsx文件)
C#将数据导出到Excel汇总
C#导出数据到Excel 源码大全
C#代码的Excel读取器(不需要Office Excel Com组件)
C#读取Excel文件提示:未在本地计算机上注册“Microsoft.Jet.OLEDB.4.0”
C#中将数据插入到Excel表中
CSFramework.COM原创:全球国家名称列表国旗图标库导入程序(C#+VS2015)
C# 取真实的文件类型
解决方案:FastReport.NET导出Excel文件单元格的数据空白
框架提供通用数据导出功能,将表格数据导出到EXCEL文件
C# SharpMap的简单使用
关于FastReport.Net 1.x导出PDF/XLS文件出现中文乱码解决方案
C# ASP.NET使用ashx一般处理程序实现上传文件功能