操作 sqlite封装的一个类库

　　　在wince设备上，使用sqlite很方便，基本都能满足项目的需要，速度也不错。在工作中，封装了此类，方便开发。

 

 

 

 

 

 using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.IO;

namespace Austec.DB
{
    public class CSqlite : IDisposable
    {
        public string m_LastError = null;
        private string dbPath = "";
        private SQLiteConnection sqConn = null;
        private SQLiteCommand sqCmd = null;
        private SQLiteTransaction transaction = null;

        private bool m_Result = false;
        public bool Result
        {
            get { return m_Result; }
        }

        /// <summary>
        /// 不允许通过该方式构造此类
        /// </summary>
        private CSqlite(){}

        /// <summary>
        /// 打开数据库
        /// </summary>
        /// <param name="dbPath">数据库路径</param>
        public CSqlite( string dbPath )
        {
            this.dbPath = dbPath;
        }

        ~CSqlite()
        {
            Close();
        }

        #region  打开与关闭

        public bool Open()
        {
            try
            {
                m_Result = false;

                if (sqConn == null)
                {
                    //在打开数据库时，会判断数据库是否存在，如果不存在，则在当前目录下创建一个
                    sqConn= new SQLiteConnection("Data Source=" + dbPath +";Pooling=true;FailIfMissing=false");
                    sqCmd = new SQLiteCommand();
                    sqCmd.Connection = sqConn;
                }

                if (sqConn.State == ConnectionState.Closed)
                {
                    bool dbExsit = System.IO.File.Exists(dbPath);
                    sqConn.Open();
                    /// 如果数据库不存在，sqlite会创建一个空的数据库，在此创建一个无用的表，填充数据库
                    if (!dbExsit)
                    {
                        ExecuteNonQuery("create table Liang ( id nvarchar(1) ) ");
                    }
                }
                m_Result = true;
                return true;
            }
            catch (System.Exception ex)
            {
                m_LastError = ex.Message;
                return false;
            }
        }

        public void Dispose()
        {
            Close();
        }

        public void Close()
        {
            if (sqConn != null)
            {
                if (sqConn.State == ConnectionState.Open)
                {
                    sqConn.Close();
                    sqConn = null;
                    sqCmd = null;
                }
            }
            System.Data.SQLite.SQLiteConnection.ClearAllPools();
        }

        #endregion

      
        /// <summary>
        ///  执行不带返回结果的命令
        /// </summary>
        /// <param name="sqlCmd">查询语句</param>
        /// <returns></returns>
        public bool ExecuteNonQuery(string sqlCmd )
        {
            m_LastError = null;
            m_Result = false;
            try
            {
                sqCmd.CommandText = sqlCmd;
                sqCmd.ExecuteNonQuery();
                m_Result = true;
            }
            catch (System.Exception ex)
            {
                m_LastError= ex.Message;
                return false;
            }
            return true;
        }

        /// <summary>
        /// 执行SQL命令,并返回Read,Read使用完毕,必须关闭
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public SQLiteDataReader ExecuteQuery(string sqlCmd)
        {
            m_LastError = null;
            m_Result = false;
            try
            {
                sqCmd.CommandText = sqlCmd;
                SQLiteDataReader read = sqCmd.ExecuteReader();
                m_Result = true;
                return read;
            }
            catch (System.Exception ex)
            {
                m_LastError = ex.Message;
                return null;
            }
        }

        /// <summary>
        /// 执行SQL命令,并返回Read,Read使用完毕,必须关闭
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public bool ExecuteQueryTable(string sqlCmd, out DataTable dt)
        {
            m_LastError = null;
            m_Result = false;

            dt = new DataTable("liang");

            try
            {
                // 执行查询命令
                SQLiteDataReader read = ExecuteQuery(sqlCmd);
                if ( m_Result)
                {
                    m_Result = false;
                    if ( read==null )
                    {
                        m_LastError = "sqlite error:未查询到数据!";
                        return false;
                    }

                    /// 添充表
                    for( int i=0; i<read.FieldCount;i++)
                    {
                        dt.Columns.Add(new DataColumn(i.ToString()));
                    }
                    while (read.Read())
                    {
                        DataRow row= dt.NewRow();
                        for( int i=0; i<read.FieldCount;i++ )
                        {
                            row[i]=read.GetValue(i).ToString();
                        }
                        dt.Rows.Add(row);
                    }
                    read.Close();
                    m_Result = true;
                    return true;
                }
                return false;
            }
            catch (System.Exception ex)
            {
                m_LastError = ex.Message;
                return false;
            }
        }

        /// <summary>
        /// 执行SQL命令,并返回第一行记录的第一列值
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public object ExecuteScalar( string sqlCmd )
        {
            m_LastError = null;
            m_Result = false;
            try
            {
                sqCmd.CommandText = sqlCmd;
                object ob= sqCmd.ExecuteScalar();
                if ( ob!=null )
                {
                    m_Result = true;
                }
                else
                {
                    m_LastError = "sqlite error:未查询到数据";

                }
                return ob;
            }
            catch (System.Exception ex)
            {
                m_LastError = ex.Message;
                return null;
            }
        }

