none
SqlBulkCopy and Deleted Rows RRS feed

  • Question

  • Hi,

     

    I'm having a rather frustrating time trying to get the SqlBulkCopy class to work with deleted rows.

     

    I've read the post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177887&SiteID=1 and I am still struggling to make this work.

     

    I have a local table on a SQL2005 Express instance that contains a number of columns from from an SMS Server's PkgPrograms table (in SQL 2000). The example below contains the relevant pieces with some example lines I added to display the problem. Any assistance massively appreciated!

     

    Gareth

     

    Code below:

     

    Code Snippet

    SqlConnectionStringBuilder localString = new SqlConnectionStringBuilder();

    SqlConnection localConnection = new SqlConnection();

    SqlDataAdapter adapter = new SqlDataAdapter();

    DataSet dataSet = new DataSet();

     

    localString.DataSource = "(local)\\SQLEXPRESS";

    localString.InitialCatalog = "SMSDATA";

    localString.IntegratedSecurity = true;

    localConnection.ConnectionString = localString.ConnectionString;

    localConnection.Open();

     

    adapter.SelectCommand =

    new SqlCommand("SELECT PkgID, Name, ProgramID, binary_checksum(PkgID, Name, Comment, Description, DependentProgram, SourceSite, ProgramFlags, ProgramID) AS 'Hash' FROM PkgPrograms");

     

    adapter.SelectCommand.Connection = localConnection;

    dataSet.Tables.Add("Local");

    adapter.Fill(dataSet.Tables["Local"]);

    dataSet.Tables["Local"].PrimaryKey =

    new DataColumn[] { dataSet.Tables["Local"].Columns["PkgID"], dataSet.Tables["Local"].Columns["Name"] };

     

    SqlBulkCopy bulkCopier = new SqlBulkCopy(localConnection);

    bulkCopier.DestinationTableName = "PkgPrograms";

    bulkCopier.ColumnMappings.Add(new SqlBulkCopyColumnMapping("PkgID", "PkgID"));

    bulkCopier.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Name", "Name"));

    bulkCopier.ColumnMappings.Add(new SqlBulkCopyColumnMapping("ProgramID", "ProgramID"));

     

     

    // Example here; delete a row which I have added specifically to test this. Then try write to server.

    dataSet.Tables["Local"].Select("ProgramID = 99")[0].Delete();

    //dataSet.Tables["Local"].AcceptChanges(); // If I include this I get a PK constaints error on trying to INSERT the record.

    bulkCopier.WriteToServer(dataSet.Tables["Local"]);

    // Throws a DeletedRowInaccessibleException

    // In reality I would be passing a RowData[] object returned from a SELECT on the processed dataSet either which way it's producing the same result.

     

    Exception
    System.Data.DeletedRowInaccessibleException was unhandled
      Message="Deleted row information cannot be accessed through the row."
      Source="System.Data"
      StackTrace:
           at System.Data.DataRow.GetDefaultRecord()
           at System.Data.DataRow.get_ItemArray()
           at System.Data.SqlClient.SqlBulkCopy.ReadFromRowSource()
           at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
           at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
           at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
           ...

     

    Saturday, February 16, 2008 12:26 AM

Answers

  • Yes, unfortunately it looks like it's a bug. We have a bug entry for this issue in our bug tracking system and plan to make the fix available in the next .Net Framework 2.0 service pack release.

     

    In the meanwhile, you can work-around the issue by getting the unchanged/added/modified rows using Select() and then the overload of WriteToServer that takes a DataRow[].

    Wednesday, April 30, 2008 7:28 PM
    Moderator