SqlBulkCopy登录失败

Question

我在使用Server身份验证时遇到了SqlBulkCopy命令的问题。Windows身份验证不会出现此问题。

SqlBulkCopy sbc = new SqlBulkCopy(sqConn.ConnectionString, SqlBulkCopyOptions.KeepIdentity);

这会引发一个错误：

用户“xx”登录失败

代码：

SqlBulkCopy sbc = new SqlBulkCopy(sqConn);

这很好，但不保留标识列的原始值。

Answer 1

解决方案非常简单，但我仍然感兴趣的是，为什么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();
            }

Answer 2

连接字符串中需要"persist security info=true"。否则，如果连接已经打开，密码将从sqlConn.ConnectionString中删除。

Answer 3

，试试看，它对我有用，

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;
            }
        }