Using SQlite in c#

[Rob Stevens]

Can someone tell me how to import the sqlite3.dll file into c#. I am pretty
new to this, and I want to use sqlite. The problem is I don't have a clue
on
how to import the dll file so i can call the references to it.
Thanks

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Rob Stevens wrote:

Can someone tell me how to import the sqlite3.dll file into c#. I am pretty
new to this, and I want to use sqlite. The problem is I don't have a clue
on
how to import the dll file so i can call the references to it.

http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers has some linkes
to .NET wrappers.

Arne

[Eps]

Arne Vajhøj wrote:

Rob Stevens wrote:

>Can someone tell me how to import the sqlite3.dll file into c#. I am
pretty
new to this, and I want to use sqlite. The problem is I don't have a
clue on
how to import the dll file so i can call the references to it.

http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers has some linkes
to .NET wrappers.

Arne

In my opinion the best one is SqLite.Net at http://sqlite.phxsoftware.com/

there are many .net wrappers for sqlite but I think the above one is the
only one that is fully ADO.NET compliant.

--
Eps

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

A little out of topic but, Have you consider using SQL express for the DB?

--
Ignacio Machin
http://www.laceupsolutions.com
Mobile & warehouse Solutions.
"Rob Stevens" <ro**********@sundry.comwrote in message
news:Ox**************@TK2MSFTNGP05.phx.gbl...

Can someone tell me how to import the sqlite3.dll file into c#. I am
pretty
new to this, and I want to use sqlite. The problem is I don't have a clue
on
how to import the dll file so i can call the references to it.
Thanks

[Samuel R. Neff]

Any why would you recommend he use an inferior product?

:-)

Seriously, SQLite is amazing--many advantages over SQL express (
small, fast, in-process, and provides even better .NET integration).

Sam

------------------------------------------------------------
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.

On Mon, 3 Dec 2007 11:13:48 -0500, "Ignacio Machin \( .NET/ C# MVP \)"
<machin TA laceupsolutions.comwrote:

>Hi,

A little out of topic but, Have you consider using SQL express for the DB?

[Rob Stevens]

"Arne Vajhøj" <ar**@vajhoej.dkwrote in message
news:47***********************@news.sunsite.dk...

http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers has some linkes
to .NET wrappers.

Arne

Thanks for the response everyone. I have another question.
Since the source is freely available for the sqlite library, is
there any way to compile it into your c# program without using
the dll file? I have seen a few pocket pc programs that use
the sqlite library, but the dll is no where to be found. So I am
wondering if this could be done on the windows side too.

My end result is to write a program for both the pocket pc and
windows. This way I can use the data file on either of the
systems.
Rob

[Brian Gideon]

On Dec 4, 9:44 am, "Rob Stevens" <[e-mail]:

Thanks for the response everyone. I have another question.
Since the source is freely available for the sqlite library, is
there any way to compile it into your c# program without using
the dll file? I have seen a few pocket pc programs that use
the sqlite library, but the dll is no where to be found. So I am
wondering if this could be done on the windows side too.

My end result is to write a program for both the pocket pc and
windows. This way I can use the data file on either of the
systems.

Rob

It is definitely possible. I'm not sure how much work it would
be...probably not much though.

[Samuel R. Neff]

It's possible but it's not quite as easy as combining a bunch of C#
projects into one dll (which can be done by dynamically building a new
large project file and then compiling).

The System.Data.SQLite wrapper is made into one dll through a mergebin
utility that is included and marges two dll's after they're compiled.
You'd have to use this utility to merge the native SQLite part into
your dll (which must already contain the C# wrapper portion). It may
be as simple as calling mergebin with the right params, or mergebin
may not work correctly in this scenario. Note that mergebin is made
specifically for SQLite by the SQLite .net developer.

Other programs that use SQLite without a dll most likely are not using
..NET but are using native SQLite which is easily statically linked
into a C application.

HTH,

Sam

------------------------------------------------------------
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.

On Tue, 4 Dec 2007 09:44:13 -0600, "Rob Stevens"

>
Thanks for the response everyone. I have another question.
Since the source is freely available for the sqlite library, is
there any way to compile it into your c# program without using
the dll file? I have seen a few pocket pc programs that use
the sqlite library, but the dll is no where to be found. So I am
wondering if this could be done on the windows side too.

My end result is to write a program for both the pocket pc and
windows. This way I can use the data file on either of the
systems.
Rob

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Samuel R. Neff wrote:

On Mon, 3 Dec 2007 11:13:48 -0500, "Ignacio Machin \( .NET/ C# MVP \)"
<machin TA laceupsolutions.comwrote:

>A little out of topic but, Have you consider using SQL express for the DB?

Any why would you recommend he use an inferior product?

:-)

