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();
}