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 { ; 11 public static DbProviderFactory Factory = DbProvi
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 {
;
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
, ParameterDirection? Direction , bool? SourceColumnNullMapping )
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 ListDbParameter> paramList = new ListDbParameter>();
53
54 for (int i = 0; i parameters.Length; i++)
55 {
DbParameterCollection)
DbParameterCollection) paramList.Add(CreateParameter(item));
DbParameter)
DbParameter);
));
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 ListDbParameter> paramList = new ListDbParameter>();
79
80 foreach (var item in model.GetType().GetProperties())
81 {
,", item.Name);
83 values.AppendFormat("@{0},", item.Name);
84 paramList.Add(CreateParameter("@" + item.Name, item.GetValue(model, null)));
85 }
86
({), )), 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 ListDbParameter> paramList = new ListDbParameter>();
94
95 foreach (var item in model.GetType().GetProperties())
96 {
@{0},", item.Name);
98 paramList.Add(CreateParameter("@" + item.Name, item.GetValue(model, null)));
99 }
100
101 paramList.AddRange(ConvertParameters(parameters));
102
{) ;
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 ExecuteCommonT>(FuncDbCommand, 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
, T exValue = default(T))
150 {
151 if (IsException)
152 return ExecuteCommonT>(function);
153
154 try
155 {
156 return ExecuteCommonT>(function);
157 }
158 catch (Exception e)
159 {
160 Console.WriteLine(e.ToString());
161 return exValue;
162 }
163 }
164
165 public void Execute(ActionDbCommand> action)
166 {
167 Execute(cmd => { action(cmd); return 0; });
168 }
169
170 #endregion
171
172 #region 执行查询
173
()
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 ScalarT>()
185 {
186 return Execute(cmd => (T)cmd.ExecuteScalar());
187 }
188
189 public Query Top(int count)
190 {
({1}) as t0", count, SqlQuery), Parameters);
192 }
193
194 public Single ToSingle()
195 {
{
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);
dr;
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 {
{
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 ListT> ToListT>()
236 {
{
239 ListT> list = new ListT>();
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)
, null);
256 }
257
258 list.Add(s);
259 }
260 }
261
262 return list;
263 }, new ListT>());
264 }
265
266 public override string ToString()
267 {
268 return Scalarstring>();
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 {
({? " {2} t1.{3} from ({0}) as t1)" : ""),
284 SqlQuery, pageSize, pageIndex * pageSize, primaryKey), Parameters);
285 }
286
recordCount)
288 {
());
290 return PagerResultQuery(primaryKey, pageIndex, pageSize).ToDataTable();
291 }
292
recordCount)
294 {
295 return ToPager("Id", pageIndex, pageSize, recordCount);
296 }
297
recordCount)
299 {
());
301 return PagerResultQuery(primaryKey, pageIndex, pageSize).ToListT>();
302 }
303
recordCount)
305 {
306 return ToPagerT>("Id", pageIndex, pageSize, recordCount);
307 }
308
309 #endregion
310 }
311
{
{
; }
317 set { Add(name.ToLower(), value); }
318 }
319 }
，网站空间，美国服务器，虚拟主机