Seriously, SQLite is amazing--many advantages over SQL express (
small, fast, in-process, and provides even better .NET integration).

SQLite has some nice features.

But SQLServer also has a couple like stored procedures and user
defined functions.

And BTW I am a bit skeptical about the .NET integration claim.

Arne

[henon]

On Dec 3, 4:32 am, "Rob Stevens" <[e-mail]:

Can someone tell me how to import the sqlite3.dll file into c#. I am pretty
new to this, and I want to use sqlite. The problem is I don't have a clue
on
how to import the dll file so i can call the references to it.

Thanks

I recently wrote (a very short article) about using sqlite with c# at
http://www.eqqon.com/index.php/SQLite_and_CSharp

hth,
-- henon

[Rob Stevens]

I recently wrote (a very short article) about using sqlite with c# at

http://www.eqqon.com/index.php/SQLite_and_CSharp

hth,
-- henon

Thank you, every little bit helps.
Rob

[Samuel R. Neff]

SQLite has user defined functions including scalar function, aggregate
functions, custom collation functions, and virtual tables (not same as
MSSQL virtualized views--virtual tabls are tables defined in code
through but are treated as real db tables by the database--for example
full text indexing is implemented through virtual tables).

..NET integration is greater because not only can you write all of the
above in any .NET language, but as SQLite still runs in process you
have full access to all of .NET plus your entire running application
from within your custom functions.

It does integrate with .NET better than MSSQL does. Surprising but
true.

Sam
------------------------------------------------------------
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.

On Sat, 08 Dec 2007 22:29:25 -0500, Arne Vajhøj <ar**@vajhoej.dk>
wrote:

>Samuel R. Neff wrote:

>On Mon, 3 Dec 2007 11:13:48 -0500, "Ignacio Machin \( .NET/ C# MVP \)"
<machin TA laceupsolutions.comwrote:

>>A little out of topic but, Have you consider using SQL express for the DB?

Any why would you recommend he use an inferior product?

:-)

Seriously, SQLite is amazing--many advantages over SQL express (
small, fast, in-process, and provides even better .NET integration).

SQLite has some nice features.

But SQLServer also has a couple like stored procedures and user
defined functions.

And BTW I am a bit skeptical about the .NET integration claim.

Arne

[Liz]

"Samuel R. Neff" <sa********@nomail.comwrote in message
news:rb********************************@4ax.com...

>
SQLite has user defined functions including scalar function, aggregate
functions, custom collation functions, and virtual tables (not same as
MSSQL virtualized views--virtual tabls are tables defined in code
through but are treated as real db tables by the database--for example
full text indexing is implemented through virtual tables).

.NET integration is greater because not only can you write all of the
above in any .NET language, but as SQLite still runs in process you
have full access to all of .NET plus your entire running application
from within your custom functions.

It does integrate with .NET better than MSSQL does.

meaning what?

[Samuel R. Neff]

On Mon, 10 Dec 2007 22:15:44 -0600, "Liz" <li*@tiredofspam.comwrote:

>
"Samuel R. Neff" <sa********@nomail.comwrote in message
news:rb********************************@4ax.com.. .

>>
SQLite has user defined functions including scalar function, aggregate
functions, custom collation functions, and virtual tables (not same as
MSSQL virtualized views--virtual tabls are tables defined in code
through but are treated as real db tables by the database--for example
full text indexing is implemented through virtual tables).

.NET integration is greater because not only can you write all of the
above in any .NET language, but as SQLite still runs in process you
have full access to all of .NET plus your entire running application
from within your custom functions.

>It does integrate with .NET better than MSSQL does.

meaning what?

meaning exactly what I said in the first two paragraphs which you
quoted. Would you like clarification on anything in particular?

Sam
------------------------------------------------------------
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.

[Liz]

"Samuel R. Neff" <sa********@nomail.comwrote in message
news:fq********************************@4ax.com...

>>It does integrate with .NET better than MSSQL does.

meaning what?

meaning exactly what I said in the first two paragraphs which you
quoted. Would you like clarification on anything in particular?

I'm just not clear why/how SqlLite integrates with .NET *better* than MSSQL;
is it because I can use methods defined within my app to operate directly on
the SqlLite data? I take it, with SL, you're linking a library into your
app and there is no DB server as such .. just code that performs like SQL on
a local file ??

[Samuel R. Neff]

Yes, exactly, there is no separate server. SQLite runs in process and
therefore runs literally as a part of your application. You can
communicate very tightly and easily between SQLite and your .NET
application without overhead of inter-process or inter-computer
communications (just a little managed/native marshalling which is
abstracted away by the wrapper).

