基于SQLite 的配置库的实现

末蓝、 2022-08-06 10:10 178阅读 0赞

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

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

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

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

![Image 1][]

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

![Image 1][]

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();  
        \}

![Image 1][]

[Image 1]: