为了便于使用SQLite.Net,参照微软企业库中数据组件的写法写了这个类,无非就是为了便于使用。
下面是代码:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
using
System;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
namespace IdentifyCode.Data
{
public class Database
{
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dataSource"> 数据文件 </param>
public Database( string dataSource)
: this (dataSource, false , true )
{
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dataSource"> 数据文件 </param>
/// <param name="readOnly"> 是否只读 </param>
/// <param name="pooling"> 是否使用连接池 </param>
public Database( string dataSource, bool readOnly, bool pooling)
{
if ( ! File.Exists(dataSource))
{
SQLiteConnection.CreateFile(dataSource);
}
SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder
{
DataSource = dataSource,
Version = 3 ,
ReadOnly = readOnly,
Pooling = pooling
};
ConnectionString = sb.ToString();
}
/// <summary>
/// 连接字符串
/// </summary>
public static string ConnectionString { get ; private set ; }
/// <summary>
/// 获取Sql语句命令
/// </summary>
/// <param name = "query"></param>
/// <returns></returns>
public DbCommand GetSqlStringCommand( string query)
{
if (String.IsNullOrEmpty(query))
{
throw new ArgumentException( " 查询字符串不能为空。 " );
}
DbCommand command = new SQLiteCommand(query) { CommandType = CommandType.Text };
return command;
}
public DbCommand GetSqlStringCommand( string query, DbTransaction transaction)
{
DbCommand command = GetSqlStringCommand(query);
command.Transaction = transaction;
return command;
}
public SQLiteTransaction GetTransaction()
{
SQLiteConnection conn = new SQLiteConnection(ConnectionString);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
return conn.BeginTransaction();
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name = "command"></param>
/// <param name = "dbType"></param>
public void AddInParameter(DbCommand command, DbType dbType)
{
command.Parameters.Add( new SQLiteParameter(dbType));
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name = "command"></param>
/// <param name = "dbType"></param>
/// <param name = "value"></param>
public void AddInParameter(DbCommand command, DbType dbType, object value)
{
if (value == null )
{
value = " null " ;
}
command.Parameters.Add( new SQLiteParameter(dbType) { Value = value });
}
/// <summary>
/// 执行
/// </summary>
/// <param name = "command"></param>
/// <returns></returns>
public long ExecuteNonQuery(DbCommand command)
{
// 如果包含事务,就执行返回,不管连接
if (command.Transaction != null )
{
return command.ExecuteNonQuery();
}
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
command.Connection = conn;
return command.ExecuteNonQuery();
}
}
/// <summary>
/// 执行
/// </summary>
/// <param name = "command"></param>
/// <returns></returns>
public DbDataReader ExecuteReader(DbCommand command)
{
SQLiteConnection conn = new SQLiteConnection(ConnectionString);
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
command.Connection = conn;
return command.ExecuteReader();
}
catch
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
throw ;
}
}
public long ExecuteScalar(DbCommand command)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
command.Connection = conn;
object obj = command.ExecuteScalar();
long l = ( long )obj;
return l;
}
}
}
}
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
namespace IdentifyCode.Data
{
public class Database
{
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dataSource"> 数据文件 </param>
public Database( string dataSource)
: this (dataSource, false , true )
{
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dataSource"> 数据文件 </param>
/// <param name="readOnly"> 是否只读 </param>
/// <param name="pooling"> 是否使用连接池 </param>
public Database( string dataSource, bool readOnly, bool pooling)
{
if ( ! File.Exists(dataSource))
{
SQLiteConnection.CreateFile(dataSource);
}
SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder
{
DataSource = dataSource,
Version = 3 ,
ReadOnly = readOnly,
Pooling = pooling
};
ConnectionString = sb.ToString();
}
/// <summary>
/// 连接字符串
/// </summary>
public static string ConnectionString { get ; private set ; }
/// <summary>
/// 获取Sql语句命令
/// </summary>
/// <param name = "query"></param>
/// <returns></returns>
public DbCommand GetSqlStringCommand( string query)
{
if (String.IsNullOrEmpty(query))
{
throw new ArgumentException( " 查询字符串不能为空。 " );
}
DbCommand command = new SQLiteCommand(query) { CommandType = CommandType.Text };
return command;
}
public DbCommand GetSqlStringCommand( string query, DbTransaction transaction)
{
DbCommand command = GetSqlStringCommand(query);
command.Transaction = transaction;
return command;
}
public SQLiteTransaction GetTransaction()
{
SQLiteConnection conn = new SQLiteConnection(ConnectionString);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
return conn.BeginTransaction();
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name = "command"></param>
/// <param name = "dbType"></param>
public void AddInParameter(DbCommand command, DbType dbType)
{
command.Parameters.Add( new SQLiteParameter(dbType));
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name = "command"></param>
/// <param name = "dbType"></param>
/// <param name = "value"></param>
public void AddInParameter(DbCommand command, DbType dbType, object value)
{
if (value == null )
{
value = " null " ;
}
command.Parameters.Add( new SQLiteParameter(dbType) { Value = value });
}
/// <summary>
/// 执行
/// </summary>
/// <param name = "command"></param>
/// <returns></returns>
public long ExecuteNonQuery(DbCommand command)
{
// 如果包含事务,就执行返回,不管连接
if (command.Transaction != null )
{
return command.ExecuteNonQuery();
}
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
command.Connection = conn;
return command.ExecuteNonQuery();
}
}
/// <summary>
/// 执行
/// </summary>
/// <param name = "command"></param>
/// <returns></returns>
public DbDataReader ExecuteReader(DbCommand command)
{
SQLiteConnection conn = new SQLiteConnection(ConnectionString);
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
command.Connection = conn;
return command.ExecuteReader();
}
catch
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
throw ;
}
}
public long ExecuteScalar(DbCommand command)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
command.Connection = conn;
object obj = command.ExecuteScalar();
long l = ( long )obj;
return l;
}
}
}
}
使用方法
1.带事务:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
Database db
=
new
Database(dataSource);
using (SQLiteTransaction tran = db.GetTransaction())
{
using (DbCommand command = db.GetSqlStringCommand(sqlString, tran))
{
foreach (Entity entity in list)
{
db.AddInParameter(command, DbType.String, ...);
...
db.ExecuteNonQuery(command);
command.Parameters.Clear();
}
tran.Commit();
}
}
using (SQLiteTransaction tran = db.GetTransaction())
{
using (DbCommand command = db.GetSqlStringCommand(sqlString, tran))
{
foreach (Entity entity in list)
{
db.AddInParameter(command, DbType.String, ...);
...
db.ExecuteNonQuery(command);
command.Parameters.Clear();
}
tran.Commit();
}
}
2.不带事务:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
Database db
=
new
Database(dataSource);
using (DbCommand command = db.GetSqlStringCommand(sqlString))
{
foreach (Entity entity in list)
{
db.AddInParameter(command, DbType.String, ...);
...
db.ExecuteNonQuery(command);
command.Parameters.Clear();
}
}
using (DbCommand command = db.GetSqlStringCommand(sqlString))
{
foreach (Entity entity in list)
{
db.AddInParameter(command, DbType.String, ...);
...
db.ExecuteNonQuery(command);
command.Parameters.Clear();
}
}
因为SQLite默认对每个操作打开一个事务,所以如果不声明事务的话,对于任何影响超过1行数据的操作都将会消耗非常长的时间。