Пример #1
0
        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);
        }
Пример #2
0
        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);
        }
Пример #3
0
        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);
        }