SQLiteDataAdapter填写例外 [英] SQLiteDataAdapter Fill exception
问题描述
我试图用OleDb的CSV解析器从一个CSV文件中加载一些数据，并将其插入到一个SQLite数据库，但我得到一个异常与 OleDbAdapter.Fill 方法，它是令人沮丧的：
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:
类型的未处理的异常 System.Data.ConstraintException 出现在system.data.dll
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.
下面是源$ C $ C：
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();
}
}
在YahooTagsInfo.csv文件看起来是这样的：
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:
- 删除第一行中的CSV文件，因此它不会混淆了真实的数据。
- 更改TRUE / FALSE实时标志1/0。
- 在我试过1和2在一起（即去掉了第一线，改变了标志）。
所有这些东西帮助...
None of these things helped...
的一个限制是，TAGID应该是唯一的。下面是桌子的样子设计的看法：
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?
更新：
我改变了HDR财产 HDR =否到 HDR =是，现在，它不给我一个例外：
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""");
我以为，如果 HDR =否和我删除的标题（即第一行），那么它应该工作......奇怪的是它没有工作。在任何情况下，现在我不再得到例外。
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.
新的问题出现在这里：<一href="http://stackoverflow.com/questions/2711021/sqlitedataadapter-update-method-returning-0">http://stackoverflow.com/questions/2711021/sqlitedataadapter-update-method-returning-0
推荐答案
第一件事：IMO安放在交通运输结构键和制约因素，如数据集是一个坏主意。
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.
二：有你检查的DS，以确保CSV是越来越装？ VS内置了一个数据集的调试可视化工具 - 加载并悬停在变量名的DS后，只需设置一个破发点，点击小向下箭头，然后选择合适的可视化
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.
第三：我不认为你正在生成插入命令。调用Update之前，请检查InsertCommand.CommandText ..
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.
这是SQLiteDataAdapter构造函数，最终被调用的来源。请注意，没有命令生成器采用。你需要明确的InserCommand财产上的SQLiteDataAdapter使用SQLiteCommandBuilder设置，也许？
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屋！
