学步园

4 SQL数据类实现

1）IDAOBUILDER实现

using System;
using System.Data;
using System.Data.SqlClient;
using HKH.DataBase.Interface;
using HKH.DataBase.Type;
using HKH.DataBase.Config;

namespace HKH.DataBase.Sql
{
 /// <summary>
 /// SqlDataAccseeObjectBuilder 的摘要说明。
 /// </summary>
 /// <remarks>
 ///  Create By Liwt on 2006 - 06 - 16
 /// </remarks>
 internal class clsSqlDAOBuilder : IDAOBuilder
 {
  #region 变量

  //对CommonData的引用
  private DataSet myds = null;

  //连接
  private SqlConnection m_SqlConn = null;

  //事务
  private SqlTransaction m_SqlTrans = null;

  #endregion

  #region 构造

  public clsSqlDAOBuilder ( DataSet myds )
  {
   try
   {
    this.myds = myds;
    this.m_SqlConn = this.BuildConnection();
   }
   catch ( Exception ex)
   {
    throw ex;
   }
  }

  public clsSqlDAOBuilder ( DataSet myds ,IDbConnection Conn,IDbTransaction Trans )
  {
   if ( !(Conn is SqlConnection) || !(Trans is SqlTransaction) )
   {
    throw new Exception("传入的参数类型不匹配，应为System.Data.SqlClient.SqlConnection和System.Data.SqlClient.SqlTransaction类型");
   }

   this.myds = myds;
   this.m_SqlConn = (SqlConnection)Conn;
   this.m_SqlTrans = (SqlTransaction)Trans;
  }

  #region 以下构造，执行存储过程时使用

  /// <summary>
  /// 空构造，得连接时使用
  /// </summary>
  public clsSqlDAOBuilder ( )
  {
   try
   {
    this.m_SqlConn = this.BuildConnection();
   }
   catch(Exception ex)
   {
    throw ex;
   }
  }
  
  public clsSqlDAOBuilder ( IDbConnection Conn,IDbTransaction Trans )
  {
   if ( !(Conn is SqlConnection) || !(Trans is SqlTransaction) )
   {
    throw new Exception("传入的参数类型不匹配，应为System.Data.SqlClient.SqlConnection和System.Data.SqlClient.SqlTransaction类型");
   }

   this.m_SqlConn = (SqlConnection)Conn;
   this.m_SqlTrans = (SqlTransaction)Trans;
  }

  #endregion

  #endregion

  #region IDAOBuilder 成员

  /// <summary>
  /// 返回一个IDbConnection对象，在DF层启用连接和事务使用
  /// </summary>
  /// <returns>IDbConnection对象</returns>
  public IDbConnection GetConnection ( )
  {
   try
   {
    if ( null == m_SqlConn )
     m_SqlConn = BuildConnection();

    return m_SqlConn;
   }
   catch ( Exception ex)
   {
    throw ex;
   }
  }
  
  /// <summary>
  /// 返回一个IDbCommand对象
  /// </summary>
  /// <param name="strSql">查询语句,该值为空时，根据ds构造标准Command</param>
  /// <returns>IDbCommand对象</returns>
  public IDbCommand GetCommand (string strSql)
  {
   try
   {
    if ( null == strSql || "" == strSql )
    {
     return this.BuildCommand();
    }
    else
    {
     return this.BuildCommand(strSql);
    }
   }
   catch ( Exception ex )
   {
    throw ex;
   }
  }

