public DataSet FillDataSet(string strQuery, string strAlias, DataSet dsDataSet,
OdbcParameter[] paramArray)
{
OdbcParameterCollection col = null;
return(FillDataSet(strQuery, strAlias, dsDataSet, paramArray, CommandType.Text, out col));
}
private void btnAdd_Click(object sender, EventArgs e)
{
var result = opnFile.ShowDialog();
if (result == DialogResult.OK)
{
Stream fileData = opnFile.OpenFile();
MemoryStream ms = new MemoryStream();
fileData.CopyTo(ms);
byte[] data = ms.ToArray();
OdbcCommand query = new OdbcCommand("INSERT INTO attachment (client, name, data) VALUES (?, ?, ?)", Utilities.kis_clients);
OdbcParameterCollection parameters = query.Parameters;
query.Parameters.Add("client", OdbcType.Int);
query.Parameters.Add("name", OdbcType.VarChar);
query.Parameters.Add("data", OdbcType.Binary);
query.Parameters["client"].Value = ClientID;
query.Parameters["name"].Value = opnFile.FileName.Substring(opnFile.FileName.LastIndexOf("\\") + 1);
query.Parameters["data"].Value = data;
if (query.ExecuteNonQuery() == 0)
{
new Message("Failed to save attachment.");
}
else
{
InitializeAttachments();
}
}
}
private static void MapParameter(this OdbcParameterCollection param, Dictionary <string, Object> dic)
{
foreach (KeyValuePair <string, Object> entry in dic)
{
param.AddWithValue(entry.Key, dic[entry.Key] ?? DBNull.Value);
}
}
public object ExecuteScalar(string strQuery, OdbcParameter[] paramArray,
CommandType cmdType)
{
OdbcParameterCollection col = null;
return(ExecuteScalar(strQuery, paramArray, cmdType, out col));
}
public object ExecuteScalar(string strQuery, OdbcParameter[] paramArray,
CommandType cmdType, out OdbcParameterCollection col)
{
object oRc = 0;
col = null;
OdbcCommand cmd = new OdbcCommand(strQuery, _connection);
cmd.CommandType = cmdType;
if (paramArray != null)
{
foreach (OdbcParameter param in paramArray)
{
cmd.Parameters.Add(param);
}
}
_connection.Open();
oRc = cmd.ExecuteScalar();
//_connection.Dispose();
_connection.Close();
col = cmd.Parameters;
return(oRc);
}
public OdbcDataReader ExecuteReader(string strQuery, OdbcParameter[] paramArray,
CommandType cmdType)
{
OdbcParameterCollection col = null;
return(ExecuteReader(strQuery, paramArray, cmdType, out col));
}
//增加参数#region 增加参数
#region
public void AddParameterCollection(OdbcCommand cmd, OdbcParameterCollection dbParameterCollection)
{
foreach (OdbcParameter dbParameter in dbParameterCollection)
{
cmd.Parameters.Add(dbParameter);
}
}
// <Snippet1>
public void CreateParameterCollection(OdbcConnection connection)
{
OdbcCommand command = new OdbcCommand(
"SELECT * FROM Customers WHERE CustomerID = ?", connection);
OdbcParameterCollection paramCollection = command.Parameters;
OdbcParameter parameter = paramCollection.Add(
"CustomerID", OdbcType.VarChar, 5, "CustomerID");
}
public static OdbcParameter AddOdbcParameter(
this OdbcParameterCollection Collection, string ParmName, int Length, string Value)
{
var parm = new OdbcParameter("@" + ParmName, OdbcType.Char, Length);
parm.Value = Value;
Collection.Add(parm);
return(parm);
}
// <Snippet1>
public void CreateParamCollection()
{
OdbcCommand command = new OdbcCommand(
"SELECT * FROM Customers WHERE CustomerID = ?", connection);
OdbcParameterCollection paramCollection = command.Parameters;
object paramObject = new OdbcParameter(
"CustomerID", OdbcType.VarChar);
int paramIndex = paramCollection.Add(paramObject);
}
public static OdbcParameter AddOdbcOutputParameter(
this OdbcParameterCollection Collection, string ParmName, int Length)
{
var parm = new OdbcParameter("@" + ParmName, OdbcType.Char, Length);
parm.Direction = ParameterDirection.Output;
Collection.Add(parm);
return(parm);
}
public static OdbcParameter AddOdbcParameter(
this OdbcParameterCollection Collection, string ParmName,
int Length, int Precision, decimal Value)
{
var parm = new OdbcParameter("@" + ParmName, OdbcType.Decimal, Length);
parm.Precision = (byte)Precision;
parm.Value = Value;
Collection.Add(parm);
return(parm);
}
public static OdbcParameter AddOdbcOutputParameter(
this OdbcParameterCollection Collection, string ParmName,
int Length, int Precision)
{
var parm = new OdbcParameter("@" + ParmName, OdbcType.Decimal, Length);
parm.Precision = (byte)Precision;
parm.Direction = ParameterDirection.Output;
Collection.Add(parm);
return(parm);
}
// <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 int ExecuteNonQuery(string strQuery, OdbcParameter[] paramArray,
CommandType cmdType, out OdbcParameterCollection col)
{
int nRc = 0;
col = null;
OdbcCommand cmd = new OdbcCommand(strQuery, _connection);
cmd.CommandType = cmdType;
if (paramArray != null)
{
foreach (OdbcParameter param in paramArray)
{
cmd.Parameters.Add(param);
}
}
try
{
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
}
_connection.Open();
nRc = cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
throw new Exception("ExecuteNonQuery가 실패했습니다.", e);
}
finally
{
col = cmd.Parameters;
// _connection.Dispose();
_connection.Close();
}
return(nRc);
}
protected void insert_Click(object sender, EventArgs e)
{
string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
string contentType = FileUpload1.PostedFile.ContentType;
using (Stream fs = FileUpload1.PostedFile.InputStream)
{
using (BinaryReader br = new BinaryReader(fs))
{
byte[] bytes = br.ReadBytes((Int32)fs.Length);
string updateQuery = "Update inventory set image_filename='" + filename + "', content_type='" + contentType + "', image=? where ID='" + id_txt.Text + "'";
// OdbcConnection dbConnection = new OdbcConnection("DSN=FLC");
using (
OdbcConnection con = new OdbcConnection("DSN=FLC"))
{
// create an OdbcCommand and get its parameters
OdbcCommand command = new OdbcCommand(updateQuery);
OdbcParameterCollection parameters = command.Parameters;
// add "value" column to parameters, store the long string in it
parameters.Add("value", OdbcType.Image);
parameters["value"].Value = bytes;
con.Open();
// set the command's connection and execute the query
command.Connection = con;
command.ExecuteNonQuery();
}
// Connection.dbCommand("Update inventory set image_filename='" + filename + "', content_type='" + contentType + "', image='" + bytes + "' where ID='" + id_txt.Text + "'");
Label1.Visible = true;
Label1.Text = "Image Saved.";
}
Response.Redirect(Request.Url.AbsoluteUri);
}
}
public DataSet FillDataSet(string strQuery, string strAlias, DataSet dsDataSet,
OdbcParameter[] paramArray, CommandType cmdType, out OdbcParameterCollection paramCol)
{
OdbcDataAdapter sqlAdapter = new OdbcDataAdapter(strQuery, _connection);
sqlAdapter.SelectCommand.CommandType = cmdType;
sqlAdapter.SelectCommand.CommandTimeout = 120;
if (dsDataSet == null)
{
dsDataSet = new DataSet();
}
if (paramArray != null)
{
foreach (OdbcParameter param in paramArray)
{
sqlAdapter.SelectCommand.Parameters.Add(param);
}
}
sqlAdapter.Fill(dsDataSet, strAlias);
paramCol = sqlAdapter.SelectCommand.Parameters;
return(dsDataSet);
}
public OdbcDataReader ExecuteReader(string strQuery, OdbcParameter[] paramArray,
CommandType cmdType, out OdbcParameterCollection paramCol)
{
OdbcDataReader reader = null;
paramCol = null;
OdbcCommand cmd = new OdbcCommand(strQuery, _connection);
cmd.CommandType = cmdType;
if (paramArray != null)
{
foreach (OdbcParameter param in paramArray)
{
cmd.Parameters.Add(param);
}
}
_connection.Open();
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
paramCol = cmd.Parameters;
return(reader);
}
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;
}
}
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 override void ProcessDelete(String cacheId, PluginConnectorBaseDeployPackage package, Dictionary <String, Object> config, List <PluginConnectorBaseDeployPackageMapping> fieldMapping)
{
if (!CheckInputConfig(config, true, Log))
{
return;
}
String connectionstring = config["connectionstring"].ToString();
String table = config["table"].ToString();
String deploy_filter = config["deploy_filter"].ToString();
String login_column = (config.ContainsKey("login_column") ? config["login_column"].ToString().ToLower() : null);
String name_column = (config.ContainsKey("name_column") ? config["name_column"].ToString().ToLower() : null);
String password_column = (config.ContainsKey("password_column") ? config["password_column"].ToString().ToLower() : null);
OdbcDB db = null;
try
{
db = new OdbcDB(connectionstring);
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>();
//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.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)
{
Log2(this, PluginLogType.Error, package.entityId, package.identityId, "Deploy filter column data of '" + k + "' not found", "");
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);
}
}
//Verifica se o registro existe
DataTable dtReg = db.ExecuteDataTable("select * from " + table + " where " + String.Join(" and ", f1), CommandType.Text, par);
if (dtReg == null)
{
Log2(this, PluginLogType.Error, package.entityId, package.identityId, "Error on select data: " + db.LastError, "");
return;
}
//Preenche a tabela de parâmetros com os campos que serão inseridos/atualizados
if (dtReg.Rows.Count == 0)
{
Log2(this, PluginLogType.Warning, package.entityId, package.identityId, "User not found", "");
return;
}
String update = "delete from " + table + " where " + String.Join(" and ", f1);
db.ExecuteNonQuery(update, CommandType.Text, par);
NotityDeletedUser(this, package.entityId, package.identityId);
Log2(this, PluginLogType.Information, package.entityId, package.identityId, "User deleted", "");
}
catch (Exception ex)
{
Log2(this, PluginLogType.Error, package.entityId, package.identityId, "Error on process deploy: " + ex.Message, "");
}
finally
{
if (db != null)
{
db.Dispose();
}
}
}
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 AddSqlParameters(OdbcParameterCollection parameters)
{
parameters.Add($"@{DatabaseKeyName}", DatabaseKeyIsUnicode ? OdbcType.NVarChar : OdbcType.VarChar).Value = MatchValue;
}
public DataTable ExecuteDataTable(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;
OdbcDataAdapter da = null;
DataSet ds = 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;
cmd.CommandText = cmd.CommandText.Replace(par.ParameterName, "?");
}
}
if (trans != null)
{
cmd.Transaction = trans;
}
DebugLog(PluginLogType.Debug, "ExecuteDataTable.SQL = " + cmd.CommandText);
DebugLog(PluginLogType.Debug, "ExecuteDataTable.Parameters " + cmd.Parameters.Count);
foreach (OdbcParameter p in cmd.Parameters)
{
DebugLog(PluginLogType.Debug, "ExecuteDataTable.Parameters[" + p.ParameterName + "] = " + p.Value);
}
da = new OdbcDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "data");
da.Dispose();
DataTable tmp = ds.Tables["data"];
return(tmp);
}
catch (Exception ex)
{
i_lastError = ex.Message;
throw ex;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
cmd = null;
}
}
public void ExecuteNonQuery(String command, CommandType commandType, OdbcParameterCollection Parameters)
{
ExecuteNonQuery(command, commandType, Parameters, null);
}
public DataTable ExecuteDataTable(String command, CommandType commandType, OdbcParameterCollection Parameters)
{
return(ExecuteDataTable(command, commandType, Parameters, null));
}
public Object ExecuteScalar(String command, CommandType commandType, OdbcParameterCollection Parameters)
{
return(ExecuteScalar(command, commandType, Parameters, 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 DataSet FillDataSet(string strQuery, string strAlias)
{
OdbcParameterCollection col = null;
return(FillDataSet(strQuery, strAlias, null, null, CommandType.Text, out col));
}
public object ExecuteScalar(string strQuery)
{
OdbcParameterCollection col = null;
return(ExecuteScalar(strQuery, null, CommandType.Text, out col));
}
