public override DataTable GetColumns()
{
var dataTable = SchemaTables.GetColumnsDataTable();
// There is no way to get the scale of a decimal with DAO. Looks like someone at Microsoft just forgot to
// implement it.
// Therefore, either ADO has to be used, or DAO together with the GetSchema()
// method (that contains precision and scale, but no default value when using ODBC, because again, looks
// like someone at Microsoft just forgot to implement it).
Dictionary <(string TableName, string ColumnName), int?> numericScales = null;
if (!_naturalOnly)
{
var schemaTable = _connection.InnerConnection.GetSchema("Columns");
numericScales = schemaTable.Rows
.Cast <DataRow>()
.ToDictionary(
t => (TableName: (string)t["TABLE_NAME"], ColumnName: (string)t["COLUMN_NAME"]),
t => t.Table.Columns.Contains("DECIMAL_DIGITS")
? t["DECIMAL_DIGITS"] != DBNull.Value
? (int?)(short?)t["DECIMAL_DIGITS"]
: null
: t["NUMERIC_SCALE"] != DBNull.Value
? (int?)(short?)t["NUMERIC_SCALE"]
: null);
}
var objectDefsCollection = new[]
public void UpdateFromConfig(DataContextElement dataContextConfig)
{
List <DataTypeMappingElement> newTypeMappings = new List <DataTypeMappingElement>();
List <DataTypeMappingElement> parentTypeMappings = dataContextConfig.TypeMappings.Where(typeMapping => !Tables.ContainsKey(typeMapping.ObjectType)).ToList();
newTypeMappings.AddRange(parentTypeMappings);
parentTypeMappings.ForEach(parentTypeMapping => AddChildTypeMappings(newTypeMappings, parentTypeMapping));
if (newTypeMappings != null)
{
using (SqlConnection sqlConn = new SqlConnection(DBConnectionString))
{
sqlConn.Open();
newTypeMappings.ForEach(typeMapping =>
SchemaTables.Add(typeMapping, sqlConn.GetSchema("Columns", new string[] { sqlConn.Database, null, typeMapping.TableName, null })));
}
// add table data for each config and schema pair
if (SchemaTables != null)
{
newTypeMappings.ForEach(typeMapping =>
{
if (SchemaTables.ContainsKey(typeMapping))
{
Tables.Add(typeMapping.ObjectType, new TableData(this, typeMapping));
}
});
}
}
}
private int GetDatabaseObjectId(TableAttribute ta)
=> Cache.DataCacher.FindOrCreate(
Cache.CreateKey(ConnectionString, ta.Schema.ToLower(), ta.Name.ToLower()),
() => SchemaTables.ConvertAll(z => Tuple.Create(
Cache.CreateKey(ConnectionString, z.SchemaName.ToLower(), z.TableName.ToLower()),
new CacheEntry <int>(z.ObjectId)
))
).Value;
private void CmBTables2_SelectedValueChanged(object sender, EventArgs e)
{
CmbColums2.DataSource = SchemaTables.GetAllColumFromTable(Constants.CONECTION_STRING, Constants.DB_NAME, CmBTables2?.SelectedValue?.ToString());
if (IsChargeForm)
{
NotifyConditon.ChangeValueItem(Identifier, GenerateQuery(), CheckAND.Checked ? "AND" : "OR");
}
}
public NotifierClient(String conectionString, List <DynamicQueryParam> paramsDynamicTableDataEvent, List <RecipientType> recipientsType, String notificationName)
{
ParamsDynamicTableDataEvent = paramsDynamicTableDataEvent;
RecipientsType = recipientsType;
ConectionString = conectionString;
NotficationName = notificationName;
Notification = new ResolverNotify();
SchemaTables = new SchemaTables();
}
public override DataTable GetColumns()
{
var dataTable = SchemaTables.GetColumnsDataTable();
// There is no way to get the ordinal position of a column with ADOX. Looks like someone at Microsoft just forgot to
// implement it.
// Therefore, either DAO has to be used, or ADOX together with the OpenSchema()
// method.
Dictionary <(string TableName, string ColumnName), int> ordinalPositions = null;
if (!_naturalOnly)
{
using (var recordset = _connection.OpenSchema(SchemaEnum.adSchemaColumns))
{
ordinalPositions = new Dictionary <(string TableName, string ColumnName), int>();
using var fields = recordset.Fields;
using var tableNameField = fields["TABLE_NAME"];
using var columnNameField = fields["COLUMN_NAME"];
using var ordinalPositionField = fields["ORDINAL_POSITION"];
recordset.MoveFirst();
while (!recordset.EOF)
{
var tableName = (string)tableNameField.Value;
var columnName = (string)columnNameField.Value;
var ordinalPosition = (int)ordinalPositionField.Value - 1;
ordinalPositions.Add((tableName, columnName), ordinalPosition);
recordset.MoveNext();
}
recordset.Close();
}
}
using var tables = _catalog.Tables;
var tableCount = tables.Count;
for (var i = 0; i < tableCount; i++)
{
using var table = tables[i];
var tableName = (string)table.Name;
using var columns = table.Columns;
var columnCount = columns.Count;
for (var j = 0; j < columnCount; j++)
{
using var column = columns[j];
using var properties = column.Properties;
var columnName = (string)column.Name;
var attributes = (ColumnAttributesEnum)column.Attributes;
var ordinalPosition = ordinalPositions?[(tableName, columnName)] ?? j;
private void BtnSearch_Click(object sender, EventArgs e)
{
PnlNortificationType.Controls.Clear();
String query = String.Concat("SELECT * FROM ", CmbDestination.SelectedValue.ToString(), " WHERE ");
List <String> listRowsTables = SchemaTables.GetAllColumFromTable(Constants.CONECTION_STRING, Constants.DB_NAME, CmbDestination.SelectedValue.ToString());
foreach (String item in listRowsTables)
{
query = String.Concat(query, "(", item, " LIKE '%", TxtSearch.Text, "%') ");
if (listRowsTables.IndexOf(item) != listRowsTables.Count - 1)
{
query = String.Concat(query, " OR ");
}
}
GrdViewResult.DataSource = SchemaTables.ExeucteQuery(Constants.CONECTION_STRING, query, this);
}
public static void Load(IDbHost db)
{
Audit.Message("\n ** TABLES QUERY **");
var data = new SchemaTables("wikialgorithm").Exec(db);
foreach (var r in data.Tables)
{
Audit.Message($" Engine={r.Engine}, Table={r.TableName}, Type={r.TableType}, RowFormat={r.RowFormat}");
}
Audit.Message("\n ** COLUMNS QUERY **");
var columnsData = new SchemaColumns("wikialgorithm").Exec(db);
foreach (var c in columnsData.ColumnsList)
{
Audit.Message($" Table={c.TableName}, Column={c.ColumnName}, DataType={c.DataType}, ColumnType={c.ColumnType}, Nullable={c.IsNullable}, Key={c.ColumnKey}");
}
}
public override DataTable GetTables()
{
var dataTable = SchemaTables.GetTablesDataTable();
using var tableDefs = _database.TableDefs;
var tableCount = (int)tableDefs.Count;
for (var i = 0; i < tableCount; i++)
{
using var tableDef = tableDefs[i];
var tableName = (string)tableDef.Name;
var tableAttributes = (int)tableDef.Attributes;
string tableType;
if (IsInternalTableByName(tableName))
{
tableType = "INTERNAL TABLE";
}
else if (IsSystemTableByName(tableName) ||
(tableAttributes & (int)TableDefAttributeEnum.dbSystemObject) == (int)TableDefAttributeEnum.dbSystemObject ||
(tableAttributes & (1 << 31)) == 1 << 31)
{
tableType = "SYSTEM TABLE";
}
else
{
tableType = "BASE TABLE";
}
var validationRule = (string)tableDef.ValidationRule;
validationRule = string.IsNullOrEmpty(validationRule)
? null
: validationRule;
var validationText = (string)tableDef.ValidationText;
validationText = string.IsNullOrEmpty(validationText)
? null
: validationText;
dataTable.Rows.Add(
tableName,
tableType,
validationRule,
validationText);
}
using var queryDefs = _database.QueryDefs;
var queryCount = (int)queryDefs.Count;
for (var i = 0; i < queryCount; i++)
{
using var queryDef = queryDefs[i];
using var parameters = queryDef.Parameters;
var parameterCount = parameters.Count;
// If the QueryDef contains parameters, we will treat it as a stored procedure.
if (parameterCount <= 0)
{
var tableName = (string)queryDef.Name;
var type = (QueryDefTypeEnum)queryDef.Type;
if (type == QueryDefTypeEnum.dbQSelect ||
type == QueryDefTypeEnum.dbQSetOperation ||
type == QueryDefTypeEnum.dbQCrosstab ||
type == QueryDefTypeEnum.dbQSQLPassThrough)
{
dataTable.Rows.Add(
tableName,
"VIEW",
null,
null);
}
}
}
dataTable.AcceptChanges();
return(dataTable);
}
internal void SendNotifcations()
{
RecipientsType.ForEach(item =>
{
item.FilttersRecipient.ForEach(r =>
{
QueryReturn = "";
ParamsDynamicTableDataEvent.ForEach(d => {
QueryReturn = r.Filtter.Replace(d.IdParam, d.ValueParam);
});
using (Connection = new SqlConnection(ConectionString))
{
Connection.Open();
#pragma warning disable S3649 // User-provided values should be sanitized before use in SQL statements
using (Command = new SqlCommand(QueryReturn, Connection))
#pragma warning restore S3649 // User-provided values should be sanitized before use in SQL statements
{
MessageBase = item.Message.Message;
TitleMessage = item.Message.TitleMessage;
DataAdapter = new SqlDataAdapter(Command);
DataTable = new DataTable();
DataAdapter.Fill(DataTable);
String table = QueryReturn.ToUpper();
if (table.Contains("FROM"))
{
table = table.Substring(table.LastIndexOf("FROM"));
}
if (table.Contains("WHERE"))
{
table = table.Substring(0, table.LastIndexOf("WHERE"));
}
table = table.Replace("FROM", "").Replace("WHERE", "");
String[] items;
if (table.Contains(","))
{
items = table.Split(',');
table = items[0].Trim();
}
else
{
table = table.Trim();
}
if (table.Contains(" "))
{
int index = QueryReturn.IndexOf(" ");
table = table.Substring(0, index + 1);
}
String valueReplace = "";
foreach (DataRow dtRow in DataTable.Rows)
{
IdTables = SchemaTables.GetColumKeys(ConectionString, Connection.Database, table);
JsonRow = JsonConvert.SerializeObject(dtRow.Table).Replace("[", "").Replace("]", "");
ObjectJson = (JObject)JsonConvert.DeserializeObject(JsonRow);
IdTables.ForEach(i => { ObjectJson.Property(i)?.Remove(); });
JsonRow = ObjectJson.ToString();
item.Message.ConfigMessage.ForEach(c =>
{
valueReplace = "";
if (String.IsNullOrEmpty(c.DefinitiveValue))
{
c.NameColum.ForEach(d =>
{
foreach (var prop in ObjectJson)
{
if (prop.Key.ToUpper().Contains(d.ToUpper()))
{
valueReplace = prop.Value?.ToString();
break;
}
}
});
if (String.IsNullOrEmpty(valueReplace))
{
c.ExpressionRegular.ForEach(f =>
{
Regex = new Regex(f);
MachtExpression = Regex.Match(JsonRow);
if (MachtExpression.Groups.Count > 0)
{
valueReplace = MachtExpression.Groups[0].Value;
}
Regex = null;
MachtExpression = null;
});
}
}
else
{
valueReplace = c.DefinitiveValue;
}
if (!String.IsNullOrEmpty(valueReplace))
{
MessageBase = MessageBase.Replace(c.DinamycParam, valueReplace);
}
});
r.TypeNotification.ForEach(t => {
string errorMessage = "Type Notification not support";
bool sendNotification = false;
Regex = new Regex(t.ExpressionRegularMach);
MachtExpression = Regex.Match(JsonRow);
if (Enum.TryParse(t.TypeNotification, out TypeNotification typeNotification) && !String.IsNullOrEmpty(MachtExpression.Value))
{
sendNotification = Notification.Send(MachtExpression.Value, TitleMessage, MessageBase, t.JsonNotificationConfig, typeNotification, t.Provaider, out errorMessage);
}
if (sendNotification)
{
errorMessage = "message send";
}
else if (String.IsNullOrEmpty(MachtExpression.Value))
{
errorMessage = "no se encontro en la consulta un destinatario";
}
Connection.Insert(new NotificationLog()
{
Destination = MachtExpression.Groups[0].Value,
MessageErrorProvaider = errorMessage,
CreatedById = "NotifyDll",
MessageSend = MessageBase,
NotificationName = NotficationName,
NameRecipientsType = item.RecipientName,
FillterRecipenttype = QueryReturn,
Provaider = t.Provaider,
TypeNotification = t.TypeNotification,
TitleMessage = TitleMessage,
CreatedAt = DateTime.Now,
IsSend = sendNotification
});
//debe enviarse a grabar a la base de datos esta informacion
});
}
MessageBase = null;
TitleMessage = null;
}
}
});
});
}
public override DataTable GetTables()
{
var dataTable = SchemaTables.GetTablesDataTable();
// Only necessary, if ADOX is used in conjunction with ODBC.
// var procedureSet = new HashSet<string>();
// using var procedures = _catalog.Procedures;
// if (procedures != null)
// {
// var procedureCount = procedures.Count;
//
// for (var i = 0; i < procedureCount; i++)
// {
// using var procedure = procedures[i];
//
// var procedureName = (string) procedure.Name;
// procedureSet.Add(procedureName);
// }
// }
using var tables = _catalog.Tables;
var tableCount = tables.Count;
for (var i = 0; i < tableCount; i++)
{
using var table = tables[i];
using var properties = table.properties;
var tableName = (string)table.Name;
// Depending on the provider (ODBC or OLE DB) used, the Tables collection might contain VIEWs
// that take parameters, which makes them procedures.
// We make sure here, that we exclude any procedures from the returned table list.
// if (!procedureSet.Contains(tableName))
{
var tableType = (string)table.Type;
Debug.Assert(
tableType == "TABLE" ||
tableType == "LINK" ||
tableType == "SYSTEM TABLE" ||
tableType == "ACCESS TABLE" ||
tableType == "VIEW" ||
tableType == "TEMPORARY TABLE");
if (IsInternalTableByName(tableName))
{
tableType = "INTERNAL TABLE";
}
else if (IsSystemTableByName(tableName))
{
tableType = "SYSTEM TABLE";
}
else if (tableType == "TABLE" ||
tableType == "LINK")
{
tableType = "BASE TABLE";
}
var validationRule = GetPropertyValue <string>(properties, "Jet OLEDB:Table Validation Rule");
validationRule = string.IsNullOrEmpty(validationRule)
? null
: validationRule;
var validationText = GetPropertyValue <string>(properties, "Jet OLEDB:Table Validation Text");
validationText = string.IsNullOrEmpty(validationText)
? null
: validationText;
dataTable.Rows.Add(
tableName,
tableType,
validationRule,
validationText);
}
}
dataTable.AcceptChanges();
return(dataTable);
}
private void ChargeControlsAsync()
{
SchemaTables.GetAllTableFromDatabase(Constants.CONECTION_STRING, Constants.DB_NAME).ForEach(x => TableListBinding.Add(x));
}
private void CmbDestination_SelectedValueChanged(object sender, EventArgs e)
{
PnlNortificationType.Controls.Clear();
KeysTable = SchemaTables.GetColumKeys(Constants.CONECTION_STRING, Constants.DB_NAME, CmbDestination.SelectedValue.ToString());
}
public List <DynamicQueryParam> GetParamsDynamicTableDataEventQuery(SqlNotificationEventArgs e, String conectionString, String queryExecutePost, SqlConnection connection, String fillterCondition, String tableEvent, String columsNotify, DateTime dateTime)
{
ISchemaTables schemaTables = new SchemaTables();
List <DynamicQueryParam> listKeys = new List <DynamicQueryParam>();
List <String> idTables = schemaTables.GetColumKeys(conectionString, connection.Database, tableEvent);
String colums = "";
int countColum = 0;
idTables.ForEach(c => {
if (countColum == 0 && !columsNotify.Contains(c))
{
colums = c;
}
else if (!columsNotify.Contains(c))
{
colums += "," + c;
}
countColum++;
});
if (String.IsNullOrEmpty(colums))
{
colums += columsNotify;
}
else
{
colums += "," + columsNotify;
}
String query = "";
switch (e.Info)
{
case SqlNotificationInfo.Insert:
query = String.Format("Select Top 1 {0} From {1} where CreatedAt < @datetime Order by CreatedAt desc", colums, tableEvent);
break;
case SqlNotificationInfo.Update:
query = String.Format("Select Top 1 {0} From {1} where UpdatedAt < @datetime Order by UpdatedAt desc", colums, tableEvent);
break;
}
using (connection = new SqlConnection(conectionString))
{
#pragma warning disable S3649 // User-provided values should be sanitized before use in SQL statements
using (SqlCommand command = new SqlCommand(query, connection))
#pragma warning restore S3649 // User-provided values should be sanitized before use in SQL statements
{
SqlParameter parameter = command.Parameters.Add("@datetime", System.Data.SqlDbType.DateTime);
// Set the value.
parameter.Value = dateTime;
command.Notification = null;
connection.Open();
// Execute the command.
using (SqlDataReader reader = command.ExecuteReader())
{
DataTable dataTable = new DataTable();
dataTable.Load(reader);
if (dataTable.Rows.Count > 0)
{
DataRow row = dataTable.Rows[0];
String JsonRow = JsonConvert.SerializeObject(row.Table).Replace("[", "").Replace("]", "");
JObject ObjectJson = (JObject)JsonConvert.DeserializeObject(JsonRow);
foreach (var prop in ObjectJson)
{
if (!String.IsNullOrEmpty(fillterCondition) && fillterCondition.Contains("@" + prop.Key))
{
fillterCondition = fillterCondition.Replace("@" + prop.Key, prop.Value?.ToString());
}
if (!String.IsNullOrEmpty(queryExecutePost) && queryExecutePost.Contains("@" + prop.Key))
{
queryExecutePost = queryExecutePost.Replace("@" + prop.Key, prop.Value?.ToString());
}
if (idTables.Contains(prop.Key))
{
listKeys.Add(new DynamicQueryParam()
{
IdParam = "@" + prop.Key, ValueParam = prop.Value?.ToString()
});
}
}
}
}
if (!String.IsNullOrEmpty(fillterCondition))
{
using (SqlCommand commandValidate = new SqlCommand(fillterCondition, connection))
{
using (SqlDataReader reader = commandValidate.ExecuteReader())
{
DataTable dataTable = new DataTable();
dataTable.Load(reader);
if (dataTable.Rows.Count <= 0)
{
listKeys = null;
}
}
}
}
if (!String.IsNullOrEmpty(queryExecutePost))
#pragma warning disable S3966 // Objects should not be disposed more than once
{
using (connection)
#pragma warning restore S3966 // Objects should not be disposed more than once
{
connection.Open();
using (SqlCommand commandValidate = new SqlCommand(queryExecutePost, connection))
#pragma warning disable S108 // Nested blocks of code should not be left empty
{
}
#pragma warning restore S108 // Nested blocks of code should not be left empty
}
}
}
}
return(listKeys);
}
public List <DynamicQueryParam> GetParamsDynamicTableDataEvenTable <T>(T entity, String conectionString, SqlConnection connection, String fillterCondition, String queryExecutePost, String tableEvent, String columsNotify, DateTime dateTime) where T : class
{
ISchemaTables schemaTables = new SchemaTables();
List <DynamicQueryParam> listKeys = new List <DynamicQueryParam>();
List <String> idTables = schemaTables.GetColumKeys(conectionString, connection.Database, tableEvent);
String colums = "";
int countColum = 0;
idTables.ForEach(c => {
if (countColum == 0 && !columsNotify.Contains(c))
{
colums = c;
}
else if (!columsNotify.Contains(c))
{
colums += "," + c;
}
countColum++;
});
if (String.IsNullOrEmpty(colums))
{
colums += columsNotify;
}
else
{
colums += "," + columsNotify;
}
JObject ObjectJson = (JObject)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(entity));
foreach (var prop in ObjectJson)
{
if (!String.IsNullOrEmpty(fillterCondition) && fillterCondition.Contains("@" + prop.Key))
{
fillterCondition = fillterCondition.Replace("@" + prop.Key, prop.Value?.ToString());
}
if (!String.IsNullOrEmpty(queryExecutePost) && queryExecutePost.Contains("@" + prop.Key))
{
queryExecutePost = queryExecutePost.Replace("@" + prop.Key, prop.Value?.ToString());
}
if (idTables.Contains(prop.Key))
{
listKeys.Add(new DynamicQueryParam()
{
IdParam = "@" + prop.Key, ValueParam = prop.Value?.ToString()
});
}
}
if (!String.IsNullOrEmpty(fillterCondition))
{
using (connection)
{
connection.Open();
using (SqlCommand commandValidate = new SqlCommand(fillterCondition, connection))
{
using (SqlDataReader reader = commandValidate.ExecuteReader())
{
DataTable dataTable = new DataTable();
dataTable.Load(reader);
if (dataTable.Rows.Count <= 0)
{
listKeys = null;
}
}
}
}
}
if (!String.IsNullOrEmpty(queryExecutePost))
{
#pragma warning disable S3966 // Objects should not be disposed more than once
using (connection)
#pragma warning restore S3966 // Objects should not be disposed more than once
{
connection.Open();
using (SqlCommand commandValidate = new SqlCommand(queryExecutePost, connection))
#pragma warning disable S108 // Nested blocks of code should not be left empty
{
}
#pragma warning restore S108 // Nested blocks of code should not be left empty
}
}
return(listKeys);
}
