SqlHelper 泛型方法，反射，支持实体类

001using System;

002using System.Collections.Generic;

003using System.Linq;

004using System.Text;

005using System.Data;

006using System.Data.SqlClient;

007using System.Reflection;

008 

009namespace DAL

010{

011    publicclass SqlHelper

012    {

013        privateconst string CONNECTION_STRING = @"server=.\sql2008;uid=sa;pwd=;database=BBSDB";

014 

015        privatestatic T ExecuteReader<T>(SqlDataReader dr)

016        {

017            T obj =default(T);

018            obj = Activator.CreateInstance<T>();//T obj = new T();//instance

019            Type type =typeof(T);//get T class type by T's Name

020            PropertyInfo[] propertyInfos = type.GetProperties();//get current Type's all properties

021            intfieldCount = dr.FieldCount;//get column count

022            for(int i = 0; i < fieldCount; i++)

023            {

024                stringfieldName = dr.GetName(i);//get column

025                foreach(PropertyInfo propertyInfo inpropertyInfos)

026                {//per property infoname

027                    stringproperyName = propertyInfo.Name;//get property name

028                    if(string.Compare(fieldName, properyName,true) == 0)

029                    {//column's name == propery's name

030                        objectvalue = dr.GetValue(i);//get column's value

031                        if(value != null && value != DBNull.Value)

032                        {

033                            propertyInfo.SetValue(obj, value,null);//set property's value

034                        }

035                        break;

036                    }

037                }

038            }

039            returnobj;

040        }

041 

042        publicstatic List<T> ExecuteList<T>(stringcmdText, CommandType commandType, paramsSqlParameter[] args)

043        {

044            List<T> list =new List<T>();

045            using(SqlConnection con = new SqlConnection(CONNECTION_STRING))

046            {

047                using(SqlCommand cmd = new SqlCommand(cmdText, con))

048                {

049                    cmd.CommandType = commandType;

050                    cmd.Parameters.AddRange(args);

051                    con.Open();

052                    using(SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))

053                    {

054                        while(dr.Read())

055                        {

056                            //dr.GetInt32(0);

057                            //dr.GetString(1);

058                            T obj = ExecuteReader<T>(dr);

059                            list.Add(obj);

060                        }

061                    }

062                }

063            }

064            returnlist;

065        }

066 

067        publicstatic T ExecuteEntity<T>(stringcmdText, CommandType commandType, paramsSqlParameter[] args)

068        {

069            T obj =default(T);

070            using(SqlConnection con = new SqlConnection(CONNECTION_STRING))

071            {

072                using(SqlCommand cmd = new SqlCommand(cmdText, con))

073                {

074                    cmd.CommandType = commandType;

075                    cmd.Parameters.AddRange(args);

076                    con.Open();

077                    using(SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))

078                    {

079                        while(dr.Read())

080                        {

081                            obj = ExecuteReader<T>(dr);

082                            break;

083                        }

084                    }

085                }

086            }

087            returnobj;

088        }

089 

090        publicstatic int ExecuteNonQuery(string cmdText, CommandType commandType,params SqlParameter[] args)

091        {

092            intresult = -1;

093            using(SqlConnection con = new SqlConnection(CONNECTION_STRING))

094            {

095                using(SqlCommand cmd = new SqlCommand(cmdText, con))

096                {

097                    cmd.Parameters.AddRange(args);

098                    cmd.CommandType = CommandType.StoredProcedure;

099                    con.Open();

100                    result = cmd.ExecuteNonQuery();

101                }

102            }

103            returnresult;

104        }

105 

106    }

107}