CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Those of you who know what lies behind frameworks such as ‘Entity Framework’ and ‘N-Hibernate’ will undoubtedly be going ‘Why’? For those of you who don’t, well… stay tuned, this might get just a little bit interesting.
With all the ORMs floating about today, the modern day .NET developer is spoiled for choice.
You have EF, NHibernate, Massive, Simple.Data and about a zillion others, and don’t get me wrong, it’s great, never has it been so easy to deal with a database at object level.
For all you line of business app guys out there, this is pretty much all you need to know.
Object goes in, object comes out; pretty simple right?
However, on more than one occasion I’ve seen developers getting really upset, because their ORM is slow, or too limiting, and I’ll be one of the first to actually sit here and sympathize with you, why?
Well if any of you know me at all, you’ll know I do a *LOT* of GIS based work.
Not just your hey let’s grab a location and plot a Google map of it on a site type work, but real in depth stuff like terrain analysis, behavioural tracking and a ton of other stuff.
For most of this work I quite often have to employ the use of PostgreSQL as a database server.
Postgres, is a fantastic bit of software (If you’ve never tried it I absolutely recommend you do), but because it’s not .NET or designed for .NET it can be a bit of a challenge to get it working.
Currently there is a build of ‘Npgsql’ that allows you to use Postgres with Entity Framework, but in my honest opinion, it still needs a lot of work done on it, before it’s ready.
Because my applications often need to do things that are either difficult or not at all possible for a lot of ORMs, I very often have to resort to using plain old ADO.NET code, and that’s what I’m about to show you here.
Underneath all of these various ORMs available, is still the same generic ADO.NET runtime, as we will be using. What most of these ORMs give us, are things like change tracking, repository patterns, unit of work patterns. And they do things like use reflection on our objects, and map the property names to table columns automatically.
All of this changes when you get down to the ADO level, because you now have to implement all of this yourself.
You might however be quite surprised at just how simple all of this actually is.
Step1 – Connecting
The first thing you need is a connection string. Most of you will be familiar with defining these in your web.config or app.config files, and if that’s what you want to do you still can. There is however, another way, using a connection string builder. You can create a connection string builder as follows:
DbConnectionStringBuilder csb = new DbConnectionStringBuilder();
Once you’ve done this, you can then populate your string using the various properties available:
csb.Add("Host", "MyServer");
csb.Add("Database", "TheDatabase");
As you can see, this is not really an intuitive way of doing it, which is why you often don’t use the generic ‘DbXXXXX‘ methods that are available; instead you install an adapter layer such as ‘NpgSql‘ that I mentioned before, or things like the official ‘Sql Server’ assemblies.
Once you add those, you then get to use something similar to the following (This is for Postgres):
NpgSqlConnectionStringBuilder csb = new NpgSqlConnectionStringBuilder() csb.Database = "TheDatabase"; csb.Host = "MyServer";
Exactly what nodes are available, depends entirely on the adapter used, but many are the same or follow the same naming pattern. In particular all of the connection string builders will have a ‘ConnectionString‘ property, and that moves us onto our next part.
Once you have a connection string, you then need a connection to the server, and you do this by using the connection object as follows:
using(DbConnection conn = new DbConnection(csb.ConnectionString))
{
conn.Open();
...
conn.Close();
}
Again, I’ve used the generic versions, just to show you the sequence needed, as with the connection string builder, you’d substitute these for your adapter specific version eg: ‘SqlDbConnection‘.
Step 2 – Running a Command
Once you’ve opened your connection, you’ll then want to run SQL commands against it. To do this, you need a ‘DbCommand‘ object, and some SQL for it to run, something similar to the following:
string sql = "select * from mytable";
using(DbCommand cmd = new DbCommand(sql, conn))
{
...
}
Word of warning, the generic version doesn’t actually take any parameters, so again, just like previously you will need to substitute this for your adapter version. If you copy and paste the example above, it will fail to compile unless you modify it.
If you want to run a call to a stored procedure or function, you need to set the command type property on your command from ‘Text’ to ‘Stored Procedure’ then specify the name of the stored proc, in place of the SQL string shown above.
Once you have your command object, you then have a choice of three different execution strategies:
cmd.ExecuteScalar(); cmd.ExecuteNonQuery(); cmd.ExecuteReader();
Execute scalar is used primarily for executing functions, where the result is generated from the first column of the first row of any result set, or in some cases by the return value from a given function or stored procedure.
Execute non Query is typically used to run updates, inserts and DDL based SQL (such as that for creating a table). Basically anything that’s not expected to return a result set should be run using the non query version.
If your query is expected to return data, for example a select query, then you need to use Execute Reader.
Step 3 – Reading the Data
Execute reader returns a data reader object as shown below (again using the generic version as we have done throughout this article):
string sql = "select * from mytable";
using(DbCommand cmd = new DbCommand(sql, conn))
{
using(DBDataReader dr = cmd.ExecuteReader())
{
...
}
}
The properties and methods on your Data Reader can then be used to sequentially get the rows from your database, and pick the values out into your own objects, for example:
string sql = "select * from mytable";
using(DbCommand cmd = new DbCommand(sql, conn))
{
using(DBDataReader dr = cmd.ExecuteReader())
{
while(dr.Read())
{
string Name = (string)dr[0];
int Age = (int)dr[1];
}
}
}
If you’ve named the columns in your query, then you can specify the name of the column directly in the call to the data reader EG: ‘(string)dr[“name”]‘ rather than using the integer index and having to know the result set order.
There are also a number of ways you can make sure the data is in the correct format, from the methods in the ‘System.Convert‘ name space right through to actually using ‘TryParse‘, ‘Parse‘ and other methods, I just used casting for simplicity in the example.
In the event that your query may or may not have rows, you can decide to go into a reading loop by using the boolean result from ‘dr.HasRows’ and a simple if statement:
if(dr.HasRows)
{
... read here ...
}
And that’s pretty much how simple it is, but what if you wish to make a class that’s generic and can be connected to any database adapter?
Well, all of the generic objects shown above, can all use their interface version, so ‘DbConnection’ becomes ‘IDbConnection’, ‘DbCommand’ becomes ‘IDbCommand’ and so on.
Since all database adapter layers inherit from each of these interfaces, using those types in place of an NpgSql or other Db object, will allow you to write a set of simple, customised generic functions, that you can easily just then inject a database adapter driver into using some kind of IoC based pattern.
There’s a lot more that can be accomplished under the hood but the basic pattern shown above will get you 90% of the way. To close of this post, here’s a full example of reading using the official SQL Server layer ‘System.Data.SqlClient‘:
using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = "Data Source=MyServer;Initial Catalog=MyDatabase; Integrated Security=true";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
string sql = "select name, email from users";
using(SqlCommand command = new SqlCommand(sql, connection))
{
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
Console.WriteLine("Name: {0}tEmail: {1}",dr[0], dr[1]);
}
dr.Close();
}
}
}
}
One thing I’ve left out of this is using parameters to insert data. This can easily take a full post itself to do however, so I’ll cover that another time, but please for the sake of security, if you’re using ADO.NET to insert data, do not use plain string concatenation to do so; this will likely lead to lots of SQL injection attacks if you do.
Instead, the DbCommand object has a parameters collection, use that to add your parameters, then mark those parameters up in your SQL string using ‘@param’. I can’t stress this any more importantly, unless you know 100% where the data for your parameters are coming from, using string concatenation is a really, really bad idea.
If there’s anything you’d like to see in this column, you can generally find me on Twitter as @shawty_ds or in the Linked.NET (Lidnug) users group on Linked-In that I help run. Feel free to come along and say hi, and if you think I can improve on anything, let me know. These small articles are for your .NET toolbox, so let me know what tools you need, and I’ll see what I can do.
