public void Start(BaseSync baseSync) { //=====NpgsqlConnection pgsqlConnectionOrigem = new NpgsqlConnection(ConfigRuntime.ConectionBancoOrigem); //=====pgsqlConnectionOrigem.Open(); DbConnection mConnectionOrigem = ManagerConnection.CreateConnection(ConfigRuntime.TypeDatabasaOrigem, ConfigRuntime.ConectionBancoOrigem); mConnectionOrigem.Open(); NpgsqlConnection pgsqlConnectionDestino = new NpgsqlConnection(ConfigRuntime.ConectionBancoDestino); pgsqlConnectionDestino.Open(); List <String> ordemCamposInsert = null; String sqlCount; string sqlRead = MontarSQLRead(baseSync, out ordemCamposInsert, out sqlCount); this.OnSqlCreate("SQL count", sqlCount, baseSync); this.OnSqlCreate("SQL read", sqlRead, baseSync); //System.Windows.Forms.Clipboard.SetText(sqlRead); //System.Windows.Forms.MessageBox.Show("OK"); DbCommand cmdCount = ManagerConnection.CreateCommand(ConfigRuntime.TypeDatabasaOrigem, sqlCount, mConnectionOrigem); //=====NpgsqlCommand cmdCount = new NpgsqlCommand(sqlCount, pgsqlConnectionOrigem); this.mCountMax = Convert.ToInt64(cmdCount.ExecuteScalar()); cmdCount.Dispose(); this.mCountMax++; //adiciona campos fixos for (int i = 0; i < baseSync.mFixedReference.Count; i++) { ordemCamposInsert.Add(baseSync.mFixedReference[i].campoDestino); } //adiciona campo ID, se houver necessidade if (baseSync.mIdGenerateProcess == true) { ordemCamposInsert.Add(baseSync.mColumnDestinoID); } DbCommand command = ManagerConnection.CreateCommand(ConfigRuntime.TypeDatabasaOrigem, sqlRead, mConnectionOrigem); DbDataReader dr = command.ExecuteReader(System.Data.CommandBehavior.Default); //=====NpgsqlCommand command = new NpgsqlCommand(sqlRead, pgsqlConnectionOrigem); //=====NpgsqlDataReader dr = command.ExecuteReader(System.Data.CommandBehavior.Default); if (baseSync.mDeleteTableDestinoAntesImportar == true) { //NpgsqlCommand npgsDelete = new NpgsqlCommand("TRUNCATE " + baseSync.mTableDestino + " CASCADE", pgsqlConnectionDestino); NpgsqlCommand npgsDelete = new NpgsqlCommand("DELETE FROM " + baseSync.mTableDestino, pgsqlConnectionDestino); npgsDelete.ExecuteNonQuery(); } //http://stackoverflow.com/questions/11237431/insert-data-from-textbox-to-postgres-sql while (dr.Read()) { NpgsqlCommand cmd = new NpgsqlCommand(); cmd.Connection = pgsqlConnectionDestino; //adiciona campos de leitura for (int i = 0; i < dr.FieldCount; i++) { //System.Windows.Forms.Clipboard.SetText(dr.FieldCount.ToString() + "/" + i.ToString() + "/ " + ordemCamposInsert[i]); Object valor = null; try { valor = dr.GetValue(i); } catch (Exception e) { System.Windows.Forms.MessageBox.Show("Erro:" + e.ToString()); } String nomeParametro = ordemCamposInsert[i]; if (i < baseSync.mSimpleReference.Count) { if (baseSync.mSimpleReference[i].mConverter != null) { valor = baseSync.mSimpleReference[i].mConverter.ConvertValue(valor); } } //se passou procura pelas ref dos joins if (i >= baseSync.mSimpleReference.Count) { var join = baseSync.mJoinReference.SelectMany(t => t.joinReferences).Where(t => t.campoDestino == nomeParametro).FirstOrDefault(); if (join != null) { if (join.mConverter != null) { valor = join.mConverter.ConvertValue(valor); } } } cmd.Parameters.Add(new NpgsqlParameter(nomeParametro, valor)); //command.Parameters.Add(new NpgsqlParameter("pw", tb2.Text)); } //adiciona campos fixos for (int i = 0; i < baseSync.mFixedReference.Count; i++) { Object valor = baseSync.mFixedReference[i].valor; //se tiver uma instrução especial, processa if (valor.ToString().Contains("[%ROW]")) { valor = valor.ToString().Replace("[%ROW]", (this.mCount + 1).ToString()); } String nomeParametro = baseSync.mFixedReference[i].campoDestino; cmd.Parameters.Add(new NpgsqlParameter(nomeParametro, valor)); } //adiciona campo ID, se houver necessidade if (baseSync.mIdGenerateProcess == true) { cmd.Parameters.Add(new NpgsqlParameter(baseSync.mColumnDestinoID, this.mCount)); } //intercepta #region Boolean cancelar = false; OnInterceptPreInsert(cmd, baseSync, ref cancelar); //se cancelar roda o evento para atualizar o contador atual e a UI if (cancelar) { this.mCount++; OnStatusChange(this.mCount, this.mCountMax, baseSync); continue; } #endregion /* * if (cmd.Parameters[1].Value.ToString().Equals("00011000161")) * { * System.Windows.Forms.MessageBox.Show("OK"); * } */ String sqlInsert = MontarSQLInsert(baseSync, ordemCamposInsert, cmd.Parameters); String sqlUpdate = MontarSQLUpdate(baseSync, ordemCamposInsert, cmd.Parameters); //http://www.the-art-of-web.com/sql/upsert/ String sqlExecute = ""; if (baseSync.mDeleteTableDestinoAntesImportar == false && baseSync.mInsertOnlyWithoutUpdate == false) { sqlExecute = String.Format("WITH upsert AS ({0} RETURNING *) {1} WHERE NOT EXISTS (SELECT * FROM upsert);", sqlUpdate, sqlInsert); } else { sqlExecute = sqlInsert; } //String sqlTeste = String.Format("{0} IF NOT FOUND THEN {1}; END IF;", sqlUpdate, sqlInsert); cmd.CommandText = sqlExecute; String tmp = ""; //for (int i = 0; i < cmd.Parameters.Count; i++) // tmp += cmd.Parameters[i].ParameterName + " : " + cmd.Parameters[i].Value.ToString() + Environment.NewLine; //System.Windows.Forms.Clipboard.SetText(tmp); try { this.mCount++; if (this.mCount > this.mCountMax) { this.mCountMax = this.mCount; } OnStatusChange(this.mCount, this.mCountMax, baseSync); int count = cmd.ExecuteNonQuery(); } catch (Exception e) { bool continaur = true; //String aux = ""; //for(int i = 0; i < cmd.Parameters.Count; i++) //{ // aux += cmd.Parameters[i].SourceColumn + "/" + cmd.Parameters[i].Value?.ToString(); //} //System.Windows.Forms.Clipboard.SetText(aux); //System.Windows.Forms.MessageBox.Show(aux); this.OnError(e, sqlExecute, ref continaur); if (continaur == false) { break; } } } if (String.IsNullOrEmpty(baseSync.mCommandSQLFinish) == false) { NpgsqlCommand cmd = new NpgsqlCommand(baseSync.mCommandSQLFinish, pgsqlConnectionDestino); cmd.ExecuteNonQuery(); cmd.Dispose(); } //======pgsqlConnectionOrigem.Close(); //======pgsqlConnectionOrigem.Dispose(); dr.Close(); mConnectionOrigem.Close(); mConnectionOrigem.Dispose(); pgsqlConnectionDestino.Close(); pgsqlConnectionDestino.Dispose(); }