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 | } |