编程之道

无论是批评的，赞扬的，指着的都请EMAIL给我，你的建议将是我前进的动力! 联系我

转一篇封装ADO的代码

using  System;
 using  System.Collections;
 using  System.Collections.Specialized;
 using  System.Runtime.Remoting.Messaging;
 using  System.Data;
 using  System.Data.SqlClient;
 using  System.Configuration;
 namespace  LTP.SQLServerDAL
{
  ///   <summary>
  ///  ADO.NET数据库操作基础类。
  ///   </summary>
  public   abstract   class  DbManagerSQL
 {
   // 数据库连接字符串
   protected   static   string  connectionString  =  ConfigurationSettings.AppSettings[ " ConnectionString " ]; 
   public  DbManagerSQL()
  {
    //
    //  TODO: 在此处添加构造函数逻辑
    //
   }
   ///   <summary>
   ///  执行SQL语句，返回影响的记录数
   ///   </summary>
   ///   <param name="SQLString"></param>
   ///   <returns></returns>
   public   static   int  ExecuteSql( string  SQLString)
  {
    using  (SqlConnection connection  =   new  SqlConnection(connectionString))
   {    
     using  (SqlCommand cmd  =   new  SqlCommand(SQLString,connection))
    {
      try
     {  
      connection.Open();
       int  rows = cmd.ExecuteNonQuery();
       return  rows;
     }
      catch (System.Data.SqlClient.SqlException E)
     {    
       throw   new  Exception(E.Message);
     }
    }    
   }
  }
   ///   <summary>
   ///  执行两条SQL语句，实现数据库事务。
   ///   </summary>
   ///   <param name="SQLString1"></param>
   ///   <param name="SQLString2"></param>
   public   static   void  ExecuteSqlTran( string  SQLString1, string  SQLString2)
  {
    using  (SqlConnection connection  =   new  SqlConnection(connectionString))
   {
    connection.Open();
    SqlCommand cmd  =   new  SqlCommand();
    cmd.Connection = connection;    
    SqlTransaction tx = connection.BeginTransaction();   
    cmd.Transaction = tx;    
     try
    {     
     cmd.CommandText = SQLString1;
     cmd.ExecuteNonQuery();
     cmd.CommandText = SQLString2;
     cmd.ExecuteNonQuery();     
     tx.Commit();     
    }
     catch (System.Data.SqlClient.SqlException E)
    {  
     tx.Rollback();
      throw   new  Exception(E.Message);
    }
     finally
    {
     cmd.Dispose();
     connection.Close();
    } 
   }
  } 
   ///   <summary>
   ///  执行多条SQL语句，实现数据库事务，每条语句以“;”分割。
   ///   </summary>
   ///   <param name="SQLStringList"></param>
   public   static   void  ExecuteSqlTran( string  SQLStringList)
  {
    using  (OdbcConnection conn  =   new  OdbcConnection(connectionString))
   {
    conn.Open();
    OdbcCommand cmd  =   new  OdbcCommand();
    cmd.Connection = conn;    
    OdbcTransaction tx = conn.BeginTransaction();   
    cmd.Transaction = tx;    
     try
    {   
      string  [] split =  SQLStringList.Split( new  Char [] {  ' ; ' });
      foreach  ( string  strsql  in  split) 
     {
       if  (strsql.Trim() != "" )
      {
       cmd.CommandText = strsql;
       cmd.ExecuteNonQuery();
      }
     }       
     tx.Commit();     
    }
     catch (System.Data.Odbc.OdbcException E)
    {  
     tx.Rollback();
      throw   new  Exception(E.Message);
    }
   }
  }
   ///   <summary>
   ///  执行带一个存储过程参数的的SQL语句。
   ///   </summary>
   ///   <param name="SQLString"></param>
   ///   <param name="content"></param>
   ///   <returns></returns>
   public   static   int  ExecuteSql( string  SQLString, string  content)
  {    
    using  (SqlConnection connection  =   new  SqlConnection(connectionString))
   {
    SqlCommand cmd  =   new  SqlCommand(SQLString,connection);  
    System.Data.SqlClient.SqlParameter  myParameter  =   new  System.Data.SqlClient.SqlParameter (  " @content " , SqlDbType.NText);
    myParameter.Value  =  content ;
    cmd.Parameters.Add(myParameter);
     try
    {
     connection.Open();
      int  rows = cmd.ExecuteNonQuery();
      return  rows;
    }
     catch (System.Data.SqlClient.SqlException E)
    {    
      throw   new  Exception(E.Message);
    }
     finally
    {
     cmd.Dispose();
     connection.Close();
    } 
   }
  }  
   ///   <summary>
   ///  向数据库里插入图像格式的字段
   ///   </summary>
   ///   <param name="strSQL"></param>
   ///   <param name="fs"></param>
   ///   <returns></returns>
   public   static   int  ExecuteSqlInsertImg( string  strSQL, byte [] fs)
  {  
    using  (SqlConnection connection  =   new  SqlConnection(connectionString))
   {
    SqlCommand cmd  =   new  SqlCommand(strSQL,connection); 
    System.Data.SqlClient.SqlParameter  myParameter  =   new  System.Data.SqlClient.SqlParameter (  " @fs " , SqlDbType.Image);
    myParameter.Value  =  fs ;
    cmd.Parameters.Add(myParameter);
     try
    {
     connection.Open();
      int  rows = cmd.ExecuteNonQuery();
      return  rows;
    }
     catch (System.Data.SqlClient.SqlException E)
    {    
      throw   new  Exception(E.Message);
    }
     finally
    {
     cmd.Dispose();
     connection.Close();
    } 
    
   }
  }
   ///   <summary>
   ///  执行一条计算查询结果语句，返回查询结果（整数）。
   ///   </summary>
   ///   <param name="strSQL"></param>
   ///   <returns></returns>
   public   static   int  GetCount( string  strSQL)
  {
    using  (SqlConnection connection  =   new  SqlConnection(connectionString))
   {
    SqlCommand cmd  =   new  SqlCommand(strSQL,connection);    
     try
    {
     connection.Open();
     SqlDataReader result  =  cmd.ExecuteReader();
      int  i = 0 ;
      while (result.Read())
     {
      i = result.GetInt32( 0 );
     }
     result.Close();    
      return  i;
    }
     catch (System.Data.SqlClient.SqlException e)
    {        
      throw   new  Exception(e.Message);
    } 
     finally
    {
     cmd.Dispose();
     connection.Close();
    }
   }
  } 
   ///   <summary>
   ///  执行一条计算查询结果语句，返回查询结果（object）。
   ///   </summary>
   ///   <param name="SQLString"></param>
   ///   <returns></returns>
   public   static   object  GetSingle( string  SQLString)
  {
    using  (SqlConnection connection  =   new  SqlConnection(connectionString))
   {
    SqlCommand cmd  =   new  SqlCommand(SQLString,connection);  
     try
    {
     connection.Open();
      object  obj  =  cmd.ExecuteScalar();
      if ((Object.Equals(obj, null )) || (Object.Equals(obj,System.DBNull.Value)))
     {     
       return   null ;
     }
      else
     {
       return  obj;
     }    
    }
     catch (System.Data.SqlClient.SqlException e)
    {    
      throw   new  Exception(e.Message);
    }
     finally
    {
     cmd.Dispose();
     connection.Close();
    }
   }
  }
   ///   <summary>
   ///  执行查询语句，返回SqlDataReader
   ///   </summary>
   ///   <param name="strSQL"></param>
   ///   <returns></returns>
   public   static  SqlDataReader ExecuteReader( string  strSQL)
  {
    using  (SqlConnection connection  =   new  SqlConnection(connectionString))
   {
    SqlCommand cmd  =   new  SqlCommand(strSQL,connection); 
    SqlDataReader myReader;   
     try
    {
     connection.Open(); 
     myReader  =  cmd.ExecuteReader();
      return  myReader;
    }
     catch (System.Data.SqlClient.SqlException e)
    {        
      throw   new  Exception(e.Message);
    }  
     finally
    {
     cmd.Dispose();
     connection.Close();
    } 
   }
  }  
   ///   <summary>
   ///  执行查询语句，返回DataSet
   ///   </summary>
   ///   <param name="SQLString"></param>
   ///   <returns></returns>
   public   static  DataSet Query( string  SQLString)
  {
    using  (SqlConnection connection  =   new  SqlConnection(connectionString))
   {
    DataSet ds  =   new  DataSet();
     try
    {
     connection.Open();
     SqlDataAdapter command  =   new  SqlDataAdapter(SQLString,connection);    
     command.Fill(ds, " ds " );
    }
     catch (System.Data.SqlClient.SqlException ex)
    {    
      throw   new  Exception(ex.Message);
    }   
     return  ds;
   }
   
  }