  /// <summary>
  /// 返回一个IDbCommand对象，执行存储过程使用
  /// </summary>
  /// <returns>IDbCommand对象</returns>
  public IDbCommand GetSprocCommand ( string sprocName,ProcParameter[] parameters )
  {
   try
   {
    int flag = 0;
    SqlCommand SprocCommand = new SqlCommand();
    SprocCommand.Connection = m_SqlConn;

    if ( null != m_SqlTrans )
    {
     SprocCommand.Transaction = m_SqlTrans;
    }

    SprocCommand.CommandType = CommandType.StoredProcedure;
    SprocCommand.CommandText = sprocName;
    SprocCommand.Parameters.Clear();
 
    // 构造SqlCommand参数集合
    if ( null != parameters )
    {
     foreach (ProcParameter procParameter in parameters)
     {
      if (procParameter.ParameterName == null || procParameter.ParameterName.Trim() == "")
      {
       throw new Exception("存储过程参数名为空！");
      }

      #region 以传入存储过程参数，构造具体数据库存储过程参数

      SqlParameter parameter = new SqlParameter();

      parameter.ParameterName = procParameter.ParameterName;
      parameter.Value = procParameter.ParameterValue;
      parameter.SqlDbType = (SqlDbType)((int)procParameter.ParameterType);
      if ( procParameter.ParameterSize > 0 )
      {
       parameter.Size = procParameter.ParameterSize;
      }
      parameter.Direction = procParameter.ParameterDirec;

      #endregion

      SprocCommand.Parameters.Add( parameter );

      if ( ParameterDirection.ReturnValue == parameter.Direction )
      {
       flag = 1;
      }
     }
    }

    // 添加一个存储过程返回值参数
    if ( 0 == flag )
    {
     SprocCommand.Parameters.Add( new SqlParameter( "@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null ));
    }

    return SprocCommand;
   }
   catch ( Exception ex )
   {
    throw ex;
   }
  }

  /// <summary>
  /// 返回一个完整的IDbDataAdapter对象，和数据库发生插入、删除、更新时使用
  /// </summary>
  /// <returns>IDbDataAdapter对象</returns>
  public IDbDataAdapter GetDataAdapter ( )
  {
   try
   {
    SqlDataAdapter sqlAda = new SqlDataAdapter(this.BuildCommand());

#warning 此处是否还可优化
    SqlCommandBuilder sqlCommBuilder = new SqlCommandBuilder(sqlAda);

    return sqlAda;
   }
   catch ( Exception ex )
   {
    throw ex;
   }
  }

  /// <summary>
  /// 返回一个IDbDataAdapter对象，查询时使用，只含有一个SelectCommand对象
  /// </summary>
  /// <returns>IDbDataAdapter对象</returns>
  public IDbDataAdapter GetQueryDataAdapter ( )
  {
   try
   {
    SqlCommand sqlComm = new SqlCommand();
    sqlComm.Connection = m_SqlConn;

    if ( null != m_SqlTrans )
    {
     sqlComm.Transaction = m_SqlTrans;
    }

    return new SqlDataAdapter(sqlComm);
   }
   catch ( Exception ex )
   {
    throw ex;
   }
  }

  #endregion

  #region 私有方法

  /// <summary>
  /// 创建一个SqlConnection对象
  /// </summary>
  /// <returns></returns>
  private SqlConnection BuildConnection()
  {
   try
   {
    string strConn = clsDBAccessConfig.GetInstance().ConnType.ConnectionString;
   
    if ( null == strConn || "" == strConn.Trim() )
    {
     throw new Exception("数据库连接配置异常！");
    }

    return new SqlConnection(strConn);
   }
   catch ( Exception ex)
   {
    throw ex;
   }
  }

  /// <summary>
  /// 创建一个SqlCommand对象
  /// </summary>
  /// <returns></returns>
  private SqlCommand BuildCommand()
  {
   try
   {
    if ( null == myds )
    {
     throw new Exception("CommonData 为 null，无法构建Command");
    }

    SqlCommand sqlComm = new SqlCommand();
    sqlComm.Connection = m_SqlConn;

    if ( null != m_SqlTrans )
    {
     sqlComm.Transaction = m_SqlTrans;
    }

    System.Text.StringBuilder strSql = new System.Text.StringBuilder();
    strSql.Append("SELECT ");
   
    for ( int i = 0 ; i < myds.Tables[0].Columns.Count ; i ++ )
    {
     string colName = myds.Tables[0].Columns[i].ColumnName;
     strSql.Append(colName);
    
     if ( i < myds.Tables[0].Columns.Count - 1 )
     {
      strSql.Append(",");
     }
    }

    strSql.Append(" FROM ");
    strSql.Append(myds.Tables[0].TableName);

    sqlComm.CommandText = strSql.ToString();

    return sqlComm;
   }
   catch ( Exception ex )
   {
    throw ex;
   }
  }

  /// <summary>
  /// 创建一个SqlCommand对象
  /// </summary>
  /// <param name="strSql">CommandText的值</param>
  /// <returns></returns>
  private SqlCommand BuildCommand(string strSql)
  {
   try
   {
    SqlCommand sqlComm = new SqlCommand();
    sqlComm.Connection = m_SqlConn;

    if ( null != m_SqlTrans )
    {
     sqlComm.Transaction = m_SqlTrans;
    }

    sqlComm.CommandText = strSql;

    return sqlComm;
   }
   catch ( Exception ex )
   {
    throw ex;
   }
  }

  #endregion
 }
}

 

2）IDBACCESS实现

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using HKH.DataBase.Type;
using HKH.DataBase.Interface;

namespace HKH.DataBase.Sql
{
 /// <summary>
 /// SQL-SERVER 数据访问实现
 /// </summary>
 /// <remarks>
 ///  Create By Liwt on 2006 - 06 - 19
 /// </remarks>
 internal class clsSqlDBAccess : IDBAccess
 {
  #region 变量

