|
Posted on
2006-03-28 08:21
剑廿三
阅读( 273)
评论()
收藏
举报
Oracle 存储过程、C# 调用 等的实践。
工具:PLSQL Developer 6.05
包头定义:
create
or
replace
package PACKAGES_OPERLOG
is
--
Author : STEPHEN WANG JIA JIAN
--
Created : 2006-3-27 14:32:40
--
Purpose :
--
Public type declarations
TYPE logcur
IS
REF
CURSOR
;
--
Public function and procedure declarations
PROCEDURE
GetAllOperLog(l_cursor OUT logcur);
PROCEDURE
CreateLog(p_UserID
char
,p_OperateTime date,p_IP
varchar2
,p_OperateType
varchar2
,p_OperateDesc
varchar2
,p_LogID
char
);
PROCEDURE
UpdateLogByLogID(p_UserID
char
,p_OperateTime date,p_IP
varchar2
,p_OperateType
varchar2
,p_OperateDesc
varchar2
,p_LogID
char
);

end
PACKAGES_OPERLOG;
包体定义:
create
or
replace
package body PACKAGES_OPERLOG
is
--
Function and procedure implementations
PROCEDURE
GetAllOperLog(l_cursor OUT logcur)
IS
BEGIN
OPEN
l_cursor
FOR
SELECT
U.ZHNAME
AS
UserName,L.OPERATETIME
AS
OPERATETIME,L.IP
AS
IP,L.OPERATETYPE
AS
OPERATENAME,L.OPERATEDESC
AS
OPERATEDESC,L.LOGID
AS
LOGID
FROM
OPER_LOG L,GDTEL_USER U
WHERE
L.USERID
=
U.USERID;
END
GetAllOperLog;

--
Create Log
PROCEDURE
CreateLog(p_UserID
char
,p_OperateTime date,p_IP
varchar2
,p_OperateType
varchar2
,p_OperateDesc
varchar2
,p_LogID
char
)
IS
v_UserID
char
(
32
):
=
p_UserID;
v_OperateTime date:
=
p_OperateTime;
v_IP
varchar2
(
50
):
=
p_IP;
v_OperateType
varchar2
(
255
):
=
p_OperateType;
v_OperateDesc
varchar2
(
255
):
=
p_OperateDesc;
v_LogID
char
(
32
):
=
p_LogID;
BEGIN
insert
into
OPER_LOG
values
(v_UserID,v_OperateTime,v_IP,v_OperateType,v_OperateDesc,v_LogID);
END
CreateLog;
--
Update Log by LogID
PROCEDURE
UpdateLogByLogID(p_UserID
char
,p_OperateTime date,p_IP
varchar2
,p_OperateType
varchar2
,p_OperateDesc
varchar2
,p_LogID
char
)
IS
v_UserID
char
(
32
):
=
p_UserID;
v_OperateTime date:
=
p_OperateTime;
v_IP
varchar2
(
50
):
=
p_IP;
v_OperateType
varchar2
(
255
):
=
p_OperateType;
v_OperateDesc
varchar2
(
255
):
=
p_OperateDesc;
v_LogID
char
(
32
):
=
p_LogID;
BEGIN
update
OPER_LOG
set
OPER_LOG.USERID
=
v_UserID, OPER_LOG.OPERATETIME
=
v_OperateTime, OPER_LOG.IP
=
v_IP, OPER_LOG.OPERATETYPE
=
v_OperateType, OPER_LOG.OPERATEDESC
=
v_OperateDesc
where
OPER_LOG.LOGID
=
v_LogID;
END
UpdateLogByLogID;
end
PACKAGES_OPERLOG;
C# 调用代码:(使用 ADOHelper)
#### 日志 ####
#region
#### 日志 ####
public
bool
CreateLog(LogEntity Log)

{
AdoHelper helper
=
AdoHelper.CreateHelper();

 IDataParameter[] ps
=
new
IDataParameter[
6
]
{
helper.GetParameter(
"
p_USERID
"
,Log.UserID),
helper.GetParameter(
"
p_OPERATETIME
"
,Log.OperDate),
helper.GetParameter(
"
p_IP
"
,Log.Ip),
helper.GetParameter(
"
p_OPERATETYPE
"
,Log.OperName),
helper.GetParameter(
"
p_OPERATEDESC
"
,Log.OperDesc),
helper.GetParameter(
"
p_LOGID
"
,Log.LogID),
}
;

helper.ExecuteNonQuery (ConnectionString,CommandType.StoredProcedure,
"
PACKAGES_OPERLOG.CreateLog
"
,ps);
return
true
;
}
public
bool
UpdateLog(LogEntity Log)

{
AdoHelper helper
=
AdoHelper.CreateHelper();

 IDataParameter[] ps
=
new
IDataParameter[
6
]
{
helper.GetParameter(
"
p_USERID
"
,Log.UserID),
helper.GetParameter(
"
p_OPERATETIME
"
,Log.OperDate),
helper.GetParameter(
"
p_IP
"
,Log.Ip),
helper.GetParameter(
"
p_OPERATETYPE
"
,Log.OperName),
helper.GetParameter(
"
p_OPERATEDESC
"
,Log.OperDesc),
helper.GetParameter(
"
p_LOGID
"
,Log.LogID),
}
;

helper.ExecuteNonQuery (ConnectionString,CommandType.StoredProcedure,
"
PACKAGES_OPERLOG.UpdateLogByLogID
"
,ps);
return
true
;
}
public
bool
DeleteLog(
string
LogID)

{
return
DeleteEntity(LogID,
"
OPER_LOG
"
,
"
LOGID
"
);
}
public
bool
DeleteLogList(ArrayList logIDList)

{
return
DeleteEntityList(logIDList,
"
OPER_LOG
"
,
"
LOGID
"
);
}
public
bool
ClearLog()

{
return
ClearEntity(
"
OPER_LOG
"
);
}
public
DataSet GetLogList()

{
DataSet ds
=
new
DataSet ();

AdoHelper helper
=
AdoHelper.CreateHelper ();
IDataParameter [] ps
=
helper.GetSpParameterSet (ConnectionString,
"
PACKAGES_OPERLOG.GETALLOPERLOG
"
);
 helper.FillDataset(ConnectionString,
"
PACKAGES_OPERLOG.GETALLOPERLOG
"
,ds,
new
string
[]
{
"
OperLog
"
}
,ps);

return
ds;
}
#endregion
ADOHelper 的文件头注释: // ===============================================================================
// Microsoft Data Access Application Block for .NET 3.0
//
// AdoHelper.cs
//
// This file contains an abstract implementations of the AdoHelper class.
//
// For more information see the Documentation.
// ===============================================================================
// Release history
// VERSION DESCRIPTION
// 2.0 Added support for FillDataset, UpdateDataset and "Param" helper methods
// 3.0 New abstract class supporting the same methods using ADO.NET interfaces
//
// ===============================================================================
// Copyright (C) 2000-2001 Microsoft Corporation
// All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
// FITNESS FOR A PARTICULAR PURPOSE.
// ==============================================================================
|