   #region  存储过程操作

   ///   <summary>
   ///  运行存储过程
   ///   </summary>
   ///   <param name="storedProcName"></param>
   ///   <param name="parameters"></param>
   ///   <returns></returns>
   public   static  SqlDataReader RunProcedure( string  storedProcName, IDataParameter[] parameters )
  {
    using  (SqlConnection connection  =   new  SqlConnection(connectionString))
   {
    SqlDataReader returnReader;
    connection.Open();
    SqlCommand command  =  BuildQueryCommand( connection,storedProcName, parameters );
    command.CommandType  =  CommandType.StoredProcedure;

    returnReader  =  command.ExecuteReader();
     // Connection.Close();
     return  returnReader;
   }
  }
   private   static  SqlCommand BuildQueryCommand(SqlConnection connection, string  storedProcName, IDataParameter[] parameters)
  {
   
    SqlCommand command  =   new  SqlCommand( storedProcName, connection );
    command.CommandType  =  CommandType.StoredProcedure;
     foreach  (SqlParameter parameter  in  parameters)
    {
     command.Parameters.Add( parameter );
    }
     return  command;
   
  }  
   public   static  DataSet RunProcedure( string  storedProcName, IDataParameter[] parameters,  string  tableName )
  {
    using  (SqlConnection connection  =   new  SqlConnection(connectionString))
   {
    DataSet dataSet  =   new  DataSet();
    connection.Open();
    SqlDataAdapter sqlDA  =   new  SqlDataAdapter();
    sqlDA.SelectCommand  =  BuildQueryCommand(connection, storedProcName, parameters );
    sqlDA.Fill( dataSet, tableName );
    connection.Close();

     return  dataSet;
   }
  }

   #endregion  
 }
}

 --------------------------------------------------------------------------------------------------------------------------------

// 这是我更新的一个版本，与上面可以说变化相当大

 // 其中FlashGateway是Flash Remoting的一个dll大家可以去掉相关代码，不影响使用

using  System;
 using  System.Collections.Generic;
 using  System.Text;
 using  System.Collections;
 using  System.Collections.Specialized;
 using  System.Data;
 using  System.Data.SqlClient;
 using  System.Drawing;
 using  mvi.sysmanage;
 using  FlashGateway.IO;

 namespace  mvi.dbaccess
{
     #region     class sqlcom
     ///   <summary>
     ///  sqlcom
     ///   </summary>  
     public   class  sqlCom
    {
         // FLASHSRV\HIPIHI
         // private string DBCnStr = @"Data Source=FLASHSRV;Initial Catalog=hipihi;Persist Security Info=True;User ID=sa;password=1";

        private   string  DBCnStr  =   @" Data Source=mvi-dpe;Initial Catalog=hipihi;Persist Security Info=True;User ID=sa;password=mvi " ;

         public  sqlCom()
        {
             // DBCnStr = @"Data Source=FLASHSRV;Initial Catalog=hipihi;Persist Security Info=True;User ID=sa;password=1";
            DBCnStr  =   @" Data Source=mvi-dpe;Initial Catalog=hipihi;Persist Security Info=True;User ID=sa;password=mvi " ;
        }

         public  sqlCom( string  connectstring)
        {
             if  (connectstring.Length  >   1 )
            {
                DBCnStr  =  connectstring;
            }
        }

         public   string  SQLCnStr
        {
             get
            {
                 return  DBCnStr;
            }
             set
            {
                DBCnStr  =  value;
            }
        }

         #region  InitSqlConnection    初始化Sql连接字符串
         ///   <summary>
         ///  初始化Sql连接字符串
         ///   </summary>
         ///   <param name="DBCnStr"> 传入的dbconnection </param>
         ///   <param name="cmdText"> 传入的cmd text </param>
         ///   <returns> sql server connection string </returns>
         private   string  InitSqlConnection( string  DBCnStr,  string  cmdText)
        {
             //  temp code
             return  DBCnStr;

             /// / final code
             // int iD = DBCnStr.IndexOf("Data Source=");
             // int iL = DBCnStr.Substring(iD + 12).Split(';')[0].Length;
             // string strSqlServerName = DBCnStr.Substring(iD + 12, iL);
             // string strNewSqlServerName = GetSqlServerName(cmdText);
             // return DBCnStr.Replace(strSqlServerName, strNewSqlServerName);
        }
         #endregion

         #region  GetSqlServerName    由sql string 获取数据库服务器名
         ///   <summary>
         ///  由sql string 获取sql server name
         ///   </summary>
         ///   <param name="cmdText"> 传入的cmd text </param>
         ///   <returns> sql server name </returns>
         private   string  GetSqlServerName( string  cmdText)
        {
             return  cmdText.Substring(cmdText.IndexOf( " from " )  +   5 ).Split( ' . ' )[ 0 ].ToString();
        }
         #endregion

