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

Question

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!

Community · Accepted Answer · 2017-05-23 12:31:40Z

2

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.

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Feb 29, 2012 at 9:18

Luke Forder

1,18914 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kyll · Accepted Answer · 2016-05-17 08:37:27Z

0

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.

edited May 17, 2016 at 8:37

Kyll

7,1648 gold badges45 silver badges64 bronze badges

answered Mar 18, 2015 at 9:15

aru

9588 silver badges17 bronze badges

Comments

Just_Alex · Accepted Answer · 2021-10-08 05:06:33Z

0

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.

edited Oct 8, 2021 at 5:06

answered Oct 7, 2021 at 7:29

Just_Alex

5581 gold badge4 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related