Introduction
During the development of a document library system (branded #Calchas), I needed to query my database often to get small bits of information. This meant that each time I had to access my data, I would need to insert many lines of code to get a single value from the database.
Hence, I decided to develop a database utilities class that would facilitate and speed up this repetitive task. My coding days began in Access' VBA and hence the method names.
I hope that the given utilities are helpful to the other C# coders out there. Feel free to leave a message with your comments, I welcome constructive criticism.
Prerequisites - Utilities How-to
If you choose to use the given DLL without manipulating the code, you should include in your project settings a setting called ConnectionString which should contain the connection string to your SQL database. Note the code has been tested with Microsoft SQL Server so if you choose to use another datasource, you should verify connectivity (and also e-mail me with feedback!).
If, on the other hand, you prefer to use your own connectivity method, you should change the code of the GetConnectionString method to retrieve your connection string in any way you see preferable.
Note that if you choose to use a connection type other than SQLConnection, you should change that in both ReadValue and ReadValues methods since both are used in the public methods.
An important note must also be made about the database design. All your tables should use a Guid as the primary key and they should also include an integer (SQL longinteger is preferable) field called AutoNumber which should number the records (identity should be set to Yes). Also, you will need to include a Guid field named User which will include the user ID of the user adding records to the database. I also use a RegDate field (of type SmallDateTime) which is filled by the SQL Server and adds the date when the record was inserted.
Using the Code
The use of the code is pretty straightforward. The public methods are split into six categories:
-
Lookup Functions: Contains methods that query the database for a given value. Namely the methods contained in this category are:
DLookupDMaxDMinDFirstDLastDSumDAverageDCount
-
Update Functions: Contains a method to update the database
-
Insert Functions: Contains a method to insert records to the database
-
Delete Functions: Contains a method to delete records from the database
-
DatabaseInfo Functions: Contains a public method that retrieves the name of the primary key field of a table from the database. It will include other database info methods such as a list of tables, etc.
-
Direct Access Functions: Contains two methods to execute SQL code directly on the database. The above mentioned methods are useful all-around procedures, but often a more hands-on approach is required!
Points of Interest
The main problem that I had was during the development of how to create a generic method to read connection string settings from a source application. Since I develop a series of C# database applications for various uses, I needed an all-around utility class that can be used to access or manipulate my databases. A hands-on approach on the XML file holding the application settings was required:
private static string GetConnectionString()
{
string connectionString = "";
XmlDocument xd = new XmlDocument();
xd.Load(Application.ExecutablePath + ".config");
XmlNodeList nodeList = xd.GetElementsByTagName("connectionStrings");
foreach (XmlNode node in nodeList)
{
XmlNode node2 = node.ChildNodes[0];
if (node2.Attributes[0].Value.EndsWith("ConnectionString"))
connectionString = node2.Attributes[1].Value;
}
return connectionString;
}
Also it was proven that even though the methods provided a fast and easy way to use the database from C#, often I would still need a more versatile way to access my data. I then built two methods that enabled me to execute SQL code directly on the database.
Finally, the development of lookup methods that take a Guid with the record's primary key as an argument meant that I would have to find some way to retrieve the name of the primary key field of a given table. The method is as follows:
public static string GetKeyField(string tableName)
{
return ReadValue("(SELECT c.name AS COLUMN_NAME " +
"FROM sys.key_constraints AS k INNER JOIN " +
"sys.tables AS t ON t.object_id = k.parent_object_id INNER JOIN " +
"sys.index_columns AS ic ON ic.object_id = t.object_id AND " +
"ic.index_id = k.unique_index_id INNER JOIN " +
"sys.columns AS c ON c.object_id = t.object_id AND " +
"c.column_id = ic.column_id " +
"WHERE (t.name = N'" + tableName + "'))", "COLUMN_NAME").ToString();
}
I cannot take credit for the SQL code that retrieves the key from the database since I found it somewhere online. If someone can recognize the code, please e-mail me the source, so that I can acknowledge it properly.
History
- 30th December, 2007: Initial version
I look forward to everybody's comments.