  private SqlDataAdapter m_DataAdapter;

  private IDAOBuilder m_IDAOBuilder = null;

  #endregion

  #region 构造

  public clsSqlDBAccess(DataSet myds)
  {
   try
   {
    m_IDAOBuilder = new DataBase.Sql.clsSqlDAOBuilder(myds);
    this.m_DataAdapter = (SqlDataAdapter)m_IDAOBuilder.GetDataAdapter();
   }
   catch (Exception ex)
   {
    throw ex;
   }
  }

  public clsSqlDBAccess(DataSet myds,IDbConnection Conn,IDbTransaction Trans)
  {
   try
   {
    if ( !(Conn is SqlConnection) || !(Trans is SqlTransaction) )
    {
     throw new Exception("传入的参数类型不匹配，应为System.Data.SqlClient.SqlConnection和System.Data.SqlClient.SqlTransaction类型");
    }

    m_IDAOBuilder = new DataBase.Sql.clsSqlDAOBuilder(myds,Conn,Trans);
    this.m_DataAdapter = (SqlDataAdapter)m_IDAOBuilder.GetDataAdapter();
   }
   catch (Exception ex)
   {
    throw ex;
   }
  }

  #region 以下构造在执行存储过程时使用，因为没有myds，无法构造标准DataAdapter

  public clsSqlDBAccess()
  {
   try
   {
    m_IDAOBuilder = new DataBase.Sql.clsSqlDAOBuilder();
   }
   catch (Exception ex)
   {
    throw ex;
   }
  }

  public clsSqlDBAccess(IDbConnection Conn,IDbTransaction Trans)
  {
   try
   {
    if ( !(Conn is SqlConnection) || !(Trans is SqlTransaction) )
    {
     throw new Exception("传入的参数类型不匹配，应为System.Data.SqlClient.SqlConnection和System.Data.SqlClient.SqlTransaction类型");
    }

    m_IDAOBuilder = new DataBase.Sql.clsSqlDAOBuilder(Conn,Trans);
   }
   catch (Exception ex)
   {
    throw ex;
   }
  }

  #endregion

  ~clsSqlDBAccess()
  {
   if( null != m_DataAdapter )
    m_DataAdapter.Dispose();

   m_IDAOBuilder = null;
  }

  #endregion

  #region IDBAccess 成员

