刚入手Oracle,存储过程和sqlserver的区别好大啊,折腾了半天也不清楚,发帖得当好多好心人大师们的帮助,实在感激啊为了给刚入门的好朋友们一点小小的指引,现把我折腾成功的关于“添加、修改、删除”的示例show一下,
目前还缺少列表GetList()、展示GetModel()以及验证Exists()的,欢迎大师们提供!web层:
//添加
//数据填充
SystemCodeModel SystemCodeModel = new SystemCodeModel();
SystemCodeModel.CodeLb = this.txtCodeLb.Text;
SystemCodeModel.LbName = this.txtLbName.Text;
SystemCodeModel.CodeValue = this.txtCodeValue.Text;
SystemCodeModel.CodeName = this.txtCodeName.Text; //调用BLL层Add
SystemCodeBLL.Add(SystemCodeModel);//修改:
//数据填充
SystemCodeModel SystemCodeModel = new SystemCodeModel();
SystemCodeModel.CodeLb = this.txtCodeLb.Text;
SystemCodeModel.LbName = this.txtLbName.Text;
SystemCodeModel.CodeValue = this.txtCodeValue.Text;
SystemCodeModel.CodeName = this.txtCodeName.Text; //调用BLL层Update
SystemCodeBLL.Update(SystemCodeModel);//删除: //删除事件
protected void GVList_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string CodeLb = this.GVList.DataKeys[e.RowIndex].Values[0].ToString();
string CodeValue = this.GVList.DataKeys[e.RowIndex].Values[1].ToString();
SystemCodeBLL.Delete(CodeLb, CodeValue);
//删除完之后重新绑定
this.Init_Data(ViewState["CodeLb"] == null ? "" : ViewState["CodeLb"].ToString());
}BLL层:
// 新增
public void Add(SystemCodeModel Model)
{
DAL.Add(Model);
}
//修改
public void Update(SystemCodeModel Model)
{
DAL.Update(Model);
}
//删除
public void Delete(string CodeLb, string CodeValue)
{
DAL.Delete(CodeLb, CodeValue);
}
DAL层://引用微软企业库
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
//建立数据库访问类
Database db = DatabaseFactory.CreateDatabase();
// 新增
public void Add(SystemCodeModel Model)
{
System.Data.Common.DbCommand sqlCommand = db.GetStoredProcCommand("SystemCode_ADD"); db.AddInParameter(sqlCommand, "CodeLb_in", DbType.String, Model.CodeLb);
db.AddInParameter(sqlCommand, "LbName_in", DbType.String, Model.LbName);
db.AddInParameter(sqlCommand, "CodeValue_in", DbType.String, Model.CodeValue);
db.AddInParameter(sqlCommand, "CodeName_in", DbType.String, Model.CodeName); db.ExecuteNonQuery(sqlCommand);
}
//修改
public void Update(SystemCodeModel Model)
{
System.Data.Common.DbCommand sqlCommand = db.GetStoredProcCommand("SystemCode_Update"); db.AddInParameter(sqlCommand, "CodeLb_in", DbType.String, Model.CodeLb);
db.AddInParameter(sqlCommand, "LbName_in", DbType.String, Model.LbName);
db.AddInParameter(sqlCommand, "CodeValue_in", DbType.String, Model.CodeValue);
db.AddInParameter(sqlCommand, "CodeName_in", DbType.String, Model.CodeName); db.ExecuteNonQuery(sqlCommand);
}
//删除
public void Delete(string CodeLb, string CodeValue)
{
System.Data.Common.DbCommand sqlCommand = db.GetStoredProcCommand("SystemCode_Delete"); db.AddInParameter(sqlCommand, "CodeLb_in", DbType.String, CodeLb);
db.AddInParameter(sqlCommand, "CodeValue_in", DbType.String, CodeValue); db.ExecuteNonQuery(sqlCommand);
}
Oracle存储过程://添加:
CREATE OR REPLACE PROCEDURE SYSTEMCODE_ADD (
Codelb_in varchar2 ,
LbName_in varchar2 ,
CodeValue_in varchar2 ,
CodeName_in varchar2 )
AS
begin
INSERT into Systemcode (Codelb,LbName,CodeValue,CodeName)
values(Codelb_in,LbName_in,CodeValue_in,CodeName_in);
end SystemCode_ADD;//修改:
CREATE OR REPLACE PROCEDURE SYSTEMCODE_Update
/*---Update更新code代码表---*/
(CODELB_in VARCHAR2,
LBNAME_in VARCHAR2,
CODEVALUE_in VARCHAR2,
CODENAME_in VARCHAR2
)
AS
BEGIN
UPDATE SYSTEMCODE SET
LBNAME = LBNAME_in ,CODENAME = CODENAME_in
WHERE CODELB = CODELB_in and CODEVALUE = CODEVALUE_in;
end SYSTEMCODE_Update;
///删除
CREATE OR REPLACE PROCEDURE SystemCode_Delete
/*---Delete删除code代码表---*/
(Codelb_in varchar2,
CodeValue_in varchar2
)
AS
begin
Delete From Systemcode
WHERE Codelb= Codelb_in and CodeValue= CodeValue_in;
end SystemCode_Delete;
/commit;
目前还缺少列表GetList()、展示GetModel()以及验证Exists()的,欢迎大师们提供!web层:
//添加
//数据填充
SystemCodeModel SystemCodeModel = new SystemCodeModel();
SystemCodeModel.CodeLb = this.txtCodeLb.Text;
SystemCodeModel.LbName = this.txtLbName.Text;
SystemCodeModel.CodeValue = this.txtCodeValue.Text;
SystemCodeModel.CodeName = this.txtCodeName.Text; //调用BLL层Add
SystemCodeBLL.Add(SystemCodeModel);//修改:
//数据填充
SystemCodeModel SystemCodeModel = new SystemCodeModel();
SystemCodeModel.CodeLb = this.txtCodeLb.Text;
SystemCodeModel.LbName = this.txtLbName.Text;
SystemCodeModel.CodeValue = this.txtCodeValue.Text;
SystemCodeModel.CodeName = this.txtCodeName.Text; //调用BLL层Update
SystemCodeBLL.Update(SystemCodeModel);//删除: //删除事件
protected void GVList_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string CodeLb = this.GVList.DataKeys[e.RowIndex].Values[0].ToString();
string CodeValue = this.GVList.DataKeys[e.RowIndex].Values[1].ToString();
SystemCodeBLL.Delete(CodeLb, CodeValue);
//删除完之后重新绑定
this.Init_Data(ViewState["CodeLb"] == null ? "" : ViewState["CodeLb"].ToString());
}BLL层:
// 新增
public void Add(SystemCodeModel Model)
{
DAL.Add(Model);
}
//修改
public void Update(SystemCodeModel Model)
{
DAL.Update(Model);
}
//删除
public void Delete(string CodeLb, string CodeValue)
{
DAL.Delete(CodeLb, CodeValue);
}
DAL层://引用微软企业库
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
//建立数据库访问类
Database db = DatabaseFactory.CreateDatabase();
// 新增
public void Add(SystemCodeModel Model)
{
System.Data.Common.DbCommand sqlCommand = db.GetStoredProcCommand("SystemCode_ADD"); db.AddInParameter(sqlCommand, "CodeLb_in", DbType.String, Model.CodeLb);
db.AddInParameter(sqlCommand, "LbName_in", DbType.String, Model.LbName);
db.AddInParameter(sqlCommand, "CodeValue_in", DbType.String, Model.CodeValue);
db.AddInParameter(sqlCommand, "CodeName_in", DbType.String, Model.CodeName); db.ExecuteNonQuery(sqlCommand);
}
//修改
public void Update(SystemCodeModel Model)
{
System.Data.Common.DbCommand sqlCommand = db.GetStoredProcCommand("SystemCode_Update"); db.AddInParameter(sqlCommand, "CodeLb_in", DbType.String, Model.CodeLb);
db.AddInParameter(sqlCommand, "LbName_in", DbType.String, Model.LbName);
db.AddInParameter(sqlCommand, "CodeValue_in", DbType.String, Model.CodeValue);
db.AddInParameter(sqlCommand, "CodeName_in", DbType.String, Model.CodeName); db.ExecuteNonQuery(sqlCommand);
}
//删除
public void Delete(string CodeLb, string CodeValue)
{
System.Data.Common.DbCommand sqlCommand = db.GetStoredProcCommand("SystemCode_Delete"); db.AddInParameter(sqlCommand, "CodeLb_in", DbType.String, CodeLb);
db.AddInParameter(sqlCommand, "CodeValue_in", DbType.String, CodeValue); db.ExecuteNonQuery(sqlCommand);
}
Oracle存储过程://添加:
CREATE OR REPLACE PROCEDURE SYSTEMCODE_ADD (
Codelb_in varchar2 ,
LbName_in varchar2 ,
CodeValue_in varchar2 ,
CodeName_in varchar2 )
AS
begin
INSERT into Systemcode (Codelb,LbName,CodeValue,CodeName)
values(Codelb_in,LbName_in,CodeValue_in,CodeName_in);
end SystemCode_ADD;//修改:
CREATE OR REPLACE PROCEDURE SYSTEMCODE_Update
/*---Update更新code代码表---*/
(CODELB_in VARCHAR2,
LBNAME_in VARCHAR2,
CODEVALUE_in VARCHAR2,
CODENAME_in VARCHAR2
)
AS
BEGIN
UPDATE SYSTEMCODE SET
LBNAME = LBNAME_in ,CODENAME = CODENAME_in
WHERE CODELB = CODELB_in and CODEVALUE = CODEVALUE_in;
end SYSTEMCODE_Update;
///删除
CREATE OR REPLACE PROCEDURE SystemCode_Delete
/*---Delete删除code代码表---*/
(Codelb_in varchar2,
CodeValue_in varchar2
)
AS
begin
Delete From Systemcode
WHERE Codelb= Codelb_in and CodeValue= CodeValue_in;
end SystemCode_Delete;
/commit;
DAL层:
//得到列表
public DataSet GetList(string CodeLb)
{
string strSql = "select * FROM SystemCode";
if (CodeLb != null && CodeLb.Trim() != "")
{
strSql += " where CodeLb='" + CodeLb + "'";
}
System.Data.Common.DbCommand sqlCommand = db.GetSqlStringCommand(strSql);
return db.ExecuteDataSet(sqlCommand);
} //得到Model
public SystemCodeModel GetModel(string CodeLb, string CodeValue)
{
string strSql = "select * FROM SystemCode";
if (CodeLb != null && CodeLb.Trim() != "")
{
strSql += " where CodeLb='" + CodeLb + "' and CodeValue='" + CodeValue + "'";
}
System.Data.Common.DbCommand sqlCommand = db.GetSqlStringCommand(strSql);
IDataReader dr = db.ExecuteReader(sqlCommand);
SystemCodeModel Model = new SystemCodeModel();
if (dr.Read())
{
Model.CodeLb = dr["CodeLb"].ToString();
Model.LbName = dr["LbName"].ToString();
Model.CodeValue = dr["CodeValue"].ToString();
Model.CodeName = dr["CodeName"].ToString();
}
dr.Close();
dr.Dispose(); return Model;
}
以下是我尝试用存储过程,出现错误:
//得到列表
public DataSet GetList(string CodeLb)
{
System.Data.Common.DbCommand sqlCommand = db.GetStoredProcCommand("SystemCode_GetList");
db.AddInParameter(sqlCommand, "CodeLb_in", DbType.String, CodeLb);
return db.ExecuteDataSet(sqlCommand);
}
列表对应的存储过程:
--列表
CREATE OR REPLACE PROCEDURE "ADMIN"."SYSTEMCODE_GETLIST"
(CodeLb_in varchar2
)
is
cursor cur is
select *
From SystemCode
WHERE CodeLb =nvl( CodeLb_in,CodeLb)
order by codelb;
begin
for rec in cur loop
/*这个地方不会写啊*/
end loop;
end SystemCode_GetList;验证的存储过程:
CREATE OR REPLACE PROCEDURE SYSTEMCODE_EXISTS (
CODELB_in VARCHAR2,
CodeValue_in VARCHAR2,
count_row out number
)
AS
TempID Number;
BEGIN
SELECT count(1) into TempID FROM SYSTEMCODE
WHERE CODELB = CODELB_in and CodeValue=CodeValue_in;
IF TempID = 0 then
count_row := 0;
ELSE
count_row := 1;
end IF;
END;
/
验证对应的DAL层: //是否已经存在
public bool Exists(string CodeLb, string CodeValue)
{
System.Data.Common.DbCommand sqlCommand = db.GetStoredProcCommand("SYSTEMCODE_EXISTS");
db.AddInParameter(sqlCommand, "CodeLb_in", DbType.String, CodeLb);
db.AddInParameter(sqlCommand, "CodeValue_in", DbType.String, CodeValue);
db.AddOutParameter(sqlCommand, "count_row", DbType.String, 40); IDataReader dr = db.ExecuteReader(sqlCommand); string result = null;
if (dr.Read())
{
result = dr["TempID"].ToString();
}
dr.Close();
dr.Dispose(); if (string.IsNullOrEmpty(result))
return false;
else
return true;
}提示的错误:
应用程序中的服务器错误。
--------------------------------------------------------------------------------ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'SYSTEMCODE_EXISTS' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Data.OracleClient.OracleException: ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'SYSTEMCODE_EXISTS' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
源错误:
行 112: db.AddOutParameter(sqlCommand, "count_row", DbType.String, 40);
行 113:
行 114: IDataReader dr = db.ExecuteReader(sqlCommand);
行 115:
行 116: string result = null;
源文件: E:\www\FastTrace\DAL\SystemManage\SystemCodeDAL.cs 行: 114
CODELB_in VARCHAR2,
CodeValue_in VARCHAR2,
count_row out number(5) -->这里没有定义长度
)
AS
TempID Number;
BEGIN
..............
END SYSTEMCODE_EXISTS; -->这里试试看
count_row out number(5) -->这里没有定义长度
这个number地方不需要定义长度,定义了就会编译出错,
END SYSTEMCODE_EXISTS; -->这里
这个end之后的是可以省略的