csharp: Oracle Stored Procedure DAL using ODP.NET

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

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13

---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: 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232

--书分类目录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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289

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