Category Archives: Software development

How to learn and polyglot vs. specialist

Welcome (back) to my blog. This weeks blog is going to be a little different than my other blogs. Unfortunately I’m a little short on time this week, too short to write up a small web page using some cool technology and write about it too. However, I still want to deliver you the blog you may have been waiting for.
If you read my blog every week, or at least regularly, please drop a line in the comments and let me know, I greatly appreciate it!
I also Twitter other articles that you might like @sanderrossel, so be sure to follow me if you haven’t done so already.

So what do I have in store for you this week? Well I recently got a question from someone who asked me what’s the best way to learn a new technology. I’ll answer that question at the end of this article, but first I’ll tell you why I think you need to learn new technologies from time to time.

Specialized vs. polyglot

One of the reasons I want to write about why you need to learn and how to learn effectively is because lately I’ve been in a few discussions with people about being a specialized programmer versus being a polyglot, or all-round programmer.

A few months ago I switched jobs. One of the reasons for me was that my previous employer wanted everyone to specialize. My specialization would be C# WinForms and Entity Framework, while other colleagues specialized in SQL Server. Sounds nice, right? Except that, next to a C# certificate, I also have a SQL Server certificate that wasn’t going to see any use in that setup… Besides, I had seen quite a bit in C# WinForms and I wanted to move on.
So I applied for another job at another company. Their motto is “we specialize in being all-round.” Put differently, everyone does everything. At one time you may be working with C# and SQL Server, while at another time you may be working with Java and Oracle. Now that’s something I could get into!

Let’s first define these terms, specialized and all-round, or polyglot, and see why you would want to be the one or the other.

I don’t think many people will disagree with me when I say a specialist is someone who has gathered a large amount of knowledge and experience in a specific field. The more specific this field the more specialized you are. Your expertise may be C#, which is a little broad, hardly an expertise actually. C# WinForms with Entity Framework, my specialization, is more specific. You could be as specific as specializing in a single Control, for example the C# WinForms DataGridView. Now that’s a specialization.
I recently read the book Soft Skills by John Sonmez from Simple Programmer. It’s a great book that I can really recommend. John talks in length about why you really need to specialize. In short, if someone has a very specific problem and you specialize in that specific field you’re worth a lot to that someone. The more you specialize the smaller the chances you’ll find someone with the exact problem you can solve, but that also means that the people who have such a specific problem will have trouble finding someone to fix it for them. Therefore, once these people find you (or you find them), and you are one of the few who can really solve their problem, they are going to want you, no matter the costs (to some degree).

So how about all-round, or polyglot. You may have heard about the polyglot programmer and how amazing they are. But what’s a polyglot anyway? The dictionary describes a polyglot as someone who speaks several languages. So a polyglot programmer is someone who knows his way around multiple programming languages. Let’s extend that a bit and say a polyglot programmer also knows multiple paradigms (Object Oriented Programming, Functional Programming,  Aspect Oriented Programming…) and probably lots of libraries too, because different languages require different libraries.
The good thing about being a polyglot is that each new language or technology is easier to learn. Is this a managed language? You’ve seen it before. Is it unmanaged? You’ve seen it before. C or BASIC syntax? You’ve seen it all before! So, in theory, you could work for just about anyone. Except that you’re probably not very good at all of these languages, so why would someone hire you over someone who has the exact knowledge they need? But when you think about it, how often do clients know exactly what they want or need.
On the other hand, once systems become real complex and need different languages, databases and paradigms in different parts of the system, well, you’re good to go.

It seems the world is pretty divided on which is best. As said, John Sonmez really urges you to take on a specialization, and it could really work out for you. But if you Google for “polyglot programmer” you’ll find websitesblogs and videos dedicated to that subject as well (both for and against)…

Why not both?

So here’s my view on both specialized and polyglot programming. They’re both great and everyone’s right. I’m usually not that politically correct, so let me explain.

Expertise is great, right? When you’re doing any kind of serious development you’re going to run into issues that can only be properly solved if you really know your stuff. For example, in WinForms you may need to create your own user control. That’s really specialized work, you really need to know the design time implications associated with creating controls. As coincidence would have it just last week I inherited a Form with a DataGridView on it. Upon setting some property I populated the DataGridView with columns. What happened at design time? Everytime I did a build or I opened the WinForms designer it would re-populated the DataGridView, but didn’t actually throw away the old columns! Before I knew it I had over 100 columns on my Form! So apparently I’m no specialist in WinForms Control development. Luckily I was knowledgeable enough to recognize the problem and fix it right away. How did I get that knowledge? You may not like the answer, but I’ve been building non-trivial WinForms apps for over four years and I’ve been reading up on books and blogs dedicated to WinForms development. Could I have solved my problem without knowing what I know? Sure, just duplicate a lot of code in every Form I have so I don’t need Form inheritance. Or maybe make a fix for the bug, which then requires another fix, etc. etc… And I’ve seen these ‘solutions’ lots of time!

So if you need four years to learn any language and/or framework to be able to build non-trivial applications with it there’s not much sense in being a polyglot, right? That’s just going to take ages and by the time you’ve mastered your second language your knowledge on the first will be outdated. Well, here’s the trick, you don’t have to be that sufficient in any language. I get my daily work done in either Visual Basic.NET or C# with WinForms, and more recently ASP.NET MVC, Entity Framework and SQL Server, and I’m pretty good at all of them. You could say that’s my specialization and it comes kind of natural as I’m working with them every day from 9 to 5. Once in a while I may need to read up on some stuff at home, and you need to keep challenging yourself, but overall, when you’re working with a technology that much you tend to get a little good at it.
To become truly specialized, an authority in your line of work, you’d need to put a lot of free time in it as well. But what if you spend that free time on learning other technologies? Well now we’re going a little polyglot while also being a little specialized! And here’s the good part, being a polyglot will make you a better specialist!

Wait, what? That sounded pretty contradicting. Let me put it this way. When all you’ve ever known is C# are you truly aware of its strengths and weaknesses?
I’m currently learning Haskell, a functional language, for my university study. When I saw the Haskell implementation of the quick sort algorithm that made me question all I ever knew about C# and object oriented programming. You may be familiar with the C# implementation, or you could maybe guess what it looks like. I can’t post it here, because it’s simply to big, but a quick Google search will give you lots of different implementations, like Iterative Quick Sort (haven’t checked if it’s actually the best out there). Beware that a lot of what you’ll find is recursive, which will give you a StackOverflowException in C# for large lists.
Now here is the Haskell implementation.

