c#操作oracle总结

资料来源：网络整理时间：2023/2/14 0:16:31 共计：3608 浏览

using System;
using System.Data;
using System.Data.OracleClient;
using System.Collections;
using System.Reflection;
namespace MyOraComm
{
   /// <summary>
   /// ConnDbForOracle 的摘要说明。
   /// </summary>
   public class ConnForOracle
   {
    protected OracleConnection Connection;
    private string connectionString;
    public ConnForOracle()
    {
     string connStr;
     connStr =
         System.Configuration.ConfigurationSettings.
         AppSettings["connStr"].ToString();
     connectionString = connStr;
     Connection = new OracleConnection(connectionString);
    }

    #region 带参数的构造函数
    /// <summary>
    /// 带参数的构造函数
    /// </summary>
    /// <param name="ConnString">数据库联接字符串</param>
    public ConnForOracle(string ConnString)
    {
     string connStr;
     connStr =
         System.Configuration.ConfigurationSettings.
         AppSettings[ConnString].ToString();
     Connection = new OracleConnection(connStr);
    }
    #endregion

    #region 打开数据库
    /// <summary>
    /// 打开数据库
    /// </summary>
    public void OpenConn()
    {
     if(this.Connection.State!=ConnectionState.Open)
      this.Connection.Open();
    }
    #endregion
    #region 关闭数据库联接
    /// <summary>
    /// 关闭数据库联接
    /// </summary>
    public void CloseConn()
    {
     if(Connection.State==ConnectionState.Open)
      Connection.Close();
    }
    #endregion

    #region 执行SQL语句，返回数据到DataSet中
    /// <summary>
    /// 执行SQL语句，返回数据到DataSet中
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="DataSetName">自定义返回的DataSet表名</param>
    /// <returns>返回DataSet</returns>
    public DataSet ReturnDataSet(string sql,string DataSetName)
    {
     DataSet dataSet=new DataSet();
     OpenConn();
     OracleDataAdapter OraDA=new OracleDataAdapter(sql,Connection);
     OraDA.Fill(dataSet,DataSetName);
     //    CloseConn();
     return dataSet;
    }
    #endregion

    #region 执行Sql语句,返回带分页功能的dataset
    /// <summary>
    /// 执行Sql语句,返回带分页功能的dataset
    /// </summary>
    /// <param name="sql">Sql语句</param>
    /// <param name="PageSize">每页显示记录数</param>
    /// <param name="CurrPageIndex"><当前页/param>
    /// <param name="DataSetName">返回dataset表名</param>
    /// <returns>返回DataSet</returns>
    public DataSet ReturnDataSet(string sql,int PageSize,
        int CurrPageIndex,string DataSetName)
    {
     DataSet dataSet=new DataSet();
     OpenConn();
     OracleDataAdapter OraDA=
         new OracleDataAdapter(sql,Connection);
     OraDA.Fill(dataSet,PageSize * (CurrPageIndex - 1),
         PageSize,DataSetName);
     //    CloseConn();
     return dataSet;
    }
    #endregion

    #region 执行SQL语句，返回 DataReader,用之前一定要先.read()打开,然后才能读到数据
    /// <summary>
    /// 执行SQL语句，返回 DataReader,用之前一定要先.read()打开,然后才能读到数据
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>返回一个OracleDataReader</returns>
    public OracleDataReader ReturnDataReader(String sql)
    {
     OpenConn();
     OracleCommand command = new OracleCommand(sql,Connection);
     return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }
    #endregion

    #region 执行SQL语句，返回记录总数数
    /// <summary>
    /// 执行SQL语句，返回记录总数数
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>返回记录总条数</returns>
    public int GetRecordCount(string sql)
    {
     int recordCount = 0;
     OpenConn();
     OracleCommand command = new OracleCommand(sql,Connection);
     OracleDataReader dataReader = command.ExecuteReader();
     while(dataReader.Read())
     {
      recordCount++;
     }
     dataReader.Close();
     //    CloseConn();
     return recordCount;
    }
    #endregion

