如何在内存数据库中的sqlite中创建表? [英] How to create tables in sqlite in memory databases ?
本文介绍了如何在内存数据库中的sqlite中创建表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想知道如何在内存数据库中创建sqlite表并存储数据。
我试过这个
SQLiteConnection con = new SQLiteConnection( @ < span class =code-string> Data Source =:memory:);
con.Open();
SQLiteCommand cmd = con.CreateCommand();
cmd.CommandText = @ CREATE TABLE [table]
(
[pkey] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[foo] TEXT NULL,
[bar] TEXT NULL
);
cmd.ExecuteNonQuery();
SQLiteCommand cmd1 = con.CreateCommand();
cmd1.CommandText = @ INSERT INTO table(foo,bar)
values(@_ foo ,@_ bar);
cmd1.Parameters.AddWithValue( @_ foo, Hello);
cmd1.Parameters.AddWithValue( @_ bar, World);
// cmd1.ExecuteNonQuery(); //取消注释错误
但是上次执行时的注释表示此操作因错误而停止。 />
这是错误。
< br />
未处理的异常:System.Data.SQLite.SQLiteException:SQL逻辑错误或未命中< br />
ing数据库< br />
靠近table:语法错误< br System.Data.SQLite.SQLite3.Prepare中的
(SQLiteConnection cnn,String strSql,SQ< br />
LiteStatement previous,UInt32 timeoutMS,String& strRemain),位于c:\ dev \ sqlite \do< br />
tnet \ System.Data.SQLite \ SQLite3.cs:第1052行< br />
在c中的System.Data.SQLite.SQLiteCommand.BuildNextCommand() :\ dev\sqlite\dotne< br />
t \ System.Data.SQLite \ SQLiteCommand.cs:第380行< br />
在System.Data.SQLite.SQLiteCommand。 GetStatement(Int32 index)在c:\dev \sqlit< br />
e \dotnet \ System.Data.SQLite \\ \\ SQLiteCommand.cs:第387行< br />
,位于c:\dev\sqlite\dotnet \ S< br />
中的System.Data.SQLite.SQLiteDataReader.NextResult() ystem.Data.SQLite \ SQLiteDataReader.cs:c中的System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd,CommandBehavi< br />
或表现)中的行1308< br />
:\ dev\sqlite \dotnet \ System.Data.SQLite \ SQLiteDataReader.cs:第1行< br />
17< br />
在System.Data.SQLite.SQLiteCommand .ExecuteReader(CommandBehavior behavior)i< br />
nc:\ dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:805行< br />
at System c:\\\\\\\\\\\\\\\\ />
at c:\ dev\sqlite\dotnet< br />
\System.Data.SQLite \\中的System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() \\ SQLiteCommand.cs:第838行< br />
在sqlite_test18.Program.Main(String [] args)中的d:\Visual Studio 2013 \ Project< br />
s \qlite test18 \ sqlite test18 \Program.cs:第34行< br />
按任意键继续。 。 。< br />
我的代码有什么问题?并且此代码适用于本地数据库而没有任何问题。
提前致谢!
解决方案
MS- SQL等似乎使用[],但SQLite既不使用也不理解[] ....
这里是SQLite.Net帮助中的一个快速示例
public static class 示例
{
public static void Main()
{
使用(SQLiteConnection connection = new SQLiteConnection(
数据源=:memory:;))
{
connection.Open();
connection.CreateModule( new SQLiteModuleEnumerable(
sampleModule, new string [] { one, two, three}));
使用(SQLiteCommand command = connection.CreateCommand())
{
command.CommandText =
CREATE VIRTUAL TABLE t1使用sampleModule;;
command.ExecuteNonQuery();
}
使用(SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = SELECT * FROM t1;;
使用(SQLiteDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
Console.WriteLine(dataReader [ 0 ]。ToString());
}
}
connection.Close();
}
}
}
所以你的SQL需要一些修改
I want to know how to create tables in sqlite in memory databases and store data.
I tried this
SQLiteConnection con = new SQLiteConnection(@"Data Source = :memory:");
con.Open();
SQLiteCommand cmd = con.CreateCommand();
cmd.CommandText = @"CREATE TABLE [table]
(
[pkey] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[foo] TEXT NULL,
[bar] TEXT NULL
)";
cmd.ExecuteNonQuery();
SQLiteCommand cmd1 = con.CreateCommand();
cmd1.CommandText = @" INSERT INTO table (foo, bar)
values(@_foo, @_bar)";
cmd1.Parameters.AddWithValue("@_foo", "Hello");
cmd1.Parameters.AddWithValue("@_bar", "World");
//cmd1.ExecuteNonQuery(); //uncomment for an error
But as the comment at the last execution says this stops with an error.
Here's the error.
<br /> Unhandled Exception: System.Data.SQLite.SQLiteException: SQL logic error or miss<br /> ing database<br /> near "table": syntax error<br /> at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQ<br /> LiteStatement previous, UInt32 timeoutMS, String& strRemain) in c:\dev\sqlite\do<br /> tnet\System.Data.SQLite\SQLite3.cs:line 1052<br /> at System.Data.SQLite.SQLiteCommand.BuildNextCommand() in c:\dev\sqlite\dotne<br /> t\System.Data.SQLite\SQLiteCommand.cs:line 380<br /> at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index) in c:\dev\sqlit<br /> e\dotnet\System.Data.SQLite\SQLiteCommand.cs:line 387<br /> at System.Data.SQLite.SQLiteDataReader.NextResult() in c:\dev\sqlite\dotnet\S<br /> ystem.Data.SQLite\SQLiteDataReader.cs:line 1308<br /> at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavi<br /> or behave) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteDataReader.cs:line 1<br /> 17<br /> at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) i<br /> n c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:line 805<br /> at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)<br /> in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:line 850<br /> at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() in c:\dev\sqlite\dotnet<br /> \System.Data.SQLite\SQLiteCommand.cs:line 838<br /> at sqlite_test18.Program.Main(String[] args) in d:\Visual Studio 2013\Project<br /> s\sqlite test18\sqlite test18\Program.cs:line 34<br /> Press any key to continue . . .<br />
What's the wrong with my code ? and this code works for local databases with no problems.
Thanks in advance !
解决方案
MS-SQL etc seem to use [], but SQLite neither uses or understands the [] ....
here's a quick example from the SQLite.Net help
public static class Sample { public static void Main() { using (SQLiteConnection connection = new SQLiteConnection( "Data Source=:memory:;")) { connection.Open(); connection.CreateModule(new SQLiteModuleEnumerable( "sampleModule", new string[] { "one", "two", "three" })); using (SQLiteCommand command = connection.CreateCommand()) { command.CommandText = "CREATE VIRTUAL TABLE t1 USING sampleModule;"; command.ExecuteNonQuery(); } using (SQLiteCommand command = connection.CreateCommand()) { command.CommandText = "SELECT * FROM t1;"; using (SQLiteDataReader dataReader = command.ExecuteReader()) { while (dataReader.Read()) Console.WriteLine(dataReader[0].ToString()); } } connection.Close(); } } }
SO your SQL will need a bit of modifying
这篇关于如何在内存数据库中的sqlite中创建表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文