ODP.NET:
引用：
using Oracle.DataAccess; //Oracle g 11.2.0 using Oracle.DataAccess.Client; using Oracle.DataAccess.Types; //下載 http://www.oracle.com/technetwork/topics/dotnet/downloads/net-downloads-160392.html //引用：D:\app\geovindu\product\11.2.0\dbhome_1\ODP.NET\bin //用法參考 // //http://docs.oracle.com/cd/B28359_01/appdev.111/b28844/procedures_dot_net.htm //http://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleDataAdapterClass.htm //.net 4.0 //https://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm
/// <summary>
/// 20160918 塗聚文
/// Geovin Du
/// </summary>
public class BookKindListDAL : IBookKindList
{
///<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[1].Value = bookKindList.BookKindParent;
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[1].Value = bookKindList.BookKindParent;
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>
///修改記錄
///</summary>
///<param name="BookKindListInfo"></param>
///<returns></returns>
public int UpdateBookKindList(BookKindListInfo bookKindList)
{
int ret = 0;
try
{
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("BookKindID",OracleDbType.Int32,4),
new OracleParameter("BookKindName",OracleDbType.NVarchar2,1000),
new OracleParameter("BookKindParent",OracleDbType.Int32,4),
};
par[0].Value = bookKindList.BookKindID;
par[1].Value = bookKindList.BookKindName;
par[2].Value = bookKindList.BookKindParent;
ret = OracleHelper.ExecuteSql("proc_Update_BookKindList", CommandType.StoredProcedure, par);
}
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("BookKindID", bookKindID);
int temp = 0;
temp = OracleHelper.ExecuteSql("proc_Delete_BookKindList", 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("BookKindID", bookKindID);
using (OracleDataReader reader = OracleHelper.GetReader("proc_Select_BookKindList", CommandType.StoredProcedure, par))
{
if (reader.Read())
{
bookKindList = new BookKindListInfo();
bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (int)reader["BookKindID"] : 0;
bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";
bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (int)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
{
using (OracleDataReader reader = OracleHelper.GetReader("proc_Select_BookKindListAll", CommandType.StoredProcedure, null))
{
while (reader.Read())
{
bookKindList = new BookKindListInfo();
bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (int)reader["BookKindID"] : 0;
bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";
bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (int)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
{
using (DataTable reader = OracleHelper.GetTable("proc_Select_BookKindListAll", CommandType.StoredProcedure, null))
{
dt = reader;
}
}
catch (OracleException ex)
{
throw ex;
}
return dt;
}
}
System.Data.OracleClient(.net 4.0)
引用：
using System.Collections; using System.Data; using System.Configuration; using System.Data.OracleClient;//.net 4.0 //用法參考 //https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracledataadapter(v=vs.110).aspx //http://blog.csdn.net/chinawn/article/details/336904 //C:\Program Files\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Data.OracleClient.dll
/// <summary>
/// 20160918 塗聚文
/// Geovin Du
/// </summary>
public class BookKindListDAL : IBookKindList
{
///<summary>
/// 追加記錄
///</summary>
///<param name="BookKindListInfo"></param>
///<returns></returns>
public int InsertBookKindList(BookKindListInfo bookKindList)
{
int ret = 0;
try
{
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("temTypeName",OracleType.NVarChar,1000),
new OracleParameter("temParent",OracleType.Number,4),
};
par[0].Value = bookKindList.BookKindName;
par[1].Value = bookKindList.BookKindParent;
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",OracleType.NVarChar,1000),
new OracleParameter("temParent",OracleType.Number,4),
new OracleParameter("temId",OracleType.Number,4),
};
par[0].Value = bookKindList.BookKindName;
par[1].Value = bookKindList.BookKindParent;
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>
///修改記錄
///</summary>
///<param name="BookKindListInfo"></param>
///<returns></returns>
public int UpdateBookKindList(BookKindListInfo bookKindList)
{
int ret = 0;
try
{
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("BookKindID",OracleType.Number,4),
new OracleParameter("BookKindName",OracleType.NVarChar,1000),
new OracleParameter("BookKindParent",OracleType.Number,4),
};
par[0].Value = bookKindList.BookKindID;
par[1].Value = bookKindList.BookKindName;
par[2].Value = bookKindList.BookKindParent;
ret = OracleHelper.ExecuteSql("proc_Update_BookKindList", CommandType.StoredProcedure, par);
}
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("BookKindID", bookKindID);
int temp = 0;
temp = OracleHelper.ExecuteSql("proc_Delete_BookKindList", 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("BookKindID", bookKindID);
using (OracleDataReader reader = OracleHelper.GetReader("proc_Select_BookKindList", CommandType.StoredProcedure, par))
{
if (reader.Read())
{
bookKindList = new BookKindListInfo();
bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (int)reader["BookKindID"] : 0;
bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";
bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (int)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
{
using (OracleDataReader reader = OracleHelper.GetReader("proc_Select_BookKindListAll", CommandType.StoredProcedure, null))
{
while (reader.Read())
{
bookKindList = new BookKindListInfo();
bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (int)reader["BookKindID"] : 0;
bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";
bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (int)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
{
using (DataTable reader = OracleHelper.GetTable("proc_Select_BookKindListAll", CommandType.StoredProcedure, null))
{
dt = reader;
}
}
catch (OracleException ex)
{
throw ex;
}
return dt;
}
}
