使用System.Data.Linq.Mapping错误和经销商的递增在一个SQLite数据库中的主键 [英] Error using System.Data.Linq.Mapping and auto incrementing the primary key in a sqlite db
问题描述
我用的SQLite
和 System.Data.Linq.Mapping
。我使用LINQ映射属性时,有与 ID
AUTOINCREMENT
字段问题 IsDbGenerated = TRUE
。
语法创建我的表。我试过这个带/不带 AUTOINCREMENT
CREATE TABLE [ TestTable的]([ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,[标题] TEXT NULL)
我的表类:
[表(NAME =TestTable的)]
公共类TestTable的
{
[列(IsPrimaryKey = TRUE,IsDbGenerated = TRUE)]
公众诠释ID {搞定;组; }
[专栏]
公共字符串的标题{搞定;组; }
}
下面是我如何调用它。当它提交我得到一个错误,我会粘贴下面这个例子中的错误。需要注意的一点是,如果我拿出 IsDbGenerated = TRUE
上方,并输入 ID
手动它插入精细,但我想它喜欢 AUTOINCREMENT
键,出于某种原因, IsDbGenerated = TRUE
是杀插件。寻找一些指导。
静态无效的主要(字串[] args)
{
字符串的connectionString = @DbLinqProvider = sqlite的;数据源= C:\pathToDB\test.s3db
SQLiteConnection连接=新SQLiteConnection(的connectionString);
DataContext的DB =新的DataContext(连接);
db.Log =新System.IO.StreamWriter(@C:\pathToDB\mylog.log){自动冲洗= TRUE};
VAR COM = db.GetTable<&TestTable的GT;();
com.InsertOnSubmit(新TestTable的{标题=asdf2});
尝试{
db.SubmitChanges();
}
赶上(SQLiteException E)
{
Console.WriteLine(e.Data.ToString());
Console.WriteLine(e.ErrorCode);
Console.WriteLine(e.HelpLink);
Console.WriteLine(e.InnerException);
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
Console.WriteLine(e.TargetSite);
Console.WriteLine(e.ToString());
}
的foreach(在COM VAR TestTable的)
{
Console.WriteLine(TestTable的:{0} {1},TestTable.id,TestTable.title);
}
Console.ReadKey();
}
错误消息:
< BLOCKQUOTE>
SQL逻辑错误或丢失database\r\\\
near \SELECT\:语法错误
块引用>
堆栈跟踪:
在System.Data.SQLite.SQLite3.Prepare(CNN SQLiteConnection,字符串
STRSQL, SQLiteStatement以前,UInt32的timeoutMS,字符串&安培;在
System.Data这
strRemain)\r\\\
在
System.Data.SQLite.SQLiteCommand.BuildNextCommand()\r\\\
.SQLite.SQLiteCommand.GetStatement(的Int32指数)\r\\\
在
System.Data.SQLite.SQLiteDataReader.NextResult()\r\\\
在
System.Data.SQLite.SQLiteDataReader ..ctor(SQLiteCommand CMD,
的CommandBehavior的行为)\r\\\
在
System.Data.SQLite.SQLiteCommand.ExecuteReader(的CommandBehavior
的行为)在$ \r\\\
b $ b System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(的CommandBehavior
的行为)\r\\\
在System.Data.Common.DbCommand.ExecuteReader()\r\\\
结果
。在System.Data.Linq.SqlClient.SqlProvider.Execute(表达式查询,
queryInfo queryInfo,IObjectReaderFactory厂,对象[]
parentArgs,对象[] userArgs,ICompiledSubQuery []子查询对象
的lastResult)\r\\\
在
System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(表达式查询,
QueryInfo [] queryInfos,IObjectReaderFactory厂,对象[]
userArguments, ICompiledSubQuery []子查询)\r\\\
在
System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(表达式
查询)\r\北,
System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject
项)\r\\\
在
System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject
产品)\r\\\
在
System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode
failureMode)\r\\\
在
将System.Data.Linq。在System.Data.Linq.DataContext.SubmitChanges DataContext.SubmitChanges(ConflictMode
failureMode)\r\\\
()在SqlLinq.Program.Main \r\\\
(字串[] args )在Program.cs中:第29行
块引用>
下面是我所看到的日志输出:
INSERT INTO [公司]([标题])
值(@ P0)
选择CONVERT(智力, SCOPE_IDENTITY())AS [值]
- @ P0:输入字符串(大小= 4000; PREC = 0;规模= 0)[asdf2]
- 语境。sqlProvider的(SQL2008)型号:AttributedMetaModel体形:4.6.81.0
选择[T0] [ID],[T0] [称号]
从[公司] AS [T0]
- 上下文:sqlProvider的(SQL2008)型号:AttributedMetaModel体形:4.6.81.0
解决方案据SQLite的文档(的宣布INTEGER PRIMARY KEY将自动增量。列)只是删除
AUTOINCREMENT
在你创建表,写整数主键
就足够了。 的SQLite会自动增加你的IDS
的
sqlite_cmd.CommandText =CREATE TABLE [TestTable的]([ID] INTEGER PRIMARY KEY NOT NULL,[标题] TEXT);
此外,你不需要设置
IsDbGenerated = TRUE
在TestTable的
上课不进入ID
手动,它只是通过插入插入细标题
:com.InsertOnSubmit(新TestTable的{标题=asdf2 }); //将自动递增ID。
编辑:的您
TestTable的
应该是现在这个样子:[表(NAME =TestTable的)]
公共类TestTable的
{
[列(IsPrimaryKey =真)]
公众诠释? ID {搞定;组; }
[专栏]
公共字符串的标题{搞定;组; }
}
结果在SQLite的经理:
I'm using
SQLite
andSystem.Data.Linq.Mapping
. I'm having an issue with theid
AUTOINCREMENT
field when using the linq mapping attributeIsDbGenerated = true
.Syntax to create my table. I've tried this with/without the
AUTOINCREMENT
CREATE TABLE [TestTable] ([id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,[title] TEXT NULL)
My TABLE class:
[Table(Name = "TestTable")] public class TestTable { [Column(IsPrimaryKey = true, IsDbGenerated =true)] public int id { get; set; } [Column] public string title { get; set; } }
Here's how I call it. When it goes to submit I get a error, I'll paste the error below this example. One thing to note is if I take out the
IsDbGenerated =true
above and enter theid
manually it does insert fine, but I'd like it toAUTOINCREMENT
and for some reason theIsDbGenerated=true
is killing the insert. Looking for some guidance.static void Main(string[] args) { string connectionString = @"DbLinqProvider=Sqlite;Data Source = c:\pathToDB\test.s3db"; SQLiteConnection connection = new SQLiteConnection(connectionString); DataContext db = new DataContext(connection); db.Log = new System.IO.StreamWriter(@"c:\pathToDB\mylog.log") { AutoFlush = true }; var com = db.GetTable<TestTable>(); com.InsertOnSubmit(new TestTable {title = "asdf2" }); try { db.SubmitChanges(); } catch(SQLiteException e) { Console.WriteLine(e.Data.ToString()); Console.WriteLine(e.ErrorCode); Console.WriteLine(e.HelpLink); Console.WriteLine(e.InnerException); Console.WriteLine(e.Message); Console.WriteLine(e.StackTrace); Console.WriteLine(e.TargetSite); Console.WriteLine(e.ToString()); } foreach (var TestTable in com) { Console.WriteLine("TestTable: {0} {1}", TestTable.id, TestTable.title); } Console.ReadKey(); }
Error message:
SQL logic error or missing database\r\nnear \"SELECT\": syntax error
Stack trace:
at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)\r\n at System.Data.SQLite.SQLiteCommand.BuildNextCommand()\r\n at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)\r\n at System.Data.SQLite.SQLiteDataReader.NextResult()\r\n at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)\r\n at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n at System.Data.Common.DbCommand.ExecuteReader()\r\n
at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)\r\n at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)\r\n at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)\r\n at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item)\r\n at System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject item)\r\n at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)\r\n at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)\r\n at System.Data.Linq.DataContext.SubmitChanges()\r\n at SqlLinq.Program.Main(String[] args) in Program.cs:line 29"Here is what I'm seeing in the log output:
INSERT INTO [company]([title]) VALUES (@p0) SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value] -- @p0: Input String (Size = 4000; Prec = 0; Scale = 0) [asdf2] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.6.81.0 SELECT [t0].[id], [t0].[title] FROM [company] AS [t0] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.6.81.0
解决方案According to the SQLite documentation (A column declared INTEGER PRIMARY KEY will AUTOINCREMENT.) just remove
AUTOINCREMENT
in your Table creation, Writinginteger primary key
is enough. SQLite will automatically increment yourids
:sqlite_cmd.CommandText = "CREATE TABLE [TestTable] ([id] INTEGER PRIMARY KEY NOT NULL , [title] TEXT)";
Also you don't need to set
IsDbGenerated = true
in yourTestTable
class and don't enter theid
manually, it does insert fine just by insertingtitle
:com.InsertOnSubmit(new TestTable { title = "asdf2" });//will automatically increment id.
Edit: Your
TestTable
should look like this now:[Table(Name = "TestTable")] public class TestTable { [Column(IsPrimaryKey = true)] public int? id { get; set; } [Column] public string title { get; set; } }
The result in SQLite Manager:
这篇关于使用System.Data.Linq.Mapping错误和经销商的递增在一个SQLite数据库中的主键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!