关于Sqlite的一个demo

最新推荐文章于 2020-05-05 20:55:24 发布

weixin_34273046

最新推荐文章于 2020-05-05 20:55:24 发布

阅读量50

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/softidea/p/3225687.html

本文介绍了一个使用C#实现的SQLite数据库辅助类，包括数据库和数据表的创建、执行SQL语句及读取数据等核心功能。通过示例代码展示了如何进行数据库连接、创建表、执行SQL事务以及查询数据。

直接上代码：

    class DBHelperSqlite
    {
        ILog logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private string dbName = "cmcc2.db";
        private string connectionString = "Data Source=cmcc2.db;Pooling=true;FailIfMissing=false";
        /// <summary>
        /// if the db is not exists,create it and create the table
        /// </summary>
        public DBHelperSqlite()
        {
            string SQLCreateStr = "CREATE TABLE [cmcc_businesses_info] (businessName nvarchar(50),businessDate nvarchar(10),indb_datetime nvarchar(20))";

            #region CASE2
            FileInfo fi = new FileInfo(dbName);
            if (fi.Exists == false)
            {
                logger.InfoFormat("db不存在");
                //Console.WriteLine("db不存在");
                SQLiteConnection.CreateFile(dbName);
                logger.InfoFormat("db创建完成");
                using (SQLiteConnection conn = new SQLiteConnection(connectionString))
                {
                    conn.Open();
                    using (SQLiteCommand cmd = new SQLiteCommand(SQLCreateStr, conn))
                    {
                        if (cmd.ExecuteNonQuery() > 0)
                        {
                            logger.InfoFormat("表创建成功");
                            //Console.WriteLine("表创建成功");
                        }
                    }
                }
            }
            #endregion
        }//ctor

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        public void ExecuteSqlTran(IList<string> SQLStringList)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                SQLiteTransaction tran = conn.BeginTransaction();
                cmd.Transaction = tran;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tran.Commit();
                }
                catch (System.Data.SQLite.SQLiteException ex)
                {
                    logger.Error(ex);
                    tran.Rollback();
                    throw new Exception(ex.Message);
                }
            }
        }//trans

        /// <summary>
        /// execute sql.get the return
        /// </summary>
        /// <returns></returns>
        public IDictionary<string, string> ExecuteReader()
        {
            IDictionary<string, string> dic = new Dictionary<string, string>();
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string SQLStr = "select businessname,max(businessDate) from cmcc_businesses_info group by businessname";
                using (SQLiteCommand cmd = new SQLiteCommand(SQLStr, conn))
                {
                    SQLiteDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        string key = reader.GetString(0);
                        string value = reader.GetString(1);
                        if (dic.ContainsKey(key))
                        {
                            Console.WriteLine("已存在：" + key + "\t" + value);
                        }
                        else
                        {
                            dic.Add(key, value);
                        }
                    }
                }
            }
            return dic;
        }