        # region GetDataSet   通过执行SQL语句返回一个状态
         ///   <summary>
         ///  通过执行SQL语句返回一个状态
         ///   </summary>
         ///   <param name="cmdText"> “SQL 文本” </param>
         ///   <param name="oCn"> "连接对象" </param>
         ///   <param name="oDs"> "引用的DataSet它将在程序中改变内容" </param>
         ///   <returns> "成功则返回0,否则返回错误代码" </returns>
         public   int  GetDataSet( string  cmdText,  ref  DataSet oDs)
        {
            DBCnStr  =   this .InitSqlConnection(DBCnStr, cmdText);
            SqlConnection oCn  =   new  SqlConnection(DBCnStr);

             try
            {
                oCn.Open();
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);


                 return   - 1 ;
            }

             // 定义数据适配对象
            SqlDataAdapter oleDataAdapter  =   new  SqlDataAdapter(cmdText, oCn);

             int  status  =   - 1 ;
             try
            {
                 // 填充DataSet
                oleDataAdapter.Fill(oDs);
                status  =   0 ;
            }
             catch  (Exception oErr)
            {
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
                status  =   - 1 ;
            }
             finally
            {
                oleDataAdapter  =   null ;
                 if  (oCn.State  ==  System.Data.ConnectionState.Open)
                {
                    oCn.Close();
                }
            }
             return  status;
        }
         #endregion

        # region GetDataTable   执行SQL语句并返回一个表
         ///   <summary>
         ///  执行SQL语句并返回一个表
         ///   </summary>
         ///   <param name="cmdText"> SQL文本 </param>
         ///   <param name="DBCnStr"> dbconnect </param>
         ///   <param name="inDt"> 返回表 </param>
         ///   <returns> 成功则返回0,否则返回错误代码 </returns>
         public   int  GetDataTable( string  cmdText,  ref  DataTable inDt)
        {
            DBCnStr  =   this .InitSqlConnection(DBCnStr, cmdText);
            SqlConnection oCn  =   new  SqlConnection(DBCnStr);

             try
            {
                oCn.Open();
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                 return   - 1 ;
            }

             // 建立数据适配对象
            SqlDataAdapter oleDataAdapter  =   new  SqlDataAdapter(cmdText, oCn);

             int  status  =   - 1 ;
             try
            {
                 // 填充数据表
                oleDataAdapter.Fill(inDt);
                status  =   0 ;
            }
             catch  (Exception oErr)
            {
                 // 异常处理
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                status  =   - 1 ;
            }
             finally
            {
                oleDataAdapter  =   null ;
                 if  (oCn.State  ==  System.Data.ConnectionState.Open)
                {
                    oCn.Close();
                }
            }
             return  status;
        }
         #endregion

        # region GetDataTable   执行SQL语句并返回一个表
         ///   <summary>
         ///  执行SQL语句并返回一个表
         ///   </summary>
         ///   <param name="cmdText"> SQL文本 </param>
         ///   <param name="DBCnStr"> dbconnect </param>
         ///   <param name="inDt"> 返回表 </param>
         ///   <returns> 成功则返回0,否则返回错误代码 </returns>
         public   int  GetCount( string  cmdText)
        {
            DBCnStr  =   this .InitSqlConnection(DBCnStr, cmdText);
            SqlConnection oCn  =   new  SqlConnection(DBCnStr);

             try
            {
                oCn.Open();
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                 return   - 1 ;
            }

             // 建立数据适配对象
            SqlDataAdapter oleDataAdapter  =   new  SqlDataAdapter(cmdText, oCn);

            DataTable inDt  =   new  DataTable();
             int  status  =   - 1 ;
             try
            {
                 // 填充数据表
                oleDataAdapter.Fill(inDt);

                status  =  inDt.Rows.Count;
            }
             catch  (Exception oErr)
            {
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
                status  =   - 1 ;
            }
             finally
            {
                oleDataAdapter  =   null ;
                 if  (oCn.State  ==  System.Data.ConnectionState.Open)
                {
                    oCn.Close();
                }
            }
             return  status;
        }
         #endregion

        # region     //   GetNVColl   执行SQL语句并返回NameValueCollection
         /// //  <summary>
         /// // 执行SQL语句并返回NameValueCollection
         /// //  </summary>
         /// //  <param name="cmdText"> SQL文本 </param>
         /// //  <param name="NameValueCollection"> nvColl </param>
         /// //  <returns> 成功则返回0,否则返回错误代码 </returns>
         // public int GetNVColl(string cmdText, ref NameValueCollection nvColl)
         // {

         //     DBCnStr = this.InitSqlConnection(DBCnStr, cmdText);
         //     SqlConnection oCn = new SqlConnection(DBCnStr);

         //     try
         //     {
         //         oCn.Open();
         //     }
         //     catch (Exception oErr)
         //     {
         //          // WriteFile(oErr.Message);
         //         Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

         //         return -1;
         //     }
         //      // 建立数据读取对象
         //     SqlCommand oleCommand = new SqlCommand(cmdText, oCn);
         //      // 填充SqlDataReader
         //     SqlDataReader oleReader;

         //     int status = -1;
         //     try
         //     {

         //         oleReader = oleCommand.ExecuteReader();
         //          //  Always call Read before accessing data.
         //         while (oleReader.Read())
         //         {
         //             for (int i = 0; i < oleReader.FieldCount; i++)
         //             {
         //                 if (oleReader.GetValue(i).ToString() != "")
         //                     nvColl.Add(oleReader.GetName(i), oleReader.GetString(i));
         //             }
         //         }

         //         status = 0;
         //     }
         //     catch (Exception oErr)
         //     {
         //          // 异常处理
         //          // WriteFile(oErr.Message);
         //         Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

         //         status = -1;
         //     }
         //     finally
         //     {
         //         oleReader = null;
         //         if (oCn.State == System.Data.ConnectionState.Open)
         //         {
         //             oCn.Close();
         //         }
         //     }
         //     return status;
         // }
         #endregion

         //
          #region  GetArrayList     执行SQL语句并返回一个ArrayList
         /// //  <summary>
         /// // 执行SQL语句并返回一个ArrayList
         /// //  </summary>
         /// //  <param name="cmdText"> SQL文本 </param>
         /// //  <returns> 返回ArrayList arraylist[i]为name，arraylist[i+1]为value </returns>
         // public ArrayList GetArrayList(string cmdText, ref ArrayList aName, ref ArrayList aValue)
         // {
         //     ArrayList aNameValue = new ArrayList();
         //     SqlConnection oCn = new SqlConnection(DBCnStr);

