http://www.codeproject.com/Articles/746191/SQLite-Helper-Csharp
Introduction
I have written a small class, SQLiteHelper which aims to simplify the usage of SQLite in C#.
Prerequisite
This small class is built on top of System.Data.SQLite.DLL. A reference of this DLL must be added into your projects.
Download: https://system.data.sqlite.org
List of Simplified Functions
- GetTableStatus
- GetColumnStatus
- CreateTable
- BeginTransaction, Commit, Rollback
- Select
- Execute
- ExecuteScalar
- ExecuteScalarStr
- ExecuteScalarInt
- ExecuteScalarDateTime
- ExecuteScalarDecimal
- ExecuteScalarBlob
- Escape
- Insert
- Update
- RenameTable
- CopyAllData
- DropTable
Getting Start
Add this using statement at the top of your class:
using System.Data.SQLite;
SQLiteConnection
and SQLiteCommand
have to be initialized before using SQLiteHelper
:
Example:
using (SQLiteConnection conn = new SQLiteConnection("data source=C:\\data"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); // do something... conn.Close();
}
}
1. GetTableStatus
Get all information of tables in the database.
DataTable dt = sh.GetTableStatus();
2. GetColumnStatus
Get all information of columns in specific table.
// Get column's information from table "person"
DataTable dt = sh.GetColumnStatus("person");
3. CreateTable
Create table.
Example table structure: Person
Column Name | Data Type | Primary Key | Not Null | Default Value |
id | int | true | ||
name | text | |||
membershipid | int | |||
level | decimal | 5.5 |
SQLiteTable tb = new SQLiteTable("person"); tb.Columns.Add(new SQLiteColumn("id", true));
tb.Columns.Add(new SQLiteColumn("name"));
tb.Columns.Add(new SQLiteColumn("membershipid", ColType.Integer));
tb.Columns.Add(new SQLiteColumn("level", ColType.Decimal, false, false, "5.5")); sh.CreateTable(tb);
4. BeginTransaction, Commit, Rollback
Applying transactions.
sh.BeginTransaction(); try
{
// execute some queries sh.Commit();
}
catch
{
sh.Rollback();
}
5. Select
Return the query result in DataTable format.
DataTable dt = sh.Select("select * from person order by id;");
6. Execute
Execute single SQL query.
sh.Execute("insert into person(name)values('hello');");
7. ExecuteScalar
Return the result from first row first column in object format.
object ob = sh.ExecuteScalar("select max(id) from person;");
8. ExecuteScalarStr
Return the result from first row first column in string format.
string s = sh.ExecuteScalarStr("select max(id) from person;");
9. ExecuteScalarInt
Return the result from first row first column in Int format.
int i = sh.ExecuteScalarInt("select max(id) from person;");
10. ExecuteScalarDateTime
Return the result from first row first column in DateTime format.
DateTime date = sh.ExecuteScalarDateTime("select dateregister from person where id = 1;");
11. ExecuteScalarDecimal
Return the result from first row first column in decimal format.
decimal d = sh.ExecuteScalarDecimal("select level from person where id = 1;");
12. ExecuteScalarBlob
Return the result from first row first column in byte[] format.
byte[] ba = sh.ExecuteScalarBlob("select photo from person where id = 1;");
13. Escape
Escape string sequence for text value to avoid SQL injection or invalid SQL syntax to be constructed.
string value = sh.Escape(input);
14. Insert
Insert data.
Sample 1: Insert single row.
var dic = new Dictionary<string, object>();
dic["name"] = "John";
dic["membershipid"] = 1;
dic["level"] = 6.8; sh.Insert("person", dic);
Sample 2: Insert multiple rows.
var lst = new List<Dictionary<string,>(); var dic1 = new Dictionary<string,>();
dic1["name"] = "John";
dic1["membershipid"] = 1;
dic1["level"] = 6.8;
lst.Add(dic1); var dic2 = new Dictionary<string,>();
dic2["name"] = "Catherine";
dic2["membershipid"] = 2;
dic2["level"] = 9.7;
lst.Add(dic2); var dic3 = new Dictionary<string,>();
dic3["name"] = "Thomas";
dic3["membershipid"] = 3;
dic3["level"] = 8.6;
lst.Add(dic3); sh.Insert("person", lst);
16. Update
Update row.
Sample 1: Update with single condition (where id = 1)
var dicData = new Dictionary<string, object>();
dicData["name"] = "no name";
dicData["membershipid"] = 0;
dicData["level"] = 5.5; sh.Update("person", dicData, "id", 1);
Sample 2: Update with multiple condition (where membership = 1 and level = 5.5)
var dicData = new Dictionary<string, object>();
dicData["name"] = "no name";
dicData["status"] = 0;
dicData["money"] = 100;
dicData["dateregister"] = DateTime.MinValue; var dicCondition = new Dictionary<string,>();
dicCondition["membershipid"] = 1;
dicData["level"] = 5.5; sh.Update("person", dicData, dicCondition);
16. RenameTable
Rename a table.
sh.RenameTable("person", "person_backup");
17. CopyAllData
Copy all data from one table to another.
sh.CopyAllData("person", "person_new");
Before copying, SQLiteHelper
will scan the two tables for match columns. Only columns that exist in both tables will be copied.
18. DropTable
Drop table, delete a table
sh.DropTable("person");
That's it, guys/girls. Comments are welcome.
Happy coding
Also available at: https://sh.codeplex.com/[^]