怎么调用存储过程和函数?
1.增加操作:
public static void StockIns(string ps_SupplierName, int pn_ItemQty, string ps_ItemDesc, string ps_ComeDate,
string ps_AttachedFile, string ps_SupAddress, string ps_SupPhone, string ps_SupFax,
string ps_SupEmail, string ps_UserNo, ref string ps_HKStockItemNo)
{
// 创建参数
OracleParameter[] parms = new OracleParameter[]
{
new OracleParameter("ps_SupplierName", OracleType.VarChar, 200),
new OracleParameter("pn_ItemQty", OracleType.Int32, 10),
new OracleParameter("ps_ItemDesc", OracleType.VarChar, 400),
new OracleParameter("ps_ComeDate", OracleType.VarChar, 20),
new OracleParameter("ps_AttachedFile", OracleType.VarChar, 100),
new OracleParameter("ps_SupAddress", OracleType.VarChar, 200),
new OracleParameter("ps_SupPhone", OracleType.VarChar, 60),
new OracleParameter("ps_SupFax", OracleType.VarChar, 60),
new OracleParameter("ps_SupEmail", OracleType.VarChar, 100),
new OracleParameter("ps_UserNo", OracleType.VarChar, 20),
new OracleParameter("ps_HKStockItemNo", OracleType.VarChar, 20,ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Default, null)
};
// 给参数赋值
parms[0].Value = ps_SupplierName;
parms[1].Value = pn_ItemQty;
parms[2].Value = ps_ItemDesc;
parms[3].Value = ps_ComeDate;
parms[4].Value = ps_AttachedFile;
parms[5].Value = ps_SupAddress;
parms[6].Value = ps_SupPhone;
parms[7].Value = ps_SupFax;
parms[8].Value = ps_SupEmail;
parms[9].Value = ps_UserNo;
OraHelper.ExecuteNonQuery(OraHelper.connstr, CommandType.StoredProcedure, "KXSCMIII.PKG_PU_HKSTOCK.Pro_StockIns", parms);
ps_HKStockItemNo = parms[10].Value.ToString(); // 返回存货单号
}
在前台*.aspx.cs文件里面调用:
string strBill = "";
HKStockDataAccess.StockIns(txtSupplierName.Text.Trim(), Int32.Parse(txtItemQty.Text.Trim()), txtItemDesc.Text.Trim(),
txtComeDate.Text.Trim(), strServerFullPath, txtSupAddress.Text.Trim(), txtSupPhone.Text.Trim(),
txtSupFax.Text.Trim(), txtSupEmail.Text.Trim(), Session["userno"].ToString(),ref strBill);
2.修改操作:
public static void ClaimStock(string ps_HKStockItemNo, string ps_Buyer,
string ps_DeliNo, string ps_UserNo)
{
// 创建参数
OracleParameter[] parms = new OracleParameter[]
{
new OracleParameter("ps_HKStockItemNo",OracleType.VarChar,20),
new OracleParameter("ps_Buyer",OracleType.VarChar,20),
new OracleParameter("ps_DeliNo",OracleType.VarChar,20),
new OracleParameter("ps_UserNo",OracleType.VarChar,20)
};
// 给参数赋值
parms[0].Value = ps_HKStockItemNo;
parms[1].Value = ps_Buyer;
parms[2].Value = ps_DeliNo;
parms[3].Value = ps_UserNo;
OraHelper.ExecuteNonQuery(OraHelper.connstr, CommandType.StoredProcedure, "KXSCMIII.PKG_PU_HKSTOCK.Pro_ClaimStock", parms);
}
3.查询操作:
public static DataTable StockDetailGet(string ps_HKStockItemNo)
{
// 创建参数
OracleParameter[] parms = new OracleParameter[]
{
new OracleParameter("ps_HKStockItemNo",OracleType.VarChar,20),
new OracleParameter("returncur", OracleType.Cursor, 2000, ParameterDirection.Output, true,
0, 0, "", DataRowVersion.Default, null)
};
// 给参数赋值
parms[0].Value = ps_HKStockItemNo;
// 取记录集
DataSet ds = OraHelper.ExecuteDataset(OraHelper.connstr, CommandType.StoredProcedure,
"KXSCMIII.PKG_PU_HKSTOCK.Pro_StockDetailGet", parms);
return ds.Tables[0];
}
4.调用函数:
public static string BuyerNameGet(string ps_CardNo)
{
// 创建参数
OracleParameter[] parms = new OracleParameter[]
{
new OracleParameter("ps_CardNo", OracleType.VarChar, 20),
new OracleParameter("ps_FullName", OracleType.VarChar, 80, ParameterDirection.ReturnValue, true, 0, 0, "", DataRowVersion.Default, null)
};
// 给参数赋值
parms[0].Value = ps_CardNo;
OraHelper.ExecuteNonQuery(OraHelper.connstr, CommandType.StoredProcedure, "KXSCMIII.PKG_PU_HKSTOCK.Fun_BuyerNameGet", parms);
return parms[1].Value.ToString(); // 返回业务员的中文名
}