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();
                }
            }
        }
    }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!