针对对数据库的操作情况,分为以下几种情况:
创建数据库文件;
返回DataTable;
返回DataReader;
执行增删改,返回受影响的行数;
执行查询,返回第一行第一列(通常用于带有行函数的查询,如SUM/AVG/COUNT等);
返回库中所有的表;
因为在System.Data.SQLite中不存在存储过程,所以所有的操作都是基于文本的SQL语句,为了避免SQL注入,所以使用了参数化的SQL语句,这个数据库通用类如下:
创建数据库文件;
返回DataTable;
返回DataReader;
执行增删改,返回受影响的行数;
执行查询,返回第一行第一列(通常用于带有行函数的查询,如SUM/AVG/COUNT等);
返回库中所有的表;
因为在System.Data.SQLite中不存在存储过程,所以所有的操作都是基于文本的SQL语句,为了避免SQL注入,所以使用了参数化的SQL语句,这个数据库通用类如下:
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Reflection;
using System.Collections;
namespace GetTime
{
public class SQLiteDBHelper
{
private string connectionString = string.Empty;
static SQLiteDBHelper()
{
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dbPath">SQLite数据库文件路径</param>
public SQLiteDBHelper(string dbPath)
{
this.connectionString = "Data Source=" + dbPath;
}
/// <summary>
/// 创建SQLite数据库文件
/// </summary>
/// <param name="dbPath">要创建的SQLite数据库文件路径</param>
public static void CreateDB(string dbPath)
{
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE Demo";
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// 对SQLite数据库执行增删改操作,返回受影响的行数。
/// </summary>
/// <param name="sql">要执行的增删改的SQL语句</param>
/// <param name="parameters">执行增删改语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, IList<SQLiteParameter> parameters)
{
int affectedRows = 0;
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = sql;
if (!(parameters == null || parameters.Count == 0))
{
foreach (SQLiteParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
affectedRows = command.ExecuteNonQuery();
}
transaction.Commit();
}
}
return affectedRows;
}
/// <summary>
/// 执行一个查询语句,返回一个关联的SQLiteDataReader实例
/// </summary>
/// <param name="sql">要执行的查询语句</param>
/// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public SQLiteDataReader ExecuteReader(string sql, IList<SQLiteParameter> parameters)
{
SQLiteConnection connection = new SQLiteConnection(connectionString);
SQLiteCommand command = new SQLiteCommand(sql, connection);
if (!(parameters == null || parameters.Count == 0))
{
foreach (SQLiteParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 执行一个查询语句,返回一个包含查询结果的DataTable
/// </summary>
/// <param name="sql">要执行的查询语句</param>
/// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, IList<SQLiteParameter> parameters)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
if (!(parameters == null || parameters.Count == 0))
{
foreach (SQLiteParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable();
adapter.Fill(data);
return data;
}
}
}
/// <summary>
/// 执行一个查询语句,返回查询结果的第一行第一列
/// </summary>
/// <param name="sql">要执行的查询语句</param>
/// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public Object ExecuteScalar(string sql, IList<SQLiteParameter> parameters)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
if (!(parameters == null || parameters.Count == 0))
{
foreach (SQLiteParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
return command.ExecuteScalar();
}
}
}
/// <summary>
/// 查询数据库中的所有数据类型信息
/// </summary>
/// <returns></returns>
public DataTable GetSchema()
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
DataTable data = connection.GetSchema("TABLES");
connection.Close();
//foreach (DataColumn column in data.Columns)
//{
// Console.WriteLine(column.ColumnName);
//}
return data;
}
}
}
}
System.Data.SQLite数据库通用类的用法
下面演示一下刚刚编写的数据库通用类的用法,代码如下:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using SQLiteQueryBrowser;
namespace SQLiteDemo
{
class Program
{
static void Main(string[] args)
{
//CreateTable();
//InsertData();
ShowData();
Console.ReadLine();
}
public static void CreateTable()
{
string dbPath = "D:\\Demo.db3";
//如果不存在改数据库文件,则创建该数据库文件
if (!System.IO.File.Exists(dbPath))
{
SQLiteDBHelper.CreateDB("D:\\Demo.db3");
}
SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3");
string sql = "CREATE TABLE Test3(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,Name char(3),TypeName varchar(50),addDate datetime,UpdateTime Date,Time time,Comments blob)";
db.ExecuteNonQuery(sql, null);
}
public static void InsertData()
{
string sql = "INSERT INTO Test3(Name,TypeName,addDate,UpdateTime,Time,Comments)values(@Name,@TypeName,@addDate,@UpdateTime,@Time,@Comments)";
SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3");
for (char c = 'A'; c <= 'Z'; c++)
{
for (int i = 0; i < 100; i++)
{
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@Name",c+i.ToString()),
new SQLiteParameter("@TypeName",c.ToString()),
new SQLiteParameter("@addDate",DateTime.Now),
new SQLiteParameter("@UpdateTime",DateTime.Now.Date),
new SQLiteParameter("@Time",DateTime.Now.ToShortTimeString()),
new SQLiteParameter("@Comments","Just a Test"+i)
};
db.ExecuteNonQuery(sql, parameters);
}
}
}
public static void ShowData()
{
//查询从50条起的20条记录
string sql = "select * from test3 order by id desc limit 50 offset 20";
SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3");
using (SQLiteDataReader reader = db.ExecuteReader(sql, null))
{
while (reader.Read())
{
Console.WriteLine("ID:{0},TypeName{1}", reader.GetInt64(0), reader.GetString(1));
}
}
}
}
}