基于SQLite 的配置库的实现

vs2010 本身 有appconfig 配置方面的类，闲来无事，正好最近正在看System.Data.SQLite.dll，所以就有了本篇文章。

首先要用到 sqlite3.dll 这个动态库文件可以到网上去下载，百度一搜会有很多所以，这里就不提供现在地址了。

下面就将实现的例程贴出来供大家指正：

1、创建一个新的项目，选择Windows 窗体应用程序，如下图所示：

2、接下来在项目中引用下载的System.Data.SQLite.dll

3、在项目中添加一个类，名称为：SqliteHelper 。本类是在网上搜集到的，忘记了出处，仅在此处贴出源码，如下：

class SqliteHelper
    {
        private string connStr = "";
        public SqliteHelper(string dbName, bool isFullPath)
        {
            if (isFullPath)
            {
                connStr = @"Data Source=" + dbName + ";Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10";
            }
            else
            {
                connStr = @"Data Source=" + System.Environment.CurrentDirectory + "\\" + dbName + ";Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10";
            }

        }

        /// <summary>
        /// 功能: 创建数据库，带路径
        /// [2011-10-24 14:16 Bee]<para />
        /// </summary>
        /// <param name="dbName"></param>
        public void CreateDB(string dbName, bool isFullPath)
        {
            if (isFullPath)
            {
                if (!File.Exists(dbName))
                {
                    SQLiteConnection.CreateFile(dbName);
                }
            }
            else
            {
                if (!File.Exists(System.Environment.CurrentDirectory + "\\" + dbName))
                {
                    SQLiteConnection.CreateFile(System.Environment.CurrentDirectory + "\\" + dbName);
                }
            }
        }

        /// <summary>
        /// 功能: 执行sql，不返回
        /// [2011-10-24 14:15 Bee]<para />
        /// </summary>
        /// <param name="sqlStr">要执行的sql</param>
        public void ExecuteSql(string sqlStr)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();
                DbCommand comm = conn.CreateCommand();
                comm.CommandText = sqlStr;
                comm.CommandType = CommandType.Text;
                comm.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 功能: 执行sql语句数组
        /// [2011-10-24 14:54 Bee]<para />
        /// </summary>
        /// <param name="sqlStr"></param>
        public void ExecuteSqlList(string[] sqlStr)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();
                SQLiteCommand comm = conn.CreateCommand();
                foreach (string item in sqlStr)
                {
                    comm.CommandText = item;
                    comm.CommandType = CommandType.Text;
                    comm.ExecuteNonQuery();
                }

            }
        }

        /// <summary>
        /// 功能: 执行sql返回deteset
        /// [2011-10-24 14:15 Bee]<para />
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public DataSet ExecDataSet(string sqlStr)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();
                SQLiteCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlStr;
                cmd.CommandType = CommandType.Text;

                DbDataAdapter da = new SQLiteDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);

                return ds;
            }
        }
        public object ExecDataAdp(string sqlStr)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();
                SQLiteCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlStr;
                cmd.CommandType = CommandType.Text;
                object var = cmd.ExecuteScalar();

                return var;
            }
        }
        /// <summary>
        /// 功能：根据提供的表，返回表的所有列的结果集
        /// </summary>
        /// <param name="tbname"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string tbname)
        {
            DataSet ds = new DataSet();
            string Strsql = "select * from " + tbname;
            ds = ExecDataSet(Strsql);
            return ds;
        }
        /// <summary>
        /// 功能: 判断表是否存在
        /// [2011-10-24 14:14 Bee]<para />
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns>存在不存在</returns>
        public bool IsTableExist(string tableName)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connStr))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {

                    command.CommandText = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='" + tableName + "'";
                    int iaaa = Convert.ToInt32(command.ExecuteScalar());
                    if (Convert.ToInt32(command.ExecuteScalar()) == 0)
                    {
                        return false;
                    }
                    else
                    {
                        return true;
                    }
                }
            }
        }
    }

