SQLiteDataAdapter Fill exception

Question

I'm trying to use the OleDb CSV parser to load some data from a CSV file and insert it into a SQLite database, but I get an exception with the OleDbAdapter.Fill method and it's frustrating:

An unhandled exception of type 'System.Data.ConstraintException' occurred in System.Data.dll

Additional information: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Here is the source code:

public void InsertData(String csvFileName, String tableName)
{
    String dir = Path.GetDirectoryName(csvFileName);
    String name = Path.GetFileName(csvFileName);

    using (OleDbConnection conn =
        new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
        dir + @";Extended Properties=""Text;HDR=No;FMT=Delimited"""))
    {
        conn.Open();
        using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + name, conn))
        {
            QuoteDataSet ds = new QuoteDataSet();
            adapter.Fill(ds, tableName); // <-- Exception here
            InsertData(ds, tableName); // <-- Inserts the data into the my SQLite db
        }
    }
}

class Program
{
    static void Main(string[] args)
    {
        SQLiteDatabase target = new SQLiteDatabase(); 
        string csvFileName = "D:\\Innovations\\Finch\\dev\\DataFeed\\YahooTagsInfo.csv"; 
        string tableName = "Tags";
        target.InsertData(csvFileName, tableName);

        Console.ReadKey();
    }
}

The "YahooTagsInfo.csv" file looks like this:

tagId,tagName,description,colName,dataType,realTime
1,s,Symbol,symbol,VARCHAR,FALSE
2,c8,After Hours Change,afterhours,DOUBLE,TRUE
3,g3,Annualized Gain,annualizedGain,DOUBLE,FALSE
4,a,Ask,ask,DOUBLE,FALSE
5,a5,Ask Size,askSize,DOUBLE,FALSE
6,a2,Average Daily Volume,avgDailyVolume,DOUBLE,FALSE
7,b,Bid,bid,DOUBLE,FALSE
8,b6,Bid Size,bidSize,DOUBLE,FALSE
9,b4,Book Value,bookValue,DOUBLE,FALSE

I've tried the following:

1. Removing the first line in the CSV file so it doesn't confuse it for real data.
2. Changing the TRUE/FALSE realTime flag to 1/0.
3. I've tried 1 and 2 together (i.e. removed the first line and changed the flag).

None of these things helped...

One constraint is that the tagId is supposed to be unique. Here is what the table look like in design view:

Can anybody help me figure out what is the problem here?

Update:
I changed the HDR property from HDR=No to HDR=Yes and now it doesn't give me an exception:

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + @";Extended Properties=""Text;HDR=Yes;FMT=Delimited""");

I assumed that if HDR=No and I removed the header (i.e. first line), then it should work... strangely it didn't work. In any case, now I'm no longer getting the exception.

The new problem arose here: http://stackoverflow.com/questions/2711021/sqlitedataadapter-update-method-returning-0

Answer 1

I changed the HDR property from HDR=No to HDR=Yes and now it doesn't give me an exception anymore... here is what the connection string looks like:

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + @";Extended Properties=""Text;HDR=Yes;FMT=Delimited""");

I assumed that if HDR=No and I removed the header (i.e. first line), then it should work... the only way to get rid of the exception if HDR=No is if I remove the constraints by calling ds.EnforceConstraints = false;

In any case, now I'm no longer getting the exception. If anybody can explain why this is happening, then please do tell.

Answer 2

First thing: IMO emplacing keys and constraints on a transport structure such as your dataset is a bad idea.

Use a transaction and let the target db throw if it wants.

Second: have you examined the ds to ensure the csv is getting loaded? VS has a dataset debug visualizer built in - simply set a break point after the ds is loaded and hover over the variable name, click the little down-arrow and select the appropriate visualizer.

Third: I don't think that you are generating an insert command. Just before you call update, check the InsertCommand.CommandText..

var cmdText = sqliteAdapter.InsertCommand.CommandText;

I think you will find that it is empty.

This is the source of the SQLiteDataAdapter ctor that ultimately gets called. Note that no command builder is employed. You need to explicitly set the InserCommand property on the SQLiteDataAdapter, perhaps by using a SQLiteCommandBuilder?

public SQLiteDataAdapter(string commandText, SQLiteConnection connection)
{
    this.SelectCommand = new SQLiteCommand(commandText, connection);
}