  /// <summary>
  /// 对通用的一个DataSet执行Insert的操作
  /// </summary>
  /// <param name="dsInsert">进行操作的DataSet</param>
  /// <param name="strTableName">TableName</param>
  /// <param name="iReturnKeyID">返回的增长主键</param>
  /// <returns>返回0表示成功，返回-1表示失败</returns>
  public int Insert(System.Data.DataSet dsInsert, string strTableName, out int iReturnKeyID)
  {
   int iColumnsName = dsInsert.Tables[strTableName].Columns.Count;
   int iEditColumnsNumber = 0;
   ArrayList m_ColumnNameArrayList = new ArrayList();
   string strGetAutoIncrementColumnName = null;
   string strGetColumnsName = null;
   bool bAutoIncrementFlag = false;
   try
   {
    //取出字段列表
    iEditColumnsNumber = iColumnsName;
    for (int ifor=0;ifor<iColumnsName;ifor++)
    {
     if (!dsInsert.Tables[strTableName].Columns[ifor].AutoIncrement)
     {
      m_ColumnNameArrayList.Add(dsInsert.Tables[strTableName].Columns[ifor].ColumnName.ToString());
     }
     else
     {
      strGetAutoIncrementColumnName = dsInsert.Tables[strTableName].Columns[ifor].ColumnName.ToString();
      iEditColumnsNumber = iColumnsName-1;
      bAutoIncrementFlag = true;
     }
    }
    //取出数据
    DataRow row = dsInsert.Tables[strTableName].Rows[0];
     
    //建立新结果体
    DataSet newdsInsert = dsInsert.Clone();
    DataRow newRow = newdsInsert.Tables[strTableName].NewRow();
    //填充数据
    for (int ifor=0;ifor<iEditColumnsNumber;ifor++)
    {
     strGetColumnsName = m_ColumnNameArrayList[ifor].ToString();
     if ( null != row[strGetColumnsName])
     {
      if ( 0 == strGetColumnsName.CompareTo("DeleteFlag"))
      {
       newRow[strGetColumnsName]=0;
      }
      else
      {
       newRow[strGetColumnsName] = row[strGetColumnsName];
      }
     }
     else
     {
      newRow[strGetColumnsName] = null;
     }
    }
    newdsInsert.Tables[strTableName].Rows.Add(newRow);
    //insert数据
    m_DataAdapter.Update(newdsInsert,strTableName);
    //返回必要的ID
    if (bAutoIncrementFlag)
    {
     iReturnKeyID = Convert.ToInt32(newdsInsert.Tables[strTableName].Rows[0][strGetAutoIncrementColumnName].ToString());
     return 0;
    }
    iReturnKeyID=-1;
    return 0;
   } 
   catch(Exception ex)
   {
    iReturnKeyID=-1;
    throw ex;
   }
  }

  /// <summary>
  /// 对通用的一个DataSet执行物理删除的操作。
  /// </summary>
  /// <param name="dsDelete">将要操作的DataSet</param>
  /// <param name="strTableName">Table名称</param>
  /// <returns>返回0表示成功，返回-1表示失败</returns>
  public int Delete(System.Data.DataSet dsDelete, string strTableName)
  {
   try
   {
    if ( dsDelete.Tables[strTableName].Rows.Count > 0 )
    {
     return m_DataAdapter.Update(dsDelete,strTableName);
    }
    
    return -1;
   }
   catch(Exception ex)
   {
    throw ex;
   }
  }

  /// <summary>
  /// 设置删除记录的标志
  /// </summary>
  /// <param name="dsDelete">进行操作的DataSet</param>
  /// <param name="strTableName">Table名称</param>
  /// <returns>返回0表示成功，返回-1表示失败</returns>
  public int DeleteFlag(System.Data.DataSet dsDelete, string strTableName)
  {
   try
   {
    if (dsDelete.Tables[strTableName].Rows.Count > 0)
    {
     return m_DataAdapter.Update(dsDelete,strTableName);
    }
    
    return -1;
   }
   catch(Exception ex)
   {
    throw ex;
   }
  }

