DBhelper

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 List<DbParameter> paramList = new List<DbParameter>(); 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 List<DbParameter> paramList = new List<DbParameter>(); 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 List<DbParameter> paramList = new List<DbParameter>(); 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 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 , 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 () 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 { ({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 List<T> ToList<T>() 236 { { 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) , 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 { ({? " {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).ToList<T>(); 302 } 303 recordCount) 305 { 306 return ToPager<T>("Id", pageIndex, pageSize, recordCount); 307 } 308 309 #endregion 310 } 311 { { ; } 317 set { Add(name.ToLower(), value); } 318 } 319 } ，网站空间，美国服务器，虚拟主机