qsort []      = []
qsort (x: xs) = qsort smaller ++ [x] ++ qsort bigger
                   where
                      smaller = [a | a <- xs, a <= x]
                      bigger = [a | a <- xs, a > x]

You don’t actually have to understand it, but the takeaway is that this Haskell implementation is so much more concise, and ultimately readable, than any C# implementation you can come up with.
But there’s more. Are you using .NET LINQ? Well, where did you think that came from? Yes, the functional programming paradigm! Functional languages are also pretty good at multi-threading because values are immutable.
Does that make you want to throw C# out of the window and do everything in Haskell (or, since you’re a .NET dev, F#, or Scala if you’re a Java dev) from now on? No, and neither should you. But you should see what’s out there and if there’s some language or technology that solves your problem better than the technology you’re (ab)using now. Even if there isn’t you’ll have gained new insights that might help you on your current job. Just by taking a more functional approach in your object oriented language can help you in writing better and more readable code.

How about another example? So as said I do quite a lot of work in SQL Server. I have plenty of experience in developing databases (I even got certified) and some experience in maintaining them (I’m what they call an accidental DBA). SQL Server was just that one tool that could solve all my problems. And then I spend just a few evening with MongoDB (I wrote a blog about that, A first look at NoSQL and MongoDB in particular). NoSQL just blew my mind. All the troubles I had with SQL Server (and SQL in general), that I took for granted, had been solved by other databases. Those few evenings with NoSQL gave me another perspective on SQL and, I dare say, made me a better SQL programmer because I was now much more aware of SQL’s strong and weak spots.

One last example. I started out as a Visual Basic programmer. Many of you now shudder in disgust. I never understood what all the VB hate was about. Sure, a lot of bad VB has been written, especially pre-.NET VB. But I’ve seen equally bad code in C# as well. And nowadays VB does almost everything C# does and vice versa. It’s just a different syntax. Now this may come as a surprise to some, but not all languages use curly braces! VB is one such language, and because I know VB learning another such language may be easier. But there’s more. In VB there’s a thing called “Option Strict”, which is set to “Off” by default. This makes VB a lot more “forgiving” than C#. For example, the following code would run fine (except for the call to MethodThatDoesNotExist() which gives a runtime exception).

Dim s As String = 42
Dim i As Integer = "42"
Dim o As New Object()
o.MethodThatDoesNotExist()

You may not like it, but this taught me a lot about implicit and explicit casting and late and early binding (which helped me get into JavaScript later). And on the subject of late binding, there’s times when it’s pretty handy. In fact, C# introduced the dynamic keyword for it in .NET4.0. Well guess what, as a VB programmer I was way ahead of you! Optional and named parameters, introduced in C# in 2010, came straight from VB. And what about those new cool Exception filters in C# 6.0? VB has had them for years.
Of course the same can be said for C# features that were introduced in VB, like Iterators (yield), but the takeaway is, that even these two very closely related siblings take stuff from each others, and knowing one can make you better at, or more prepared for, the other.

So why not spend a few hours with some new language once in a while? Just get something up and running in a language you’ve heard about, but don’t know yet, use a framework you haven’t used before, or get a taste of some NoSQL database. Make sure you pick one that’s popular as you’ll be able to find lots of documentation.

How to learn

So I may have convinced you that learning new languages, paradigms or frameworks can be fun and that it can actually help you get an edge as a programmer. But how do you learn a new language or framework? I mentioned Soft Skills before, but I am going to do so again. In this book John Sonmez has a ten step plan for learning just about anything as quickly as possible. I know it works, because John’s approach to learning closely resembles my own method. So here’s what to do.

Read up on the topic you’re interested in. Get a feel of what’s important and what you need at the very least to get started. Also get a feel of how big this thing is you’re learning. Is it a few days of work, or will years of study go into this? Don’t spent to long on this though.
Once you have a picture of what it takes to become proficient at whatever it is you’re trying to learn determine what parts you want to do. Set some bigger goal you want to reach and some smaller goals that will take you there.
After that find resources, books, blogs, videos, whatever it takes to get your started. And then filter them so you can get started with reaching your first goal.
For most languages and frameworks my first goal is to set up the environment. Once I’ve done that I need some “Hello, world!” kind of application, so I know I can actually write and run code with it. Usually that’s not too difficult with all the pre-reading I’ve done.
After that just play around a bit, get familiar with the syntax, the libraries, the tools, etc. After that you can do whatever you want. I’d repeat those last few steps, keep setting new, small goals, and just get there.
Don’t spend to much on any tool you won’t be using though. Just get a feel for it and see how you can use certain elements from it in your day to day programming.

 

That’s my approach to learning. John does a much better job at explaining it than I do, so I suggest you buy his book, or order his course 10 Steps to Learn Anything Quickly.
Now John’s 10th step, one I didn’t have, is rather interesting. Teach! Of course I’ve been doing that by writing my articles, but I’ve never thought about it as a part of the learning process. So how can you teach? Well, start a blog! And, as you might’ve guessed, John has a (free) course on that as well. If you’re interested in learning and teaching I suggest you sign up for his three week email course on blogging.

I’ve successfully used this approach (including teaching) while learning web development. Try learning that, “web development”. No way you can pull that off! Web development is such a broad topic you’ll be overwhelmed, discouraged, and, ultimately, you’ll probably fail. Unless you start by learning HTML, then learning CSS, etc. Just take it one step at a time.
And you can see how I did this by reading up on my series on web development. In eight easy lessons you’ll have learned the ‘impossible’ task of “learning web development” and it wasn’t even hard.

I did the same with NoSQL. First I needed to know what NoSQL is. So apparently there’s different ‘flavours’. Then I needed to pick one, so I picked the one that’s closest to SQL (easy starting point!). Then I picked a database, MongoDB was listed as most popular, so that means I can find a lot of stuff about it. Then I needed to know how I can install it, after that run it, then try to insert some data, read it, edit it, read it again, delete, etc. And finally try doing that from C#, first connect, then read, then edit. Step by step. I’ve blogged about the result, A first look at NoSQL and MongoDB in particular.

There you have it. Get started now. Pick up that language you’ve been interested in, but that you never got around to doing. It might benefit you in more ways than you know. Now is as good a time as any. And on that subject, John’s book Soft Skills also talks about how to stop procrastinating and just do what you should be doing (actually the book talks about a lot).
Next week I’ll be back with hopefully a ‘normal’ technical article again. So stay tuned!

Happy coding!

Using DataSets to work with (relational) in-memory data

After a previous blog, Using C# to connect to and query from a SQL database, I got a request to write a bit more about the SqlDataAdapter and DataSets. So here it goes.

Looking at the DataSet

So what exactly is a DataSet? Besides it obviously being a class in the .NET Framework the DataSet represents an in-memory cache of data. This data can be stored in multiple DataTables. DataTables contains DataRows, which are made up of DataColumns. DataTables in a single DataSet can be related to each other by what can best be described as foreign key relations, the DataRelation is used to establish such a relation. Additionally DataTables can contain contraints such as UniqueContraints and ForeignKeyContraints. In case you hadn’t noticed yet, this is the relational model we know from SQL databases!
So yes, you’d think that working with DataSets and SQL kind of go hand-in-hand and it is in fact quite easy to fill a DataSet using the SqlDataAdapter. We’ve seen this in the previous blog post, but let’s quickly take a look at it again.

DataTable table = new DataTable();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(table);
}

Notice that I’ve used a DataTable instead of a DataSet. In this case all I’m getting from the database is a single resultset. If I would’ve used a DataSet the DataAdapter would’ve filled it with one DataTable, the one we have now. So in the next example I’m going to use a DataSet and do something with the data we get.

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(set);
}

foreach (DataTable table in set.Tables)
{
    foreach (DataRow row in table.Rows)
    {
        Console.WriteLine("ID: {0}, Name: {1} {2}.", row["ID"], row["FirstName"], row["LastName"]);
    }
}

Console.ReadKey();

Notice that I loop through the tables of the DataSet and through the Rows of the DataTable. the row["ColumnName"] accesses the value (boxed in an object) of the column in that row. Obviously if I had two tables, the other not being a Person, this code would break.

Accessing multiple tables and schema’s

So in the next example we’re going to load multiple tables into our DataSet. By default, the DataSet creates tables with the name “Table”, “Table1”, “Table2”, etc. If we want to access our tables by name rather than index, like we do with our columns, we’ll have to name them ourselves. You’ll see this in the next example.

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntityrn" +
    "SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(set);
    set.Tables[0].TableName = "BusinessEntity";
    set.Tables[1].TableName = "Person";
}

foreach (DataTable table in set.Tables)
{
    if (table.TableName == "Person")
    {
        foreach (DataRow row in table.Rows)
        {
            Console.WriteLine(String.Format("ID: {0}, Name: {1} {2}.", row["ID"], row["FirstName"], row["LastName"]));
        }
    }
    else if (table.TableName == "BusinessEntity")
    { //... 
    }
}

Console.ReadKey();

And even better would be to not loop through tables at all, but simply access the table you want by name.

DataTable personTable = set.Tables["Person"];
foreach (DataRow row in personTable.Rows)
{ //...
}

As you can see we’re querying for two tables (the first only having the ID column) and the second being our Person. It’s also possible to create your DataSet without having to query for data. This can be done by using the FillSchema method of the DataAdapter. The following will result in the same tables we had in the previous example, but without data.

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntityrn" +
    "SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.FillSchema(set, SchemaType.Source);
    set.Tables[0].TableName = "BusinessEntity";
    set.Tables[1].TableName = "Person";
}

Of course the SqlTableAdapter still has to do a roundtrip to the database to get the schema information. It will send the following query to get the schema (I’m not sure if it’s the same for all versions of .NET and/or SQL Server).

SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT BusinessEntityID AS ID FROM Person.BusinessEntity
SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person
SELECT BusinessEntityID AS ID, AddressID, AddressTypeID FROM Person.BusinessEntityAddress
SELECT AddressID, AddressLine1, AddressLine2, City, PostalCode FROM Person.Address SET FMTONLY OFF; SET NO_BROWSETABLE OFF;

Getting relational

I mentioned that DataSets can also have relations between tables. Unfortunately you’ll have to add those yourselves. Why would you want to do this? Because you can now navigate from a row in one table to child rows in other tables. BusinessEntity and Person are related, one BusinessEntity represents one Person. However, a BusinessEntity can have one or more addresses through the BusinessEntityAddress table which has a relation to Address. Let’s load all those tables into our DataSet, create the appropriate relations and display the names and addresses of our business entities (note that I’m only printing the ID’s and names of people who have at least one address). Remember that in order to create a relation all rows from a child table need a row in their master table (just like in the database). This is especially tricky when you start using filters. For example getting ONLY Persons with FirstName like ‘A%’, but getting ALL addresses will result in addresses without a person (or a child without a master), which prevents you from creating a relation.

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntityrn" +
    "SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Personrn" +
    "SELECT BusinessEntityID AS ID, AddressID, AddressTypeID FROM Person.BusinessEntityAddressrn" +
    "SELECT AddressID, AddressLine1, AddressLine2, City, PostalCode FROM Person.Address", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(set);
    set.Tables[0].TableName = "BusinessEntity";
    set.Tables[1].TableName = "Person";
    set.Tables[2].TableName = "BusinessEntityAddress";
    set.Tables[3].TableName = "Address";

    set.Relations.Add("BusinessEntity_Person", set.Tables["BusinessEntity"].Columns["ID"], set.Tables["Person"].Columns["ID"]);
    set.Relations.Add("BusinessEntity_BusinessEntityAddress", set.Tables["BusinessEntity"].Columns["ID"], set.Tables["BusinessEntityAddress"].Columns["ID"]);
    set.Relations.Add("BusinessEntityAddress_Address", set.Tables["BusinessEntityAddress"].Columns["AddressID"], set.Tables["Address"].Columns["AddressID"]);
}

foreach (DataRow entityRow in set.Tables["Businessentity"].Rows)
{
    foreach (DataRow personRow in entityRow.GetChildRows("BusinessEntity_Person"))
    {
        foreach (DataRow entityAddressRow in entityRow.GetChildRows("BusinessEntity_BusinessEntityAddress"))
        {
            foreach (DataRow addressRow in entityAddressRow.GetChildRows("BusinessEntityAddress_Address"))
            {
                Console.WriteLine("The entity with ID {0} has name {1} {2} and address {3} {4} {5}.",
                    entityRow["ID"], personRow["FirstName"], personRow["LastName"],
                    addressRow["AddressLine1"], addressRow["City"], addressRow["PostalCode"]);
            }
        }
    }
}

Console.ReadKey();

Now that’s quite a bit of code… I’m using a lot of (magical) strings to illustrate how it works. I suggest you create some constants or something. Don’t duplicate strings like that.
So take a good look at that example and let it sink in. You’ve just loaded your database, including relations, in code! That’s pretty neat! But it gets even better!

CRUD operations using DataSets

So we’ve looked at reading data, but what about creating, updating and deleting data? To do this you need to set the Insert, Update and DeleteCommands of your DataAdapter and call the Update method. You can do this manually, simply create three SqlCommands, one with an INSERT statement, one with an UPDATE statement and one with a DELETE statement. You can do with less if you know that certain actions are impossible, for example records from a certain table could never be deleted.
You can also use the SqlCommandBuilder, like I will do in the next example. The SqlCommandBuilder can create the insert, update and delete commands for you based on your (single table!) select query. So make sure you use the same select query in both your actual select and your update routine! The SqlCommandBuilder needs to get the schema of your table and does this using your select query. That also means it needs to make a roundtrip to the database (the same we saw with FillSchema). So when you really need that performance, or if you want to do things ‘right’, set your commands manually.
Another caveat when updating records is that you can only update one table at a time and it will do so row by row. If you update a DataSet and don’t specify a table the Update method will use the first table in the DataSet.
Before updating you can call HasChanges (to prevent going to the database if you have no changes) and GetChanges, which returns a new DataSet containing only changed DataTables and DataRows. When you do you’ll need to explicitly call AcceptChanges on your original DataSet to indicate the changes were successfully saved to the data source. Alternatively you can call RejectChanges to restore the DataSet to its original state (since the last Accept- or RejectChanges was called or since it was created). You can also accept or reject changes on DataTable and even DataRow level.
That’s A LOT of information, let’s look at some code!

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person ORDER BY ID", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(set);
}

set.Tables[0].Rows[0]["FirstName"] = "Sander";

if (set.HasChanges())
{
    DataSet changes = set.GetChanges();
    using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
    using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person ORDER BY ID", connection))
    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
    using (SqlCommandBuilder builder = new SqlCommandBuilder(adapter))
    {
        adapter.Update(changes);
        set.AcceptChanges();
    }
}

You can insert and delete rows in the same manner. Let’s look at inserting a record. In this case we always need to insert a BusinessEntity or we’ll create a Person without a BusinessEntity. This is actually more complicated than it sounds. First, we’ll need all the columns that do not allow NULL values and set values. Second we need to let our DataSet know that there’s a relation between BusinessEntity and Person. We’ll also need to let the DataSet know that the BusinessEntityID is an ID field with an Identity Specification (which means we don’t have to set it ourselves), but our Person needs that same ID (we can use FillSchema for this!). Last we need to issue an update for each table seperately, which means creating seperate SqlCommands, SqlDataAdapters and SqlCommandbuilders…

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntityrn" +
    "SELECT BusinessEntityID AS ID, PersonType, NameStyle, FirstName, MiddleName, LastName, EmailPromotion FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.TableMappings.Add("Person.BusinessEntity", "BusinessEntity");
    adapter.TableMappings.Add("Person.Person", "Person");
    adapter.FillSchema(set, SchemaType.Source);
    adapter.Fill(set);
    set.Tables[0].TableName = "BusinessEntity";
    set.Tables[1].TableName = "Person";
    set.Relations.Add("Relation", set.Tables["Businessentity"].Columns["ID"], set.Tables["Person"].Columns["ID"]);
}

DataTable businessEntityTable = set.Tables["BusinessEntity"];
DataRow newBusinessEntityRow = businessEntityTable.NewRow();
businessEntityTable.Rows.Add(newBusinessEntityRow);

DataTable personTable = set.Tables["Person"];
DataRow newPersonRow = personTable.NewRow();
newPersonRow["PersonType"] = "EM";
newPersonRow["NameStyle"] = 0;
newPersonRow["FirstName"] = "Sander";
newPersonRow["LastName"] = "Rossel";
newPersonRow["EmailPromotion"] = 0;
newPersonRow.SetParentRow(newBusinessEntityRow);
personTable.Rows.Add(newPersonRow);

if (set.HasChanges())
{
    DataSet changes = set.GetChanges();
    using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
    using (SqlCommand businessEntityCmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntity", connection))
    using (SqlDataAdapter businessEntityAdapter = new SqlDataAdapter(businessEntityCmd))
    using (SqlCommandBuilder businessEntityBuilder = new SqlCommandBuilder(businessEntityAdapter))
    using (SqlCommand personCmd = new SqlCommand(
        "SELECT BusinessEntityID AS ID, PersonType, NameStyle, FirstName, MiddleName, LastName, EmailPromotion FROM Person.Person", connection))
    using (SqlDataAdapter personAdapter = new SqlDataAdapter(personCmd))
    using (SqlCommandBuilder personBuilder = new SqlCommandBuilder(personAdapter))
    {
        businessEntityAdapter.Update(changes, "BusinessEntity");
        personAdapter.Update(changes, "Person");
        set.AcceptChanges();
    }
}

We can also delete rows. In this database it’s a bit difficult because everything is related and some entities simply cannot be deleted (only made inactive). But we can, of course, delete our just inserted row. Most of the code above remains the same, except the part where we created the DataRows. I’ll use some LINQ to retrieve the last inserted BusinessEntity (assuming it’s the row we just inserted). Once I got the row I’ll first delete the child rows (Persons) and then I’ll delete the BusinessEntity.
Now here comes the tricky part, in the example above we first needed to insert the BusinessEntity to generate an ID so we could insert the Person. For deletion it’s the other way around! That means that if you would do updates, inserts and deletes all in one you’d get an exception. Either your inserts fail because you insert child rows before their parent rows are inserted or you delete parent rows before their child rows are deleted.
You can use an overload on GetChanges that accepts a DataRowState to create two DataSets, one with updates and inserts and one with deletes and execute them in the correct order.
In the next example I’ll use the DataRowState, but I’m only deleting rows.

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntityrn" +
    "SELECT BusinessEntityID AS ID, PersonType, NameStyle, FirstName, MiddleName, LastName, EmailPromotion FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.TableMappings.Add("Person.BusinessEntity", "BusinessEntity");
    adapter.TableMappings.Add("Person.Person", "Person");
    adapter.FillSchema(set, SchemaType.Source);
    adapter.Fill(set);
    set.Tables[0].TableName = "BusinessEntity";
    set.Tables[1].TableName = "Person";
    set.Relations.Add("Relation", set.Tables["Businessentity"].Columns["ID"], set.Tables["Person"].Columns["ID"]);
}

DataTable table = set.Tables["BusinessEntity"];
List rows = table.Rows.Cast().ToList();
int maxId = table.Rows.Cast().Max(b => b.Field("ID"));
DataRow row = rows.Single(be => be.Field("ID") == maxId);
foreach (DataRow personRow in row.GetChildRows("Relation"))
{
    row.Delete();
}
row.Delete();

if (set.HasChanges())
{
    DataSet changes = set.GetChanges(DataRowState.Deleted);
    using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
    using (SqlCommand businessEntityCmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntity", connection))
    using (SqlDataAdapter businessEntityAdapter = new SqlDataAdapter(businessEntityCmd))
    using (SqlCommandBuilder businessEntityBuilder = new SqlCommandBuilder(businessEntityAdapter))
    using (SqlCommand personCmd = new SqlCommand(
        "SELECT BusinessEntityID AS ID, PersonType, NameStyle, FirstName, MiddleName, LastName, EmailPromotion FROM Person.Person", connection))
    using (SqlDataAdapter personAdapter = new SqlDataAdapter(personCmd))
    using (SqlCommandBuilder personBuilder = new SqlCommandBuilder(personAdapter))
    {
        personAdapter.Update(changes, "Person");
        businessEntityAdapter.Update(changes, "BusinessEntity");
        set.AcceptChanges();
    }
}

And there you have it! No one said working with DataSets was easy… In this post we looked at DataSets and SqlDataAdapters, it is possible to use DataSets with other data sources such as XML though! In fact, DataSets and DataTables are unaware of their data source, you can fill them however you like. Their structure also makes them perfect for binding to DataGridViews in WinForms (and probably WPF too, but I wouldn’t really know).
Their weakness is that they are not strong-typed. We are forced to work with strings to refer to table and column names, and all values contained in them are objects (which means they could be anything).
Microsoft saw this and created so-called Typed DataSets. I recommend you skip Typed DataSets and move straight to LINQ-To-SQL or the Entity Framework. That’s a whole different topic though.

Comments are welcome. Happy coding!

A first look at NoSQL and MongoDB in particular

So today I decided to have a look at NoSQL. It’s not exactly new and actually I’m a bit late to jump on the NoSQL train, but so far I had no need for it (and actually I still don’t, but I had some time to spare and a blog to write). Since NoSQL can be quite complicated, as it imposes a new way of thinking about storing data, and I can’t possibly discuss everything there is to discuss, I’ll add some additional reading at the end of the article.

An overview of NoSQL

First things first, what is NoSQL? As the name implies it’s not SQL (Structured Query Language), a standard for databases to support the relational database model. As SQL has been the standard for about thirty to twenty years I’m not going to discuss it, you probably know it. A common misunderstanding with NoSQL is that it stands for “no SQL”, while it actually means “Not Only SQL”, which implies there is at least some SQL-y goodness to be had in NoSQL as well. Whatever that SQL-y goodness may be it’s not the relational model. And this is where NoSQL is fundamentally different from SQL, expect de-normalized and duplicated data. This ‘feature’ makes it possible to make schema’s flexible though. In NoSQL it’s generally easy to add fields to your database. Where in a SQL database you would possibly lock a table for minutes if it contains a bit of data, in NoSQL you can add fields on the fly (during production!). Querying data can also go faster than your typical SQL database, because of the de-normalization you reduce or even eliminate expensive joins. A downside to this method of storing data is that is it harder to get consistency in your data. Where in SQL consistency is more or less guaranteed if you have normalized your database NoSQL offers consistency or eventual consistency. How NoSQL databases provide this (eventual) consistency differs per vendor, but it doesn’t come as natural as in SQL databases. Also, because of the way data is stored and queried NoSQL databases tend to scale better across machines than SQL databases.
Other than that no uniform definition can be given for NoSQL because there is no standard. Still NoSQL can be roughly divided into four database models (some would say more, let’s not get into such details): Document, Graph, Key-value and Wide Column. So let’s get a quick overview of those and try one out!

The Document Model

First there’s the Document model. When thinking of a document don’t think of a Word or Excel document, think of an object like you would have in an object-oriënted language such as Java or C#. Each document has fields containing a value such as a string, a date, another document or an array of values. The schema of a document is dynamic and as such it’s a breeze to add new fields. Documents can be queried on any field.
Because a value can be another document or array of documents data access is simplified and it reduces or even eliminates the use for joins, like you would need in a relational database. It also means you will need to de-normalize and store redundant data though!
Document model databases can be used in a variety of applications. The model is flexible and documents have rich query capabilities. Additionally the document structure closely resembles objects in modern programming languages.
Some examples of Document databases are MongoDB and CouchDB

The Graph Model

Next there’s the Graph model. This model, like its name implies, stores data in graphs, with nodes, edges and properties to represent the data. A graph is a mathematical structure and I won’t won’t go into it any further. Graph databases model data as networks of relationships between entities. Sounds difficult? I think so too. Anyway, when your application is based on various relationships, such as social networks, the graph database is the way to go.
Some examples of Graph databases are HyperGraphDB and Neo4j.

The Key-value Model

Key-value databases are the simplest of the NoSQL databases. They basically provide a key and a value, where the value can be anything. Data can be queried by key only. Each key can have a different (type of) value. Because of this simplicity these databases tend to be highly performant and scalable, however, because of this simplicity, they’re also not applicable to many applications.
Some examples of Key-value databases are Redis and Riak.

The Wide Column Model

Last is the Wide Column model. Like the Key-value model the Wide Column model consists of a key on which data can be queried, can be highly performant and isn’t for each application. Each key holds a ‘single’ value that can have a variable number of columns. Each column can nest other columns. Columns can be grouped into a family and each column can be part of multiple column families. Like the Object model the schema of a Wide Column store is flexible. Phew, and I though the Graph model was complicated!
Some examples of Wide Column databases are Cassandra and HBase.

Getting started with MongoDB

So anyway, there you have it. I must admit I haven’t actually used any of them, but I’m certainly planning to get into them a bit deeper. And actually, as promised, I’m going to try one out right now! I’ve picked MongoDB, one of the fastest growing databases of the moment. It’s a Document store and so has a wider applicability than the other types. You can download the free version at www.mongodb.org. There’s also a lot of documentation on there, so I recommend you look around a bit later. Installation is pretty straightforward. Just click next a few times and install. If you change any settings I won’t be held responsible if it doesn’t work or if you can’t follow the rest of this post. So go ahead, I’ll wait.
Ready? Once you have installed MongoDB you’ll need to run it. I was a bit surprised it doesn’t run as a service (like, for example, SQL Server) by default.
So how do you start MongoDB? Open up a command window (yes, really). First you need to create the data directory where MongoDB stores its files. The default is data\db, to create it type md data\db in your command window. Next you need to navigate to the folder where you’ve installed MongoDB. For me this was C:\Program Files\MongoDB 2.6 Standard\bin. Then start mongod.exe. If, like me, you’ve never had to work with a command window here’s what you need to type in your command window:

cd C:\
md data\db
cd C:\Program Files\MongoDB 2.6 Standard\bin
mongod.exe

If you still encounter problems or you’re not running Windows you can check this Install MongoDB tutorial. It also explains how to run MongoDB as a service, so recommended reading material there!

You might be wondering if MongoDB has a Management System where we can query and edit data without the need of a programming language. You can use the command window to issue JavaScript commands to your MongoDB database. To do this you’ll need to start mongo.exe through a command window. The Getting Started with MongoDB page explains this in greater detail. However I would HIGHLY RECOMMEND that you download MongoVUE instead. It’s an easy to use, graphical, management system for MongoDB. Do yourself a favour and install it before you read any further. You can check out the data we’ll be inserting and editing in the next paragraphs.

One more thing before we continue. Mongo stores its documents as BSON, which stands for Binary JSON. It’s not really relevant right now, but it’s good to know. We’ll see some classes named Bson*, now you know where it comes from. MongoVUE let’s you see your stored documents in JSON format.

The C# side of MongoDB

So now that we are running MongoDB start up a new C# Console project in Visual Studio. Make sure you have saved your project (just call it MongoDBTest or something). Now open up the Package Manager Console, which can be found in the menu under Tools -> Library Package Manager -> Package Manager Console. Getting MongoDB to work in your project is as simple as entering the following command: PM> Install-Package mongocsharpdriver. The MongoDB drivers will be installed and added to your project automatically. Make sure you import the following namespaces to your file:

using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;
using MongoDB.Driver;
using MongoDB.Driver.Builders;
using MongoDB.Driver.Linq;
using System;
using System.Linq;

So are you ready to write some code? First we’ll need something we want to store in our database, let’s say a Person. I’ve created the following class to work with when we start.

public class Person
{
    public ObjectId Id { get; set; }
    public string Name { get; set; }
}

Classes don’t come easier. Notice I’ve used the ObjectId for the Id field. Using this type for an ID field makes Mongo generate an ID for you. You can use any type as an ID field, but you’ll need to set it to a unique value yourself (or you’ll overwrite the record that already has that ID).  Another gotcha is that you need to call your ID field Id (case-sensitive) or annotate it with the BsonIdAttribute. And since we’re talking about Attributes, here’s another one that’ll come in handy soon, the BsonIgnoreAttribute. Properties with that Attribute won’t be persisted to the store.

public class Person
{
    [BsonId()]
    public ObjectId MyID { get; set; }
    public string Name { get; set; }
    [BsonIgnore()]
    public string NotPersisted { get; set; }
}

For now we’ll work with the default Id field. So now let’s make a connection to our instance and create a database. This is actually rather easy as you’ll see. Mongo creates a database automatically whenever you put some data in it. After we got a connection to our database we’ll want to put some data in that database. More specific, we want to create a Person and store it. To do this we’ll first ask for a collection of Persons with a specific name (a table name, if you like). You can store multiple collections of Persons if you use different names for the collections, so beware for typo’s! After we got a collection from the database we’ll create a Person and save it to the database. That’s a lot of stuff all at once, but actually the code is so simple you’ll get it anyway!

// Connect to the database.
string connectionString = "mongodb://localhost";
MongoClient client = new MongoClient(connectionString);
MongoServer server = client.GetServer();
MongoDatabase database = server.GetDatabase("testdb");

// Store a person.
MongoCollection persons = database.GetCollection("person");
Person p1 = new Person() { Name = "Sander" };
persons.Save(p1);
Console.WriteLine(p1.Id.ToString());
Console.ReadKey();

Wow, that was pretty easy, wasn’t it!? Mongo generated an ID for you, as you can see. Next we’re going to get this Person back from our database. There’s a few ways to do this. We can work using the Mongo API or we can use LINQ. Both present multiple methods of querying for one or multiple records. I suggest you read the documentation and experiment a bit. I’ll already show you a couple of methods to get our Person back from the database.

// Using the MongoDB API.
ObjectId id = p1.Id;
Person sanderById = persons.FindOneById(id);
Person sanderByName = persons.FindOne(Query.EQ(p => p.Name, "Sander"));

// Using LINQ.
var sandersByLinq = from p in persons.AsQueryable()
                    where p.Name == "Sander"
                    select p;
Person sander = sandersByLinq.SingleOrDefault();

You’ll notice the Query.EQ. EQ stands for equal and builds a query that tests if a field is equal to a specific value. There are other query types like GT (Greater Than), LT (Less Than), In, Exists etc.

But wait, I’m not happy with this code at all! What Person really needs are LastName and Age fields. Now here comes this flexible schema I’ve been telling you about. Simply add the properties to your class. If you’ll fetch a Person that doesn’t have these fields specified they’ll be set to a default value. In case of Age you might want to use an int? rather than an int, or your already existing Persons will have an age of 0 rather than null.

Person incompleteSander = persons.FindOne(Query.EQ(p => p.Name, "Sander"));
Console.WriteLine(String.Format("{0}'s last name is {1} and {0}'s age is {2}",
    incompleteSander.Name, incompleteSander.LastName, incompleteSander.Age.ToString()));

incompleteSander.LastName = "Rossel";
incompleteSander.Age = 27;

// Let's save those new values.
persons.Save(incompleteSander);

Console.ReadKey();
// Retrieve the person again, but this time with last name and age.
Person completeSander = persons.FindOne(Query.EQ(p => p.Name, "Sander"));
Console.WriteLine(String.Format("{0}'s last name is {1} and {0}'s age is {2}",
    completeSander.Name, completeSander.LastName, completeSander.Age.ToString()));

Console.ReadKey();

Now let’s also add an address to Person. Address will be a new class and Person will hold a reference to an Address. Now you can just model this like you always would.

public class Person
{
    public ObjectId Id { get; set; }
    public string Name { get; set; }
    public string LastName { get; set; }
    public int? Age { get; set; }
    public Address Address { get; set; }
}

public class Address
{
    public string AddressLine { get; set; }
    public string PostalCode { get; set; }
}

Notice that Address doesn’t need an Id field? That’s because it’s a sub-document of Person, it doesn’t exist without a Person and as such doesn’t need an Id to make it unique. Now fetch your already existing Person from the database, check that it’s address is empty, create an address, save it and fetch it again.

Person addresslessSander = persons.FindOne(Query.EQ(p => p.Name, "Sander"));
if (addresslessSander.Address != null)
{
    Console.WriteLine(String.Format("Sander lives at {0} on postal code {1}", addresslessSander.Address.AddressLine, addresslessSander.Address.PostalCode));
}
else
{
    Console.WriteLine("Sander lives nowhere...");
}

addresslessSander.Address = new Address() { AddressLine = "Somewhere", PostalCode = "1234 AB" };
persons.Save(addresslessSander);

Person addressSander = persons.FindOne(Query.EQ(p => p.Name, "Sander"));
if (addressSander.Address != null)
{
    Console.WriteLine(String.Format("Sander lives at {0} on postal code {1}", addressSander.Address.AddressLine, addressSander.Address.PostalCode));
}
else
{
    Console.WriteLine("Sander lives nowhere...");
}

Console.ReadKey();

Make sure you check out the JSON in MongoVUE. Also try experimenting with Lists of classes. Try adding more Addresses, for example. We haven’t deleted or updated any records either, we’ve only overwritten entire entries. Experiment and read the documentation.

We’ve now scratched the surface of NoSQL and MongoDB in particular. Of course MongoDB has a lot more to offer, but I hope this post has helped getting your feet wet in NoSQL and MongoDB. Perhaps it has given you that little push you needed to get started. It has for me. Expect more NoSQL blogs in the future!

Additional reading

As promised, here’s some additional reading:
NoSQL – Wikipedia
MongoDB White Papers
Document Databases : A look at them
How to take advantage of Redis just adding it to your stack

Comments are welcome. Happy coding!

Using C# to connect to and query from a SQL database

As a developer you’ll probably spend a lot of time getting data in and out of a database. Data is important in any organization and your job as a developer is to present that data to a user, have them add or edit that data and store it back to the database.

Yet I have found that many developers really have no clue how to work with a database! Many developers can get data out of databases, but do so in an unsafe way that may break your code and, worse, give hackers an opportunity to get direct access to your database! Others use an ORM like NHibernate, Entity Framework or LINQ To SQL, but have no idea what’s going on. In this blog post I will address these issues: how to setup a database connection, query for data in a secure manner and use that data in your code. I’ll also show you how to push data back to a database.

I am assuming you know how to set up a database and you know your way around C# and the .NET Framework. For my example I have used the Adventure Works 2014 Sample Database on a SQL Server 2014 database.

So let’s start. To create a connection to a database you’ll first need a database connection object. In our case we need a specific type of connection object, being the SqlConnection. Using the SqlConnection you can configure all kinds of settings that are used for your current session to the database. In this blog we’ll use defaults only. For creating the SqlConnection we’ll use the constructor that takes a (connection)string as input parameter. Usually you’d get the connectiongstring from a config file or some such. Alternatively you can create one using the SqlConnectionStringBuilder, but I won’t go into that here. Notice that I’ve wrapped the SqlConnection in a using block. This ensures that the connection is actually closed once we’re done with it. Make sure you actually open the connection only when needed.

using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
{
    connection.Open();
}

Unfortunately this doesn’t do anything yet. We’ll need a SqlCommand which takes the query we want to send to the database. In this case I’m going to select all persons from the table Person.Person. We can create a command object in different ways, but I’m going to create one using the constructor that takes the query and our just created connection. Once we have created the command we must open the connection (which we already did) and have it execute our query. There are a few ways to have the command actually execute your query.
The first is ExecuteNonQuery, which seems odd because we are going to execute a query, right? Well actually you use this method when you don’t expect a result (perhaps and update statement, or a call to a Stored Procedure that returns no resultset).
The second method, and the one we’ll need in this example, is ExecuteReader. This method returns a SqlDataReader which represents a forward-only stream of rows from the database. The columns of each row can be accessed by index or name. We’ll see how to use the SqlDataReader in the next example.
The third method, and last I will discuss, is ExecuteScalar. You can use this method when you expect exactly one result from a query.
There’s also an ExecuteXmlReader method which I will not discuss here. Additionally every method has its async versions. For older versions of .NET these are the BeginExecute and EndExecute methods and for later versions of .NET these are the ExecuteAsync methods. I will not discuss them here.
So let’s look at our example. We’re going to create a command to fetch some data from the Person.Person table and use ExecuteReader to get our results.

List persons = new List();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person", connection))
{
    connection.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        // Check is the reader has any rows at all before starting to read.
        if (reader.HasRows)
        {
            // Read advances to the next row.
            while (reader.Read())
            {
                Person p = new Person();
                // To avoid unexpected bugs access columns by name.
                p.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                p.FirstName = reader.GetString(reader.GetOrdinal("FirstName"));
                int middleNameIndex = reader.GetOrdinal("MiddleName");
                // If a column is nullable always check for DBNull...
                if (!reader.IsDBNull(middleNameIndex))
                {
                    p.MiddleName = reader.GetString(middleNameIndex);
                }
                p.LastName = reader.GetString(reader.GetOrdinal("LastName"));
                persons.Add(p);
            }
        }
    }
}
// Use persons here...