  /// <summary>
  /// 更新DataSet对应的数据。
  /// </summary>
  /// <param name="dsUpdate">进行操作的DataSet</param>
  /// <param name="strTableName">Table名称</param>
  /// <returns>返回0表示成功，返回-1表示失败</returns>
  public int Update(System.Data.DataSet dsUpdate, string strTableName)
  {
   try
   {
    if (dsUpdate.Tables[strTableName].Rows.Count > 0)
    {
     return m_DataAdapter.Update(dsUpdate,strTableName);
    }

    return -1;
   }
   catch(Exception ex)
   {
    throw ex;
   }
  }

  /// <summary>
  /// 执行存储过程
  /// </summary>
  /// <param name="sprocName">存储过程名称</param>
  /// <param name="parameters">存储过程参数</param>
  /// <returns>返回0表示成功，返回-1表示失败</returns>
  public int ExecuteProcedure(string sprocName, ProcParameter[] parameters, System.Data.DataSet dataset, string strTableName)
  {
   SqlCommand SqlComm = null;
   SqlDataAdapter m_SqlData = null;
   try
   {
    int result;
    
    if ( null == strTableName || "" == strTableName.Trim() )
    {
     strTableName = "SourceTable";
    }

    SqlComm = (SqlCommand)this.m_IDAOBuilder.GetSprocCommand(sprocName, parameters);

    m_SqlData = new SqlDataAdapter( SqlComm );
    m_SqlData.Fill(dataset,strTableName);

    result=System.Convert.ToInt32(SqlComm.Parameters["@Return_Value"].Value);

    if (System.Data.ConnectionState.Open == SqlComm.Connection.State && null == SqlComm.Transaction )
    {
     SqlComm.Connection.Close ();
    }
    return result;
   }
   catch(Exception ex)
   {
    if (System.Data.ConnectionState.Open == SqlComm.Connection.State)
    {
     SqlComm.Connection.Close ();
    }
    throw ex;
   }
  }

  /// <summary>
  /// 执行存储过程
  /// </summary>
  /// <param name="sprocName">存储过程名称</param>
  /// <param name="parameters">存储过程参数</param>
  /// <param name="dataset">执行的DataSet</param>
  /// <param name="strTableName">数据表名称</param>
  /// <returns>返回0表示成功，返回-1表示失败</returns>
  public int ExecuteProcedure(string sprocName, ProcParameter[] parameters)
  {
   SqlCommand SqlComm = null;

   try
   {
    int result;
    int ResultRow;
    
    ResultRow =0;

    SqlComm = (SqlCommand)this.m_IDAOBuilder.GetSprocCommand(sprocName, parameters);
 
    if (System.Data.ConnectionState.Closed == SqlComm.Connection.State)
    {
     SqlComm.Connection.Open();
    }
    
    ResultRow = SqlComm.ExecuteNonQuery();

    result = Convert.ToInt32(SqlComm.Parameters["@Return_Value"].Value);

    if (System.Data.ConnectionState.Open == SqlComm.Connection.State && null == SqlComm.Transaction )
    {
     SqlComm.Connection.Close ();
    }

    return result;
   }
   catch(Exception ex)
   {
    if (System.Data.ConnectionState.Open == SqlComm.Connection.State)
    {
     SqlComm.Connection.Close ();
    }
    throw ex;
   }
  }

  /// <summary>
  /// 执行存储过程
  /// </summary>
  /// <param name="sprocName">存储过程名称</param>
  /// <param name="parameters">存储过程参数</param>
  /// <param name="dr">执行的SqlDataReader</param>
  /// <param name="TableName">数据表名称</param>
  /// <returns>0：成功</returns>
  /// <returns>！0：失败</returns>
  public int ExecuteProcedure(string sprocName, ProcParameter[] parameters, System.Data.IDataReader dr)
  {
   if ( !(dr is SqlDataReader) )
   {
    throw new Exception("传入的DataReader应为SqlDataReader类型");
   }

   SqlCommand SqlComm = null;
   int result;
   
   SqlComm = (SqlCommand)this.m_IDAOBuilder.GetSprocCommand(sprocName, parameters);
   try
   {
    if (System.Data.ConnectionState.Closed == SqlComm.Connection.State)
    {
     SqlComm.Connection.Open();
    }

    dr = SqlComm.ExecuteReader(CommandBehavior.CloseConnection);
    result=System.Convert.ToInt32(SqlComm.Parameters["@Return_Value"].Value);

    if (System.Data.ConnectionState.Open == SqlComm.Connection.State)
    {
     SqlComm.Connection.Close ();
    }
    return result;
   }
   catch(Exception ex)
   {
    if (System.Data.ConnectionState.Open == SqlComm.Connection.State)
    {
     SqlComm.Connection.Close ();
    }
    throw ex;
   }
  }

