DBHelper

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
{
public static class DBHelper
{

private static SqlConnection conn; // 定义一个连接对象
private static SqlTransaction trans; // 定义一个事务对象

private static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[ " FredConnectionString " ].ConnectionString;


#region 打开连接
/// <summary>
/// 打开连接 单态模式
/// </summary>
public static void 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>
public static void Close()
{
if (conn != null )
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}

}

}
#endregion

#region 执行 DML语句
/// <summary>
/// 执行sql语句返回影响的行数
/// </summary>
/// <param name="safeSql"> 字符串 </param>
/// <returns> 影响的行数 </returns>
public static int 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>
public static int 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>
public static int 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>
public static int 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>
public static 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>
public static 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>
public static 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>
public static 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 事务处理
// 开始事务
public static void BeginTrans()
{
Open();
trans = conn.BeginTransaction();
}
// 执行事务
public static int 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)
{
throw new Exception(e.Message);
}

}
// 提交事务
public static void CommitTrans()
{
trans.Commit();
Close();
}

// 回滚事务
public static void RollBackTrans()
{
trans.Rollback();
Close();
}
#endregion

#region 创建SqlCommand，只在类内部调用
private static 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>
public static int 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>
public static int 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>
public static object RunProcWithOutPut( string sProcName, params SqlParameter[] p)
{
SqlCommand cmd = CreateCommand(sProcName, p);
try
{
cmd.ExecuteNonQuery();
Close();
}
catch (Exception ex)
{

Console.WriteLine(ex.Message);
return null ;
}
finally
{
Close();
}
return cmd.Parameters[ " @output " ].Value;
}
#endregion

#region 执行无参存储过程，返回DataTable
/// <summary>
/// 执行无参存储过程，返回DataTable
/// </summary>
/// <param name="sProcName"> 存储过程名 </param>
/// <param name="table"> 返回DataTable </param>
public static void 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>
public static void 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>
public static void 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>
public static void 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>
public static 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>
public static 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>
public static SqlParameter MakeOutParam( string sParamName, SqlDbType d, int size)
{
return MakeParam(sParamName, d, size, ParameterDirection.Output, null );
}
#endregion

}

}

转载于:https://www.cnblogs.com/muyoushui/archive/2010/06/04/1751624.html