         //     try
         //     {
         //         oCn.Open();
         //     }
         //     catch (Exception oErr)
         //     {
         //          // WriteFile(oErr.Message);
         //         Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

         //         return null;
         //     }
         //      // 建立数据读取对象
         //     SqlCommand oleCommand = new SqlCommand(cmdText, oCn);
         //      // 填充SqlDataReader
         //     SqlDataReader oleReader;

         //     ArrayList status = null;
         //     try
         //     {

         //         oleReader = oleCommand.ExecuteReader();
         //          //  Always call Read before accessing data.
         //         while (oleReader.Read())
         //         {
         //             for (int i = 0; i < oleReader.FieldCount; i++)
         //             {
         //                 if (oleReader.GetValue(i).ToString() != "")
         //                     aName.Add(oleReader.GetName(i));
         //                 aValue.Add(oleReader.GetString(i + 1));
         //             }
         //         }

         //         status = aValue;
         //     }
         //     catch (Exception oErr)
         //     {
         //          // 异常处理
         //          // WriteFile(oErr.Message);
         //         Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

         //         status = null;
         //     }
         //     finally
         //     {
         //         oleReader = null;
         //         if (oCn.State == System.Data.ConnectionState.Open)
         //         {
         //             oCn.Close();
         //         }
         //     }
         //     return status;
         // }
         #endregion
         //
         #region    GetArrayList   执行SQL语句并返回一个ArrayList
         ///   <summary>
         ///  执行SQL语句并返回一个ArrayList
         ///   </summary>
         ///   <param name="cmdText"> SQL文本 </param>
         ///   <returns> 返回ArrayList arraylist[i]为name，arraylist[i+1]为value </returns>
         public   int  GetArrayList( string  cmdText,  ref  ArrayList aNameValue)
        {
            DBCnStr  =   this .InitSqlConnection(DBCnStr, cmdText);
            SqlConnection oCn  =   new  SqlConnection(DBCnStr);

             try
            {
                oCn.Open();
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                 return   - 1 ;
            }
             // 建立数据读取对象
            SqlCommand oleCommand  =   new  SqlCommand(cmdText, oCn);
             // 填充SqlDataReader
            SqlDataReader oleReader;

             int  status  =   - 1 ;
             try
            {

                oleReader  =  oleCommand.ExecuteReader();
                 //  Always call Read before accessing data.
                 while  (oleReader.Read())
                {
                     for  ( int  i  =   0 ; i  <  oleReader.FieldCount  -   1 ; i  ++  )
                    {
                         if  (oleReader.GetValue(i).ToString()  !=   "" )
                            aNameValue.Add(oleReader.GetName(i));
                    }
                }

                status  =   1 ;
            }
             catch  (Exception oErr)
            {
                 // 异常处理
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                status  =   - 1 ;
            }
             finally
            {
                oleReader  =   null ;
                 if  (oCn.State  ==  System.Data.ConnectionState.Open)
                {
                    oCn.Close();
                }
            }
             return  status;
        }
         #endregion

         #region    GetASObject   执行SQL语句并返回一个包含多条数据的ASObject (name，value)
         ///   <summary>
         ///  执行SQL语句,查询两个字段,并返回一个ASObject
         ///   </summary>
         ///   <param name="cmdText"> SQL文本 </param>
         ///   <param name="asO"> ASObject 对象 </param>
         ///   <returns> 返回int  ASObject[i]为(name，value) </returns>
         public   int  GetASObjectMulti( string  cmdText,  ref  ASObject asO)
        {
            DBCnStr  =   this .InitSqlConnection(DBCnStr, cmdText);
            SqlConnection oCn  =   new  SqlConnection(DBCnStr);
             try
            {
                oCn.Open();
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                 return   - 1 ;
            }
             // 建立数据读取对象
            SqlCommand oleCommand  =   new  SqlCommand(cmdText, oCn);
             // 填充SqlDataReader
            SqlDataReader oleReader;

             int  status  =   - 1 ;
             try
            {
                 int  i  =   1 ;
                oleReader  =  oleCommand.ExecuteReader();
                 //  Always call Read before accessing data.
                 while  (oleReader.Read())
                {
                     for  ( int  j  =   0 ; j  <  oleReader.FieldCount; j ++ )
                    {
                        asO.Add(i + " @ " + oleReader.GetName(j),oleReader.GetValue(j)); // i@+"name",i为第几条数据的序号
                    }
                    i ++ ;
                }

                status  =   1 ;
            }
             catch  (Exception oErr)
            {
                 // 异常处理
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                status  =   - 1 ;
            }
             finally
            {
                oleReader  =   null ;
                 if  (oCn.State  ==  System.Data.ConnectionState.Open)
                {
                    oCn.Close();
                }
            }
             return  status;
        }
         #endregion

         #region    GetASObjectSingle   执行SQL语句并返回一个包含单条数据的ASObject (name，value)
         ///   <summary>
         ///  执行SQL语句查询一条数据(必须返回一条数据),返回一个ASObject
         ///   </summary>
         ///   <param name="cmdText"> SQL文本 </param>
         ///   <param name="asO"> ASObject 对象 </param>
         ///   <returns> 返回int  ASObject[i]为(name，value) </returns>
         public   int  GetASObjectSingle( string  cmdText,  ref  ASObject asO)
        {
            DBCnStr  =   this .InitSqlConnection(DBCnStr, cmdText);
            SqlConnection oCn  =   new  SqlConnection(DBCnStr);
             try
            {
                oCn.Open();
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                 return   - 1 ;
            }
             // 建立数据读取对象
            SqlCommand oleCommand  =   new  SqlCommand(cmdText, oCn);
             // 填充SqlDataReader
            SqlDataReader oleReader;

             int  status  =   - 1 ;
             try
            {
                 // oleReader = oleCommand.ExecuteScalar();  //  modified by apenni 2006-5-6
                oleReader  =  oleCommand.ExecuteReader();
                 //  Always call Read before accessing data.
                 while  (oleReader.Read())
                {
                     for  ( int  i  =   0 ; i  <  oleReader.FieldCount; i ++ )
                    {
                        asO.Add(oleReader.GetName(i), oleReader.GetValue(i));
                    }
                }

                status  =   1 ;
            }
             catch  (Exception oErr)
            {
                 // 异常处理
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                status  =   - 1 ;
            }
             finally
            {
                oleReader  =   null ;
                 if  (oCn.State  ==  System.Data.ConnectionState.Open)
                {
                    oCn.Close();
                }
            }
             return  status;
        }
         #endregion

