参考网址: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