2023年6月21日发(作者：)
C#中⾼效批量插⼊的⽅法⽅式⼀：使⽤数据库中的Table类型变量实现数据插⼊这种⽅法的前提是数据库中必须⽀持Table类型的变量。--创建⾃定义表变量Create Type PassportTableType as nvarchar(50))--创建存储过程Create PROCEDURE [dbo].[CreatePassportWithTVP]
@TVP PassportTableType readonly
AS
BEGIN
SET NOCOUNT ON;
Insert into Passport(PassportKey) select PassportKey from @TVP
END--在程序中调⽤SqlParameter[] sqlParameter = { new SqlParameter("@TVP", dataTable) };
eNonQuery(connectionString, Procedure, "CreatePassportWithTVP", sqlParameter);⽅式⼆：使⽤SqlBulkCopy使⽤SqlBulkCopy类进⾏数据插⼊其原理是采⽤了SQL Server的BCP协议进⾏数据的批量复制，使⽤这个有点要注意的地⽅，就是数据源表中的列和⽬标表中的列⼀⼀对应/// /// 批量插⼊数据 /// /// ⽬标表 /// 数据源表 /// 链接字符串 /// public static bool SqlBulkCopyInsert(string DestinationTableName, DataTable dt,string connectionString) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString)) { try { ationTableName = DestinationTableName; ize = ; SqlConnection sqlConnection = new SqlConnection(connectionString); (); if (dt != null && != 0) { oServer(dt); } (); (); } catch { return false; } } return true; }下⾯是想写成泛型的，这是个半成品还没有想好怎样实现通⽤性会更好/// ///
/// /// /// /// /// /// /// public static bool SqlBulkCopyInsert(string DestinationTableName, string[] DataColumnName, Dictionary dictionary) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(teV2String)) { try { DataTable dataTable = leSchema(DataColumnName); foreach (KeyValuePair keyValuePair in dictionary) { DataRow dataRow = (); ray = new object[] { , }; (dataRow); } ationTableName = DestinationTableName; ize = ; SqlConnection sqlConnection = new SqlConnection(teV2String); (); if (dataTable != null && != 0) { oServer(dataTable, ed); } (); (); } catch { return false; } } return true; }
2023年6月21日发(作者：)
C#中⾼效批量插⼊的⽅法⽅式⼀：使⽤数据库中的Table类型变量实现数据插⼊这种⽅法的前提是数据库中必须⽀持Table类型的变量。--创建⾃定义表变量Create Type PassportTableType as nvarchar(50))--创建存储过程Create PROCEDURE [dbo].[CreatePassportWithTVP]
@TVP PassportTableType readonly
AS
BEGIN
SET NOCOUNT ON;
Insert into Passport(PassportKey) select PassportKey from @TVP
END--在程序中调⽤SqlParameter[] sqlParameter = { new SqlParameter("@TVP", dataTable) };
eNonQuery(connectionString, Procedure, "CreatePassportWithTVP", sqlParameter);⽅式⼆：使⽤SqlBulkCopy使⽤SqlBulkCopy类进⾏数据插⼊其原理是采⽤了SQL Server的BCP协议进⾏数据的批量复制，使⽤这个有点要注意的地⽅，就是数据源表中的列和⽬标表中的列⼀⼀对应/// /// 批量插⼊数据 /// /// ⽬标表 /// 数据源表 /// 链接字符串 /// public static bool SqlBulkCopyInsert(string DestinationTableName, DataTable dt,string connectionString) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString)) { try { ationTableName = DestinationTableName; ize = ; SqlConnection sqlConnection = new SqlConnection(connectionString); (); if (dt != null && != 0) { oServer(dt); } (); (); } catch { return false; } } return true; }下⾯是想写成泛型的，这是个半成品还没有想好怎样实现通⽤性会更好/// ///
/// /// /// /// /// /// /// public static bool SqlBulkCopyInsert(string DestinationTableName, string[] DataColumnName, Dictionary dictionary) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(teV2String)) { try { DataTable dataTable = leSchema(DataColumnName); foreach (KeyValuePair keyValuePair in dictionary) { DataRow dataRow = (); ray = new object[] { , }; (dataRow); } ationTableName = DestinationTableName; ize = ; SqlConnection sqlConnection = new SqlConnection(teV2String); (); if (dataTable != null && != 0) { oServer(dataTable, ed); } (); (); } catch { return false; } } return true; }
发布评论