A handy little database helper
Each time I sit down to make a prototype of something which requires me to communicate with a database I end up writing the same scaffolding each and every time and the code is never pretty (hey, it’s a prototype it doesn’t have to). However a while back I finally got around to writing a small helper class which enables me to do pretty much anything I want with the database and it’s also provider transparent, thanks to the use of the DbProviderFactories class. Not only that but it makes the database code pretty as well, even in prototypes!
I present to you, the awesomeness that the Database class is
1: using System;
2: using System.Configuration;
3: using System.Data;
4: using System.Data.Common;
5:
6: /// <summary>
7: /// Provides a simple way to perform database operations.
8: /// </summary>
9: public class Database
10: {11: /// <summary>
12: /// Executes a <see cref="IDbCommand"/>.
13: /// </summary>
14: /// <param name="command">The delegate to execute.</param>
15: /// <param name="connectionName">The name of the connection settings to use.</param>
16: public static void Execute(string connectionName, Action<IDbCommand> command)
17: {18: using (IDbConnection connection = GetConnection(connectionName))
19: {20: using (IDbCommand cmd = connection.CreateCommand())
21: {22: command(cmd);
23: }
24: }
25: }
26:
27: /// <summary>
28: /// Creates a connection object for the data store.
29: /// </summary>
30: /// <param name="connectionName">The name of the connection settings to use.</param>
31: /// <returns>A <see cref="IDbConnection"/> object.</returns>
32: private static IDbConnection GetConnection(string connectionName)
33: {34: ConnectionStringSettings settings =
35: ConfigurationManager.ConnectionStrings[connectionName];
36:
37: DbProviderFactory factory =
38: DbProviderFactories.GetFactory(settings.ProviderName);
39:
40: DbConnection connection
41: = factory.CreateConnection();
42: connection.ConnectionString = settings.ConnectionString;
43:
44: return connection;
45: }
46: }
Two static methods and only one of them being public, could it be any easier? Let’s start by examining the private method GetConnection. This method retrieves the database connection settings, from out config file (app.config or web.config it doesn’t matter), using a provided name. It then uses the information to create a new database connection and initializes it before it’s returned.
The config information needed is a couple of lines of code. Below you can see how you’d set it up to setup a SQL sever connection and store the settings with the name default.
1: <?xml version="1.0" encoding="utf-8" ?>
2: <configuration>
3: <connectionStrings>
4: <add
5: name="default"
6: providerName="System.Data.SqlClient"
7: connectionString="...."/>
8: </connectionStrings>
9: </configuration>
Next up is the public function Execute which is where the magic takes place. This method takes two parameters, the name of the connection settings to use, and a delegate to a method which takes one parameter, an object which implements the IDbCommand interface. Internally, the method setup the connection object, creates a new IDbCommand object and executes the delegate and passes in the created command object as a parameter. That’s it.
We can no call this static method and pass in an Anonymous Method where we use the provided command object. To make the code even more enjoyable to the eye we can do this using the lambda expression syntax. Below is a short example of how you could use the database helper class to communicate with a SQL Server and retrieve a datatable of values from a table.
1: /// <summary>
2: /// Gets the orders from the database.
3: /// </summary>
4: /// <returns>
5: /// A <see cref="DataTable"/> containing the orders.
6: /// </returns>
7: private DataTable GetOrders()
8: {9: DataTable orders =
10: new DataTable();
11:
12: Database.Execute("default", command =>13: {14: command.CommandType = CommandType.Text;
15: command.CommandText = "SELECT * FROM Orders";
16:
17: SqlDataAdapter adapter =
18: new SqlDataAdapter((SqlCommand)command);
19: adapter.Fill(orders);
20: });
21:
22: return orders;
23: }
Notice the call to the Execute method on line 12. It’s being passed the connection settings name and an anonymous method where the actual work is being done. Hope you like it, I know I do :-)
The full source code can be downloaded from my codeplex page.