    #region 取当前序列,条件为seq.nextval或seq.currval
    /// <summary>
    /// 取当前序列
    /// </summary>
    /// <param name="seqstr"></param>
    /// <param name="table"></param>
    /// <returns></returns>
    public decimal GetSeq(string seqstr)
    {
     decimal seqnum = 0;
     string sql="select "+seqstr+" from dual";
     OpenConn();
     OracleCommand command = new OracleCommand(sql,Connection);
     OracleDataReader dataReader = command.ExecuteReader();
     if(dataReader.Read())
     {
      seqnum=decimal.Parse(dataReader[0].ToString());
     }
     dataReader.Close();
     //    CloseConn();
     return seqnum;
    }
    #endregion

    #region 执行SQL语句,返回所影响的行数
    /// <summary>
    /// 执行SQL语句,返回所影响的行数
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public int ExecuteSQL(string sql)
    {
     int Cmd=0;
     OpenConn();
     OracleCommand command = new OracleCommand(sql,Connection);
     try
     {
      Cmd =command.ExecuteNonQuery();
     }
     catch
     {

     }
     finally
     {
      //     CloseConn();
     }

     return Cmd;
    }
    #endregion

    //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    //　用hashTable对数据库进行insert,update,del操作,注意此时只能
    //   用默认的数据库连接"connstr"
    //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

    #region 根据表名及哈稀表自动插入数据库用法：Insert("test",ht)
    public int Insert(string TableName,Hashtable ht)
    {
     OracleParameter[] Parms=new OracleParameter[ht.Count];
     IDictionaryEnumerator et = ht.GetEnumerator();
     DataTable dt=GetTabType(TableName);
     System.Data.OracleClient.OracleType otype;
     int size=0;
     int i=0;

     while ( et.MoveNext() ) // 作哈希表循环
     {
      GetoType(et.Key.ToString().ToUpper(),dt,out otype,out size);
      System.Data.OracleClient.OracleParameter op=MakeParam(":"+
          et.Key.ToString(),otype,size,et.Value.ToString());
      Parms[i]=op; // 添加SqlParameter对象
      i=i+1;
     }
     string str_Sql=GetInsertSqlbyHt(TableName,ht); // 获得插入sql语句
     int val=ExecuteNonQuery(str_Sql,Parms);
     return val;
    }
    #endregion

    #region 根据相关条件对数据库进行更新操作用法：Update("test","Id=:Id",ht);
    public int Update(string TableName,string ht_Where, Hashtable ht)
    {
     OracleParameter[] Parms=new OracleParameter[ht.Count];
     IDictionaryEnumerator et = ht.GetEnumerator();
     DataTable dt=GetTabType(TableName);
     System.Data.OracleClient.OracleType otype;
     int size=0;
     int i=0;
     // 作哈希表循环
     while ( et.MoveNext() )
     {
      GetoType(et.Key.ToString().ToUpper(),dt,out otype,out size);
      System.Data.OracleClient.OracleParameter op=
          MakeParam(":"+et.Key.ToString(),otype,size,et.Value.ToString());
      Parms[i]=op; // 添加SqlParameter对象
      i=i+1;
     }
     string str_Sql=GetUpdateSqlbyHt(TableName,ht_Where,ht); // 获得插入sql语句
     int val=ExecuteNonQuery(str_Sql,Parms);
     return val;
    }
    #endregion

    #region del操作
       //,注意此处条件个数与hash里参数个数应该一致
       //用法：Del("test","Id=:Id",ht)
    public int Del(string TableName,string ht_Where,Hashtable ht)
    {
     OracleParameter[] Parms=new OracleParameter[ht.Count];
     IDictionaryEnumerator et = ht.GetEnumerator();
     DataTable dt=GetTabType(TableName);
     System.Data.OracleClient.OracleType otype;
     int i=0;
     int size=0;
     // 作哈希表循环
     while ( et.MoveNext() )
     {
      GetoType(et.Key.ToString().ToUpper(),dt,out otype,out size);
      System.Data.OracleClient.OracleParameter op=
          MakeParam(":"+et.Key.ToString(),et.Value.ToString());
      Parms[i]=op; // 添加SqlParameter对象
      i=i+1;
     }
     string str_Sql=GetDelSqlbyHt(TableName,ht_Where,ht); // 获得删除sql语句
     int val=ExecuteNonQuery(str_Sql,Parms);
     return val;
    }
    #endregion

