public bool DBBulkInsert(DataTable tabla, string nombreTablaBD)
{
bool rta = false;
using (var dbBulkcopy = new IBM.Data.DB2.DB2BulkCopy(connectionString, IBM.Data.DB2.DB2BulkCopyOptions.Default))
{
// my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
if (tabla.Rows.Count > 0)
{
foreach (DataColumn col in tabla.Columns)
{
dbBulkcopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
dbBulkcopy.BulkCopyTimeout = 600;
dbBulkcopy.DestinationTableName = nombreTablaBD;
try
{
dbBulkcopy.WriteToServer(tabla);
dbBulkcopy.Close();
DBFunctions.closeConnection();
rta = true;
}
catch (Exception z)
{
dbBulkcopy.Close();
DBFunctions.closeConnection();
//rta = false;
}
}
}
return(rta);
}
protected bool insertTable(DataTable table1, DataTable table2)
{
if (ddltabla.SelectedValue == "MITEMS")
{
//CAMBIAR REFERENCIA POR MEDIO DE LA LINEA
for (int i = 0; i < table2.Rows.Count; i++)
{
string codItAlma = "";
Referencias.Guardar(table2.Rows[i][0].ToString().Replace('"', ' ').Trim(), ref codItAlma, DBFunctions.SingleData("SELECT plin_tipo FROM plineaitem WHERE plin_codigo='" + table2.Rows[i][2].ToString().Trim() + "'"));
table2.Rows[i][0] = codItAlma;
}
}
//Insert
bool rta = false;
string servidor = ConfigurationManager.AppSettings["Server" + GlobalData.getEMPRESA()];
string database = ConfigurationManager.AppSettings["DataBase" + GlobalData.getEMPRESA()];
string usuario = ConfigurationManager.AppSettings["UID"];
string password = ConfigurationManager.AppSettings["PWD" + GlobalData.getEMPRESA()];
string timeout = ConfigurationManager.AppSettings["ConnectionTimeout"];
string port = ConfigurationManager.AppSettings["DataBasePort"];
AMS.CriptoServiceProvider.Crypto miCripto = new Crypto(AMS.CriptoServiceProvider.Crypto.CryptoProvider.TripleDES);
miCripto.IV = ConfigurationManager.AppSettings["VectorInicialEncriptacion"];
miCripto.Key = ConfigurationManager.AppSettings["ValorConcatClavePrivada"];
string newPwd = miCripto.DescifrarCadena(password);
string connectionString = "Server=" + servidor + ":" + port + ";DataBase=" + database + ";UID=" + usuario + ";PWD=" + newPwd + "";
//IBM.Data.DB2.DB2BulkCopy dbBulkcopy = new IBM.Data.DB2.DB2BulkCopy(connectionString, IBM.Data.DB2.DB2BulkCopyOptions.KeepIdentity);
using (var dbBulkcopy = new IBM.Data.DB2.DB2BulkCopy(connectionString, IBM.Data.DB2.DB2BulkCopyOptions.Default))
{
// my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
if (table1.Rows.Count == table2.Columns.Count)
{
foreach (DataColumn col in table2.Columns)
{
dbBulkcopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
dbBulkcopy.BulkCopyTimeout = 600;
dbBulkcopy.DestinationTableName = ddltabla.SelectedValue;
try
{
dbBulkcopy.WriteToServer(table2);
dbBulkcopy.Close();
DBFunctions.closeConnection();
rta = true;
}
catch (Exception z)
{
dbBulkcopy.Close();
DBFunctions.closeConnection();
rta = false;
Utils.MostrarAlerta(Response, "No se ingresó ningún registro debido a que el archivo Excel presenta fallas. \n Por favor revise su archivo o pruebe con el botón Revisar y Subir");
lbError.Text = z.Message;
}
}
}
return(rta);
}
public string ActualizarPrecios(DataTable dtActualizacion, DataTable dtNuevos, bool accion)
{
string rta = "";
//Lamentablemente cuando se habla de actualizar datos no sirve bulkcopy, ya que este sólo inserta
if (dtActualizacion.Rows.Count > 0)
{
ArrayList sqlString = new ArrayList();
foreach (DataRow row in dtActualizacion.Rows)
{
sqlString.Add("UPDATE DBXSCHEMA.MPRECIOITEM SET MPRE_PRECIO = '" + row[3] + "' WHERE MITE_CODIGO = '" + row[1] + "' AND PPRE_CODIGO = '" + this.codigoLista + "'");
}
if (DBFunctions.Transaction(sqlString))
{
rta = "OK";
}
else
{
rta = "falló ACTUALIZACIÓN de datos: " + DBFunctions.exceptions + "<br />";
}
}
//Items nuevos en la tabla mprecioitem
if (dtNuevos.Rows.Count > 0)
{
//dtNuevos.Columns.Remove("MITE_CODIGO");
//dtNuevos.Columns[0].ColumnName = "MITE_CODIGO";
//dtNuevos.AcceptChanges();
DataTable lasLineas = DBFunctions.Request(new DataSet(), IncludeSchema.NO, "SELECT PLIN_TIPO, PLIN_CODIGO FROM DBXSCHEMA.PLINEAITEM").Tables[0];
for (int i = 0; i < dtNuevos.Rows.Count; i++)
{
string codItAlma = "";
try
{
Referencias.Guardar(dtNuevos.Rows[i][0].ToString().Replace('"', ' ').Trim(), ref codItAlma, lasLineas.Select("PLIN_CODIGO = '" + dtNuevos.Rows[i][1].ToString().Trim() + "'")[0].ItemArray[0].ToString());//DBFunctions.SingleData("SELECT plin_tipo FROM plineaitem WHERE plin_codigo='" + dtNuevos.Rows[i][1].ToString().Trim() + "'"));
}catch
{
Referencias.Guardar(dtNuevos.Rows[i][0].ToString().Replace('"', ' ').Trim(), ref codItAlma, DBFunctions.SingleData("SELECT plin_tipo FROM plineaitem WHERE plin_codigo='" + dtNuevos.Rows[i][1].ToString().Trim() + "'"));
}
dtNuevos.Rows[i][0] = codItAlma;
dtNuevos.Rows[i][1] = codigoLista;
}
if (rta.Length < 3)
{
string servidor = ConfigurationManager.AppSettings["Server" + GlobalData.getEMPRESA()];
string database = ConfigurationManager.AppSettings["DataBase" + GlobalData.getEMPRESA()];
string usuario = ConfigurationManager.AppSettings["UID"];
string password = ConfigurationManager.AppSettings["PWD" + GlobalData.getEMPRESA()];
string timeout = ConfigurationManager.AppSettings["ConnectionTimeout"];
string port = ConfigurationManager.AppSettings["DataBasePort"];
AMS.CriptoServiceProvider.Crypto miCripto = new Crypto(AMS.CriptoServiceProvider.Crypto.CryptoProvider.TripleDES);
miCripto.IV = ConfigurationManager.AppSettings["VectorInicialEncriptacion"];
miCripto.Key = ConfigurationManager.AppSettings["ValorConcatClavePrivada"];
string newPwd = miCripto.DescifrarCadena(password);
string connectionString = "Server=" + servidor + ":" + port + ";DataBase=" + database + ";UID=" + usuario + ";PWD=" + newPwd + "";
//IBM.Data.DB2.DB2BulkCopy dbBulkcopy = new IBM.Data.DB2.DB2BulkCopy(connectionString, IBM.Data.DB2.DB2BulkCopyOptions.KeepIdentity);
using (var dbBulkcopy = new IBM.Data.DB2.DB2BulkCopy(connectionString, IBM.Data.DB2.DB2BulkCopyOptions.Default))
{
// my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
foreach (DataColumn col in dtNuevos.Columns)
{
dbBulkcopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
dbBulkcopy.BulkCopyTimeout = 600;
dbBulkcopy.DestinationTableName = "MPRECIOITEM";
try
{
dbBulkcopy.WriteToServer(dtNuevos);
dbBulkcopy.Close();
DBFunctions.closeConnection();
rta = "OK";
}
catch (Exception z)
{
dbBulkcopy.Close();
rta = "<br />" + "falló INSERCIÓN de datos: " + z.Message;
DBFunctions.closeConnection();
}
}
}
}
return(rta);
}