         #region    ExecuteSql    执行SET，DELETE语句时返回影响的行数
         ///   <summary>
         ///  执行SET，DELETE语句时返回影响的行数
         ///   </summary>
         ///   <param name="cmdText"> “SQL文本” </param>
         ///   <returns> “返回影响的行数,否则返回错误代码” </returns>
         public   int  ExecuteSql( string  cmdText)
        {
             int  intReturn  =   - 1 ; // 返回影响的行数。
            SqlCommand oCmd  =   new  SqlCommand();

            DBCnStr  =   this .InitSqlConnection(DBCnStr, cmdText);
            SqlConnection oCn  =   new  SqlConnection(DBCnStr);
             try
            {
                oCn.Open();
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                 return   - 1 ;
            }

            oCmd.Connection  =  oCn;
            oCmd.CommandType  =  CommandType.Text;
            oCmd.CommandText  =  cmdText;

             // 定义事务 设定隔离级别
            SqlTransaction oTx  =  oCn.BeginTransaction(IsolationLevel.ReadCommitted);
            oCmd.Transaction  =  oTx;

             // 处理SQL语句
             #region    事务处理
             try
            {
                 // 支持事务
                intReturn  =  oCmd.ExecuteNonQuery();
                oTx.Commit();

            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                oTx.Rollback();

                intReturn  =   - 1 ;

            }
             finally
            {
                oCmd  =   null ;
                oTx  =   null ;
                 if  (oCn.State  ==  System.Data.ConnectionState.Open)
                {
                    oCn.Close();
                }
            }
             #endregion

             return  intReturn;
        }
         #endregion

         #region    ExecuteSql    执行SET，DELETE语句时返回影响的行数
         ///   <summary>
         ///  执行SET，DELETE语句时返回影响的行数
         ///   </summary>
         ///   <param name="cmdText"> “SQL文本,支持多sql语句通过';'拼接” </param>
         ///   <returns> “返回影响的行数,否则返回错误代码” </returns>
         public   int  ExecuteSql( params   string [] cmdText)
        {
             string  strSql  =   string .Empty;
             foreach  ( string  strCmd  in  cmdText)
            {
                strSql  +=  strCmd;
            }
             return  ExecuteSql(strSql);
        }
         // added by apenni 2006-5-6
         #endregion

         #region    CallStoreProc   调用系统存储过程返回一个整数
         ///   <summary>
         ///  调用系统存储过程返回一个整数
         ///   </summary>
         ///   <param name = "strSysSPName"> “存储过程枚举类型” </param>
         ///   <param name="InParaName"> "in参数名字" </param>
         ///   <param name = "ParamValue"> “参数列表” </param>
         ///   <param name="OutParaName"> "out参数名字" </param>
         ///   <param name="OutParaValue"> "返回的参数值" </param>
         ///   <param name="IType"> "out参数的类型" </param>
         ///   <returns> "成功则返回所影响的行数,否则返回-1" </returns>
         public   int  CallStoreProc( string  strSysSPName, IList InParaName, IList InParamValue, IList OutParaName,  ref   object [] OutParaValue, DBTYPE[] IType)
        {
             int  inReturn  =   - 1 ;


            SqlCommand oCmd  =   new  SqlCommand();

            oCmd.CommandText  =  strSysSPName;
            oCmd.CommandType  =  CommandType.StoredProcedure;

             #region  in参数的建立
             if  (InParamValue  !=   null   &&  InParaName  !=   null )
            {
                 // 建立in参数
                 for  ( int  i  =   0 ; i  <  InParamValue.Count; i ++ )
                {
                    SqlParameter oPara  =   new  SqlParameter();

                     // 调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
                     if  (InParaName[i]  !=   null )
                    {
                        oPara  =  SQLParamHelper.CreateParameterWithValue(InParaName[i].ToString(), InParamValue[i]);
                        oPara.Direction  =  ParameterDirection.Input;
                        oCmd.Parameters.Add(oPara);
                    }
                }
            }
             #endregion

             #region  out参数的建立

             if  (OutParaName  !=   null   &&  OutParaValue  !=   null   &&  IType  !=   null )
            {
                 // 建立in参数
                 for  ( int  i  =   0 ; i  <  OutParaName.Count; i ++ )
                {
                    SqlParameter oPara  =   new  SqlParameter();

                     // 调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
                     if  (OutParaName[i]  !=   null )
                    {
                        oPara  =  SQLParamHelper.CreateOutParameterWithValue(OutParaName[i].ToString(), IType[i].ToString());
                        oPara.Direction  =  ParameterDirection.Output;
                        oCmd.Parameters.Add(oPara);
                    }
                }
            }

             #endregion

            SqlConnection oCn  =   new  SqlConnection(DBCnStr);
             try
            {
                oCn.Open();
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
                 return   - 1 ;
            }

            oCmd.Connection  =  oCn;

             // 连接数据库和执行存储过程
             try
            {
                inReturn  =  oCmd.ExecuteNonQuery();
                 for  ( int  i  =   0 ; i  <  OutParaValue.Length; i ++ )
                {
                    OutParaValue[i]  =  oCmd.Parameters[OutParaName[i].ToString()].Value;
                }
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                inReturn  =   - 1 ;
            }
             finally
            {
                 if  (oCn.State  ==  System.Data.ConnectionState.Open)
                {
                    oCn.Close();
                }
                oCmd  =   null ;
            }
             return  inReturn;
        }
         #endregion

