CODEWITHSUNDEEP
c#sqlsql-serverdbf
Biggles
Biggles

Reputation: 1345

SqlBulkCopy WriteToServer throws an exception Record is deleted

I'm performing a conversion of DBF + DBT files into the SQL. I use Microsoft.Jet.OLEDB.4.0 connector for accessing the files and SqlConnector for writing data in the MS SQL, to improve performance I use SqlBulkCopy approach. Most of the files are converted fine, but on some the method SqlBulkCopy.WriteToServer throws an exception :

Record is deleted. The search key was not found in any record.

The copy operation doesn't complete and I'm missing lots of records in the SQL.

Is there a way how to bypass this problem, or am I suppose to give up on SqlBulkCopy and copy row after row?

EDIT: So I decided to PACK the database, however no luck so far. When I use vfpoledb for reading, it crashes even sooner because of some problem with casting decimals. So I'd like to use PACK first (with vfpoledb) and then the JetOleDb reader.Even though the PACK is executed,and I can see that the dbf and dbt files change, the reader.GetValues() is still throwing the same exception.

       try
        {
            string file = @"f:\Elims\dsm\CPAGEMET.DBF";
            string tableName = Path.GetFileNameWithoutExtension(file);
            var dbfConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='dBASE III;DELETED=YES;HDR=NO;IMEX=1'", Path.GetDirectoryName(file));

            var packConnString = string.Format(@"Provider=vfpoledb;Data Source={0};Collating Sequence=machine;", file);
            OleDbConnection packConnector = new OleDbConnection(packConnString);
            packConnector.Open();

            OleDbCommand command = new OleDbCommand(string.Format("PACK {0}",tableName), packConnector);
            var result = command.ExecuteNonQuery();
            packConnector.Close();


            OleDbConnection oleConnector = new OleDbConnection(dbfConnectionString);
            oleConnector.Open();

            string cmd = string.Format("SELECT * FROM [{0}]", tableName);
            var oleDbCommand = new OleDbCommand(cmd, oleConnector);
            OleDbDataReader dataReader = oleDbCommand.ExecuteReader();
            object[] values = new object[dataReader.FieldCount];
            int iRow = 0;
            while (dataReader.Read())
            {
                iRow++;
                Console.WriteLine("Row " + iRow);
                dataReader.GetValues(values);
            }
            oleConnector.Close();
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message + e.StackTrace);
        }

Thanks

Upvotes: 0

Views: 1025

Answers (2)

Biggles
Biggles

Reputation: 1345

So after some digging, I came out to final resolution, so I will just summarize my findings. The "record is deleted" exception was very misleading, as the problem never actually was a deleted record. There were 3 empty rows, that were actually triggering the exception. Once these got deleted, everything started to work, I didn't even have to pack the database. This applied to a scenario, where I used Microsoft.Jet.OLEDB connector.

I tried to use the vfpoledb connector, but I ran into other problem with decimal numbers. I wrote a fix inspired by this msdn discussion and not only everything started to work (so deleted and empty rows are successfully skipped), but also the import is now 15x faster than with the Jet connector (again using BulkCopy)

Upvotes: 0

Oleg
Oleg

Reputation: 823

It looks like some of the records are marked as deleted. Restore them back if you need these records, or delete them permanently (using PACK command) if you don't need these records.

Upvotes: 1

Related Questions