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 (mostSELECT
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 (typicallySELECT
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.