SQLite Helper (C#) z
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/[^]