Have Geek Brain

Run SQlite Command with Entity Framework

I am using SQLite database and Entity Framework (with .net framework 3.5). I am trying to execute a simple SQL command without a query to create a new table in this database. My Entity Framework already contains an object model for this table: I just want to generate the corresponding table using the command.

(By the way, maybe a better way to do this. Any ideas someone :)

My problem is that I cannot execute any command, not even simple commands.

Here is my code:

EntityConnection entityConnection = new EntityConnection(entitiesConnectionString);
Entities db = new Entities(entityConnection);

DbCommand command = db.Connection.CreateCommand();
command.CommandText ="CREATE TABLE MyTable (Id int NOT NULL, OtherTable_Id nchar(40)         REFERENCES OtherTable (Id) On Delete CASCADE On Update NO ACTION, SomeData nvarchar(1024) NOT NULL, Primary Key(Id) );";
command.ExecuteNonQuery();

I got this error:

System.Data.EntitySqlException: The query syntax is not valid., near identifier 'TABLE', line 1, column 8.
   at System.Data.Common.EntitySql.CqlParser.yyerror(String s)
   at System.Data.Common.EntitySql.CqlParser.yyparse()
   at System.Data.Common.EntitySql.CqlParser.Parse(String query)
   at System.Data.Common.EntitySql.CqlQuery.Parse(String query, ParserOptions parserOptions)
   at System.Data.Common.EntitySql.CqlQuery.Compile(String query, Perspective perspective, ParserOptions parserOptions, Dictionary`2 parameters, Dictionary`2 variables, Boolean validateTree)
   at System.Data.EntityClient.EntityCommand.MakeCommandTree()
   at System.Data.EntityClient.EntityCommand.CreateCommandDefinition()
   at System.Data.EntityClient.EntityCommand.TryGetEntityCommandDefinitionFromQueryCache(EntityCommandDefinition& entityCommandDefinition)
   at System.Data.EntityClient.EntityCommand.GetCommandDefinition()
   at System.Data.EntityClient.EntityCommand.InnerPrepare()
   at System.Data.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.EntityClient.EntityCommand.ExecuteScalar[T_Result](Func`2 resultSelector)

It seems to be a syntax error, but I can't figure out where the problem is and how to solve it. The EntityConnection is ok because I can use any objects generated with EF.

I tried with another simple command, but that threw another exception:

DbCommand command = db.Connection.CreateCommand();
command.CommandText = "SELECT COUNT(Id) From OtherTable;";
int result = (int)command.ExecuteScalar();

And I got this error because it is not the same, but it might help:

System.Data.EntitySqlException: 'Groupe' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly., near simple identifier, line 1, column 23.
   at System.Data.Common.EntitySql.CqlErrorHelper.ReportIdentifierError(Expr expr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertIdentifier(Expr expr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Expr astExpr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ProcessAliasedFromClauseItem(AliasExpr aliasedExpr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClauseItem(FromClauseItem fromClauseItem, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ProcessFromClause(FromClause fromClause, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertQuery(Expr expr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.Convert(Expr astExpr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertRootExpression(Expr astExpr, SemanticResolver sr)
   at System.Data.Common.EntitySql.SemanticAnalyzer.ConvertGeneralExpression(Expr astExpr, SemanticResolver sr)
   at System.Data.Common.EntitySql.CqlQuery.AnalyzeSemantics(Expr astExpr, Perspective perspective, ParserOptions parserOptions, Dictionary`2 parameters, Dictionary`2 variables)
   at System.Data.Common.EntitySql.CqlQuery.Compile(String query, Perspective perspective, ParserOptions parserOptions, Dictionary`2 parameters, Dictionary`2 variables, Boolean validateTree)
   at System.Data.EntityClient.EntityCommand.MakeCommandTree()
   at System.Data.EntityClient.EntityCommand.CreateCommandDefinition()
   at System.Data.EntityClient.EntityCommand.TryGetEntityCommandDefinitionFromQueryCache(EntityCommandDefinition& entityCommandDefinition)
   at System.Data.EntityClient.EntityCommand.GetCommandDefinition()
   at System.Data.EntityClient.EntityCommand.InnerPrepare()
   at System.Data.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.EntityClient.EntityCommand.ExecuteScalar[T_Result](Func`2 resultSelector)
+2


a source to share


3 answers


I think the problem might be that you are using EnitityConnection. Have you tried using OleDbConnection or OdbcDbConnection?

OdbcDbConnection entityConnection = new OdbcDbConnection(entitiesConnectionString); 
Entities db = new Entities(entityConnection); 

 DbCommand command = db.Connection.CreateCommand(); 
command.CommandText ="CREATE TABLE MyTable (Id int NOT NULL, OtherTable_Id nchar(40)         REFERENCES OtherTable (Id) On Delete CASCADE On Update NO ACTION, SomeData nvarchar(1024) NOT NULL, Primary Key(Id) );"; 
command.ExecuteNonQuery();


There is also an ADO.Net provider for SqLite here: http://sourceforge.net/projects/sqlite-dotnet2/ I haven't used it, but it looks like you can use:

SQLiteConnection cnn = new SQLiteConnection(entitiesConnectionString);
+2


a source


Thanks for your answers, you saved my day :)

I wanted to limit the number of connections and I used the current EntityConnection to check the database before trying to modify it. But I tried this code and now it works fine:



SQLiteConnection connection = new SQLiteConnection(sqliteConnString);
connection.Open();

DataRow[] result = connection.GetSchema("Tables").Select("Table_Name = 'MyTable'");
if (result == null || result.Length == 0)
{                  
    SQLiteCommand cmd = new SQLiteCommand(
                        "CREATE TABLE MyTable (Id int NOT NULL, OtherTable_Id nchar(40) REFERENCES OtherTable (Id) On Delete CASCADE On Update NO ACTION, SomeData nvarchar(1024) NOT NULL, Primary Key(Id) );"
    , connection);

    cmd.ExecuteNonQuery();
}

connection.Close();
+1


a source


Why are you writing SQL commands against Entity collection? If you want to do this, use ADO.NET components for SQLite, otherwise use LINQ to Entities to query data from your SQLite database.

0


a source






More articles:


All Articles