335 lines
12 KiB
C#
335 lines
12 KiB
C#
|
/*
|
||
|
|
|-------------------------------------------------------------------------------|
|
||
|
|
| Copyright © Computer Ease Limited |
|
||
|
|
| Address: 1/9 Bloack-A Lalmatia, Dhaka-1207, Bangladesh |
|
||
|
|
| Email: info@celimited.com, cease@bol-online.com, web: www.celimited.com |
|
||
|
|
| Unauthorized copy or distribution is strictly prohibited |
|
||
|
|
| Author: S. M. Russel, Last modified date: 17/04/2009 |
|
||
|
|
|-------------------------------------------------------------------------------|
|
||
|
|
*/
|
||
|
|
|
||
|
|
using System;
|
||
|
|
using System.Data;
|
||
|
|
using System.Data.OracleClient;
|
||
|
|
using Ease.Core.DataAccess;
|
||
|
|
using Ease.Core.Utility;
|
||
|
|
|
||
|
|
namespace Ease.CoreV35.DataAccess.Oracle
|
||
|
|
{
|
||
|
|
#region DataAccess: Oracle Helper
|
||
|
|
|
||
|
|
public sealed class OracleHelperExtension
|
||
|
|
{
|
||
|
|
private OracleHelperExtension()
|
||
|
|
{
|
||
|
|
}
|
||
|
|
|
||
|
|
#region Fill Data set
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Fills a typed DataSet using the DataReader's current result. This method
|
||
|
|
/// allows paginated access to the database.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="dataReader">The DataReader used to fetch the values.</param>
|
||
|
|
/// <param name="dataSet">The DataSet used to store the values.</param>
|
||
|
|
/// <param name="tableName">The name of the DataSet table used to add the
|
||
|
|
/// DataReader records.</param>
|
||
|
|
/// <param name="from">The quantity of records skipped before placing
|
||
|
|
/// values on the DataReader on the DataSet.</param>
|
||
|
|
/// <param name="count">The maximum quantity of records alloed to fill on the
|
||
|
|
/// DataSet.</param>
|
||
|
|
public static void Fill(IDataReader dataReader, DataSet dataSet, string tableName, int from, int count)
|
||
|
|
{
|
||
|
|
DataAccessHelper.Fill(dataReader, dataSet, tableName, from, count);
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Fills a typed DataSet using the DataReader's current result. This method
|
||
|
|
/// allows paginated access to the database.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="dataReader">The DataReader used to fetch the values.</param>
|
||
|
|
/// <param name="dataSet">The DataSet used to store the values.</param>
|
||
|
|
/// <param name="tableName">The name of the DataSet table used to add the DataReader records.</param>
|
||
|
|
public static void Fill(IDataReader dataReader, DataSet dataSet, string tableName)
|
||
|
|
{
|
||
|
|
OracleHelperExtension.Fill(dataReader, dataSet, tableName, 0, 0);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Parameter
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Return OracleParameter object according to parameter.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="pType">Type of data.</param>
|
||
|
|
/// <param name="direction">Direction of parameter.</direction>
|
||
|
|
/// <param name="pValue">Value of parametetr.</param>
|
||
|
|
/// <returns>Instatnce of OracleParameter.</returns>
|
||
|
|
public static OracleParameter CreateParam(OracleType pType, ParameterDirection direction, object pValue)
|
||
|
|
{
|
||
|
|
return OracleHelperExtension.CreateParam(string.Empty, pType, direction, pValue);
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Return OracleParameter object according to parameter.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="pName">Name of parameter.</param>
|
||
|
|
/// <param name="pType">Type of data.</param>
|
||
|
|
/// <param name="direction">Direction of parameter.</param>
|
||
|
|
/// <returns>Instatnce of OracleParameter.</returns>
|
||
|
|
public static OracleParameter CreateParam(string pName, OracleType pType, ParameterDirection direction)
|
||
|
|
{
|
||
|
|
return OracleHelperExtension.CreateParam(pName, pType, direction, null);
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Return OracleParameter object according to parameter.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="pName">Name of parameter.</param>
|
||
|
|
/// <param name="pType">Type of data.</param>
|
||
|
|
/// <param name="direction">Direction of parameter.</direction>
|
||
|
|
/// <param name="pValue">Value of parametetr.</param>
|
||
|
|
/// <returns>Instatnce of OracleParameter.</returns>
|
||
|
|
public static OracleParameter CreateParam(string pName, OracleType pType, ParameterDirection direction,
|
||
|
|
object pValue)
|
||
|
|
{
|
||
|
|
OracleParameter param = new OracleParameter(pName, pType);
|
||
|
|
param.Direction = direction;
|
||
|
|
if (pType == OracleType.VarChar)
|
||
|
|
param.Size = 1000;
|
||
|
|
param.Value = pValue;
|
||
|
|
|
||
|
|
return param;
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Return OracleParameter object according to parameter.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="pName">Name of parameter.</param>
|
||
|
|
/// <param name="pType">Type of data.</param>
|
||
|
|
/// <param name="direction">Direction of parameter.</param>
|
||
|
|
/// <param name="pValue">Value of parametetr.</param>
|
||
|
|
/// <param name="size">If varchar then lenth of value.</param>
|
||
|
|
/// <returns>Instatnce of OracleParameter.</returns>
|
||
|
|
public static OracleParameter CreateParam(string pName, OracleType pType, ParameterDirection direction,
|
||
|
|
int size, object pValue)
|
||
|
|
{
|
||
|
|
OracleParameter param = new OracleParameter(pName, pType);
|
||
|
|
param.Direction = direction;
|
||
|
|
param.Size = size;
|
||
|
|
param.Value = pValue;
|
||
|
|
|
||
|
|
return param;
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Input Parameter
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Return input direction OracleParameter object.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="pType">Type of data.</param>
|
||
|
|
/// <param name="pValue">Value of parametetr.</param>
|
||
|
|
/// <returns>Instatnce of OracleParameter.</returns>
|
||
|
|
public static OracleParameter CreateInParam(OracleType pType, object pValue)
|
||
|
|
{
|
||
|
|
return OracleHelperExtension.CreateInParam(string.Empty, pType, pValue);
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Return input direction OracleParameter object.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="pName">Name of parameter.</param>
|
||
|
|
/// <param name="pType">Type of data.</param>
|
||
|
|
/// <returns>Instatnce of OracleParameter.</returns>
|
||
|
|
public static OracleParameter CreateInParam(string pName, OracleType pType)
|
||
|
|
{
|
||
|
|
return OracleHelperExtension.CreateInParam(pName, pType, null);
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Return input direction OracleParameter object.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="pName">Name of parameter.</param>
|
||
|
|
/// <param name="pType">Type of data.</param>
|
||
|
|
/// <param name="pValue">Value of parametetr.</param>
|
||
|
|
/// <returns>Instatnce of OracleParameter.</returns>
|
||
|
|
public static OracleParameter CreateInParam(string pName, OracleType pType, object pValue)
|
||
|
|
{
|
||
|
|
OracleParameter param = new OracleParameter(pName, pType);
|
||
|
|
param.Direction = ParameterDirection.Input;
|
||
|
|
if (pType == OracleType.VarChar)
|
||
|
|
param.Size = 1000;
|
||
|
|
param.Value = pValue;
|
||
|
|
|
||
|
|
return param;
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Return input direction OracleParameter object.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="pName">Name of parameter.</param>
|
||
|
|
/// <param name="pType">Type of data.</param>
|
||
|
|
/// <param name="pValue">Value of parametetr.</param>
|
||
|
|
/// <param name="size">If varchar then lenth of value.</param>
|
||
|
|
/// <returns>Instatnce of OracleParameter.</returns>
|
||
|
|
public static OracleParameter CreateInParam(string pName, OracleType pType, object pValue, int size)
|
||
|
|
{
|
||
|
|
OracleParameter param = new OracleParameter(pName, pType);
|
||
|
|
param.Direction = ParameterDirection.Input;
|
||
|
|
param.Size = size;
|
||
|
|
param.Value = pValue;
|
||
|
|
|
||
|
|
return param;
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Output Parameter
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Return output direction OracleParameter object.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="pType">Type of data.</param>
|
||
|
|
/// <param name="pValue">Value of parametetr.</param>
|
||
|
|
/// <returns>Instatnce of OracleParameter.</returns>
|
||
|
|
public static OracleParameter CreateOutParam(OracleType pType, object pValue)
|
||
|
|
{
|
||
|
|
return OracleHelperExtension.CreateOutParam(string.Empty, pType, pValue);
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Return output direction OracleParameter object.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="pName">Name of parameter.</param>
|
||
|
|
/// <param name="pType">Type of data.</param>
|
||
|
|
/// <returns>Instatnce of OracleParameter.</returns>
|
||
|
|
public static OracleParameter CreateOutParam(string pName, OracleType pType)
|
||
|
|
{
|
||
|
|
return OracleHelperExtension.CreateOutParam(pName, pType, null);
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Return output direction OracleParameter object.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="pName">Name of parameter.</param>
|
||
|
|
/// <param name="pType">Type of data.</param>
|
||
|
|
/// <param name="pValue">Value of parametetr.</param>
|
||
|
|
/// <returns>Instatnce of OracleParameter.</returns>
|
||
|
|
public static OracleParameter CreateOutParam(string pName, OracleType pType, object pValue)
|
||
|
|
{
|
||
|
|
OracleParameter param = new OracleParameter(pName, pType);
|
||
|
|
param.Direction = ParameterDirection.Output;
|
||
|
|
if (pType == OracleType.VarChar)
|
||
|
|
{
|
||
|
|
param.Size = 1000;
|
||
|
|
}
|
||
|
|
|
||
|
|
param.Value = pValue;
|
||
|
|
|
||
|
|
return param;
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// Return output direction OracleParameter object.
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="pName">Name of parameter.</param>
|
||
|
|
/// <param name="pType">Type of data.</param>
|
||
|
|
/// <param name="pValue">Value of parametetr.</param>
|
||
|
|
/// <param name="size">If varchar then lenth of value.</param>
|
||
|
|
/// <returns>Instatnce of OracleParameter.</returns>
|
||
|
|
public static OracleParameter CreateOutParam(string pName, OracleType pType, object pValue, int size)
|
||
|
|
{
|
||
|
|
OracleParameter param = new OracleParameter(pName, pType);
|
||
|
|
param.Direction = ParameterDirection.Output;
|
||
|
|
param.Size = size;
|
||
|
|
param.Value = pValue;
|
||
|
|
|
||
|
|
return param;
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Add Parameter
|
||
|
|
|
||
|
|
public static OracleCommand CreateCommand(string name)
|
||
|
|
{
|
||
|
|
OracleCommand command = new OracleCommand(name);
|
||
|
|
|
||
|
|
return command;
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void AddParameter(OracleCommand command, string name, OracleType type,
|
||
|
|
ParameterDirection direction, object Value)
|
||
|
|
{
|
||
|
|
OracleParameter param = command.CreateParameter();
|
||
|
|
|
||
|
|
param.ParameterName = name;
|
||
|
|
param.OracleType = type;
|
||
|
|
param.Direction = direction;
|
||
|
|
if (type == OracleType.VarChar)
|
||
|
|
param.Size = 4000;
|
||
|
|
param.Value = Value;
|
||
|
|
command.Parameters.Add(param);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void AddParameter(OracleCommand command, string name, OracleType type,
|
||
|
|
ParameterDirection direction)
|
||
|
|
{
|
||
|
|
OracleHelperExtension.AddParameter(command, name, type, direction, null);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region GetBlob
|
||
|
|
|
||
|
|
public static OracleLob GetBlob(TransactionContext tc, byte[] bytes)
|
||
|
|
{
|
||
|
|
OracleLob tblob = GetTempBlob(tc);
|
||
|
|
tblob.BeginBatch(OracleLobOpenMode.ReadWrite);
|
||
|
|
tblob.Write(bytes, 0, bytes.Length);
|
||
|
|
tblob.EndBatch();
|
||
|
|
|
||
|
|
return tblob;
|
||
|
|
}
|
||
|
|
|
||
|
|
public static OracleLob GetTempBlob(TransactionContext tc)
|
||
|
|
{
|
||
|
|
OracleCommand command =
|
||
|
|
new OracleCommand("DECLARE t BLOB; BEGIN DBMS_LOB.CREATETEMPORARY(t, false, 0); :tempblob := t; END;");
|
||
|
|
AddParameter(command, "tempblob", OracleType.Blob, ParameterDirection.Output);
|
||
|
|
|
||
|
|
tc.PrepareCommand(command);
|
||
|
|
command.ExecuteNonQuery();
|
||
|
|
|
||
|
|
return (OracleLob) (command.Parameters[0]).Value;
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region private function
|
||
|
|
|
||
|
|
private static object GetValue(object fieldValue, Type fieldType)
|
||
|
|
{
|
||
|
|
switch (fieldType.Name)
|
||
|
|
{
|
||
|
|
case "Int16":
|
||
|
|
case "Int32":
|
||
|
|
case "Int64":
|
||
|
|
if (fieldValue == DBNull.Value)
|
||
|
|
return 0;
|
||
|
|
else
|
||
|
|
return fieldValue;
|
||
|
|
}
|
||
|
|
|
||
|
|
return fieldValue;
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
}
|