public async Task <SqlConnection> GetOpenConnectionAsync(CancellationToken cancellationToken = default(CancellationToken))
{
SqlConnection conn = new SqlConnection();
int retry = Convert.ToInt16(_settingDA.Retry ?? "0");
do
{
try
{
if (conn.State == ConnectionState.Closed)
{
conn.ConnectionString = _settingDA.InformesClientes ?? string.Empty;
await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
retry = 0;
}
}
catch (SqlException sqlex)
{
if (retry <= 3)
{
var message = $"Ha ocurrido un error inesperado al intentar iniciar la conexión con la base de datos [{_settingDA.InformesClientes}]." + sqlex.Message;
_traceHelper.TraceInfo(TraceInfoTypes.Error, nameof(DataAccessHelper), new Dictionary <string, object>
{
[nameof(message)] = message,
[nameof(sqlex)] = sqlex
});
}
retry--;
Thread.Sleep(Convert.ToInt16(_settingDA.RetryMiliseconds ?? "1000"));
}
catch (Exception ex)
{
var message = $"Ha ocurrido un error inesperado al intentar iniciar la conexión con la base de datos [{_settingDA.InformesClientes}]." + ex.Message;
_traceHelper.TraceInfo(TraceInfoTypes.Error, nameof(DataAccessHelper), new Dictionary <string, object>
{
[nameof(message)] = message,
[nameof(ex)] = ex
});
}
}while (retry > 0);
return(conn);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public async Task <IEnumerable <WorkModel> > ListAsync()
{
List <WorkModel> result = null;
try
{
return(await _dataAccessWork.ListAsync());
}
catch (Exception ex)
{
_traceHelper.TraceInfo(TraceInfoTypes.Error, nameof(ListAsync), new Dictionary <string, object>
{
[nameof(ex)] = ex
});
}
return(result);
}
public async Task <int> CreateAsync(AvaluoClienteCab obj)
{
int isCreate = 0;
try
{
using (IDbConnection _conn = await _dataAccess.GetOpenConnectionAsync())
{
try
{
string insert = @"
IF NOT EXISTS (SELECT 1 FROM [INFORMECLIENTE].[V_AVALUO_BIEN] WHERE
[ID_INFORME_CONSULTADO] = @IdInformeConsultado AND [RUT_INFORME_CONSULTADO] = @RutInformeConsultado AND [ID_AVALUO_BIEN] IS NOT NULL)
BEGIN
INSERT INTO [AVALUOBIEN].[TAVALUO_BIEN_CAB]
([ID_INFORME_CONSULTADO]
,[RUT_INFORME_CONSULTADO]
,[EXISTE_DETALLE_AVALUO]
,[NUMERO_PROPIEDADES]
,[FECHA_CONSULTA_AVALUO])
VALUES
(@IdInformeConsultado,
@RutInformeConsultado,
@ExisteDetalleAvaluo,
@NumeroPropiedades,
@FechaConsultaAvaluo);
SELECT CAST(SCOPE_IDENTITY() as int);
END";
isCreate = (obj != null) ? await _conn.ExecuteScalarAsync <int>(insert, obj) : 0;
}
catch (SqlException sqlex)
{
isCreate = -1;
_traceHelper.TraceInfo(TraceInfoTypes.Error, nameof(AvaluoClienteCabService), new Dictionary <string, object>
{
[nameof(sqlex)] = sqlex
});
}
catch (Exception ex)
{
isCreate = -2;
_traceHelper.TraceInfo(TraceInfoTypes.Error, nameof(AvaluoClienteCabService), new Dictionary <string, object>
{
[nameof(ex)] = ex
});
}
finally
{
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
}
}
}
}
catch (Exception ex)
{
isCreate = -3;
_traceHelper.TraceInfo(TraceInfoTypes.Error, nameof(AvaluoClienteCabService), new Dictionary <string, object>
{
[nameof(ex)] = ex
});
}
return(isCreate);
}
public async Task <int> CreateAsync(WorkModel obj)
{
int isCreate = 0;
try
{
using (IDbConnection _conn = await _dataAccess.GetOpenConnectionAsync())
{
try
{
string insert = @"
IF NOT EXISTS (SELECT 1 FROM [DBINFORMESCLIENTES].[INFORMECLIENTE].[V_DIRECTORIO_PERSONA] WHERE
[ID_INFORME_CONSULTADO] = @IdInformeConsultado AND [RUT_INFORME_CONSULTADO] = @RutInformeConsultado
AND [ID_DIRECTORIO_PERSONA] IS NOT NULL)
BEGIN
INSERT INTO [DIRECTORIOPERSONA].[TDIRECTORIO_PERSONA_CAB]
([ID_INFORME_CONSULTADO]
,[RUT_INFORME_CONSULTADO]
,[TIPO_PERSONA]
,[RESULTADO_CONSULTA]
,[FECHA_CONSULTA_PERSONA])
VALUES
(@IdInformeConsultado
,@RutInformeConsultado
,@TipoPersona
,@ResultadoConsulta
,@FechaConsultaPersona);
SELECT CAST(SCOPE_IDENTITY() as int);
END;";
isCreate = (obj != null) ? await _conn.ExecuteScalarAsync <int>(insert, obj) : 0;
}
catch (SqlException sqlex)
{
isCreate = -1;
_traceHelper.TraceInfo(TraceInfoTypes.Error, nameof(CreateAsync), new Dictionary <string, object>
{
[nameof(sqlex)] = sqlex
});
}
catch (Exception ex)
{
isCreate = -2;
_traceHelper.TraceInfo(TraceInfoTypes.Error, nameof(CreateAsync), new Dictionary <string, object>
{
[nameof(ex)] = ex
});
}
finally
{
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
}
}
}
}
catch (Exception ex)
{
isCreate = -3;
_traceHelper.TraceInfo(TraceInfoTypes.Error, nameof(CreateAsync), new Dictionary <string, object>
{
[nameof(ex)] = ex
});
}
return(isCreate);
}