SqlLiteデータベースヘルプクラスと基本DEMO

23456 ワード
title:SqlLiteデータベースヘルプ類と基本DEMO categories:Codeing date:2019-10-15:05:13 tags:[C菵、プログラミング開発、実用教程、DEMO]thumbnail:http://hemrj.cn/%E5%BE%AE%E4%BF%A1%E5%9B%BE%E7%89%87_2010152920.jpg---
SqlLiteデータベースヘルプクラスと基本DEMO
SQLliteのメリット
◇   
SQLite C/S          ，           ，                。  SQLite               ，           。             ，   3.6.11  ，Windows 487KB、Linux 347KB。
◇    
SQLite          ：                 ，       “  ”。                。
◇    
   “    ”，           （   、  、   、 ）         。      copy           ，     。
◇   /    
           ，         。        ，SQLite             。                   （  Android、WindowsMobile、Symbin、Palm、VxWorks ）   。
◇     （in-memory database）
   ，       ，    PC    GB        （        ）。   ，SQLite               。
SQLite API                    （          ）。         I/O         ，            。     ，  SQLite         ，         Load   ，          Dump    OK 。      ，     “onlinebackup API”      ，                backup    ？
参照を追加
NuGet管理プログラム-->ブラウズ-->System.Data.SQLiteを検索して、参照をインストールします。
ここで直接ダウンロードして、System.Data.SQLite.dllを参照して、SQLite.Interop.dll.zipをdebugディレクトリの下に置いてください。
1.http://hemrj.cn/System.Data.SQLite.dll 2.http://hemrj.cn/SQLite.Interop.dll.zip
Sql Liteヘルプクラス
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Nine.UnitlLibrary
{
    ///  
    /// SQLite        
    ///           :
    /// Execute,Save,Update,Delete...
    /// @author Nine
    /// 
    public class SqlLiteHepler
    {

        private bool _showSql = true;

        /// 
        ///        SQL  
        /// 
        public bool ShowSql
        {
            get
            {
                return this._showSql;
            }
            set
            {
                this._showSql = value;
            }
        }

        private readonly string _dataFile;

        private SQLiteConnection _conn;

        public SqlLiteHepler(string dataFile)
        {
            if (dataFile == null)
                throw new ArgumentNullException("dataFile=null");
            this._dataFile = dataFile;
        }

        /// 
        ///   SQLiteManager        
        /// 
        public void Open()
        {
            this._conn = OpenConnection(this._dataFile);
        }

        public void Close()
        {
            if (this._conn != null)
            {
                this._conn.Close();
            }
        }

        /// 
        ///        ,         
        /// 
        public void CloseQuietly()
        {
            if (this._conn != null)
            {
                try
                {
                    this._conn.Close();
                }
                catch { }
            }
        }

        /// 
        ///               SQLiteConnection, Open
        ///        ,   
        /// 
        /// 
        /// 
        public static SQLiteConnection OpenConnection(string dataFile)
        {
            if (dataFile == null)
                throw new ArgumentNullException("dataFile=null");

            if (!File.Exists(dataFile))
            {
                SQLiteConnection.CreateFile(dataFile);
            }

            SQLiteConnection conn = new SQLiteConnection();
            SQLiteConnectionStringBuilder conStr = new SQLiteConnectionStringBuilder
            {
                DataSource = dataFile
            };
            conn.ConnectionString = conStr.ToString();
            conn.Open();
            return conn;
        }

        /// 
        ///      SQLiteManager        
        /// 
        public SQLiteConnection Connection
        {
            get
            {
                return this._conn;
            }
            set
            {
                if (value == null)
                {
                    throw new ArgumentNullException();
                }
                this._conn = value;
            }
        }

        protected void EnsureConnection()
        {
            if (this._conn == null)
            {
                throw new Exception("SQLiteManager.Connection=null");
            }
        }

        public string GetDataFile()
        {
            return this._dataFile;
        }

        /// 
        ///    table    
        /// 
        /// 
        /// 
        public bool TableExists(string table)
        {
            if (table == null)
                throw new ArgumentNullException("table=null");
            this.EnsureConnection();
            // SELECT count(*) FROM sqlite_master WHERE type='table' AND name='test';
            SQLiteCommand cmd = new SQLiteCommand("SELECT count(*) as c FROM sqlite_master WHERE type='table' AND name=@tableName ");
            cmd.Connection = this.Connection;
            cmd.Parameters.Add(new SQLiteParameter("tableName", table));
            SQLiteDataReader reader = cmd.ExecuteReader();
            reader.Read();
            int c = reader.GetInt32(0);
            reader.Close();
            reader.Dispose();
            cmd.Dispose();
            //return false;
            return c == 1;
        }

        /// 
        ///   SQL,        
        ///           
        /// paramArr == null      
        /// 
        /// 
        /// 
        public int ExecuteNonQuery(string sql, SQLiteParameter[] paramArr)
        {
            if (sql == null)
            {
                throw new ArgumentNullException("sql=null");
            }
            this.EnsureConnection();

            if (this.ShowSql)
            {
                Console.WriteLine("SQL: " + sql);
            }

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandText = sql;
            if (paramArr != null)
            {
                foreach (SQLiteParameter p in paramArr)
                {
                    cmd.Parameters.Add(p);
                }
            }
            cmd.Connection = this.Connection;
            int c = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return c;
        }

        /// 
        ///   SQL,  SQLiteDataReader
        ///    Reader     ,     Read()  
        /// paramArr=null,      
        /// 
        /// 
        /// 
        /// 
        public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] paramArr)
        {
            return (SQLiteDataReader)ExecuteReader(sql, paramArr, (ReaderWrapper)null);
        }

        /// 
        ///   SQL,  readerWrapper!=null,     readerWrapper SQLiteDataReader    ,     
        /// 
        /// 
        /// null      
        /// null     SQLiteDataReader
        /// 
        public object ExecuteReader(string sql, SQLiteParameter[] paramArr, ReaderWrapper readerWrapper)
        {
            if (sql == null)
            {
                throw new ArgumentNullException("sql=null");
            }
            this.EnsureConnection();

            SQLiteCommand cmd = new SQLiteCommand(sql, this.Connection);
            if (paramArr != null)
            {
                foreach (SQLiteParameter p in paramArr)
                {
                    cmd.Parameters.Add(p);
                }
            }
            SQLiteDataReader reader = cmd.ExecuteReader();
            object result = null;
            if (readerWrapper != null)
            {
                result = readerWrapper(reader);
            }
            else
            {
                result = reader;
            }
            reader.Close();
            reader.Dispose();
            cmd.Dispose();
            return result;
        }

        /// 
        ///   SQL,     ,  RowWrapper        
        ///        ,     List (List.Count=0)
        /// rowWrapper = null ,  WrapRowToDictionary
        /// 
        /// 
        /// 
        /// 
        /// 
        public List ExecuteRow(string sql, SQLiteParameter[] paramArr, RowWrapper rowWrapper)
        {
            if (sql == null)
            {
                throw new ArgumentNullException("sql=null");
            }
            this.EnsureConnection();

            SQLiteCommand cmd = new SQLiteCommand(sql, this.Connection);
            if (paramArr != null)
            {
                foreach (SQLiteParameter p in paramArr)
                {
                    cmd.Parameters.Add(p);
                }
            }

            if (rowWrapper == null)
            {
                rowWrapper = new RowWrapper(SqlLiteHepler.WrapRowToDictionary);
            }

            SQLiteDataReader reader = cmd.ExecuteReader();
            List result = new List();
            if (reader.HasRows)
            {
                int rowNum = 0;
                while (reader.Read())
                {
                    object row = rowWrapper(rowNum, reader);
                    result.Add(row);
                    rowNum++;
                }
            }
            reader.Close();
            reader.Dispose();
            cmd.Dispose();
            return result;
        }

        public static object WrapRowToDictionary(int rowNum, SQLiteDataReader reader)
        {
            int fc = reader.FieldCount;
            Dictionary row = new Dictionary();
            for (int i = 0; i < fc; i++)
            {
                string fieldName = reader.GetName(i);
                object value = reader.GetValue(i);
                row.Add(fieldName, value);
            }
            return row;
        }

        /// 
        ///   insert into  
        /// 
        /// 
        /// 
        /// 
        public int Save(string table, Dictionary entity)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table=null");
            }
            this.EnsureConnection();
            string sql = BuildInsert(table, entity);
            return this.ExecuteNonQuery(sql, BuildParamArray(entity));
        }

        private static SQLiteParameter[] BuildParamArray(Dictionary entity)
        {
            List list = new List();
            foreach (string key in entity.Keys)
            {
                list.Add(new SQLiteParameter(key, entity[key]));
            }
            if (list.Count == 0)
                return null;
            return list.ToArray();
        }

        private static string BuildInsert(string table, Dictionary entity)
        {
            StringBuilder buf = new StringBuilder();
            buf.Append("insert into ").Append(table);
            buf.Append(" (");
            foreach (string key in entity.Keys)
            {
                buf.Append(key).Append(",");
            }
            buf.Remove(buf.Length - 1, 1); //       ,
            buf.Append(") ");
            buf.Append("values(");
            foreach (string key in entity.Keys)
            {
                buf.Append("@").Append(key).Append(","); //       
            }
            buf.Remove(buf.Length - 1, 1);
            buf.Append(") ");

            return buf.ToString();
        }

        private static string BuildUpdate(string table, Dictionary entity)
        {
            StringBuilder buf = new StringBuilder();
            buf.Append("update ").Append(table).Append(" set ");
            foreach (string key in entity.Keys)
            {
                buf.Append(key).Append("=").Append("@").Append(key).Append(",");
            }
            buf.Remove(buf.Length - 1, 1);
            buf.Append(" ");
            return buf.ToString();
        }

        /// 
        ///   update  
        /// where       'where'   
        /// 
        ///   where=null,    whereParams
        ///   where!=null,whereParams=null,where     
        /// 
        /// 
        /// 
        /// 
        /// 
        /// 
        public int Update(string table, Dictionary entity, string where, SQLiteParameter[] whereParams)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table=null");
            }
            this.EnsureConnection();
            string sql = BuildUpdate(table, entity);
            SQLiteParameter[] arr = BuildParamArray(entity);
            if (where != null)
            {
                sql += " where " + where;
                if (whereParams != null)
                {
                    SQLiteParameter[] newArr = new SQLiteParameter[arr.Length + whereParams.Length];
                    Array.Copy(arr, newArr, arr.Length);
                    Array.Copy(whereParams, 0, newArr, arr.Length, whereParams.Length);

                    arr = newArr;
                }
            }
            return this.ExecuteNonQuery(sql, arr);
        }

        /// 
        ///       ,      null
        /// conditionCol = null      ,    select * from table 
        /// 
        /// 
        /// 
        /// 
        /// 
        public Dictionary QueryOne(string table, string conditionCol, object conditionVal)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table=null");
            }
            this.EnsureConnection();

            string sql = "select * from " + table;
            if (conditionCol != null)
            {
                sql += " where " + conditionCol + "=@" + conditionCol;
            }
            if (this.ShowSql)
            {
                Console.WriteLine("SQL: " + sql);
            }

            List list = this.ExecuteRow(sql, new SQLiteParameter[] {
                new SQLiteParameter(conditionCol,conditionVal)
            }, null);
            if (list.Count == 0)
                return null;
            return (Dictionary)list[0];
        }
        /// 
        ///   delete from table   
        /// where    'where'   
        /// where=null    whereParams
        /// 
        /// 
        /// 
        /// 
        /// 
        public int Delete(string table, string where, SQLiteParameter[] whereParams)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table=null");
            }
            this.EnsureConnection();
            string sql = "delete from " + table + " ";
            if (where != null)
            {
                sql += "where " + where;
            }

            return this.ExecuteNonQuery(sql, whereParams);
        }

        #region NINE 19.10.10
        ///  
        ///         ，           DataTable 
        ///  
        ///          
        ///   SQL          ，        SQL         
        ///  
        public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteCommand command = new SQLiteCommand(sql, _conn))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                DataTable data = new DataTable();
                adapter.Fill(data);
                return data;
            }
        }
        ///  
        ///   SQLite      
        ///  
        ///     SQLite        
        public static void CreateDB(string dbPath)
        {
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)";
                    command.ExecuteNonQuery();
                    command.CommandText = "DROP TABLE Demo";
                    command.ExecuteNonQuery();
                }
            }
        }
        ///  
        ///  SQLite          ，        。 
        ///  
        ///         SQL   
        ///              ，        SQL         
        ///  
        public int ExecuteNonQuerySql(string sql, SQLiteParameter[] parameters)
        {
            int affectedRows = 0;
            using (DbTransaction transaction = _conn.BeginTransaction())
            {
                using (SQLiteCommand command = new SQLiteCommand(_conn))
                {
                    command.CommandText = sql;
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    affectedRows = command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            return affectedRows;
        }
        ///  
        ///         ，       SQLiteDataReader   
        ///  
        ///          
        ///   SQL          ，        SQL         
        ///  
        public SQLiteDataReader ExecuteReaderSql(string sql, SQLiteParameter[] parameters)
        {
            SQLiteCommand command = new SQLiteCommand(sql, _conn);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        ///  
        ///         ，              
        ///  
        ///          
        ///   SQL          ，        SQL         
        ///  
        public Object ExecuteScalar(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteCommand command = new SQLiteCommand(sql, _conn))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                DataTable data = new DataTable();
                adapter.Fill(data);
                return data;
            }
        }

        ///  
        ///                 
        ///  
        ///  
        public DataTable GetSchema()
        {
            using (SQLiteConnection connection = new SQLiteConnection(_conn))
            {
                DataTable data = connection.GetSchema("TABLES");
                connection.Close();
                //foreach (DataColumn column in data.Columns) 
                //{ 
                //  Console.WriteLine(column.ColumnName); 
                //} 
                return data;
            }
        }
        #endregion
    }

    /// 
    ///  SQLiteManager.Execute     , SQLiteDataReader   object 
    /// 
    /// 
    /// 
    public delegate object ReaderWrapper(SQLiteDataReader reader);

    /// 
    ///  SQLiteDataReader     object
    /// 
    /// 
    /// 
    /// 
    public delegate object RowWrapper(int rowNum, SQLiteDataReader reader);
}
Sql Lite DEMO
using System;
using System.Data.SQLite;
using System.Windows;

