1
using System;
2using System.Collections.Generic;
3
4using System.Data;
5using System.Data.OleDb;
6using MyShop.IDAL;
7using MyShop.Model;
8
9namespace MyShop.AccessDAL
10
...{
11
public class GuestBook : IGuestBook
12
...{
13 private ConfigInfo configInfo = new ConfigInfo();
14 private string tableName = "Ljh_GuestBook";
15
16 public GuestBook()
17 ...{
18 if (!string.IsNullOrEmpty(configInfo.TablePrefix.Trim()))
19 tableName = configInfo.TablePrefix + "GuestBook";
20
}
21 IGuestBook member#region IGuestBook member
22
23
24 /**//// <summary>
25 /// add
26 /// </summary>
27 /// <param name="model"></param>
28 /// <returns>返回ID, 如果发生错误则返回-1</returns>
29 public int Add(GuestBookInfo model)
30 ...{
31 if (model == null)
32 ...{
33 return -1;
34 }
35 string commandText = "insert into " + this.tableName + "(GuestName,content,QQ,Email,HomePage,LeaveTime,Reply,Gender,IsPassed,Face) values(@GuestName,@content,@QQ,@Email,@HomePage,@LeaveTime,@Reply,@Gender,@IsPassed,@Face) ";
36 OleDbParameter[] commandParameters =...{
37 Database.MakeInParam("@GuestName", OleDbType.VarWChar , 20 ,model.GuestName ),
38 Database.MakeInParam("@content", OleDbType.VarWChar , 0 ,model.content ),
39 Database.MakeInParam("@QQ", OleDbType.VarWChar , 50 ,model.QQ ),
40 Database.MakeInParam("@Email", OleDbType.VarWChar , 50 ,model.Email ),
41 Database.MakeInParam("@HomePage", OleDbType.VarWChar , 50 ,model.HomePage ),
42 Database.MakeInParam("@LeaveTime", OleDbType.Date , 8 ,model.LeaveTime ),
43 Database.MakeInParam("@Reply", OleDbType.VarWChar , 0 ,model.Reply ),
44 Database.MakeInParam("@Gender", OleDbType.UnsignedTinyInt , 1 ,model.Gender ),
45 Database.MakeInParam("@IsPassed", OleDbType.UnsignedTinyInt , 1 ,model.IsPassed ),
46 Database.MakeInParam("@Face", OleDbType.Integer , 4 ,model.Face)
47 };
48 int intIdentity = -1;
49 try
50 ...{
51 Database.ExecuteNonQuery(CommandType.Text, out intIdentity, commandText, commandParameters);
52 }
53 catch (Exception exception)
54 ...{
55 throw exception;
56 }
57 return intIdentity;
58 }
59
60 /**//// <summary>
61 /// 删除
62 /// </summary>
63 /// <param name="filter">where后面的条件语句，不加where</param>
64 /// <returns>返回影响行数</returns>
65 public int Delete(string filter)
66 ...{
67 int count = -1;
68 string sql = @"delete from " + tableName;
69 if (!string.IsNullOrEmpty(filter.Trim()))
70 ...{
71 sql = sql + " where " + filter;
72 }
73 try
74 ...{
75
76 count = Database.ExecuteNonQuery(sql);
77 }
78 catch (Exception ex)
79 ...{
80 throw ex;
81 }
82 return count;
83 }
84
85 /**//// <summary>
86 ///
87 /// </summary>
88 /// <param name="filter"></param>
89 /// <returns></returns>
90 public bool Exist(string filter)
91 ...{
92 bool result = false;
93 string sql = @"select * from " + tableName;
94 if (!string.IsNullOrEmpty(filter))
95 ...{
96 sql = sql + " where " + filter;
97 }
98 try
99 ...{
100
101
102 DataSet dataset = new DataSet();
103 dataset = Database.ExecuteDataSet(sql);
104 if (dataset.Tables[0].Rows.Count > 0)
105 ...{
106 result = true;
107 }
108 }
109 catch (Exception ex)
110 ...{
111 throw ex;
112 }
113 return result;
114 }
115
116 /**//// <summary>
117 /// 返回所有
118 /// </summary>
119 /// <returns>返回所有</returns>
120 public DataSet GetDataSet()
121 ...{
122 string sql = "select * from " + tableName;
123 DataSet dataset = new DataSet();
124 try
125 ...{
126
127 dataset = Database.ExecuteDataSet(sql);
128
129 }
130 catch (Exception ex)
131 ...{
132 throw ex;
133 }
134 return dataset;
135 }
136
137 public DataSet GetDataSet(string filter)
138 ...{
139 if (string.IsNullOrEmpty(filter))
140 return null;
141
142 string sql = "select * from " + tableName + " where " + filter;
143 DataSet dataset = new DataSet();
144 try
145 ...{
146
147 dataset = Database.ExecuteDataSet(sql);
148 }
149 catch (Exception ex)
150 ...{
151 throw ex;
152 }
153 return dataset;
154 }
155
156 /**//// <summary>
157 ///
158 /// </summary>
159 /// <param name="dr"></param>
160 /// <returns></returns>
161 public GuestBookInfo GetModel(DataRow dr)
162 ...{
163 if (dr == null)
164 return null;
165 GuestBookInfo model = new GuestBookInfo();
166
167 if (dr["GuestId"].ToString() != "") model.GuestId = Convert.ToInt32( dr["GuestId"]);
168 if (dr["GuestName"].ToString() != "") model.GuestName = dr["GuestName"].ToString();
169 if (dr["content"].ToString() != "") model.content = dr["content"].ToString();
170 if (dr["QQ"].ToString() != "") model.QQ = dr["QQ"].ToString();
171 if (dr["Email"].ToString() != "") model.Email = dr["Email"].ToString();
172 if (dr["HomePage"].ToString() != "") model.HomePage = dr["HomePage"].ToString();
173 if (dr["LeaveTime"].ToString() != "") model.LeaveTime = dr["LeaveTime"].ToString();
174 if (dr["Reply"].ToString() != "") model.Reply = dr["Reply"].ToString();
175 if (dr["Gender"].ToString() != "") model.Gender = Convert.ToInt32( dr["Gender"]);
176 if (dr["IsPassed"].ToString() != "") model.IsPassed =Convert.ToInt32( dr["IsPassed"]);
177 if (dr["Face"].ToString() != "") model.Face = Convert.ToInt32(dr["Face"]);
178 return model;
179 }
180
181 /**//// <summary>
182 ///
183 /// </summary>
184 /// <param name="sql"></param>
185 /// <returns></returns>
186 public DataSet Query(string sql)
187 ...{
188 if (string.IsNullOrEmpty(sql))
189 return null;
190
191 DataSet dataset = new DataSet();
192 try
193 ...{
194
195 dataset = Database.ExecuteDataSet(sql);
196 }
197 catch (Exception ex)
198 ...{
199 throw ex;
200 }
201 return dataset;
202 }
203
204
205 /**//// <summary>
206 /// update
207 /// </summary>
208 /// <param name="model"></param>
209 /// <param name="filter"></param>
210 /// <returns></returns>
211 public int Update(GuestBookInfo model, string filter)
212 ...{
213 int result;
214 if (string.IsNullOrEmpty(filter))
215 ...{
216 throw new Exception("The 'filter' can not be null!");
217 }
218 string sql = @"update " + tableName + " set GuestName=@GuestName,content=@content,QQ=@QQ,Email=@Email,HomePage=@HomePage,LeaveTime=@LeaveTime,Reply=@Reply,Gender=@Gender,IsPassed=@IsPassed,face=@Face " + " where " + filter;
219
220 OleDbParameter[] prams = ...{
221 Database.MakeInParam("@GuestName", OleDbType.VarWChar , 20 ,model.GuestName ),
222 Database.MakeInParam("@content", OleDbType.VarWChar , 0 ,model.content ),
223 Database.MakeInParam("@QQ", OleDbType.VarWChar , 50 ,model.QQ ),
224 Database.MakeInParam("@Email", OleDbType.VarWChar , 50 ,model.Email ),
225 Database.MakeInParam("@HomePage", OleDbType.VarWChar , 50 ,model.HomePage ),
226 Database.MakeInParam("@LeaveTime", OleDbType.Date , 8 ,model.LeaveTime ),
227 Database.MakeInParam("@Reply", OleDbType.VarWChar , 0 ,model.Reply ),
228 Database.MakeInParam("@Gender", OleDbType.UnsignedTinyInt , 1 ,model.Gender ),
229 Database.MakeInParam("@IsPassed", OleDbType.UnsignedTinyInt , 1 ,model.IsPassed ),
230 Database.MakeInParam("@Face", OleDbType.Integer , 4 ,model.Face)
231 };
232
233 try
234 ...{
235
236 result = Database.ExecuteNonQuery(sql, prams);
237 }
238 catch (Exception ex)
239 ...{
240 throw ex;
241 }
242 return result;
243 }
244
245 #endregion
246
247 /**//// <summary>
248 /// 快速搜索
249 /// </summary>
250 /// <param name="searchType"></param>
251 /// <returns></returns>
252 public DataSet QuickSearch(int searchType)
253 ...{
254 string str;
255 switch (searchType)
256 ...{
257 case 1:
258 str = " [IsPassed] = 0 order by GuestId Desc ";
259 break;
260
261 case 2:
262 str = " DateDiff('d',LeaveTime,now()) = 0 order by GuestId Desc ";
263 break;
264
265 case 3:
266 str = " DateDiff('ww',LeaveTime,now()) = 0 order by GuestId Desc ";
267 break;
268
269 case 4:
270 str = " DateDiff('m',LeaveTime,now()) = 0 order by GuestId Desc ";
271 break;
272
273 case 5:
274 str = " DateDiff('m',LeaveTime,now()) <= 3 order by GuestId Desc ";
275 break;
276
277
MyShop080617/AccessDAL/GuestBook.cs，打开代码结构图