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

001	using System;

002	using System.Collections.Generic;

003	using System.Linq;

004	using System.Text;

005	using System.Data;

006	using System.Data.SqlClient;

007	using System.Reflection;

008

009	namespace DAL

010

{

011	public class SqlHelper

012

{

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

014

015	private static 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	int fieldCount = dr.FieldCount;//get column count

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

023

{

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

025	foreach (PropertyInfo propertyInfo in propertyInfos)

026	{//per property infoname

027	string properyName = propertyInfo.Name;//get property name

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

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

030	object value = 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	return obj;

040

}

041

042	public static List<T> ExecuteList<T>(string cmdText, CommandType commandType, params SqlParameter[] 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	return list;

065

}

066

067	public static T ExecuteEntity<T>(string cmdText, CommandType commandType, params SqlParameter[] 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	return obj;

088

}

089

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

091

{

092	int result = -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	return result;

104

}

105

106

}

107

}