前端之家收集整理的这篇文章主要介绍了
Sqlite DBconnection DAL类,
前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
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();
}
}
}
}