using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace RecruitmentSystem.Heper
{
/// <summary>
/// sqlhelper提供对sqlClient常用功能的封装,提高性能和高扩展性的最佳实践
/// </summary>
public class SQLHelper
{ //数据库连接字符串
public static readonly string sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
/// <summary>
/// 执行不返回结果的sql语句,依赖特定的数据库和使用的提供者参数
/// </summary>
/// <param name="connectionString">为SqlConnection指定一个有效的连接字符串</param>
/// <param name="cmdType">命令类型(stored procedure, text等等)</param>
/// <param name="cmdText">存储过程名或T-SQL命令</param>
/// <param name="commandParameters">SqlParamters数组用于执行命令</param>
/// <returns>返回一个整型值得到影响的行数</returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType,
string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{ //根据传入的SqlParameter以及cmdType和cmdText构建SqlCommand命令
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery(); //执行sql,并且返回受影响的参数
cmd.Parameters.Clear();//清除参数集合
return val;
}
}
/// <summary>
/// 执行不返回结果的sql语句,依赖特定的数据库和使用的提供者参数
/// </summary>
/// <param name="con">为SqlConnection指定一个有效的连接字符串</param>
/// <param name="cmdType">命令类型(stored procedure, text等等)</param>
/// <param name="cmdText">存储过程名或T-SQL命令</param>
/// <param name="commandParameters">SqlParamters数组用于执行命令</param>
/// <returns>返回一个整型值得到影响的行数</returns>
public static int ExecuteNonQuery(SqlConnection con, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, con, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 执行一个sqlcommand语句,并且返回SqlDataReader对象
/// </summary>
/// <param name="connectionString">链接数据库的字符串</param>
/// <param name="cmdType">命令类型(stored procedure, text等等)</param>
/// <param name="cmdText">存储过程名或T-SQL命令</param>
/// <param name="commandParameters">SqlParamters数组用于执行命令</param>
/// <returns>返回SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string connectionString,
CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);//根据链接字符串实例化SqlConnection
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//执行完reader后自动关闭
cmd.Parameters.Clear();
return rdr;//返回sqldatareader对象
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// 准备一个SqlCommand执行
/// </summary>
/// <param name="cmd">sqlcommand</param>
/// <param name="conn">数据库的连接字符串</param>
/// <param name="trans">事务</param>
/// <param name="cmdType">command类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="cmdParams">参数,sqlParameter</param>
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn,
SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParams)
{
if (conn.State != ConnectionState.Open)//如果数据库没有打开
{
conn.Open();//打开数据库
}
cmd.Connection = conn; //将连接字符串赋给SqlCommand连接对象
cmd.CommandText = cmdText;//sql语句
if (trans != null)
cmd.Transaction = trans; //开启事务
cmd.CommandType = cmdType; //命令类型
if (cmdParams != null)
{
foreach (SqlParameter param in cmdParams)
cmd.Parameters.Add(param);
}
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace RecruitmentSystem.Heper
{
/// <summary>
/// sqlhelper提供对sqlClient常用功能的封装,提高性能和高扩展性的最佳实践
/// </summary>
public class SQLHelper
{ //数据库连接字符串
public static readonly string sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
/// <summary>
/// 执行不返回结果的sql语句,依赖特定的数据库和使用的提供者参数
/// </summary>
/// <param name="connectionString">为SqlConnection指定一个有效的连接字符串</param>
/// <param name="cmdType">命令类型(stored procedure, text等等)</param>
/// <param name="cmdText">存储过程名或T-SQL命令</param>
/// <param name="commandParameters">SqlParamters数组用于执行命令</param>
/// <returns>返回一个整型值得到影响的行数</returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType,
string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{ //根据传入的SqlParameter以及cmdType和cmdText构建SqlCommand命令
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery(); //执行sql,并且返回受影响的参数
cmd.Parameters.Clear();//清除参数集合
return val;
}
}
/// <summary>
/// 执行不返回结果的sql语句,依赖特定的数据库和使用的提供者参数
/// </summary>
/// <param name="con">为SqlConnection指定一个有效的连接字符串</param>
/// <param name="cmdType">命令类型(stored procedure, text等等)</param>
/// <param name="cmdText">存储过程名或T-SQL命令</param>
/// <param name="commandParameters">SqlParamters数组用于执行命令</param>
/// <returns>返回一个整型值得到影响的行数</returns>
public static int ExecuteNonQuery(SqlConnection con, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, con, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 执行一个sqlcommand语句,并且返回SqlDataReader对象
/// </summary>
/// <param name="connectionString">链接数据库的字符串</param>
/// <param name="cmdType">命令类型(stored procedure, text等等)</param>
/// <param name="cmdText">存储过程名或T-SQL命令</param>
/// <param name="commandParameters">SqlParamters数组用于执行命令</param>
/// <returns>返回SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string connectionString,
CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);//根据链接字符串实例化SqlConnection
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//执行完reader后自动关闭
cmd.Parameters.Clear();
return rdr;//返回sqldatareader对象
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// 准备一个SqlCommand执行
/// </summary>
/// <param name="cmd">sqlcommand</param>
/// <param name="conn">数据库的连接字符串</param>
/// <param name="trans">事务</param>
/// <param name="cmdType">command类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="cmdParams">参数,sqlParameter</param>
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn,
SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParams)
{
if (conn.State != ConnectionState.Open)//如果数据库没有打开
{
conn.Open();//打开数据库
}
cmd.Connection = conn; //将连接字符串赋给SqlCommand连接对象
cmd.CommandText = cmdText;//sql语句
if (trans != null)
cmd.Transaction = trans; //开启事务
cmd.CommandType = cmdType; //命令类型
if (cmdParams != null)
{
foreach (SqlParameter param in cmdParams)
cmd.Parameters.Add(param);
}
}
}
}