C#操作sqlite

由于前几个月使用c#开发了一个程序，由于数据量并不是太大，数据库使用的是sqlite。下面是个人在开发时的总结记录。

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

using System.Data.SQLite;
using System.Data;
using System.Data.Common;
using System.IO;

using System.Diagnostics;
using System.Collections;

MainApp
{

    /// <summary>
    /// 本类为SQLite数据库帮助静态类,使用时只需直接调用即可,无需实例化
    /// </summary>

    public static class CSQLite
    {
        private static Object mutext = new object();
        private static string dbpath = CPublic.getFilePath(@"\app.s3db");
        private static string Conn = "Data Source=" + dbpath + ";Version=3;Pooling=true;";
        #region 创建数据库表结构
        /// <summary>
        /// 创建db数据库
        /// </summary>
        /// <returns>布尔值，是否创建成功</returns>
        public static bool CreateDatabase()
        {
            try
            {

                FileInfo DatabaseFile = new FileInfo(dbpath);
                if (!DatabaseFile.Exists)
                {
                    if (!DatabaseFile.Directory.Exists)
                    {
                        DatabaseFile.Directory.Create();
                    }
                    SQLiteConnection.CreateFile(DatabaseFile.FullName);

                    CreateTable();
                }
            }
            catch (Exception e)
            {
                CPublic.WriteLog(e.ToString());
            }

            return File.Exists(dbpath);
        }

        /// <summary>
        /// 执行数据库查询，返回conn对象
        /// </summary>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>conn数据库连接对象</returns>
        public static SQLiteConnection getConn()
        {
            SQLiteConnection conn = new SQLiteConnection(Conn);
            conn.Open();
            return conn;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>关闭数据库连接对象</returns>
        public static void db_close(DbDataReader reader, SQLiteConnection conn)
        {
            if (reader != null)
                reader.Close();
            if (conn != null && conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }

        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="reader"></param>
        public static void dbClose(SQLiteDataReader reader,SQLiteConnection conn)
        {
            if (reader != null)
                reader.Close();
            if (conn != null && conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }

        /// <summary>
        ///     创建表
        /// </summary>
        public static void CreateTableTable()
        {
            StringBuilder photoTable = new StringBuilder();
            photoTable.Append("CREATE TABLE BaseVehicle");
            photoTable.Append("(");
            photoTable.Append("Coding           nvarchar(100) primary key,");
            photoTable.Append(")");
            ExecuteNonQuery(photoTable.ToString());
        }

        /// <summary>
        ///     创建表
        /// </summary>       
        private static void CreateTable()
        {
            CreateTableTable();
        }

        #endregion

        #region 插入：

        /// <summary>
        /// 
        /// </summary>
        public static void InsertTable()
        {
            string sql = " insert or IGNORE into xxxx";
            sql += " (Code)";
            sql += " values";
            sql += " (@Code)";

            SQLiteParameter[] parms = new SQLiteParameter[13];
            parms[0] = new SQLiteParameter() { ParameterName = "@Code", Value = Code };

            lock (mutext)
            {
                ExecuteNonQuery(sql, parms);
            }

        }

       //批量更新，也可以改成批量插入等操作
        public static void UpdateTable()
        {
            string sql = "update table set xxx= @xxx";
            sql += "where code = @code";

            lock (mutext)
            {
                using(SQLiteConnection conn = new SQLiteConnection(Conn))
                {
                    conn.Open();
                    SQLiteCommand cmd = new SQLiteCommand(conn);
                    cmd.CommandText = sql;
                    SQLiteTransaction trans = conn.BeginTransaction();
                    try
                    {
                        foreach (DictionaryEntry entry in hash_table)
                        {
                                //添加具体数据
                                cmd.Parameters.AddRange(parms);
                                cmd.ExecuteNonQuery();
                            }
                        }
                        trans.Commit();
                    }
                    catch (System.Exception ex)
                    {
                        Trace.WriteLine("ExecuteNonQuery2:" + ex.ToString());
                        CPublic.WriteLog("ExecuteNonQuery2:" + ex.ToString());
                        trans.Rollback();
                    }
                    conn.Close();
                }
            }
           
        }

        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 执行数据库操作(新增、更新或删除)
        /// </summary>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>所受影响的行数</returns>
        public static int ExecuteNonQuery(string commandText)
        {
            SQLiteCommand cmd = null;
            SQLiteTransaction trans = null;
            int value = 0;

            using (SQLiteConnection conn = new SQLiteConnection(Conn))
            {
                try
                {
                    conn.Open();
                    cmd = new SQLiteCommand(commandText, conn);
                    trans = conn.BeginTransaction(IsolationLevel.Serializable);
                    value = cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (System.Data.SQLite.SQLiteException ex)
                {
                    Trace.WriteLine("ExecuteNonQuery:" + commandText);
                    trans.Rollback();
                    return value;
                }
                conn.Close();
            }
            return value;

        }

        public static int ExecuteNonQuery(string sql, SQLiteParameter[] parms)
        {
            SQLiteCommand cmd = null;
            SQLiteTransaction trans = null;
            int value = 0;

            using (SQLiteConnection conn = new SQLiteConnection(Conn))
            {
                try
                {
                    conn.Open();
                    cmd = new SQLiteCommand(sql, conn);
                    trans = conn.BeginTransaction(IsolationLevel.Serializable);
                    if (parms != null)
                    {
                        cmd.Parameters.AddRange(parms);
                    }
                    value = cmd.ExecuteNonQuery();
                    trans.Commit();
                    cmd.Parameters.Clear();
                }
                catch (System.Data.SQLite.SQLiteException ex)
                {
                    Trace.WriteLine("ExecuteNonQuery2:" + sql);
                    trans.Rollback();
                    return value;
                }
                conn.Close();
            }
            return value;

        }


        #endregion


        #region ExecuteReader

        /// <summary>
        /// 执行数据库查询，返回SqlDataReader对象
        /// </summary>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>SqlDataReader对象</returns>
        public static DbDataReader ExecuteReader(string commandText, SQLiteConnection conn)
        {

            if (Conn == null || Conn.Length == 0)
                throw new ArgumentNullException("Conn");
            if (commandText == null || commandText.Length == 0)
                throw new ArgumentNullException("commandText");
            DbDataReader reader = null;
            SQLiteCommand cmd = null;
            try
            {
                cmd = new SQLiteCommand(commandText, conn);
                reader = cmd.ExecuteReader();
            }
            catch (System.Data.SQLite.SQLiteException ex)
            {
                Trace.WriteLine("ExecuteReader_motify:"+commandText);
            }
            return reader;
        }
        #endregion