// <Snippet1> public void CreateParameterCollection(OdbcCommand command) { OdbcParameterCollection paramCollection = command.Parameters; paramCollection.Add("@CategoryName", OdbcType.Char); paramCollection.Add("@Description", OdbcType.Char); paramCollection.Add("@Picture", OdbcType.Binary); string paramNames = ""; for (int i = 0; i < paramCollection.Count; i++) { paramNames += paramCollection[i].ToString() + "\n"; } Console.WriteLine(paramNames); paramCollection.Clear(); }
public override void ProcessDeploy(String cacheId, PluginConnectorBaseDeployPackage package, Dictionary <String, Object> config, List <PluginConnectorBaseDeployPackageMapping> fieldMapping) { if (!CheckInputConfig(config, true, Log)) { return; } String table = config["table"].ToString(); String lock_column = (config.ContainsKey("lock_column") ? config["lock_column"].ToString().ToLower() : null); String locked_value = (config.ContainsKey("locked_value") ? config["locked_value"].ToString().ToLower() : null); String unlocked_value = (config.ContainsKey("unlocked_value") ? config["unlocked_value"].ToString().ToLower() : null); OdbcDB db = null; StringBuilder processLog = new StringBuilder(); PluginLogType logType = PluginLogType.Information; try { db = new OdbcDB(config["system_dsn"].ToString(), (config.ContainsKey("username") ? config["username"].ToString() : ""), (config.ContainsKey("password") ? config["password"].ToString() : "")); db.openDB(); List <String> prop = new List <String>(); String login = package.login; //Monta a tabela de campos para a filtragem Dictionary <String, String> filter = new Dictionary <String, String>(); //Adiciona os mapeamentos que são ID ou único para filtragem foreach (PluginConnectorBaseDeployPackageMapping m in fieldMapping) { if ((m.isId || m.isUnique) && !filter.ContainsKey(m.dataName.ToLower())) { filter.Add(m.dataName.ToLower(), null); } } foreach (PluginConnectorBaseDeployPackageMapping m in fieldMapping) { if (m.isLogin && filter.ContainsKey(m.dataName.ToLower())) { filter[m.dataName.ToLower()] = package.login; } else if (m.isName && filter.ContainsKey(m.dataName)) { filter[m.dataName.ToLower()] = package.fullName.fullName; } } //Verifica se a coluna do login é uma coluna da filtragem /* * foreach (String f in deploy_filter.Trim(", ".ToCharArray()).Split(",".ToCharArray())) * if (!filter.ContainsKey(f.ToLower().Trim())) * filter.Add(f.ToLower().Trim(), null); * * if (login_column != null && filter.ContainsKey(login_column)) * filter[login_column] = package.login; * * if (name_column != null && filter.ContainsKey(name_column)) * filter[name_column] = package.fullName.fullName;*/ foreach (PluginConnectorBasePackageData dt in package.importsPluginData) { if (filter.ContainsKey(dt.dataName.ToLower()) && filter[dt.dataName.ToLower()] == null) { filter[dt.dataName.ToLower()] = dt.dataValue; } } foreach (PluginConnectorBasePackageData dt in package.pluginData) { if (filter.ContainsKey(dt.dataName.ToLower()) && filter[dt.dataName.ToLower()] == null) { filter[dt.dataName.ToLower()] = dt.dataValue; } } foreach (PluginConnectorBasePackageData dt in package.properties) { if (filter.ContainsKey(dt.dataName.ToLower()) && filter[dt.dataName.ToLower()] == null) { filter[dt.dataName.ToLower()] = dt.dataValue; } } //Verifica se algum campo da filtragem é nulo foreach (String k in filter.Keys) { if (filter[k] == null) { logType = PluginLogType.Error; processLog.AppendLine("Deploy filter column data of '" + k + "' not found"); String debugInfo = JSON.Serialize2(new { package = package, fieldMapping = fieldMapping }); if (package.password != "") { debugInfo = debugInfo.Replace(package.password, "Replaced for user security"); } Log2(this, PluginLogType.Error, package.entityId, package.identityId, "Deploy filter column data of '" + k + "' not found", debugInfo); return; } } //Resgata a restutura da tabela de destino DataTable dtInsertSchema = db.GetSchema(table); //Monta o where OdbcParameterCollection par = OdbcDB.GetSqlParameterObject(); //Preenche a tabela de parâmetros com os campos do where List <String> f1 = new List <string>(); foreach (DataColumn dc in dtInsertSchema.Columns) { if (filter.ContainsKey(dc.ColumnName.ToLower())) { if (dc.DataType.Equals(typeof(String))) { par.Add("@s_" + dc.ColumnName, GetDBType(dc.DataType)).Value = Corte(filter[dc.ColumnName.ToLower()], dc.MaxLength); } else { par.Add("@s_" + dc.ColumnName, GetDBType(dc.DataType)).Value = filter[dc.ColumnName.ToLower()]; } f1.Add(dc.ColumnName + " = @s_" + dc.ColumnName); } } //Monta todos os campos que serão inseridos/atualizados Dictionary <String, String> data = new Dictionary <String, String>(); foreach (DataColumn dc in dtInsertSchema.Columns) { if (dc.AutoIncrement) { processLog.AppendLine("Field " + dc.ColumnName + " ignored because it was indicated with an AutoIncrement fiend"); } else if (!data.ContainsKey(dc.ColumnName.ToLower())) { //Nao adiciona coluna que é autoincremento (ID) data.Add(dc.ColumnName.ToLower(), null); } } if (data.ContainsKey("locked")) { data["locked"] = (package.locked || package.temp_locked ? "1" : "0"); } DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "lock_column = " + (String.IsNullOrEmpty(lock_column) ? "empty" : lock_column), ""); DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "locked_value = " + (String.IsNullOrEmpty(locked_value) ? "empty" : locked_value), ""); DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "unlocked_value = " + (String.IsNullOrEmpty(unlocked_value) ? "empty" : unlocked_value), ""); if ((lock_column != null) && (data.ContainsKey(lock_column))) { if ((package.locked || package.temp_locked) && (!String.IsNullOrEmpty(locked_value))) { data[lock_column] = locked_value; } else if ((!package.locked && !package.temp_locked) && (!String.IsNullOrEmpty(unlocked_value))) { data[lock_column] = unlocked_value; } else { data[lock_column] = (package.locked || package.temp_locked ? "1" : "0"); } DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "data[lock_column] = " + data[lock_column], ""); } String password_column = ""; foreach (PluginConnectorBaseDeployPackageMapping m in fieldMapping) { if (m.isPassword && data.ContainsKey(m.dataName)) { data[m.dataName] = package.password; password_column = m.dataName; } else if (m.isLogin && data.ContainsKey(m.dataName)) { data[m.dataName] = package.login; } else if (m.isName && data.ContainsKey(m.dataName)) { data[m.dataName] = package.fullName.fullName; } } /*if (login_column != null && data.ContainsKey(login_column)) * data[login_column] = package.login; * * if (password_column != null && data.ContainsKey(password_column)) * data[password_column] = package.password; * * if (name_column != null && data.ContainsKey(name_column)) * data[name_column] = package.fullName.fullName;*/ #if DEBUG processLog.AppendLine("1. Entity Data"); #endif foreach (PluginConnectorBasePackageData dt in package.entiyData) { if (data.ContainsKey(dt.dataName.ToLower()) && data[dt.dataName.ToLower()] == null) { data[dt.dataName.ToLower()] = dt.dataValue; //DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "1. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue, ""); #if DEBUG processLog.AppendLine("1. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue); #endif } } #if DEBUG processLog.AppendLine("2. Import Plugin Data"); #endif foreach (PluginConnectorBasePackageData dt in package.importsPluginData) { if (data.ContainsKey(dt.dataName.ToLower()) && data[dt.dataName.ToLower()] == null) { data[dt.dataName.ToLower()] = dt.dataValue; //DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "1. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue, ""); #if DEBUG processLog.AppendLine("2. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue); #endif } } #if DEBUG processLog.AppendLine("3. Plugin Data"); #endif foreach (PluginConnectorBasePackageData dt in package.pluginData) { if (data.ContainsKey(dt.dataName.ToLower()) && data[dt.dataName.ToLower()] == null) { data[dt.dataName.ToLower()] = dt.dataValue; //DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "2. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue, ""); #if DEBUG processLog.AppendLine("3. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue); #endif } } #if DEBUG processLog.AppendLine("4. Properties"); #endif foreach (PluginConnectorBasePackageData dt in package.properties) { if (data.ContainsKey(dt.dataName.ToLower()) && data[dt.dataName.ToLower()] == null) { data[dt.dataName.ToLower()] = dt.dataValue; //DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "3. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue, ""); #if DEBUG processLog.AppendLine("4. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue); #endif } } #if DEBUG processLog.AppendLine("5. Final data"); #endif foreach (String k in data.Keys) { //DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "4. data[" + k + "] = " + data[k], ""); #if DEBUG processLog.AppendLine("5. data[" + k + "] = " + data[k]); #endif } //Verifica se o registro existe DataTable dtReg = db.ExecuteDataTable("select * from " + table + " where " + String.Join(" and ", f1), CommandType.Text, par); if (dtReg == null) { logType = PluginLogType.Error; processLog.AppendLine("Error on select data: " + db.LastError); Log2(this, PluginLogType.Error, package.entityId, package.identityId, "Error on select data: " + db.LastError, ""); return; } LogEvent dbExecLog = new LogEvent(delegate(Object sender, PluginLogType type, String text) { processLog.AppendLine(text); }); //Preenche a tabela de parâmetros com os campos que serão inseridos/atualizados if (dtReg.Rows.Count > 0) { //Existe, atualiza List <String> c1 = new List <string>(); foreach (DataColumn dc in dtInsertSchema.Columns) { if (data.ContainsKey(dc.ColumnName.ToLower())) { if (!String.IsNullOrWhiteSpace(data[dc.ColumnName.ToLower()])) { if (dc.DataType.Equals(typeof(String))) { String txt = Corte((data[dc.ColumnName.ToLower()] != null ? data[dc.ColumnName.ToLower()] : ""), dc.MaxLength); par.Add("@" + dc.ColumnName, GetDBType(dc.DataType), txt.Length).Value = txt; } else { par.Add("@" + dc.ColumnName, GetDBType(dc.DataType)).Value = (data[dc.ColumnName.ToLower()] != null ? data[dc.ColumnName.ToLower()] : ""); } c1.Add(dc.ColumnName + " = @" + dc.ColumnName); } } } foreach (OdbcParameter p in par) { #if DEBUG processLog.AppendLine("5. par[" + p.ParameterName + "] = " + p.Value); #endif } String update = "update " + table + " set " + String.Join(", ", c1) + " where " + String.Join(" and ", f1); db.OnLog += dbExecLog; db.ExecuteNonQuery(update, CommandType.Text, par); db.OnLog -= dbExecLog; NotityChangeUser(this, package.entityId); if (package.password != "") { processLog.AppendLine("User updated with password"); } else { processLog.AppendLine("User updated without password"); } } else { //Não existe, cria if ((data.ContainsKey(password_column)) && (package.password == "")) { package.password = IAM.Password.RandomPassword.Generate(16); data[password_column] = package.password; processLog.AppendLine("User not found in AD and IAM Password not found in properties list, creating a random password (" + package.password + ")"); } //Limpa os parâmetros par.Clear(); List <String> c1 = new List <string>(); List <String> c2 = new List <string>(); foreach (DataColumn dc in dtInsertSchema.Columns) { if (data.ContainsKey(dc.ColumnName.ToLower())) { try { if (!String.IsNullOrWhiteSpace(data[dc.ColumnName.ToLower()])) { if (dc.DataType.Equals(typeof(String))) { String txt = Corte((data[dc.ColumnName.ToLower()] != null ? data[dc.ColumnName.ToLower()] : ""), dc.MaxLength); par.Add("@" + dc.ColumnName, GetDBType(dc.DataType), txt.Length).Value = txt; } else { par.Add("@" + dc.ColumnName, GetDBType(dc.DataType)).Value = data[dc.ColumnName.ToLower()]; } c1.Add(dc.ColumnName); c2.Add("@" + dc.ColumnName); } } catch (Exception ex) { processLog.AppendLine("Column: " + dc.ColumnName + ", DataType: " + dc.DataType.ToString()); try { processLog.AppendLine("Value: " + data[dc.ColumnName.ToLower()]); } catch (Exception ex1) { processLog.AppendLine("Value error: " + ex1.Message); } throw new Exception("Erro filling data. Column=" + dc.ColumnName + ", " + dc.DataType.ToString(), ex); } } } foreach (OdbcParameter p in par) { //DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "6. par[" + p.ParameterName + "] = " + p.Value, ""); #if DEBUG processLog.AppendLine("6. par[" + p.ParameterName + "] = " + p.Value); #endif } String insert = "insert into " + table + " (" + String.Join(",", c1) + ") values (" + String.Join(",", c2) + ")"; db.OnLog += dbExecLog; db.ExecuteNonQuery(insert, CommandType.Text, par); db.OnLog -= dbExecLog; NotityChangeUser(this, package.entityId); processLog.AppendLine("User added"); } //Executa as ações do RBAC if ((package.pluginAction != null) && (package.pluginAction.Count > 0)) { foreach (PluginConnectorBaseDeployPackageAction act in package.pluginAction) { try { switch (act.actionKey.ToLower()) { case "procedure": String sql1 = act.actionValue.Replace("{login}", package.login).Replace("{full_name}", package.fullName.fullName); db.ExecuteNonQuery(sql1, CommandType.StoredProcedure, null); break; case "sql": String sql2 = act.actionValue.Replace("{login}", package.login).Replace("{full_name}", package.fullName.fullName); db.ExecuteNonQuery(sql2, CommandType.Text, null); break; default: processLog.AppendLine("Action not recognized: " + act.actionKey); Log2(this, PluginLogType.Warning, package.entityId, package.identityId, "Action not recognized: " + act.actionKey, ""); break; } } catch (Exception ex) { processLog.AppendLine("Error on execute action (" + act.actionKey + "): " + ex.Message); Log2(this, PluginLogType.Error, package.entityId, package.identityId, "Error on execute action (" + act.actionKey + "): " + ex.Message, ""); } } } } catch (Exception ex) { logType = PluginLogType.Error; processLog.AppendLine("Error on process deploy: " + ex.Message); String debugInfo = JSON.Serialize2(new { package = package, fieldMapping = fieldMapping }); if (package.password != "") { debugInfo = debugInfo.Replace(package.password, "Replaced for user security"); } Log2(this, PluginLogType.Error, package.entityId, package.identityId, "Error on process deploy: " + ex.Message, debugInfo); } finally { if (db != null) { db.Dispose(); } Log2(this, logType, package.entityId, package.identityId, "Deploy executed", processLog.ToString()); processLog.Clear(); processLog = null; } }
public void ExecuteNonQuery(String command, CommandType commandType, OdbcParameterCollection Parameters, OdbcTransaction trans) { if ((i_cn == null) || (i_cn.State == ConnectionState.Closed)) { closeDB(); openDB(); } Int16 step = 0; while ((step < 10) && (conn.State == ConnectionState.Connecting)) { System.Threading.Thread.Sleep(100); } OdbcCommand cmd = new OdbcCommand(command, i_cn); cmd.CommandType = commandType; cmd.CommandTimeout = i_timeout; String debug = ""; try { if (Parameters != null) { cmd.Parameters.Clear(); foreach (OdbcParameter par in Parameters) { cmd.Parameters.Add(par.ParameterName, par.OdbcType, par.Size).Value = par.Value; cmd.CommandText = cmd.CommandText.Replace(par.ParameterName, "?"); } } if (trans != null) { cmd.Transaction = trans; } DebugLog(PluginLogType.Debug, "ExecuteNonQuery.SQL = " + cmd.CommandText); DebugLog(PluginLogType.Debug, "ExecuteNonQuery.Parameters " + cmd.Parameters.Count); foreach (OdbcParameter p in cmd.Parameters) { DebugLog(PluginLogType.Debug, "ExecuteNonQuery.Parameters[" + p.ParameterName + "] = " + p.Value); } cmd.ExecuteNonQuery(); } catch (Exception ex) { i_lastError = ex.Message; throw ex; } finally { if (Parameters != null) { Parameters.Clear(); } Parameters = null; if (cmd != null) { cmd.Dispose(); } cmd = null; } }
public void Insert2(String insertSQL, OdbcParameterCollection Parameters) { if ((i_cn == null) || (i_cn.State == ConnectionState.Closed)) { closeDB(); openDB(); } Int16 step = 0; while ((step < 10) && (conn.State == ConnectionState.Connecting)) { System.Threading.Thread.Sleep(100); } DebugLog(PluginLogType.Debug, "insertSQL.SQL = " + insertSQL); OdbcCommand cmd = new OdbcCommand(insertSQL, i_cn); OdbcDataReader dr = null; try { if (Parameters != null) { cmd.Parameters.Clear(); foreach (OdbcParameter par in Parameters) { cmd.Parameters.Add(par.ParameterName, par.OdbcType, par.Size).Value = par.Value; } } DebugLog(PluginLogType.Debug, "Insert2.Parameters " + cmd.Parameters.Count); foreach (OdbcParameter p in cmd.Parameters) { DebugLog(PluginLogType.Debug, "Insert2.Parameters[" + p.ParameterName + "] = " + p.Value); } cmd.ExecuteNonQuery(); } catch (Exception ex) { i_lastError = ex.Message; throw ex; } finally { if (Parameters != null) { Parameters.Clear(); } Parameters = null; if (cmd != null) { cmd.Dispose(); } if (dr != null) { dr.Close(); } cmd = null; } }
public void Insert(String insertSQL, OdbcParameterCollection Parameters) { OdbcConnection conn = new OdbcConnection(i_ConnectionString); conn.Open(); Int16 step = 0; while ((step < 10) && (conn.State == ConnectionState.Connecting)) { System.Threading.Thread.Sleep(100); } OdbcCommand cmd = null; OdbcDataReader dr = null; try { cmd = new OdbcCommand(insertSQL, conn); if (Parameters != null) { cmd.Parameters.Clear(); foreach (OdbcParameter par in Parameters) { cmd.Parameters.Add(par.ParameterName, par.OdbcType, par.Size).Value = par.Value; } } cmd.ExecuteNonQuery(); } catch (Exception ex) { i_lastError = ex.Message; throw ex; } finally { if (Parameters != null) { Parameters.Clear(); } Parameters = null; if (cmd != null) { cmd.Dispose(); } if (dr != null) { dr.Close(); } if (conn != null) { conn.Close(); } if (conn != null) { conn.Dispose(); } cmd = null; } }