SQLiteDataAdapter填充异常 [英] SQLiteDataAdapter Fill exception
问题描述
我正在尝试使用OleDb CSV解析器从CSV文件中加载一些数据并将其插入到SQLite数据库中，但是我收到了一个例外，其中包含 OleDbAdapter.Fill 方法，令人沮丧：
一个未处理的类型为
'b'System.Data.ConstraintException'的异常'
在System.Data.dll中
附加信息：
启用限制失败。一行或多行
包含违反非空值，
唯一或外键约束的值。
这是源代码：
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 + @;扩展属性=文本; HDR =否; FMT =分隔））
{
conn.Open（）;
using（OleDbDataAdapter adapter = new OleDbDataAdapter（SELECT * FROM+ name，conn））
{
QuoteDataSet ds = new QuoteDataSet（）;
adapter.Fill（ds，tableName）; //< - Exception here
InsertData（ds，tableName）; //< - 将数据插入我的SQLite db
}
}
}
类程序
{
static void Main（string [] args）
{
SQLiteDatabase target = new SQLiteDatabase（）;
string csvFileName =D：\\Innovations\\Finch\\dev\\DataFeed\\YahooTagsInfo.csv;
string tableName =标签;
target.InsertData（csvFileName，tableName）;
Console.ReadKey（）;
}
}
YahooTagsInfo.csv文件如下所示：
tagId，tagName，description，colName，dataType，realTime
1，s，符号，符号，VARCHAR，FALSE
2，c8，小时变化，后期，DOUBLE，TRUE
3，g3，年化增益，年化增益，DOUBLE，FALSE
4，a，问，问，DOUBLE，FALSE
5，a5，询问大小，askSize，DOUBLE，FALSE
6，a2，平均每日量，平均每日收入，DOUBLE，FALSE
7，b，出价，出价，双重，FALSE
8，b6，Bid Size，bidSize，DOUBLE，FALSE
9，b4，Book Value，bookValue，DOUBLE，FALSE
我尝试过以下操作：
- 删除CSV文件中的第一行，以免混淆
- 将TRUE / FALSE realTime标志更改为1/0。
- 我一起尝试了1和2（即删除了第一行更改了标志）
这些事情都没有帮助...
一个约束是tagId应该是唯一的。这是设计视图中的表格：
有人可以帮我弄清楚这里有什么问题吗？
更新：
将HDR属性从 HDR = No 更改为 HDR = Yes 现在它不给我一个例外：
OleDbConnection conn = new OleDbConnection = Microsoft.Jet.OLEDB.4.0; Data Source =+ dir + @;扩展属性=文本; HDR =是; FMT =分隔）;
我假设如果 HDR =否我删除了标题（即第一行），然后它应该工作...奇怪的是它没有工作。在任何情况下，现在我不再得到例外。
新问题出现在这里： SQLiteDataAdapter更新方法返回0
IMO对运输结构（如数据集）的关键和约束是一个坏主意。
使用一个事务，让目标数据库抛出，如果它需要。
第二：你检查了以确保csv正在加载？ VS具有内置的数据集调试可视化程序 - 只需在ds加载并将鼠标悬停在变量名称之后设置断点，单击小的向下箭头并选择适当的可视化程序。
第三：我不认为你正在生成一个插入命令。在调用更新之前，请检查InsertCommand.CommandText ..
var cmdText = sqliteAdapter.InsertCommand.CommandText;
我想你会发现它是空的。
这是最终被调用的SQLiteDataAdapter ctor的源代码。请注意，没有使用命令生成器。您需要在SQLiteDataAdapter上显式设置InserCommand属性，可能是使用SQLiteCommandBuilder？
public SQLiteDataAdapter（string commandText，SQLiteConnection connection ）
{
this.SelectCommand = new SQLiteCommand（commandText，connection）;
}
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 arose here: SQLiteDataAdapter Update method returning 0
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);
}
这篇关于SQLiteDataAdapter填充异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
