using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Reflection;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using Harlinn.Oracle.DBTool.Example.Common;
using Harlinn.Oracle.DBTool.Example.Types;
namespace Harlinn.Oracle.DBTool.Example.DB
{
[DataObject]
public partial class EnumerationValueElementAccessor : Accessor
{
private static readonly log4net.ILog sfLog = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
private static void LogException(Exception exc, MethodBase method)
{
Logger.LogException(sfLog, exc, method);
}
public const string SCHEMA = "HARLINN";
public const string TABLE = "ENUMERATION_VALUES";
public const string DB_QUALIFIED_NAME = "HARLINN.ENUMERATION_VALUES";
public static DataTable CreateDataTable()
{
try
{
DataTable result = new DataTable();
DataColumn idDataColumn = new DataColumn( "ID", typeof(decimal) );
idDataColumn.AllowDBNull = false;
result.Columns.Add(idDataColumn);
DataColumn enumerationDataColumn = new DataColumn( "ENUMERATION", typeof(decimal) );
enumerationDataColumn.AllowDBNull = false;
result.Columns.Add(enumerationDataColumn);
DataColumn nameDataColumn = new DataColumn( "NAME", typeof(string) );
nameDataColumn.AllowDBNull = false;
result.Columns.Add(nameDataColumn);
DataColumn valueDataColumn = new DataColumn( "VALUE", typeof(decimal) );
valueDataColumn.AllowDBNull = false;
result.Columns.Add(valueDataColumn);
DataColumn optimisticLockDataColumn = new DataColumn( "OPTIMISTIC_LOCK", typeof(decimal) );
optimisticLockDataColumn.AllowDBNull = false;
result.Columns.Add(optimisticLockDataColumn);
DataColumn descriptionDataColumn = new DataColumn( "DESCRIPTION", typeof(string) );
descriptionDataColumn.AllowDBNull = true;
result.Columns.Add(descriptionDataColumn);
DataColumn commentsDataColumn = new DataColumn( "COMMENTS", typeof(string) );
commentsDataColumn.AllowDBNull = true;
result.Columns.Add(commentsDataColumn);
DataColumn[] keys = new DataColumn[1];
keys[0] = idDataColumn;
result.PrimaryKey = keys;
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static DataTable CreateDataTable( List<EnumerationValueElementData> elements )
{
try
{
DataTable result = CreateDataTable();
foreach(EnumerationValueElementData element in elements)
{
object id = element.Id;
object enumeration = element.Enumeration;
object name = element.Name;
object value = element.Value;
object optimisticLock = element.OptimisticLock;
object description;
if( element.Description != null )
{
description = element.Description;
}
else
{
description = null;
}
object comments;
if( element.Comments != null )
{
comments = element.Comments;
}
else
{
comments = null;
}
result.Rows.Add(id,enumeration,name,value,optimisticLock,description,comments );
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static void WriteToServer( OracleConnection oracleConnection, string qualifiedDBName, DataTable dataTable )
{
try
{
using ( OracleBulkCopy bulkCopy = new OracleBulkCopy( oracleConnection ) )
{
bulkCopy.DestinationTableName = qualifiedDBName;
bulkCopy.WriteToServer( dataTable );
}
}
catch ( Exception exc )
{
LogException( exc, MethodBase.GetCurrentMethod( ) );
throw;
}
}
[DataObjectMethod(DataObjectMethodType.Select,true)]
public static List<EnumerationValueElementData> GetAll( )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
List<EnumerationValueElementData> result = new List<EnumerationValueElementData>( );
EnumerationValueElementReader elementReader = new EnumerationValueElementReader( qualifiedDBName );
using( elementReader )
{
while( elementReader.Read( ) )
{
EnumerationValueElementData element = elementReader.EnumerationValue;
result.Add(element);
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
[DataObjectMethod(DataObjectMethodType.Select,false)]
public static List<EnumerationValueElementData> GetAll( OracleConnection oracleConnection )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
List<EnumerationValueElementData> result = new List<EnumerationValueElementData>( );
EnumerationValueElementReader elementReader = new EnumerationValueElementReader( oracleConnection, qualifiedDBName );
using( elementReader )
{
while( elementReader.Read( ) )
{
EnumerationValueElementData element = elementReader.EnumerationValue;
result.Add(element);
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static KeyedEnumerationValueCollection GetKeyedCollection( )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
KeyedEnumerationValueCollection result = new KeyedEnumerationValueCollection( );
EnumerationValueElementReader elementReader = new EnumerationValueElementReader( qualifiedDBName );
using( elementReader )
{
while( elementReader.Read( ) )
{
EnumerationValueElementData element = elementReader.EnumerationValue;
result.Add(element);
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
[DataObjectMethod(DataObjectMethodType.Select,false)]
public static EnumerationValueElementData GetById( long id )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
EnumerationValueElementData result = null;
EnumerationValueElementReader elementReader = EnumerationValueElementReader.CreateReaderById( qualifiedDBName,id );
using( elementReader )
{
if( elementReader.Read( ) )
{
EnumerationValueElementData element = elementReader.EnumerationValue;
result = element;
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
[DataObjectMethod(DataObjectMethodType.Select,false)]
public static EnumerationValueElementData GetById( OracleConnection oracleConnection, long id )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
EnumerationValueElementData result = null;
EnumerationValueElementReader elementReader = EnumerationValueElementReader.CreateReaderById( oracleConnection ,qualifiedDBName,id );
using( elementReader )
{
if( elementReader.Read( ) )
{
EnumerationValueElementData element = elementReader.EnumerationValue;
result = element;
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
[DataObjectMethod(DataObjectMethodType.Select,false)]
public static EnumerationValueElementData GetByEnumerationAndName( long enumeration, string name )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
EnumerationValueElementData result = null;
EnumerationValueElementReader elementReader = EnumerationValueElementReader.CreateReaderByEnumerationAndName( qualifiedDBName,enumeration,name );
using( elementReader )
{
if( elementReader.Read( ) )
{
EnumerationValueElementData element = elementReader.EnumerationValue;
result = element;
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
[DataObjectMethod(DataObjectMethodType.Select,false)]
public static EnumerationValueElementData GetByEnumerationAndName( OracleConnection oracleConnection, long enumeration, string name )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
EnumerationValueElementData result = null;
EnumerationValueElementReader elementReader = EnumerationValueElementReader.CreateReaderByEnumerationAndName( oracleConnection ,qualifiedDBName,enumeration,name );
using( elementReader )
{
if( elementReader.Read( ) )
{
EnumerationValueElementData element = elementReader.EnumerationValue;
result = element;
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
[DataObjectMethod(DataObjectMethodType.Select,false)]
public static EnumerationValueElementData GetByEnumerationAndValue( long enumeration, long value )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
EnumerationValueElementData result = null;
EnumerationValueElementReader elementReader = EnumerationValueElementReader.CreateReaderByEnumerationAndValue( qualifiedDBName,enumeration,value );
using( elementReader )
{
if( elementReader.Read( ) )
{
EnumerationValueElementData element = elementReader.EnumerationValue;
result = element;
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
[DataObjectMethod(DataObjectMethodType.Select,false)]
public static EnumerationValueElementData GetByEnumerationAndValue( OracleConnection oracleConnection, long enumeration, long value )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
EnumerationValueElementData result = null;
EnumerationValueElementReader elementReader = EnumerationValueElementReader.CreateReaderByEnumerationAndValue( oracleConnection ,qualifiedDBName,enumeration,value );
using( elementReader )
{
if( elementReader.Read( ) )
{
EnumerationValueElementData element = elementReader.EnumerationValue;
result = element;
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
[DataObjectMethod(DataObjectMethodType.Select,false)]
public static List<EnumerationValueElementData> GetByEnumeration( long enumeration )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
List<EnumerationValueElementData> result = new List<EnumerationValueElementData>( );
EnumerationValueElementReader elementReader = EnumerationValueElementReader.CreateReaderByEnumeration( qualifiedDBName,enumeration );
using( elementReader )
{
while( elementReader.Read( ) )
{
EnumerationValueElementData element = elementReader.EnumerationValue;
result.Add( element );
}
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Insert( EnumerationValueElementData element )
{
try
{
EnumerationValueElementData result = null;
int recordsInserted = Insert( GetConnection(), element, out result );
return recordsInserted;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Insert( EnumerationValueElementData element, out EnumerationValueElementData result )
{
try
{
int recordsInserted = Insert( GetConnection(), element, out result );
return recordsInserted;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Insert( OracleConnection oracleConnection, EnumerationValueElementData element, out EnumerationValueElementData result )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
int recordsInserted = 0;
result = null;
OracleCommand oracleCommand = oracleConnection.CreateCommand();
using (oracleCommand)
{
oracleCommand.BindByName = true;
string insertSQLStatement = "INSERT INTO {0}(ID,ENUMERATION,NAME,VALUE,OPTIMISTIC_LOCK,DESCRIPTION,COMMENTS) " +
" VALUES(SEQ_ENUMERATION_VALUES.NEXTVAL, :enumeration_, :name_, :value_, 0, :description_, :comments_)" +
" RETURNING ID INTO :id_";
string finalInsertSQLStatement = string.Format(insertSQLStatement,qualifiedDBName);
oracleCommand.CommandText = finalInsertSQLStatement;
OracleParameter idParameter = oracleCommand.Parameters.Add(new OracleParameter( ":id_", OracleDbType.Decimal ));
idParameter.Direction = ParameterDirection.Output;
OracleParameter enumerationParameter = oracleCommand.Parameters.Add(new OracleParameter( ":enumeration_", OracleDbType.Decimal ));
enumerationParameter.Value = element.Enumeration;
OracleParameter nameParameter = oracleCommand.Parameters.Add(new OracleParameter( ":name_", OracleDbType.NVarchar2 ));
nameParameter.Value = element.Name;
OracleParameter valueParameter = oracleCommand.Parameters.Add(new OracleParameter( ":value_", OracleDbType.Decimal ));
valueParameter.Value = element.Value;
OracleParameter descriptionParameter = oracleCommand.Parameters.Add(new OracleParameter( ":description_", OracleDbType.NClob ));
if( element.Description != null )
{
descriptionParameter.Value = element.Description;
}
else
{
descriptionParameter.IsNullable = true;
descriptionParameter.Value = null;
}
OracleParameter commentsParameter = oracleCommand.Parameters.Add(new OracleParameter( ":comments_", OracleDbType.NClob ));
if( element.Comments != null )
{
commentsParameter.Value = element.Comments;
}
else
{
commentsParameter.IsNullable = true;
commentsParameter.Value = null;
}
recordsInserted = oracleCommand.ExecuteNonQuery();
if( recordsInserted != 0)
{
result = element;
result.Id = ((OracleDecimal)idParameter.Value).ToInt64();
result.ElementState = ElementState.Stored;
}
}
return recordsInserted;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static void Insert( List<EnumerationValueElementData> elements )
{
try
{
Insert( GetConnection(), elements);
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static void Insert( OracleConnection oracleConnection, List<EnumerationValueElementData> elements )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
DataTable dataTable = CreateDataTable( elements );
using (dataTable)
{
WriteToServer(oracleConnection,qualifiedDBName,dataTable);
}
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Update( EnumerationValueElementData element )
{
try
{
EnumerationValueElementData result = null;
int recordsUpdated = Update(GetConnection(), element, out result);
return recordsUpdated;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Update( EnumerationValueElementData element, out EnumerationValueElementData result )
{
try
{
int recordsUpdated = Update(GetConnection(), element, out result);
return recordsUpdated;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Update( OracleConnection oracleConnection, EnumerationValueElementData element, out EnumerationValueElementData result )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
int recordsUpdated = 0;
result = null;
OracleCommand oracleCommand = oracleConnection.CreateCommand();
using (oracleCommand)
{
oracleCommand.BindByName = true;
string updateSQLStatement = "UPDATE {0} SET " +
"ENUMERATION = :enumeration_ , " +
"NAME = :name_ , " +
"VALUE = :value_ , " +
"OPTIMISTIC_LOCK = OPTIMISTIC_LOCK + 1, " +
"DESCRIPTION = :description_ , " +
"COMMENTS = :comments_ " +
" WHERE " +
"(ID = :id_) AND " +
"(OPTIMISTIC_LOCK = :optimisticLock_) " +
" RETURNING OPTIMISTIC_LOCK INTO :optimisticLock_";
string finalUpdateSQLStatement = string.Format(updateSQLStatement,qualifiedDBName);
oracleCommand.CommandText = finalUpdateSQLStatement;
OracleParameter idParameter = oracleCommand.Parameters.Add(new OracleParameter( ":id_", OracleDbType.Decimal ));
idParameter.Value = element.Id;
OracleParameter enumerationParameter = oracleCommand.Parameters.Add(new OracleParameter( ":enumeration_", OracleDbType.Decimal ));
enumerationParameter.Value = element.Enumeration;
OracleParameter nameParameter = oracleCommand.Parameters.Add(new OracleParameter( ":name_", OracleDbType.NVarchar2 ));
nameParameter.Value = element.Name;
OracleParameter valueParameter = oracleCommand.Parameters.Add(new OracleParameter( ":value_", OracleDbType.Decimal ));
valueParameter.Value = element.Value;
OracleParameter optimisticLockParameter = oracleCommand.Parameters.Add(new OracleParameter( ":optimisticLock_", OracleDbType.Decimal ));
optimisticLockParameter.Direction = ParameterDirection.InputOutput;
optimisticLockParameter.Value = element.OptimisticLock;
OracleParameter descriptionParameter = oracleCommand.Parameters.Add(new OracleParameter( ":description_", OracleDbType.NClob ));
if( element.Description != null )
{
descriptionParameter.Value = element.Description;
}
else
{
descriptionParameter.IsNullable = true;
descriptionParameter.Value = null;
}
OracleParameter commentsParameter = oracleCommand.Parameters.Add(new OracleParameter( ":comments_", OracleDbType.NClob ));
if( element.Comments != null )
{
commentsParameter.Value = element.Comments;
}
else
{
commentsParameter.IsNullable = true;
commentsParameter.Value = null;
}
recordsUpdated = oracleCommand.ExecuteNonQuery();
if( recordsUpdated != 0)
{
result = element;
result.OptimisticLock = ((OracleDecimal)optimisticLockParameter.Value).ToInt64();
result.ElementState = ElementState.Stored;
}
else
{
EnumerationValueElementReader reader = EnumerationValueElementReader.CreateReaderById(oracleConnection, qualifiedDBName,element.Id);
using (reader)
{
result = element;
if (reader.Read())
{
result.ElementState = ElementState.ConcurrencyConflict;
result.ConcurrencyConflictElement = reader.EnumerationValue;
}
else
{
result.ElementState = ElementState.Deleted;
}
}
}
}
return recordsUpdated;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Save( EnumerationValueElementData element )
{
try
{
EnumerationValueElementData result = null;
int recordsSaved = Save(GetConnection(), element, out result);
return recordsSaved;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Save( EnumerationValueElementData element, out EnumerationValueElementData result )
{
try
{
int recordsSaved = Save(GetConnection(), element, out result);
return recordsSaved;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Save( OracleConnection oracleConnection, EnumerationValueElementData element, out EnumerationValueElementData result )
{
try
{
int recordsSaved = 0;
result = null;
if (element.ElementState == ElementState.New)
{
recordsSaved = Insert(oracleConnection, element, out result);
}
else if (element.ElementState == ElementState.Changed)
{
recordsSaved = Update(oracleConnection, element, out result);
}
return recordsSaved;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static List<EnumerationValueElementData> Save( List<EnumerationValueElementData> elements )
{
try
{
List<EnumerationValueElementData> result = Save(GetConnection(), elements);
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static List<EnumerationValueElementData> Save( OracleConnection oracleConnection, List<EnumerationValueElementData> elements )
{
try
{
//string qualifiedDBName = DB_QUALIFIED_NAME;
List<EnumerationValueElementData> result = null;
OracleCommand oracleCommand = oracleConnection.CreateCommand();
using (oracleCommand)
{
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
public static int Delete( long id, long optimisticLock )
{
try
{
int result = Delete(GetConnection(), id, optimisticLock);
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
private const string DELETE_STATEMENT = "DELETE FROM {0} WHERE ID = :id AND OPTIMISTIC_LOCK = :optimisticLock";
public static int Delete( OracleConnection oracleConnection, long id, long optimisticLock )
{
try
{
string qualifiedDBName = DB_QUALIFIED_NAME;
int result = 0;
OracleCommand oracleCommand = oracleConnection.CreateCommand();
using (oracleCommand)
{
string deleteStatement = string.Format(DELETE_STATEMENT, qualifiedDBName);
oracleCommand.CommandText = deleteStatement;
OracleParameter idParameter = oracleCommand.Parameters.Add(new OracleParameter( ":id", OracleDbType.Decimal ));
idParameter.Value = id;
result = oracleCommand.ExecuteNonQuery();
}
return result;
}
catch (Exception exc)
{
LogException(exc, MethodBase.GetCurrentMethod());
throw;
}
}
}
}
Submit an article or tip