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

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

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

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

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

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

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

 1  public class PubConstant
 2     {
 3 
 4         #region 连接字符串
 5         /// <summary>
 6         /// 连接数据库字符串
 7         /// </summary>
 8         /// <returns></returns>
 9         public static string ConnectionString
10         {
11             get
12             {
13                 string _connectionString = ConfigurationManager.AppSettings["ConnectionString"];
14 
15                 string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
16                 if (ConStringEncrypt == "true")
17                 {
18                     _connectionString = DESEncrypt.Decrypt(_connectionString);
19                 }
20                 //获取绝对路径，
21                 string conn= string.Format(@"Data Source ={0}{1}; Pooling = true; FailIfMissing = false", Application.StartupPath, _connectionString);
22 
23                 return conn;
24             }
25         }
26 
27         #endregion
28         /// <summary>
29         /// 得到web.config里配置项的数据库连接字符串。
30         /// </summary>
31         /// <param name="configName"></param>
32         /// <returns></returns>
33         public static string GetConnectionString(string configName)
34         {
35             string connectionString = ConfigurationManager.AppSettings[configName];
36             string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
37             if (ConStringEncrypt == "true")
38             {
39                 connectionString = DESEncrypt.Decrypt(connectionString);
40             }
41             return connectionString;
42         }
43 
44 
45     }

在app.config文件中添加key:

然后在DbHelperSQLite写：

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

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

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