        /// <summary>
        /// 按照内存表的结构创建表及索引;如果表已经存在，将直接返回
        /// </summary>
        /// <param name="dt">在创建的表</param>
        /// <param name="strIndexField">索引字段,多个字段以逗号分隔</param>
        /// <returns></returns>
        public bool CreateTable(ref DataTable dt, string strIndexField )
        {
            try
            {
                m_Result = false;
                if ( dt==null )
                {
                    return false;
                }
                // 查询表有没有存在
                string sql = "select count(*) from sqlite_master where tbl_name='" + dt.TableName + "' and type='table'";
                object ob = ExecuteScalar(sql);
                if (!m_Result)
                {
                    return false;
                }
                if (Convert.ToInt32(ob) == 1)
                {
                    m_Result = true;
                    return true;
                }

                string strSql="", strCmdSql="";

                strCmdSql = " CREATE TABLE " + dt.TableName + " ( ";
                for (int i = 0; i < dt.Columns.Count; i++ )
                {
                    strSql += dt.Columns[i].ColumnName + " nvarchar(30) ";
                    if ( i<dt.Columns.Count-1)
                    {
                        strSql += ", ";
                    }
                }

                strCmdSql += strSql + " )";
                if (!ExecuteNonQuery(strCmdSql))
                {
                    return false;
                }
               
                if ( strIndexField!=null && strIndexField.Trim().Length>0 )
                {
                    string[] strIndex = strIndexField.Split(',');
                    for (int i = 0; i < strIndex.Length; i++)
                    {
                        strCmdSql = "CREATE INDEX " + dt.TableName + "_index" + i.ToString() +" ON " + dt.TableName + " ( " + strIndex[i] + " )";
                        if (!ExecuteNonQuery(strCmdSql))
                        {
                            return false;
                        }
                    }
                }
                m_Result = true;
                return true;
            }
            catch (System.Exception ex)
            {
                m_LastError = ex.Message;
                return false;
            }
        }

        /// <summary>
        /// 删除表及表的索引
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public bool DropTableAndIndex( string tablename )
        {
            m_Result = false;
            try
            {
                // 查询表及索引有没有存在
                string sql = "select count(*) from sqlite_master where tbl_name='" + tablename + "' and type='table'";
                object ob= ExecuteScalar(sql);
                if (!m_Result)
                {
                    return false;
                }
                if ( Convert.ToInt32(ob)==0 )
                {
                    m_Result=true;
                    return true;
                }
                
                string cmdSql = "drop table " + tablename ;
                if ( !ExecuteNonQuery(cmdSql) )
                {
                    return false;
                }
                m_Result = true;
                return true;
            }
            catch (System.Exception ex)
            {
                m_LastError = ex.Message;
                return false;
            }
        }

        /// <summary>
        /// 将内存表插入相应的表中
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool InsertTable( ref DataTable dt)
        {
            m_Result = false;
            if (dt == null)
            {
                return false;
            }
            if ( dt.Rows.Count==0 )
            {
                m_Result = true;
                return true;
            }

            int i = 0;
            string sql = "insert into " + dt.TableName + " values( ";
            string sqlData = "", strCmdSql = "";

            BeginTransaction();
            foreach (DataRow row in dt.Rows)
            {
                sqlData = "";
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    sqlData += "'" + row[i].ToString() + "'";
                    if (i < dt.Columns.Count - 1)
                    {
                        sqlData += ", ";
                    }
                }
                strCmdSql = sql + sqlData + " )";
                if (!ExecuteNonQuery(strCmdSql))
                {
                    Rollback();
                    return false;
                }
            }
           
            Commit();
            m_Result = true;
            return true;
        }

        /// <summary>
        /// 删除指定表的数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public bool DeleteTableData( string tableName )
        {
            m_Result = false;

            string sql = "select count(*) from sqlite_master where tbl_name='" + tableName + "' and type='table'";
            object ob= ExecuteScalar(sql);
            if (!m_Result)
            {
                return false;
            }
            m_Result = false;
            if ( Convert.ToInt32(ob)==0 )
            {
                m_LastError = "被删除数据的表不存在!";
                return false;
            }

            sql = "delete from " + tableName;
            if ( ExecuteNonQuery(sql) )
            {
                m_Result = true;
                return true;
            }
            else
            {
                return false;
            }
        }

        #region 事务操作
        public void BeginTransaction()
        {
            transaction = sqConn.BeginTransaction();
        }

        public void Commit()
        {
            transaction.Commit();
        }

        public void Rollback()
        {
            transaction.Rollback();
        }
        #endregion
    }
}