自己寫的訪問SqlServer數據庫的通用DAL層，sqlserverdal
如題,直接貼代碼.
首先是DataTable轉List<T>的方法,這個方法通用性極強.
![]()
1 #region Table轉List
2 /// <summary>
3 /// Table轉List
4 /// </summary>
5 /// <typeparam name="T"></typeparam>
6 /// <param name="dt"></param>
7 /// <returns></returns>
8 public static List<T> TableToList<T>(DataTable dt) where T : new()
9 {
10 // 定義集合
11 IList<T> list = new List<T>();
12
13 // 獲得此模型的類型
14 Type type = typeof(T);
15
16 foreach (DataRow dr in dt.Rows)
17 {
18 T t = new T();
19
20 // 獲得公共屬性
21 PropertyInfo[] propertys = t.GetType().GetProperties();
22
23 foreach (PropertyInfo pi in propertys)
24 {
25 // 判斷此屬性是否有Setter
26 if (!pi.CanWrite) continue;
27
28 object value = dr[pi.Name];
29 if (value != DBNull.Value)
30 pi.SetValue(t, value, null);
31 }
32
33 list.Add(t);
34 }
35 return list.ToList();
36 }
37 #endregion
View Code
下面貼出主要代碼
![]()
1 /// <summary>
2 /// 數據訪問基類:BaseService
3 /// </summary>
4 /// <typeparam name="T"></typeparam>
5 public class BaseService<T> : IBaseService<T> where T : class ,new()
6 {
7 /// <summary>
8 /// 得到某列最大值
9 /// </summary>
10 /// <param name="connection"></param>
11 /// <param name="fieldname"></param>
12 /// <returns></returns>
13 public virtual int GetMax(string connection,string fieldname)
14 {
15 #region 參數
16 T entity = new T();
17 SqlParameter[] parameters = new SqlParameter[]
18 {
19 new SqlParameter("@fieldname",fieldname)
20 };
21 #endregion
22
23 StringBuilder sqlStr=new StringBuilder();
24 sqlStr.Append("select max(");
25 sqlStr.Append("@fieldname");
26 sqlStr.Append(") from ");
27 sqlStr.Append(entity.GetType().Name);
28
29 return int.Parse(DatabaseAccess.SqlHelper.ExecuteDataSetText(connection, sqlStr.ToString(), parameters).Tables[0].Rows[0][0].ToString());
30 }
31
32 /// <summary>
33 /// 是否存在該記錄
34 /// </summary>
35 /// <param name="connection">連接字符串</param>
36 /// <param name="primarykey">主鍵值</param>
37 /// <returns></returns>
38 public virtual bool Exists(string connection, object primarykey)
39 {
40 #region 參數
41 T entity = new T();
42
43 string primaryKey = GetPrimarykey(connection, entity);
44 List<SqlParameter> parameters = new List<SqlParameter>();
45 parameters.Add(new SqlParameter("@" + primaryKey, primarykey));
46 #endregion
47
48 StringBuilder sqlStr = new StringBuilder();
49 sqlStr.Append("select count(1) from ");
50 sqlStr.Append(entity.GetType().Name);
51 sqlStr.Append(" where ");
52 sqlStr.Append(primaryKey + "=@" + primaryKey);
53
54 int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray());
55 if (res > 0)
56 return true;
57 else
58 return false;
59 }
60
61 /// <summary>
62 /// 增加一條數據
63 /// </summary>
64 public virtual bool Add(string connection, T entity)
65 {
66 #region 參數
67 List<SqlParameter> parameters = new List<SqlParameter>();
68 string fields = "";
69 string placeholders = "";
70 foreach (var item in entity.GetType().GetProperties())
71 {
72 fields += item.Name + ",";
73 placeholders += "@" + item.Name + ",";
74
75 SqlParameter parameter = new SqlParameter();
76 parameter.ParameterName = "@" + item.Name;
77 parameter.Value = item.GetValue(entity, null);
78 parameters.Add(parameter);
79 }
80 #endregion
81
82 StringBuilder sqlStr = new StringBuilder();
83 sqlStr.Append("insert " + entity.GetType().Name + " (");
84 sqlStr.Append(fields.Substring(0, fields.Length - 1) + ")");
85 sqlStr.Append(" values (");
86 sqlStr.Append(placeholders.Substring(0, placeholders.Length - 1) + ")");
87
88 int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray());
89 if (res > 0)
90 return true;
91 else
92 return false;
93 }
94
95 /// <summary>
96 /// 更新一條數據
97 /// </summary>
98 /// <param name="connection">連接字符串</param>
99 /// <param name="entity">類</param>
100 /// <returns></returns>
101 public virtual bool Update(string connection, T entity)
102 {
103 #region 參數
104 string primaryKey = GetPrimarykey(connection, entity);
105 List<SqlParameter> parameters = new List<SqlParameter>();
106 parameters.Add(new SqlParameter("@"+primaryKey, entity.GetType().GetProperty(primaryKey).GetValue(entity,null)));
107 string fields = "";
108 foreach (var item in entity.GetType().GetProperties())
109 {
110 if (item.Name != primaryKey)
111 {
112 fields += item.Name + "=@" + item.Name + ",";
113
114 SqlParameter parameter = new SqlParameter();
115 parameter.ParameterName = "@" + item.Name;
116 parameter.Value = item.GetValue(entity, null);
117 parameters.Add(parameter);
118 }
119 }
120 #endregion
121
122 StringBuilder sqlStr = new StringBuilder();
123 sqlStr.Append("update " + entity.GetType().Name);
124 sqlStr.Append(" set ");
125 sqlStr.Append(fields.Substring(0,fields.Length-1));
126 sqlStr.Append(" where ");
127 sqlStr.Append(primaryKey + "=@" + primaryKey);
128
129 int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray());
130 if (res > 0)
131 return true;
132 else
133 return false;
134 }
135
136 /// <summary>
137 /// 刪除一條數據
138 /// </summary>
139 /// <param name="connection">連接字符串</param>
140 /// <param name="primaryKey">主鍵值</param>
141 /// <returns></returns>
142 public virtual bool Delete(string connection, object primarykey)
143 {
144 #region 參數
145 T entity = new T();
146
147 string primaryKey = GetPrimarykey(connection, entity);
148 List<SqlParameter> parameters = new List<SqlParameter>();
149 parameters.Add(new SqlParameter("@" + primaryKey, primarykey));
150 #endregion
151
152 StringBuilder sqlStr = new StringBuilder();
153 sqlStr.Append("delete from ");
154 sqlStr.Append(entity.GetType().Name);
155 sqlStr.Append(" where ");
156 sqlStr.Append(primaryKey + "=@" + primaryKey);
157
158 int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray());
159 if (res > 0)
160 return true;
161 else
162 return false;
163 }
164
165 /// <summary>
166 /// 刪除多條數據
167 /// </summary>
168 /// <param name="connection">連接字符串</param>
169 /// <param name="base_idlist">主鍵值列表</param>
170 /// <returns></returns>
171 public virtual bool DeleteList(string connection, List<object> primarykeys)
172 {
173 #region 參數
174 T entity = new T();
175
176 string primaryKey = GetPrimarykey(connection, entity);
177
178 string primaryKeys = "";
179 foreach(var item in primarykeys)
180 {
181 primaryKeys += item.ToString();
182 }
183
184 List<SqlParameter> parameters = new List<SqlParameter>();
185 parameters.Add(new SqlParameter("@" + primaryKey, primaryKeys));
186 #endregion
187
188 StringBuilder sqlStr = new StringBuilder();
189 sqlStr.Append("delete from ");
190 sqlStr.Append(entity.GetType().Name);
191 sqlStr.Append(" where ");
192 sqlStr.Append(primaryKey + " in(@" + primaryKey + ")");
193
194 int res = DatabaseAccess.SqlHelper.ExecteNonQueryText(connection, sqlStr.ToString(), parameters.ToArray());
195 if (res > 0)
196 return true;
197 else
198 return false;
199 }
200
201 /// <summary>
202 /// 得到一個對象實體
203 /// </summary>
204 /// <param name="connection">連接字符串</param>
205 /// <param name="primarykey">主鍵值</param>
206 /// <returns></returns>
207 public virtual T GetModel(string connection, object primarykey)
208 {
209 #region 參數
210 T entity = new T();
211
212 string primaryKey = GetPrimarykey(connection, entity);
213
214 List<SqlParameter> parameters = new List<SqlParameter>();
215 parameters.Add(new SqlParameter("@table", entity.GetType().Name));
216 parameters.Add(new SqlParameter("@" + primaryKey, primarykey));
217 #endregion
218
219 StringBuilder sqlStr = new StringBuilder();
220 sqlStr.Append("select * from ");
221 sqlStr.Append(entity.GetType().Name);
222 sqlStr.Append(" where ");
223 sqlStr.Append(primaryKey + "=@" + primaryKey);
224
225 return Common.ToList.TableToList<T>(DatabaseAccess.SqlHelper.ExecuteDataSetText(connection, sqlStr.ToString(), parameters.ToArray()).Tables[0]).FirstOrDefault();
226 }
227
228 /// <summary>
229 /// 獲得數據列表
230 /// </summary>
231 /// <param name="connection">連接字符串</param>
232 /// <param name="strWhere">查詢條件</param>
233 /// <returns></returns>
234 public virtual List<T> GetList(string connection, string strWhere)
235 {
236 #region 參數
237 T entity = new T();
238
239 List<SqlParameter> parameters = new List<SqlParameter>();
240 parameters.Add(new SqlParameter("@where", strWhere));
241 #endregion
242
243 StringBuilder sqlStr = new StringBuilder();
244 sqlStr.Append("select * from ");
245 sqlStr.Append(entity.GetType().Name);
246 if (strWhere.Trim().Length > 0)
247 sqlStr.Append(" where @where");
248
249 return Common.ToList.TableToList<T>(DatabaseAccess.SqlHelper.ExecuteDataSetText(connection, sqlStr.ToString(), parameters.ToArray()).Tables[0]);
250 }
251
252 /// <summary>
253 /// 獲得總數
254 /// </summary>
255 /// <param name="connection">連接字符串</param>
256 /// <param name="strWhere">查詢條件</param>
257 /// <returns></returns>
258 public virtual int GetRecordCount(string connection, string strWhere)
259 {
260 #region 參數
261 T entity = new T();
262
263 List<SqlParameter> parameters = new List<SqlParameter>();
264 parameters.Add(new SqlParameter("@where", strWhere));
265 #endregion
266
267 StringBuilder sqlStr = new StringBuilder();
268 sqlStr.Append("select count(1) FROM ");
269 sqlStr.Append(entity.GetType().Name);
270 if (strWhere.Trim().Length > 0)
271 sqlStr.Append(" where @where");
272
273 return int.Parse(DatabaseAccess.SqlHelper.ExecuteScalarText(connection, sqlStr.ToString(), parameters.ToArray()).ToString());
274 }
275
276 /// <summary>
277 /// 執行sql語句
278 /// </summary>
279 /// <param name="connection"></param>
280 /// <param name="cmdtype"></param>
281 /// <param name="sql"></param>
282 /// <param name="parameters"></param>
283 /// <returns></returns>
284 public virtual int ExecteNonQuerySQL(string connection, CommandType cmdtype, string sql, params SqlParameter[] parameters)
285 {
286 return DatabaseAccess.SqlHelper.ExecteNonQuery(connection, cmdtype, sql, parameters);
287 }
288
289 /// <summary>
290 /// 獲取類主鍵
291 /// </summary>
292 /// <param name="connection">連接字符串</param>
293 /// <param name="entity">類</param>
294 /// <returns></returns>
295 public virtual string GetPrimarykey(string connection, T entity)
296 {
297 StringBuilder sqlStr = new StringBuilder();
298 sqlStr.Append("EXEC sp_pkeys @table_name='" + entity.GetType().Name + "'");
299
300 SqlParameter[] parameters = new SqlParameter[]
301 {
302
303 };
304
305 return DatabaseAccess.SqlHelper.ExecuteDataSetText(connection,sqlStr.ToString(),parameters).Tables[0].Rows[0]["COLUMN_NAME"].ToString();
306 }
307
308 /// <summary>
309 /// 執行sql語句
310 /// </summary>
311 /// <param name="connection"></param>
312 /// <param name="cmdtype"></param>
313 /// <param name="sql"></param>
314 /// <param name="parameters"></param>
315 /// <returns></returns>
316 public virtual DataSet ExecuteDataSetSQL(string connection, CommandType cmdtype, string sql, params SqlParameter[] parameters)
317 {
318 return DatabaseAccess.SqlHelper.ExecuteDataSet(connection, cmdtype, sql, parameters);
319 }
320 }
View Code
這些方法中默認表都是有主鍵的,而且實體名稱和表名一致,否則執行會報錯.
這套方法已經被我用在一個舊項目的改造中,目前來看效果還是不錯的,因此在這與大家分享,因為本人水平有限,而且沒用太多時間去完善,這套代碼肯定會有很多瑕疵,希望高手不吝賜教.
