Does anyone here use SQLite?

[Rob Stevens]

I have been trying to find some working examples of working with this DB,
but
I can't seem to find anything at all. If I do find examples, its all
referring to the
command line program that comes with sqlite.

I am trying to figure out the following using sqlite from phxsoftware.com or
System.Data.SQLite.

1. How do you create a DB with a table and some fields in the table?

2. How do you load a DB, lets say I want to open a different DB.

3. How do you add new records into the DB?

4. How do you delete records from the DB?

I know each of these questions are only a few lines of code, but since I
know
nothing about sqlite, it is very difficult.


Thanks in advance.

 

[Lasse Vågsæther Karlsen]

I have been trying to find some working examples of working with this DB,
but
I can't seem to find anything at all. If I do find examples, its all
referring to the
command line program that comes with sqlite.

I am trying to figure out the following using sqlite from phxsoftware.com or
System.Data.SQLite.

1. How do you create a DB with a table and some fields in the table?

1. create a zero-byte file (doesn't matter how)
SQLiteConnection.CreateFile can be used here if you want to use
something provided by SQLiteConnection.

2. Execute CREATE TABLE statements against the database
Use SQLiteCommand to execute sql against the database.

2. How do you load a DB, lets say I want to open a different DB.

Use the constructor of SQLiteConnection that accepts a connection string
and use the syntax "Data Source=filenamehere".

3. How do you add new records into the DB?

You execute INSERT statements against it.

4. How do you delete records from the DB?

You execute delete statements against it.

I know each of these questions are only a few lines of code, but since I
know
nothing about sqlite, it is very difficult.

Also, just for the record, always post what you have tried and what
failed. Since you've also posted the exact same question on the SQLite
forum, where you've also posted an example that shows that you do in
fact know how to create the database, then my impression is that you
already know the answers to some of your questions and thus should've
been more specific when asking them.

If you have more specific questions, let us know what you've tried and
what you need help with and you'll get better answers.

 

[Peter Bromberg [C# MVP]]

I have used SQLite for a variety of different projects over the last few
years. If you download Robert Simpson's implementation of System.Data.SQLite
here
http://sqlite.phxsoftware.com/
there are test cases included that show you most of the features and "how do
I's".

-- Peter
http://www.eggheadcafe.com
http://petesbloggerama.blogspot.com
http://www.blogmetafinder.com

 

[Rob Stevens]

Also, just for the record, always post what you have tried and what

failed. Since you've also posted the exact same question on the SQLite
forum, where you've also posted an example that shows that you do in

The reason I posted the same exact question is because as I stated in the
forum, I know nothing about SQL or SQLITE. I have been messing
around with this for a few days. It is very surprising to me that there are
almost none existent examples out there on using sqlite.

Whenever I do find examples its based on the command line program that
comes with sqlite source code. There are no examples for someone now
starting out. I am not trying to get fancy with this program, I just want
to
know how to do the basics. I have been reading and searching for days,
and as you can see I have come up with nothing.

So saying do a select statement against the database is really not doing
anything for me at all.


Appreciate your help though.

 

[Rob Stevens]

I have used SQLite for a variety of different projects over the last few

years. If you download Robert Simpson's implementation of
System.Data.SQLite
here
http://sqlite.phxsoftware.com/
there are test cases included that show you most of the features and "how
do
I's".

Hi Peter,

Actually there is not a lot of the basic examples for someone now starting
out
with the product. It seems the more responses in there are geared toward
the
more difficult things. I noticed that someone asked for a sample program on
how to do the basics back in 2006. The author said when he has time, so
that
may never be possible. Because I know he has a lot to do with the forum
plus
everything else.

I am looking for the same too. I just want a sample program that shows how
to
do all the basic stuff.

 

[Eps]

System.Data.SQLite
The above is fully ADO.Net compliant, the reason your not seeing
examples specifically for SQLite.Net is because when using a database
from c# using ADO.Net there is virtualy no difference in the code.

So what you want is to look into ADO.Net, be prepared to do some
reading, or jsut do what I did which was start playing around with it.

There is a "End to End" example of how to use SQLite, this is taken from
the HowTo forum on their site.


http://sqlite.phxsoftware.com/forums/t/539.aspx

You use SQLite.NET like any other ADO.NET provider. Here's an end to
end example:


using System;

using System.Data;

using System.Data.Common;

using System.Data.SQLite;

namespace test

{

class Program

{

static void Main(string[] args)

{

// Create a connection and a command

using (DbConnection cnn = new SQLiteConnection("Data Source=test.db3"))

using (DbCommand cmd = cnn.CreateCommand())

{

// Open the connection. If the database doesn't exist,

// it will be created automatically

cnn.Open();

// Create a table in the database

cmd.CommandText = "CREATE TABLE FOO (ID INTEGER PRIMARY KEY, MYVALUE
VARCHAR(50))";

cmd.ExecuteNonQuery();



// Create a parameterized insert command

cmd.CommandText = "INSERT INTO FOO (MYVALUE) VALUES(?)";

cmd.Parameters.Add(cmd.CreateParameter());



// Insert 10 rows into the database

for (int n = 0; n < 10; n++)

{

cmd.Parameters[0].Value = "Value " + n.ToString();

cmd.ExecuteNonQuery();

}

// Now read them back

cmd.CommandText = "SELECT * FROM FOO";

using (DbDataReader reader = cmd.ExecuteReader())

{

while (reader.Read())

{

Console.WriteLine(String.Format("ID = {0}, MYVALUE = {1}", reader[0],
reader[1]));

}

}

}

Console.ReadKey();

}

}

}

 

[Rob Stevens]

The above is fully ADO.Net compliant, the reason your not seeing examples

specifically for SQLite.Net is because when using a database from c# using
ADO.Net there is virtualy no difference in the code.

So what you want is to look into ADO.Net, be prepared to do some reading,
or jsut do what I did which was start playing around with it.

Wow, that exaplains a lot. Thanks a lot, I will definitely start doing
the research now.


Rob