public String GetSqlRead(BaseSync baseSync)
{
List <String> ordemCamposInsert = null;
String sqlCount;
string sqlRead = MontarSQLRead(baseSync, out ordemCamposInsert, out sqlCount);
return(sqlRead);
}
public DbDataReader GetReader(BaseSync baseSync)
{
DbConnection mConnectionOrigem = ManagerConnection.CreateConnection(ConfigRuntime.TypeDatabasaOrigem, ConfigRuntime.ConectionBancoOrigem);
mConnectionOrigem.Open();
List <String> ordemCamposInsert = null;
String sqlCount;
string sqlRead = MontarSQLRead(baseSync, out ordemCamposInsert, out sqlCount);
DbCommand command = ManagerConnection.CreateCommand(ConfigRuntime.TypeDatabasaOrigem, sqlRead, mConnectionOrigem);
DbDataReader dr = command.ExecuteReader(System.Data.CommandBehavior.Default);
return(dr);
}
private String MontarSQLUpdate(BaseSync baseSync, List <String> ordemCamposInsert, NpgsqlParameterCollection parametros)
{
String sql = "UPDATE " + baseSync.mTableDestino;
sql += " SET ";
for (int i = 0; i < ordemCamposInsert.Count; i++)
{
sql += ordemCamposInsert[i];
sql += "=";
sql += ":" + parametros[i].ParameterName + ",";
}
sql = sql.Remove(sql.Length - 1, 1); //remove virguala
sql += " WHERE ";
//sql += baseSync.mColumnDestinoID + " = " + ":" + baseSync.mColumnOrigemID;
sql += baseSync.mColumnDestinoID + " = " + ":" + baseSync.mColumnDestinoID;
return(sql);
}
private String MontarSQLInsert(BaseSync baseSync, List <String> ordemCamposInsert, NpgsqlParameterCollection parametros)
{
String sql = "INSERT INTO " + baseSync.mTableDestino;
StringBuilder camposInsert = new StringBuilder("(");
for (int i = 0; i < ordemCamposInsert.Count; i++)
{
camposInsert.Append(ordemCamposInsert[i]);
camposInsert.Append(",");
}
camposInsert = camposInsert.Remove(camposInsert.Length - 1, 1);
camposInsert.Append(")");
//values
sql += camposInsert.ToString();
//sql += " VALUES ";
sql += " SELECT ";
//StringBuilder camposValues = new StringBuilder("(");
StringBuilder camposValues = new StringBuilder(" ");
for (int i = 0; i < parametros.Count; i++)
{
camposValues.Append(":" + parametros[i].ParameterName);
camposValues.Append(",");
}
camposValues = camposValues.Remove(camposValues.Length - 1, 1);
//camposValues.Append(")");
camposValues.Append(" ");
sql += camposValues.ToString();
return(sql);
}
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();
}
//a ordem que processa a lista já grava os campos do banco destino
private String MontarSQLRead(BaseSync baseSync, out List <String> camposOrdemInsert, out String sqlCount)
{
camposOrdemInsert = new List <string>();
String sql = "";
sqlCount = "";
sql = "SELECT ";
//sqlCount = baseSync.mSelectDistinct == false? "SELECT COUNT (*) " : "SELECT DISTINCT COUNT (*) ";
StringBuilder sqlDistinctOn = new StringBuilder();
if (baseSync.mDistinctOnColumns.Length > 0)
{
sqlDistinctOn.Append("distinct on ( ");
for (int i = 0; i < baseSync.mDistinctOnColumns.Length; i++)
{
sqlDistinctOn.Append(baseSync.mDistinctOnColumns[i]);
sqlDistinctOn.Append(",");
}
sqlDistinctOn.Remove(sqlDistinctOn.Length - 1, 1);
sqlDistinctOn.Append(") ");
}
StringBuilder sqlCampos = new StringBuilder();
//gera comando simples referencia
for (int i = 0; i < baseSync.mSimpleReference.Count; i++)
{
//nomedatabela.nomedocampo,
if (String.IsNullOrEmpty(baseSync.mSimpleReference[i].mBeforeCommandForRead) == false)
{
sqlCampos.Append(baseSync.mSimpleReference[i].mBeforeCommandForRead);
}
//se nao for uma coluna virtual entao adiciona Tabela.Coluna
if (baseSync.mSimpleReference[i].mIsVirtualColumn == false)
{
sqlCampos.Append(baseSync.mTableOrigem);
sqlCampos.Append(".");
}
//senao apenas Coluna
sqlCampos.Append(baseSync.mSimpleReference[i].campoOrigem);
if (String.IsNullOrEmpty(baseSync.mSimpleReference[i].mAfterCommandForRead) == false)
{
sqlCampos.Append(baseSync.mSimpleReference[i].mAfterCommandForRead);
}
sqlCampos.Append(",");
//camposOrdemInsert.Add(baseSync.mTableDestino + "." +baseSync.mSimpleReference[i].campoDestino);
camposOrdemInsert.Add(baseSync.mSimpleReference[i].campoDestino);
}
//gera comando join
for (int i = 0; i < baseSync.mJoinReference.Count; i++)
{
//nomedatabela.nomedocampo,
for (int j = 0; j < baseSync.mJoinReference[i].joinReferences.Count; j++)
{
sqlCampos.Append(baseSync.mJoinReference[i].tabela2);
sqlCampos.Append(".");
sqlCampos.Append(baseSync.mJoinReference[i].joinReferences[j].campoOrigem);
sqlCampos.Append(",");
camposOrdemInsert.Add(baseSync.mJoinReference[i].joinReferences[j].campoDestino);
}
//camposOrdemInsert.Add(baseSync.mSimpleReference[i].campoDestino);
}
//gera join
String sqlJoin = " ";
//if (baseSync.mJoinReference.Count > 0)
for (int i = 0; i < baseSync.mJoinReference.Count; i++)
{
//sqlJoin += " LEFT JOIN ";
sqlJoin += " " + baseSync.mJoinReference[i].tipoJoin + " ";
//for(int k = 0; k < baseSync.mJoinReference[i].joinReferences)
sqlJoin += baseSync.mJoinReference[i].tabela2;
sqlJoin += " ON ";
for (int j = 0; j < baseSync.mJoinReference[i].joinOnReference.Count; j++)
{
if (baseSync.mJoinReference[i].joinOnReference[j].campoOnTabela1_IsValue == true)
{
sqlJoin += " " + baseSync.mJoinReference[i].joinOnReference[j].campoOnTabela1;
}
else
{
sqlJoin += " " + baseSync.mTableOrigem + "." + baseSync.mJoinReference[i].joinOnReference[j].campoOnTabela1;
}
sqlJoin += "=";
sqlJoin += baseSync.mJoinReference[i].tabela2 + "." + baseSync.mJoinReference[i].joinOnReference[j].campoOnTabela2;
//sqlJoin += " AND";
sqlJoin += $" {baseSync.mJoinReference[i].joinOnReference[j].proxOperador}";
}
//sqlJoin = sqlJoin.Remove(sqlJoin.Length - 3, 3); //remove o ultimo "AND"
sqlJoin = sqlJoin.Remove(sqlJoin.LastIndexOf(' '), sqlJoin.Length - sqlJoin.LastIndexOf(' ')) + " "; //remove o ultimo "AND"
}
if (baseSync.mSelectDistinct == true)
{
sql += " DISTINCT ";
}
sql += sqlDistinctOn.ToString();
sql += sqlCampos.ToString(0, sqlCampos.Length - 1); //ja remove virgula
//só tira a virgula se tiver, pode ser que tenha só 1 campo (prodempresas)
String sqlToSqlCount = sql;
if (sql.IndexOf(',') != -1)
{
sqlToSqlCount = sql.Substring(0, sql.IndexOf(',')) + " ";
}
sql += " FROM " + baseSync.mTableOrigem + " ";
sql += sqlJoin;
sqlToSqlCount += " FROM " + baseSync.mTableOrigem + " ";
sqlToSqlCount += sqlJoin;
//sqlCount += " FROM " + baseSync.mTableOrigem + " ";
//sqlCount += sqlJoin;
sql += " " + baseSync.mWhere;
sqlToSqlCount += " " + baseSync.mWhere;
//sqlCount += " " + baseSync.mWhere;
//String sqlToSqlCount = sql;
//remove o order by do sqlcount
/*
* int indexOrderBy = sqlToSqlCount.IndexOf("order by");
* if (indexOrderBy > 0)
* sqlToSqlCount = sqlToSqlCount.Remove(indexOrderBy);
*/
sqlCount += "SELECT COUNT(*) FROM ";
sqlCount += "(";
sqlCount += sqlToSqlCount;
//sqlCount += sqlToSqlCount;
sqlCount += ") as tmp";
return(sql);
}
