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         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 } ，网站空间，美国服务器，虚拟主机