public ContactsBackendMap GetById(int id)
{
StringBuilder sb = new StringBuilder(selectCmdBase);
sb.Append(" WHERE ID_MAP = :pID_MAP");
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = sb.ToString();
cmd.BindByName = true;
cmd.Parameters.Add("pID_MAP", OracleDbType.Decimal, id, System.Data.ParameterDirection.Input);
ContactsBackendMap contact = DaoOracleDbHelper <ContactsBackendMap> .ExecSelectCommand(cmd,
DaoOracleDbHelper.MapToContactsBackendMap).FirstOrDefault();
if (contact != null)
{
Complete(contact);
}
return(contact);
}
}
catch
{
throw;
}
}
public void Delete(int id)
{
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = deleteCmdBase;
cmd.BindByName = true;
cmd.Parameters.Add("pID_MAP", OracleDbType.Decimal, id, System.Data.ParameterDirection.Input);
int risp = cmd.ExecuteNonQuery();
if (risp != 1)
{
throw new InvalidOperationException("Errore nella cancellazione");
}
}
}
catch (Exception ex)
{
//TASK: Allineamento log - Ciro
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException(ex.Message,
"ORA_ERR010", string.Empty, string.Empty, ex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
err.objectID = Convert.ToString(id);
_log.Error(err);
throw mEx;
}
else
{
throw ex;
}
}
}
public ICollection <ContactsBackendMap> GetAll()
{
StringBuilder sb = new StringBuilder(selectCmdBase);
sb.Append(" ORDER BY REF_ID_BACKEND, REF_ID_TITOLO");
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = sb.ToString();
ICollection <ContactsBackendMap> contacts = DaoOracleDbHelper <ContactsBackendMap> .ExecSelectCommand(cmd,
DaoOracleDbHelper.MapToContactsBackendMap);
if (contacts != null)
{
foreach (ContactsBackendMap c in contacts)
{
Complete(c);
}
}
return(contacts);
}
}
catch
{
throw;
}
}
/// <summary>
/// Runs a repository operation either as part of an outer unit of work or a standalone one.
/// If it's the latter case and CurrentConnection is null, it will connect to the CurrentDBName.
/// </summary>
/// <param name="fn">A delegate function that will be executed.</param>
/// <param name="commandType"></param>
protected void Run(Action <SqlCommand> fn, CommandType commandType = CommandType.StoredProcedure)
{
try
{
//we create a new connection without a transaction
if (!IsEnlisted)
{
InitDbConnection(CurrentDbName);
}
//we create a command that will be used by the current repository
using (var cmd = CurrentConnection.CreateCommand())
{
cmd.Transaction = CurrentTransaction;
cmd.CommandType = commandType;
fn((SqlCommand)cmd);
}
}
finally
{
//if the repository is not enlisted within an outer unit of work,
//we can commit the transaction and close it's connection.
if (!IsEnlisted)
{
CurrentConnection.Close();
}
}
}
public ICollection <ContactsBackendMap> GetPerTitoloEntita(int idTitolo, int idEntita)
{
StringBuilder sb = new StringBuilder(selectCmdBase);
sb.Append(" WHERE REF_ID_ENTITA = :pID_ENTITA");
sb.Append(" AND REF_ID_TITOLO = :pID_TITOLO");
sb.Append(" ORDER BY REF_ID_BACKEND, REF_ID_TITOLO");
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = sb.ToString();
cmd.BindByName = true;
cmd.Parameters.Add("pID_ENTITA", OracleDbType.Decimal, idEntita, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("pID_TITOLO", OracleDbType.Decimal, idTitolo, System.Data.ParameterDirection.Input);
ICollection <ContactsBackendMap> contacts = DaoOracleDbHelper <ContactsBackendMap> .ExecSelectCommand(cmd,
DaoOracleDbHelper.MapToContactsBackendMap);
if (contacts != null)
{
foreach (ContactsBackendMap c in contacts)
{
Complete(c);
}
}
return(contacts);
}
}
catch
{
throw;
}
}
public List <BackEndRefCode> GetByDescr(string backendDescr)
{
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
StringBuilder sb = new StringBuilder("SELECT id_backend")
.Append(", backend_code")
.Append(", backend_descr")
.Append(", category")
.Append(", descr_plus")
.Append(" FROM rubr_backend")
.Append(" WHERE UPPER(backend_descr) LIKE :p_backend_descr");
cmd.CommandText = sb.ToString();
cmd.Parameters.Add(new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.Varchar2,
ParameterName = "p_backend_descr",
Value = string.Format("%{0}%", backendDescr.ToUpper())
});
return((List <BackEndRefCode>) DaoOracleDbHelper <BackEndRefCode> .ExecSelectCommand(cmd,
DaoOracleDbHelper.MapToBackEndRefCode));
}
}
catch
{
return(null);
}
}
public BackEndRefCode GetByCode(string backendCode)
{
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
StringBuilder sb = new StringBuilder("SELECT id_backend")
.Append(", backend_code")
.Append(", backend_descr")
.Append(", category")
.Append(", descr_plus")
.Append(" FROM rubr_backend")
.Append(" WHERE backend_code = :p_backend_code");
cmd.CommandText = sb.ToString();
cmd.Parameters.Add(new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.Varchar2,
ParameterName = "p_backend_code",
Size = 15,
Value = backendCode
});
return(DaoOracleDbHelper <BackEndRefCode> .ExecSelectCommand(cmd,
DaoOracleDbHelper.MapToBackEndRefCode).SingleOrDefault());
}
}
catch
{
return(null);
}
}
/// <summary>
/// Cria uma nova instância de commando.
/// </summary>
/// <returns></returns>
public virtual IDbCommand CreateCommand()
{
IDbCommand cmd = CurrentConnection.CreateCommand();
cmd.Connection = CurrentConnection;
cmd.Transaction = this.CurrentTransaction;
cmd.CommandTimeout = _commandTimeout;
return(cmd);
}
public ICollection <ContactsBackendMap> GetPerEntita(int idEntita)
{
StringBuilder sb = new StringBuilder();
sb.Append("WITH T_BACK AS (");
sb.Append(" SELECT REF_ID_BACKEND");
sb.Append(" FROM RUBR_CONTATTI_BACKEND");
sb.Append(" WHERE REF_ID_ENTITA = :pID_ENTITA),");
sb.Append("T_CONT_BACK AS (");
sb.Append(" SELECT *");
sb.Append(" FROM RUBR_CONTATTI_BACKEND");
sb.Append(" WHERE REF_ID_BACKEND IN ");
sb.Append(" (SELECT DISTINCT REF_ID_BACKEND");
sb.Append(" FROM T_BACK))");
sb.Append(" SELECT *");
sb.Append(" FROM T_CONT_BACK");
sb.Append(" UNION");
sb.Append(" SELECT NULL, 1, REF_ID_BACKEND, NULL, ID_TITOLO, NULL");
sb.Append(" FROM COMUNICAZIONI_TITOLI CT, T_BACK TB");
sb.Append(" WHERE ID_TITOLO != 0");
sb.Append(" AND NOT EXISTS (");
sb.Append(" SELECT 1");
sb.Append(" FROM T_CONT_BACK TCB");
sb.Append(" WHERE TCB.REF_ID_TITOLO = CT.ID_TITOLO");
sb.Append(" AND TCB.REF_ID_BACKEND = TB.REF_ID_BACKEND)");
sb.Append(" ORDER BY 3, 5");
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = sb.ToString();
cmd.BindByName = true;
cmd.Parameters.Add("pID_ENTITA", OracleDbType.Decimal, idEntita, System.Data.ParameterDirection.Input);
ICollection <ContactsBackendMap> contacts = DaoOracleDbHelper <ContactsBackendMap> .ExecSelectCommand(cmd,
DaoOracleDbHelper.MapToContactsBackendMap);
if (contacts != null)
{
foreach (ContactsBackendMap c in contacts)
{
Complete(c);
}
}
return(contacts);
}
}
catch
{
throw;
}
}
private Dictionary <string, DbParameterAccessor> ReadIntoParams(String[] queryParameters, String query)
{
DbCommand dc = CurrentConnection.CreateCommand();
dc.Transaction = CurrentTransaction;
dc.CommandText = query;
dc.CommandType = CommandType.Text;
for (int i = 0; i < queryParameters.Length; i++)
{
AddInput(dc, ":" + i, queryParameters[i].ToUpper());
}
DbDataReader reader = dc.ExecuteReader();
Dictionary <String, DbParameterAccessor>
allParams = new Dictionary <string, DbParameterAccessor>();
int position = 0;
while (reader.Read())
{
String paramName = (reader.IsDBNull(0)) ? null : reader.GetString(0);
String dataType = reader.GetString(1);
int length = (reader.IsDBNull(2)) ? 0 : reader.GetInt32(2);
String direction = reader.GetString(3);
OracleParameter dp = new OracleParameter();
dp.Direction = GetParameterDirection(direction);
if (paramName != null)
{
dp.ParameterName = paramName; dp.SourceColumn = paramName;
}
else
{
dp.Direction = ParameterDirection.ReturnValue;
}
dp.OracleType = GetDBType(dataType);
if (length > 0)
{
dp.Size = length;
}
else
{
if (!ParameterDirection.Input.Equals(dp.Direction) || typeof(String).Equals(GetDotNetType(dataType)))
{
dp.Size = 4000;
}
}
allParams[NameNormaliser.NormaliseName(paramName)] =
new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType);
}
return(allParams);
}
public void DetachDatabase(string anotherDatabaseAliasName)
{
using (var command = CurrentConnection.CreateCommand())
{
string sql = $"detach database {anotherDatabaseAliasName}";
command.CommandText = sql;
command.CommandType = CommandType.Text;
s_logger.Debug(sql);
command.ExecuteNonQuery();
_attachedDatabaseAliasNames.Remove(anotherDatabaseAliasName);
}
}
public virtual DbCommand CreateCommand(string statement, CommandType commandType)
{
if (CurrentConnection == null)
{
throw new ApplicationException("Not connected to database");
}
DbCommand dc = CurrentConnection.CreateCommand();
dc.CommandText = statement.Replace("\r", " ").Replace("\n", " ");
dc.CommandType = commandType;
dc.Transaction = CurrentTransaction;
return(dc);
}
public ICollection <ContactsApplicationMapping> GetAll()
{
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = cmdSelectRoot;
return(DaoOracleDbHelper <ContactsApplicationMapping> .ExecSelectCommand(cmd,
DaoOracleDbHelper.MapToContactsApplicationMapping));
}
}
catch
{
throw;
}
}
public void Update(ContactsApplicationMapping entity)
{
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE RUBR_CONTATTI_BACKEND");
sb.Append(" SET REF_ID_CANALE = :pID_CANALE");
sb.Append(", REF_ID_BACKEND = :pID_BACKEND");
sb.Append(", REF_ID_CONTATTO = :pID_CONTATTO");
sb.Append(", REF_ID_TITOLO = :pID_TITOLO");
sb.Append(" WHERE ID_MAP = :pID_MAP");
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = sb.ToString();
cmd.BindByName = true;
cmd.Parameters.AddRange(MapToOracleParameter(entity));
cmd.Parameters.Add("pID_MAP", OracleDbType.Decimal, entity.IdMap, ParameterDirection.Input);
int risp = cmd.ExecuteNonQuery();
if (risp != 1)
{
throw new InvalidOperationException("Aggiornamento non effettuato");
}
}
}
catch (Exception ex)
{
//TASK: Allineamento log - Ciro
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException(ex.Message,
"ORA_ERR009", string.Empty, string.Empty, ex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
err.objectID = Convert.ToString(entity.RefIdReferral);
_log.Error(err);
throw mEx;
}
else
{
throw ex;
}
}
}
public void Delete(decimal id)
{
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
StringBuilder sb = new StringBuilder("DELETE FROM rubr_backend WHERE")
.Append(" id_backend = :p_id");
cmd.CommandText = sb.ToString();
cmd.Parameters.Add(new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.Decimal,
ParameterName = "p_id",
Precision = 10,
Scale = 0,
Value = id
});
int resp = cmd.ExecuteNonQuery();
if (resp != 1)
{
throw new InvalidOperationException("Oggetto non cancellato");
}
}
}
catch (Exception ex)
{
//TASK: Allineamento log - Ciro
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException(ex.Message,
"ORA_ERR006", string.Empty, string.Empty, ex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
log.Error(err);
throw mEx;
}
else
{
throw ex;
}
}
}
public void UpdateMailBody(long idMail, string mailBody)
{
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = "UPDATE MAIL_CONTENT SET MAIL_TEXT = :pMAIL_TEXT"
+ " WHERE ID_MAIL = :pID_MAIL";
cmd.BindByName = true;
cmd.Parameters.Add("pMAIL_TEXT", OracleDbType.Clob, mailBody, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("pID_MAIL", OracleDbType.Decimal, idMail, System.Data.ParameterDirection.Input);
int risp = cmd.ExecuteNonQuery();
if (risp != 1)
{
throw new InvalidOperationException("Errore nell'aggiornamento del testo mail. IdMail: " + idMail);
}
}
}
catch (Exception e)
{
//TASK: Allineamento log - Ciro
if (!e.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException(String.Format("Errore aggiornamento body mail. >> {0}", e.Message),
"ERR_COM_022",
string.Empty,
string.Empty,
e.InnerException);
ErrorLogInfo err = new ErrorLogInfo(mEx);
err.objectID = idMail.ToString();
_log.Error(err);
throw mEx;
}
else
{
throw e;
}
//ErrorLogInfo error = new ErrorLogInfo();
//error.freeTextDetails = e.Message;
//error.logCode = "ERR_COM_022";
//_log.Error(error);
}
}
public void Insert(ContactsApplicationMapping entity)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO RUBR_CONTATTI_BACKEND(");
sb.Append("REF_ID_CANALE, REF_ID_BACKEND, REF_ID_CONTATTO, REF_ID_TITOLO");
sb.Append(") VALUES (");
sb.Append(":pID_CANALE, :pID_BACKEND, :pID_CONTATTO, :pID_TITOLO");
sb.Append(")");
sb.Append(" RETURNING ID_MAP INTO :pID_MAP");
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = sb.ToString();
cmd.BindByName = true;
cmd.Parameters.AddRange(MapToOracleParameter(entity));
cmd.Parameters.Add("pID_MAP", OracleDbType.Decimal, ParameterDirection.Output);
int risp = cmd.ExecuteNonQuery();
if (risp == 1)
{
entity.IdMap = (long)cmd.Parameters["pID_MAP"].Value;
}
}
}
catch (Exception ex)
{
//TASK: Allineamento log - Ciro
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException(ex.Message,
"ORA_ERR009", string.Empty, string.Empty, ex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
err.objectID = Convert.ToString(entity.RefIdReferral);
_log.Error(err);
throw mEx;
}
else
{
throw ex;
}
}
}
private DbDataReader ExecuteParameterQuery(String[] queryParameters, String query)
{
DbCommand dc = CurrentConnection.CreateCommand();
dc.Transaction = CurrentTransaction;
dc.CommandText = query;
dc.CommandType = CommandType.Text;
if (queryParameters.Length == 2)
{
AddInput(dc, "@schema", queryParameters[0]);
AddInput(dc, "@objname", queryParameters[1]);
}
else
{
AddInput(dc, "@objname", queryParameters[0]);
}
DbDataReader reader = dc.ExecuteReader();
return(reader);
}
internal RubricaContattiType GetContattiById(long idContact)
{
RubricaContattiType cont = null;
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = "SELECT VALUE(V0) FROM V_RUBR_CONTATTI_OBJ V0 WHERE ID_CONTACT = :pID_CONTACT";
cmd.BindByName = true;
cmd.Parameters.Add("pID_CONTACT", OracleDbType.Decimal, idContact, System.Data.ParameterDirection.Input);
using (OracleDataReader rd = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow))
{
while (rd.Read())
{
cont = (RubricaContattiType)rd.GetValue(0);
cont.SetEntita(CurrentConnection);
}
}
}
return(cont);
}
/// <summary>
/// Runs a repository operation as a standalone operation without a transaction.
/// It will connect to the database informed.
/// </summary>
/// <param name="dbName">Enumeration of type <see cref="DatabaseName"/>.</param>
/// <param name="fn">Delegate function that will be executed with a parameter of type <see cref="IDbCommand"/>.</param>
/// <param name="commandType"></param>
protected void Run(DatabaseName dbName, Action <SqlCommand> fn, CommandType commandType = CommandType.StoredProcedure)
{
try
{
//we create a new connection without a transaction (for now)
if (!IsEnlisted)
{
InitDbConnection(dbName);
}
else
{
if (CurrentDbName != dbName)
{
throw new DatabaseException($"The current repository is operating on database '{dbName}', but is enlisted to a Unit Of Work on database '{CurrentDbName}'. The repository must be enlisted with the same database as it's Unit Of Work.");
}
}
//we create a command that will be used by the current repository
using (var cmd = CurrentConnection.CreateCommand())
{
cmd.Transaction = CurrentTransaction;
cmd.CommandType = commandType;
fn((SqlCommand)cmd);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
//if the repository is not enlisted within an outer unit of work,
//we can close its connection.
if (!IsEnlisted)
{
CurrentConnection?.Close();
}
}
}
public ContactsApplicationMapping GetById(long id)
{
StringBuilder sb = new StringBuilder(cmdSelectRoot);
sb.Append(" WHERE ID_MAP = :pID_MAP");
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = sb.ToString();
cmd.BindByName = true;
cmd.Parameters.Add("pID_MAP", OracleDbType.Decimal, id, ParameterDirection.Input);
return(DaoOracleDbHelper <ContactsApplicationMapping> .ExecSelectCommand(cmd,
DaoOracleDbHelper.MapToContactsApplicationMapping).FirstOrDefault());
}
}
//catch (OracleException oex)
//{
// throw oex;
//}
catch (Exception ex)
{
//TASK: Allineamento log - Ciro
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException(ex.Message,
"ORA_ERR008", string.Empty, string.Empty, ex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
err.objectID = Convert.ToString(id);
_log.Error(err);
throw mEx;
}
else
{
throw ex;
}
}
}
public void Delete(long id)
{
StringBuilder sb = new StringBuilder();
sb.Append("DELETE FROM RUBR_CONTATTI_BACKEND");
sb.Append(" WHERE ID_MAP = :pID_MAP");
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = sb.ToString();
cmd.BindByName = true;
cmd.Parameters.Add("pID_MAP", OracleDbType.Decimal, id, ParameterDirection.Input);
int risp = cmd.ExecuteNonQuery();
if (risp != 1)
{
throw new InvalidOperationException("Cancellazione non effettuata");
}
}
}
catch (Exception ex)
{
//TASK: Allineamento log - Ciro
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException(ex.Message,
"ORA_ERR010", string.Empty, string.Empty, ex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
err.objectID = Convert.ToString(id);
_log.Error(err);
throw mEx;
}
else
{
throw ex;
}
}
}
public void Insert(ContactsBackendMap entity)
{
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = insertCmdBase;
cmd.BindByName = true;
cmd.Parameters.AddRange(MapToParams(entity));
cmd.Parameters.Add("pID_MAP", OracleDbType.Decimal, System.Data.ParameterDirection.Output);
int risp = cmd.ExecuteNonQuery();
if (risp == 1)
{
entity.Id = Convert.ToInt32(cmd.Parameters["pID_MAP"].Value.ToString());
}
}
}
catch
{
throw;
}
}
public void Update(ContactsBackendMap entity)
{
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = updateCmdBase;
cmd.BindByName = true;
cmd.Parameters.AddRange(MapToParams(entity));
cmd.Parameters.Add("pID_MAP", OracleDbType.Decimal, entity.Id, System.Data.ParameterDirection.InputOutput);
int risp = cmd.ExecuteNonQuery();
if (risp != 1)
{
throw new InvalidOperationException("Errore nell'aggiornamento");
}
}
}
catch (Exception ex)
{
//TASK: Allineamento log - Ciro
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException(ex.Message,
"ORA_ERR009", string.Empty, string.Empty, ex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
err.objectID = Convert.ToString(entity.Id);
_log.Error(err);
throw mEx;
}
else
{
throw ex;
}
}
}
private Dictionary <string, DbParameterAccessor> ReadIntoParams(SchemaObjectName tableOrViewname, string query)
{
Dictionary <string, DbParameterAccessor> accessorDictionary = new Dictionary <string, DbParameterAccessor>();
int position = 0;
DbCommand dc = CurrentConnection.CreateCommand();
dc.Transaction = CurrentTransaction;
dc.CommandText = query;
dc.CommandType = CommandType.Text;
AddSchemaAndTablenameParametersToCommand(dc, tableOrViewname);
using (DbDataReader reader = dc.ExecuteReader())
{
while (reader.Read())
{
DbParameterAccessor accessor = MySqlTypeConverter.BuildDbParameterAccessorFrom(reader, position++);
Console.WriteLine("DbParameter DbFieldName={0}", accessor.DbFieldName);
accessorDictionary.Add(accessor.DbFieldName.ToLowerInvariant(), accessor);
}
}
return(accessorDictionary);
}
public void Update(SendMail.Model.BackEndRefCode entity)
{
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
StringBuilder sb = new StringBuilder("UPDATE rubr_backend SET")
.Append(" backend_code = :p_code, backend_descr = :p_descr")
.Append(", category = :p_cat, descr_plus = :p_descrp")
.Append(" WHERE id_backend = :p_id");
cmd.CommandText = sb.ToString();
cmd.BindByName = true;
cmd.Parameters.Add(new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.Varchar2,
ParameterName = "p_code",
Size = 15,
Value = entity.Codice
});
cmd.Parameters.Add(new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.Varchar2,
ParameterName = "p_descr",
Size = 100,
Value = entity.Descrizione
});
cmd.Parameters.Add(new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.NVarchar2,
ParameterName = "p_cat",
Size = 40,
Value = entity.Categoria
});
cmd.Parameters.Add(new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.Varchar2,
ParameterName = "p_descrp",
Size = 10,
Value = entity.DescrizionePlus
});
cmd.Parameters.Add(new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.Decimal,
ParameterName = "p_id",
Precision = 10,
Scale = 0,
Value = entity.Id
});
int risp = cmd.ExecuteNonQuery();
if (risp != 1)
{
throw new InvalidOperationException("Oggetto non aggiornato");
}
}
}
catch
{
throw;
}
}
private Dictionary <string, DbParameterAccessor> ReadIntoParams(String query, String objname, String schemaname)
{
objname = NameNormaliser.NormaliseName(objname);
DbCommand dc = CurrentConnection.CreateCommand();
dc.Transaction = CurrentTransaction;
dc.CommandText = query;
dc.CommandType = CommandType.Text;
AddInput(dc, "@objname", objname);
if (schemaname != null)
{
AddInput(dc, "@schemaname", NameNormaliser.NormaliseName(schemaname));
}
DbDataReader reader = dc.ExecuteReader();
Dictionary <String, DbParameterAccessor>
allParams = new Dictionary <string, DbParameterAccessor>();
int position = 0;
while (reader.Read())
{
String paramName = (reader.IsDBNull(0)) ? null : reader.GetString(0);
String dataType = reader.GetString(1);
int length = (reader.IsDBNull(2)) ? 0 : System.Convert.ToInt32(reader[2]);
String direction = (reader.IsDBNull(3)) ? "IN" : reader.GetString(3);
byte precision = 0;
byte scale = 0;
if (!reader.IsDBNull(4))
{
scale = System.Convert.ToByte(reader[4]);
}
if (!reader.IsDBNull(5))
{
precision = System.Convert.ToByte(reader[5]);
}
SqlParameter dp = new SqlParameter();
dp.Direction = GetParameterDirection(direction);
if (!String.IsNullOrEmpty(paramName))
{
dp.ParameterName = paramName; dp.SourceColumn = paramName;
}
else
{
dp.Direction = ParameterDirection.ReturnValue;
}
dp.SqlDbType = GetDBType(dataType);
if (precision > 0)
{
dp.Precision = precision;
}
if (scale > 0)
{
dp.Scale = scale;
}
if (length > 0)
{
dp.Size = System.Convert.ToInt32(length);
}
else
{
if (!ParameterDirection.Input.Equals(dp.Direction) || typeof(String).Equals(GetDotNetType(dataType)))
{
dp.Size = 4000;
}
}
allParams[NameNormaliser.NormaliseName(paramName)] =
new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType);
}
reader.Close();
if (allParams.Count == 0)
{
throw new ApplicationException("Cannot read columns/parameters for object " + objname + " - check spelling or access privileges ");
}
return(allParams);
}
private Dictionary <string, DbParameterAccessor> ReadIntoParams(String objname, String query)
{
if (objname.Contains("."))
{
// The object name is multi-part an will not be amended
}
else
{
objname = "[" + NameNormaliser.NormaliseName(objname) + "]";
}
DbCommand dc = CurrentConnection.CreateCommand();
dc.Transaction = CurrentTransaction;
dc.CommandText = query;
dc.CommandType = CommandType.Text;
AddInput(dc, "@objname", objname);
DbDataReader reader = dc.ExecuteReader();
var allParams = new Dictionary <string, DbParameterAccessor>();
int position = 0;
while (reader.Read())
{
String paramName = (reader.IsDBNull(0)) ? null : reader.GetString(0);
String dataType = reader.GetString(1);
int length = (reader.IsDBNull(2)) ? 0 : Convert.ToInt32(reader[2]);
int isOutput = (reader.IsDBNull(3)) ? 0 : Convert.ToInt32(reader[3]);
byte precision = Convert.ToByte(reader[5]);
byte scale = Convert.ToByte(reader[6]);
var dp = new SqlParameter {
Direction = GetParameterDirection(isOutput)
};
if (!String.IsNullOrEmpty(paramName))
{
dp.ParameterName = paramName; dp.SourceColumn = paramName;
}
else
{
dp.Direction = ParameterDirection.ReturnValue;
}
dp.SqlDbType = GetDBType(dataType);
String typeName = NormaliseTypeName(dataType);
if (precision > 0)
{
dp.Precision = precision;
}
if (scale > 0)
{
dp.Scale = scale;
}
if ("NTEXT".Equals(typeName) || ("TEXT".Equals(typeName)))
{
dp.Size = MAX_STRING_SIZE;
}
else if ("NVARCHAR".Equals(typeName) || ("NCHAR".Equals(typeName)))
{
dp.Size = System.Convert.ToInt32(length) / 2;
}
else if (length > 0)
{
dp.Size = Convert.ToInt32(length);
}
else
{
if (!ParameterDirection.Input.Equals(dp.Direction) ||
typeof(String).Equals(GetDotNetType(dataType)))
{
dp.Size = MAX_STRING_SIZE;
}
}
allParams[NameNormaliser.NormaliseName(paramName)] =
new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType);
}
reader.Close();
if (allParams.Count == 0)
{
throw new ApplicationException("Cannot read columns/parameters for object " + objname + " - check spelling or access privileges ");
}
return(allParams);
}
public void Insert(SendMail.Model.BackEndRefCode entity)
{
try
{
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
StringBuilder sb = new StringBuilder("INSERT INTO rubr_backend")
.Append(" (backend_code, backend_descr, category, descr_plus)")
.Append(" VALUES")
.Append(" (:p_code, :p_descr, :p_cat, :p_descrp)")
.Append(" RETURNING id_backend INTO :p_id");
cmd.CommandText = sb.ToString();
cmd.BindByName = true;
cmd.Parameters.Add(new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.Varchar2,
ParameterName = "p_code",
Size = 15,
Value = entity.Codice
});
cmd.Parameters.Add(new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.Varchar2,
ParameterName = "p_descr",
Size = 100,
Value = entity.Descrizione
});
cmd.Parameters.Add(new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.NVarchar2,
ParameterName = "p_cat",
Size = 40,
Value = entity.Categoria
});
cmd.Parameters.Add(new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.Varchar2,
ParameterName = "p_descrp",
Size = 10,
Value = entity.DescrizionePlus
});
cmd.Parameters.Add(new OracleParameter
{
Direction = ParameterDirection.Output,
OracleDbType = OracleDbType.Decimal,
ParameterName = "p_id",
Precision = 10,
Scale = 0
});
int risp = cmd.ExecuteNonQuery();
if (risp != 1)
{
throw new InvalidOperationException("Dato non inserito");
}
entity.Id = ((OracleDecimal)cmd.Parameters["p_id"].Value).Value;
}
}
catch (Exception ex)
{
//Allineamento log - Ciro
if (ex.GetType() != typeof(ManagedException))
{
ManagedException mEx = new ManagedException(ex.Message,
"ERR_ORADB111",
string.Empty,
string.Empty,
ex);
ErrorLogInfo er = new ErrorLogInfo(mEx);
log.Error(er);
throw mEx;
}
else
{
throw;
}
}
}
private Dictionary <string, DbParameterAccessor> ReadIntoParams(String objname, String query)
{
if (objname.Contains("."))
{
String[] schemaAndName = objname.Split(new char[] { '.' }, 2);
objname = schemaAndName[0] + ".." + schemaAndName[1];
}
else
{
objname = NameNormaliser.NormaliseName(objname);
}
DbCommand dc = CurrentConnection.CreateCommand();
dc.Transaction = CurrentTransaction;
dc.CommandText = query;
dc.CommandType = CommandType.Text;
AddInput(dc, "@objname", objname);
DbDataReader reader = dc.ExecuteReader();
Dictionary <String, DbParameterAccessor> allParams = new Dictionary <string, DbParameterAccessor>();
int position = 0;
while (reader.Read())
{
String paramName = (reader.IsDBNull(0)) ? null : reader.GetString(0);
String dataType = reader.GetString(1);
int length = (reader.IsDBNull(2)) ? 0 : System.Convert.ToInt32(reader[2]);
int isOutput = (reader.IsDBNull(3)) ? 0 : System.Convert.ToInt32(reader[3]);
byte precision = (reader.IsDBNull(4)) ? Convert.ToByte(0) : System.Convert.ToByte(reader[4]);
byte scale = (reader.IsDBNull(5)) ? Convert.ToByte(0) : System.Convert.ToByte(reader[5]);
AseParameter dp = new AseParameter();
dp.Direction = GetParameterDirection(isOutput);
if (!String.IsNullOrEmpty(paramName))
{
if (!paramName.StartsWith(ParameterPrefix))
{
dp.ParameterName = ParameterPrefix + paramName;
}
else
{
dp.ParameterName = paramName;
}
dp.SourceColumn = paramName;
}
else
{
dp.Direction = ParameterDirection.ReturnValue;
}
dp.AseDbType = GetDBType(dataType);
String typeName = NormaliseTypeName(dataType);
if (precision > 0)
{
dp.Precision = precision;
}
if (scale > 0)
{
dp.Scale = scale;
}
if ("NTEXT".Equals(typeName) || ("TEXT".Equals(typeName)))
{
dp.Size = MAX_STRING_SIZE;
}
else if (length > 0)
{
dp.Size = System.Convert.ToInt32(length);
}
else
{
if (!ParameterDirection.Input.Equals(dp.Direction) ||
typeof(String).Equals(GetDotNetType(dataType)))
{
dp.Size = MAX_STRING_SIZE;
}
}
allParams[NameNormaliser.NormaliseName(paramName)] = new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType);
}
reader.Close();
return(allParams);
}