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:
Removing the first line in the CSV file so it doesn't confuse it for real data.
Changing the TRUE/FALSE realTime flag to 1/0.
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 is here:
public void InsertData(QuoteDataSet data, String tableName)
{
using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
{
conn.Open();
using (SQLiteDataAdapter sqliteAdapter = new SQLiteDataAdapter("SELECT * FROM " + tableName, conn))
{
Console.WriteLine("Num rows updated is " + sqliteAdapter.Update(data, tableName));
}
}
}
Now the Num rows updated is 0... any hints?
