SQLite Helper (C#) z

时间:2024-01-01 17:07:51

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

  1. GetTableStatus
  2. GetColumnStatus
  3. CreateTable
  4. BeginTransaction, Commit, Rollback
  5. Select
  6. Execute
  7. ExecuteScalar
  8. ExecuteScalarStr
  9. ExecuteScalarInt
  10. ExecuteScalarDateTime
  11. ExecuteScalarDecimal
  12. ExecuteScalarBlob
  13. Escape
  14. Insert
  15. Update
  16. RenameTable
  17. CopyAllData
  18. DropTable

Getting Start

Add this using statement at the top of your class:

SQLite Helper (C#) z Collapse | Copy Code
using System.Data.SQLite;

SQLiteConnection and SQLiteCommand have to be initialized before using SQLiteHelper:

Example:

SQLite Helper (C#) z Collapse | Copy Code
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.

SQLite Helper (C#) z Collapse | Copy Code
DataTable dt = sh.GetTableStatus();

2. GetColumnStatus

Get all information of columns in specific table.

SQLite Helper (C#) z Collapse | Copy Code
// 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
SQLite Helper (C#) z Collapse | Copy Code
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.

SQLite Helper (C#) z Collapse | Copy Code
sh.BeginTransaction();

try
{
// execute some queries sh.Commit();
}
catch
{
sh.Rollback();
}

5. Select

Return the query result in DataTable format.

SQLite Helper (C#) z Collapse | Copy Code
DataTable dt = sh.Select("select * from person order by id;");

6. Execute

Execute single SQL query.

SQLite Helper (C#) z Collapse | Copy Code
sh.Execute("insert into person(name)values('hello');");

7. ExecuteScalar

Return the result from first row first column in object format.

SQLite Helper (C#) z Collapse | Copy Code
object ob = sh.ExecuteScalar("select max(id) from person;");

8. ExecuteScalarStr

Return the result from first row first column in string format.

SQLite Helper (C#) z Collapse | Copy Code
string s = sh.ExecuteScalarStr("select max(id) from person;");

9. ExecuteScalarInt

Return the result from first row first column in Int format.

SQLite Helper (C#) z Collapse | Copy Code
int i = sh.ExecuteScalarInt("select max(id) from person;");

10. ExecuteScalarDateTime

Return the result from first row first column in DateTime format.

SQLite Helper (C#) z Collapse | Copy Code
DateTime date = sh.ExecuteScalarDateTime("select dateregister from person where id = 1;");

11. ExecuteScalarDecimal

Return the result from first row first column in decimal format.

SQLite Helper (C#) z Collapse | Copy Code
decimal d = sh.ExecuteScalarDecimal("select level from person where id = 1;");

12. ExecuteScalarBlob

Return the result from first row first column in byte[] format.

SQLite Helper (C#) z Collapse | Copy Code
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.

SQLite Helper (C#) z Collapse | Copy Code
string value = sh.Escape(input);

14. Insert

Insert data.

Sample 1: Insert single row.

SQLite Helper (C#) z Collapse | Copy Code
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.

SQLite Helper (C#) z Collapse | Copy Code
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)

SQLite Helper (C#) z Collapse | Copy Code
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)

SQLite Helper (C#) z Collapse | Copy Code
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.

SQLite Helper (C#) z Collapse | Copy Code
sh.RenameTable("person", "person_backup");

17. CopyAllData

Copy all data from one table to another.

SQLite Helper (C#) z Collapse | Copy Code
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

SQLite Helper (C#) z Collapse | Copy Code
sh.DropTable("person");

That's it, guys/girls. Comments are welcome.

Happy coding SQLite Helper (C#) z

Also available at: https://sh.codeplex.com/[^]