Problem
We are all familiar with different programs which search for a string in a text file. Such programs can search in one or more files, and at the end of the search we get a list of files which contain the string. In several cases we can find all occurrences of that string in a file. This problem can be extended such that we can search for a string in a SQL Server database which we will cover with this C# program.
Solution
Searching for a string in SQL Server database tables has been discussed over the years, and there are several approaches for the problem (see references at the end). This article presents a different approach for the problem. For simplification, I search for a string in a single user database, and in a single user table from that database. The project has a user interface, where the user connects to SQL Server, chooses a database from a drop-down list, and after that chooses a table from that database where the search is performed. The results are exported to an Excel file. The string is searched in every column which can contains string type in C# (char, varchar, nchar, nvarchar) and each row which contains the string is exported to a corresponding Excel row.
For this article I used SQL Server 2019 Enterprise, Visual Studio 2022 and Microsoft Office 365. The C# project is written in .NET 6, and the operating system used is Windows 11.
Example Search
For example, let us consider the table below that holds the data we want to search:
If we search for the string “brown fox”, the following is what should be extracted and only include rows and data where a match is made.
As you can see, the 3rd line in the SQL table doesn’t have the string, so it is not included. Also, the columns which are of different type (numeric or datetime for example), as columns col2, xyz and NumericField, are not included.
C# Application to Search for Strings
If you run the attached C# project code, the main screen is as follows. This allows you to connect to a SQL Server using a trusted connection, enter text to be searched, pick a database on that SQL Server instance and then pick a table from that database.
Just something to note, when you first start the application you only see the below until after you make a SQL Server connection. Other screen elements will appear only after you connect successfully to the SQL Server.
Search SQL Server Tables Code Explanation
Because it is much easier to retrieve information from SQL Server related to different objects, like databases, tables, columns etc., we will use SQL Server Management Objects (SMO):
- using Microsoft.SqlServer.Management.Common;
- using Microsoft.SqlServer.Management.Smo;
Connecting to SQL Server and retrieving a list of user databases is easy:
_connstr = "Data Source=" + sqlServerBox.Text + ";Integrated Security=true;Column Encryption Setting=enabled;MultipleActiveResultSets=True;TrustServerCertificate=True";
_conn = new SqlConnection(_connstr);
_serverConnection = new ServerConnection(_conn);
_serverConnection.Connect();
_server = new Server(_serverConnection);
_server.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect;
DatabaseCollection dbs = _server.Databases;
foreach(Database db in dbs)
{
if(!db.IsSystemObject) // Add only user databases
databaseList.Items.Add(db.Name);
}
As you can see, we need to use TrustServerCertificate=True setting when we use integrated security and SMO.
After we connect to SQL Server, we select a database from the databaseList list and get a list of user tables of that database:
_databaseName = databaseList.SelectedItem.ToString();
_db = _server.Databases[_databaseName];
TableCollection tables = _db.Tables;
foreach(Table tbl in tables)
{
if(!tbl.IsSystemObject)
tableList.Items.Add(tbl.Name);
}
For the selected table we get a list of columns, and consider only those that have a string type and are long enough to contain the searched string. If we find such a column we add it to a dictionary (column name as key and column index as value), because when we read the data from the table we need to identify the Excel corresponding column and add a new row in the Excel sheet in that column. Because of this technique the column order in the Excel sheet might not be the same as in the database table. In the example above if we have the last row in the database table as first one, the column my-new-id would be the first in the Excel sheet.
We get the list of columns first: ColumnCollection columns = tbl.Columns;
Here the tbl is of type Table.
Having the list of columns, we can populate the Excel sheet:
// We write column names in the 3rd row of the Excel file
// Data will be written from row 4 on
int i = 4;
foreach(DataRow row in result.Tables[0].Rows)
{
bool haverows = false;
foreach(Column col in columns)
{
// Ignore columns which are not string
if(!col.DataType.IsStringType)
continue;
// Ignore columns which are shorter than what we search
if(_stringToSearch.Length > col.DataType.MaximumLength)
continue;
// If the column in this row is null, ignore it
if(row[col.Name] == DBNull.Value)
continue;
string cell = (string)row[col.Name];
if(cell != null)
{
if(cell.Contains(_stringToSearch, (caseSensitive.Checked) ?
StringComparison.InvariantCulture : StringComparison.InvariantCultureIgnoreCase))
{
// found the string
haverows = true;
foundstring = true;
int colindex;
// see if the column we already found
if(!columnsFound.ContainsKey(col.Name))
{
// Add the new column to the list and to the Excel file
columnsFound.Add(col.Name, ++lastColumnIndex);
xlsWorksheet.Cells[3, lastColumnIndex + 1] = col.Name;
colindex = lastColumnIndex + 1;
}
else
{
// Get the Excel column index
colindex = columnsFound[col.Name] + 1;
}
xlsWorksheet.Cells[i, colindex] = cell.Length <= 255 ? cell :
"<Table column too is long>";
}
}
}
if(haverows)
++i; // go to the next row in the Excel file
}
We save the Excel file only if we find the search string in at least one column.
if(foundstring)
{
// Remove the old excel report file
try
{
FileInfo oldFile = new FileInfo(fileName);
if(oldFile.Exists)
{
File.SetAttributes(oldFile.FullName, FileAttributes.Normal);
oldFile.Delete();
}
}
catch(Exception ex)
{
MessageBox.Show("Error removing old Excel report: " + ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
// we found the string, try to save the Excel file
xlsWorkbook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue,
misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, Excel.XlSaveConflictResolution.xlLocalSessionChanges,
misValue, misValue, misValue, misValue);
xlsWorkbook.Close(true, misValue, misValue);
}
else
{
xlsWorkbook.Close(false, misValue, misValue);
}
Possible Enhancements and Some Limitations
It is easy to extend this project, such that the search can be done in all databases attached to the SQL instance, or to perform the search in all tables of a selected database, having one Excel sheet for each table which contains the searched string.
This approach has some limitations. For example, if the number of rows in the database table exceeds the maximum number of rows allowed in the Excel sheet. The project detects when the database table contains too many rows which cannot be saved in the Excel file. In this case it simply ignores the rest of the rows in the database table. You can improve this, for example, by adding a message at the end of the Excel file which says there are too many rows in the database table
If a table has a primary key, you can include it in the Excel file as first column(s) (a primary key may have several columns). By doing this you may consider to not duplicate a column in the primary key if that column might contain the string you are searching for.
Full Project Code
References
- https://stackoverflow.com/questions/9185871/how-do-i-search-an-sql-server-database-for-a-string
- https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server
- https://stackoverflow.com/questions/8435963/how-to-find-a-string-inside-a-entire-database
- https://stackoverflow.com/questions/591853/search-for-a-string-in-all-tables-rows-and-columns-of-a-db
Next Steps
- Extend the search for all user tables in the database, and save the results for each table in its own Excel sheet.
- Find other things you can do to extend this base set of code.