         #region    CallStoreProc   调用系统存储过程并影响生成一个object对象值
         ///   <summary>
         ///  调用系统存储过程并影响生成一个object对象值
         ///   </summary>
         ///   <param name = "strSysSPName"> “存储过程枚举类型” </param>
         ///   <param name="InParaName"> "in参数名字" </param>
         ///   <param name = "InParamValue"> “in参数列表” </param>
         ///   <param name="OutParaName"> "out参数名字" </param>
         ///   <param name="OutParaValue"> "out参数值" </param>
         ///   <param name="IType"> "out参数的类型" </param>
         ///   <param name="inObject"> "引用的值" </param>
         ///   <returns> 成功则返回1,否则返回-1或错误代码 </returns>
         public   int  CallStoreProc( string  strSysSPName, IList InParaName, IList InParamValue, IList OutParaName,  ref   object [] OutParaValue, DBTYPE[] IType,  ref   object  objReturn)
        {
             // 建立Command对象
            SqlCommand oCmd  =   new  SqlCommand();

            oCmd.CommandText  =  strSysSPName.ToString();
            oCmd.CommandType  =  CommandType.StoredProcedure;

             int  status  =   - 1 ;

             #region  in参数的建立
             if  (InParamValue  !=   null   &&  InParaName  !=   null )
            {
                 // 建立in参数
                 for  ( int  i  =   0 ; i  <  InParamValue.Count; i ++ )
                {
                    SqlParameter oPara  =   new  SqlParameter();

                     // 调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
                     if  (InParaName[i]  !=   null )
                    {
                        oPara  =  SQLParamHelper.CreateParameterWithValue(InParaName[i].ToString(), InParamValue[i]);
                        oPara.Direction  =  ParameterDirection.Input;
                        oCmd.Parameters.Add(oPara);
                    }
                }
            }
             #endregion

             #region  out参数的建立

             if  (OutParaName  !=   null   &&  OutParaValue  !=   null   &&  IType  !=   null )
            {
                 // 建立in参数
                 for  ( int  i  =   0 ; i  <  OutParaName.Count; i ++ )
                {
                    SqlParameter oPara  =   new  SqlParameter();

                     // 调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
                     if  (OutParaName[i]  !=   null )
                    {
                        oPara  =  SQLParamHelper.CreateOutParameterWithValue(OutParaName[i].ToString(), IType[i].ToString());
                        oPara.Direction  =  ParameterDirection.Output;
                        oCmd.Parameters.Add(oPara);
                    }
                }
            }

             #endregion

            SqlConnection oCn  =   new  SqlConnection(DBCnStr);
             try
            {
                oCn.Open();
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                 return   - 1 ;
            }

            oCmd.Connection  =  oCn;

             // 连接数据库和执行存储过程
             try
            {
                 // 通过SqlDataAdapter来填充Table
                objReturn  =  oCmd.ExecuteScalar();
                 #region  取得返回参数的值

                 for  ( int  i  =   0 ; i  <  OutParaValue.Length; i ++ )
                {
                    OutParaValue[i]  =  oCmd.Parameters[OutParaName[i].ToString()].Value;
                }

                 #endregion
                status  =   0 ;
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                status  =   - 1 ;
            }
             finally
            {
                 if  (oCn.State  ==  System.Data.ConnectionState.Open)
                {
                    oCn.Close();
                }
                oCmd  =   null ;
            }
             return  status;
        }
         #endregion
       
         #region     CallStoreProc    调用用户存储过程返回一个DataTable(Select 语句)
         ///   <summary>
         ///  调用用户存储过程返回一个DataTable(Select 语句)
         ///   </summary>
         ///   <param name = "strSPName"> “存储过程名” </param>
         ///   <param name="InParaName"> "in参数名字" </param>
         ///   <param name = "InParamValue"> “in参数列表” </param>
         ///   <param name="OutParaName"> "out参数名字" </param>
         ///   <param name="IType"> "out参数的类型" </param>
         ///   <param name="OutParaValue"> "out参数值" </param>
         ///   <param name="oDT"> "传入的DataTable引用" </param>
         /// <returns> "成功则返回1,否则返回-1或错误代码" </returns>
         public   int  CallStoreProc( string  strSPName, IList InParaName, IList InParamValue, IList OutParaName,  ref   object [] OutParaValue, DBTYPE[] IType,  ref  DataTable oDT)
        {

             // 建立Command对象
            SqlCommand oCmd  =   new  SqlCommand();

            oCmd.CommandText  =  strSPName.ToString();
            oCmd.CommandType  =  CommandType.StoredProcedure;

             int  status  =   - 1 ;

             #region  in参数的建立
             if  (InParamValue  !=   null   &&  InParaName  !=   null )
            {
                 // 建立in参数
                 for  ( int  i  =   0 ; i  <  InParamValue.Count; i ++ )
                {
                    SqlParameter oPara  =   new  SqlParameter();

                     // 调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
                     if  (InParaName[i]  !=   null )
                    {
                        oPara  =  SQLParamHelper.CreateParameterWithValue(InParaName[i].ToString(), InParamValue[i]);
                        oPara.Direction  =  ParameterDirection.Input;
                        oCmd.Parameters.Add(oPara);
                    }
                }
            }
             #endregion

             #region  out参数的建立

             if  (OutParaName  !=   null   &&  OutParaValue  !=   null   &&  IType  !=   null )
            {
                 // 建立in参数
                 for  ( int  i  =   0 ; i  <  OutParaName.Count; i ++ )
                {
                    SqlParameter oPara  =   new  SqlParameter();

                     // 调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
                     if  (OutParaName[i]  !=   null )
                    {
                        oPara  =  SQLParamHelper.CreateOutParameterWithValue(OutParaName[i].ToString(), IType[i].ToString());
                        oPara.Direction  =  ParameterDirection.Output;
                        oCmd.Parameters.Add(oPara);
                    }
                }
            }

             #endregion

            SqlConnection oCn  =   new  SqlConnection(DBCnStr);
             try
            {
                oCn.Open();
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                 return   - 1 ;
            }

            oCmd.Connection  =  oCn;

             // 连接数据库和执行存储过程
             try
            {
                 // 通过SqlDataAdapter来填充Table

                SqlDataAdapter oDp  =   new  SqlDataAdapter(oCmd.CommandText.ToString(), oCn);

                 // 建立SqlDataAdapter与SqlCommand的连接
                oDp.SelectCommand  =  oCmd;
                oDp.DeleteCommand  =  oCmd;
                oDp.UpdateCommand  =  oCmd;
                oDp.DeleteCommand  =  oCmd;

                 // 填充DataTable
                oDp.Fill(oDT);
                 #region  取得返回参数的值

                 for  ( int  i  =   0 ; i  <  OutParaValue.Length; i ++ )
                {
                    OutParaValue[i]  =  oCmd.Parameters[OutParaName[i].ToString()].Value;
                }

                 #endregion
                status  =   0 ;
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                status  =   - 1 ;
            }
             finally
            {
                 if  (oCn.State  ==  System.Data.ConnectionState.Open)
                {
                    oCn.Close();
                }
                oCmd  =   null ;
            }
             return  status;
        }
         #endregion