You may have noticed that getting a value from a SqlDataReader isn’t easy! There are methods like GetString, GetInt32, GetBoolean, etc. to convert values from their database representation to their CLR type equivalents. Unfortunately they throw on DBNull values. So in case of MiddleName, which is a NULLABLE column in the database, we need to check for DBNull before setting the MiddleName value. In case of integer or booleans (or any non-nullable type) we would use the nullable equivalents of those types like int? or bool? (which is short for Nullable<T>).

Another method to get data from the database is by using a SqlDataAdapter. This results in a DataTable or DataSet (for multiple resultsets) containing the database data. I won’t go into the use of DataTables and DataSets, but they are like in-memory GridViews. They even track if a row was changed and can automatically generate update, insert or delete commands when used with a SqlCommandBuilder.
The next code snippet shows how to fill a DataTable (that’s a lot less code than the SqlDataReader example, but keep in mind that the result is also very different).

DataTable table = new DataTable();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(table);
}
// Use table here...

For the next example we are going to select a subset of persons by first name. That means we’ll have to change our query. Let’s look at an example.

string firstName = "John";
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person WHERE FirstName = '" + firstName + "'", connection))
{
    // ...
}

Looking good, right? NO! THIS IS REALLY VERY WRONG! For John this works great (I’ll tell you in a moment why it works, but still isn’t great), but for D’Artagnan (a musketeer) this won’t work at all! While the apostrophe is all good in C# it ends a string in SQL. So the query you’ll be sending to SQL is SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person WHERE FirstName = D'Artgnan. Go to SQL server Management Studio, open a new query window and try to run that exact query. You’ll get an error message saying something about an unclosed quotation mark. What it should’ve been was D”Artagnan. But even replacing every apostrophe with double apostrophe won’t work.

