青鸟 王云鹏老师写的SqlHelper 泛型方法，反射，支持实体类_.NET_编程开发_程序员俱乐部

monospace; margin: 0em; width: 100%; background-color: #fbfbfb">  1: using System;
  2: using System.Collections.Generic;
  3: using System.Linq;
  4: using System.Text;
  5: using System.Data;
  6: using System.Data.SqlClient;
  7: using System.Reflection;
  8: 
  9: namespace DAL
 10: {
 11:     public class SqlHelper
 12:     {
 13:         private const string CONNECTION_STRING = @"server=.\sql2008;uid=sa;pwd=;database=BBSDB";
 14: 
 15:         private static T ExecuteReader<T>(SqlDataReader dr)
 16:         {
 17:             T obj = default(T);
 18:             obj = Activator.CreateInstance<T>();//T obj = new T();//instance
 19:             Type type = typeof(T);//get T class type by T's Name
 20:             PropertyInfo[] propertyInfos = type.GetProperties();//get current Type's all properties
 21:             int fieldCount = dr.FieldCount;//get column count
 22:             for (int i = 0; i < fieldCount; i++)
 23:             {
 24:                 string fieldName = dr.GetName(i);//get column 
 25:                 foreach (PropertyInfo propertyInfo in propertyInfos)
 26:                 {//per property infoname
 27:                     string properyName = propertyInfo.Name;//get property name
 28:                     if (string.Compare(fieldName, properyName, true) == 0)
 29:                     {//column's name == propery's name
 30:                         object value = dr.GetValue(i);//get column's value
 31:                         if (value != null && value != DBNull.Value)
 32:                         {
 33:                             propertyInfo.SetValue(obj, value, null);//set property's value
 34:                         }
 35:                         break;
 36:                     }
 37:                 }
 38:             }
 39:             return obj;
 40:         }
 41: 
 42:         public static List<T> ExecuteList<T>(string cmdText, CommandType commandType, params SqlParameter[] args)
 43:         {
 44:             List<T> list = new List<T>();
 45:             using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
 46:             {
 47:                 using (SqlCommand cmd = new SqlCommand(cmdText, con))
 48:                 {
 49:                     cmd.CommandType = commandType;
 50:                     cmd.Parameters.AddRange(args);
 51:                     con.Open();
 52:                     using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
 53:                     {
 54:                         while (dr.Read())
 55:                         {
 56:                             //dr.GetInt32(0);
 57:                             //dr.GetString(1);
 58:                             T obj = ExecuteReader<T>(dr);
 59:                             list.Add(obj);
 60:                         }
 61:                     }
 62:                 }
 63:             }
 64:             return list;
 65:         }
 66: 
 67:         public static T ExecuteEntity<T>(string cmdText, CommandType commandType, params SqlParameter[] args)
 68:         {
 69:             T obj = default(T);
 70:             using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
 71:             {
 72:                 using (SqlCommand cmd = new SqlCommand(cmdText, con))
 73:                 {
 74:                     cmd.CommandType = commandType;
 75:                     cmd.Parameters.AddRange(args);
 76:                     con.Open();
 77:                     using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
 78:                     {
 79:                         while (dr.Read())
 80:                         {
 81:                             obj = ExecuteReader<T>(dr);
 82:                             break;
 83:                         }
 84:                     }
 85:                 }
 86:             }
 87:             return obj;
 88:         }
 89: 
 90:         public static int ExecuteNonQuery(string cmdText, CommandType commandType, params SqlParameter[] args)
 91:         {
 92:             int result = -1;
 93:             using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
 94:             {
 95:                 using (SqlCommand cmd = new SqlCommand(cmdText, con))
 96:                 {
 97:                     cmd.Parameters.AddRange(args);
 98:                     cmd.CommandType = CommandType.StoredProcedure;
 99:                     con.Open();
100:                     result = cmd.ExecuteNonQuery();
101:                 }
102:             }
103:             return result;
104:         }
105: 
106:     }
107: }
108: