C# BulkCopy, DBF Errors (Timout, & Provider could not determine...)

bulkinsert c# foxpro smo sqlbulkcopy

Question

I've written a small console app that I point to a folder containing DBF/FoxPo files.

It then creates a table in SQL based on each dbf table, then does a bulk copy to insert the data into SQL. It works quite well for the most part, except for a few snags..

1) Some of the FoxPro tables contain 5000000+ records and the connection expries before the insert completes..

Here is my connection string:

<add name="SQL" connectionString="data source=source_source;persist security info=True;user id=DBFToSQL;password=DBFToSQL;Connection Timeout=20000;Max Pool Size=200" providerName="System.Data.SqlClient" />

Error message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

CODE:

using (SqlConnection SQLConn = new SqlConnection(SQLString))
using (OleDbConnection FPConn = new OleDbConnection(FoxString))
{
    ServerConnection srvConn = new Microsoft.SqlServer.Management.Common.ServerConnection(SQLConn);
    try
    {
        FPConn.Open();                       
        string dataString = String.Format("Select * from {0}", tableName);

        using (OleDbCommand Command = new OleDbCommand(dataString, FPConn))
        using (OleDbDataReader Reader = Command.ExecuteReader(CommandBehavior.SequentialAccess))
        {                       
            tbl = new Table(database, tableName, "schema");

            for (int i = 0; i < Reader.FieldCount; i++)
            {                           
                col = new Column(tbl, Reader.GetName(i), ConvertTypeToDataType(Reader.GetFieldType(i)));
                col.Nullable = true;
                tbl.Columns.Add(col);                       
            }

            tbl.Create();                       
            BulkCopy(Reader, tableName);
        }                   
    }
    catch (Exception ex)
    {
       // LogText(ex, @"C:\LoadTable_Errors.txt", tableName);
        throw ex;
    }
    finally
    {
        SQLConn.Close();
        srvConn.Disconnect();
    }
}

private DataType ConvertTypeToDataType(Type type)
{
    switch (type.ToString())
    {
        case "System.Decimal":
            return DataType.Decimal(18, 38);
        case "System.String":
            return DataType.NVarCharMax;
        case "System.Int32":
            return DataType.Int;
        case "System.DateTime":
            return DataType.DateTime;
        case "System.Boolean":
            return DataType.Bit;
        default:
            throw new NotImplementedException("ConvertTypeToDataType Not implemented for type : " + type.ToString());
    }
}

 private void BulkCopy(OleDbDataReader reader, string tableName)
{
    using (SqlConnection SQLConn = new SqlConnection(SQLString))
    {       
        SQLConn.Open();
        SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLConn);

        bulkCopy.DestinationTableName = "schema." + tableName;

        try
        {
            bulkCopy.WriteToServer(reader);         
        }
        catch (Exception ex)
        {           
            //LogText(ex, @"C:\BulkCopy_Errors.txt", tableName);
        }
        finally
        {
            SQLConn.Close();
            reader.Close();
        }
    }
}

My 2nd & 3rd errors are the following:

I understand what the issues are, but how to rectify them i'm not so sure

2) "The provider could not determine the Decimal value. For example, the row was just created, the default for the Decimal column was not available, and the consumer had not yet set a new Decimal value."

3) SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

I found a result on google that indicated what the issue is : [A]... and a possible work around [B] (but I'd like to keep my decimal values as decimal, and dates as date, as I'll be doing further calculations against the data)

What I'm wanting to do as a solution

1.) Either increase the connection time, (but i dont think i can increase it any more than i have), or alternatively is it possible to split the OleDbDataReader's results and do in incremental bulk insert?

2.)I was thinking if its possible to have bulk copy to ignore results with errors, or have the records that do error out log to a csv file or something to that extent?

Accepted Answer

So where you do the "for" statement I would probably break it up to take so many at a time :

int i = 0;
int MaxCount = 1000;

while (i < Reader.FieldCount)
{
    var tbl = new Table(database, tableName, "schema"); 

    for (int j = i; j < MaxCount; j++) 
    {                            
        col = new Column(tbl, Reader.GetName(j), ConvertTypeToDataType(Reader.GetFieldType(j))); 
        col.Nullable = true; 
        tbl.Columns.Add(col);
        i++;                      
    } 

    tbl.Create();                        
    BulkCopy(Reader, tableName); 
}

So, "i" keeps track of the overall count, "j" keeps track of the incremental count (ie your max at one time count) and when you have created your 'batch', you create the table and Bulk Copy it.

Does that look like what you would expect?

Cheers,
Chris.


Popular Answer

This is my current attemt at the bulk copy method, I't works for about 90% of the tables, but i get an OutOfMemory exeption, with the bigger tables... I'd like to split the reader's data into smaller secions, without having to pass it into a DataTable and store it in memory first (which is the cause of the OutOfMemory exception on the bigger result sets)

UPDATE

Imodified the code below as to how it looks in my solution.. It aint pretty.. but it works. I'll def do some refactoring, and update my answer again.

    private void BulkCopy(OleDbDataReader reader, string tableName, Table table)
    {
        Console.WriteLine(tableName + " BulkCopy Started.");
        try
        {
            DataTable tbl = new DataTable();
            List<Type> typeList = new List<Type>();
            foreach (Column col in table.Columns)
            {
                tbl.Columns.Add(col.Name, ConvertDataTypeToType(col.DataType));
                typeList.Add(ConvertDataTypeToType(col.DataType));
            }

            int batch = 1;
            int counter = 0;

            DataRow tblRow = tbl.NewRow();

            while (reader.Read())
            {
                counter++;
                int colcounter = 0;
                foreach (Column col in table.Columns)
                {
                    try
                    {
                        tblRow[colcounter] = reader[colcounter];
                    }
                    catch (Exception)
                    {
                        tblRow[colcounter] = GetDefault(typeList[0]);
                    }
                    colcounter++;
                }

                tbl.LoadDataRow(tblRow.ItemArray, true);

                if (counter == BulkInsertIncrement)
                {
                    Console.WriteLine(tableName + " :: Batch >> " + batch);
                    counter = PerformInsert(tableName, tbl, batch);
                    batch++;
                }
            }

            if (counter > 0)
            {
                Console.WriteLine(tableName + " :: Batch >> " + batch);
                PerformInsert(tableName, tbl, counter);
            }

            tbl = null;
            Console.WriteLine("BulkCopy Success!");
        }
        catch (Exception ex)
        {
            Console.WriteLine("BulkCopy Fail!");
            SharedLogger.Write(ex, @"C:\BulkCopy_Errors.txt", tableName);
            Console.WriteLine(ex.Message);
        }
        finally
        {
            reader.Close();
            reader.Dispose();

        }
        Console.WriteLine(tableName + " BulkCopy Ended.");
        Console.WriteLine("*****");
        Console.WriteLine("");
    }


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why