DBHeler.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using BookShop.Models;
namespace BookShop.DAL
{
class DBHeler:IDisposable
{
private string connectionString; //配置文件的连接字符串语句
private bool isConnected; //是否连接,默认为false
private bool isTransaction; //是否开始事务,默认为false
private SqlConnection connection; //SqlConnection 数据库连接对象
private SqlCommand command; //SqlCommand 执行对象
private SqlTransaction transaction; //事务
/// <summary>
/// 初始化
/// </summary>
public DBHeler()
{
isConnected = false;
isTransaction = false;
}
/// <summary>
/// 连接数据库,连接成功返回true
/// </summary>
/// <returns></returns>
public bool ConnectionDatabase()
{
if (!isConnected) //若数据库已连接
{
try
{
if (connection == null) //若连接对象为空
{
//获取连接字符串
connectionString = ConfigurationManager.ConnectionStrings["BookShopPlus"].ToString();
connection = new SqlConnection(connectionString); //创建连接对象
connection.Open(); //打开连接
}
if (command == null) //若SqlCommand对象为空
{
command = new SqlCommand(); //创建SqlCommand对象
}
command.Connection = connection; //声明SqlCommand对象的连接为SqlConnection连接对象
}
catch //捕获异常
{
isConnected = false; //是否连接为False
return false;
}
}
isConnected = true; //否则最后，是否连接为True
return true;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
/// <returns></returns>
public bool CloseDatabase()
{
Dispose(); //释放正在使用的所用资源
return true;
}
/// <summary>
/// 执行方法(适用增、删、改等操作)
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public int Excecute(string sql,params SqlParameter[] param)
{
if (!ConnectionDatabase()) //若数据库连接过程中出错
{
throw new Exception("连接数据库失败");
}
command.CommandType = CommandType.Text;
command.Parameters.AddRange(param);
command.CommandText = sql;
try
{
command.Parameters.Clear();
return command.ExecuteNonQuery();
}
catch
{
if (isTransaction) //若已经开启事务
{
transaction.Rollback(); //将事务回滚
}
throw;
}
}
/// <summary>
/// 执行方法(查询)
/// </summary>
/// <param name="sql"></param>
/// <param name="para"></param>
/// <returns></returns>
public SqlDataReader SelectDo(string sql)
{
if (!ConnectionDatabase()) //若数据库连接过程中出错
{
throw new Exception("连接数据库失败");
}
try
{
command.CommandText = sql;
return command.ExecuteReader();
}
catch
{
if (isTransaction) //若已经开启事务
{
transaction.Rollback(); //将事务回滚
}
throw;
}
}
/// <summary>
/// 执行方法(查询)(重载1)
/// </summary>
/// <param name="sql"></param>
/// <param name="para"></param>
/// <returns></returns>
public SqlDataReader SelectDo(string sql, params SqlParameter[] para)
{
if (!ConnectionDatabase()) //若数据库连接过程中出错
{
throw new Exception("连接数据库失败");
}
try
{
command.CommandType = CommandType.Text;
command.CommandText = sql;
command.Parameters.AddRange(para);
SqlDataReader reader = command.ExecuteReader();
command.Parameters.Clear();
return reader;
}
catch
{
if (isTransaction) //若已经开启事务
{
transaction.Rollback(); //将事务回滚
}
throw;
}
}
/// <summary>
/// 开始事务(某些特定情况下可以使用,例如：删除学生、ATM交易)
/// </summary>
public void StartTransaction()
{
if (!ConnectionDatabase())
{
throw new Exception("连接数据库失败");
}
isTransaction = true;
transaction = connection.BeginTransaction();
command.Transaction = transaction;
}
public bool Commit()
{
if (!isTransaction)
{
return true;
}
try
{
transaction.Commit();
}
catch (SqlException ex)
{
transaction.Rollback();
throw ex;
}
return true;
}
#region IDisposable 成员
public void Dispose()
{
if (isConnected)
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
connection.Dispose();
command = null;
connection = null;
transaction = null;
isConnected = false;
}
}
GC.SuppressFinalize(true);
}
#endregion
}
}
