using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Collections.Generic;
using System.Diagnostics;
///<summary>
/// DBHelper 的摘要说明
///</summary>
namespace DAL
{
publicstaticclass DBHelper
{
privatestatic SqlConnection conn; //定义一个连接对象
privatestatic SqlTransaction trans;//定义一个事务对象
privatestaticstring connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["FredConnectionString"].ConnectionString;
#region 打开连接
///<summary>
/// 打开连接 单态模式
///</summary>
publicstaticvoid Open()
{
if (conn ==null)
{
conn =new SqlConnection(connectionString);
conn.Open();
}
//判断连接是否关闭，如关闭再次打开
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//如果连接中断，重新打开
if (conn.State == System.Data.ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
}
#endregion
#region 关闭连接
///<summary>
/// 关闭数据库连接
///</summary>
publicstaticvoid Close()
{
if (conn !=null)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
#endregion
#region 执行 DML语句
///<summary>
/// 执行sql语句返回影响的行数
///</summary>
///<param name="safeSql">字符串</param>
///<returns>影响的行数</returns>
publicstaticint ExecuteCommand(string safeSql)
{
Open();
SqlCommand cmd =new SqlCommand(safeSql, conn);
int result = cmd.ExecuteNonQuery();
Close();
return result;
}
///<summary>
/// 执行sql语句返回影响的行数
///</summary>
///<param name="sql">字符串</param>
///<param name="values">参数数组</param>
///<returns>影响的行数</returns>
publicstaticint ExecuteCommand(string sql, params SqlParameter[] values)
{
Open();
SqlCommand cmd =new SqlCommand(sql, conn);
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
Close();
return result;
}
///<summary>
/// 执行sql语句，返回一行一列的值
///</summary>
///<param name="safeSql">字符串</param>
///<returns>结果</returns>
publicstaticint GetScalar(string safeSql)
{
Open();
SqlCommand cmd =new SqlCommand(safeSql, conn);
int result = Convert.ToInt32(cmd.ExecuteScalar());
Close();
return result;
}
///<summary>
/// 执行sql语句，返回一行一列的值
///</summary>
///<param name="sql">字符串</param>
///<param name="values">参数数组</param>
///<returns>结果</returns>
publicstaticint GetScalar(string sql, params SqlParameter[] values)
{
Open();
SqlCommand cmd =new SqlCommand(sql, conn);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
Close();
return result;
}
///<summary>
/// 执行sql语句，返回DataReader
///</summary>
///<param name="safeSql">字符串</param>
///<returns>DataReader</returns>
publicstatic SqlDataReader GetReader(string safeSql)
{
Open();
SqlCommand cmd =new SqlCommand(safeSql, conn);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
///<summary>
/// 执行sql语句，返回DataReader
///</summary>
///<param name="sql">字符串</param>
///<param name="values">参数数组</param>
///<returns>DataReader</returns>
publicstatic SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
Open();
SqlCommand cmd =new SqlCommand(sql, conn);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
///<summary>
/// 执行sql语句，返回表
///</summary>
///<param name="safeSql">字符串</param>
///<returns>数据表</returns>
publicstatic DataTable GetDataSet(string safeSql)
{
Open();
DataSet ds =new DataSet();
SqlCommand cmd =new SqlCommand(safeSql, conn);
SqlDataAdapter da =new SqlDataAdapter(cmd);
da.Fill(ds);
Close();
return ds.Tables[0];
}
///<summary>
/// 执行sql语句，返回表
///</summary>
///<param name="sql">字符串</param>
///<param name="values">参数数组</param>
///<returns>数据表</returns>
publicstatic DataTable GetDataSet(string sql, params SqlParameter[] values)
{
Open();
DataSet ds =new DataSet();
SqlCommand cmd =new SqlCommand(sql, conn);
cmd.Parameters.AddRange(values);
SqlDataAdapter da =new SqlDataAdapter(cmd);
da.Fill(ds);
Close();
return ds.Tables[0];
}
#endregion
#region 事务处理
//开始事务
publicstaticvoid BeginTrans()
{
Open();
trans = conn.BeginTransaction();
}
//执行事务
publicstaticint ExecuteTrans(string sSQL)
{
try
{
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = sSQL;
cmd.CommandTimeout =600;
int i = cmd.ExecuteNonQuery();
return i;
}
catch (Exception e)
{
thrownew Exception(e.Message);
}
}
//提交事务
publicstaticvoid CommitTrans()
{
trans.Commit();
Close();
}
//回滚事务
publicstaticvoid RollBackTrans()
{
trans.Rollback();
Close();
}
#endregion
#region 创建SqlCommand，只在类内部调用
privatestatic SqlCommand CreateCommand(string sProcName, params SqlParameter[] parms)
{
//打开连接
Open();
SqlCommand cmd =new SqlCommand(sProcName, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout =600;
//传参
if (parms !=null)
{
cmd.Parameters.AddRange(parms);
}
//返回参数
cmd.Parameters.Add(new SqlParameter("Return Value", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
return cmd;
}
#endregion
#region 执行存储过程
///<summary>
/// 无参数存储过程
///</summary>
///<param name="sProcName">存储过程名</param>
///<returns>返回Int</returns>
publicstaticint RunProc(string sProcName)
{
SqlCommand cmd = CreateCommand(sProcName, null);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.Write(ex.Message);
return-1;
}
finally
{
Close();
}
return (int)cmd.Parameters["Return Value"].Value;
}
#endregion
#region 执行带参数的存储过程
///<summary>
/// 执行带参数的存储过程
///</summary>
///<param name="sProcName">存储过程名称</param>
///<param name="p">参数数组</param>
///<returns>返回值</returns>
publicstaticint RunProc(string sProcName, params SqlParameter[] p)
{
SqlCommand cmd = CreateCommand(sProcName, p);
try
{
cmd.ExecuteNonQuery();
Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return-1;
}
finally
{
Close();
}
return (int)cmd.Parameters["Return Value"].Value;
}
#endregion
#region 执行带参数的存储过程，返回output参数
///<summary>
/// 执行带参数的存储过程，返回output参数
///</summary>
///<param name="sProcName">存储过程名称</param>
///<param name="p">参数数组</param>
///<returns>返回值</returns>
publicstaticobject RunProcWithOutPut(string sProcName, params SqlParameter[] p)
{
SqlCommand cmd = CreateCommand(sProcName, p);
try
{
cmd.ExecuteNonQuery();
Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
returnnull;
}
finally
{
Close();
}
return cmd.Parameters["@output"].Value;
}
#endregion
#region 执行无参存储过程，返回DataTable
///<summary>
/// 执行无参存储过程，返回DataTable
///</summary>
///<param name="sProcName">存储过程名</param>
///<param name="table">返回DataTable</param>
publicstaticvoid RunProc(string sProcName, out DataTable table)
{
DataSet ds =new DataSet();
SqlCommand cmd = CreateCommand(sProcName, null);
try
{
SqlDataAdapter da =new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds, "T");
table = ds.Tables[0];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
table =null;
}
finally
{
Close();
}
}
#endregion
#region 执行带参存储过程，返回DataTable
///<summary>
/// 执行带参存储过程，返回DataTable
///</summary>
///<param name="sProcName">存储过程名</param>
///<param name="p">参数列表</param>
///<param name="dr">返回DataTable</param>
publicstaticvoid RunProc(string sProcName, SqlParameter[] p, out DataTable table)
{
DataSet ds =new DataSet();
SqlCommand cmd = CreateCommand(sProcName, p);
try
{
SqlDataAdapter da =new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds, "T");
table = ds.Tables[0];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
table =null;
}
finally
{
Close();
}
}
#endregion
#region 执行无参存储过程，返回DataReader
///<summary>
/// 执行无参存储过程，返回DataReader
///</summary>
///<param name="sProcName">存储过程名</param>
///<param name="dr">返回DataReader</param>
publicstaticvoid RunProc(string sProcName, out SqlDataReader dr)
{
SqlCommand cmd = CreateCommand(sProcName, null);
try
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
dr =null;
}
}
#endregion
#region 执行带参存储过程，返回DataReader
///<summary>
/// 执行带参存储过程，返回DataReader
///</summary>
///<param name="sProcName">存储过程名</param>
///<param name="p">参数列表</param>
///<param name="dr">返回DataReader</param>
publicstaticvoid RunProc(string sProcName, SqlParameter[] p, out SqlDataReader dr)
{
SqlCommand cmd = CreateCommand(sProcName, p);
try
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
dr =null;
}
}
#endregion
#region 创建SqlParameter
///<summary>
/// 创建SqlParameter
///</summary>
///<param name="sParamName">参数名称</param>
///<param name="DbType">参数类型，SqlDbType枚举</param>
///<param name="size">参数大小</param>
///<param name="d">参数类型</param>
///<param name="v">参数值</param>
///<returns>返回创建好的SqlParameter</returns>
publicstatic SqlParameter MakeParam(string sParamName, SqlDbType DbType,
Int32 size, ParameterDirection d, object v)
{
SqlParameter pa;
if (size >0)
pa =new SqlParameter(sParamName, DbType, size);
else
pa =new SqlParameter(sParamName, DbType);
pa.Direction = d;
if (!(d == ParameterDirection.Output && v ==null))
pa.Value = v;
return pa;
}
#endregion
#region 创建输入参数
///<summary>
/// 创建传入参数
///</summary>
///<param name="sParamName">参数名</param>
///<param name="d">参数类型，为SqlDbType枚举</param>
///<param name="size">大小</param>
///<param name="v">参数值</param>
///<returns>返回创建好的输入参数</returns>
publicstatic SqlParameter MakeInParam(string sParamName, SqlDbType d, int size, object v)
{
return MakeParam(sParamName, d, size, ParameterDirection.Input, v);
}
#endregion
#region 创建输出参数
///<summary>
/// 创建输出参数
///</summary>
///<param name="sParamName">参数名</param>
///<param name="d">参数类型，为SqlDbType枚举成员</param>
///<param name="size">参数大小</param>
///<returns>返回创建好的输出参数</returns>
publicstatic SqlParameter MakeOutParam(string sParamName, SqlDbType d, int size)
{
return MakeParam(sParamName, d, size, ParameterDirection.Output, null);
}
#endregion
}
}
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Collections.Generic;
using System.Diagnostics;
///<summary>
/// DBHelper 的摘要说明
///</summary>
namespace DAL
{
publicstaticclass DBHelper
{
privatestatic SqlConnection conn; //定义一个连接对象
privatestatic SqlTransaction trans;//定义一个事务对象
privatestaticstring connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["FredConnectionString"].ConnectionString;
#region 打开连接
///<summary>
/// 打开连接 单态模式
///</summary>
publicstaticvoid Open()
{
if (conn ==null)
{
conn =new SqlConnection(connectionString);
conn.Open();
}
//判断连接是否关闭，如关闭再次打开
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//如果连接中断，重新打开
if (conn.State == System.Data.ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
}
#endregion
#region 关闭连接
///<summary>
/// 关闭数据库连接
///</summary>
publicstaticvoid Close()
{
if (conn !=null)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
#endregion
#region 执行 DML语句
///<summary>
/// 执行sql语句返回影响的行数
///</summary>
///<param name="safeSql">字符串</param>
///<returns>影响的行数</returns>
publicstaticint ExecuteCommand(string safeSql)
{
Open();
SqlCommand cmd =new SqlCommand(safeSql, conn);
int result = cmd.ExecuteNonQuery();
Close();
return result;
}
///<summary>
/// 执行sql语句返回影响的行数
///</summary>
///<param name="sql">字符串</param>
///<param name="values">参数数组</param>
///<returns>影响的行数</returns>
publicstaticint ExecuteCommand(string sql, params SqlParameter[] values)
{
Open();
SqlCommand cmd =new SqlCommand(sql, conn);
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
Close();
return result;
}
///<summary>
/// 执行sql语句，返回一行一列的值
///</summary>
///<param name="safeSql">字符串</param>
///<returns>结果</returns>
publicstaticint GetScalar(string safeSql)
{
Open();
SqlCommand cmd =new SqlCommand(safeSql, conn);
int result = Convert.ToInt32(cmd.ExecuteScalar());
Close();
return result;
}
///<summary>
/// 执行sql语句，返回一行一列的值
///</summary>
///<param name="sql">字符串</param>
///<param name="values">参数数组</param>
///<returns>结果</returns>
publicstaticint GetScalar(string sql, params SqlParameter[] values)
{
Open();
SqlCommand cmd =new SqlCommand(sql, conn);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
Close();
return result;
}
///<summary>
/// 执行sql语句，返回DataReader
///</summary>
///<param name="safeSql">字符串</param>
///<returns>DataReader</returns>
publicstatic SqlDataReader GetReader(string safeSql)
{
Open();
SqlCommand cmd =new SqlCommand(safeSql, conn);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
///<summary>
/// 执行sql语句，返回DataReader
///</summary>
///<param name="sql">字符串</param>
///<param name="values">参数数组</param>
///<returns>DataReader</returns>
publicstatic SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
Open();
SqlCommand cmd =new SqlCommand(sql, conn);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
///<summary>
/// 执行sql语句，返回表
///</summary>
///<param name="safeSql">字符串</param>
///<returns>数据表</returns>
publicstatic DataTable GetDataSet(string safeSql)
{
Open();
DataSet ds =new DataSet();
SqlCommand cmd =new SqlCommand(safeSql, conn);
SqlDataAdapter da =new SqlDataAdapter(cmd);
da.Fill(ds);
Close();
return ds.Tables[0];
}
///<summary>
/// 执行sql语句，返回表
///</summary>
///<param name="sql">字符串</param>
///<param name="values">参数数组</param>
///<returns>数据表</returns>
publicstatic DataTable GetDataSet(string sql, params SqlParameter[] values)
{
Open();
DataSet ds =new DataSet();
SqlCommand cmd =new SqlCommand(sql, conn);
cmd.Parameters.AddRange(values);
SqlDataAdapter da =new SqlDataAdapter(cmd);
da.Fill(ds);
Close();
return ds.Tables[0];
}
#endregion
#region 事务处理
//开始事务
publicstaticvoid BeginTrans()
{
Open();
trans = conn.BeginTransaction();
}
//执行事务
publicstaticint ExecuteTrans(string sSQL)
{
try
{
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = sSQL;
cmd.CommandTimeout =600;
int i = cmd.ExecuteNonQuery();
return i;
}
catch (Exception e)
{
thrownew Exception(e.Message);
}
}
//提交事务
publicstaticvoid CommitTrans()
{
trans.Commit();
Close();
}
//回滚事务
publicstaticvoid RollBackTrans()
{
trans.Rollback();
Close();
}
#endregion
#region 创建SqlCommand，只在类内部调用
privatestatic SqlCommand CreateCommand(string sProcName, params SqlParameter[] parms)
{
//打开连接
Open();
SqlCommand cmd =new SqlCommand(sProcName, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout =600;
//传参
if (parms !=null)
{
cmd.Parameters.AddRange(parms);
}
//返回参数
cmd.Parameters.Add(new SqlParameter("Return Value", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
return cmd;
}
#endregion
#region 执行存储过程
///<summary>
/// 无参数存储过程
///</summary>
///<param name="sProcName">存储过程名</param>
///<returns>返回Int</returns>
publicstaticint RunProc(string sProcName)
{
SqlCommand cmd = CreateCommand(sProcName, null);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.Write(ex.Message);
return-1;
}
finally
{
Close();
}
return (int)cmd.Parameters["Return Value"].Value;
}
#endregion
#region 执行带参数的存储过程
///<summary>
/// 执行带参数的存储过程
///</summary>
///<param name="sProcName">存储过程名称</param>
///<param name="p">参数数组</param>
///<returns>返回值</returns>
publicstaticint RunProc(string sProcName, params SqlParameter[] p)
{
SqlCommand cmd = CreateCommand(sProcName, p);
try
{
cmd.ExecuteNonQuery();
Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return-1;
}
finally
{
Close();
}
return (int)cmd.Parameters["Return Value"].Value;
}
#endregion
#region 执行带参数的存储过程，返回output参数
///<summary>
/// 执行带参数的存储过程，返回output参数
///</summary>
///<param name="sProcName">存储过程名称</param>
///<param name="p">参数数组</param>
///<returns>返回值</returns>
publicstaticobject RunProcWithOutPut(string sProcName, params SqlParameter[] p)
{
SqlCommand cmd = CreateCommand(sProcName, p);
try
{
cmd.ExecuteNonQuery();
Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
returnnull;
}
finally
{
Close();
}
return cmd.Parameters["@output"].Value;
}
#endregion
#region 执行无参存储过程，返回DataTable
///<summary>
/// 执行无参存储过程，返回DataTable
///</summary>
///<param name="sProcName">存储过程名</param>
///<param name="table">返回DataTable</param>
publicstaticvoid RunProc(string sProcName, out DataTable table)
{
DataSet ds =new DataSet();
SqlCommand cmd = CreateCommand(sProcName, null);
try
{
SqlDataAdapter da =new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds, "T");
table = ds.Tables[0];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
table =null;
}
finally
{
Close();
}
}
#endregion
#region 执行带参存储过程，返回DataTable
///<summary>
/// 执行带参存储过程，返回DataTable
///</summary>
///<param name="sProcName">存储过程名</param>
///<param name="p">参数列表</param>
///<param name="dr">返回DataTable</param>
publicstaticvoid RunProc(string sProcName, SqlParameter[] p, out DataTable table)
{
DataSet ds =new DataSet();
SqlCommand cmd = CreateCommand(sProcName, p);
try
{
SqlDataAdapter da =new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds, "T");
table = ds.Tables[0];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
table =null;
}
finally
{
Close();
}
}
#endregion
#region 执行无参存储过程，返回DataReader
///<summary>
/// 执行无参存储过程，返回DataReader
///</summary>
///<param name="sProcName">存储过程名</param>
///<param name="dr">返回DataReader</param>
publicstaticvoid RunProc(string sProcName, out SqlDataReader dr)
{
SqlCommand cmd = CreateCommand(sProcName, null);
try
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
dr =null;
}
}
#endregion
#region 执行带参存储过程，返回DataReader
///<summary>
/// 执行带参存储过程，返回DataReader
///</summary>
///<param name="sProcName">存储过程名</param>
///<param name="p">参数列表</param>
///<param name="dr">返回DataReader</param>
publicstaticvoid RunProc(string sProcName, SqlParameter[] p, out SqlDataReader dr)
{
SqlCommand cmd = CreateCommand(sProcName, p);
try
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
dr =null;
}
}
#endregion
#region 创建SqlParameter
///<summary>
/// 创建SqlParameter
///</summary>
///<param name="sParamName">参数名称</param>
///<param name="DbType">参数类型，SqlDbType枚举</param>
///<param name="size">参数大小</param>
///<param name="d">参数类型</param>
///<param name="v">参数值</param>
///<returns>返回创建好的SqlParameter</returns>
publicstatic SqlParameter MakeParam(string sParamName, SqlDbType DbType,
Int32 size, ParameterDirection d, object v)
{
SqlParameter pa;
if (size >0)
pa =new SqlParameter(sParamName, DbType, size);
else
pa =new SqlParameter(sParamName, DbType);
pa.Direction = d;
if (!(d == ParameterDirection.Output && v ==null))
pa.Value = v;
return pa;
}
#endregion
#region 创建输入参数
///<summary>
/// 创建传入参数
///</summary>
///<param name="sParamName">参数名</param>
///<param name="d">参数类型，为SqlDbType枚举</param>
///<param name="size">大小</param>
///<param name="v">参数值</param>
///<returns>返回创建好的输入参数</returns>
publicstatic SqlParameter MakeInParam(string sParamName, SqlDbType d, int size, object v)
{
return MakeParam(sParamName, d, size, ParameterDirection.Input, v);
}
#endregion
#region 创建输出参数
///<summary>
/// 创建输出参数
///</summary>
///<param name="sParamName">参数名</param>
///<param name="d">参数类型，为SqlDbType枚举成员</param>
///<param name="size">参数大小</param>
///<returns>返回创建好的输出参数</returns>
publicstatic SqlParameter MakeOutParam(string sParamName, SqlDbType d, int size)
{
return MakeParam(sParamName, d, size, ParameterDirection.Output, null);
}
#endregion
}
}