Sqlite 的 DatabaseContext

 

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

namespace BBKWMS.Util
{
    public class DatabaseContext : IDisposable
    {
        private IDbTransaction _transaction;
        public IDbTransaction Transaction
        {
            get { return _transaction; }
            set { _transaction = value; }
        }

        private IDbConnection _connection;
        public IDbConnection Connection
        {
            get { return _connection; }
            set { _connection = value; }
        }

        public DatabaseContext(string dbName, bool useTrans = false)
        {
            string dbPath = Path.Combine(Environment.CurrentDirectory, dbName);
            string _connectionString = @"Data Source=" + dbPath + ";Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10";
            //ConnectionStringSettings settings = new ConnectionStringSettings("connName", _connectionString, "System.Data.SQLite");
            //DbProviderFactory provider = DbProviderFactories.GetFactory(settings.ProviderName);
            Connection = new SQLiteConnection(_connectionString);
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            if (useTrans)
            {
                Transaction = Connection.BeginTransaction();//IsolationLevel.ReadCommitted
            }
        }

        public void CommitTrans()
        {
            if (Transaction != null)
            {
                Transaction.Commit();
            }
        }

        public void RollbackTrans()
        {
            if (Transaction != null)
            {
                Transaction.Rollback();
            }
        }


        public int ExecuteNonQuery(string commandText, List<IDbDataParameter> cmdParms = null)
        {
            int result = 0;
            IDbCommand cmd = Connection.CreateCommand();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = commandText;
            cmd.Transaction = Transaction;
            if (cmdParms != null)
            {
                foreach (IDbDataParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm); //cmd.Parameters.AddWithValue("@uNavnParam", uNavn);
                }
            }
            result = cmd.ExecuteNonQuery();
            return result;
        }

        public DbDataReader ExecuteReader(string commandText, List<IDbDataParameter> cmdParms = null)
        {
            DbDataReader dbReader = default(DbDataReader); 
            IDbCommand cmd = Connection.CreateCommand();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = commandText;
            cmd.Transaction = Transaction;
            if (cmdParms != null)
            {
                foreach (IDbDataParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
            dbReader = (DbDataReader)cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return dbReader;
        }

        public DataSet ExecuteDataSet(string commandText, List<IDbDataParameter> cmdParms = null)
        {
            DataSet ds = new DataSet();
            IDbCommand cmd = Connection.CreateCommand();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = commandText;
            cmd.Transaction = Transaction;
            if (cmdParms != null)
            {
                foreach (IDbDataParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
            DbDataAdapter sda = new SQLiteDataAdapter((SQLiteCommand)cmd);
            sda.Fill(ds);
            return ds;
        }

        //public void xx()
        //{
        //    System.Configuration.ConnectionStringSettings
        //    ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["DB"];
        //    DbProviderFactory provider = DbProviderFactories.GetFactory(settings.ProviderName);
        //    using (DbConnection conn = provider.CreateConnection())
        //    {
        //        conn.ConnectionString = settings.ConnectionString;
        //        conn.Open();
        //        DbCommand cmd = conn.CreateCommand();
        //        cmd.CommandText = "Select top 10 * From ShortTermBill";
        //        DbDataAdapter da = provider.CreateDataAdapter();
        //        da.SelectCommand = cmd;
        //        DataSet ds = new DataSet();
        //        da.Fill(ds);
        //        da.Dispose();
        //    }
        //}

        public void Dispose()
        {
            if (Connection != null)
            {
                if (Connection.State == ConnectionState.Open)
                {
                    //关闭连接时，事务会自动回滚
                    //if (Transaction != null)
                    //{
                    //    Transaction.Commit();
                    //}
                    Connection.Close();
                }
                Connection.Dispose();
            }
        }
    }
    /*
     using (DatabaseContext dc = new DatabaseContext("cms_language.db", true))
            {
                try
                {
                    //string executeSql = @"INSERT INTO language (lid,area,section,lkey,lvalue) VALUES (@lid,@area, @section,@lkey,@lvalue );";
                    //List<IDbDataParameter> cmdParms = new List<IDbDataParameter>();
                    //cmdParms.Add(new System.Data.SQLite.SQLiteParameter("@lid", DbType.Int32){ Value = 18});
                    //cmdParms.Add(new System.Data.SQLite.SQLiteParameter("@area", DbType.String) { Value = Guid.NewGuid().ToString() });
                    //cmdParms.Add(new System.Data.SQLite.SQLiteParameter("@section", DbType.String) { Value = Guid.NewGuid().ToString() });
                    //cmdParms.Add(new System.Data.SQLite.SQLiteParameter("@lkey", DbType.String) { Value = Guid.NewGuid().ToString() });
                    //cmdParms.Add(new System.Data.SQLite.SQLiteParameter("@lvalue", DbType.String) { Value = Guid.NewGuid().ToString() });
                    //dc.ExecuteNonQuery(executeSql, cmdParms);
                    //dc.CommitTrans();

                    DataSet ds = dc.ExecuteDataSet("SELECT lid,area,section,lkey,lvalue FROM language;");

                    System.Data.Common.DbDataReader dr = dc.ExecuteReader("SELECT lid,area,section,lkey,lvalue FROM language;");

                    if (dr.HasRows)//如果有数据 
                    {
                        while (dr.Read())
                        {
                            for (int i = 0; i < dr.FieldCount; i++) //逐个字段的遍历
                            {
                                string xx = dr[i].ToString();
                            }
                        }
                    }
                    dc.CommitTrans();
                }
                catch (Exception ex)
                {
                    dc.RollbackTrans();
                }
            }
     */
}