Lesson 4 - Connected Database Applications in C# .NET

In the last lesson, Approaches to working with relational databases in .NET, we described various approaches to working with databases in object-oriented languages, as well as technologies that are included in the .NET framework for these purposes.

Today in the C# .NET tutorial, we'll show the basics of working with databases using the connected approach.

You can replace each * character that appears before class names with an appropriate data provider.

Getting a ConnectionString

In order to connect to a database, we need a ConnectionString. It's a string that contains data required to connect to a database. Typically, there's a database name and possibly a password.

In Visual Studio there are several ways to get a ConnectionString. The universal way that works in both .NET Framework and .NET Core is to open the "Server Explorer" or "SQL Server Object Explorer" window, right-click on the database and select "Properties". In the "Properties" window, we'll find the ConnectionString, which we can copy somewhere:

Data Source=(localdb)\MSSQLLocalDB; AttachDbFilename= C:\ICTdemy\SQL\VocabularySQL\VocabularySQL\VocabularyDB.mdf;Initial Catalog=VocabularyDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

If you've connected the database via "Data Sources" and checked the option to save the ConnectionString to the project settings, you can access it easily directly in the code via the Settings class. Let's print it to the console:

string connectionString = Properties.Settings.Default.VocabularyDBConnectionString;
Console.WriteLine(connectionString);

The ConnectionString:

Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\VocabularyDB.mdf;Integrated Security=True

AttachDbFilename And InitialCatalog Properties

The InitialCatalog property passes a name of the database we want to connect to. It's used for any database (not only local). The AttachDbFilename property is used to connect to a local database with the .mdf extension. ?A path to a local database file? is passed to this property. A ConnectionString can contain both properties at once or only one of them. In .NET Framework applications, the AttachDbFilename property must always be used when working with local databases.

Preparing Our .NET Core Project

Classes for working with SQL databases are contained in the System.Data.SqlClient namespace. This namespace is already in .NET Framework by default and we don't have to deal with anything. However, .NET Core doesn't include it and it must be installed via the NuGet package.

If we use .NET Core, right-click on the Solution Explorer and then on "Manage NuGet packages for Solution". Here in the Browse tab, select System.Data.SqlClient and install:

SQLConnectionStringBuilder

Each ADO.NET data provider needs slightly different data, so sometimes a connection string is generated using the *ConnectionStringBuilder class. Simply create an instance of this class and assign required values to its properties. The resulting string will be available in the ConnectionString property. If you don't want to deal with compatibility with other providers, you can skip this step and return to it later. In the course, we'll talk a little bit about everything, the purpose isn't to discourage you or give an impression of how complex databases are, but so you know how to use all of it.

Example 1

Using the SQLConnectionStrigBuilder class create a connection string for our training database named VocabularyDB.

The Solution

SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = @"(LocalDB)\MSSQLLocalDB";
csb.InitialCatalog = "VocabularyDB";
csb.IntegratedSecurity = true;
string connectionString = csb.ConnectionString;

Note: Be sure to declare the appropriate namespace (add using System.Data.SqlClient;).

Connection

Once we have the ConnectionString, we can connect to the database. We can do this by creating an instance of the *Connection class, which we'll pass the ConnectionString to in the constructor. We'll then open the connection by calling the Open() method. It's a good practice to create all database objects in a using block to close unnecessary connections quickly. The second approach is to leave the connection open, which is only done for desktop applications and requires a database wrapper.

Example 2

Test a connection to the database using the connection string from the first example. Print to the console whether the connection was successfully opened or not.

The Solution

string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=VocabularyDB;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    Console.WriteLine("The application has connected to the database successfully.");
}
Console.ReadKey();

The output:

Console application
The application has connected to the database successfully.

We'll write the next code under the line connection.Open();.

Database Commands

Database commands are represented by the *Command class. They allow us to run any SQL query over a connected database. To create them, we must know the SQL syntax (I'll try to describe it a bit here as well, however, several courses on ICTdemy are devoted in detail to SQL).

For a new instance of the *Command class, we must define 2 basic properties - Connection (see example 2) and CommandText. Then we can run the command.

The following methods are used to execute it:

• ExecuteReader() - We use this one if the SQL query returns a set of data (most SELECT commands, such as a query for one or more words).
• ExecuteScalar() - The second method finds its application if the SQL query returns a single value (typically SELECT with an aggregate function, such as the number of all words).
• ExecuteNonQuery() - The last method is used to execute queries that don't return any value (INSERT, UPDATE, DELETE, such as inserting a new word). Its return value carries information about the number of "affected" rows.

Example 3

Create a command that gets the number of records in the table Word from the connected database in the second example and prints it to the screen.

The Solution

SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "SELECT COUNT(*) FROM Word";
int numberOfWords = (int)command.ExecuteScalar();  // the method returns the type object - it needs to be converted to int
connection.Close();
Console.WriteLine("The number of words in the database is {0}", numberOfWords);

The output:

Console application
The number of words in the database is 6

The query begins with the word SELECT. SQL clauses are usually capitalized. This is followed by COUNT(*), which means that we're interested in a number. An asterisk indicates that we want to count with respect to all columns. FROM Word indicates that we're selecting data from the table Word. Simple, isn't it? By the way, SQL was formerly called SEQUEL - Structured English Query Language. Each query therefore looks like a simple English sentence.

Some beginners find out the number of records in a table by retrieving all rows even with data and then by counting them (we'll learn to retrieve rows next time). The returned result is the same as the result when using COUNT, but we unnecessarily burdened the database by reading values that we won't use at all. There's a big performance difference between "retrieve one number" or "retrieve a million words just to find out there are a million".

In the next lesson, SqlDataReader and Connected Databases in C# .NET, we'll retrieve some words from our database.