         #region    CallStoreProc    调用系统存储过程并影响生成一个DataSet对象
         ///   <summary>
         ///  调用系统存储过程并影响生成一个DataSet对象
         ///   </summary>
         ///   <param name = "strSysSPName"> “存储过程枚举类型” </param>
         ///   <param name="InParaName"> "in参数名字" </param>
         ///   <param name = "InParamValue"> “in参数列表” </param>
         ///   <param name="OutParaName"> "out参数名字" </param>
         ///   <param name="OutParaValue"> "out参数值" </param>
         ///   <param name="IType"> "out参数的类型" </param>
         ///   <param name=" oDs"> "引用的DataSet" </param>
         ///   <returns> 成功则返回1,否则返回-1或错误代码 </returns>
         public   int  CallStoreProc( string  strSysSPName, IList InParaName, IList InParamValue, IList OutParaName,  ref   object [] OutParaValue, DBTYPE[] IType,  ref  DataSet oDs)
        {
            SqlCommand oCmd  =   new  SqlCommand();

            oCmd.CommandText  =  strSysSPName;
            oCmd.CommandType  =  CommandType.StoredProcedure;

             int  status  =   - 1 ;

             #region  in参数的建立
             if  (InParamValue  !=   null   &&  InParaName  !=   null )
            {
                 // 建立in参数
                 for  ( int  i  =   0 ; i  <  InParamValue.Count; i ++ )
                {
                    SqlParameter oPara  =   new  SqlParameter();

                     // 调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
                     if  (InParaName[i]  !=   null )
                    {
                        oPara  =  SQLParamHelper.CreateParameterWithValue(InParaName[i].ToString(), InParamValue[i]);
                        oPara.Direction  =  ParameterDirection.Input;
                        oCmd.Parameters.Add(oPara);
                    }
                }
            }
             #endregion

             #region  out参数的建立

             if  (OutParaName  !=   null   &&  OutParaValue  !=   null   &&  IType  !=   null )
            {
                 // 建立in参数
                 for  ( int  i  =   0 ; i  <  OutParaName.Count; i ++ )
                {
                    SqlParameter oPara  =   new  SqlParameter();

                     // 调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
                     if  (OutParaName[i]  !=   null )
                    {
                        oPara  =  SQLParamHelper.CreateOutParameterWithValue(OutParaName[i].ToString(), IType[i].ToString());
                        oPara.Direction  =  ParameterDirection.Output;
                        oCmd.Parameters.Add(oPara);
                    }
                }
            }

             #endregion

            SqlConnection oCn  =   new  SqlConnection(DBCnStr);
             try
            {
                oCn.Open();
            }
             catch  (Exception oErr)
            {
                 // WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                 return   - 1 ;
            }

            oCmd.Connection  =  oCn;

             // 连接数据库和执行存储过程
             try
            {
                 // 通过SqlDataAdapter来填充Table
                SqlDataAdapter oDp  =   new  SqlDataAdapter(oCmd.CommandText.ToString(), oCn);

                 // 建立SqlDataAdapter与SqlCommand的连接
                oDp.SelectCommand  =  oCmd;
                oDp.DeleteCommand  =  oCmd;
                oDp.UpdateCommand  =  oCmd;
                oDp.DeleteCommand  =  oCmd;
                 //
                 oDp.Fill(oDs);
                 #region  取得返回参数的值
                 for  ( int  i  =   0 ; i  <  OutParaValue.Length; i ++ )
                {
                    OutParaValue[i]  =  oCmd.Parameters[OutParaName[i].ToString()].Value;
                }
                 #endregion
                status  =   0 ;
            }
             catch  (Exception oErr)
            {

                 //  WriteFile(oErr.Message);
                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);

                status  =   - 1 ;
            }
             finally
            {
                 if  (oCn.State  ==  System.Data.ConnectionState.Open)
                {
                    oCn.Close();
                }
                oCmd  =   null ;
            }
             return  status;
        }
         #endregion

         #region      GetSqlWhere  产生SQL语句
         ///   <summary>
         ///  产生SQL语句
         ///   </summary>
         ///   <param name="InName"> 表字段名 </param>
         ///   <param name="InValue"> 表字段值 </param>
         ///   <returns> 结果SQL语句 </returns>
         public   string  GetSqlWhere(ArrayList InName, ArrayList InValue)
        {
            DataTable DataTableTmp  =   new  DataTable();
             string  StrSqlWhereTmp  =   "" ;
             string  StrTmp  =   "" ;
             string  StrName  =   "" ;
             string  StrValue  =   "" ;

             if  (InName  ==   null   ||  InValue  ==   null )
            {
                 return   null ;
            }
             for  ( int  i  =   0 ; i  <  InName.Count; i ++ )
            {
                StrTmp  =  InName[i].ToString();
                 if  (StrTmp.Substring( 0 ,  2 )  ==   " #S " )  // 开始时间
                {
                    StrName  =  StrTmp.Substring( 2 )  +   "  >=  " ;
                    StrValue  =   " to_date(' "   +  InValue[i].ToString()  +   " ','yyyy-mm-dd HH24:Mi:ss') " ;
                }
                 else   if  (StrTmp.Substring( 0 ,  2 )  ==   " #E " ) // 结束时间
                {
                    StrName  =  StrTmp.Substring( 2 )  +   "  <  " ;
                    StrValue  =   " to_date(' "   +  InValue[i].ToString()  +   " ','yyyy-mm-dd HH24:Mi:ss') " ;
                }
                 else   if  (StrTmp.Substring( 0 ,  2 )  ==   " #N " ) // <>条件
                {
                    StrName  =  StrTmp.Substring( 2 )  +   "  <>  " ;
                    StrValue  =  InValue[i].ToString();
                }
                 else   if  (StrTmp.Substring( 0 ,  2 )  ==   " #D " ) // 大于条件
                {
                    StrName  =  StrTmp.Substring( 2 )  +   " > " ;
                    StrValue  =  InValue[i].ToString();
                }
                 else   if  (StrTmp.Substring( 0 ,  2 )  ==   " #X " ) // 小于条件
                {
                    StrName  =  StrTmp.Substring( 2 )  +   " < " ;
                    StrValue  =  InValue[i].ToString();
                }
                 else   if  (StrTmp.Substring( 0 ,  2 )  ==   " #I " ) // IN条件
                {
                    StrName  =  StrTmp.Substring( 2 )  +   "  IN ( " ;
                    StrValue  =  InValue[i].ToString()  +   " ) " ;
                }
                 else   if  (StrTmp.Substring( 0 ,  2 )  ==   " #0 " ) // 没有条件
                {
                     return  InValue[i].ToString();
                }
                 else          // 等于条件
                {
                    StrName  =  StrTmp  +   " = " ;
                    StrValue  =  InValue[i].ToString();
                }

                StrSqlWhereTmp  =  StrSqlWhereTmp  +  StrName  +  StrValue  +   "  and  " ;
            }

            StrSqlWhereTmp  =  StrSqlWhereTmp.Substring( 0 , StrSqlWhereTmp.Length  -   5 );
             return  StrSqlWhereTmp;
        }
         #endregion
    }
     #endregion
   
     #region     class SQLParamHelper
     ///   <summary>
     ///  SQLParamHelper
     ///   </summary>
     internal   class  SQLParamHelper
    {
         #region  创建出入参数
         ///   <summary>
         ///  根据输入的OBJECT对象生成不同的参数
         ///   </summary>
         ///   <param name="name"> “参数名字” </param>
         ///   <param name="nValue"> “参数值” </param>
         ///   <returns></returns>
         public   static  SqlParameter CreateParameterWithValue( string  name,  object  nValue)
        {
             string  strType;
            SqlParameter param;
             int  intLenth  =   0 ;

             if  (nValue  !=   null )
            {
                strType  =  nValue.GetType().ToString();
                intLenth  =  nValue.ToString().Trim().Length;
                 if  (intLenth  >   0 )
                {
                     switch  (strType)
                    {
                         case   " System.Int32 " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.BigInt, intLenth);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System.Double " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.Decimal);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System.Single " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.Float);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System.UInt64 " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.BigInt, intLenth);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System.Int64 " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.BigInt, intLenth);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System.Decimal " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.Decimal, intLenth);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System.Object " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.Real, intLenth);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System.UInt16 " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.BigInt, intLenth);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System.Int16 " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.BigInt, intLenth);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System.Boolean " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.Binary);
                                param.Direction  =  ParameterDirection.Input;
                                 bool  bolTemp  =  ( bool )nValue;
                                param.Value  =  (bolTemp  ==   true   ?   1  :  0 );
                                 break ;
                            }
                         case   " System.String " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.VarChar, intLenth);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System.DateTime " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.DateTime, intLenth);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System.Char " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.Char, intLenth);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System.SByte " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.Bit, intLenth);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System_XMl " :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.Xml,  300 );
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         case   " System.Text " :
                            {
                                 if  (intLenth  <   2000 )
                                {
                                    param  =   new  SqlParameter(name, SqlDbType.Text, intLenth);
                                }
                                 else
                                {
                                    param  =   new  SqlParameter(name, SqlDbType.Text);
                                }
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                         default :
                            {
                                param  =   new  SqlParameter(name, SqlDbType.Variant);
                                param.Direction  =  ParameterDirection.Input;
                                param.Value  =  nValue;
                                 break ;
                            }
                    }
                    param.Direction  =  ParameterDirection.Input;
                }
                 else
                {
                    param  =   new  SqlParameter(name, SqlDbType.VarChar,  10 );
                    param.Direction  =  ParameterDirection.Input;
                    param.Value  =   "" ;
                }
            }
             else
            {
                param  =   new  SqlParameter(name, SqlDbType.Variant);
                param.Direction  =  ParameterDirection.Input;
                param.Value  =   null ;
            }
             return  param;

        }
         #endregion

         #region     CreateOutParameterWithValue    建立输出参数
         ///   <summary>
         ///  建立输出参数
         ///   </summary>
         ///   <param name="name"> "参数名" </param>
         ///   <param name="objType"> "参数类型" </param>
         ///   <returns></returns>
         public   static  SqlParameter CreateOutParameterWithValue( string  name,  string  objType)
        {
             string  strType  =  objType;

            SqlParameter param;

             switch  (strType)
            {
                 case   " System_Object " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.Variant);
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_Single " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.Float);
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_UInt64 " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.BigInt);
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_Int64 " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.BigInt);
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_Int32 " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.Int);
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_UInt16 " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.SmallInt);
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_Int16 " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.SmallInt);
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_Double " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.Float);
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_Decimal " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.Decimal);
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_Boolean " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.Binary);
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_String " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.VarChar,  200 );
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_DateTime " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.DateTime);
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_Char " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.Char,  100 );
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_SByte " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.NChar,  30 );
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_Text " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.Text,  300 );
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 case   " System_XMl " :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.Xml,  300 );
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
                 default :
                    {
                        param  =   new  SqlParameter(name, SqlDbType.Variant);
                        param.Direction  =  ParameterDirection.Output;
                         break ;
                    }
            }
             return  param;

        }
         #endregion  CreateOutParams
    
         #region  CreateParameter    创建形式参数
         ///   <summary>
         ///  转换参数为SQL语句的表达式
         ///   </summary>
         ///   <param name="nValue"> 传入的Object类型值 </param>
         ///   <returns> 已经转换好的String </returns>
         public   static   string  CreateParameter(SqlParameter oValue)
        {
             string  strTemPara;
             object  oPara_Value  =  oValue.Value;

             if  (oPara_Value  !=   null )
            {
                 string  strType  =  oValue.SqlDbType.ToString();
                 switch  (strType)
                {
                     case   " VarChar " :
                        {
                            strTemPara  =   " ' "   +  CheckMark(oPara_Value)  +   " ' " ;
                             break ;
                        }
                     case   " Char " :
                        {
                            strTemPara  =   " ' "   +  CheckMark(oPara_Value)  +   " ' " ;
                             break ;
                        }
                     case   " NChar " :
                        {
                            strTemPara  =   " ' "   +  CheckMark(oPara_Value)  +   " ' " ;
                             break ;
                        }
                     case   " NVarChar " :
                        {
                            strTemPara  =   " ' "   +  CheckMark(oPara_Value)  +   " ' " ;
                             break ;
                        }
                     // 日期型
                     case   " DateTime " :
                        {
                            DateTime dt  =   new  DateTime();
                            dt  =  (DateTime)oPara_Value;

                             string  strTP  =   " ' "   +  dt.Year  +   " - "   +  dt.Month  +   " - "   +  dt.Day;
                            strTP  +=   "   "   +  dt.Hour.ToString()  +   " : "   +  dt.Minute.ToString();
                            strTP  +=   " : "   +  dt.Second.ToString()  +   " ', " ;
                            strTemPara  =   " TO_DATE( "   +  strTP  +   " 'yyyy-mm-dd hh24:mi:ss' "   +   " ) " ;
                             break ;
                        }
                     case   " LongVarChar " :
                        {
                            strTemPara  =   " ' "   +  CheckMark(oPara_Value)  +   " ' " ;
                             break ;
                        }
                     case   " Clob " :
                        {
                            strTemPara  =   " ' "   +  CheckMark(oPara_Value)  +   " ' " ;
                             break ;
                        }
                     default :
                        {
                            strTemPara  =  oPara_Value.ToString();
                             break ;
                        }
                }
            }
             else
            {
                 // 将null传入
                strTemPara  =   " null " ;
            }
             return  strTemPara;
        }
         #endregion

         #region    CheckMark   替换object的'为''并转换为String
         ///   <summary>
         ///  替换object的'为''并转换为String
         ///   </summary>
         ///   <param name="objIn"> 传入的Object类型 </param>
         ///   <returns> 已经替换'为''的String </returns>
         private   static   string  CheckMark( object  objIn)
        {
             string  strTmp  =  objIn.ToString();

             return  strTmp.Replace( " ' " ,  " '' " );   //  modified by apenni 06.01.02

             // string strRet = "";
             // for (int i = 0; i < strTmp.Length; i++)
             // {
             //     if (strTmp[i].ToString() == "'")
             //     {
             //         strRet += "''";
             //     }
             //     else
             //     {
             //         strRet += strTmp[i].ToString();
             //     }
             // }
             // return strRet;
        }
         #endregion
    }
     #endregion


posted on 2006-06-09 11:11 疯流成性 阅读(743) 评论(0)  编辑  收藏 所属分类: .NET


只有注册用户登录后才能发表评论。
网站导航: