SQL Server的批量上传策略 [英] Bulk upload strategy for SQL server

查看:51
本文介绍了SQL Server的批量上传策略的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在使用以下功能将数据从CSV上传到SQL表.

I am uploading data from CSV to SQL table using the following function.

有更好的方法吗?

我担心,现在,连接保持了很长时间.因此需要减少.

I am concerned about , right now, connection hold for long time. Hence need to be reduced.

   public bool SaveProxyBulkUploadData(List<AddServerPError> saveBulkUploadData)
        {
            try
            {
                foreach (AddServerPError addServetData in saveBulkUploadData)
                {
                    DbCommand dbCmd = CitiScriptExecutionDB.GetStoredProcCommand("USP_HS_InsertProxyBulkUploadData");
                    CitiScriptExecutionDB.AddInParameter(dbCmd, "@groupid", DbType.String, addServetData.GroupId);
                    CitiScriptExecutionDB.AddInParameter(dbCmd, "@proxyname", DbType.String, addServetData.ProxyName);
                    CitiScriptExecutionDB.AddInParameter(dbCmd, "@proxytype", DbType.String, addServetData.ProxyType);
                    CitiScriptExecutionDB.AddInParameter(dbCmd, "@sa_spoc_dl", DbType.String, addServetData.SA_SPOC_DL);
                    CitiScriptExecutionDB.AddInParameter(dbCmd, "@assignmentgroup", DbType.String, addServetData.AssignmentGroup);
                    CitiScriptExecutionDB.AddInParameter(dbCmd, "@createdby", DbType.String, addServetData.CreatedBy);
                    CitiScriptExecutionDB.ExecuteNonQuery(dbCmd);
                }

                return true;
            }
            catch (Exception ex)
            {
                string msg = ex.Message + ex.StackTrace;
                throw;
            }
        }

存储过程

CREATE PROCEDURE USP_HS_InsertBulkUploadData   
 @groupid  int,  
 @hostname varchar(50),   
 @type1 varchar(50),  
 @type2 varchar(50),  
 @createdby varchar(50)       
AS        
BEGIN       

 Insert into [dbo].[EUCUsecaseGroupInputParam]    
 (    
  GroupId,  
  HostName,    
  Type1,    
  Type2,     
  CreatedBy,     
  CreatedDate,     
  UpdatedBy,    
  UpdatedDate,    
  IsActive    
  )        
 Values(   
  @groupId,  
  @hostname,    
  @type1,    
  @type2,    
  @createdby,    
  GetDate(),    
  null,    
  null,    
  1    
  )       
END 

推荐答案

您最好的选择可能是 SqlBulkCopy ,它可以非常有效地将原始TDS投向服务器. SqlBulkCopy 接受两种类型的输入:

Your best bet here is probably SqlBulkCopy, which throws raw TDS at the server very efficiently. SqlBulkCopy takes two types of input:

  • DataTable
  • IDataReader

因此,此时您有3个选择:

So at that point you have 3 options:

  • 手动将您的 List< AddServerPError> 转换为 DataTable
  • 使用类似 FastMember 之类的方法在现有列表上获取 IDataReader (此示例位于项目页面的底部: https://www.nuget.org/packages/FastMember/
  • 抛弃现有列表,并直接将其作为 IDataReader 读取CSV-有一个"LumenWorks" CsvReader 一直以来都很好用;我看到的最接近的nuget链接是 https://www.nuget.org/packages/LumenWorksCsvReader/(尽管这不是原始内容,但已对其进行了扩展)
  • transform your List<AddServerPError> to a DataTable manually
  • use something like FastMember to obtain an IDataReader over your existing list (an example of this is at the bottom of the project page: https://github.com/mgravell/fast-member) - on nuget: https://www.nuget.org/packages/FastMember/
  • ditch your existing list, and read the CSV directly as an IDataReader - there is a "LumenWorks" CsvReader that has always worked well for that; the closest nuget link I can see is https://www.nuget.org/packages/LumenWorksCsvReader/ (although this isn't the original, it has been extended)

这篇关于SQL Server的批量上传策略的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