SQLite database engine and sample code in C#
The definition in SQLite official site is the following: “SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world.” So, how can we use SQLite engine to develop standalone desktop database application in Windows environments. At SQLite official site, you can download all C source of the SQLite library. Nowadays people develop .NET applications by using C#, VB.NET, C++ etc. I usually use C# to develop Windows applications. How can we utilize SQLite library in .NET environments? In another words, does a SQLite .net interface exist? Yes, it does. System.Data.SQLite, an open source ADO.NET provider for the SQLite database engine, provides all the functionality you need to operate SQLite databases. What you need to do is to download latest version of System.Data.SQLite at http://sourceforge.net/project/showfiles.php?group_id=132486&package_id=145568. Run the setup file and install SQLite library and the .NET interface library into your PC. Then you can use this library to develop standalone high standard database application. To operate the database you can use commands of SQL language. Pretty a amazing. I wrote a PERL script running in Linux system to operate MySQL databases on MySQL server. Later on, I ported this PERL script to C#. I did not need to change any SQL statements used in the PERL script. Some of them are really complicated.
In order to give your some guideline to start using System.Data.SQLite library, I am going to share a simplified framework, a C# class, with you. You can modify it to use it in your project freely.
/*
* Author: Zhanshan Dong
* Created: 4/3/2009
* Modified: 9/18/2009
*/
using System;
using System.Windows.Forms;
using System.Data.SQLite;
using System.IO;
using System.Collections.Generic;
namespace TwitterGIFT
{
///
/// Description of Class1.
///
public class GiftSQLite
{
private SQLiteConnection Conn;
private SQLiteCommand Cmd;
private string dbFilename = "";
public GiftSQLite(string userName)
{
username = userName;
dbFilename = userName + ".db";
string FullPath = Application.StartupPath + "\\" + dbFilename;
if (Application.StartupPath.Substring(Application.StartupPath.Length - 1,1) == "\\")
FullPath = Application.StartupPath + dbFilename;
bool hasDBFile = File.Exists(FullPath);
ConnectDB();
Cmd = CreateCMD();
if (! hasDBFile )
{
CreateTables();
}
}
~GiftSQLite()
{
Cmd.Dispose();
Conn.Close();
}
private void ConnectDB()
{
Conn = new SQLiteConnection();
Conn.ConnectionString = "Data Source=" + username + ".db;New=True;Compress=True;Synchronous=Off";
Conn.Open();
}
private SQLiteCommand CreateCMD()
{
SQLiteCommand cmd = new SQLiteCommand();
return Conn.CreateCommand();
}
private void disposeCMD(SQLiteCommand cmd)
{
cmd.Dispose();
}
private void ExecuteNonQuerySQL(string SQL) {
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
private SQLiteDataReader ExecuteQuerySQL(string SQL, SQLiteCommand cmd) {
cmd.CommandText = SQL;
return cmd.ExecuteReader();
}
private void CreateTables()
{
// create a table
ExecuteNonQuerySQL("CREATE TABLE tablename (field1 integer primary key, ...)");
...
}
private int getRowCount(string tablename)
{
SQLiteCommand cmd = CreateCMD();
SQLiteDataReader dbReader = ExecuteQuerySQL("SELECT count(id_"+ tablename + ") FROM "+ tablename,cmd);
int rowCount = 0;
if (dbReader.HasRows) { dbReader.Read(); rowCount = dbReader.GetInt32(0); }
dbReader.Close();
cmd.Dispose();
return rowCount;
}
private void processAllRows()
{
SQLiteCommand cmd = CreateCMD();
SQLiteDataReader dbReader = ExecuteQuerySQL("SELECT * FROM tablename",cmd);
if (dbReader.HasRows)
{
while (dbReader.Read())
{
int id = dbReader.GetInt32(0);
...
...
...
}
}
dbReader.Close();
disposeCMD(cmd);
}
}
}
Besides the functionality in your application to do database operation, you do need SQLite database client tools to browse and manage your database. There are plenty of tools available. I tested a very simple one from http://sqlitetool.googlepages.com/. RazorSQL provides an array of tools for editing, browsing, querying and managing SQLite database. TwitterGIFT is a Windows application we developed that utilized the SQLite engine to handle database.
[...] SQLite database engine and sample code in C# | 太阳帆数据 Sunfine Data [...]