    //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    //　＝＝＝＝＝＝＝＝上面三个操作的内部调用函数＝＝＝＝＝＝＝
    //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

    #region 根据哈稀表及表名自动生成相应insert语句(参数类型的)
    /// <summary>
    /// 根据哈稀表及表名自动生成相应insert语句
    /// </summary>
    /// <param name="TableName">要插入的表名</param>
    /// <param name="ht">哈稀表</param>
    /// <returns>返回sql语句</returns>
    public static string GetInsertSqlbyHt(string TableName,Hashtable ht)
    {
     string str_Sql="";
     int i=0;
     int ht_Count=ht.Count; // 哈希表个数
     IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
     string before="";
     string behide="";
     while ( myEnumerator.MoveNext() )
     {
      if (i==0)
      {
       before="("+myEnumerator.Key;
      }
      else if (i+1==ht_Count)
      {
       before=before+","+myEnumerator.Key+")";
      }
      else
      {
       before=before+","+myEnumerator.Key;
      }
      i=i+1;
     }
     behide=" Values"+before.Replace(",",",:").Replace("(","(:");
     str_Sql="Insert into "+TableName+before+behide;
     return str_Sql;
    }
    #endregion

    #region 根据表名，where条件，哈稀表自动生成更新语句(参数类型的)
    public static string GetUpdateSqlbyHt(string Table,
        string ht_Where,Hashtable ht)
    {
     string str_Sql="";
     int i=0;
     int ht_Count=ht.Count; // 哈希表个数
     IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
     while ( myEnumerator.MoveNext() )
     {
      if (i==0)
      {
       if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+
           "=:"+myEnumerator.Key).ToLower())==-1)
       {
        str_Sql=myEnumerator.Key+"=:"+myEnumerator.Key;
       }
      }
      else
      {
       if (ht_Where.ToString().ToLower().IndexOf((":"+
           myEnumerator.Key+" ").ToLower())==-1)
       {
        str_Sql=str_Sql+","+myEnumerator.Key+"=:"+myEnumerator.Key;
       }

      }
      i=i+1;
     }
     if (ht_Where==null || ht_Where.Replace(" ","")=="") // 更新时候没有条件
     {
      str_Sql="update "+Table+" set "+str_Sql;
     }
     else
     {
      str_Sql="update "+Table+" set "+str_Sql+" where "+ht_Where;
     }
     str_Sql=str_Sql.Replace("set ,","set ").Replace("update ,","update ");
     return str_Sql;
    }
    #endregion

    #region 根据表名，where条件，哈稀表自动生成del语句(参数类型的)
    public static string GetDelSqlbyHt(string Table,
        string ht_Where,Hashtable ht)
    {
     string str_Sql="";
     int i=0;

     int ht_Count=ht.Count; // 哈希表个数
     IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
     while ( myEnumerator.MoveNext() )
     {
      if (i==0)
      {
       if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+
           "=:"+myEnumerator.Key).ToLower())==-1)
       {
        str_Sql=myEnumerator.Key+"=:"+myEnumerator.Key;
       }
      }
      else
      {
       if (ht_Where.ToString().ToLower().IndexOf((":"+
           myEnumerator.Key+" ").ToLower())==-1)
       {
        str_Sql=str_Sql+","+myEnumerator.Key+"=:"+myEnumerator.Key;
       }

      }
      i=i+1;
     }
     if (ht_Where==null || ht_Where.Replace(" ","")=="") // 更新时候没有条件
     {
      str_Sql="Delete "+Table;
     }
     else
     {
      str_Sql="Delete "+Table+" where "+ht_Where;
     }
     return str_Sql;
    }
    #endregion

    #region 生成oracle参数
    /// <summary>
    /// 生成oracle参数
    /// </summary>
    /// <param name="ParamName">字段名</param>
    /// <param name="otype">数据类型</param>
    /// <param name="size">数据大小</param>
    /// <param name="Value">值</param>
    /// <returns></returns>
    public static OracleParameter MakeParam(string ParamName,
        System.Data.OracleClient.OracleType otype,int size,Object Value)
    {
     OracleParameter para=new OracleParameter(ParamName,Value);
     para.OracleType=otype;
     para.Size=size;
     return para;
    }
    #endregion

    #region 生成oracle参数
    public static OracleParameter MakeParam(string ParamName,string Value)
    {
     return new OracleParameter(ParamName, Value);
    }
    #endregion

    #region 根据表结构字段的类型和长度拼装oracle sql语句参数
    public static void GetoType(string key,DataTable dt,
        out System.Data.OracleClient.OracleType otype,out int size)
    {

     DataView dv=dt.DefaultView;
     dv.RowFilter="column_name='"+key+"'";
     string fType=dv[0]["data_type"].ToString().ToUpper();
     switch (fType)
     {
      case "DATE":
       otype= OracleType.DateTime;
       size=int.Parse(dv[0]["data_length"].ToString());
       break;
      case "CHAR":
       otype= OracleType.Char;
       size=int.Parse(dv[0]["data_length"].ToString());
       break;
      case "LONG":
       otype= OracleType.Double;
       size=int.Parse(dv[0]["data_length"].ToString());
       break;
      case "NVARCHAR2":
       otype= OracleType.NVarChar;
       size=int.Parse(dv[0]["data_length"].ToString());
       break;
      case "VARCHAR2":
       otype= OracleType.NVarChar;
       size=int.Parse(dv[0]["data_length"].ToString());
       break;
      default:
       otype= OracleType.NVarChar;
       size=100;
       break;
     }
    }
    #endregion

    #region动态取表里字段的类型和长度,此处没有动态用到connstr,是默认的！by/文少
    public System.Data.DataTable GetTabType(string tabnale)
    {
     string sql="select column_name,data_type,data_length "+
         "from all_tab_columns where table_name='"+tabnale.ToUpper()+"'";
     OpenConn();
     return (ReturnDataSet(sql,"dv")).Tables[0];

    }
    #endregion

    #region 执行sql语句
    public int ExecuteNonQuery(string cmdText, params OracleParameter[] cmdParms)
    {

     OracleCommand cmd = new OracleCommand();
     OpenConn();
     cmd.Connection=Connection;
     cmd.CommandText = cmdText;
     if (cmdParms != null)
     {
      foreach (OracleParameter parm in cmdParms)
       cmd.Parameters.Add(parm);
     }
     int val = cmd.ExecuteNonQuery();
     cmd.Parameters.Clear();
     //    conn.CloseConn();
     return val;
    }
    #endregion

    //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    //　＝＝＝＝＝＝＝＝＝内部调用函数完＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

   //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
   }
}

