首页 > > 网络编程 > 其它 >

Asp.net简单三层+Sqllite 增删改查

2018-06-17 21:48:37来源：未知阅读 ()

新建项目à新建一个空白解决方案
在Model新建一个实体类
1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Text;
5. using System.Threading.Tasks;
8. namespace factory.Model
9. {
10. public class factorys
11. {
12. //ID INTEGER PRIMARY KEY AUTOINCREMENT
13. // NOT NULL
14. // DEFAULT 1,
15. //correspondent NVARCHAR( 100 ) COLLATE NOCASE,
16. //contactaddress NVARCHAR( 100 ) COLLATE NOCASE,
17. //contacts NVARCHAR( 50 ) COLLATE NOCASE,
18. //contactway NVARCHAR( 50 ) COLLATE NOCASE,
19. //contactposition NVARCHAR( 50 ),
20. //dutydepartment NVARCHAR( 50 ),
21. //dutyofficer NVARCHAR( 50 ) COLLATE NOCASE,
22. //note NVARCHAR( 2000 ) COLLATE NOCASE
23. private int _ID;
25. public int ID
26. {
27. get { return _ID; }
28. set { _ID = value; }
29. }
31. /// <summary>
32. /// 客户单位
33. /// </summary>
34. private string _correspondent;
36. public string Correspondent
37. {
38. get { return _correspondent; }
39. set { _correspondent = value; }
40. }
42. /// <summary>
43. /// 联系地址
44. /// </summary>
45. private string _contactaddress;
47. public string Contactaddress
48. {
49. get { return _contactaddress; }
50. set { _contactaddress = value; }
51. }
53. /// <summary>
54. /// 联系人
55. /// </summary>
56. private string _contacts;
58. public string Contacts
59. {
60. get { return _contacts; }
61. set { _contacts = value; }
62. }
64. /// <summary>
65. /// 联系方式
66. /// </summary>
67. private string _contactway;
69. public string Contactway
70. {
71. get { return _contactway; }
72. set { _contactway = value; }
73. }
75. /// <summary>
76. /// 联系人职务
77. /// </summary>
78. private string _contactposition;
80. public string Contactposition
81. {
82. get { return _contactposition; }
83. set { _contactposition = value; }
84. }
86. /// <summary>
87. /// 负责部门
88. /// </summary>
89. private string _dutydepartment;
91. public string Dutydepartment
92. {
93. get { return _dutydepartment; }
94. set { _dutydepartment = value; }
95. }
97. /// <summary>
98. /// 负责人
99. /// </summary>
100. private string _dutyofficer;
102. public string Dutyofficer
103. {
104. get { return _dutyofficer; }
105. set { _dutyofficer = value; }
106. }
108. /// <summary>
109. /// 备注
110. /// </summary>
111. private string _note;
113. public string Note
114. {
115. get { return _note; }
116. set { _note = value; }
117. }
120. }
121. }
右击解决方案名称à新建一个DAL与BLL ,Model类库,因为个人习惯建好每个类库时喜欢à右击类库属性à默认命名空间将factoryModel改为àfactory.Model所以应用命名空间时为
1. using factory.Model;
如果不更改则为
1. using factoryModel;
新建一个Windows窗体应用程序(UI层)
添加引用 : 因为用的是SQLite数据库所以要手动添加一个SQLite的dll引用文件,在解决方案下新建一个lib文件夹将SQLite的dll文件添加进去 DAL引用Model,与新建的lib文件下的SQLite.dll 及 BLL引用DAL,Model与Model UI引用DAL与Model
DAL下的ado.net SqlliteHelper.cs
1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Text;
5. using System.Threading.Tasks;
6. using System.Configuration;
7. using System.Data.SQLite;
8. using System.Data;
9. namespace factory.DAL
10. {
11. public class SqlliteHelper
12. {
13. //连接字符串
14. private static readonly string str = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
16. //方法
17. /// <summary>
18. /// 增删改都可以
19. /// </summary>
20. /// <param name="sql">sql语句</param>
21. /// <param name="ps">sql语句中的参数</param>
22. /// <returns>返回受影响的行数</returns>
23. public static int ExecuteNonQuery(string sql, params SQLiteParameter[] ps)
24. {
25. try
26. {
27. using (SQLiteConnection con = new SQLiteConnection(str))
28. {
29. using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
30. {
31. if (ps != null)
32. {
33. cmd.Parameters.AddRange(ps);
34. }
35. con.Open();
36. return cmd.ExecuteNonQuery();
37. }
38. }
39. }
40. catch (Exception ex)
41. {
42. throw ex;
43. }
45. }
46. /// <summary>
47. /// 查询首行首列
48. /// </summary>
49. /// <param name="sql">sql语句</param>
50. /// <param name="ps">参数</param>
51. /// <returns>首行首列object</returns>
52. public static object ExecuteScalar(string sql, params SQLiteParameter[] ps)
53. {
54. try
55. {
56. using (SQLiteConnection con = new SQLiteConnection(str))
57. {
58. using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
59. {
60. con.Open();
61. if (ps != null)
62. {
63. cmd.Parameters.AddRange(ps);
64. }
65. return cmd.ExecuteScalar();
66. }
67. }
68. }
69. catch (Exception ex)
70. {
71. throw ex;
72. }
74. }
76. /// <summary>
77. /// 查询的
78. /// </summary>
79. /// <param name="sql"></param>
80. /// <param name="ps"></param>
81. /// <returns></returns>
82. public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] ps)
83. {
84. SQLiteConnection con = new SQLiteConnection(str);
85. try
86. {
87. using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
88. {
89. if (ps != null)
90. {
91. cmd.Parameters.AddRange(ps);
92. }
93. try
94. {
95. con.Open();
96. return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
97. }
98. catch (Exception ex)
99. {
100. con.Close();
101. con.Dispose();
102. throw ex;
103. }
104. }
105. }
106. catch (Exception ex)
107. {
109. throw ex;
110. }
112. }
114. /// <summary>
115. /// 查询的是一个表
116. /// </summary>
117. /// <param name="sql">sql语句</param>
118. /// <param name="ps">sql语句中的参数</param>
119. /// <returns>一个表</returns>
120. public static DataTable ExecuteTable(string sql, params SQLiteParameter[] ps)
121. {
122. try
123. {
124. DataTable dt = new DataTable();
125. using (SQLiteDataAdapter sda = new SQLiteDataAdapter(sql, str))
126. {
127. if (ps != null)
128. {
129. sda.SelectCommand.Parameters.AddRange(ps);
131. }
132. sda.Fill(dt);
134. }
135. return dt;
136. }
137. catch (Exception ex)
138. {
140. throw ex;
141. }
143. }
145. }
146. }
App.config配置文件
1. <?xml version="1.0" encoding="utf-8" ?>
2. <configuration>
3. <startup useLegacyV2RuntimeActivationPolicy="true">
5. <supportedRuntime version="v4.0"/>
7. </startup>
9. <connectionStrings>
11. <add connectionString="Data Source=factory.db;Version=3;" name="conStr"/>
12. </connectionStrings>
13. </configuration>
SQLite数据库文件就放在UI的binàdebug目录下
最后说下参数是
完整图片
这是本人第一次写博客,主要还是以记录为主,如有其它不当之处还望指点
源码下载
SQLite第三方编辑工具(sqlitestudio)