SQL Server的批量上传策略 [英] Bulk upload strategy for SQL server
本文介绍了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 aDataTable
manually - use something like
FastMember
to obtain anIDataReader
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屋!
查看全文