980 lines
33 KiB
C#
980 lines
33 KiB
C#
using System;
|
||
using System.Data;
|
||
using System.Data.OracleClient;
|
||
using System.Data.Common;
|
||
using System.Collections.Generic;
|
||
using Microsoft.Practices.EnterpriseLibrary.Data;
|
||
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
|
||
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
|
||
using System.Data.SqlClient;
|
||
|
||
public class DataAccess
|
||
{
|
||
private static NLog.Logger logger = NLog.LogManager.GetCurrentClassLogger();
|
||
|
||
public static IDataReader ExecuteDataReader(DBProvider _dbprovider, CommandType _commandType, string _commandText)
|
||
{
|
||
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
try
|
||
{
|
||
// Execute the command making sure the connection gets closed in the end
|
||
return db.ExecuteReader(_commandType, _commandText);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, _commandText);
|
||
throw ecc;
|
||
}
|
||
}
|
||
|
||
public static DataTable ExecuteDataTable(DBProvider _dbprovider, CommandType _commandType, string _commandText)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
try
|
||
{
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DataTable table = db.ExecuteDataSet(_commandType, _commandText).Tables[0];
|
||
table.TableName = "TABLENAME";
|
||
return table;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, _commandText);
|
||
try {
|
||
logger.Error(ecc.Message);
|
||
}
|
||
catch { }
|
||
throw ecc;
|
||
}
|
||
|
||
}
|
||
|
||
public static object ExecuteScalar(DBProvider _dbprovider, CommandType _commandType, string _commandText)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
try
|
||
{
|
||
// Execute the command making sure the connection gets closed in the end
|
||
return db.ExecuteScalar(_commandType, _commandText);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, _commandText);
|
||
throw ecc;
|
||
}
|
||
}
|
||
|
||
public static int ExecuteNonQuery(DBProvider _dbprovider, CommandType _commandType, string _commandText)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
int ritorno = -1;
|
||
|
||
using (DbConnection connection = db.CreateConnection())
|
||
{
|
||
connection.Open();
|
||
DbTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable);
|
||
try
|
||
{
|
||
DbCommand comm = null;
|
||
if (_commandType == CommandType.StoredProcedure)
|
||
{
|
||
comm = db.GetStoredProcCommand(_commandText);
|
||
}
|
||
else
|
||
{
|
||
comm = db.GetSqlStringCommand(_commandText);
|
||
}
|
||
|
||
|
||
Parametro returnParam = null;
|
||
|
||
if (_dbprovider != DBProvider.Oracle)
|
||
{
|
||
returnParam = new Parametro();
|
||
returnParam.ParameterName = "RETURN";
|
||
returnParam.DbType = DbType.Int32;
|
||
returnParam.Direction = ParameterDirection.ReturnValue;
|
||
returnParam.Value = -1;
|
||
db.AddParameter(comm, returnParam.ParameterName, returnParam.DbType, returnParam.Direction, returnParam.SourceColumn, returnParam.SourceVersion, returnParam.Value);
|
||
}
|
||
ritorno = db.ExecuteNonQuery(comm, transaction);
|
||
|
||
if (_dbprovider != DBProvider.Oracle)
|
||
ritorno = (Int32)comm.Parameters["@RETURN"].Value;
|
||
|
||
|
||
//Commit the transaction
|
||
transaction.Commit();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
//Roll back the transaction.
|
||
transaction.Rollback();
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, _commandText);
|
||
try {
|
||
logger.Error(ecc.Message);
|
||
}
|
||
catch { }
|
||
throw ecc;
|
||
}
|
||
}
|
||
return ritorno;
|
||
}
|
||
|
||
public static IDataReader ExecuteDataReaderStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DbCommand comm = db.GetStoredProcCommand(nomeStoredProcedure);
|
||
comm.CommandTimeout = 120;
|
||
if (paramList != null)
|
||
{
|
||
foreach (Parametro param in paramList)
|
||
{
|
||
|
||
db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
}
|
||
}
|
||
|
||
try
|
||
{
|
||
// Execute the command making sure the connection gets closed in the end
|
||
return db.ExecuteReader(comm);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
logger.Error(db.ConnectionStringWithoutCredentials);
|
||
logger.Error(nomeStoredProcedure);
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, nomeStoredProcedure, paramList);
|
||
try {
|
||
logger.Error(ecc.Message);
|
||
}
|
||
catch { }
|
||
throw ecc;
|
||
}
|
||
}
|
||
|
||
public static DataTable ExecuteDataTableStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DbCommand comm = db.GetStoredProcCommand(nomeStoredProcedure);
|
||
comm.CommandType = CommandType.StoredProcedure;
|
||
|
||
if (paramList != null)
|
||
{
|
||
foreach (Parametro param in paramList)
|
||
{
|
||
db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
}
|
||
}
|
||
|
||
try
|
||
{
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DataTable table = db.ExecuteDataSet(comm).Tables[0];
|
||
table.TableName = "TABLENAME";
|
||
return table;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, nomeStoredProcedure, paramList);
|
||
try {
|
||
logger.Error(ecc.Message);
|
||
}
|
||
catch { }
|
||
throw ecc;
|
||
}
|
||
}
|
||
|
||
public static DataSet ExecuteDataSetStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DbCommand comm = db.GetStoredProcCommand(nomeStoredProcedure);
|
||
|
||
if (paramList != null)
|
||
{
|
||
foreach (Parametro param in paramList)
|
||
{
|
||
db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
}
|
||
}
|
||
|
||
try
|
||
{
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DataSet ds = db.ExecuteDataSet(comm);
|
||
|
||
return ds;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, nomeStoredProcedure, paramList);
|
||
throw ecc;
|
||
}
|
||
}
|
||
|
||
public static DataSet ExecuteDataSetStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramListInput, List<string> paramNameOutputList)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DbCommand comm = db.GetStoredProcCommand(nomeStoredProcedure);
|
||
|
||
if (paramListInput != null)
|
||
{
|
||
foreach (Parametro param in paramListInput)
|
||
{
|
||
db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
}
|
||
}
|
||
|
||
foreach (string paramOut in paramNameOutputList)
|
||
{
|
||
comm.Parameters.Add(AddRefCursorParameter(paramOut));
|
||
}
|
||
|
||
try
|
||
{
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DataSet dataset = db.ExecuteDataSet(comm);
|
||
return dataset;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, nomeStoredProcedure, paramListInput);
|
||
throw ecc;
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// Lancia la stored il cui nome <20> nomeStoredProcedure con in parametri passati in input.
|
||
/// Viene aggiunto automaticamente un parametro in output il cui nome <20> paramNameOutput che contiene il resulset della stored procedure.
|
||
/// Utilizzare solo per il provider Oracle.
|
||
/// </summary>
|
||
/// <param name="_dbprovider">Provider verso il quale lanciare la stored</param>
|
||
/// <param name="nomeStoredProcedure">Nome della stored procedure da lanciare</param>
|
||
/// <param name="paramListInput">Lista di parametri in input</param>
|
||
/// <param name="paramNameOutput">Nome del parametro in output</param>
|
||
/// <returns></returns>
|
||
public static DataTable ExecuteDataTableStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramListInput, string paramNameOutput)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DbCommand comm = db.GetStoredProcCommand(nomeStoredProcedure);
|
||
|
||
if (paramListInput != null)
|
||
{
|
||
foreach (Parametro param in paramListInput)
|
||
{
|
||
db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
}
|
||
|
||
comm.Parameters.Add(AddRefCursorParameter(paramNameOutput));
|
||
}
|
||
|
||
try
|
||
{
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DataTable table = db.ExecuteDataSet(comm).Tables[0];
|
||
table.TableName = "TABLENAME";
|
||
return table;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, nomeStoredProcedure, paramListInput);
|
||
throw ecc;
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// Lancia la stored il cui nome <20> nomeStoredProcedure con in parametri passati in input.
|
||
/// Oltre ai classici parametri di input pu<70> accettare un Datatable che verr<72> mappato come cursore
|
||
/// Utilizzare solo per il provider Oracle.
|
||
/// </summary>
|
||
/// <param name="_dbprovider">Provider verso il quale lanciare la stored</param>
|
||
/// <param name="nomeStoredProcedure">Nome della stored procedure da lanciare</param>
|
||
/// <param name="paramListInput">Lista di parametri in input</param>
|
||
/// <param name="cursorParamList">Lista di parametri Cursore(DataTable) di input o di output</param>
|
||
/// <returns></returns>
|
||
public static DataTable ExecuteDataTableStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramListInput, List<OracleParameter> cursorParamList, OracleParameter outParam)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DbCommand comm = db.GetStoredProcCommand(nomeStoredProcedure);
|
||
if (paramListInput != null)
|
||
{
|
||
foreach (Parametro param in paramListInput)
|
||
{
|
||
db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
}
|
||
}
|
||
if (cursorParamList != null) //Khorne
|
||
{
|
||
foreach (OracleParameter oraParam in cursorParamList)
|
||
{
|
||
// creation of cursor parameters IN and OUT
|
||
comm.Parameters.Add(oraParam);
|
||
}
|
||
}
|
||
//parametro di output
|
||
comm.Parameters.Add(outParam);
|
||
try
|
||
{
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DataTable table = db.ExecuteDataSet(comm).Tables[0];
|
||
table.TableName = "TABLENAME";
|
||
return table;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, nomeStoredProcedure, paramListInput);
|
||
throw ecc;
|
||
}
|
||
}
|
||
|
||
public static object ExecuteScalarStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DbCommand comm = db.GetStoredProcCommand(nomeStoredProcedure);
|
||
if (paramList != null)
|
||
{
|
||
foreach (Parametro param in paramList)
|
||
{
|
||
|
||
db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
}
|
||
}
|
||
try
|
||
{
|
||
// Execute the command making sure the connection gets closed in the end
|
||
return db.ExecuteScalar(comm);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, nomeStoredProcedure, paramList);
|
||
try {
|
||
logger.Error(ecc.Message);
|
||
}
|
||
catch { }
|
||
throw ecc;
|
||
}
|
||
}
|
||
|
||
public static int ExecuteNonQueryStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, SqlParameter[,] paramList)
|
||
{
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
int ritorno = -1;
|
||
using (DbConnection connection = db.CreateConnection())
|
||
{
|
||
using (DbTransaction tran = connection.BeginTransaction())
|
||
{
|
||
try
|
||
{
|
||
for (int i = 0; i < paramList.GetLength(0); i++)
|
||
{
|
||
using (SqlCommand cmd = new SqlCommand(nomeStoredProcedure, (SqlConnection)connection, (SqlTransaction)tran))
|
||
{
|
||
cmd.CommandType = CommandType.StoredProcedure;
|
||
for (int j = 0; j < paramList.GetLength(1); j++)
|
||
{
|
||
cmd.Parameters.Add(paramList[i, j]);
|
||
}
|
||
ritorno = cmd.ExecuteNonQuery();
|
||
}
|
||
}
|
||
tran.Commit();
|
||
}
|
||
catch
|
||
{
|
||
try {
|
||
logger.Error(string.Concat(nomeStoredProcedure));
|
||
}
|
||
catch { }
|
||
ritorno = -2;
|
||
tran.Rollback();
|
||
connection.Dispose();
|
||
return ritorno;
|
||
}
|
||
}
|
||
}
|
||
return ritorno;
|
||
}
|
||
|
||
private static string paramsToString(Parametro[] par)
|
||
{
|
||
string res = String.Empty;
|
||
foreach (var p in par)
|
||
{
|
||
res += String.Concat(p.ParameterName, ":", p.Value, ";");
|
||
}
|
||
return res;
|
||
}
|
||
|
||
public static int ExecuteNonQueryStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
int ritorno = -1;
|
||
|
||
using (DbConnection connection = db.CreateConnection())
|
||
{
|
||
connection.Open();
|
||
DbTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable);
|
||
try
|
||
{
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DbCommand comm = db.GetStoredProcCommand(nomeStoredProcedure);
|
||
if (paramList != null)
|
||
{
|
||
foreach (Parametro param in paramList)
|
||
{
|
||
db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
}
|
||
}
|
||
Parametro returnParam = null;
|
||
|
||
if (_dbprovider != DBProvider.Oracle)
|
||
{
|
||
returnParam = new Parametro();
|
||
returnParam.ParameterName = "RETURN";
|
||
returnParam.DbType = DbType.Int32;
|
||
returnParam.Direction = ParameterDirection.ReturnValue;
|
||
returnParam.Value = -1;
|
||
|
||
db.AddParameter(comm, returnParam.ParameterName, returnParam.DbType, returnParam.Direction, returnParam.SourceColumn, returnParam.SourceVersion, returnParam.Value);
|
||
}
|
||
|
||
ritorno = db.ExecuteNonQuery(comm, transaction);
|
||
|
||
if (_dbprovider != DBProvider.Oracle)
|
||
ritorno = (Int32)comm.Parameters["@RETURN"].Value;
|
||
//transaction.IsolationLevel = IsolationLevel.
|
||
//Commit the transaction
|
||
transaction.Commit();
|
||
return ritorno;
|
||
|
||
}
|
||
catch (DataBaseException ex)
|
||
{
|
||
try {
|
||
logger.Error(string.Concat(nomeStoredProcedure, paramsToString(paramList.ToArray())));
|
||
}
|
||
catch { }
|
||
//Roll back the transaction.
|
||
transaction.Rollback();
|
||
throw new DataBaseException(ex, db.ConnectionStringWithoutCredentials, nomeStoredProcedure, paramList);
|
||
}
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// Usata solamente per lanciare la procedura avvio_report
|
||
/// </summary>
|
||
/// <param name="_dbprovider"></param>
|
||
/// <param name="nomeStoredProcedure"></param>
|
||
/// <param name="paramList"></param>
|
||
/// <returns></returns>
|
||
public static int ExecuteNonQueryStoredProcedureNoTransaction(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
int ritorno = -1;
|
||
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DbCommand comm = db.GetStoredProcCommand(nomeStoredProcedure);
|
||
if (paramList != null)
|
||
{
|
||
foreach (Parametro param in paramList)
|
||
{
|
||
db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
}
|
||
}
|
||
|
||
try
|
||
{
|
||
// Execute the command making sure the connection gets closed in the end
|
||
db.ExecuteNonQuery(comm);
|
||
ritorno = Convert.ToInt32(comm.Parameters["v_RIS"].Value);
|
||
|
||
return ritorno;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
try {
|
||
logger.Error(string.Concat(nomeStoredProcedure, paramsToString(paramList.ToArray())));
|
||
}
|
||
catch { }
|
||
throw new DataBaseException(ex, db.ConnectionStringWithoutCredentials, nomeStoredProcedure, paramList);
|
||
}
|
||
}
|
||
|
||
public static IDataReader ExecuteDataReaderSqlStatement(DBProvider _dbprovider, string sqlText, List<Parametro> paramList)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DbCommand comm = db.GetSqlStringCommand(sqlText);
|
||
if (paramList != null)
|
||
{
|
||
foreach (Parametro param in paramList)
|
||
{
|
||
db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
}
|
||
}
|
||
try
|
||
{
|
||
return db.ExecuteReader(comm);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
try {
|
||
logger.Error(string.Concat(sqlText, paramsToString(paramList.ToArray())));
|
||
}
|
||
catch { }
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, sqlText, paramList);
|
||
throw ecc;
|
||
}
|
||
}
|
||
|
||
public static DataTable ExecuteDataTableSqlStatement(DBProvider _dbprovider, string sqlText, List<Parametro> paramList)
|
||
{
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DbCommand comm = db.GetSqlStringCommand(sqlText);
|
||
if (paramList != null)
|
||
{
|
||
foreach (Parametro param in paramList)
|
||
{
|
||
db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
}
|
||
}
|
||
try
|
||
{
|
||
DataTable table = db.ExecuteDataSet(comm).Tables[0];
|
||
table.TableName = "TABLENAME";
|
||
return table;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, sqlText, paramList);
|
||
throw ecc;
|
||
}
|
||
|
||
|
||
}
|
||
|
||
public static object ExecuteScalarSqlStatement(DBProvider _dbprovider, string sqlText, List<Parametro> paramList)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DbCommand comm = db.GetSqlStringCommand(sqlText);
|
||
if (paramList != null)
|
||
{
|
||
foreach (Parametro param in paramList)
|
||
{
|
||
|
||
db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
}
|
||
}
|
||
try
|
||
{
|
||
return db.ExecuteScalar(comm);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, sqlText, paramList);
|
||
throw ecc;
|
||
}
|
||
}
|
||
|
||
public static int ExecuteNonQuerySqlStatement(DBProvider _dbprovider, string sqlText, List<Parametro> paramList)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
int ritorno = -1;
|
||
|
||
using (DbConnection connection = db.CreateConnection())
|
||
{
|
||
connection.Open();
|
||
DbTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable);
|
||
try
|
||
{
|
||
// Execute the command making sure the connection gets closed in the end
|
||
DbCommand comm = db.GetSqlStringCommand(sqlText);
|
||
if (paramList != null)
|
||
{
|
||
foreach (Parametro param in paramList)
|
||
{
|
||
|
||
db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
}
|
||
}
|
||
Parametro returnParam = null;
|
||
|
||
if (_dbprovider != DBProvider.Oracle)
|
||
{
|
||
returnParam = new Parametro();
|
||
returnParam.ParameterName = "RETURN";
|
||
returnParam.DbType = DbType.Int32;
|
||
returnParam.Direction = ParameterDirection.ReturnValue;
|
||
returnParam.Value = -2;
|
||
|
||
db.AddParameter(comm, returnParam.ParameterName, returnParam.DbType, returnParam.Direction, returnParam.SourceColumn, returnParam.SourceVersion, returnParam.Value);
|
||
}
|
||
|
||
ritorno = db.ExecuteNonQuery(comm, transaction);
|
||
|
||
if (_dbprovider != DBProvider.Oracle)
|
||
ritorno = (Int32)comm.Parameters["@RETURN"].Value;
|
||
|
||
|
||
//Commit the transaction
|
||
transaction.Commit();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
//Roll back the transaction.
|
||
transaction.Rollback();
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, sqlText, paramList);
|
||
throw ecc;
|
||
}
|
||
}
|
||
|
||
return ritorno;
|
||
}
|
||
|
||
public static bool ExecuteNonQueryMultipleTransaction(DBProvider _dbprovider, List<TransactionStatement> _transactionStatement)
|
||
{
|
||
bool result = true;
|
||
string _sqlstatement = "";
|
||
List<Parametro> _paramlist = null;
|
||
DbCommand command = null;
|
||
// The DataTable to be ret urned
|
||
Database db = GetDatabaseObject(_dbprovider);
|
||
|
||
using (DbConnection connection = db.CreateConnection())
|
||
{
|
||
connection.Open();
|
||
DbTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable);
|
||
|
||
try
|
||
{
|
||
foreach (TransactionStatement _trans in _transactionStatement)
|
||
{
|
||
_sqlstatement = _trans.SqlStatement;
|
||
_paramlist = _trans.ListaParametro;
|
||
// Esegue la transazione.
|
||
|
||
if (_trans.StatementType == CommandType.Text)
|
||
command = db.GetSqlStringCommand(_trans.SqlStatement);
|
||
else if (_trans.StatementType == CommandType.StoredProcedure)
|
||
command = db.GetStoredProcCommand(_trans.SqlStatement);
|
||
|
||
foreach (Parametro param in _trans.ListaParametro)
|
||
db.AddParameter(command, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
|
||
|
||
db.ExecuteNonQuery(command, transaction);
|
||
}
|
||
// Commit the transaction.
|
||
transaction.Commit();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
// Roll back the transaction.
|
||
transaction.Rollback();
|
||
DataBaseException ecc = new DataBaseException(ex, db.ConnectionStringWithoutCredentials, _sqlstatement, _paramlist);
|
||
throw ecc;
|
||
}
|
||
finally
|
||
{
|
||
connection.Close();
|
||
}
|
||
}
|
||
|
||
return result;
|
||
|
||
}
|
||
|
||
private static Database GetDatabaseObject(DBProvider _dbprovider)
|
||
{
|
||
// The DataTable to be ret urned
|
||
Database db = null;
|
||
string connstring;
|
||
|
||
if (_dbprovider == DBProvider.SqlServer)
|
||
{
|
||
connstring = WebConfigParameter.getConnectionString("SqlServerConnection");
|
||
db = new SqlDatabase(connstring);
|
||
}
|
||
else if (_dbprovider == DBProvider.SqlServerStampeC6)
|
||
{
|
||
connstring = WebConfigParameter.getConnectionString("SqlServerStampeC6Connection");
|
||
db = new SqlDatabase(connstring);
|
||
}
|
||
else if (_dbprovider == DBProvider.SqlServerConsulenzaBase)
|
||
{
|
||
connstring = WebConfigParameter.getConnectionString("SqlServerConnectionConsulenzaBase");
|
||
db = new SqlDatabase(connstring);
|
||
}
|
||
else if (_dbprovider == DBProvider.SqlServerConsulenzaUnica)
|
||
{
|
||
connstring = WebConfigParameter.getConnectionString("SqlServerConsulenzaUnicaConnection");
|
||
db = new SqlDatabase(connstring);
|
||
}
|
||
else if (_dbprovider == DBProvider.SqlServerConsulenzaEvoluta)
|
||
{
|
||
connstring = WebConfigParameter.getConnectionString("SqlServerConsulenzaEvolutaConnection");
|
||
db = new SqlDatabase(connstring);
|
||
}
|
||
else if (_dbprovider == DBProvider.SqlServerCatalogoProdotti)
|
||
{
|
||
connstring = WebConfigParameter.getConnectionString("SqlServerCatalogoProdottiConnection");
|
||
db = new SqlDatabase(connstring);
|
||
}
|
||
else if (_dbprovider == DBProvider.SqlServerReportModeler)
|
||
{
|
||
connstring = WebConfigParameter.getConnectionString("SqlServerConnectionReportModeler");
|
||
db = new SqlDatabase(connstring);
|
||
}
|
||
else if (_dbprovider == DBProvider.SqlServerWebTemplateReportModeler)
|
||
{
|
||
connstring = WebConfigParameter.getConnectionString("SqlServerConnectionWebTemplateReportModeler");
|
||
db = new SqlDatabase(connstring);
|
||
}
|
||
else if (_dbprovider == DBProvider.SqlServerReportArchive)
|
||
{
|
||
connstring = WebConfigParameter.getConnectionString("SqlServerConnectionReportArchive");
|
||
db = new SqlDatabase(connstring);
|
||
}
|
||
else if (_dbprovider == DBProvider.Oracle)
|
||
{
|
||
connstring = WebConfigParameter.getConnectionString("OracleConnection");
|
||
db = new OracleDatabase(connstring);
|
||
}
|
||
|
||
return db;
|
||
}
|
||
|
||
private static OracleParameter AddRefCursorParameter(string name)
|
||
{
|
||
return new OracleParameter(name, OracleType.Cursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value);
|
||
}
|
||
}
|
||
|
||
public class Parametro : DbParameter
|
||
{
|
||
public Parametro()
|
||
{
|
||
_sourceversion = DataRowVersion.Current;
|
||
_paramdirection = ParameterDirection.Input;
|
||
}
|
||
|
||
DbType _dbtype;
|
||
|
||
public override DbType DbType
|
||
{
|
||
get
|
||
{
|
||
return _dbtype;
|
||
}
|
||
set
|
||
{
|
||
_dbtype = value;
|
||
}
|
||
}
|
||
|
||
ParameterDirection _paramdirection;
|
||
public override ParameterDirection Direction
|
||
{
|
||
get
|
||
{
|
||
return _paramdirection;
|
||
}
|
||
set
|
||
{
|
||
_paramdirection = value;
|
||
}
|
||
}
|
||
|
||
bool _nullable;
|
||
public override bool IsNullable
|
||
{
|
||
get
|
||
{
|
||
return _nullable;
|
||
}
|
||
set
|
||
{
|
||
_nullable = value;
|
||
}
|
||
}
|
||
|
||
string _paramName;
|
||
public override string ParameterName
|
||
{
|
||
get
|
||
{
|
||
return _paramName;
|
||
}
|
||
set
|
||
{
|
||
_paramName = value;
|
||
}
|
||
}
|
||
|
||
public override void ResetDbType()
|
||
{
|
||
throw new Exception("The method or operation is not implemented.");
|
||
}
|
||
|
||
int _size;
|
||
public override int Size
|
||
{
|
||
get
|
||
{
|
||
return _size;
|
||
}
|
||
set
|
||
{
|
||
_size = value;
|
||
}
|
||
}
|
||
|
||
string _sourceColumn;
|
||
public override string SourceColumn
|
||
{
|
||
get
|
||
{
|
||
return _sourceColumn;
|
||
}
|
||
set
|
||
{
|
||
_sourceColumn = value;
|
||
}
|
||
}
|
||
|
||
bool _sourcecolumnnullmapping;
|
||
public override bool SourceColumnNullMapping
|
||
{
|
||
get
|
||
{
|
||
return _sourcecolumnnullmapping;
|
||
}
|
||
set
|
||
{
|
||
_sourcecolumnnullmapping = value;
|
||
}
|
||
}
|
||
|
||
DataRowVersion _sourceversion;
|
||
public override DataRowVersion SourceVersion
|
||
{
|
||
get
|
||
{
|
||
return _sourceversion;
|
||
}
|
||
set
|
||
{
|
||
_sourceversion = value;
|
||
}
|
||
}
|
||
|
||
object _value;
|
||
public override object Value
|
||
{
|
||
get
|
||
{
|
||
return _value;
|
||
}
|
||
set
|
||
{
|
||
_value = value;
|
||
}
|
||
}
|
||
}
|
||
|
||
public class TransactionStatement
|
||
{
|
||
private string _sqlstatement;
|
||
private CommandType _statementtype;
|
||
private List<Parametro> _paramlist;
|
||
|
||
public TransactionStatement()
|
||
{
|
||
_sqlstatement = "";
|
||
_paramlist = new List<Parametro>();
|
||
_statementtype = CommandType.Text;
|
||
}
|
||
|
||
public List<Parametro> ListaParametro
|
||
{
|
||
get
|
||
{
|
||
return _paramlist;
|
||
}
|
||
}
|
||
|
||
public string SqlStatement
|
||
{
|
||
get
|
||
{
|
||
return _sqlstatement;
|
||
}
|
||
set
|
||
{
|
||
_sqlstatement = value;
|
||
}
|
||
}
|
||
|
||
public void addParametroToList(Parametro _parametro)
|
||
{
|
||
_paramlist.Add(_parametro);
|
||
}
|
||
|
||
public CommandType StatementType
|
||
{
|
||
get
|
||
{
|
||
return _statementtype;
|
||
}
|
||
set
|
||
{
|
||
_statementtype = value;
|
||
}
|
||
}
|
||
}
|