And yes it performs SQL on a local file. I wouldn't say "like SQL",
it's real SQL with a query parser, analyzer, virtual machine for
executing queries, data page caching, locking, and atomic transactions
(even across processes).

Best regards,

Sam

------------------------------------------------------------
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.
On Tue, 11 Dec 2007 12:38:17 -0600, "Liz" <li*@tiredofspam.comwrote:

>
"Samuel R. Neff" <sa********@nomail.comwrote in message
news:fq********************************@4ax.com.. .

>>>It does integrate with .NET better than MSSQL does.

meaning what?

>meaning exactly what I said in the first two paragraphs which you
quoted. Would you like clarification on anything in particular?

I'm just not clear why/how SqlLite integrates with .NET *better* than MSSQL;
is it because I can use methods defined within my app to operate directly on
the SqlLite data? I take it, with SL, you're linking a library into your
app and there is no DB server as such .. just code that performs like SQL on
a local file ??

[Liz]

"Samuel R. Neff" <sa********@nomail.comwrote in message
news:0b********************************@4ax.com...

Yes, exactly, there is no separate server. SQLite runs in process and
therefore runs literally as a part of your application. You can
communicate very tightly and easily between SQLite and your .NET
application without overhead of inter-process or inter-computer
communications (just a little managed/native marshalling which is
abstracted away by the wrapper).

how does ADO.NET talk to it? with System.Data.SqlClient ??

And yes it performs SQL on a local file. I wouldn't say "like SQL",

I should have said "like SQL Server" ...

it's real SQL with a query parser, analyzer, virtual machine for
executing queries, data page caching, locking, and atomic transactions
(even across processes).

with some not trivial limitations I gather; but it looks like a great tool,
actually ... I'm wondering how people are using it; seems like it might be
useful as a local cache for BI apps ... or as a config store ... or
...........

[Samuel R. Neff]

There's a number of .NET wrappers. The best one provides a fully
ADO.NET implementation and thus works just like System.Data.SqlClient
but is System.Data.SQLiteClient. He has a version that works with
LINQ to some extent but not sure if that's fully done yet.

http://sqlite.phxsoftware.com

It's used in a huge variety of applications. Desktop apps like Adobe
Lightroom and Mozilla Firefox use it as their database. It's very
commonly used in embedded devices like MP3 players and cell phones
(including iPod) and is available in both Google Gears and Adobe AIR
for hybrid online/offline applications. Microsoft actually uses
SQLite in one of it's PC games although I forget which one.

http://sqlite.org/famous.html

It can also be used in a server model but then lack of concurrency
could be a problem with a very high volume server (i.e, thousands of
requests per second volume).

The main limitation is concurrency. For writing it locks the entire
db during the time it flushes cached writes to disk. While this
sounds bad, it's really no biggie even for medium volume applications
(dozens of transactions per second) as it's still multi-user for
reading and for preparing writes (processing sql and building up the
transaction journal). It only needs the exclusive lock for a few
milliseconds.

We have timing routines in our application for long queries, and
during development we never saw the warnings logged. We thought this
was odd. So for a period of time we logged the time it took to run
all queries. Most queries got logged as taking zero milliseconds.
About one out of 20 would log as taking 16ms. And this is with 70+
tables, often joining 10 tables in a query, and after loading the db
up with data equivalent to a few years woth of usage of our
application.

Our previous application used MSSQL and most queries took a few
hundred MS and occasionally we ran into problems with queries taking
several seconds. Not the exact same application, but very similar
database schema and very similar queries.

HTH,

Sam
------------------------------------------------------------
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.

On Tue, 11 Dec 2007 17:39:07 -0600, "Liz" <li*@tiredofspam.comwrote:

>
"Samuel R. Neff" <sa********@nomail.comwrote in message
news:0b********************************@4ax.com.. .

>Yes, exactly, there is no separate server. SQLite runs in process and
therefore runs literally as a part of your application. You can
communicate very tightly and easily between SQLite and your .NET
application without overhead of inter-process or inter-computer
communications (just a little managed/native marshalling which is
abstracted away by the wrapper).

how does ADO.NET talk to it? with System.Data.SqlClient ??

>And yes it performs SQL on a local file. I wouldn't say "like SQL",

I should have said "like SQL Server" ...

>it's real SQL with a query parser, analyzer, virtual machine for
executing queries, data page caching, locking, and atomic transactions
(even across processes).

with some not trivial limitations I gather; but it looks like a great tool,
actually ... I'm wondering how people are using it; seems like it might be
useful as a local cache for BI apps ... or as a config store ... or
..........

[Liz]

nice overview ... thanks, Sam ...

