public Result <List <T> > ExecuteWithResults <T>(string query, ConexionParameters parameters, out List <T> results)
{
var r = ExecuteWithResults <T>(query, parameters);
results = r.Data as List <T>;
return(r);
}
public Castable ExecuteScalar(string storeProcedure, ConexionParameters parameters)
{
Castable result;
try
{
ConexionOpen();
using (var cmd = this._conexion.CreateCommand())
{
cmd.CommandText = storeProcedure;
cmd.Transaction = _transaction;
cmd.CommandTimeout = ConexionTimeOut;
if (parameters != null)
{
cmd.CommandType = CommandType.StoredProcedure;
foreach (var p in parameters.Parameters)
{
var pt = cmd.CreateParameter();
pt.ParameterName = p.Name;
pt.DbType = p.Type;
pt.Value = p.Value;
pt.Size = p.Size;
pt.Direction = p.Direction;
cmd.Parameters.Add(pt);
}
}
var scalar = cmd.ExecuteScalar();
result = new Castable(scalar);
if (parameters != null)
{
foreach (IDbDataParameter p in cmd.Parameters)
{
var param = parameters.Parameters.FirstOrDefault(x => x.Name == p.ParameterName);
if (param != null)
{
param.Value = p.Value;
}
}
}
}
}
catch (Exception ex)
{
throw new Exception(ThrowExceptionExecuteMessage + ex.Message, ex);
}
finally
{
ConexionClose();
}
return(result);
}
public Result Execute(string query, ConexionParameters parameters)
{
try
{
ConexionOpen();
using (var cmd = this._conexion.CreateCommand())
{
cmd.CommandText = query;
cmd.Transaction = _transaction;
cmd.CommandTimeout = ConexionTimeOut;
if (parameters != null)
{
cmd.CommandType = CommandType.StoredProcedure;
foreach (var p in parameters.Parameters)
{
var pt = cmd.CreateParameter();
pt.ParameterName = p.Name;
pt.DbType = p.Type;
pt.Value = p.Value;
pt.Size = p.Size;
pt.Direction = p.Direction;
cmd.Parameters.Add(pt);
}
}
var rows = cmd.ExecuteNonQuery();
if (parameters != null)
{
foreach (IDbDataParameter p in cmd.Parameters)
{
var param = parameters.Parameters.FirstOrDefault(x => x.Name == p.ParameterName);
if (param != null)
{
param.Value = p.Value;
}
}
}
}
}
catch (Exception ex)
{
throw new Exception(ThrowExceptionExecuteMessage + ex.Message, ex);
}
finally
{
ConexionClose();
}
return(ResultBuilder(parameters));
}
public Result <T> ExecuteWithResultsToObject <T>(string query, ConexionParameters parameters)
{
var results = ExecuteWithResults <T>(query, parameters);
return(new Result <T>()
{
Value = results.Value,
Message = results.Message,
Code = results.Code,
Data = results.Data.FirstOrDefault()
});
}
private Result <T> ResultBuilder <T>(ConexionParameters parameters)
{
var r = ResultBuilder(parameters);
var result = new Result <T>()
{
Value = r.Value,
Message = r.Message
};
return(result);
}
private Result ResultBuilder(ConexionParameters parameters)
{
var result = new Result(true, "the query has been executed correctly");
var resultParameter = parameters?.Parameters?.FirstOrDefault(p => p.Name.ToLower() == "@result" || p.Name.ToLower() == "@presult" || p.Name.ToLower() == "@resultado" || p.Name.ToLower() == "@presultado");
if (resultParameter != null)
{
if (resultParameter.Value.ToString() == "")
{
throw new Exception($"The '{resultParameter.Name}' parameter was not initialized in the stored procedure");
}
result.Value = new Castable(resultParameter.Value).ToBoolean();
}
var messageParameter = parameters?.Parameters?.FirstOrDefault(p => p.Name.ToLower() == "@msg" || p.Name.ToLower() == "@pmsg" || p.Name.ToLower() == "@message" || p.Name.ToLower() == "@pmessage" || p.Name.ToLower() == "@mensaje" || p.Name.ToLower() == "@pmensaje");
if (messageParameter != null)
{
result.Message = new Castable(messageParameter.Value).ToString();
}
var codeParameter = parameters?.Parameters?.FirstOrDefault(p => p.Name.ToLower() == "@codigo" || p.Name.ToLower() == "@pcodigo" || p.Name.ToLower() == "@code" || p.Name.ToLower() == "@pcode");
if (codeParameter != null)
{
if (codeParameter.Value.ToString() == "")
{
throw new Exception($"The '{codeParameter.Name}' parameter was not initialized in the stored procedure");
}
result.Code = new Castable(codeParameter.Value).ToInt32();
}
return(result);
}
public bool RecordsetsExecute(string storeProcedure, ConexionParameters parameters)
{
bool result = false;
try
{
ConexionOpen();
this._commandRecordsets = this._conexion.CreateCommand();
if (_readerRecordsets != null && !_readerRecordsets.IsClosed)
{
_readerRecordsets.Close();
_readerRecordsets.Dispose();
_readerRecordsets = null;
}
_commandRecordsets.CommandText = storeProcedure;
_commandRecordsets.Transaction = _transaction;
_commandRecordsets.CommandTimeout = ConexionTimeOut;
if (parameters != null)
{
_parametersRecordsets = parameters;
_commandRecordsets.CommandType = CommandType.StoredProcedure;
foreach (var p in parameters.Parameters)
{
var pt = _commandRecordsets.CreateParameter();
pt.ParameterName = p.Name;
pt.DbType = p.Type;
pt.Value = p.Value;
pt.Size = p.Size;
pt.Direction = p.Direction;
_commandRecordsets.Parameters.Add(pt);
}
}
_readerRecordsets = _commandRecordsets.ExecuteReader();
switch (this._conexionType)
{
case ConexionType.MSSQLServer:
result = ((SqlDataReader)_readerRecordsets).HasRows;
break;
//case ConexionType.PostgreSQL:
// result = ((PgSqlDataReader)_readerRecordsets).HasRows;
// break;
}
}
catch (Exception ex)
{
throw new Exception(ThrowExceptionExecuteMessage + ex.Message, ex);
}
if (!result)
{
IsMoreRecordsets = _readerRecordsets.NextResult();
}
return(result);
}
public void ExecuteWithResults(string query, ConexionParameters parameters, Action <ConexionCastableRow> action)
{
try
{
ConexionOpen();
using (var cmd = this._conexion.CreateCommand())
{
cmd.CommandText = query;
cmd.Transaction = _transaction;
cmd.CommandTimeout = ConexionTimeOut;
if (parameters != null)
{
cmd.CommandType = CommandType.StoredProcedure;
foreach (var p in parameters.Parameters)
{
var pt = cmd.CreateParameter();
pt.ParameterName = p.Name;
pt.DbType = p.Type;
pt.Value = p.Value;
pt.Size = p.Size;
pt.Direction = p.Direction;
cmd.Parameters.Add(pt);
}
}
var lector = cmd.ExecuteReader();
while (lector.Read())
{
var castableRow = new ConexionCastableRow(lector);
action(castableRow);
}
lector.Close();
if (parameters != null)
{
foreach (IDbDataParameter p in cmd.Parameters)
{
var param = parameters.Parameters.FirstOrDefault(x => x.Name == p.ParameterName);
if (param != null)
{
param.Value = p.Value;
}
}
}
}
}
catch (Exception ex)
{
throw new Exception(ThrowExceptionExecuteMessage + ex.Message, ex);
}
finally
{
ConexionClose();
}
}
public Result <List <T> > ExecuteWithResults <T>(string query, ConexionParameters parameters)
{
List <T> lst = new List <T>();
try
{
ConexionOpen();
using (var cmd = this._conexion.CreateCommand())
{
cmd.CommandText = query;
cmd.Transaction = _transaction;
cmd.CommandTimeout = ConexionTimeOut;
if (parameters != null)
{
cmd.CommandType = CommandType.StoredProcedure;
foreach (var p in parameters.Parameters)
{
var pt = cmd.CreateParameter();
pt.ParameterName = p.Name;
pt.DbType = p.Type;
pt.Value = p.Value;
pt.Size = p.Size;
pt.Direction = p.Direction;
cmd.Parameters.Add(pt);
}
}
var lector = cmd.ExecuteReader();
lst = ResultsInDataReader <T>(ref lector);
lector.Close();
if (parameters != null)
{
foreach (IDbDataParameter p in cmd.Parameters)
{
var param = parameters.Parameters.FirstOrDefault(x => x.Name == p.ParameterName);
if (param != null)
{
param.Value = p.Value;
}
}
}
}
}
catch (Exception ex)
{
throw new Exception(ThrowExceptionExecuteMessage + ex.Message, ex);
}
finally
{
ConexionClose();
}
var result = ResultBuilder <List <T> >(parameters);
result.Data = lst;
return(result);
}
public Result <T> ExecuteScalar <T>(string storeProcedure, ConexionParameters parameters)
{
object scalar;
try
{
ConexionOpen();
using (var cmd = this._conexion.CreateCommand())
{
cmd.CommandText = storeProcedure;
cmd.Transaction = _transaction;
cmd.CommandTimeout = ConexionTimeOut;
if (parameters != null)
{
cmd.CommandType = CommandType.StoredProcedure;
foreach (var p in parameters.Parameters)
{
var pt = cmd.CreateParameter();
pt.ParameterName = p.Name;
pt.DbType = p.Type;
pt.Value = p.Value;
pt.Size = p.Size;
pt.Direction = p.Direction;
cmd.Parameters.Add(pt);
}
}
scalar = cmd.ExecuteScalar();
if (parameters != null)
{
foreach (IDbDataParameter p in cmd.Parameters)
{
var param = parameters.Parameters.FirstOrDefault(x => x.Name == p.ParameterName);
if (param != null)
{
param.Value = p.Value;
}
}
}
}
}
catch (Exception ex)
{
throw new Exception(ThrowExceptionExecuteMessage + ex.Message, ex);
}
finally
{
ConexionClose();
}
var result = ResultBuilder <T>(parameters);
try
{
var type = typeof(Result <T>);
foreach (PropertyInfo pro in type.GetProperties())
{
string propertyName = pro.Name.ToLowerInvariant();
if (propertyName == "data")
{
pro.SetValue(result, scalar, null);
}
else
{
continue;
}
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public Result ExecuteWithResults(string query, ConexionParameters parameters, out DataSet ds)
{
try
{
ConexionOpen();
using (var cmd = this._conexion.CreateCommand())
{
cmd.CommandText = query;
cmd.Transaction = _transaction;
cmd.CommandTimeout = ConexionTimeOut;
if (parameters != null)
{
cmd.CommandType = CommandType.StoredProcedure;
foreach (var p in parameters.Parameters)
{
var pt = cmd.CreateParameter();
pt.ParameterName = p.Name;
pt.DbType = p.Type;
pt.Value = p.Value;
pt.Size = p.Size;
pt.Direction = p.Direction;
cmd.Parameters.Add(pt);
}
}
ds = new DataSet();
switch (this._conexionType)
{
case ConexionType.MSSQLServer:
var sqlAdapter = new SqlDataAdapter((SqlCommand)cmd);
sqlAdapter.Fill(ds);
break;
//case ConexionType.PostgreSQL:
// var pgAdapter = new PgSqlDataAdapter((PgSqlCommand)cmd);
// pgAdapter.Fill(dt);
// break;
}
if (parameters != null)
{
foreach (IDbDataParameter p in cmd.Parameters)
{
var param = parameters.Parameters.FirstOrDefault(x => x.Name == p.ParameterName);
if (param != null)
{
param.Value = p.Value;
}
}
}
}
}
catch (Exception ex)
{
throw new Exception(ThrowExceptionExecuteMessage + ex.Message, ex);
}
finally
{
ConexionClose();
}
return(ResultBuilder(parameters));
}