namespace SqlLiteHelper
{
    /// 
    /// MainWindow.xaml      
    /// 
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            SqlLiteHepler sqlLiteHepler = new SqlLiteHepler(@"D:\NineBackstageToolsDB.db");
            sqlLiteHepler.Open();
            //insert
            string insertSql = "INSERT INTO CountUserData(JsonData) values (@JsonData)";
            SQLiteParameter[] insertParameters = new SQLiteParameter[]{
            new SQLiteParameter("@JsonData","test") };
            int insertRowCount = sqlLiteHepler.ExecuteNonQuerySql(insertSql, insertParameters);
            //update
            string updateSql = "Update CountUserData  set JsonData= @JsonData where id = @ID";
            SQLiteParameter[] updateParameters = new SQLiteParameter[]{
            new SQLiteParameter("@JsonData","update"),
            new SQLiteParameter("@ID","3")
            };
            int updateRowCount = sqlLiteHepler.ExecuteNonQuerySql(updateSql, updateParameters);
            //delete
            string deleteSql = "delete from CountUserData where id = @ID";
            SQLiteParameter[] deleteParameters = new SQLiteParameter[]{
            new SQLiteParameter("@ID","4")
            };
            int deleteRowCount = sqlLiteHepler.ExecuteNonQuerySql(deleteSql, deleteParameters);
            //select
            string sqlText = "select * from XyhisLog where (logtime) > (@logtime)";
            SQLiteParameter[] selectParameters = new SQLiteParameter[]{
            new SQLiteParameter("@logtime",DateTime.Now.Date.ToString().Replace("/","-")) };
            var ss = sqlLiteHepler.ExecuteDataTable(sqlText, selectParameters);

        }
    }
}
Sql LiteHelper Demoソースのダウンロードhttp://hemrj.cn/SqlLiteHelper.zip
[BZOJ 2987][クラスユークリッドアルゴリズム]Earthquake
（2018-04-16 PythonはZeroからOneまで）五、正則式_u u5.1.10小練習