using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
/*
* 所属层次:数据访问类组件(数据操作)
*/
namespace DBUtility
{
/// <summary>
/// SqlHelper类用于操作数据库(可升级,扩展) 抽象类+静态方法。
/// </summary>
public abstract class SqlHelper
{
//数据库连接字符串
//连接字符串在界面层的...的配置文件中。
//public static readonly string ConnectionStringSqlServer = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
/// <summary>
/// 给定连接的数据库用假设参数执行一个sql命令(不返回数据集)
/// </summary>
/// <param name="connectionString">一个有效的连接字符串</param>
/// <param name="commandType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="commandText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>执行命令所影响的行数</returns>
public static int ExecuteNonQuery( CommandType cmdType, string cmdText, SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmd.Parameters != null)
{
cmd.Parameters.Clear();
}
if (commandParameters != null)
{
foreach (SqlParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
try
{
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
catch
{
//关闭连接,抛出异常
conn.Close();
throw;
}
finally
{
conn.Close();
}
}
}
/// <summary>
/// 执行存储过程,返回一个输出参数
/// </summary>
/// <param name="connectionString">一个有效的连接字符串</param>
/// <param name="cmdText">存储过程名称</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>输出参数</returns>
public static object ExecuteProcGetOutput( string cmdText, string outputParaName, SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
if (commandParameters != null)
{
foreach (SqlParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
try
{
cmd.ExecuteNonQuery();
object val = cmd.Parameters[outputParaName].Value;
cmd.Parameters.Clear();
return val;
}
catch
{
//关闭连接,抛出异常
conn.Close();
throw;
}
}
}
/// <summary>
/// 用执行的数据库连接执行一个返回数据集的sql命令
/// </summary>
/// <param name="connectionString">一个有效的连接字符串</param>
/// <param name="commandType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="commandText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>包含结果的读取器</returns>
public static SqlDataReader ExecuteReader( CommandType cmdType, string cmdText, SqlParameter[] commandParameters)
{
//创建一个SqlCommand对象
SqlCommand cmd = new SqlCommand();
//创建一个SqlConnection对象
SqlConnection conn = new SqlConnection(connectionString);
//不能使用using 因为要返回SqlDataReader,否则外面的SqlDataReader不能使用。
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (commandParameters != null)
{
foreach (SqlParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
try
{
//调用 SqlCommand 的 ExecuteReader 方法
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//清除参数
cmd.Parameters.Clear();
return reader;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// 用执行的数据库连接执行一个返回一张表命令
/// </summary>
/// <param name="connectionString">一个有效的连接字符串</param>
/// <param name="commandText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>包含结果的读取器</returns>
public static DataTable ExectuteDataTable(string cmdText, SqlParameter[] commandParameters)
{
//创建一个SqlConnection对象
SqlConnection conn = new SqlConnection(connectionString);
//创建一个SqlDataAdapter对象
SqlDataAdapter sda = new SqlDataAdapter(cmdText,conn);
if (commandParameters != null)
{
foreach (SqlParameter parm in commandParameters)
sda.SelectCommand.Parameters.Add(parm);
}
try
{
//调用 SqlCommand 的 ExecuteReader 方法
DataTable dt = new DataTable();
sda.Fill(dt);
//清除参数
sda.SelectCommand.Parameters.Clear();
return dt;
}
catch
{
conn.Close();
throw;
}
}
}
}