• C# 执行存储过程 方法


    参考网址:https://www.cnblogs.com/hzy168/archive/2013/04/01/2992621.html

    cs代码如下:

      1 #region 通过检验类别提取检验项目
      2     protected void Search1_Click(object sender, EventArgs e)
      3     {
      4 
      5         string sqlLab = " Status='0' ";
      6         if (DDL_Class.SelectedValue != "--项目类别--")
      7         {
      8             sqlLab += " and item_class='" + DDL_Class.SelectedValue + "'";
      9         }
     10         if (TB_LabName.Text.Trim().Length > 0)
     11         {
     12             sqlLab += " and item_name like '%" + TB_LabName.Text.Trim() + "%'";
     13         }
     14         dtItem = Item.GetList(sqlLab + "  order by Item_name ").Tables["ds"];
     15 
     16         string itemcodes = "";//需要获取价格的项目编码组合
     17         foreach (DataRow myrow in dtItem.Rows)
     18         {
     19             itemcodes += myrow["Item_Code"].ToString() + "^";
     20         }
     21         itemcodes = itemcodes.Substring(0, itemcodes.Length - 1);        
     22         getcodelist(itemcodes, 5);//分批执行存储过程
     23         RBL_List.DataSource = dtitem.DefaultView;
     24         RBL_List.DataTextField = "item_codeAnPrice";
     25         RBL_List.DataValueField = "item_code";
     26         RBL_List.DataBind();
     27     }
     28 
     29     /// <summary>
     30     /// 分批执行存储过程
     31     /// 因为存储过程不允许返回的字符串的长度超过4000
     32     /// </summary>
     33     /// <param name="itemcodes"></param>
     34     /// <param name="n"></param>
     35     public void getcodelist(string itemcodes, int n)
     36     {
     37         string items = "";
     38         if (Regex.Matches(itemcodes, "\^").Count > n)
     39         {
     40             string[] b = itemcodes.Split('^');
     41             for (int i = 0; i < n; i++)
     42             {
     43                 items += b[i] + "^";
     44             }
     45             items = items.Substring(0, items.Length - 1);
     46             //执行存储过程
     47             getStoredProcName(items, dtitem);
     48             //递归
     49             getcodelist(itemcodes.Substring(items.Length - 1, itemcodes.Length - items.Length), n);
     50         }
     51         else
     52         {
     53             items = itemcodes;
     54             //执行存储过程
     55             getStoredProcName(items, dtitem);
     56         }
     57     }
     58 
     59     /// <summary>
     60     /// 执行存储过程
     61     /// </summary>
     62     /// <param name="items"></param>
     63     /// <param name="dtitem"></param>
     64     public void getStoredProcName(string items, DataTable dtitem)
     65     {
     66         //获取项目对应的价格
     67         //存储过程的参数声明  
     68         OracleParameter[] parameters ={
     69          new OracleParameter("inputExecType",OracleType.Number),
     70            new OracleParameter("inputPara",OracleType.NVarChar,1000),
     71          new OracleParameter("returnCode",OracleType.Number) ,
     72                                              new OracleParameter("returnTxt",OracleType.NVarChar,1000)
     73         };
     74         parameters[0].Value = 0;
     75         parameters[0].Direction = ParameterDirection.Input;
     76         parameters[1].Value = items;
     77         parameters[1].Direction = ParameterDirection.Input;
     78         parameters[2].Direction = ParameterDirection.Output;
     79         parameters[3].Direction = ParameterDirection.Output;
     80         RunProcedure("StoredProcName", parameters);
     81         if (parameters[2].Value.ToString() == "1")
     82         {
     83             string itemprices = parameters[3].Value.ToString();//ROW:Search^1^C20130322001^B型钠尿肽前体测定(心衰全定量)^224^血清
     84             itemprices = itemprices.Replace("ROW:Search", "|");
     85             string[] itemall = itemprices.Split('|');//^1^C20130322001^B型钠尿肽前体测定(心衰全定量)^224^血清
     86             for (int i = 1; i < itemall.Length; i++)
     87             {
     88                 DataRow newRow;
     89                 string[] item = itemall[i].Split('^');
     90                 newRow = dtitem.NewRow();
     91                 newRow["item_code"] = item[2];//项目编号
     92                 newRow["item_name"] = item[3];//项目名字
     93                 newRow["item_price"] = item[4];//项目价格
     94                 newRow["item_codeAnPrice"] = item[3].ToString() + "    " + item[4] + "";
     95                 dtitem.Rows.Add(newRow);
     96                 dtitem.AcceptChanges();//这一句很重要,否则每次新增的纪录会覆盖久的纪录
     97             }
     98         }
     99     }  
    100 
    101     private void RunProcedure(string storedProcName, OracleParameter[] parameters)
    102     {
    103 
    104         OracleConnection oc = new OracleConnection("Data Source=DataSource;Persist Security Info=True;User ID=username;Password=pas;Unicode=True;Max Pool Size = 512;");
    105         oc.Open();
    106         OracleCommand ocm = oc.CreateCommand();
    107         ocm.CommandText = storedProcName;//声明存储过程名 
    108         ocm.CommandType = CommandType.StoredProcedure;
    109         foreach (OracleParameter parameter in parameters)
    110         {
    111             ocm.Parameters.Add(parameter);
    112         }
    113         ocm.ExecuteNonQuery();//执行存储过程 
    114         oc.Close();
    115     }
    116 
    117     #endregion
  • 相关阅读:
    xScrapBook
    使用STL仿函数和判断式来降低复杂性并改善可读[转]
    C++ 开源程序库[转]
    资源泄漏的悲剧
    Excel导入的HDR=YES; IMEX=1详解
    largeint.lib
    共享刚写的简单DirectUI库 只实现了思想
    document.body.scrollTop的值总为零的解决办法
    CDCHandle谨慎使用
    C++中std::tr1::function和bind 组件的使用
  • 原文地址:https://www.cnblogs.com/chenpanpan/p/14415112.html
Copyright © 2020-2023  润新知