DBTextFinder Application, examples of connectors
In this post we will look at the data structures and the necessary interfaces to develop connectors for any database to add to the DBTextFinder application, a tool for finding text in registers, stored procedures and views. You can also download sample code, developed with Microsoft Visual Studio 2013 with connectors for SQL Server, Oracle 12c and MySQL.
This post is related with the article DBTextFinder application to search text in databases.
Here you can download the code of the SQL Server connector, download the project for the MySQL connector and download the Oracle connector project code.
All projects refer to the DBTFCommons.dll class library, which contains the definitions of the classes and interfaces used by all of them. This library is installed along with the program.
To develop a connector for a particular database, you only have to implement a single interface. Following, we will review this interface and all the classes needed for communication with the application.
Data classes used by DBTextFinder
All classes used for data exchange between the application and the connectors are in the namespace of DBTFCommons.Data.
SearchScope enum
With this enumeration you can indicate the scope to which belong the objects in the database that can be selected, there are three different values:
- Tables: Tables and Views.
- StoredProcs: Stored procedures, functions, triggers, views code and, in the case of Oracle, packages.
- All: All objects in the database.
ConnectionData class
This class contains the data needed to connect:
public class ConnectionData
{
public string StringConnection { get; set; }
public SearchScope Scope { get; set; }
}
StringConnection is the connection string to the database that you must build in the connector. Scope is used to set the type of objects on which it will work.
ConnectionOptions class
This class is related to the dialog box used by the application to configure the connections:
public class ConnectionOptions
{
public bool SwowInitialCatalog { get; set; }
public bool ShowWindowsAuthentication { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public string DataSource { get; set; }
public string InitialCatalog { get; set; }
public bool WindowsAuthentication { get; set; }
public string StringConnection { get; set; }
public string ProviderName { get; set; }
public string Error { get; set; }
public string Description { get; set; }
}
ShowInitialCatalog allows hiding the Catalog text box, ShowWindowAuthentication has the same use to hide Windows authentication. Username and Password contain the username and password for the connection. DataSource is the server address, and InitialCatalog is the schema or database at which you access (depending on the connector). WindowsAuthentication is used to configure the access using integrated Windows authentication, StringConnection is the connection string to the database. ProviderName is the name of the access provider, which in this case is the type name of the connector.
In the Error property can be returned error messages produced when testing the connection, and Description is the description of the connector that will be displayed to the user in the Connector drop-down list.
FieldData class
This class is used to store data concerning a field in a table in which a match was found with the search text:
public class FieldData : IComparable<FieldData>, IEquatable<FieldData>
{
public string Name { get; set; }
public string Value { get; set; }
public bool Selected { get; set; }
public string ReplaceEx { get; set; }
public int CompareTo(FieldData other);
public bool Equals(FieldData other);
}
Name contains the field name, and Value is for the original value. Selected is used to indicate whether the field has been selected by the user for a replacement operation. ReplaceEx is the expression that will replace the matching string found.
CompareTo and Equals are the IComparable and IEquatable interfaces implementation.
ObjectResult class
It is used to return the search results and to indicate the selected items for text replacement operations:
public class ObjectResult
{
public string Schema { get; set; }
public string Name { get; set; }
public bool Selected { get; set; }
}
Schema is the schema to which the object belongs, and Name is the object name. The Selected property indicates whether the object is selected by the user.
TableResult class
It is an extension of the ObjectResult class, used to return search results on tables. It contains several members with data of the different fields of the table.
public class TableResult : ObjectResult
{
public TableResult(IEnumerable<FieldData> text, IEnumerable<FieldData>
keys, IEnumerable<FieldData> all);
public FieldData[] TextFields { get; set; }
public FieldData[] KeyFields { get; set; }
public FieldData[] AllFields { get; set; }
public string QueryFields { get; }
public int SelectedCount { get; }
public FieldData GetField(string name);
public void SetValue(string name, string value);
public override string ToString();
}
The constructor accepts three parameters; in the text parameter is passed a IEnumerable with the text fields of the table, in the keys parameter, the fields that form the primary key, and the all parameter id for all fields of the table.
The TextFields, KeyFields and AllFields properties allow retrieving information about the fields that contain text, form part of the primary key, or all fields in the table, respectively.
QueryFields is a function that returns the comma-separated list of table fields, to build SQL SELECT statements.
With the GetField function you can retrieve information from a given field providing its name, and with SetValue you can change its value.
The ToString override is used to display the user the data in the row.
ProcMatch class
The objects of this class store data about the matches found within the code of a stored procedure or view.
public class ProcMatch
{
public int Index { get; set; }
public int Length { get; set; }
public bool Selected { get; set; }
public string ReplaceEx { get; set; }
public override string ToString();
}
Index is the index of the first character of the match within the code, and Length their length. The Selected property indicates whether the match has been selected by the user. ReplaceEx contains the replacement string, and the ToString override allows showing the match to the user.
CodeType enumeration
The values of this enumeration indicate whether a given text is the code of a stored procedure or of a view, with their View or Procedure values.
ProcResult class
This class extends the search results to the code of procedures and views:
public class ProcResult : ObjectResult
{
public CodeType CodeType { get; set; }
public string ProcedureCode { get; set; }
public ProcMatch[] Matches { get; set; }
public int MatchCount { get; }
public int SelectedCount { get; }
public void AddMatch(ProcMatch pm);
public override string ToString();
}
CodeType is used to determine whether the results belong to the code of a view or a procedure, and ProcedureCode contains the complete code.
Matches is the collection of all matches found in the code, and MatchCount returns the number of matches found. SelectedCount returns the number of user-selected matches.
AddMatch is used to add new matches, and the override of ToString returns the name of the object, qualified with the name of the schema to which it belongs.
Interfaces used by the application
These interfaces are defined in the DBTFCommons.Interfaces namespace.
IDBTFSearchCallback interface
This interface is used to return search results when asynchronous versions of the main interface methods are used.
public interface IDBTFSearchCallback
{
bool SearchResult(ObjectResult result);
}
The only member is the SearchResult function, used to pass to the program the results encountered. The return value indicates whether to cancel the operation.
IDBTFConnection interface
This is the interface that must implement the connector. All functions have a synchronous and an asynchronous version, which perform the same function. The asynchronous versions must going returning the results to the application using the interface IDBTFSearchCallback discussed above.
public interface IDBTFConnection
{
IDBTFSearchCallback Callback { get; set; }
ConnectionOptions GetConnectionOptions();
Task<ConnectionOptions> GetConnectionOptionsAsync();
ConnectionOptions ParseConnectionString(string strconn);
Task<ConnectionOptions> ParseConnectionStringAsync(string strconn);
ConnectionOptions ValidateConnectionOptions(ConnectionOptions conntest);
Task<ConnectionOptions> ValidateConnectionOptionsAsync(ConnectionOptions
conntest);
string[] GetSchemas(ConnectionData connection);
Task<string[]> GetSchemasAsync(ConnectionData connection);
string[] GetTables(ConnectionData connection, string schema);
Task<string[]> GetTablesAsync(ConnectionData connection, string schema);
string[] GetViews(ConnectionData connection, string schema);
Task<string[]> GetViewsAsync(ConnectionData connection, string schema);
string[] GetProcedures(ConnectionData connection, string schema);
Task<string[]> GetProceduresAsync(ConnectionData connection, string
schema);
ObjectResult[] Search(ConnectionData connection, string tables, string
views, string procedures, string searchexpr, bool ignorecase);
Task SearchAsync(ConnectionData connection, string tables, string views,
string procedures, string searchexpr, bool ignorecase);
void Replace(ConnectionData connection, ObjectResult result, string
searchexpr, bool ignorecase);
Task ReplaceAsync(ConnectionData connection, ObjectResult result, string
searchexpr, bool ignorecase);
void Delete(ConnectionData connection, ObjectResult result);
Task DeleteAsync(ConnectionData connection, ObjectResult result);
}
The application provides in the Callback property one instance of IDBTFSearchCallback appropriate to communicate the search results in asynchronous operations.
With the GetConnection function you must return the appropriate connection for your connector. ParseConnectionString is used to extract connection settings from a connection string, and ValidateConnectionOptions must make a connection test with the user-selected options.
In order to obtain the list of objects in the database, you must implement the GetSchemas functions, which returns schema names, GetTables, to return the list of table names, GetViews for views and GetProcedures for stored procedures, functions and triggers.
Search starts a search in the specified connection. As parameters you have connection data in connection, and in tables the names of tables separated by commas. These names are qualified with the schema name to which they belong. In views are passed the names of the views in the same format, and the names of procedures in procedures. searchexpr is the search expression, and ignorecase will indicate whether you the search must be case sensitive.
Replace perform the replacements of text within a given search result, which is passed in the result parameter. ignorecase and searchexpr have the same meaning as in the Search function.
Finally, Delete is called to delete records, procedures, and views, according to the result passed in the result parameter.