SqlBulkInsert and Guid problem

I am currently importing large datasets from an ASCII file containing Guid. Example import data string:

35313532-3200-0000-0000-000000000000, PRT100, MYCORP ENTERPRISES, ...

The problem is the first column which is in the SQL server displayed as Guid. I get the following error: A given value of type String from the data source cannot be converted to the uniqueidentifier type of the specified target column.

I think the column suits me. Does anyone know how to import a Guid string representation into a real Guid?

Here the function in question:

private static void ImportBc(string sInputConnectionString,
                             string sOutputConnectionString,
                             string sInputTable,
                             string sOutputTable,
                             string[,] arrMap,
                             bool bDelete)
{

    Console.WriteLine("Import datoteke {0}......", sInputTable);

    if (bDelete)
    {
        var sqlCnn = new SqlConnection { ConnectionString = sOutputConnectionString };
        sqlCnn.Open();
        //var deleteCommand = new SqlCommand("TRUNCATE TABLE " + sOutputTable) { Connection = sqlCnn };
        var deleteCommand = new SqlCommand("DELETE FROM " + sOutputTable) { Connection = sqlCnn };
        deleteCommand.ExecuteNonQuery();
        sqlCnn.Close();
    }

    string oledbConnectionString = sInputConnectionString;

    var connection = new OleDbConnection(oledbConnectionString);




    var command = new OleDbCommand(sInputTable, connection);
    connection.Open();

    // Create DbDataReader 
    using (DbDataReader dr = command.ExecuteReader())
    {

        // SQL Server Connection String
        string sqlConnectionString = sOutputConnectionString;

        // Bulk Copy to SQL Server
        using (var bulkCopy = new SqlBulkCopy(sqlConnectionString))
        {

            bulkCopy.BatchSize = 10000;
            bulkCopy.NotifyAfter = 10000;
            bulkCopy.SqlRowsCopied += OnSqlRowsCopied;
            bulkCopy.DestinationTableName = sOutputTable;

            /* Zbog timeout expired problema */
            bulkCopy.BulkCopyTimeout = 9000;


            for (int i = 0; i < arrMap.GetLength(0); i++)
            {
                bulkCopy.ColumnMappings.Add(arrMap[i, 0], arrMap[i, 1]);
                Console.WriteLine("{0} {1}", arrMap[i, 0], arrMap[i, 1]);
            }



            bulkCopy.WriteToServer(dr);
        }
    }
}
+3
source share
2 answers

: "35313532-3200-0000-0000-000000000000", PRT100, MYCORP ENTERPRISES,...?

0

:

, ,

a) , , , :

 // Let fix tons of mapping issues by
 // Setting the column mapping in SqlBulkCopy instance:
 foreach (DataColumn dataColumn in _dataTable.Columns)
 {
     bulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName);
  }

b) KeepIdentity:

SqlBulkCopyOptions.KeepIdentity

, , ArrMap . Generic Bulk Insert? ( , .

, .

0

All Articles