Whenever you send a query to SQL Server a query plan is made and the fastest way to get your data is calculated. For our query SQL Server might decide it will use an index we placed on FirstName. Once the plan is decided it’s cached and re-used when the exact same query is called. In our example that would mean a plan is made and cached for each name we look for! That’s not very efficient since every plan will probably be the same anyway…

What’s even worse and THIS IS VERY IMPORTANT is that by concatenating strings to form a query like that is a HUGE SAFETY RISK! Maybe you’ve heard of SQL Injection Attacks. Let me demonstrate this. Let’s assume for a moment that the user gets a textbox to enter a name and that name is concatenated to your query like above. Now the user enters John'; USE master; DROP DATABASE AdventureWorks2014 -- and BAM! There goes your database… Really, it’s gone. I hope you have a backup. This technique is used to get personal information of users like email addresses and passwords.
Here is a mandatory xkcd on the subject:

xkcd: Exploits of a Mom

So how are we going to solve these problems? Parameterisation! By creating parameterized queries the query plan can be re-used for different values and SQL injection belongs to the past! So how does this look?

string firstName = "John";
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person WHERE FirstName = @FirstName", connection))
{
    cmd.Parameters.AddWithValue("FirstName", firstName);
    connection.Open();
    using (var reader = cmd.ExecuteReader())
    {
        // ...
    }
}

And that’s how easy it is! Notice that by adding a parameter we also improved the readability of our code. Wow, that’s a win-win-win situation!
There is one caveat though, when you want to pass a NULL to the database you’ll have to use the DBNull.Value object instead of simply null. So when fetching data we converted DBNull to null and now we’ll have to convert null to DBNull. We’ll see this happening in the next example.

Now what if we want to update, insert or delete a record in the database? We can go about it in much the same way, but use ExecuteNonQuery (which returns the number of affected rows only).

int businessEntityID = 1;
string firstName = "Sander";
string middleName = null;
string lastName = "Rossel";
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("UPDATE Person.Person SET FirstName = @FirstName, MiddleName = @MiddleName, LastName = @LastName WHERE BusinessEntityID = @BusinessEntityID", connection))
{
    cmd.Parameters.AddWithValue("FirstName", firstName);
    if (middleName == null)
    {
        cmd.Parameters.AddWithValue("MiddleName", DBNull.Value);
    }
    else
    {
        cmd.Parameters.AddWithValue("MiddleName", middleName);
    }
    cmd.Parameters.AddWithValue("LastName", lastName);
    cmd.Parameters.AddWithValue("BusinessEntityID", businessEntityID);
    connection.Open();
    cmd.ExecuteNonQuery();
}

I have to add that it’s generally a good idea to check for null for ALL your parameters. You can make a helper function to prevent your code from cluttering up to much.
And in case you want your original Person back, here are his first-, middle- and last name: Ken J Sánchez.

So far we have only worked with plain text queries. Many times you’ll want to execute a stored procedure. This works in much the same way as sending your query to the database. You simply have to set the CommandType of your command to StoredProcedure and pass in the parameters.

int businessEntityID = 1;
string nationalIDNumber = "295847284";
DateTime birthDate = new DateTime(1987, 11, 8);
char maritalStatus = 'S';
char gender = 'M';
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("HumanResources.uspUpdateEmployeePersonalInfo", connection))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("BusinessEntityID", businessEntityID);
    cmd.Parameters.AddWithValue("NationalIDNumber", nationalIDNumber);
    cmd.Parameters.AddWithValue("BirthDate", birthDate);
    cmd.Parameters.AddWithValue("MaritalStatus", maritalStatus);
    cmd.Parameters.AddWithValue("Gender", gender);
    connection.Open();
    cmd.ExecuteNonQuery();
}

In case you want your original employee back, here is his original birthdate: 1969-01-29.

Perhaps you have noticed that the SqlConnection inherits from DbConnection which implements IDbConnection. We have also used other classes like the SqlCommand and SqlDataReader which inherit from DbCommand and DbDataReader in a same manner. The only thing you need to know right now is that many database providers have these classes as a common base class which means that if you know how to connect to SQL Server you (more or less) know how to connect to most SQL databases like Oracle, MySQL, PostgreSQL, Firebird, etc. In theory (and probably in practice too, although I’ve never tried) you can create a flexible data layer that can switch seamlessly between (SQL) databases because of these common base classes and interfaces.

Well, there you have it. We have successfully and correctly selected data, updated data and executed a stored procedure using C#. I assume you can now guess how to use ExecuteScalar, which I mentioned, but haven’t discussed further. Things don’t stop here though. There’s much more like queries that return multiple result sets, stored procedures that return output parameters, BLOB’s, bulk operations, transactions… Way to much to discuss here. Luckily there are many books, articles and blogs on the subject.

Happy coding!