当使用SQL Server身份验证时，我在使用SqlBulkCopy命令时遇到了问题。但是，在使用Windows身份验证时没有此问题。
SqlBulkCopy sbc = new SqlBulkCopy(sqConn.ConnectionString, SqlBulkCopyOptions.KeepIdentity);
这会抛出一个错误：
用户 'xx' 的登录失败
代码：
SqlBulkCopy sbc = new SqlBulkCopy(sqConn);
这个方法可以正常运行，但是无法保留标识列的原始值。
"persist security info=true"。否则，如果连接已经打开，则密码将从sqlConn.ConnectionString中删除。解决方案非常简单，但我仍然很想知道为什么SQL Server身份验证应该与Windows身份验证不同。
using (SqlTransaction transaction =
sqConn.BeginTransaction())
{
SqlBulkCopy sbc = new SqlBulkCopy(sqConn,SqlBulkCopyOptions.KeepIdentity,transaction);
sbc.DestinationTableName = file;
sbc.BatchSize = 1000;
sbc.NotifyAfter = 1000;
sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
sbc.WriteToServer(SourceTable);
transaction.Commit();
}
new SqlBulkCopy() has 4 overloads: SqlConnection only, SqlConnection/SqlBulkCopyOptions/SqlTransaction, string only, and string/SqlBulkCopyOptions. If you want to supply the copy option KeepIdentity, then you will have to supply something for the SqlTransaction, otherwise the compiler will try to match on the string/SqlBulkCopyOptions overload. As far as I know, there's nothing stopping you from just passing a null object for the SqlTransaction: new SqlBulkCopy(sqConn, SqlBulkCopyOptions.KeepIdentity, null) - Thorin试试这个，对我有用
private static void BulkInsert(DataTable dtExcel, SqlConnection con)
{
try
{
{
if (con.State == ConnectionState.Closed)
con.Open();
var sqlTransactionScope = con.BeginTransaction();
//Open bulkcopy connection.
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, sqlTransactionScope))
{
//Set destination table name
bulkcopy.BulkCopyTimeout = 0;
bulkcopy.BatchSize = 1000;
bulkcopy.DestinationTableName = "[dbo].[cc_alertowner]";
try
{
foreach (DataColumn col in dtExcel.Columns)
{
bulkcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(col.ColumnName, col.ColumnName));
}
// bulkcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("", ""));
// bulkcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("DateCreated", "DateCreated"));
if (con.State == ConnectionState.Closed)
con.Open();
bulkcopy.WriteToServer(dtExcel);
sqlTransactionScope.Commit();
}
catch (Exception ex)
{
sqlTransactionScope.Rollback();
throw;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
看起来你的 SQL Server 混合模式身份验证已关闭。
右键单击数据库实例并选择属性。 点击安全性，在服务器身份验证中选择第二个单选按钮 SQL Server 和 Windows 身份验证模式。
完成后，请从 services.msc 中重新启动 SQL 服务。