C# SQLite 帮助类分享

1、在5月份的使用做一个小型管理软件，用到Sqlite, 最近几天又用到，因此整一个C# 上，Sqlite的帮助类 

 如下：

 

 /// <summary>
    /// SQLITE 帮助类
    /// </summary>
    class SQLiteHelper
    {
        private static SQLiteHelper mInstance = null;

        private String mConnectdbPath = "Data Source=" + Environment.CurrentDirectory + "\\active.db";

        private static Object mLockObject = new Object();

        /// <summary>
        /// 构造函数
        /// </summary>
        private SQLiteHelper()
        {
        }

        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static SQLiteHelper GetInstance()
        {
            if (mInstance == null)
            {
                lock (mLockObject)
                {
                    mInstance = new SQLiteHelper();
                }
            }

            return mInstance;
        }


        /// <summary>  
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。  
        /// </summary>  
        /// <param name="sql">要执行的增删改的SQL语句</param>  
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>  
        /// <returns></returns>  
        public int ExecuteNonQuery(String sql, SQLiteParameter[] paramters)
        {
            int affectedRows = 0;

            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();
                    using (DbTransaction transaction = conn.BeginTransaction())
                    {
                        using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                        {
                            cmd.CommandText = sql;
                            if (paramters != null)
                            {
                                cmd.Parameters.AddRange(paramters);
                            }

                            affectedRows = cmd.ExecuteNonQuery();
                        }

                        transaction.Commit();
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return affectedRows;
        }

        /// <summary>  
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。  
        /// </summary>  
        /// <param name="sql">要执行的增删改的SQL语句</param>  
        /// <returns></returns>  
        public int ExecuteNonQuery(String sql)
        {
            int affectedRows = 0;

            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();
                    using (DbTransaction transaction = conn.BeginTransaction())
                    {
                        using (SQLiteCommand cmd = new SQLiteCommand(conn))
                        {
                            cmd.CommandText = sql;
                            affectedRows = cmd.ExecuteNonQuery();
                        }

                        transaction.Commit();
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return affectedRows;
        }

        /// <summary>  
        /// 执行一个查询语句，返回一个包含查询结果的DataTable  
        /// </summary>  
        /// <param name="sql">要执行的查询语句</param>  
        /// <returns></returns>  
        public DataTable ExecuteQueryDataTable(String sql)
        {
            DataTable data = null;

            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();
                    using (DbTransaction transaction = conn.BeginTransaction())
                    {
                        using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                        {
                            SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                            data = new DataTable();

                            adapter.Fill(data);
                        }

                        transaction.Commit();
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return data;
        }
        /*
        public List<RecordJaveBean> ExecuteQuery(String sql)
        {
            try
            {
                mList.Clear();
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();
                    SQLiteCommand cmdRead = new SQLiteCommand(sql, conn);

                    using (SQLiteDataReader reader = cmdRead.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            mList.Add(new RecordJaveBean(reader.GetString(1), reader.GetString(2), reader.GetString(3)));
                        }
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return mList;
        }
        */
        public void ExecuteInsert(String sql)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();

                    SQLiteCommand cmdRead = new SQLiteCommand(sql, conn);
                    cmdRead.ExecuteReader();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        public void ExecuteDelegate(String sql)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();

                    SQLiteCommand cmdRead = new SQLiteCommand(sql, conn);
                    cmdRead.ExecuteReader();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }


        /// <summary>  
        /// 对SQLite数据库获取当家查询结果集的记录总数
        /// </summary>  
        /// <param name="sql">要执行的增删改的SQL语句</param>  
        /// <returns></returns>  
        public int ExecuteGetResultTotalLine(String sql)
        {
            int lines = 0;

            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
                {
                    conn.Open();
                    using (DbTransaction transaction = conn.BeginTransaction())
                    {
                        using (SQLiteCommand cmd = new SQLiteCommand(conn))
                        {
                            cmd.CommandText = sql;
                            lines = Convert.ToInt32(cmd.ExecuteScalar());
                        }

                        transaction.Commit();
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return lines;
        }

    }