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 静态似有变量
/// <summary>
/// SqlConnection 对象实例
/// </summary>
private static SqlConnection conn;
#endregion
#region 共有方法
/// <summary>
/// </summary>
/// <returns></returns>
private static void InitConn()
{
conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["dbstr"].ToString());
}
/// <summary>
/// 实例化一个执行T-Sql语句的SqlCommand对象实例
/// </summary>
/// <param name="que">要执行的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>
/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</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)
{
// 检查未分配值的输入参数,将其分配以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>
/// 创建 SqlCommand 对象实例(用来返回一个整数值)
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</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>
/// 关闭连接和CMD命令并清理资源
/// </summary>
private static void CloseConn()
{
if (conn != null)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
}
#endregion
#region 执行T-Sql语句访问数据库
/// <summary>
/// 执行指定的SQL语句，返回受影响的行数
/// </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>
/// 执行一个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>
/// 执行一个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>
/// 执行一个T-SQL查询，返回DataSet对象
/// </summary>
/// <param name="query">查询的SQL语句</param>
/// <param name="TableName">DataSet结果中的表名</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 执行存储过程访问数据库
/// <summary>
/// 执行指定的存储过程并返回受影响的行
/// </summary>
/// <param name="query">要调用的存储过程名</param>
/// <param name="para">参数集合</param>
/// <returns></returns>
public static int ExecuteNonQuery(string query, IDataParameter[] para)
{
SqlCommand cmd = BuildIntCommand(query, para);
int i = cmd.ExecuteNonQuery();
CloseConn();
return i;
}
/// <summary>
/// 执行有返回参数的存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</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>
/// 调用存储过程执行一个查询，返回首行首列
/// </summary>
/// <param name="query">要调用的存储过程名</param>
/// <param name="para">参数集合</param>
/// <returns></returns>
public static object ExecuteScalar(string query, IDataParameter[] para)
{
SqlCommand cmd = BuildCommand(query, para);
object obj = cmd.ExecuteScalar();
CloseConn();
return obj;
}
/// <summary>
/// 调用存储过程执行查询，返回DataReader对象
/// </summary>
/// <param name="query">要调用的存储过程名</param>
/// <param name="para">参数集合</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>
/// 执行一个T-SQL查询，返回DataSet对象
/// </summary>
/// <param name="query">存储过程名</param>
/// <param name="para">存储过程参数</param>
/// <param name="TableName">DataSet结果中的表名</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
}
}
