SQLite数据库_c/s架构的心得

1.使用是Navicat Premium软件， Microsoft Windows版本。

2.选择SQLite并新建数据库：

3.将建好的SQLite数据库，放到新建的项目的debug文件下中，

并在程序的文件夹也放一份：

注：当需要修改数据库是，修改bin文件夹下的数据库，数据变化也在bin文件夹下的数据库可即时查看

4.项目中写数据库连接代码，一般建一个PubConstant类：

 public class PubConstant
    {

        #region 连接字符串
        /// <summary>
        /// 连接数据库字符串
        /// </summary>
        /// <returns></returns>
        public static string ConnectionString
        {
            get
            {
                string _connectionString = ConfigurationManager.AppSettings["ConnectionString"];

                string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
                if (ConStringEncrypt == "true")
                {
                    _connectionString = DESEncrypt.Decrypt(_connectionString);
                }
                //获取绝对路径，
                string conn= string.Format(@"Data Source ={0}{1}; Pooling = true; FailIfMissing = false", Application.StartupPath, _connectionString);

                return conn;
            }
        }

        #endregion
        /// <summary>
        /// 得到web.config里配置项的数据库连接字符串。
        /// </summary>
        /// <param name="configName"></param>
        /// <returns></returns>
        public static string GetConnectionString(string configName)
        {
            string connectionString = ConfigurationManager.AppSettings[configName];
            string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
            if (ConStringEncrypt == "true")
            {
                connectionString = DESEncrypt.Decrypt(connectionString);
            }
            return connectionString;
        }


    }

在app.config文件中添加key:

然后在DbHelperSQLite写：

 public abstract class DbHelperSQLite
    {
        //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.        
        public static string connectionString = PubConstant.ConnectionString;

5.常用两个表的数据显示：

 select a.id,a.studentID '学员编号',a.name '学员姓名',a.sex '性别',a.phone '联系电话',
a.address '地址',a.semester '学期',a.tuitionPayable '应缴学费',a.deposit '定金',
ifnull((SELECT sum(b.money) FROM payment b WHERE b.payment_type = '学费' and a.studentID = b.student_id ),0) '已缴学费',
ifnull((SELECT sum(c.money*c.room_days) FROM payment c WHERE c.payment_type = '住宿费' and a.studentID = c.student_id ),0) '已缴住宿费',
ifnull((SELECT sum(d.money) FROM payment d WHERE d.payment_type = '餐费' and a.studentID = d.student_id ),0) '已缴餐费',
a.paymentStatus,a.studentType,a.sign '状态',a.remark '备注'
 from student_information a