public class SqliteDataModel
{
private SQLiteCommand sqliteCommand;
private SQLiteDataAdapter sqliteAdpater;
private SQLiteTransaction sqliteTransaction;
private SQLiteConnection strConn = null;
public SQLiteConnection StrConn
{
get
{
if (strConn == null)
{
string strConnstring = AppDomain.CurrentDomain.BaseDirectory+"test.DB3";
if (!File.Exists(strConnstring))
{
SQLiteConnection.CreateFile(strConnstring);
SQLiteConnectionStringBuilder connStr = new SQLiteConnectionStringBuilder();
connStr.DataSource = strConnstring;
strConn = new SQLiteConnection(connStr.ToString());
if (strConn.State == ConnectionState.Closed)
{
//修改这个参数的默认值的原因是删除数据之后会自动清理数据文件
strConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.CommandText = " PRAGMA auto_vacuum = 1";
cmd.Connection = strConn;
cmd.ExecuteNonQuery();
strConn.Close();
}
}
else
{
SQLiteConnectionStringBuilder connStr = new SQLiteConnectionStringBuilder();
connStr.DataSource = strConnstring;
strConn = new SQLiteConnection(connStr.ToString());
}
}
return strConn;
}
}
public SqliteDataModel()
{
this.Init();
}
private void Init()
{
if (this.sqliteCommand != null)
{
this.sqliteCommand.Dispose();
this.sqliteCommand = null;
}
if (this.sqliteAdpater != null)
{
this.sqliteAdpater.Dispose();
this.sqliteAdpater = null;
}
if (this.sqliteTransaction != null)
{
this.sqliteTransaction.Dispose();
this.sqliteTransaction = null;
}
if (this.strConn != null)
{
this.strConn.Dispose();
}
this.sqliteCommand = new SQLiteCommand();
this.sqliteAdpater = new SQLiteDataAdapter();
this.sqliteCommand.Connection = this.StrConn;
this.sqliteAdpater.SelectCommand = this.sqliteCommand;
}
public int ExeNonQuery(string strSql)
{
int nReturn = 0;
this.sqliteCommand.CommandText = strSql;
try
{
if (this.StrConn.State == ConnectionState.Closed)
{
this.StrConn.Open();
nReturn = sqliteCommand.ExecuteNonQuery();
this.StrConn.Close();
}
else
{
nReturn = sqliteCommand.ExecuteNonQuery();
}
}
catch (SQLiteException ex)
{
Pub.WriteLog(ex.StackTrace);
this.StrConn.Close();
return nReturn;
}
return nReturn;
}
public DataTable ExeQuery(string strSql)
{
this.sqliteCommand.CommandText = strSql;
DataTable dtResult = new DataTable();
try
{
if (this.StrConn.State == ConnectionState.Closed)
{
this.StrConn.Open();
this.sqliteAdpater.Fill(dtResult);
this.StrConn.Close();
}
else
{
this.sqliteAdpater.Fill(dtResult);
}
}
catch (SQLiteException ex)
{
Pub.WriteLog(ex.StackTrace);
return null;
}
return dtResult;
}
/// <summary>
/// 存储二进制文件
/// </summary>
/// <param name="buf">文件缓存</param>
/// <param name="sql"></param>
/// <param name="para">占位符</param>
/// <returns></returns>
public bool ExeNonQuery(byte[] buf, string sql,string para)
{
lock (this)
{
this.sqliteCommand.CommandText = sql;
try
{
if (this.StrConn.State == ConnectionState.Closed)
{
this.StrConn.Open();
this.sqliteTransaction = this.StrConn.BeginTransaction();
this.sqliteCommand.Transaction = this.sqliteTransaction;
sqliteCommand.Parameters.Add(para, DbType.Binary).Value = buf;
sqliteCommand.ExecuteNonQuery();
this.sqliteTransaction.Commit();
this.StrConn.Close();
}
else
{
this.sqliteTransaction = this.StrConn.BeginTransaction();
this.sqliteCommand.Transaction = this.sqliteTransaction;
sqliteCommand.Parameters.Add(para, DbType.Binary).Value = buf;
sqliteCommand.ExecuteNonQuery();
this.sqliteTransaction.Commit();
}
}
catch (Exception ex)
{
Pub.WriteLog(ex.StackTrace);
this.sqliteTransaction.Rollback();
this.StrConn.Close();
return false;
}
}
return true;
}
public SQLiteDataReader ExeQueryDataReader(string strSql)
{
this.sqliteCommand.CommandText = strSql;
SQLiteDataReader dr;
lock (this)
{
try
{
if (this.StrConn.State == ConnectionState.Closed)
{
this.StrConn.Open();
dr = this.sqliteCommand.ExecuteReader();
this.StrConn.Close();
}
else
{
dr = this.sqliteCommand.ExecuteReader();
}
}
catch (System.Data.SQLite.SQLiteException Ex)
{
this.StrConn.Close();
Pub.WriteLog(Ex.StackTrace);
return null;
}
}
return dr;
}
public void BeginTransaction()
{
lock (this)
{
if (this.StrConn.State != ConnectionState.Open)
{
this.StrConn.Open();
}
this.sqliteTransaction = this.StrConn.BeginTransaction();
}
}
public void CommitTransaction()
{
lock (this)
{
this.sqliteTransaction.Commit();
this.sqliteTransaction.Dispose();
this.sqliteTransaction = null;
if (this.StrConn.State != ConnectionState.Closed)
{
this.StrConn.Close();
}
}
}
public void RollBackTransaction()
{
lock (this)
{
try
{
this.sqliteTransaction.Rollback();
}
catch (Exception ex)
{
Pub.WriteLog(ex.StackTrace);
}
this.sqliteTransaction.Dispose();
this.sqliteTransaction = null;
if (this.StrConn.State != ConnectionState.Closed)
{
this.StrConn.Close();
}
}
}
}
Sqlite DBconnection DAL类
最新推荐文章于 2022-02-12 18:10:37 发布