学步园

using System;
using System.Data;
using System.Collections;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace Government.Common
{
 ///

 /// CDataBase 的摘要说明。
 ///

 public class CDataBase
 {
  public CDataBase()
  {
  }

  
  ///

  /// 取单值
  ///

  /// SQL 命令
  /// 结果
  public object GetSingleValue( string strSQL )
  {
   OleDbConnection conn = null;
   OleDbCommand cmd = null;
   
   try
   {
    conn = new OleDbConnection(GV.s_strConnection);
    conn.Open();

    cmd = conn.CreateCommand();
    cmd.CommandText = strSQL;
    cmd.CommandType = CommandType.Text;
    cmd.CommandTimeout = 5;

    return cmd.ExecuteScalar();
   }
   catch(Exception e)
   {
                GV.Assert( false
     , "CDataBase::GetSingleValue"
     , strSQL + "/r/n" + e.Message
     );

    cmd.Cancel();
    return "";
   }
   finally
   {
    if( cmd != null )
    {
     cmd.Dispose();
    }
        
    if( conn != null )
    {    
     if( conn.State != ConnectionState.Closed )
     {
      conn.Close();
     }
     conn.Dispose();
    }
   }
  }// GetSingleValue

  ///

  /// 判断结果是否为空
  ///

  /// SQL 命令
  /// 是否成功
  public bool IsNull( string strSQL )
  {
   return ( GetSingleValue(strSQL) == null );
  }// IsNull

  ///

  /// 执行单条SQL命令
  ///

  /// SQL 命令
  /// 是否成功
  public bool ExecuteSQL(string strSQL)
  {
   OleDbConnection conn = null;
   OleDbCommand cmd = null;

      
   try
   {   
    conn = new OleDbConnection(GV.s_strConnection);
    conn.Open();

    cmd = conn.CreateCommand();
    cmd.CommandText = strSQL;
    cmd.CommandType = CommandType.Text;
    cmd.CommandTimeout = 5;
    cmd.ExecuteNonQuery();

    return true;
   }
   catch(Exception e)
   {
    GV.Assert( false
     , "CDataBase::ExecuteSQL"
     , strSQL + "/r/n" + e.Message
     );

                cmd.Cancel();
    return false;
   }
   finally
   {
    if( cmd != null )
    {
     cmd.Dispose();
    }
        
    if( conn != null )
    {    
     if( conn.State != ConnectionState.Closed )
     {
      conn.Close();
     }
     conn.Dispose();
    }
   }
  }// ExecuteSQL

  ///

  /// 在一个事务中依次执行多条SQL
  ///

  /// SQL 命令组
  /// 是否成功
  public bool ExecuteMultiSQL(ref ArrayList arySQL )
  {
   OleDbConnection conn = null;
   OleDbCommand cmd = null;
   OleDbTransaction tran = null;
   int index = 0;

   try
   {
    conn = new OleDbConnection(GV.s_strConnection);
    conn.Open();

    tran = conn.BeginTransaction();
    cmd = conn.CreateCommand();
    cmd.Transaction = tran;
    cmd.CommandType = CommandType.Text;
    cmd.CommandTimeout = 5;

    for( index = 0; index     {
     cmd.CommandText = arySQL[index].ToString();
     cmd.ExecuteNonQuery();
    }

    tran.Commit();
    return true;
   }
   catch(Exception e)
   {
    GV.Assert( false
     , "CDataBase::ExecuteMultiSQL"
     , arySQL[index].ToString() + "/r/n" + e.Message
     );

    cmd.Cancel();
    tran.Rollback();
    return false;
   }
   finally
   {
    if( cmd != null )
    {
     cmd.Dispose();
    }
        
    if( conn != null )
    {    
     if( conn.State != ConnectionState.Closed )
     {
      conn.Close();
     }
     conn.Dispose();
    }
   }
  }// ExecuteMultiSQL

  ///

  /// 获取查询视图（不可更新）
  ///

  /// SQL命令
  /// 获取DataSet视图
  public DataSet GetView(string strSQL)
  {
   OleDbConnection conn = null;
   OleDbDataAdapter adapter = null;
   DataSet ds = null;

   try
   {
    conn = new OleDbConnection(GV.s_strConnection);
    conn.Open();

    ds = new DataSet();
    adapter = new OleDbDataAdapter( strSQL, conn);
    adapter.Fill(ds);

    return ds;
   }
   catch(Exception e)
   {
    GV.Assert( false
     , "CDataBase::GetView"
     , strSQL.ToString() + "/r/n" + e.Message
     );

    if( ds != null )
     ds.Dispose();
    return null;
   }
   finally
   {
    if( adapter != null )
    {
     adapter.Dispose();
    }

    if( conn != null )
    {    
     if( conn.State != ConnectionState.Closed )
     {
      conn.Close();
     }
     conn.Dispose();
    }
   }
  }// GetView

  ///

  /// 获取SQL Server存储过程的记录集
  ///

  public DataSet GetProcView( string strSQL)
  {
   SqlConnection conn = null;
   SqlCommand cmd = null;
   SqlDataAdapter adapter = null;
   DataSet ds = null;

   try
   {
    conn = new SqlConnection(GV.s_strSqlConnection);
    cmd = new SqlCommand( strSQL, conn);
    conn.Open();

    ds = new DataSet();
    adapter = new SqlDataAdapter(cmd);
    adapter.Fill(ds);

    return ds;
   }
   catch(Exception e)
   {
    GV.Assert( false
     , "CDataBase::GetProcView"
     , strSQL.ToString() + "/r/n" + e.Message
     );

    if( ds != null )
     ds.Dispose();
    return null;
   }
   finally
   {
    if( adapter != null )
    {
     adapter.Dispose();
    }

    if( cmd != null )
    {
     cmd.Dispose();
    }

    if( conn != null )
    {    
     if( conn.State != ConnectionState.Closed )
     {
      conn.Close();
     }
     conn.Dispose();
    }
   }

  }

  ///

  /// 获取查询记录集（可以更新）
  ///

  /// SQL命令
  /// 记录集
  public CRecordSet GetRecordSet( string strSQL )
  {
   OleDbConnection conn = null;

   try
   {
    conn = new OleDbConnection(GV.s_strConnection);
    
    return ( new CRecordSet( ref conn, strSQL) );
   }
   catch( Exception e )
   {
    GV.Assert( false
     , "CDataBase::GetRecordSet"
     , strSQL + "/r/n" + e.Message);

    if( conn != null )
    {    
     if( conn.State != ConnectionState.Closed )
     {
      conn.Close();
     }
     conn.Dispose();
    }

    return null;
   }
  }// GetRecordSet end

 }// CDataBase end
}

using System;
using System.Data;
using System.Data.OleDb;
using  System.Diagnostics;

namespace Government.Common
{
 ///

 /// RecordSet 用于模拟实现ADO的使用
 ///

 public class CRecordSet
 {

  // Members
  OleDbDataAdapter m_Adapter = null;
  OleDbCommandBuilder m_CommandBuilder = null;
  DataSet    m_DataSet = null;
  Int32    m_iCurPtr = 0;
  

  ///

  /// 构造函数
  ///

  /// 连接对象
  /// 表名
  /// WHERE 子句
  public CRecordSet( ref OleDbConnection objConn
        ,string strSQL)
  {
   m_Adapter = new OleDbDataAdapter( strSQL, objConn);
   m_CommandBuilder = new OleDbCommandBuilder(m_Adapter);

   m_DataSet = new DataSet();
   m_Adapter.Fill( m_DataSet );
   this.MoveFirst();
  }// CRecordSet end

  // 获取记录集信息
  //////////////////////////////////////////////////////////////////////////////////////////
  
  public int GetRecordCount()
  {
   if( m_DataSet == null )
    return 0;

   return m_DataSet.Tables[0].Rows.Count;
  }

  public bool IsNull()
  {
   return ( this.GetRecordCount() == 0 );
  }

  public bool IsBOF()
  {
   return ( this.IsNull() || m_iCurPtr == 0 );
  }

  public bool IsEOF()
  {
   return ( this.IsNull() || m_iCurPtr >= this.GetRecordCount() );
  }

  public int GetFieldCount()
  {
   if( m_DataSet != null )
   {
    return m_DataSet.Tables[0].Columns.Count;
   }
   return 0;
  }

  public string GetFieldName( int nColumn )
  {
   GV.Assert( ( nColumn >= 0) && (nColumn     ,  "CRecordSet::GetFieldName"
    , "索引越界!");

   if( m_DataSet != null &&
     nColumn >= 0 &&
    nColumn    {
    return m_DataSet.Tables[0].Columns[nColumn].ColumnName;
   }

   return null;
  }

  public Type GetFieldType( int nColumn )
  {
   GV.Assert( ( nColumn >= 0) && (nColumn     ,  "CRecordSet::GetFieldType"
    , "索引越界!");

   if( m_DataSet != null &&
     nColumn >= 0 &&
    nColumn

   {
    return m_DataSet.Tables[0].Columns[nColumn].DataType;
   }

   return null;
  }

  // 移动光标
  //////////////////////////////////////////////////////////////////////////////////////////

  public bool MoveFirst()
  {
   m_iCurPtr = 0;
   return true;
  }

  public bool MoveLast()
  {
   if( !this.IsNull() )
   {
    m_iCurPtr = this.GetRecordCount() - 1;
    return true;
   }
   
   return false;      
  }

  public bool MovePre()
  {
   GV.Assert( !this.IsBOF()
    , "CRecordSet::MovePre"
    , "已经到头 !");

   if( !this.IsBOF() )
   {
    m_iCurPtr --;
    return true;
   }

   return false;
  }

  public bool MoveNext()
  {
   GV.Assert( !this.IsEOF()
    , "CRecordSet::MoveNext"
    , "已经到头 !");

   if( !this.IsEOF() )
   {
    m_iCurPtr ++;
    return true;
   }

   return false;
  }

  // 取值
  //////////////////////////////////////////////////////////////////////////////////////////  

  public string GetCollect( int nColumn )
  {
   GV.Assert( ( nColumn >= 0) && (nColumn     ,  "CRecordSet::GetCollect"
    , "索引越界!");

   if( this.IsNull() )
    return null;

   try
   {
    if(  nColumn >= 0 &&
     nColumn     {
     return m_DataSet.Tables[0].Rows[m_iCurPtr][nColumn].ToString();
    }
   }
   catch( Exception e )
   {
    GV.Assert( false, "CRecordSet::GetCollect", e.Message);
   }
   return null;   
  }

  public string GetCollect( string strField )
  {
   GV.Assert( strField.Length > 0
    ,  "CRecordSet::GetCollect"
    , "字段名为空!");

   if( this.IsNull() )
    return null;

   try
   {
    if( strField.Length > 0 )
    {
     return m_DataSet.Tables[0].Rows[m_iCurPtr][strField].ToString();
    }
   }
   catch( Exception e )
   {
    GV.Assert( false, "CRecordSet::GetCollect", e.Message);
   }
   return null; 
  }

  // 赋值
  //////////////////////////////////////////////////////////////////////////////////////////
  
  public bool PutCollect( int nColumn, string strValue)
  {
   GV.Assert( ( nColumn >= 0) && (nColumn     ,  "CRecordSet::PutCollect"
    , "索引越界!");

   if( this.IsNull() )
    return false;

   try
   {
    if(  nColumn >= 0 &&
     nColumn     {
     if( strValue.Length == 0 )
      m_DataSet.Tables[0].Rows[m_iCurPtr][nColumn] = DBNull.Value;
     else
      m_DataSet.Tables[0].Rows[m_iCurPtr][nColumn] = strValue;

     return true;
    }
   }
   catch( Exception e )
   {
    GV.Assert( false, "CRecordSet::PutCollect", e.Message);
   }
   
   return false;
  }

  public bool PutCollect( string strField, string strValue)
  {
   GV.Assert( strField.Length > 0
    ,  "CRecordSet::PutCollect"
    , "字段名为空!");

   if( this.IsNull() )
    return false;

   try
   {
    if( strValue.Length == 0 )
                    m_DataSet.Tables[0].Rows[m_iCurPtr][strField] = DBNull.Value;
    else
     m_DataSet.Tables[0].Rows[m_iCurPtr][strField] = strValue;

    return true;
   }
   catch(Exception e)
   {
    GV.Assert( false, "CRecordSet::PutCollect", e.Message);
    return false;
   }   
  }

  // 动作
  //////////////////////////////////////////////////////////////////////////////////////////

  // 添加
  public bool AddNew()
  {
   DataRow dr = m_DataSet.Tables[0].NewRow();
   m_DataSet.Tables[0].Rows.Add(dr);
   m_iCurPtr = this.GetRecordCount() - 1;
   return true;
  }

  // 删除
  public bool Delete()
  {
   if( this.IsNull() )
    return false;

   m_DataSet.Tables[0].Rows[m_iCurPtr].Delete();

   if( m_iCurPtr > this.GetRecordCount() - 1 )
   {
    m_iCurPtr = this.GetRecordCount() - 1;
   }
   return true;
  }

  // 更新数据
  public bool Update()
  {
   try
   {
    m_Adapter.Update( m_DataSet.GetChanges() );
    return true;
   }
   catch(Exception e)
   {
    m_DataSet.RejectChanges();
    GV.Assert( false, "CRecordSet::Update", e.Message);
    return false;
   }
  }

  // 取消更新
  public bool CancelUpdate()
  {
   m_DataSet.RejectChanges();
   return true;
  }

  // 关闭
  public bool Close()
  {
   m_Adapter.Dispose();
   m_CommandBuilder.Dispose();
   m_DataSet.Dispose();
   return true;
  }
 }
}