SQLite and the NET Framework This PPT Http

SQLite and the. NET Framework This PPT: Http: //www. eggheadcafe. com/articles/SQLite. ppt

What is SQLite? n 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 SQL 92. 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 30 K lines of C code, less than 250 KB code space Installation: Just set “Build Action” to “Content” on Database file!

More SQLite Features n Create Database Programmatically in Connection string ¨ No need to distribute or install database ¨ SQL to create database, tables, triggers can be embedded n Triggers ¨ Triggers can be used to handle many of the functions of stored procedures. n Vacuum ¨ Just like “Compact Database” in MS Access.

The SQLite ADO. NET Provider Conforms to normal ADO. NET Best practices n Offers: n ¨ SQLite. Connection ¨ SQLite. Command - all Methods except “Xml” ¨ SQLite. Data. Adapter ¨ SQLite. Command. Builder ¨ Supports NTFS compression of Database File

SQLite 3. 0 ODBC Driver n n 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. WIN 32 binaries (the ODBC driver DLL, install/uninstall programs) are in http: //www. chwerner. de/sqliteodbc-win 32. zip It works! It is SLOOOOW!

SQLite in Action: A First Look private void Create. Database() { SQLite. Connection Conn = new SQLite. Connection(); string str. Path=Server. Map. Path("blog. db"); Connection. String = "Data Source="+str. Path+"; Compress=False; Synchronous=Off; New=True; Version=3"; Conn. Open(); SQLite. Command Cmd = new SQLite. Command(); Cmd = Conn. Create. Command(); Cmd. Command. Text="CREATE TABLE Blog. Items (Blogid integer primary key , title varchar(250), link varchar(300), [description] varchar(8000), pub. Date datetime, guid varchar(128), [public] integer )"; Cmd. Execute. Non. Query(); . .

Important Scenarios n Mobile Code ¨ Device-hosted applications ¨ Database runs 100% on Device ¨ “Zero configuration” installation ¨ NO Client License Fees n Server / Desktop Code ¨ Alternative to MSDE, MSAccess, My. Sql ¨ No installation Required ¨ Fast database engine ¨ Multi. Threaded

Mobile Code Scenario n Advantages of mobile code database ¨ Executes locally for performance and rich features ¨ Can use a Web. Service to receive “Get. Changes” Data. Sets 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 Cache. Dependency: • • http: //www. eggheadcafe. com/articles/20040916. asp SQLite DB and SQLite. Blog: 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/sqlitecf 3. zip