About one out of 20 would log as taking 16ms. And this is with 70+
tables, often joining 10 tables in a query, and after loading the db
up with data equivalent to a few years woth of usage of our
application.

what kind of row counts?
"Samuel R. Neff" <sa********@nomail.comwrote in message
news:fn********************************@4ax.com...

>
There's a number of .NET wrappers. The best one provides a fully
ADO.NET implementation and thus works just like System.Data.SqlClient
but is System.Data.SQLiteClient. He has a version that works with
LINQ to some extent but not sure if that's fully done yet.

http://sqlite.phxsoftware.com

It's used in a huge variety of applications. Desktop apps like Adobe
Lightroom and Mozilla Firefox use it as their database. It's very
commonly used in embedded devices like MP3 players and cell phones
(including iPod) and is available in both Google Gears and Adobe AIR
for hybrid online/offline applications. Microsoft actually uses
SQLite in one of it's PC games although I forget which one.

http://sqlite.org/famous.html

It can also be used in a server model but then lack of concurrency
could be a problem with a very high volume server (i.e, thousands of
requests per second volume).

The main limitation is concurrency. For writing it locks the entire
db during the time it flushes cached writes to disk. While this
sounds bad, it's really no biggie even for medium volume applications
(dozens of transactions per second) as it's still multi-user for
reading and for preparing writes (processing sql and building up the
transaction journal). It only needs the exclusive lock for a few
milliseconds.

We have timing routines in our application for long queries, and
during development we never saw the warnings logged. We thought this
was odd. So for a period of time we logged the time it took to run
all queries. Most queries got logged as taking zero milliseconds.
About one out of 20 would log as taking 16ms. And this is with 70+
tables, often joining 10 tables in a query, and after loading the db
up with data equivalent to a few years woth of usage of our
application.

Our previous application used MSSQL and most queries took a few
hundred MS and occasionally we ran into problems with queries taking
several seconds. Not the exact same application, but very similar
database schema and very similar queries.

HTH,

Sam
------------------------------------------------------------
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.

On Tue, 11 Dec 2007 17:39:07 -0600, "Liz" <li*@tiredofspam.comwrote:

>>
"Samuel R. Neff" <sa********@nomail.comwrote in message
news:0b********************************@4ax.com. ..

>>Yes, exactly, there is no separate server. SQLite runs in process and
therefore runs literally as a part of your application. You can
communicate very tightly and easily between SQLite and your .NET
application without overhead of inter-process or inter-computer
communications (just a little managed/native marshalling which is
abstracted away by the wrapper).

how does ADO.NET talk to it? with System.Data.SqlClient ??

>>And yes it performs SQL on a local file. I wouldn't say "like SQL",

I should have said "like SQL Server" ...

>>it's real SQL with a query parser, analyzer, virtual machine for
executing queries, data page caching, locking, and atomic transactions
(even across processes).

with some not trivial limitations I gather; but it looks like a great
tool,
actually ... I'm wondering how people are using it; seems like it might
be
useful as a local cache for BI apps ... or as a config store ... or
..........

[Samuel R. Neff]

On Wed, 12 Dec 2007 12:07:48 -0600, "Liz" <li*@tiredofspam.comwrote:

>

nice overview ... thanks, Sam ...

>About one out of 20 would log as taking 16ms. And this is with 70+
tables, often joining 10 tables in a query, and after loading the db
up with data equivalent to a few years woth of usage of our
application.

what kind of row counts?

most tables have thousands of records but there are a few tables that
store data in a ridiculously normalized form and end up with hundreds
of millions of records. All worked wonderfully in SQLite. The
largest table actually ended up causing us to have 30 second command
timeout problems with MSSQL and we had to re-write the query and run
two separate queries, whereas SQLite handled the original query very
efficiently.

HTH,

Sam
------------------------------------------------------------
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.

[Liz]

"Samuel R. Neff" <sa********@nomail.comwrote in message
news:ss********************************@4ax.com...

On Wed, 12 Dec 2007 12:07:48 -0600, "Liz" <li*@tiredofspam.comwrote:

>>About one out of 20 would log as taking 16ms. And this is with 70+
tables, often joining 10 tables in a query, and after loading the db
up with data equivalent to a few years woth of usage of our
application.

what kind of row counts?

most tables have thousands of records but there are a few tables that
store data in a ridiculously normalized form and end up with hundreds
of millions of records. All worked wonderfully in SQLite. The
largest table actually ended up causing us to have 30 second command
timeout problems with MSSQL and we had to re-write the query and run
two separate queries, whereas SQLite handled the original query very
efficiently.

sounds impressive; don't think I'd use it multi-user ... but it makes you
wonder (again) about these bloated beasts from MS and all the others ...