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