csharp: Oracle Stored Procedure DAL using ODP.NET

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 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; begin SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=temTypeName; dbms_output.put_line(存在相同的记录,添加不成功!||ncount); 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 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; begin SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=temTypeName; dbms_output.put_line(存在相同的记录,添加不成功!||ncount); temId:=0; 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); dbms_output.put_line(存在相同的记录,添加不成功!输出参数:||mid); end if; 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 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; 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);
CREATE OR REPLACE PROCEDURE procDeleteBookKindList(p_BookKindID IN BookKindList.BookKindID%TYPE) BEGIN DELETE BookKindList where BookKindID = p_BookKindID; COMMIT; ---一条记录 --创建包: 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) 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;
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) begin open cur_return for select * from BookKindList; end procSelectBookKindListAll; end pkgSelectBookKindListAll;
--输出获取到的数据 DBMS_OUTPUT.PUT_LINE (pdtrow.BookKindID||，||pdtrow.BookKindName); END LOOP; CLOSE cur_return; 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;

 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;

 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;

 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;

 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;

 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;

 //定义参数，注意参数名必须与存储过程定义时一致，且类型为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();

 //定义参数，注意参数名必须与存储过程定义时一致，且类型为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;


 /// 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;

 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;

 }

Microsoft SQL Server Product Samples:Database 原文:Microsoft SQL Server Product Samples:Database 从SQL Server 2005 之后示例数据都为AdventureWorks,需要的通过codeplex网站下载。
Entity Framework4.0 (一)概述(EF4 的Database First方法) 转自:http://www.cnblogs.com/marksun/archive/2011/12/15/2289582.html Entity Framework4.0(以后简称：EF4)，是Microsoft的一款ORM（Object-Relation-Mapping）框架。
geovindu 读者是,读之者,者之读.一沙一世界! to be is to do举世皆清我独浊,众人皆醒我独醉.俺是农民工,程序员.