SqlBulkCopy with different collations

I need to migrate data from one DB to another. I choosed to use SqlBulkCopy,开发者_如何学C but have a problem with it, because source database has different collation than destination, so, I've got an exception:

System.InvalidOperationException: The locale id '1049' of the source column 'Id' and the locale id '1033' of the destination column 'Id' do not match.
   at System.Data.SqlClient.SqlBulkCopy.AnalyzeTargetAndCreateUpdateBulkCommand(BulkCopySimpleResultSet internalResults)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
   at MigrateToNormalized.DirectMapCommand.Migrate(SqlConnection source, SqlConnection destination, SqlTransaction transaction) in D:\Projects\APS\DTE\MigrateTo
Normalized\MigrateToNormalized\MigrateToNormalized\DirectMapCommand.cs:line 53
   at MigrateToNormalized.Program.Main(String[] args) in D:\Projects\APS\DTE\MigrateToNormalized\MigrateToNormalized\MigrateToNormalized\Program.cs:line 32

Can anyone tell me, how to resolve this issue without direct usage of COLLATE statements in SQL query? Is there some easy way to change collation for all columns in source database?

It is right that when we use SqlBulkCopy, sometimes it gives an error, the best way to map the columns when you are using SqlBulkCopy.

My Privious Code :

SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder("Data Source=ServerName;User Id=userid;Password=****;Initial Catalog=Deepak; Pooling=true; Max pool size=200; Min pool size=0");
SqlConnection con = new SqlConnection(cb.ConnectionString);
SqlCommand cmd = new SqlCommand("select Name,Class,Section,RollNo from Student", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
SqlBulkCopy sbc = new SqlBulkCopy("Data Source=DestinationServer;User Id=destinationserveruserid;Password=******;Initial Catalog=DeepakTransfer; Pooling=true; Max pool size=200; Min pool size=0");
sbc.DestinationTableName = "StudentTrans";   
sbc.WriteToServer(rdr); 
sbc.Close();
rdr.Close();
con.Close();

The Code Was giving me the Error as :

The locale id '0' of the source column 'RollNo' and the locale id '1033' of the destination column 'Section' do not match.

Now After Column Mapping my Code Is Running Successfully.

My Modified Code is :

SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder("Data Source=ServerName;User Id=userid;Password=****;Initial Catalog=Deepak;");
SqlConnection con = new SqlConnection(cb.ConnectionString);
SqlCommand cmd = new SqlCommand("select Name,Class,Section,RollNo from Student", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
SqlBulkCopy sbc = new SqlBulkCopy("Data Source=DestinationServer;User Id=destinationserveruserid;Password=******;Initial Catalog=DeepakTransfer;");
sbc.DestinationTableName = "StudentTrans";
sbc.ColumnMappings.Add("Name", "Name");
sbc.ColumnMappings.Add("Class", "Class");
sbc.ColumnMappings.Add("Section", "Section");
sbc.ColumnMappings.Add("RollNo", "RollNo");
sbc.WriteToServer(rdr);
sbc.Close();
rdr.Close();
con.Close();

This code is running Successfully.

You can select columns with different collation:

SELECT Foo COLLATE SQL_Latin1_General_CP1_CI_AS AS Bar FROM Baz

This will convert collation of column Foo to the new collation. In the example above, the column Foo is converted to collation SQL_Latin1_General_CP1_CI_AS and named as Bar in the query.

You then need to add columnmapping for your new column for your bulkcopy command:

using (var bulkCopy = new SqlBulkCopy(connection))
{
    bulkCopy.DestinationTableName = "FooBars";
    bulkCopy.ColumnMappings.Add("Bar", "FooBar"); 
    bulkCopy.WriteToServer(reader);
}

Simple adding column mapping didn't work for me. And I've implemented insert through SqlBulkCopy and DataTable - this one works fine.

private void BulkCopyTable(string sourceConnection, string targetConnection, Table sTable, Table tTable)
{
    using (SqlConnection sourceConn = new SqlConnection(sourceConnection))
    {
        if (cbFixStructure.Checked)
            CheckAndRecreateTarget(targetConnection, sTable, tTable);

        string selectSql = "SELECT * FROM " + sTable.Schema + ".[" + sTable.Name + "]";

        string selectCntSql = "SELECT COUNT(*) FROM " + sTable.Schema + ".[" + sTable.Name + "] WITH(NOLOCK)";
        using (SqlCommand selectCmd = new SqlCommand(selectSql, sourceConn))
        {
            selectCmd.CommandTimeout = 60 * 100 * 1000;
            sourceConn.Open();
            Int64 totalCount = 0;
            using (SqlCommand cntCommand = new SqlCommand(selectCntSql, sourceConn))
            {
                cntCommand.CommandTimeout = 60 * 100 * 1000;
                totalCount = Convert.ToInt64(cntCommand.ExecuteScalar());
            }

            DataTable dtBuffer = new DataTable();
            var columns = sTable.Columns.Cast<Column>().Where(p => p.Computed == false).ToList();
            foreach (var clm in columns)
            {
                var sdt = clm.DataType.SqlDataType;
                if (sdt == SqlDataType.UserDefinedDataType)
                {
                    var lst = Enum.GetValues(typeof(SqlDataType)).Cast<SqlDataType>();
                    sdt = lst.Where(p => p.ToString().ToLower() == TargetDataBase.UserDefinedDataTypes[clm.DataType.Name].SystemType.ToString()).First();
                }

                dtBuffer.Columns.Add(new DataColumn(clm.Name, GetClrType(sdt)));
            }
            using (SqlDataReader reader = selectCmd.ExecuteReader())
            {
                using (SqlBulkCopy blkCopy = new SqlBulkCopy(targetConnection, SqlBulkCopyOptions.KeepIdentity))
                {
                    blkCopy.BulkCopyTimeout = 60 * 100 * 1000;
                    blkCopy.DestinationTableName = sTable.Schema + ".[" + sTable.Name + "]";

                    foreach (var colmn in columns)
                    {
                        blkCopy.ColumnMappings.Add(colmn.Name, colmn.Name);
                    }

                    int bufferCountLengthMax = 500;
                    int rowCnt = 0;
                    int globalCounter = 0;
                    while (reader.Read())
                    {
                        var dataRow = dtBuffer.NewRow();
                        foreach (var clm in columns)
                        {
                            dataRow[clm.Name] = reader[clm.Name];
                        }
                        dtBuffer.Rows.Add(dataRow);
                        rowCnt++;
                        globalCounter++;
                        if (rowCnt >= bufferCountLengthMax)
                        {
                            dtBuffer.AcceptChanges();
                            blkCopy.WriteToServer(dtBuffer);
                            rowCnt = 0;
                            dtBuffer.Rows.Clear();
                            GC.Collect();
                            DoLogText(String.Format("Table \"{0}\" copied rows {1} out of {2}", sTable.Schema + ".[" + sTable.Name + "]", globalCounter, totalCount));
                        }
                    }
                    if (rowCnt > 0)
                    {
                        dtBuffer.AcceptChanges();
                        blkCopy.WriteToServer(dtBuffer);
                        rowCnt = 0;
                        dtBuffer.Rows.Clear();
                        GC.Collect();
                        DoLogText(String.Format("Table \"{0}\" copied rows {1} out of {2}", sTable.Schema + ".[" + sTable.Name + "]", globalCounter, totalCount));
                    }
                }
            }
        }
    }
    DoLogText(String.Format("Table \"{0}\" done", sTable.Name));
}

You can change the collations for the columns in the table you are using for the sqlbulkcopy.

For Example

CREATE TABLE T3
(
    C1 int PRIMARY KEY,
    C2 varchar(50) NULL,
    C3 int NULL,
    C4 int 
) ;
GO

ALTER TABLE T3 ALTER COLUMN C2 varchar(50) COLLATE Latin1_General_BIN