其他oracle 的常用方法：

#region 初始化常用变量
        /// <summary>
        /// 获取连接字符串
        /// </summary>
        private readonly string constr = Constant.CON_DATABASE_DB;

        private OracleConnection con;     //创建SQL连接
        private OracleCommand cmd;        //创建SQL命令对象
        private OracleDataAdapter sda;    //创建SQL数据适配器
        private OracleDataReader sdr;     //创建SQL数据阅读器
        private OracleParameter param;    //创建SQL参数
        private DataTable dt;             //创建数据表
        #endregion

        #region 创建并打开数据库连接
        /// <summary>
        /// 创建并打开数据库连接
        /// </summary>
        private void OpenCon()
        {
            con = new OracleConnection(constr);
            con.Open();
        }
        #endregion

        #region 关闭已打开的连接
        /// <summary>
        /// 关闭已打开的连接
        /// </summary>
        private void CloseCon()
        {
            if (con != null)
            {
                con.Close();
                con.Dispose();
            }
        }
        #endregion

        #region 返回数据集
        /// <summary>
        /// 返回数据集
        /// </summary>
        /// <param name="sqlstring">SQL语句</param>
        /// <returns>数据集</returns>
        public DataTable GetDt(string sqlString)
        {
            OpenCon();
            sda = new OracleDataAdapter(sqlString, con);
            dt = new DataTable();
            sda.Fill(dt);
            CloseCon();
            return dt;
        }
        #endregion

        #region 获取数据视图
        /// <summary>
        /// 获取数据视图
        /// </summary>
        /// <param name="sqlstring">SQL语句</param>
        /// <returns>数据视图</returns>
        public DataView GetDv(string sqlString)
        {
            return GetDt(sqlString).DefaultView;
        }
        #endregion

        #region 获取阅读器
        /// <summary>
        /// 获取阅读器
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <returns>阅读器</returns>
        public OracleDataReader GetDataReader(string sqlString)
        {
            OpenCon();
            cmd = new OracleCommand(sqlString, con);
            sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sdr;
        }
        #endregion



        #region 执行SQL语句
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        public int ExecSql(string sqlString)
        {
            OpenCon();
            int flag = -20;
            cmd = new OracleCommand(sqlString, con);
            try
            {
                flag = (int)cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Source + ex.Message);
            }
            CloseCon();
            return flag;
        }
        #endregion

        #region 执行带参SQL语句
        /// <summary>
        /// 执行带参SQL语句
        /// </summary>
        /// <param name="sqlString">SQL带参语句</param>
        /// <returns>执行SQL语句返回值</returns>
        public int ExecParmSql(string sqlString, OracleParameter[] parms)
        {
            cmd = BuildSqlCommand(sqlString, parms);
            cmd.ExecuteNonQuery();
            CloseCon();
            return (int)cmd.Parameters["ReturnValue"].Value;
        }
        #endregion

        #region 执行带参SQL语句,返回数据阅读对象
        /// <summary>
        /// 执行带参SQL语句,返回数据阅读对象
        /// </summary>
        /// <param name="sqlString">带参SQL语句</param>
        /// <param name="parms">SQL语句参数</param>
        /// <returns>数据阅读器</returns>
        public OracleDataReader ExecParmSqlReader(string sqlString, OracleParameter[] parms)
        {
            cmd = BuildSqlCommand(sqlString, parms);
            sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sdr;
        }
        #endregion

        #region 执行带参SQL语句
        /// <summary>
        /// 执行带参SQL语句
        /// </summary>
        /// <param name="sqlString">SQL带参语句</param>
        /// <param name="parms">参数</param>
        /// <returns>数据集</returns>
        public DataTable ExecParmSqlDs(string sqlString, OracleParameter[] parms)
        {
            cmd = BuildSqlCommand(sqlString, parms);
            sda = new OracleDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
        #endregion

        #region 执行存储过程
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <returns>返回值</returns>
        public int ExecProc(string procName)
        {
            cmd = BulidProcCommand(procName, null);
            cmd.ExecuteNonQuery();
            CloseCon();
            return (int)cmd.Parameters["ReturnValue"].Value;
        }
        #endregion

        #region 执行带参数的存储过程
        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="parms">存储过程参数</param>
        /// <returns>存储过程返回值</returns>
        public int ExecProc(string procName, OracleParameter[] parms)
        {
            cmd = BulidProcCommand(procName, parms);
            cmd.ExecuteNonQuery();
            CloseCon();
            return (int)cmd.Parameters["ReturnValue"].Value;
        }
        #endregion

        #region 执行存储过程
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="dataReader">数据阅读器</param>
        public void ExecProc(string procName, OracleDataReader dataReader)
        {
            cmd = BulidProcCommand(procName, null);
            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        #endregion

        #region 执行存储过程
        /// <summary>
        ///执行存储过程
        /// </summary>
        /// <param name="procName">过程名</param>
        /// <param name="parms">过程参数</param>
        /// <param name="dataReader">阅读器</param>
        public void ExecProc(string procName, OracleParameter[] parms, OracleDataReader dataReader)
        {
            cmd = BulidProcCommand(procName, parms);
            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        #endregion

        #region 执行存储过程
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">过程名</param>
        /// <param name="parms">过程参数</param>
        /// <returns>返回数据集</returns>
        public DataTable ExecProcDs(string procName, OracleParameter[] parms)
        {
            cmd = BulidProcCommand(procName, parms);
            sda = new OracleDataAdapter(cmd);
            dt = new DataTable();
            sda.Fill(dt);
            CloseCon();
            return dt;
        }
        #endregion

        #region 生成存储过程命令对象
        /// <summary>
        /// 生成存储过程命令对象
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="parms">存储过程参数</param>
        /// <returns>命令对象</returns>
        public OracleCommand BulidProcCommand(string procName, OracleParameter[] parms)
        {
            OpenCon();
            cmd = new OracleCommand(procName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            if (parms != null)
            {
                foreach (OracleParameter parameter in parms)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
            cmd.Parameters.Add(new OracleParameter("ReturnValue", OracleType.Int16, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return cmd;
        }
        #endregion

        #region 生成SQL命令对象
        /// <summary>
        /// 生成SQL命令对象
        /// </summary>
        /// <param name="sqlstr">SQL带参语句</param>
        /// <param name="parms">SQL语句参数</param>
        /// <returns>SQL命令对象</returns>
        public OracleCommand BuildSqlCommand(string sqlstr, OracleParameter[] parms)
        {
            OpenCon();
            cmd = new OracleCommand(sqlstr, con);
            cmd.CommandType = CommandType.Text;
            if (parms != null)
            {
                foreach (OracleParameter parameter in parms)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
            cmd.Parameters.Add(new OracleParameter("ReturnValue", OracleType.Int16, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return cmd;
        }

        /// <summary>
        /// 生成SQL命令对象
        /// </summary>
        /// <param name="sqlstr">SQL带参语句</param>
        /// <param name="parms">SQL语句参数</param>
        /// <returns>SQL命令对象</returns>
        public OracleCommand BuildSqlCommand(string sqlstr)
        {
            OpenCon();
            cmd = new OracleCommand(sqlstr, con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new OracleParameter("ReturnValue", OracleType.Int16, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return cmd;
        }
        #endregion

        #region 生成过程参数
        /// <summary>
        /// 生成过程参数
        /// </summary>
        /// <param name="proName">过程名</param>
        /// <param name="dbTpye">数据类型</param>
        /// <param name="size">数据大小</param>
        /// <param name="direction">参数方向</param>
        /// <param name="value">参数值</param>
        /// <returns>过程参数</returns>
        private OracleParameter MakeParm(string parmName, OracleType dbTpye, Int32 size, ParameterDirection direction, object value)
        {
            if (size > 0)
            {
                param = new OracleParameter(parmName, dbTpye, size);
            }
            else
            {
                param = new OracleParameter(parmName, dbTpye);
            }
            param.Direction = direction;
            if (!(direction == ParameterDirection.Output && value == null))
            {
                param.Value = value;
            }
            return param;
        }
        #endregion

        #region 生成存储过程输入参数
        /// <summary>
        /// 生成存储过程输入参数
        /// </summary>
        /// <param name="procName">过程名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">参数大小</param>
        /// <param name="value">参数值</param>
        /// <returns>过程参数</returns>
        public OracleParameter MakeInParm(string parmName, OracleType dbType, Int32 size, object value)
        {
            return MakeParm(parmName, dbType, size, ParameterDirection.Input, value);
        }
        #endregion

        #region 生成存储过程输出参数
        /// <summary>
        /// 生成存储过程输出参数
        /// </summary>
        /// <param name="procName">过程名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">参数大小</param>
        /// <param name="value">参数值</param>
        /// <returns>过程参数</returns>
        public OracleParameter MakOutParm(string parmName, OracleType dbType, Int32 size)
        {
            return MakeParm(parmName, dbType, size, ParameterDirection.Output, null);
        }
        #endregion

版权说明：
本网站凡注明“广州京杭原创”的皆为本站原创文章，如需转载请注明出处！
本网转载皆注明出处，遵循行业规范，如发现作品内容版权或其它问题的，请与我们联系处理！
欢迎扫描右侧微信二维码与我们联系。

·上一条：ping tracert nslookup命令的使用方法和相关参数判断 | ·下一条：VS2012 无法启动IIS Express Web服务器的解决方案