The Daily Parker

Politics, Weather, Photography, and the Dog

D is for Database

Blogging A to ZWelcome to day 4 of the Blogging A-to-Z challenge. After yesterday's more theoretical post on the CLR, today will have a practical example of how to connect to data sources from C# applications.

Almost every application ever written needs to store data somewhere. If you're deploying a .NET website into Microsoft Azure (like this blog), you will probably connect it to an Azure SQL Database. Naturally, Visual Studio and C# make this pretty easy.

Here's the code that opens up a database connection and prints out to the Trace window what it's opened:

#region Copyright ©2018 Inner Drive Technology

using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;

#endregion

namespace InnerDrive.DailyParkerAtoZ.WeekOne
{
	public static class DataConnections
	{
		private static string ConnectionString => ConfigurationManager.ConnectionStrings["Database"].ConnectionString;

		public static void Connect()
		{
			using (var connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				Trace.WriteLine($"Connected to {connection.DataSource}");
			}
		}
	}
}

Let's take a look at that line by line.

Lines 3-6 tell the compiler that the objects referenced in the executable code come from those four namespaces (which I'll talk more about on April 16th). The SqlConnection class, for example, lives in the System.Data.SqlClient namespace. If I didn't have the using statement on line 5, I'd have to reference the class and its namespace as System.Data.SqlClient.SqlConnection, which is cumbersome.

Line 13 creates a ConnectionString property that gets its value from a configuration setting. More on that below.

Line 17 first sets up a different kind of using statement, which makes sure that whatever the expensive SqlConnection class does while its alive, it gets cleaned up when it finishes on line 21—even if it throws an exception. Then the same line creates a new SqlConnection object and assigns it to the variable connection.

Line 19 attempts to open the connection to the database. If it succeeds, line 20 prints out the name of the data source. If it fails, it throws an exception that whatever method called this one can catch.

The configuration file looks like this (but with a real database, user ID, and password):

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<connectionStrings>
		<add 
			name="Database" 
			connectionString="server={server name}.database.windows.net;initial catalog={database name};persist security info=True;user id={user ID};password={password};"
		/>
	</connectionStrings>
</configuration>

Notice that the name on line 5 corresponds to the configuration setting name on line 13 of the C# code. That's how the code knows which connection string to read from configuration.

Finally, there's also a unit test, which looks like this:

#region Copyright ©2018 Inner Drive Technology

using InnerDrive.DailyParkerAtoZ.WeekOne;
using Microsoft.VisualStudio.TestTools.UnitTesting;

#endregion

namespace InnerDrive.DailyParkerAtoZ.UnitTests
{
	[TestClass]
	public class DataConnectionsTests
	{
		[TestMethod]
		public void CanConnectToDataSource()
		{
			DataConnections.Connect();
		}
	}
}

If the call to DataConnections.Connect() succeeds, the test passes. If the call fails, the test fails and shows the exception that gets thrown.

You can download the code for all of these posts here. You'll have to change the configuration information in the unit test project's app.config file to make it work, of course.

Comments are closed