using System;
using System.Collections.Generic;
using System.Text;
//引入必須的命名空間
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DAL
{
public class SqlHelper
{
#region 靜態(tài)似有變量
/// <summary>
/// SqlConnection 對象實例
/// </summary>
private static SqlConnection conn;
#endregion
#region 共有方法
/// <summary>
/// 返回一個未打開的數(shù)據(jù)庫連接類
/// </summary>
/// <returns></returns>
private static void InitConn()
{
conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["dbstr"].ToString());
}
/// <summary>
/// 實例化一個執(zhí)行T-Sql語句的SqlCommand對象實例
/// </summary>
/// <param name="que">要執(zhí)行的SQL語句</param>
/// <returns></returns>
private static SqlCommand BuildCommand(string que)
{
InitConn();
try
{
if (conn.State == ConnectionState.Closed)
conn.Open();
SqlCommand cmd = new SqlCommand(que, conn);
return cmd;
}
catch(SqlException EX)
{
throw EX;
}
}
/// <summary>
/// 構(gòu)建 SqlCommand 對象(用來返回一個結(jié)果集，而不是一個整數(shù)值)
/// </summary>
/// <param name="connection">數(shù)據(jù)庫連接</param>
/// <param name="storedProcName">存儲過程名</param>
/// <param name="parameters">存儲過程參數(shù)</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildCommand(string storedProcName, IDataParameter[] parameters)
{
InitConn();
conn.Open();
SqlCommand cmd = new SqlCommand(storedProcName, conn);
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 檢查未分配值的輸入?yún)?shù),將其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
return cmd;
}
/// <summary>
/// 創(chuàng)建 SqlCommand 對象實例(用來返回一個整數(shù)值)
/// </summary>
/// <param name="storedProcName">存儲過程名</param>
/// <param name="parameters">存儲過程參數(shù)</param>
/// <returns>SqlCommand 對象實例</returns>
private static SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand cmd = BuildCommand(storedProcName, parameters);
cmd.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
return cmd;
}
/// <summary>
/// 關(guān)閉連接和CMD命令并清理資源
/// </summary>
private static void CloseConn()
{
if (conn != null)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
}
#endregion
#region 執(zhí)行T-Sql語句訪問數(shù)據(jù)庫
/// <summary>
/// 執(zhí)行指定的SQL語句，返回受影響的行數(shù)
/// </summary>
/// <param name="query">查詢的SQL語句</param>
/// <returns></returns>
public static int ExecuteNonQuery(string query)
{
SqlCommand cmd = BuildCommand(query);
int i = cmd.ExecuteNonQuery();
CloseConn();
return i;
}
/// <summary>
/// 執(zhí)行一個T-SQL查詢，返回首行首列
/// </summary>
/// <param name="query">查詢的SQL語句</param>
/// <returns></returns>
public static object ExecuteScalar(string query)
{
SqlCommand cmd = BuildCommand(query);
object obj = cmd.ExecuteScalar();
CloseConn();
return obj;
}
/// <summary>
/// 執(zhí)行一個T-SQL查詢，返回DataReader對象
/// </summary>
/// <param name="query">查詢的SQL語句</param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string query)
{
SqlCommand cmd = BuildCommand(query);
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
/// <summary>
/// 執(zhí)行一個T-SQL查詢，返回DataSet對象
/// </summary>
/// <param name="query">查詢的SQL語句</param>
/// <param name="TableName">DataSet結(jié)果中的表名</param>
/// <returns></returns>
public static DataSet GetList(string query,string TableName)
{
InitConn();
SqlDataAdapter sda = new SqlDataAdapter(query, conn);
DataSet ds = new DataSet();
sda.Fill(ds, TableName);
CloseConn();
return ds;
}
#endregion
#region 執(zhí)行存儲過程訪問數(shù)據(jù)庫
/// <summary>
/// 執(zhí)行指定的存儲過程并返回受影響的行
/// </summary>
/// <param name="query">要調(diào)用的存儲過程名</param>
/// <param name="para">參數(shù)集合</param>
/// <returns></returns>
public static int ExecuteNonQuery(string query, IDataParameter[] para)
{
SqlCommand cmd = BuildIntCommand(query, para);
int i = cmd.ExecuteNonQuery();
CloseConn();
return i;
}
/// <summary>
/// 執(zhí)行有返回參數(shù)的存儲過程
/// </summary>
/// <param name="storedProcName">存儲過程名</param>
/// <param name="parameters">存儲過程參數(shù)</param>
/// <param name="rowsAffected">影響的行數(shù)</param>
/// <returns></returns>
public static int ExecuteNonQuery(string query, IDataParameter[] para, out int rowsAffected)
{
SqlCommand cmd = BuildIntCommand(query, para);
rowsAffected = cmd.ExecuteNonQuery();
int result = (int)cmd.Parameters["ReturnValue"].Value;
CloseConn();
return result;
}
/// <summary>
/// 調(diào)用存儲過程執(zhí)行一個查詢，返回首行首列
/// </summary>
/// <param name="query">要調(diào)用的存儲過程名</param>
/// <param name="para">參數(shù)集合</param>
/// <returns></returns>
public static object ExecuteScalar(string query, IDataParameter[] para)
{
SqlCommand cmd = BuildCommand(query, para);
object obj = cmd.ExecuteScalar();
CloseConn();
return obj;
}
/// <summary>
/// 調(diào)用存儲過程執(zhí)行查詢，返回DataReader對象
/// </summary>
/// <param name="query">要調(diào)用的存儲過程名</param>
/// <param name="para">參數(shù)集合</param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string query, IDataParameter[] para)
{
SqlCommand cmd = BuildCommand(query, para);
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
/// <summary>
/// 執(zhí)行一個T-SQL查詢，返回DataSet對象
/// </summary>
/// <param name="query">存儲過程名</param>
/// <param name="para">存儲過程參數(shù)</param>
/// <param name="TableName">DataSet結(jié)果中的表名</param>
/// <returns></returns>
public static DataSet GetList(string query,IDataParameter[] para, string TableName)
{
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = BuildCommand(query, para);
DataSet ds = new DataSet();
sda.Fill(ds, TableName);
CloseConn();
return ds;
}
#endregion
}
}
