C#连接SQLite3数据库共通类

使用：

1、在解决方案的引用中添加引用

System.Data.SQLite.dll

2、项目添加现有项：

CSqlite.cs

3、修改命名空间

namespace SqliteAccess

SqliteAccess改为自己的项目名

4、 使用

   //创建对象，参数为数据文件路径

            CSqlite csqlite = new CSqlite(@"D:\sqlite3db\World.db");

  //打开数据库

                csqlite.Open();

  //开启事务

                csqlite.BeginTransaction();

…………………………

/// 执行SQL命令,并返回DataTable

 public DataTable ExecuteQueryTable(string sqlCmd)

///  执行SQL命令，返回影响行数

public int ExecuteNonQuery(string sqlCmd)

…………………………

   //提交事务

                csqlite.Commit();

  //回滚事务

                csqlite.Rollback();

 //关闭数据库

                csqlite.Dispose();

 

———————————————————华丽的分割线—————————————————————

 

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

namespace SqliteAccess
{
    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;
                throw ex;
            }
        }

        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>
        ///  执行SQL命令，返回影响行数
        /// </summary>
        /// <param name="sqlCmd">查询语句</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlCmd)
        {
            //m_LastError = null;
            m_Result = false;
            int count = 0;
            try
            {
                sqCmd.CommandText = sqlCmd;
                count = sqCmd.ExecuteNonQuery();
                m_Result = true;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return count;
        }

        /// <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)
            {
                throw ex;
                //m_LastError = ex.Message;
                //return null;
            }
        }

        /// <summary>
        /// 执行SQL命令,并返回DataTable
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public DataTable ExecuteQueryTable(string sqlCmd)
        {
            //m_LastError = null;
            m_Result = false;

            //dt = new DataTable("liang");
            DataTable dt = new DataTable();
            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();

                }
                return dt;
            }
            catch (System.Exception ex)
            {
                throw ex;
                //m_LastError = ex.Message;
                //return dt;
            }
        }

        /// <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)
            {
                throw ex;
                //m_LastError = ex.Message;
                //return null;
            }
        }

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

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

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