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
