SQLite Helper (C#) zt

时间:2022-09-08 22:04:11

http://www.codeproject.com/Articles/746191/SQLite-Helper-Csharp

This small class (SQLiteHelper.cs) 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. GetTableList
  3. GetColumnStatus
  4. CreateTable
  5. UpdateTableStructure
  6. BeginTransaction, Commit, Rollback
  7. Select
  8. Execute
  9. ExecuteScalar
  10. Escape
  11. Insert
  12. Update
  13. LastInsertRowId
  14. RenameTable
  15. CopyAllData
  16. DropTable
  17. ShowDatabase
  18. AttachDatabase, DetachDatabase

Getting Start

Add this using statement at the top of your class:

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

SQLiteConnection and SQLiteCommand have to be initialized before using SQLiteHelper:

Example:

SQLite Helper (C#) zt 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#) zt Collapse | Copy Code
DataTable dt = sh.GetTableStatus();

Sample result:

type name tbl_name rootpage sql
table sqlite_sequence sqlite_sequence 3 CREATE TABLE sqlite_sequence(name,seq)
table person2 person2 5 CREATE TABLE "person2"(
id integer primary key autoincrement,
name text,
tel text,
email text,
job text,
remarks text)
table player player 4 CREATE TABLE `player`(
id integer primary key autoincrement,
lvl integer,
weaponid integer,
teamid integer,
location text,
team_name text,
remarks text)
table product product 6 CREATE TABLE "product"(
id integer primary key autoincrement,
name text,
qty integer)

2. GetTableList

Get a list of tables in database.

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

3. GetColumnStatus

Get all information of columns in specific table.

SQLite Helper (C#) zt Collapse | Copy Code
// Get column's information from table "person"
DataTable dt = sh.GetColumnStatus("person");

Sample Result:

cid name type notnull dflt_value pk
0 id integer 0   1
1 lvl integer 0   0
2 weaponid integer 0   0
3 teamid integer 0   0
4 location text 0   0
5 team_name text 0   0
6 remarks text 0   0

4. CreateTable

Create table.

Example table structure: Person

Column Name Data Type Primary Key Auto Increment Not Null Default Value
id int true true    
name text        
membershipid int        
level decimal       5.5
SQLite Helper (C#) zt 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);

5. UpdateTableStructure

As the name said, it is used to update a table's structure. Maybe you have added new columns, or drop/deleted some columns. This method helps you to update it.

The process at code behind:

  • Assume that the old table is named: person
  • The class creates a temporary table (named: person_temp) with your new defined structure.
  • Copy all rows from person to person_temp.
  • Drop/delete table of person.
  • Rename table of person_temp to person

Code example:

SQLite Helper (C#) zt Collapse | Copy Code
SQLiteTable tb = new SQLiteTable();
tb.Columns.Add(new SQLiteColumn("id", true));
tb.Columns.Add(new SQLiteColumn("name"));
tb.Columns.Add(new SQLiteColumn("sku"));
tb.Columns.Add(new SQLiteColumn("code"));
tb.Columns.Add(new SQLiteColumn("category"));
tb.Columns.Add(new SQLiteColumn("remarks")); sh.UpdateTableStructure("person", tb);

6. BeginTransaction, Commit, Rollback

What is transaction?

By default, every SQL query that is sent to SQLite database engine happens in a transaction. The engine automatically BEGIN a transaction and COMMIT it at the end. COMMIT is something like "Make it take effect".

If we send 3 SQL queries (INSERT, UPDATE, DELETE, etc...), 3 transactions are taken place. According to [SQLite official documentation - Frequently Asked Questions]:

"...A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second..."

Which means, with a 7200RPM hard disk, the best that we can do is 60 INSERTs (or UPDATE, DELETE, etc) per second.

But, If we manually issue a BEGIN TRANSACTION, all the queries will be wrapped in single transaction, then SQLite can execute huge amount of queries per second. Somebody said he can execute 10 million per second at [*.com], but this is also depends on the speed of hard disk that you are using.

Code example with SQLiteHelper:

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

try
{
// INSERT.....
// INSERT.....
// UPDATE....
// ... skip for another 50,000 queries....
// DELETE....
// UPDATE...
// INSERT..... sh.Commit();
}
catch
{
sh.Rollback();
}

ROLLBACK, in the above example means Cancel Transaction. All queries that have sent to SQLite database within that specific transaction are dismissed.

7. Select

Return the query result in DataTable format.

  • Select(string sql)
  • Select(string sql, Dictionary<string, object> dicParameters = null)
  • Select(string sql, IEnumerable<SQLiteParameter> parameters = null)

Example 1:

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

Example 2 (With parameters support):

SQLite Helper (C#) zt Collapse | Copy Code
var dic = new Dictionarystring, object();
dic["@aaa"] = 1;
dic["@bbb"] = 1;
DataTable dt = sh.Select("select * from member where membershipid = @aaa and locationid = @bbb;", dic);

Example 3 (With parameters support):

SQLite Helper (C#) zt Collapse | Copy Code
DataTable dt = sh.Select("select * from member where membershipid = @aaa and locationid = @bbb;",
new SQLiteParameter[] {
new SQLiteParameter("@aaa", 1),
new SQLiteParameter("@bbb", 1)
});

8. Execute

Execute single SQL query.

  • Execute(string sql)
  • Execute(string sql, Dictionary<string, object> dicParameters = null)
  • Execute(string sql, IEnumerable<SQLiteParameter> parameters = null)

Example:

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

9. ExecuteScalar

Return the result of first row first column in specific data type.

  • ExecuteScalar(string sql)
  • ExecuteScalar(string sql, Dictionary<string, object> dicParameters = null)
  • ExecuteScalar(string sql, IEnumerable<SQLiteParameter> parameters = null)
  • ExecuteScalar<datatype>(string sql)
  • ExecuteScalar<datatype>(string sql, Dictionary<string, object> dicParameters = null)
  • ExecuteScalar<datatype>(string sql, IEnumerable<SQLiteParameter> parameters = null)

Example:

SQLite Helper (C#) zt Collapse | Copy Code
string a = sh.ExecuteScalar<string>("select 'Hello!';");

int b = sh.ExecuteScalar<int>("select 1000;");

decimal c = sh.ExecuteScalar<decimal>("select 4.4;");

DateTime d = sh.ExecuteScalar<DateTime>("select date('now');");

byte[] e = sh.ExecuteScalar<byte[]>("select randomblob(16);");

10. Escape

Escape string sequence for text value to avoid SQL injection or invalid SQL syntax to be constructed.

SQLite Helper (C#) zt Collapse | Copy Code
sh.Execute("insert into person(name) values('" + Escape(input) + "');");

11. Insert

Insert new row of data. All data will be added as parameters at code behind. This support blob (byte[]) value too.

SQLite Helper (C#) zt Collapse | Copy Code
var dic = new Dictionary<string, object>();
dic["name"] = "John";
dic["membershipid"] = 1;
dic["level"] = 6.8; sh.Insert("person", dic);

12. Update

Update row. All data will be added as parameters at code behind. This support blob (byte[]) value too.

Example 1: Update with single condition (where id = 1)

SQLite Helper (C#) zt 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);

Example 2: Update with multiple condition (where membership = 1 and level = 5.5 and teamid = 1)

SQLite Helper (C#) zt 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, object>();
dicCondition["membershipid"] = 1;
dicCondition["level"] = 5.5;
dicCondition["teamid"] = 1; sh.Update("person", dicData, dicCondition);

13. LastInsertRowId

Get the last issued id (Auto-Increment)

SQLite Helper (C#) zt Collapse | Copy Code
sh.Insert("person", dicData);
long id = sh.LastInsertRowId();

14. RenameTable

Rename a table.

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

15. CopyAllData

Copy all data from one table to another.

SQLite Helper (C#) zt 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.

16. DropTable

Drop table, delete a table

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

17. ShowDatabase

Display attached databases.

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

18. AttachDatabase, DetachDatabase

Attach or detach a database

SQLite Helper (C#) zt Collapse | Copy Code
sh.AttachDatabase("C:\\data2013.sq3", "lastyeardb");
sb.DetachDatabase("lastyeardb");

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

SQLite Helper (C#) zt的更多相关文章

  1. sqlite helper

    //-------------------------------------------------------------------------- // // Copyright (c) BUS ...

  2. SQLite Helper &lpar;C&num;&rpar; z

    http://www.codeproject.com/Articles/746191/SQLite-Helper-Csharp Introduction I have written a small ...

  3. C&num;操作SQLite数据库

    SQLite介绍 SQLite is a software library that implements a self-contained, serverless, zero-configurati ...

  4. Android中多表的SQLite数据库(译)

    原文: Android SQLite Database with Multiple Tables 在上一篇教程Android SQLite Database Tutorial中,解释了如何在你的And ...

  5. C&num; SQLite 创建数据库的方法增删查改语法和命令

    SQLite介绍 SQLite是一个开源.免费的小型RDBMS(关系型数据库),能独立运行.无服务器.零配置.支持事物,用C实现,内存占用较小,支持绝大数的SQL92标准. SQLite数据库官方主页 ...

  6. 安卓APP与智能硬件相结合的简易方案

    第1章 概  述 (作者小波QQ463431476) (来源http://blog.chinaaet.com/zhaocundang/p/5100017645博客) (来源   http://www. ...

  7. Spring JavaMail发送邮件

    JavaMail的介绍 JavaMail,顾名思义,提供给开发者处理电子邮件相关的编程接口.它是Sun发布的用来处理email的API.它可以方便地执行一些常用的邮件传输.   虽然JavaMail是 ...

  8. HRPlugin For Xcode发布(附源码地址)

    今天给大家介绍的这个插件,是我在IOS平台上开发以来,一些想法的集合体.因为本人时常感觉在开发过程中无论从GOOGLE资料查找和SQL数据库查询,正则表达式测试,SVN等,这些经常要做的操作中,耽误了 ...

  9. 使用Rxjava缓存请求

    最近,我尝试使用RxJava开发了一款闲时备份app.我必须承认,一旦你get到了正确的方式,RxJava几乎感觉就像作弊.一切看起来更简洁,多个请求能够被组合,且非常容易控制.通过在UI线程观察和在 ...

随机推荐

  1. 如何修改Windows Azure Web App的时区

    Windows Azure的时区默认使用UTC,如果是VM可以直接修改,但Azure Web Apps的隔离性导致我们无法进行设置.应用系统使用DateTime.Now的地方很多,不可能做出一一修改. ...

  2. webservice调用接口,接口返回数组类型

    1. 其中sendSyncMsg1接口是方法名,Vector实现了List接口,xml是sendSyncMsg1的方法形参 Service service = new Service(); Call ...

  3. Hadoop 2、配置HDFS HA &lpar;高可用&rpar;

    前提条件 先搭建 http://www.cnblogs.com/raphael5200/p/5152004.html 的环境,然后在其基础上进行修改 一.安装Zookeeper 由于环境有限,所以在仅 ...

  4. ios9API基础知识总结(二)

    UIAlertView(警告框) UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"标题" message:@&qu ...

  5. ZOJ 2675 Little Mammoth&lpar;计算几何&rpar;

    圆形与矩形截面的面积 三角仍然可以做到这一点 代码: #include<stdio.h> #include<string.h> #include<stdlib.h> ...

  6. Linux - 简明Shell编程03 - 字符串(String)

    脚本地址 https://github.com/anliven/L-Shell/tree/master/Shell-Basics 示例脚本及注释 #!/bin/bash str="Shell ...

  7. Java获取当前的年月

    今天,我在尝试从数据库取数据的过程中,发现页面初始化时需要给时间控件赋初值.于是,我就写了一个获取当前年月的时间工具类. 1.具体源码如下: YearAndMonth.java: /** * @Tit ...

  8. &lpar;cvpr 2018&rpar;Technology details of SMRD

    1.摘要 近年来,深度卷积神经网络(CNN)方法在单幅图像超分辨率(SISR)领域取得了非常大的进展.然而现有基于 CNN 的 SISR 方法主要假设低分辨率(LR)图像由高分辨率(HR)图像经过双三 ...

  9. delphi with&period;&period;&period; do和自定义变量重名

    with类中的变量和外部变量如果重名,会将外部变量覆盖,这点需要注意!!!!!

  10. springboot配置多数据源mongodb

    参考大佬的文章 https://juejin.im/entry/5ab304dd51882555825241b3