Using SQlite in c#

  • Thread starter Thread starter Rob Stevens
  • Start date Start date
R

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
 
Hi,

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).

Sam
 
Arne Vajhøj said:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers has some linkes
to .NET wrappers.

Arne
Click to expand...

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
 
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
Click to expand...

It is definitely possible. I'm not sure how much work it would
be...probably not much though.
 
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"
 
Samuel said:
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).
Click to expand...

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
 
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.
 
Samuel R. Neff said:
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.
Click to expand...

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


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 ??
 
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
 
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).
Click to expand...

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",
Click to expand...

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).
Click to expand...

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
...........
 
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.
 
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.
Click to expand...

what kind of row counts?
 
nice overview ... thanks, Sam ...


what kind of row counts?
Click to expand...

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
 
Back
Top