Transcript Document

SQLite and the
.NET Framework
This PPT: Http://www.eggheadcafe.com/articles/SQLite.ppt
What is SQLite?

SQLite is a small C library that implements a
self-contained, embeddable, zero-configuration
SQL database engine.







Transactions are atomic, consistent, isolated, and durable
(ACID) even after system crashes and power failures.
Zero-configuration - no setup or administration needed.
Implements most of SQL92.
A complete database is stored in a single disk file.
Database files can be freely shared between machines with
different byte orders.
Supports databases up to 2 terabytes (241 bytes) in size.
Sizes of strings and BLOBs limited only by available memory.
What is SQLite – continued









Faster than popular client/server database engines for most
common operations.
Simple, easy to use API.
Bindings for many languages available
Well-commented source code with over 95% test coverage.
Self-contained: no external dependencies.
Sources are in the public domain. Use for any purpose.
There is now a robust SQLite.NET ADO.NET provider!
Small code footprint: less than 30K lines of C code, less than
250KB code space
Installation: Just set “Build Action” to “Content” on Database file!
More SQLite Features

Create Database Programmatically in
Connection string
 No
need to distribute or install database
 SQL to create database, tables, triggers can
be embedded

Triggers
 Triggers
can be used to handle many of the
functions of stored procedures.

Vacuum
 Just
like “Compact Database” in MS Access.
The SQLite ADO.NET Provider
Conforms to normal ADO.NET Best
practices
 Offers:

 SQLiteConnection
- all Methods except “Xml”
 SQLiteDataAdapter
 SQLiteCommandBuilder
 Supports NTFS compression of Database File
 SQLiteCommand
SQLite 3.0 ODBC Driver




Http://www.ch-werner.de/sqliteodbc/html/
This is an experimental open source ODBC driver for the
wonderful SQLite 2.8.* and SQLite 3.* (alpha) Database
Engine/Library. So far it is more a proof of concept and
might contain lots of memory leaks and all other kinds of
bugs. Thus I highly appreciate feedback.
WIN32 binaries (the ODBC driver DLL, install/uninstall
programs) are in http://www.chwerner.de/sqliteodbc/sqliteodbc-win32.zip
It works! It is SLOOOOW!
SQLite in Action: A First Look
private void CreateDatabase()
{
SQLiteConnection Conn = new SQLiteConnection();
string strPath=Server.MapPath("blog.db");
Conn.ConnectionString = "Data Source="+strPath+";
Compress=False;Synchronous=Off;New=True;Version=3";
Conn.Open();
SQLiteCommand Cmd = new SQLiteCommand();
Cmd = Conn.CreateCommand();
Cmd.CommandText="CREATE TABLE BlogItems
(Blogid integer primary key , title varchar(250),
link varchar(300), [description] varchar(8000),
pubDate datetime, guid varchar(128), [public] integer )";
Cmd.ExecuteNonQuery();
....
Important Scenarios

Mobile Code
 Device-hosted
applications
 Database runs 100% on Device
 “Zero configuration” installation
 NO Client License Fees

Server / Desktop Code
 Alternative
to MSDE, MSAccess, MySql
 No installation Required
 Fast database engine
 MultiThreaded
Mobile Code Scenario

Advantages of mobile code database
 Executes
locally for performance and rich
features
 Can use a WebService to receive
“GetChanges” DataSets to update database
on server
 No dependency on “what kind” of database is
used on the server.
 No complicated Server configuration
 No Client license fees to use SQLite
Demos!
URLS - Articles and Sample Code!
• ASP.NET CacheDependency:
•
•
•
•
http://www.eggheadcafe.com/articles/20040916.asp
SQLite DB and SQLiteBlog:
http://www.eggheadcafe.com/articles/20040714.asp
.Net Forum Control with SQLite:
http://www.eggheadcafe.com/articles/20041009.asp
SQLite Compact Framework:
http://www.eggheadcafe.com/articles/sqlitecf3.zip