1 using System;
2 using System.Configuration;
3 using System.Collections.Generic;
4 using System.Data;
5 using System.Data.Common;
6 using System.Text;
7
8 public static class Db
9 {
10 private static ConnectionStringSettings ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"];
11 public static DbProviderFactory Factory = DbProviderFactories.GetFactory(ConnectionString.ProviderName);
12
13 public static DbConnection CreateConnection()
14 {
15 DbConnection con = Factory.CreateConnection();
16 con.ConnectionString = ConnectionString.ConnectionString;
17 return con;
18 }
19
20 #region 参数
21
22 public static DbParameter CreateParameter(DbParameter param)
23 {
24 return CreateParameter(param.ParameterName, param.Value, param.DbType, param.Size, param.Direction, param.SourceColumn, param.SourceColumnNullMapping, param.SourceVersion);
25 }
26
27 public static DbParameter CreateParameter(string ParameterName, object Value, DbType? DbType = null, int? Size = null, ParameterDirection? Direction = null, string SourceColumn = null, bool? SourceColumnNullMapping = null, DataRowVersion? SourceVersion = null)
28 {
29 DbParameter param = Factory.CreateParameter();
30
31 param.ParameterName = ParameterName;
32 param.Value = Value;
33
34 if (DbType != null)
35 param.DbType = DbType.Value;
36 if (Size != null)
37 param.Size = Size.Value;
38 if (Direction != null)
39 param.Direction = Direction.Value;
40 if (SourceColumn != null)
41 param.SourceColumn = SourceColumn;
42 if (SourceColumnNullMapping != null)
43 param.SourceColumnNullMapping = SourceColumnNullMapping.Value;
44 if (SourceVersion != null)
45 param.SourceVersion = SourceVersion.Value;
46
47 return param;
48 }
49
50 private static DbParameter[] ConvertParameters(object[] parameters)
51 {
52 List<DbParameter> paramList = new List<DbParameter>();
53
54 for (int i = 0; i < parameters.Length; i++)
55 {
56 if (parameters[i] is DbParameterCollection)
57 foreach (DbParameter item in parameters[i] as DbParameterCollection) paramList.Add(CreateParameter(item));
58 else if (parameters[i] is DbParameter)
59 paramList.Add(parameters[i] as DbParameter);
60 else
61 paramList.Add(CreateParameter("@" + i, parameters[i]));
62 }
63
64 return paramList.ToArray();
65 }
66
67 #endregion
68
69 public static Query Query(string query, params object[] parameters)
70 {
71 return new Query(query, ConvertParameters(parameters));
72 }
73
74 public static bool Insert(string table, object model)
75 {
76 StringBuilder fields = new StringBuilder();
77 StringBuilder values = new StringBuilder();
78 List<DbParameter> paramList = new List<DbParameter>();
79
80 foreach (var item in model.GetType().GetProperties())
81 {
82 fields.AppendFormat("[{0}],", item.Name);
83 values.AppendFormat("@{0},", item.Name);
84 paramList.Add(CreateParameter("@" + item.Name, item.GetValue(model, null)));
85 }
86
87 return Db.Query(string.Format("insert into [{0}]({1}) values({2})", table, fields.ToString().TrimEnd(','), values.ToString().TrimEnd(',')), paramList.ToArray()).Execute() > 0;
88 }
89
90 public static bool Update(string table, object model, string where, params object[] parameters)
91 {
92 StringBuilder fieldsAndValues = new StringBuilder();
93 List<DbParameter> paramList = new List<DbParameter>();
94
95 foreach (var item in model.GetType().GetProperties())
96 {
97 fieldsAndValues.AppendFormat("[{0}]=@{0},", item.Name);
98 paramList.Add(CreateParameter("@" + item.Name, item.GetValue(model, null)));
99 }
100
101 paramList.AddRange(ConvertParameters(parameters));
102
103 return Db.Query(string.Format("update [{0}] set {1}", table, fieldsAndValues.ToString().TrimEnd(',') + ((where ?? "").Trim() == "" ? "" : " where " + where)), paramList.ToArray()).Execute() > 0;
104 }
105 }
106
107 public class Query
108 {
109 #region 构造方法
110
111 public Query(string query, DbParameter[] parameters)
112 {
113 SqlQuery = query;
114 Parameters = parameters;
115 }
116
117 public Query(string query, DbParameter[] parameters, bool isException)
118 : this(query, parameters)
119 {
120 IsException = isException;
121 }
122
123 #endregion
124
125 #region 属性/字段
126
127 private bool IsException { get; set; }
128 public string SqlQuery { get; set; }
129 public DbParameter[] Parameters { get; set; }
130
131 #endregion
132
133 #region 执行基础
134
135 private T ExecuteCommon<T>(Func<DbCommand, T> function)
136 {
137 using (DbConnection con = Db.CreateConnection())
138 using (DbCommand cmd = con.CreateCommand())
139 {
140 cmd.CommandText = SqlQuery;
141 cmd.Parameters.AddRange(Parameters);
142 con.Open();
143 T result = function(cmd);
144 cmd.Parameters.Clear();
145 return result;
146 }
147 }
148
149 public T Execute<T>(Func<DbCommand, T> function, T exValue = default(T))
150 {
151 if (IsException)
152 return ExecuteCommon<T>(function);
153
154 try
155 {
156 return ExecuteCommon<T>(function);
157 }
158 catch (Exception e)
159 {
160 Console.WriteLine(e.ToString());
161 return exValue;
162 }
163 }
164
165 public void Execute(Action<DbCommand> action)
166 {
167 Execute(cmd => { action(cmd); return 0; });
168 }
169
170 #endregion
171
172 #region 执行查询
173
174 public int Execute()
175 {
176 return Execute(cmd => cmd.ExecuteNonQuery());
177 }
178
179 public object Scalar()
180 {
181 return Execute(cmd => cmd.ExecuteScalar());
182 }
183
184 public T Scalar<T>()
185 {
186 return Execute(cmd => (T)cmd.ExecuteScalar());
187 }
188
189 public Query Top(int count)
190 {
191 return Db.Query(string.Format("select top {0} * from ({1}) as t0", count, SqlQuery), Parameters);
192 }
193
194 public Single ToSingle()
195 {
196 return Execute(cmd =>
197 {
198 Single s = new Single();
199
200 using (var dr = cmd.ExecuteReader())
201 {
202 if (dr.Read())
203 {
204 string name = string.Empty;
205
206 for (int i = 0; i < dr.FieldCount; i++)
207 {
208 name = dr.GetName(i);
209 s[name] = dr[name];
210 }
211 }
212 else
213 {
214 throw new Exception("Not Find !!");
215 }
216 }
217
218 return s;
219 });
220
221 }
222
223 public DataTable ToDataTable()
224 {
225 return Execute(cmd =>
226 {
227 DbDataAdapter da = Db.Factory.CreateDataAdapter();
228 da.SelectCommand = cmd;
229 DataTable dt = new DataTable();
230 da.Fill(dt);
231 return dt;
232 });
233 }
234
235 public List<T> ToList<T>()
236 {
237 return Execute(cmd =>
238 {
239 List<T> list = new List<T>();
240
241 using (var dr = cmd.ExecuteReader())
242 {
243 while (dr.Read())
244 {
245 Type t = typeof(T);
246 T s = default(T);
247 string name = string.Empty;
248
249 for (int i = 0; i < dr.FieldCount; i++)
250 {
251 name = dr.GetName(i);
252 var pro = t.GetProperty(name);
253
254 if (pro != null)
255 pro.SetValue(s, dr[name], null);
256 }
257
258 list.Add(s);
259 }
260 }
261
262 return list;
263 }, new List<T>());
264 }
265
266 public override string ToString()
267 {
268 return Scalar<string>();
269 }
270
271 #endregion
272
273 #region 分页
274
275 private Query RecordCountQuery
276 {
277 get { return Db.Query(string.Format("select count(*) from ({0}) as t0", SqlQuery), Parameters); }
278 }
279
280 private Query PagerResultQuery(string primaryKey, int pageIndex, int pageSize)
281 {
282 return Db.Query(string.Format("select top {1} * from ({0}) as t0" +
283 (pageIndex > 1 ? " where t0.{3} not in (select top {2} t1.{3} from ({0}) as t1)" : ""),
284 SqlQuery, pageSize, pageIndex * pageSize, primaryKey), Parameters);
285 }
286
287 public DataTable ToPager(string primaryKey, int pageIndex, int pageSize, Action<int> recordCount)
288 {
289 recordCount(RecordCountQuery.Scalar<int>());
290 return PagerResultQuery(primaryKey, pageIndex, pageSize).ToDataTable();
291 }
292
293 public DataTable ToPager(int pageIndex, int pageSize, Action<int> recordCount)
294 {
295 return ToPager("Id", pageIndex, pageSize, recordCount);
296 }
297
298 public List<T> ToPager<T>(string primaryKey, int pageIndex, int pageSize, Action<int> recordCount)
299 {
300 recordCount(RecordCountQuery.Scalar<int>());
301 return PagerResultQuery(primaryKey, pageIndex, pageSize).ToList<T>();
302 }
303
304 public List<T> ToPager<T>(int pageIndex, int pageSize, Action<int> recordCount)
305 {
306 return ToPager<T>("Id", pageIndex, pageSize, recordCount);
307 }
308
309 #endregion
310 }
311
312 public class Single : Dictionary<string, object>
313 {
314 public new object this[string name]
315 {
316 get { return base[name.ToLower()]; }
317 set { Add(name.ToLower(), value); }
318 }
319 }
