CODEWITHSUNDEEP
c#linqsystem.data.sqlite
WannabeCoder
WannabeCoder

Reputation: 77

Creating database using DataContext and System.Data.SQLite in C#

I'm writing simple application gathering information about machine's hardware. I plan to store data in sqlite. Got following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.IO;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace SQLiteTest
{
    public class MyDB : DataContext
    {
        public Table<Computer> kompy;
        public MyDB(string connection) : base(connection) { }
        public MyDB(IDbConnection connection) : base(connection) { }
    }


    [Table]
    public class Computer
    {
        [Column(IsPrimaryKey = true, CanBeNull = false)]
        public uint CompId;

        [Column]
        public uint CompanyId;

        [Column]
        public string CompName;
    }


    class Program
    {
        static void Main(string[] args)
        {
            string rootPath = Environment.CurrentDirectory;
            string dbPath = rootPath + "\\db.sqlite3";

            SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
            builder.Add("Data Source", dbPath);

            SQLiteConnection sqlcnn = new SQLiteConnection(builder.ConnectionString);
            MyDB db = new MyDB(sqlcnn);
            db.CreateDatabase();
            db.SubmitChanges();
            db.Dispose();            
        }
    }
}

Running this program produces exception:

Unhandled Exception: System.Data.SQLite.SQLiteException: SQLite error
near "DATABASE": syntax error
   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQ
LiteStatement previous, UInt32 timeoutMS, String& strRemain)
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavi
or behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   at System.Data.Linq.SqlClient.SqlProvider.ExecuteCommand(String command)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider
.CreateDatabase()
   at System.Data.Linq.DataContext.CreateDatabase()
   at SQLiteTest.Program.Main(String[] args) in C:\Users\bootch\documents\visual
 studio 2010\Projects\SQLiteTest\SQLiteTest\Program.cs:line 47

1) Why this code doesn't work, what am I missing?

2) Is there a way to get text version of underlying sql command to see what's wrong

3) If database doesn't exist I want create database and all tables. I'm lazy so I thought I can use types I created for running queries. Is it possible with System.Data.SQLite?

Thanks in advance, for comments and answers!

Upvotes: 4

Views: 9820

Answers (3)

Just_Alex
Just_Alex

Reputation: 558

I have found this to work for me.

            string fileDB = "Data Source=:memory:";
            Console.WriteLine(fileDB);
            SQLiteConnection conn = new SQLiteConnection(fileDB);
            DataContext db = new DataContext(conn);
            Console.WriteLine(db.Connection.ServerVersion);

Using a file, credits to this guy for the file path.

string fileDB = "Data Source=" + Directory.GetParent(Environment.CurrentDirectory).Parent.Parent.FullName +
                            @"\test.sqlite";

First check the company example of Linq2SQL here.

Upvotes: 0

aru
aru

Reputation: 788

I just went through this and tried to implement. Got same error, then found that we can create database using SQLiteConnection as so:

sqlcnn.CreateFile("MyDatabase.sqlite");

And then using "db" you can do all database operation. Try it.

Upvotes: 0

Luke Forder
Luke Forder

Reputation: 1189

Looks like you trying to use Linq2Sql with Sqlite which isn't supported by default (only SQL Server and SQL Server CE are supported), see this question for more details.

Have a look at using Entity Framework, which supports Sqlite, instead.

Upvotes: 2

Related Questions