4、在项目中再次添加一个类，此类是本篇文章的目的，实现 vs2010 配置类的替换，首先贴出源码，源码部分有注视：

class AppConfig
    {
        private string dbconfig = string.Empty;
        public AppConfig()
        {
            dbconfig = "AppConfig.db3";
        }
        /// <summary>
        /// 创建配置数据库
        /// </summary>
        public void createDBAppConfig()
        {
            SqliteHelper shl = new SqliteHelper(this.dbconfig,false);
            shl.CreateDB(this.dbconfig, false);
        }
        /// <summary>
        /// 创建配置表
        /// </summary>
        public void createTBAppConfig()
        {
            SqliteHelper shl = new SqliteHelper(this.dbconfig, false);
            if (!shl.IsTableExist("AppConfig"))
            {
                shl.ExecuteSql("create table AppConfig(key varchar(30),value varchar(100),PRIMARY KEY (KEY));");
            }
        }
        /// <summary>
        /// 加载配置数据，如果不存在，先添加再加载
        /// </summary>
        /// <param name="strKey">关键字</param>
        /// <returns>返回关键字的键值</returns>
        public string LoadKey(string strKey)
        {
            if (!IsExistsKey(strKey))
            {
                SaveKey(strKey,"");
            }
            string strValue="";
            SqliteHelper shl = new SqliteHelper(this.dbconfig, false);
            strValue = shl.ExecDataAdp("select value from AppConfig where key = '" + strKey + "';").ToString();
            return strValue;
        }
        /// <summary>
        /// 判断关键字是否存在
        /// </summary>
        /// <param name="strKey">关键字</param>
        /// <returns>返回一个布尔值</returns>
        public bool IsExistsKey(string strKey)
        {
            bool bReturn = false;
            int iRows = 0;
            SqliteHelper shl = new SqliteHelper(this.dbconfig, false);
            iRows = Convert.ToInt32(shl.ExecDataAdp("select count(1) from AppConfig where key = '" + strKey + "';"));
            if (iRows != 0) bReturn = true;
            return bReturn;
        }
        /// <summary>
        /// 更新配置数据
        /// </summary>
        /// <param name="strKey">关键字</param>
        /// <param name="strValue">配置值</param>
        public void SaveKey(string strKey, string strValue)
        {
            SqliteHelper shl = new SqliteHelper(this.dbconfig, false);
            shl.ExecuteSql("replace into AppConfig values('" + strKey + "','" + strValue + "');");
        }
        /// <summary>
        /// 删除关键字
        /// </summary>
        /// <param name="strKey"></param>
        public void DeleteKey(string strKey)
        {
            if (IsExistsKey(strKey))
            {
                SqliteHelper shl = new SqliteHelper(this.dbconfig, false);
                shl.ExecuteSql("delete from AppConfig where key = '" + strKey + "';");
            }          
        }
    }

5、创建一个窗体，简单实现 此功能，下面贴出源码 和 窗口运行后的效果图

private AppConfig app = new AppConfig();//创建配置库
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //判断是否存在配置库及配置表，只有第一次起作用，若存在 将不执行
            app.createDBAppConfig();
            app.createTBAppConfig();

            tb_server.Text = app.LoadKey("server");
            tb_port.Text = app.LoadKey("port");
            tb_user.Text = app.LoadKey("user");
            tb_pwd.Text = app.LoadKey("pwd");
            tb_db.Text = app.LoadKey("dbname");
            //app.SaveKey("server", "127.0.0.1");
            //app.DeleteKey("a1111");
        }

        private void bSave_Click(object sender, EventArgs e)
        {
            app.SaveKey("server", tb_server.Text);
            app.SaveKey("port", tb_port.Text);
            app.SaveKey("user", tb_user.Text);
            app.SaveKey("pwd", tb_pwd.Text);
            app.SaveKey("dbname", tb_db.Text);
            //app.DeleteKey("a1111");
        }

        private void bExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }