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)); }