Click here to Skip to main content
15,848,349 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to know how to create tables in sqlite in memory databases and store data.

I tried this

C#
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 !
Posted
Updated 26-Sep-14 21:18pm
v3

1 solution

MS-SQL etc seem to use [], but SQLite neither uses or understands the [] ....

here's a quick example from the SQLite.Net help

C#
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
 
Share this answer
 
Comments
M­­ar­­­­k 27-Sep-14 6:16am    
well. thanks..
and how can I create some columns inside that virtual table ?
And do all the tables in sqlite memory database must be virtual tables ?
Garth J Lancaster 27-Sep-14 7:31am    
I think you need to go here http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki, download the help file & read the chapter 'Query Language Understood by SQLite' -> CREATE Virtual Tables reference - it does a better job that I would - I know the example I posted showed virtual tables, but (after a quick skim-read) they don't have to be - that example was more to illustrate the non-use of [] in SQLite SQL than 'normal' tables vs 'virtual' tables
M­­ar­­­­k 27-Sep-14 8:18am    
I read that. It's not descriptive enough :(
Any reference with some good examples ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900