Introduction
Sometimes, we have to know where in the code we are updating or selecting a given field, or using a hardcoded value or string, or making a given operation, and so on. Although database tools allow us to know database objects dependencies, this may not be enough to quickly find the exact code fragment where we are doing an exact data handling or operation.
DBTextFinder is a simple tool that helps you to perform a precise search in all the stored procedures, functions, triggers, packages and views code, or a selected subset of them, using regular expressions.
Additionally, you can search for a given text in all the text fields of a selected set of tables, using regular expressions too.
The application provides connections to MySQL, SQL Server and Oracle servers, and supports remote connections via WCF services. You can easily extend the list of available DBMS writing your own connectors without having to change the application code.
This tool is translated to two languages, English and Spanish.
The project is written in C# using Visual Studio 2013.
The Application Interface
DBTextFinder is an MDI application. The searches are carried on asynchronously, so that you can launch some of them simultaneously.
In order to connect to a given database server, you need the proper connector. A connector is a DLL that implements the interfaces defined in the DBTFCommons shared class library. By default, there are three complete connectors, for MySQL, SQL Server and Oracle databases.
The additional connectors can be added using the Install Connector option in the Options menu. You only have to select the DLL file with the find file dialog box. You can manage connectors with the Manage Connectors option in the Options menu, enabling or disabling them.
You can create or modify a database connection using the Manage Connections option in the Options menu.
To create a new connection, select the proper connector in the drop down list, write a connection name in the Connection Name text box, and provide the connection data. In the Server text box, you have to provide the database server address, Catalog is for the name of the database in SQL Server and MySQL, or for the TNS service name in Oracle. You have to provide a user name and password, or, in SQL Server, choose Windows Authentication.
To modify an existing connection, you have to select their name in the drop down list below the Connection Name text box.
You can test the connection before saving with the Test Connection button. Then, use the Save button to store it in the application config file.
Now, you can start using the tool. Select the New option in the File menu and, in the search form, select the connection name in the drop down list, in the upper left side.
In the right side, there is a tree view where you can select the database objects to include in the search. You can filter this view too by using the Search in Tables and Search in Procedures checkboxes, in the top of the form.
Once the database objects are selected, you can write the search expression in the text box located in the upper side of the form. You can check the Ignore Case checkbox to perform a case insensitive search. Then, press the Search button. You can cancel the operation with the Cancel button.
Suppose, for example, that you want to find where in the code is performed an update operation in the HireDate field of the Employee table, in the AdventureWorks sample database. You can write the following regular expression: update\b(.|\n)*employee(.|\n)*hiredate. Two results are shown in the result list pane:
When you select one of them, you can see in the bottom pane the source code and the list of matches. If you select one of these matches, you can see the corresponding text selected in the source code.
You can select some of the matches and replace them for a given text using the Replace button and the text box above it. You can drop the entire procedure or view using the Drop button. Be careful if you want to use these two options, as they do not have an undo procedure. Of course, a warning message will be shown before proceeding.
Regarding the search in tables, the application is not intended to make massive updates of the data, so it works row to row, as the search is performed on all the text columns of each row. All the fields of the selected record, along with the key values, are shown in the bottom pane, but you can only edit the fields with matches. For example, searching AdventureWorks for "Research and Development":
The text on the fields with the Replace checkbox on the right can be replaced if you check them, write the new text, and press the Replace button. The record can be also deleted with the Delete button. As with the same options in the case of procedures and views, be careful if you use these options.
Using the Code
There are various projects in the solution:
DBTextFinder: the main application, you don't need to modify this project if you only want to write a new database connectorDBTFCommons: Contains the definition of data types and interfaces used by the application. You have to add a reference to this class library to your project when developing new connectors.DBTFMySqlConnector, DBTFOracleConnector and DBTFSqlServerConnection: the connectors for MySQL, Oracle and SQL Server. You can use this code as an example to write your own connector.DBTFWCFServiceSqlServer: An example of a WCF service to connect to a SQL Server database.DBTFConsoleService: Another example of WCF service to connect to a SQL Server database, implemented as a console application.DBTFWinServiceSqlServer: Another example of WCF implementation as a Windows service.dbtfsetup: The setup project for the solution.
A detailed explanation of the data types and interfaces would made this article very long and tedious, so, you can read this article to find more details about that.
All the data and interfaces are defined so that they can be used in the implementation of a WCF service, you can read more about that in this article.
And that's all. Thanks for reading!!!
History
- 21st December, 2017: Initial version
