private void DropYCrearTabla(DisenioTxt disenio, SqlConnection conn, SqlTransaction tran)
{
var cmdCreateTable = new SqlCommand($"IF OBJECT_ID('dbo.{TableName}_{((int)disenio)}', 'U') IS NOT NULL DROP TABLE {TableName}_{((int)disenio)}", conn);
cmdCreateTable.Transaction = tran;
cmdCreateTable.ExecuteNonQuery();
cmdCreateTable.CommandText = $"CREATE TABLE {TableName}_{((int)disenio)} (";
_formatos[disenio].OrderBy(d => d.Indice).ToList().ForEach(d => {
switch (d.TipoDato)
{
case SqlDbType.BigInt:
cmdCreateTable.CommandText += $"{d.Nombre} BIGINT NULL ";
break;
case SqlDbType.Int:
cmdCreateTable.CommandText += $"{d.Nombre} INT NULL ";
break;
case SqlDbType.Decimal:
cmdCreateTable.CommandText += $"{d.Nombre} Decimal(18,4) NULL ";
break;
case SqlDbType.VarChar:
cmdCreateTable.CommandText += $"{d.Nombre} VARCHAR({d.LongitudCampo}) NULL ";
break;
case SqlDbType.DateTime:
cmdCreateTable.CommandText += $"{d.Nombre} DATETIME NULL ";
break;
default:
break;
}
if (d.Indice < _formatos[disenio].Count())
{
cmdCreateTable.CommandText += ", ";
}
});
cmdCreateTable.CommandText += ")";
cmdCreateTable.ExecuteNonQuery();
}
private async Task ProcesarFormato(DisenioTxt formato, List <List <string> > lineas, SqlConnection conn, SqlTransaction tran)
{
List <List <string> > lineasDisenio = new List <List <string> >();
IEnumerable <IEnumerable <List <string> > > lineasBulk = null;
try
{
await Task.Run(() =>
{
SetLabelProgreso(lblProgreso, "Obteniendo registros del diseño " + ((int)formato).ToString() + "...");
lineasDisenio.AddRange(lineas.Where(l => l[0] == ((int)formato).ToString()));
});
lineasBulk = lineasDisenio.Partir(10000);
progressBar.Maximum = lineasBulk.Count();
progressBar.Value = 0;
await Task.Run(() =>
{
SetLabelProgreso(lblProgreso, "Procesando diseño " + ((int)formato).ToString() + "...");
lineasBulk.ToList().ForEach(lin => {
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
{
bulkCopy.DestinationTableName = "dbo." + TableName + "_" + ((int)formato).ToString();
try
{
bulkCopy.WriteToServer(MakeTable(lin.ToList(), formato));
IncrementarProgress(progressBar, 1);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
});
});
}
finally
{
lineas.RemoveAll(r => r[0] == ((int)formato).ToString());
lineasDisenio.Clear();
lineasDisenio = null;
lineasBulk = null;
}
}
private void CargarFormatos(DisenioTxt disenio)
{
if (!_formatos.ContainsKey(disenio))
{
_formatos.Add(disenio, new List <Columna>());
}
_formatos[disenio].Clear();
switch (disenio)
{
case DisenioTxt.D4305:
#region diseño 4305
_formatos[disenio].Add(new Columna("TipoIdentificacion", 1, 2, SqlDbType.VarChar));
_formatos[disenio].Add(new Columna("NroIdentificacion", 2, 11, SqlDbType.VarChar));
_formatos[disenio].Add(new Columna("Denominacion", 3, 55, SqlDbType.VarChar));
_formatos[disenio].Add(new Columna("Categoria", 4, 1, SqlDbType.Int));
_formatos[disenio].Add(new Columna("ResidenciaSector", 5, 1, SqlDbType.Int));
_formatos[disenio].Add(new Columna("Gobierno", 6, 1, SqlDbType.Int));
_formatos[disenio].Add(new Columna("Provincia", 7, 2, SqlDbType.VarChar));
_formatos[disenio].Add(new Columna("Situacion", 8, 2, SqlDbType.VarChar));
_formatos[disenio].Add(new Columna("Vinculacion", 9, 1, SqlDbType.Int));
_formatos[disenio].Add(new Columna("PrevisionesAsistenciaCrediticia", 10, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("PrevisionesParticipaciones", 11, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("PrevRespEventGtiaOtorgadas", 12, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("IncPrevMinPermCat4o5Gral", 13, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("IncPrevMinPermCat4o5Normas", 14, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("MaxAsistCliVincODeuda05", 15, 14, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("TotalFinancionesDeudor", 16, 14, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("ActividadPrincipal", 17, 3, SqlDbType.VarChar));
_formatos[disenio].Add(new Columna("InformacionTransitoria", 18, 1, SqlDbType.Int));
_formatos[disenio].Add(new Columna("DeudorLey25326Art26", 19, 1, SqlDbType.Int));
_formatos[disenio].Add(new Columna("Refinanciaciones", 20, 1, SqlDbType.Int));
_formatos[disenio].Add(new Columna("RecatObligatoria", 21, 1, SqlDbType.Int));
_formatos[disenio].Add(new Columna("SituacionJuridica", 22, 1, SqlDbType.Int));
_formatos[disenio].Add(new Columna("IrrecuperablesDispTecnica", 23, 1, SqlDbType.Int));
_formatos[disenio].Add(new Columna("DiasAtrasoEnPago", 24, 4, SqlDbType.Int));
_formatos[disenio].Add(new Columna("MiPyME", 25, 2, SqlDbType.VarChar));
_formatos[disenio].Add(new Columna("SituacionSinReclasificar", 26, 2, SqlDbType.VarChar));
#endregion
break;
case DisenioTxt.D4306:
#region Diseño 4306
_formatos[disenio].Add(new Columna("TipoIdentificacion", 1, 2, SqlDbType.VarChar));
_formatos[disenio].Add(new Columna("NroIdentificacion", 2, 11, SqlDbType.VarChar));
_formatos[disenio].Add(new Columna("TipoAsistenciaCrediticia", 3, 2, SqlDbType.Int));
_formatos[disenio].Add(new Columna("GtiaPrefACapIntDevengDeudaVenc", 4, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("GtiaPrefACapIntDevengDeudaTotal", 5, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("GtiaPrefACapIntDevengNoPrevDeudaVenc", 6, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("GtiaPrefACapIntDevengNoPrevDeudaTotal", 7, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("GtiaPrefBIntDevengPrevDeudaVenc", 8, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("GtiaPrefBIntDevengPrevDeudaTotal", 9, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("SinGtiaPrefCapIntDevengNoPrevDeudaVenc", 10, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("SinGtiaPrefCapIntDevengNoPrevDeudaTotal", 11, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("SinGtiaPrefIntDevengPrevDeudaVenc", 12, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("SinGtiaPrefIntDevengPrevDeudaTotal", 13, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("PrevMinCreAdicionales", 14, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("FechaUltimaRefinanciacion", 15, 8, SqlDbType.DateTime));
_formatos[disenio].Add(new Columna("FinanciacionParaMiPyMEs", 16, 12, SqlDbType.Decimal));
#endregion
break;
case DisenioTxt.D4307:
#region Diseño 4307
_formatos[disenio].Add(new Columna("TipoIdentificacion", 1, 2, SqlDbType.VarChar));
_formatos[disenio].Add(new Columna("NroIdentificacion", 2, 11, SqlDbType.VarChar));
_formatos[disenio].Add(new Columna("ParticiOtrasSociedadesDeducRPC", 3, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("ParticiOtrasSociedadesNoDeducRPC", 4, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("AdelCtaCteAcordadosConContragtiasPrefA", 5, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("AdelCtaCteAcordadosConContragtiasPrefB", 6, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("AdelCtaCteAcordadosSinContragtiasPref", 7, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("ResponsEventualesConContragtiasPrefA", 8, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("ResponsEventualesConContragtiasPrefB", 9, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("ResponsEventualesSinContragtiasPref", 10, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("GtiasOtorgadasConContragtiasPrefA", 11, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("GtiasOtorgadasConContragtiasPrefB", 12, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("GtiasOtorgadasSinContragtiasPref", 13, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("MontoPorIrrecuperablesCtasOrden", 14, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("FinanciacionesFianzasAvalesOtrasRespOtorExt", 15, 12, SqlDbType.Decimal));
_formatos[disenio].Add(new Columna("DiferenciaPorAdquisicionCartera", 16, 12, SqlDbType.Decimal));
#endregion
break;
default:
break;
}
}
private void InsertarRegistroDisenio(DisenioTxt disenio, List <string> registro, SqlConnection conn, SqlTransaction tran)
{
var cmdInsert = new SqlCommand("");
cmdInsert.Connection = conn;
cmdInsert.Transaction = tran;
switch (disenio)
{
case DisenioTxt.D4305:
cmdInsert.CommandText = "INSERT INTO " + TableName + "_4305 (";
break;
case DisenioTxt.D4306:
cmdInsert.CommandText = "INSERT INTO " + TableName + "_4306 (";
break;
case DisenioTxt.D4307:
cmdInsert.CommandText = "INSERT INTO " + TableName + "_4307 (";
break;
default:
break;
}
_formatos[disenio].ForEach(d => cmdInsert.CommandText += $"{d.Nombre}{(d.Indice < _formatos[disenio].Count() ? "," : string.Empty)} ");
cmdInsert.CommandText += ") Values ( ";
_formatos[disenio].ForEach(d => cmdInsert.CommandText += $"@{d.Nombre}{(d.Indice < _formatos[disenio].Count() ? "," : string.Empty)} ");
cmdInsert.CommandText += ")";
_formatos[disenio].ForEach(d => cmdInsert.Parameters.Add(d.Nombre, d.TipoDato));
_formatos[disenio].ForEach(d =>
{
try
{
switch (d.TipoDato)
{
case SqlDbType.BigInt:
cmdInsert.Parameters[d.Nombre].Value = (registro[d.Indice].ToString().Any() ? long.Parse(registro[d.Indice]) : (object)DBNull.Value);
break;
case SqlDbType.Int:
cmdInsert.Parameters[d.Nombre].Value = (registro[d.Indice].ToString().Any() ? int.Parse(registro[d.Indice]) : (object)DBNull.Value);
break;
case SqlDbType.Decimal:
cmdInsert.Parameters[d.Nombre].Value = (registro[d.Indice].ToString().Any() ? Decimal.Parse(registro[d.Indice]) : (object)DBNull.Value);
break;
case SqlDbType.VarChar:
cmdInsert.Parameters[d.Nombre].Value = (registro[d.Indice].ToString().Any() ? registro[d.Indice].ToString() : (object)DBNull.Value);
break;
case SqlDbType.DateTime:
cmdInsert.Parameters[d.Nombre].Value = (registro[d.Indice].ToString().Any() ? registro[d.Indice].ToString() : (object)DBNull.Value);
break;
default:
break;
}
}
catch (Exception ex)
{
throw ex;
}
});
cmdInsert.ExecuteNonQuery();
}
private DataTable MakeTable(List <List <string> > lines, DisenioTxt formato)
{
// Create a new DataTable named NewProducts.
DataTable tablaSQL = new DataTable(TableName + "_4305");
_formatos[formato].ForEach(f =>
{
try
{
DataColumn col = new DataColumn();
switch (f.TipoDato)
{
case SqlDbType.BigInt:
col.DataType = System.Type.GetType("System.Int64");
break;
case SqlDbType.Int:
col.DataType = System.Type.GetType("System.Int32");
break;
case SqlDbType.VarChar:
col.DataType = System.Type.GetType("System.String");
break;
case SqlDbType.Decimal:
col.DataType = System.Type.GetType("System.Decimal");
break;
case SqlDbType.DateTime:
col.DataType = System.Type.GetType("System.DateTime");
break;
default:
col.DataType = System.Type.GetType("System.String");
break;
}
col.ColumnName = f.Nombre;
tablaSQL.Columns.Add(col);
return;
}
catch (Exception ex)
{
return;
}
});
lines.ForEach(l =>
{
var terminos = l;
if (terminos.Count() > 0)
{
DataRow row;
row = tablaSQL.NewRow();
_formatos[formato].ForEach(f =>
{
try
{
if ((terminos[f.Indice].Trim().Count() == 0))
{
row[f.Nombre] = DBNull.Value;
}
else
{
switch (f.TipoDato)
{
case SqlDbType.VarChar:
row[f.Nombre] = terminos[f.Indice].Trim();
break;
case SqlDbType.Decimal:
row[f.Nombre] = Decimal.Parse(terminos[f.Indice].Trim());
break;
case SqlDbType.Int:
row[f.Nombre] = Int32.Parse(terminos[f.Indice].Trim());
break;
case SqlDbType.BigInt:
row[f.Nombre] = Int64.Parse(terminos[f.Indice].Trim());
break;
case SqlDbType.DateTime:
var d = DateTime.ParseExact(terminos[f.Indice].Trim(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd");
row[f.Nombre] = DateTime.Parse(d.Trim());
break;
default:
row[f.Nombre] = terminos[f.Indice].Trim();
break;
}
}
}
catch (Exception ex)
{
// Throw ex
return;
}
});
tablaSQL.Rows.Add(row);
}
});
return(tablaSQL);
}