private static void deleteFilter(
SqlConnection connection,
string instance,
DataCollectionFilter filter
)
{
Debug.Assert(connection != null);
Debug.Assert(!string.IsNullOrEmpty(instance));
Debug.Assert(filter != null);
Debug.Assert(filter.FilterDisposition == Disposition.Deleted);
// Setup filter id param, and delete the filter.
SqlParameter paramHeaderId = new SqlParameter(ParamDeleteFilterFromRepositoryFilterruleheaderid, filter.FilterId);
Sql.SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure,
NonQueryDeleteFilterFromRepository, new SqlParameter[] { paramHeaderId });
}
private static void createNewFilter(
SqlConnection connection,
string instance,
DataCollectionFilter filter
)
{
Debug.Assert(connection != null);
Debug.Assert(!string.IsNullOrEmpty(instance));
Debug.Assert(filter != null);
Debug.Assert(filter.FilterDisposition == Disposition.New);
// Setup register server params.
SqlParameter paramConnectionname = new SqlParameter(ParamWriteFilterToRepositoryConnectionname, instance);
SqlParameter paramRulename = new SqlParameter(ParamWriteFilterToRepositoryRulename, filter.FilterName);
SqlParameter paramDescription = new SqlParameter(ParamWriteFilterToRepositoryDescription, filter.Description);
// Execure stored procedure and get the header id.
int hdrid = 0;
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(connection, null, CommandType.StoredProcedure,
QueryWriteFilterToRepository, new SqlParameter[] { paramConnectionname, paramRulename, paramDescription }))
{
if (rdr.Read())
{
hdrid = rdr.GetInt32(0);
}
}
// Add all the filters to the filter table.
SqlParameter paramRuleheaderid = new SqlParameter(ParamWriteRuleToRepositoryRuleheaderid, hdrid);
foreach (Rule rule in filter.Rules)
{
SqlParameter paramClass = new SqlParameter(ParamWriteRuleToRepositoryClass, rule.ObjectType);
SqlParameter paramScope = new SqlParameter(ParamWriteRuleToRepositoryScope, rule.ObjectScopeString);
SqlParameter paramMatchstring = new SqlParameter(ParamWriteRuleToRepositoryMatchstring, rule.MatchString);
Sql.SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure,
QueryWriteRuleToRepository, new SqlParameter[] { paramRuleheaderid, paramClass,
paramScope, paramMatchstring });
}
}
private static void updateFilter(
SqlConnection connection,
string instance,
DataCollectionFilter filter
)
{
Debug.Assert(connection != null);
Debug.Assert(!string.IsNullOrEmpty(instance));
Debug.Assert(filter != null);
Debug.Assert(filter.FilterDisposition == Disposition.Modified);
// Delete the filter rules.
SqlParameter paramDeleteHeaderId = new SqlParameter(ParamDeleteRulesFromRepositoryFilterruleheaderid, filter.FilterId);
Sql.SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure,
NonQueryDeleteRulesFromRepository, new SqlParameter[] { paramDeleteHeaderId });
// Update filter header.
SqlParameter paramUpdateHeaderId = new SqlParameter(ParamUpdateFilterHeaderFilterruleheaderid, filter.FilterId);
SqlParameter paramRulename = new SqlParameter(ParamUpdateFilterHeaderRulename, filter.FilterName);
SqlParameter paramDescription = new SqlParameter(ParamUpdateFilterHeaderDescription, filter.Description);
SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure,
NonQueryUpdateFilterHeader, new SqlParameter[] { paramUpdateHeaderId, paramRulename, paramDescription });
// Add new rules.
SqlParameter paramAddRuleHeaderId = new SqlParameter(ParamWriteRuleToRepositoryRuleheaderid, filter.FilterId);
foreach (Rule rule in filter.Rules)
{
SqlParameter paramClass = new SqlParameter(ParamWriteRuleToRepositoryClass, rule.ObjectType);
SqlParameter paramScope = new SqlParameter(ParamWriteRuleToRepositoryScope, rule.ObjectScopeString);
SqlParameter paramMatchstring = new SqlParameter(ParamWriteRuleToRepositoryMatchstring, rule.MatchString);
Sql.SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure,
QueryWriteRuleToRepository, new SqlParameter[] { paramAddRuleHeaderId, paramClass,
paramScope, paramMatchstring });
}
}
public static List <DataCollectionFilter> GetSnapshotFilters(
string instance,
int snapshotid
)
{
Debug.Assert(!string.IsNullOrEmpty(instance));
Debug.Assert(snapshotid != 0);
// Init return.
List <DataCollectionFilter> filters = new List <DataCollectionFilter>();
try
{
// Open connection to repository and retrieve rules.
using (SqlConnection connection = new SqlConnection(Program.gController.Repository.ConnectionString))
{
// Open the connection.
connection.Open();
// Query the repository for filter rules for the server.
Dictionary <int, DataCollectionFilter> filterDictionary = new Dictionary <int, DataCollectionFilter>();
SqlParameter paramSnapshotid = new SqlParameter(ParamQueryGetSnapshotFiltersSnapshotid, snapshotid);
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(connection, null, CommandType.Text,
QueryGetSnapshotFilters, new SqlParameter[] { paramSnapshotid }))
{
while (rdr.Read())
{
// Read the filter id.
SqlInt32 filterId = rdr.GetSqlInt32((int)SnapshotFiltersColumn.FilterRuleHeaderId);
Debug.Assert(!filterId.IsNull);
if (filterId.IsNull)
{
continue;
} // invalid filter id continue.
// If the filter is not in the dictionary, create object and add to the dictionary.
DataCollectionFilter filter = null;
if (!filterDictionary.TryGetValue(filterId.Value, out filter))
{
SqlString filterName = rdr.GetSqlString((int)SnapshotFiltersColumn.RuleName);
SqlString description = rdr.GetSqlString((int)SnapshotFiltersColumn.Description);
SqlString createdBy = rdr.GetSqlString((int)SnapshotFiltersColumn.CreatedBy);
SqlDateTime creationTime = rdr.GetSqlDateTime((int)SnapshotFiltersColumn.CreatedTm);
SqlString lastModifiedBy = rdr.GetSqlString((int)SnapshotFiltersColumn.LastModifiedBy);
SqlDateTime lastModificationTime = rdr.GetSqlDateTime((int)SnapshotFiltersColumn.LastModifiedTm);
filter = new DataCollectionFilter(filterId, instance, filterName, description, createdBy, creationTime,
lastModifiedBy, lastModificationTime);
filterDictionary.Add(filterId.Value, filter);
}
// Query for rule fields and add new rule to filter.
SqlInt32 ruleId = rdr.GetInt32((int)SnapshotFiltersColumn.FilterRuleId);
SqlInt32 objClass = rdr.GetSqlInt32((int)SnapshotFiltersColumn.Class);
SqlString objScope = rdr.GetSqlString((int)SnapshotFiltersColumn.Scope);
SqlString matchString = rdr.GetSqlString((int)SnapshotFiltersColumn.MatchString);
Rule rule = new Rule(ruleId, objClass, objScope, matchString);
filter.AddRule(rule);
}
// Add the filters to the list.
foreach (KeyValuePair <int, DataCollectionFilter> kvp in filterDictionary)
{
filters.Add(kvp.Value);
}
}
}
}
catch (Exception ex)
{
MsgBox.ShowError(ErrorMsgs.CantGetSnapshotFilters, ex);
filters = null;
}
return(filters);
}
public static void GetFilters(
string connectionString,
string instance,
out List <DataCollectionFilter> filters
)
{
Debug.Assert(!string.IsNullOrEmpty(connectionString));
Debug.Assert(!string.IsNullOrEmpty(instance));
// Init return.
filters = new List <DataCollectionFilter>();
// Open connection to repository and retrieve rules.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection.
connection.Open();
// Query the repository for filter rules for the server.
Dictionary <int, DataCollectionFilter> filterDictionary = new Dictionary <int, DataCollectionFilter>();
SqlParameter paramInstance = new SqlParameter(ParamQueryGetRegisteredServerFiltersInstance, instance);
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(connection, null, CommandType.Text,
QueryGetRegisteredServerFilters, new SqlParameter[] { paramInstance }))
{
while (rdr.Read())
{
// Read the filter id.
SqlInt32 filterId = rdr.GetSqlInt32((int)RegisteredServerFiltersColumn.FilterRuleHeaderId);
Debug.Assert(!filterId.IsNull);
if (filterId.IsNull)
{
continue;
} // invalid filter id continue.
// If the filter is not in the dictionary, create object and add to the dictionary.
DataCollectionFilter filter = null;
if (!filterDictionary.TryGetValue(filterId.Value, out filter))
{
SqlString connectionName = rdr.GetSqlString((int)RegisteredServerFiltersColumn.ConnectionName);
SqlString filterName = rdr.GetSqlString((int)RegisteredServerFiltersColumn.RuleName);
SqlString description = rdr.GetSqlString((int)RegisteredServerFiltersColumn.Description);
SqlString createdBy = rdr.GetSqlString((int)RegisteredServerFiltersColumn.CreatedBy);
SqlDateTime creationTime = rdr.GetSqlDateTime((int)RegisteredServerFiltersColumn.CreatedTm);
SqlString lastModifiedBy = rdr.GetSqlString((int)RegisteredServerFiltersColumn.LastModifiedBy);
SqlDateTime lastModificationTime = rdr.GetSqlDateTime((int)RegisteredServerFiltersColumn.LastModifiedTm);
//SQLSecure 3.1 (Barkha Khatri) getting servertype as well
ServerType serverType = Helper.ConvertSQLTypeStringToEnum(rdr.GetString((int)RegisteredServerFiltersColumn.ServerType));
filter = new DataCollectionFilter(filterId, connectionName, filterName, description, createdBy, creationTime,
lastModifiedBy, lastModificationTime, serverType);
filterDictionary.Add(filterId.Value, filter);
}
// Query for rule fields and add new rule to filter.
SqlInt32 ruleId = rdr.GetInt32((int)RegisteredServerFiltersColumn.FilterRuleId);
SqlInt32 objClass = rdr.GetSqlInt32((int)RegisteredServerFiltersColumn.Class);
SqlString objScope = rdr.GetSqlString((int)RegisteredServerFiltersColumn.Scope);
SqlString matchString = rdr.GetSqlString((int)RegisteredServerFiltersColumn.MatchString);
Rule rule = new Rule(ruleId, objClass, objScope, matchString);
filter.AddRule(rule);
}
// Add the filters to the list.
foreach (KeyValuePair <int, DataCollectionFilter> kvp in filterDictionary)
{
filters.Add(kvp.Value);
}
}
}
}
