C#数据访问层

 using System;
using System.Data;
using System.Data.SqlClient;

namespace ClothLib.Common
{
 public class DataBaseOperate:IDisposable
 {
  // 保存数据库连接字符串
  private string m_sConnectionString;
  
  // 数据库连接标志
  private bool m_bConnected;
  
  // 存储过程开启标志
  private bool m_bTransaction;
  
  
  //数据库连接对象
  private SqlConnection m_sqlConnection;
  private SqlCommand m_sqlCommand;
  private SqlTransaction m_sqlTransaction;
  private SqlDataAdapter m_sqlDataAdapter;
  
  /// <summary>
  /// 构造函数,初始化内部变量
  /// </summary>
  public DataBaseOperate()
   :this("Integrated Security=SSPI;server=.;database=Wear")
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }
  
  public DataBaseOperate(
   string sConnectionString)
  {
   if (sConnectionString != "")
   {
    m_sConnectionString = sConnectionString;
    m_bConnected = false;
   }
   m_bTransaction = false;
  }

  /// <summary>
  /// 连接数据库,并打开数据库连接
  /// </summary>
  /// <returns>成功返回true</returns>
  private bool ConnectDataBase()
  {
   if (!m_bConnected)
   {
    try
    {
     if (m_sqlConnection == null)
     {
      m_sqlConnection = new SqlConnection(m_sConnectionString);
      m_sqlConnection.Open();
     }

     if (m_sqlCommand == null)
     {
      m_sqlCommand = new SqlCommand();
     }
     m_bConnected = true;
     m_sqlCommand.Connection = m_sqlConnection;
    }
    catch(SqlException e)
    {
     throw e;
    }
   }
   return true;
  }
  
  /// <summary>
  /// 关闭数据库,释放数据库资源
  /// </summary>
  /// <returns>成功返回true</returns>
  public bool CloseDataBase()
  {
   Dispose();
   return true;
  }
  public void sqlcon()
  {
   if(m_sqlConnection == null)
   {
    m_sqlConnection= new SqlConnection("Integrated Security=SSPI;server=.;database=Wear");
    m_sqlConnection.Open();
   }
   
  }

  /// <summary>
  /// 执行数据库查询操作
  /// </summary>
  /// <param name="sQuery">查询的Sql语句</param>
  /// <param name="sTableName">返回数据集的表名</param>
  /// <returns>返回数据集</returns>
  public DataSet Search(
   string sQuery,
   string sTableName )
  {
    
   //若连接数据库失败抛出错误
   if (!ConnectDataBase())
   {
    throw(new ApplicationException("没有建立数据库连接。"));
   }
   
   DataSet dataSet = new DataSet();
   m_sqlCommand.CommandType = System.Data.CommandType.Text;
   m_sqlCommand.CommandText = sQuery;
   m_sqlDataAdapter = new SqlDataAdapter();
   m_sqlDataAdapter.SelectCommand = m_sqlCommand;
   try
   {
    m_sqlDataAdapter.Fill(
     dataSet,
     sTableName);
   }
   catch(SqlException e)
   {
    //如果正在执行事务,回滚
    if(m_bTransaction)
    {
     m_sqlTransaction.Rollback();
    }
    throw e;
   }
   return dataSet;
  }

  
  /// <summary>
  ///  执行修改数据库操作,修改、删除等无返回值的操作
  /// </summary>
  /// <param name="sSql">执行的SQL语句</param>
  /// <returns>成功执行返回True</returns>
  public bool Execute(
   string sSql)
  {
   
   if (!ConnectDataBase())
   {
    throw(new ApplicationException("没有建立数据库连接"));
   }
   
   m_sqlCommand.CommandType = System.Data.CommandType.Text;
   m_sqlCommand.CommandText = sSql;
   try
   {
    m_sqlCommand.ExecuteNonQuery();    
   }
   catch(SqlException e)
   {
    //如果正在执行事务,回滚
    if(m_bTransaction)
    {
     m_sqlTransaction.Rollback();
    }
    throw e;
   }
   return true;   
  }

  /// <summary>
  /// 开始事务处理功能,之后执行的全部数据库操作语句需要调用提交函数(commit)生效
  /// </summary>
  public void StartTransation()
  {
   //若连接数据库失败抛出错误
   if (!ConnectDataBase())
   {
    throw(new ApplicationException("没有建立数据库连接。"));
   }

   m_bTransaction = true;
   m_sqlTransaction = m_sqlConnection.BeginTransaction(
    IsolationLevel.ReadCommitted);
   m_sqlCommand.Transaction = m_sqlTransaction;
   
  }

  /// <summary>
  /// 当前待处理事务提交,失败全部回滚
  /// <returns>成功提交返回true</returns>
  public bool Commit()
  {
   //如果没有开启事务处理功能,不做任何操作,直接返回成功
   if (!m_bTransaction)
   {
    return true;
   }

   try
   {
    m_sqlTransaction.Commit();
   }
   catch(SqlException e)
   {
    m_sqlTransaction.Rollback();
    throw e;
   }
   return true;
  }

  /// <summary>
  /// 除去对象资源.
  /// </summary>
  public void Dispose()
  {
   Dispose(true);
   GC.SuppressFinalize(true);
  }

  /// <summary>
  /// 释放对象实例变量.
  /// </summary>
  protected virtual void Dispose(
   bool bDispose)
  {
   if (!bDispose)
    return;
   
   if (m_bConnected)
   {
    if (m_sqlConnection.State != ConnectionState.Closed )
    {
     m_sqlConnection.Dispose();
     m_sqlConnection.Close();
     
     m_sqlCommand = null;
     m_sqlConnection = null;
     m_sqlTransaction = null;

     m_bConnected = false;
    }
   }
  }
 }
}

阅读更多
换一批

没有更多推荐了,返回首页