csharp: Oracle Stored Procedure DAL using ODP.NET
2024-04-06 18:32:30
paging : http://www.codeproject.com/Articles/44858/Custom-Paging-GridView-in-ASP-NET-Oracle
https://github.com/MarcosMeli/FileHelpers/
http://www.codeproject.com/Articles/685310/Simple-and-fast-CSV-library-in-Csharp
http://www.codeproject.com/Tips/665519/Writing-a-DataTable-to-a-CSV-File?msg=5258197
https://github.com/JoshClose/CsvHelper
http://dba-oracle.com/t_display_oracle_stored_procedures.htm
oracle database metadata
https://oracle-base.com/articles/9i/dbms_metadata
https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBHHHBG
https://docs.oracle.com/cd/B19306_01/appdev.102/b14294/metadata.htm
http://www.rgagnon.com/javadetails/java-handle-utf8-file-with-bom.html
https://sqlmetadata.codeplex.com/
https://github.com/dlemstra
https://github.com/dlemstra/Magick.NET
https://github.com/dlemstra/QRCodeCore
https://github.com/JimBobSquarePants/ImageSharp
http://magick.codeplex.com/SourceControl/latest#Readme.md
https://graphicsmagick.codeplex.com/SourceControl/latest#Readme.txt
---top 5 适应于 Oracle,Sql server,DB2
select * from (select dense_rank() over (order by BookKindID) as dr,b.* from BookKindList b) x where dr<=5; --分页 Oracle
select * from
(select t1.*,rownum rn from (select * from BookKindList) t1 where rownum<=12)
where rn>=8; --分页 适应于 Oracle,Sql server,DB2 select * from (select row_number() over (order by BookKindID) as rn,b.* from BookKindList b) x where rn between 1 and 5; select * from (select row_number() over (order by BookKindID) as rn,b.* from BookKindList b) x where rn between 6 and 10;
Oracle sql:
--书分类目录kind
-- Geovin Du
create table BookKindList
(
BookKindID INT PRIMARY KEY,
BookKindName nvarchar2(500) not null,
BookKindParent INT null,
BookKindCode varchar(100) ---編號
);
--序列创建 drop SEQUENCE BookKindList_SEQ; CREATE SEQUENCE BookKindList_SEQ
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
NOCACHE; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE --自增长触发器
drop trigger BookKindList_ID_AUTO; create or replace trigger BookKindList_ID_AUTO
before insert on BookKindList --BookKindList 是表名
for each row
declare
nextid number;
begin
IF :new.BookKindID IS NULL or :new.BookKindID=0 THEN --BookKindID是列名
select BookKindList_SEQ.Nextval --BookKindList_SEQ正是刚才创建的
into nextid
from dual;
:new.BookKindID:=nextid;
end if;
end; -- 添加
drop PROCEDURE proc_Insert_BookKindList; CREATE OR REPLACE PROCEDURE proc_Insert_BookKindList
(
temTypeName nvarchar2,
temParent number
)
AS
ncount number;
begin
--SELECT COUNT (*) INTO ncount FROM BookKindList fm1 where EXISTS (SELECT BookKindName from BookKindList fm2 where fm2.BookKindName=temTypeName);--判斷是否存
SELECT count(*) INTO ncount FROM BookKindList where BookKindName=temTypeName;
if ncount<=0 then
begin
INSERT INTO BookKindList (BookKindName,BookKindParent) VALUES(temTypeName,temParent);
commit;
end;
else
begin
SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=temTypeName;
dbms_output.put_line('存在相同的记录,添加不成功!'||ncount);
end;
end if;
Exception
When others then
dbms_output.put_line('存在问题,添加不成功!'||ncount);
Rollback;
end proc_Insert_BookKindList; --测试 oracle 11g 涂聚文 20150526
exec proc_Insert_BookKindList ('油彩画',3); drop PROCEDURE proc_Insert_BookKindOut; drop PROCEDURE procInsertBookKindOut;
-- 添加有返回值
CREATE OR REPLACE PROCEDURE proc_Insert_BookKindOut --添加返回ID
(
temTypeName nvarchar2,
temParent int,
temId out int
)
AS
ncount number;
reid number;
begin
--SELECT COUNT (*) INTO ncount FROM BookKindList fm1 where EXISTS (SELECT BookKindName from BookKindList fm2 where fm2.BookKindName=temTypeName);--判斷是否存
SELECT count(*) INTO ncount FROM BookKindList where BookKindName=temTypeName;
if ncount<=0 then
begin
--INSERT INTO BookKindList (BookKindID,BookKindName,BookKindParent) VALUES(BookKindList_SEQ.nextval,temTypeName,temParent);
INSERT INTO BookKindList (BookKindName,BookKindParent) VALUES(temTypeName,temParent);
select BookKindList_SEQ.currval into reid from dual;
temId:=reid;
dbms_output.put_line('添加成功!'||temId);
commit;
end;
else
begin
SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=temTypeName;
dbms_output.put_line('存在相同的记录,添加不成功!'||ncount);
temId:=0;
end;
end if;
Exception
When others then
begin
dbms_output.put_line('存在问题,添加不成功!'||ncount);
temId:=0;
Rollback;
end;
end proc_Insert_BookKindOut; --测试 oracle 11g 涂聚文 20150526
declare
mid number:=0;
nam nvarchar2(100):='黑白画';
par number:=3;
begin
--proc_Insert_BookKindOut(nam in nvarchar2,par in int,mid in out int);
proc_Insert_BookKindOut(nam,par ,mid);
if mid>0 then
dbms_output.put_line('添加成功!输出参数:'||mid);
else
dbms_output.put_line('存在相同的记录,添加不成功!输出参数:'||mid);
end if;
end; --修改
CREATE OR REPLACE PROCEDURE procUpdateBookKindList (
p_id IN INT,--BookKindList.BookKindID%TYPE,
p_name IN nvarchar2,--BookKindList.BookKindName%TYPE,
p_parent IN INT,--BookKindList.BookKindParent%TYPE,
p_code IN varchar--BookKindList.BookKindCode%TYPE
)
IS
ncount number;
BEGIN
SELECT count(*) INTO ncount FROM BookKindList where BookKindName=p_name;
if ncount<=0 then
begin
UPDATE BookKindList SET BookKindName=p_name,BookKindParent=p_parent,BookKindCode=p_code WHERE BookKindID=p_id;
COMMIT;
end;
else
begin
SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=p_name;
dbms_output.put_line('存在相同的记录,修改不成功!'||ncount);
end;
end if;
END procUpdateBookKindList; --测试
begin
procUpdateBookKindList(8,'哲学',1,'Geovin Du');
end; --删除
CREATE OR REPLACE PROCEDURE procDeleteBookKindList(p_BookKindID IN BookKindList.BookKindID%TYPE)
IS
BEGIN DELETE BookKindList where BookKindID = p_BookKindID;
COMMIT;
END; ---一条记录
--创建包:
create or replace package pack_BookKindId is
type cur_BookKindId is ref cursor;
end pack_BookKindId; --创建存储过程
create or replace procedure procSelectBookKindList
(p_id in int,p_cur out pack_BookKindId.cur_BookKindId)
is
v_sql varchar2(400);
begin if p_id = 0 then --0 查询所有
open p_cur for select * from BookKindList;
else
v_sql := 'select * from BookKindList where BookKindID =: p_id';
open p_cur for v_sql using p_id;
end if;
end procSelectBookKindList; --创建包以游标的形式返回BookKindList表的所有记录结果集 drop package pkg_Select_BookKindListAll; drop procedure proc_Select_BookKindListAll; create or replace package pkgSelectBookKindListAll is
-- Author : geovindu
type mycur is ref cursor;
procedure procSelectBookKindListAll(cur_return out mycur);
end pkgSelectBookKindListAll; create or replace package body pkgSelectBookKindListAll is
-- Function and procedure implementations
procedure procSelectBookKindListAll(cur_return out mycur)
is
begin
open cur_return for select * from BookKindList; end procSelectBookKindListAll; end pkgSelectBookKindListAll; -- 测试包和存储过程查询表中所有内容
declare
--定义游标类型的变量
cur_return pkgSelectBookKindListAll.mycur;
--定义行类型
pdtrow BookKindList%rowtype;
begin
--执行存储过程
pkgSelectBookKindListAll.procSelectBookKindListAll(cur_return);
--遍历游标中的数据
LOOP
--取当前行数据存入pdtrow
FETCH cur_return INTO pdtrow;
--如果未获取数据就结束循环
EXIT WHEN cur_return%NOTFOUND;
--输出获取到的数据
DBMS_OUTPUT.PUT_LINE (pdtrow.BookKindID||'，'||pdtrow.BookKindName);
END LOOP;
CLOSE cur_return;
end;
csharp code:
/// <summary>
/// 20160918 涂聚文
/// Geovin Du
/// </summary>
public class BookKindListDAL : IBookKindList
{
//private static string connectionString =@"DATA SOURCE=oracle11g;USER ID=geovin;password=geovindu;";
///<summary>
/// 追加记录
///</summary>
///<param name="BookKindListInfo"></param>
///<returns></returns>
public int InsertBookKindList(BookKindListInfo bookKindList)
{
int ret = 0;
try
{
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("temTypeName",OracleDbType.NVarchar2,1000),
new OracleParameter("temParent",OracleDbType.Int32,4),
};
par[0].Value = bookKindList.BookKindName;
par[0].Direction = ParameterDirection.Input;
par[1].Value = bookKindList.BookKindParent;
par[1].Direction = ParameterDirection.Input;
ret = OracleHelper.ExecuteSql("proc_Insert_BookKindList", CommandType.StoredProcedure, par);
}
catch (OracleException ex)
{
throw ex;
}
return ret;
}
/// <summary>
/// 追加记录返回
/// </summary>
/// <param name="authorList"></param>
/// <param name="authorID"></param>
/// <returns></returns>
public int InsertBookKindOutput(BookKindListInfo bookKindList, out int bookKindLID)
{
bookKindLID = 0;
int ret = 0;
try
{
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("temTypeName",OracleDbType.NVarchar2,1000),
new OracleParameter("temParent",OracleDbType.Int32,4),
new OracleParameter("temId",OracleDbType.Int32,4),
};
par[0].Value = bookKindList.BookKindName;
par[0].Direction = ParameterDirection.Input;
par[1].Value = bookKindList.BookKindParent;
par[1].Direction = ParameterDirection.Input;
par[2].Direction = ParameterDirection.Output;
ret = OracleHelper.ExecuteSql("proc_Insert_BookKindOut", CommandType.StoredProcedure, par);
if (ret > 0)
{
bookKindLID =int.Parse(par[2].Value.ToString());
}
}
catch (OracleException ex)
{
throw ex;
}
return ret;
}
///<summary>
///修改记录
///涂聚文 20160920
///</summary>
///<param name="BookKindListInfo"></param>
///<returns></returns>
public int UpdateBookKindList(BookKindListInfo bookKindList)
{
int ret = 0;
try
{
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("p_id",OracleDbType.Int32,4),
new OracleParameter("p_name",OracleDbType.NVarchar2,1000),
new OracleParameter("p_parent",OracleDbType.Int32,4),
new OracleParameter("p_code",OracleDbType.Varchar2,1000),
};
par[0].Value = bookKindList.BookKindID;
par[0].Direction = ParameterDirection.Input;
par[1].Value = bookKindList.BookKindName;
par[1].Direction = ParameterDirection.Input;
par[2].Value = bookKindList.BookKindParent;
par[2].Direction = ParameterDirection.Input;
par[3].Value = bookKindList.BookKindCode;
par[3].Direction = ParameterDirection.Input;
ret = OracleHelper.ExecuteSql("procUpdateBookKindList", CommandType.StoredProcedure, par);
// ret = 1;
}
catch (OracleException ex)
{
throw ex;
}
return ret;
}
///<summary>
/// 删除记录
///</summary>
///<param name="bookKindIDInfo"></param>
///<returns></returns>
public bool DeleteBookKindList(int bookKindID)
{
bool ret = false;
try
{
OracleParameter par = new OracleParameter("p_BookKindID", bookKindID);
par.Direction = ParameterDirection.Input;
int temp = 0;
temp = OracleHelper.ExecuteSql("procDeleteBookKindList", CommandType.StoredProcedure, par);
if (temp != 0)
{
ret = true;
}
}
catch (OracleException ex)
{
throw ex;
}
return ret;
}
///<summary>
/// 查询记录
///</summary>
///<param name="bookKindIDInfo"></param>
///<returns></returns>
public BookKindListInfo SelectBookKindList(int bookKindID)
{
BookKindListInfo bookKindList = null;
try
{
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("p_id",OracleDbType.Int32,4),
new OracleParameter("p_cur",OracleDbType.RefCursor),
};
par[0].Value = bookKindID;
par[0].Direction = ParameterDirection.Input;
par[1].Direction = ParameterDirection.Output;
using (OracleDataReader reader = OracleHelper.GetReader("procSelectBookKindList", CommandType.StoredProcedure, par)) //proc_Select_BookKindList 提示名称过长Oracle
{
if (reader.Read())
{
bookKindList = new BookKindListInfo();
bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (decimal)reader["BookKindID"] : 0;
bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";
bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (decimal)reader["BookKindParent"] : 0; }
}
}
catch (OracleException ex)
{
throw ex;
}
return bookKindList;
} ///<summary>
/// 查询所有记录
///</summary>
///<returns></returns>
public List<BookKindListInfo> SelectBookKindListAll()
{
List<BookKindListInfo> list = new List<BookKindListInfo>();
BookKindListInfo bookKindList = null;
try
{
//定义参数，注意参数名必须与存储过程定义时一致，且类型为OracleType.Cursor
OracleParameter cur_set = new OracleParameter("cur_return", OracleDbType.RefCursor);
//设置参数为输出类型
cur_set.Direction = ParameterDirection.Output;
//
//OracleHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, "pkg_Select_BookKindListAll.proc_Select_BookKindListAll", cur_set)
using (OracleDataReader reader = OracleHelper.GetReader("pkg_Select_BookKindListAll.proc_Select_BookKindListAll", CommandType.StoredProcedure, cur_set))
{
while (reader.Read())
{
bookKindList = new BookKindListInfo();
string s = reader["BookKindID"].ToString();
bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (decimal)reader["BookKindID"] : 0;
bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";
bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (decimal)reader["BookKindParent"] : 0;
list.Add(bookKindList); }
}
}
catch (OracleException ex)
{
throw ex;
}
return list;
}
///<summary>
/// 查询所有记录
///</summary>
///<returns></returns>
public DataTable SelectBookKindListDataTableAll()
{
DataTable dt = new DataTable();
try
{
//定义参数，注意参数名必须与存储过程定义时一致，且类型为OracleType.Cursor
OracleParameter cur_set = new OracleParameter("cur_return", OracleDbType.RefCursor);
//设置参数为输出类型
cur_set.Direction = ParameterDirection.Output;
//添加参数
//comm.Parameters.Add(cur_set);
using (DataTable reader = OracleHelper.GetTable("pkgSelectBookKindListAll.procSelectBookKindListAll", CommandType.StoredProcedure, cur_set))
{
dt = reader; }
}
catch (OracleException ex)
{
throw ex;
}
return dt;
} /// <summary>
/// 填充dataSet数据集－Oracle库
/// </summary>
/// <param name="pindex">当前页</param>
/// <param name="psql">执行查询的SQL语句</param>
/// <param name="psize">每页显示的记录数</param>
/// <returns></returns>
private bool gridbind(int pindex, string psql, int psize)
{
OracleConnection conn = new OracleConnection();
OracleCommand cmd = new OracleCommand();
OracleDataAdapter dr = new OracleDataAdapter();
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.CommandText = "DotNet.DotNetPageRecordsCount";
cmd.Parameters.Add("psqlcount", OracleDbType.Varchar2).Value = psql;
cmd.Parameters.Add("prcount", OracleDbType.Int32).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery();
string PCount = cmd.Parameters["prcount"].Value.ToString();
cmd.Parameters.Clear();
cmd.CommandText = "DotNet.DotNetPagination";
if (pindex != 0)
{
cmd.Parameters.Add("pindex", OracleDbType.Int32).Value = pindex - 1;
}
else
{
cmd.Parameters.Add("pindex", OracleDbType.Int32).Value = pindex;
}
cmd.Parameters.Add("psql", OracleDbType.Varchar2).Value = psql;
cmd.Parameters.Add("psize", OracleDbType.Int32).Value = psize;
cmd.Parameters.Add("v_cur", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
cmd.Parameters.Add("pcount", OracleDbType.Int32).Direction = ParameterDirection.Output;
dr.SelectCommand = cmd;
try
{
DataSet ds = new DataSet();
dr.Fill(ds);
//显示页码条的状态
//showStatus(Convert.ToInt32(cmd.Parameters["pindex"].Value + 1,
// Convert.ToInt32(cmd.Parameters["pcount"].Value),
// Convert.ToInt32(PCount));
for (int i = 0; i < ds.Tables.Count; i++)
{ //把数据行为零的表删除
if (ds.Tables[i].Rows.Count == 0)
ds.Tables.Remove(ds.Tables[i].TableName);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
} conn.Close();
return true;
}
}
最新文章
- readelf与动态库
- 什么是REST、RESTful
- Cocos2d-x分类
- python中的深拷贝和潜拷贝
- Sql Server 附加没有日志文件的数据库(.mdf)文件方法
- 关于php中数据访问的几点补充
- java jmx
- shoususaiBti
- dedecms内容页调用缩略图 缩略图多种用法（借鉴）
- 让git忽略ignore所有文件，只对某些文件进行版本控制
- 自定义实现简单的Android颜色选择器（附带源码）
- Stream消息流 和 Stream Grouping 消息流组
- Java nextInt()函数
- BZOJ_1774_[Usaco2009 Dec]Toll 过路费_floyd
- C#使用Windows Service
- Markdown初入门(使用Typora编辑)
- 高DPI下界面错乱的解决方法和原理
- H5滑条(input type=range)
- Apache Thrift的C++多线程编程定式
- Android笔记：OptionsMenu
热门文章
- 干货!表达式树解析"框架"(1)
- [备忘]Redis运行出现Client sent AUTH, but no password is set
- xamarin UWP中MessageDialog与ContentDialog的区别
- Spring声明式事务管理
- FreeBSD_11-系统管理——{Part_8 - IPFW}
- 轻量级前端MVVM框架avalon - 模型转换
- BFC之宽度自适应布局篇
- ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
- C/C++ 双精度double 数据相加出错缺陷解释
- VirtualBox安装Debian6的方法和步骤(详细)