  /// <summary>
  /// 执行查询语句
  /// </summary>
  /// <param name="strSql">要执行的SQL语句</param>
  /// <param name="dsSelect">要填充的DataSet，以引用方式传入</param>
  /// <param name="strTableName">要填充的表名</param>
  /// <returns></returns>
  public bool Fill(string strSql,DataSet dsSelect,string strTableName)
  {
   try
   {
    SqlDataAdapter m_SqlDataAdapter = (SqlDataAdapter)this.m_IDAOBuilder.GetQueryDataAdapter();
    m_SqlDataAdapter.SelectCommand.CommandText = strSql;

    m_SqlDataAdapter.Fill(dsSelect,strTableName);

    return true;
   }
   catch(Exception ex)
   {
    throw ex;
   }
  }

  /// <summary>
  /// 执行单值查询
  /// </summary>
  /// <param name="strSql">要执行的Sql语句</param>
  /// <returns>失败返回null</returns>
  public object ExecuteScalar(string strSql)
  {
   SqlCommand m_Comm = null;
   try
   {
    m_Comm = (SqlCommand)this.m_IDAOBuilder.GetCommand(strSql);

    object objTemp = null;
    bool isClose = false;

    if (System.Data.ConnectionState.Closed == m_Comm.Connection.State)
    {
     m_Comm.Connection.Open();
     isClose = true;
    }

    objTemp = m_Comm.ExecuteScalar();

    if (isClose)
    {
     m_Comm.Connection.Close();
    }

    return objTemp;
   }
   catch(Exception ex)
   {
    if (System.Data.ConnectionState.Open == m_Comm.Connection.State)
    {
     m_Comm.Connection.Close ();
    }
    throw ex;
   }
  }

  /// <summary>
  /// 执行DataReader
  /// </summary>
  /// <param name="strSql">要执行的Sql语句</param>
  /// <returns>失败返回null</returns>
  public IDataReader ExecuteReader(string strSql)
  {
   SqlDataReader dr = null ;
   SqlCommand m_Comm = null;
   try
   {
    m_Comm = (SqlCommand)this.m_IDAOBuilder.GetCommand(strSql);

    if (System.Data.ConnectionState.Closed == m_Comm.Connection.State)
    {
     m_Comm.Connection.Open();
    }

    dr = m_Comm.ExecuteReader(CommandBehavior.CloseConnection);

    return dr;
   }
   catch(Exception ex)
   {
    throw ex;
   }
  }

  /// <summary>
  /// 执行SQL语句
  /// </summary>
  /// <param name="strSql">要执行的Sql语句</param>
  /// <returns>失败返回-1</returns>
  public int ExecuteNonQuery(string strSql)
  {
   SqlCommand m_Comm = null;
   try
   {
    m_Comm = (SqlCommand)this.m_IDAOBuilder.GetCommand(strSql);

    int iResult = 0;
    bool isClose = false;

    if (System.Data.ConnectionState.Closed == m_Comm.Connection.State)
    {
     m_Comm.Connection.Open();
     isClose = true;
    }

    iResult = m_Comm.ExecuteNonQuery();

    if (isClose)
    {
     m_Comm.Connection.Close();
    }

    return iResult;
   }
   catch(Exception ex)
   {
    if (System.Data.ConnectionState.Open == m_Comm.Connection.State)
    {
     m_Comm.Connection.Close ();
    }
    throw ex;
   }
  }

  #endregion
 }
}