using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using System.Configuration;
namespace Web.SqlServerDAL
{
public class SqlHelper
{
#region 變量
/// <summary>
/// 數據庫連接對象
/// </summary>
private static SqlConnection _con = null;
public static string constr = "數據庫連接地址";
#endregion
#region 屬性
/// <summary>
/// 獲取或設置數據庫連接對象
/// </summary>
public static SqlConnection Con
{
get
{
if (SqlHelper._con == null)
{
SqlHelper._con = new SqlConnection();
}
if (SqlHelper._con.ConnectionString == "")
{
SqlHelper._con.ConnectionString = SqlHelper.constr;
}
return SqlHelper._con;
}
set
{
SqlHelper._con = value;
}
}
#endregion
#region 方法
#region 執行返回一行一列的數據庫操作
/// <summary>
/// 執行返回一行一列的數據庫操作
/// </summary>
/// <param name="commandText">SQL語句或存儲過程名</param>
/// <param name="commandType">SQL命令類型</param>
/// <param name="param">SQL命令參數數組</param>
/// <returns>第一行第一列的記錄</returns>
public static int ExecuteScalar(string commandText, CommandType commandType, params SqlParameter[] param)
{
int count = 0;
using (SqlHelper.Con)
{
using (SqlCommand cmd = new SqlCommand(commandText, SqlHelper.Con))
{
try
{
cmd.CommandType = commandType;
cmd.Parameters.AddRange(param);
SqlHelper.Con.Open();
count = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
}
}
return count;
}
#endregion
#region 執行不查詢的數據庫操作
/// <summary>
/// 執行不查詢的數據庫操作
/// </summary>
/// <param name="commandText">SQL語句或存儲過程名</param>
/// <param name="commandType">SQL命令類型</param>
/// <param name="param">SQL命令參數數組</param>
/// <returns>受影響的行數</returns>
public static int ExecuteNonQuery(string commandText, CommandType commandType, params SqlParameter[] param)
{
int result = 0;
using (SqlHelper.Con)
{
using (SqlCommand cmd = new SqlCommand(commandText, SqlHelper.Con))
{
try
{
cmd.CommandType = commandType;
cmd.Parameters.AddRange(param);
SqlHelper.Con.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
}
return result;
}
#endregion
#region 執行返回一條記錄的泛型對象
/// <summary>
/// 執行返回一條記錄的泛型對象
/// </summary>
/// <typeparam name="T">泛型類型</typeparam>
/// <param name="reader">只進只讀對象</param>
/// <returns>泛型對象</returns>
private static T ExecuteDataReader<T>(IDataReader reader)
{
T obj = default(T);
try
{
Type type = typeof(T);
obj = (T)Activator.CreateInstance(type);//從當前程序集裡面通過反射的方式創建指定類型的對象
//obj = (T)Assembly.Load(SqlHelper._assemblyName).CreateInstance(SqlHelper._assemblyName + "." + type.Name);//從另一個程序集裡面通過反射的方式創建指定類型的對象
PropertyInfo[] propertyInfos = type.GetProperties();//獲取指定類型裡面的所有屬性
foreach (PropertyInfo propertyInfo in propertyInfos)
{
for (int i = 0; i < reader.FieldCount; i++)
{
string fieldName = reader.GetName(i);
if (fieldName.ToLower() == propertyInfo.Name.ToLower())
{
object val = reader[propertyInfo.Name];//讀取表中某一條記錄裡面的某一列信息
if (val != null && val != DBNull.Value)
propertyInfo.SetValue(obj, val, null);//給對象的某一個屬性賦值
break;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return obj;
}
#endregion
#region 執行返回一條記錄的泛型對象
/// <summary>
/// 執行返回一條記錄的泛型對象
/// </summary>
/// <typeparam name="T">泛型類型</typeparam>
/// <param name="commandText">SQL語句或存儲過程名</param>
/// <param name="commandType">SQL命令類型</param>
/// <param name="param">SQL命令參數數組</param>
/// <returns>實體對象</returns>
public static T ExecuteEntity<T>(string commandText, CommandType commandType, params SqlParameter[] param)
{
T obj = default(T);
using (SqlHelper.Con)
{
using (SqlCommand cmd = new SqlCommand(commandText, SqlHelper.Con))
{
cmd.CommandType = commandType;
cmd.Parameters.AddRange(param);
SqlHelper.Con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
obj = SqlHelper.ExecuteDataReader<T>(reader);
}
}
}
return obj;
}
#endregion
#region 執行返回多條記錄的泛型集合對象
/// <summary>
/// 執行返回多條記錄的泛型集合對象
/// </summary>
/// <typeparam name="T">泛型類型</typeparam>
/// <param name="commandText">SQL語句或存儲過程名</param>
/// <param name="commandType">SQL命令類型</param>
/// <param name="param">SQL命令參數數組</param>
/// <returns>泛型集合對象</returns>
public static List<T> ExecuteList<T>(string commandText, CommandType commandType, params SqlParameter[] param)
{
List<T> list = new List<T>();
using (SqlHelper.Con)
{
using (SqlCommand cmd = new SqlCommand(commandText, SqlHelper.Con))
{
try
{
cmd.CommandType = commandType;
cmd.Parameters.AddRange(param);
SqlHelper.Con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
T obj = SqlHelper.ExecuteDataReader<T>(reader);
list.Add(obj);
}
}
catch (Exception ex)
{
throw ex;
}
}
}
return list;
}
#endregion
#endregion
}
}
