How to Connect to a Database in .NET Framework

This article describes the basic code and namespaces required to connect to a database and how to execute a set of commands on the database using the C# code and your application.

Background

I have always had trouble learning how to connect to a database using C#. Since I was an ASP.NET developer, using C# to create a software connecting to the database objects was always a problem and I thought it was a hard thing to do.

I have now learned about it and so I want to help new developers to learn how to connect their applications to a SQL Server database using the C# code in their applications.

I will be adding the code for a console application, since console applications are the simplest applications on .NET Frameworks, WPF and Windows Forms that can also be used but a console would be a good fit for explaining the basic implementation of the code in this scenario.

Working with Data in .NET Framework

The .NET Framework always provides the basic implementation of working with data and the basic functions that you can use in your code. Data can be extracted from some files, whether it be data in your application, from a database software and so on. In .NET you can work with them, using the namespaces provided by the .NET Framework.

OLEDB, SQL, LINQ are the basic examples of such types of software. In SQL you connect to the databases and in this namespace we're going to move on to a next step to talk about the databases and C# code.

However, the namespace we're going to use is System.Data.SqlClient, not System.Data.Sql, although the second namespace also works with SQL related functions, but SqlClient is the one namespace we're looking for in this scenario to continue the progress.

System.Data.SqlClient

This assembly (namespace) of .NET Framework contains all of the classes required to connect to the databases, read/write data to the databases.

Errors and Success reports are generated by it. SqlError is generated for errors and the success codes are executed and shown on the screen.

We will be using this namespace directly and the classes contained by it in our code, to connect to the database. To execute the commands to read the data from it, or to update the records or to add new ones as a whole.

Working with SQL Server using C#

In this example I will be using SQL Server, since I only have SQL Server installed, so I am not sure whether this would work with MySQL and Oracle and so on. I will however add that content to this article soon.

Connecting to a database

Connection to a database requires a connection string. This string has the information about the server you're going to connect to, the database you will require and the credentials that you can use to connect. Each database has its own properties, its own server, name and type of login information, using which you can connect to the database to read/write the data from it.

You can learn more about Connection Strings from the Wikipedia, as they say.

Quote

In computing, a connection string is a string that specifies information about a data source and the means of connecting to it. It is ed in code to an underlying driver or provider in order to initiate the connection. Whilst commonly used for a database connection, the data source could also be a spreadsheet or text file.

From this preceding statement one can easily conclude that the connection string is a basic information about the data source, data and the method to connect to it.

The Connection Strings Reference is the website where you can easily find the connection string for your database. They provide the strings, for nearly all of the database services and their types. Do try them! In the code I am providing, the string was caught from their website for testing my own database too.

In the namespace I have talked about, the SqlConnection class does the job for us. We can use the following code to connect to the SQL Database:

using(SqlConnection conn = new SqlConnection()) {
    conn.ConnectionString = "Server=[server_name];Database=[database_name];Trusted_Connection=true";
    // using the code here...
}

This would create a new connection to the SQL Server database that will be connected using the ConnectionString provided. You need not have SQL Server installed on your system. We've already said that a connectionString is just a string to specify the underlying code about the location and the schema of the data software provider. So we can even have the SQL Server connection established that is on a seperate developmen environment. The preceding connection string does.

1. Server

This part of the string specifies the underlying code, the name of the server to connect to. Your server's name would differ in this manner.

2. Database

This is the name of the database you're connecting to.

In all of the databases, there are two types of login methods. Windows Authentication and Database Authentication. In Windows Authentication, the database is authenticated using the user's credentials from Windows (the OS) and in Database Authentication you the username and word, in order to connect to the database.

In my case, the authentication was Windows, so I need to write the Trusted_Connection part inside the string. If you're using the database authentication then you will provide the username and word fields in the string.

Connection pools

Connecting to a database, as already said, is a long process of opening the connection, closing the connection and so on. To repeat this process for every single user in the application is not a good approach and will slow down the processes of code execution. So, in program executions many such connections would be opened and closed and again opened that are identical. These processes are time consuming and are the opposite of a good UX.

In the .NET Framework ADO.NET plays a part in this and minimizes the opening and closing process to make the program execution a bit faster by creating, what we call, a Connection Pool. This technique reduces the number of times the connection is opened, by saving the instance of the connection. For every new connection it just looks for a connection already opened and then if the connection exists, doesn't attempt to create a new connection, otherwise it opens a new connection based on the connection string.

It must be remembered that only the connections with the same configuration can be pooled. Any connection with even a single dissimilarity would require a new pool for itself. Generally, it is based on the ConnectionString of the connection. You can learn how that would differ by changing the values in the connection string.

An example from the MSDN documentation would be like:

using(SqlConnection connection = new SqlConnection(
    "Integrated Security=SSPI;Initial Catalog=Northwind")) {
    connection.Open();
    // Pool A is created.
}
using(SqlConnection connection = new SqlConnection(
    "Integrated Security=SSPI;Initial Catalog=pubs")) {
    connection.Open();
    // Pool B is created because the connection strings differ.
}
using(SqlConnection connection = new SqlConnection(
    "Integrated Security=SSPI;Initial Catalog=Northwind")) {
    connection.Open();
    // The connection string matches pool A.
}

MSDN documentation about connection pools you can learn more on this topic from the MSDN documentation about Connection Pooling in SQL Server.

Why use "using" in code

In C# there are some objects that use the resources of the system. That need to be removed, closed, flushed and disposed of and so on. In C# you can either write the code to create a new instance to the resource, use it, close it, flush it and dispose of it. Or on the other hand you can simply just use this using statement block in which the object created is closed, flushed and disposed of and the resources are then allowed to be used again by other processes. This ensures that the framework would take the best measures for each process.

We could have done it using the simple line to line code like:

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "connection_string";
conn.Open();
// use the connection here
 conn.Close();
conn.Dipose();
// remember, there is no method to flush a connection.

Document about SqlConnection on MSDN

This was minimized as:

using(SqlConnection conn = new SqlConnection()) {
    conn.ConnectionString = "connection_string";
    conn.Open();
 // use the connection here
}

Once the code would step out of this block. The resources would be closed and disposed of on their own. The framework would take care in the best way.

Executing the Commands

Once connected to the database, you can execute the set of commands that you're having and which would execute on the server (or the data provider) to execute the function you're trying to do, such as query for data, insert the data, update records and so on and so forth.

SQL has the basic syntax for the commands and in my opinion has the simple syntax of commands and nearly human-understandable commands in the programming world. In the namespace the class, SqlCommand does this job for us. An example of a command would be like:

SqlCommand command = new SqlCommand("SELECT * FROM TableName", conn);

Each and every command on the SQL Server would be executed like this. The first parameter is the command and the second one is the connection on which the command would execute. You can any command into it and the underlying code would convert it back to the command that would execute on the server where the data is present and then will return the result to you, whether an error or a success report.

Sometimes you might want to use the command of the INSERT INTO clause. To work that out, you will need to add parameters to the command, so that your database is safe from SQL Injections. Using parameters would reduce the chances of your database being attacked, by throwing an error if the user tries to add some commands (using the form) into the database server.

Parameterizing the data

Parameterzining the query is done using the SqlParameter ed into the command. For example, you might want to search for the records where a criteria matches. You can denote that criteria, by ing the variable name into the query and then adding the value to it using the SqlParameter object. For instance, the following is your SqlCommand to be ed on to the server:

// Create the command
SqlCommand command = new SqlCommand("SELECT * FROM TableName WHERE FirstColumn = @0", conn);
// Add the parameters.
command.Parameters.Add(new SqlParameter("0", 1));

In the preceding code, the variable added is 0 and the value to it is ed. You can use any variable but it must start with a @ sign. Once that has been done, you can then add the parameters to that name. In this case, the value 1 has been hardcoded and you can add a variable value here too.

Remember, the connection must be opened in order to run this code, you can use conn.Open() to open the connection if asked.

As explained in the code, I have used the parameter as a number (0) that can also be a name. For example, you can also write the code as:

// Create the command
SqlCommand command = new SqlCommand("SELECT * FROM TableName WHERE FirstColumn = @firstColumnValue", conn);
// Add the parameters.
command.Parameters.Add(new SqlParameter("firstColumnValue", 1));

This way, it will be easier for you to keep them in mind. I am better in working with numbers and indexes like in an array so I used 0, you can use a name, a combination of alphanumeric characters and so on. Just the name in the SqlParameter object and you'll be good to go!

Reading the data returned

In SQL you usually use the SELECT statement to get the data from the database to show, CodeProject would do so to show the recent articles from their database, Google would do so to index the results and so on. But how to show those data results in the application using C#? That is the question here. Well, in the namespace we're talking about, there is the class SqlDataReader present for the SqlCommand that returns the Reader object for the data. You can use this to read through the data and for each of the columns provide the results on the screen.

The following code would get the results from the command once executed:

/ Create new SqlDataReader object and read data from the command.
using (SqlDataReader reader = command.ExecuteReader())
{
while there is another record present
while (reader.Read()) {
    // write the data on to the screen
    Console.WriteLine(String.Format("{0} \t | {1} \t | {2} \t | {3}",
    // call the objects from their index
    reader[0], reader[1], reader[2], reader[3]));
}
}

This is the same code, it will execute and once done executing it will let the framework handle the job and close the resources depending on the best method.

Adding data to the SQL Server

A similar method is implemented for adding the data to the database. Only the command would change and we know in the database, the INSERT INTO clause is used to add the data. So, the command would become:

SqlCommand insertCommand = new SqlCommand("INSERT INTO TableName (FirstColumn, SecondColumn, ThirdColumn, ForthColumn) VALUES (@0, @1, @2, @3)", conn);

You can then use the SqlParameter objects to add the values to the parameters. This way, when the command is executed the data would be added to the table that you've specified.

Catching the errors from SQL Server

SQL Server generates the errors for you to catch and work on them. In the namespace we're working on there are two classes that work with the errors and exceptions thrown by SQL Server.

1.SqlError

2. SqlException

These are used to get the error details or to catch the exceptions in the code and print the data results respectively. If you're going to use a try catch block you're more likely to use the SqlException thing in your code.

For this to work, we will a command that we know will throw an error.

SqlCommand errorCommand = new SqlCommand("SELECT * FROM someErrorColumn", conn);

Now we know that this is faulty, but this won't generate any error, untill we execute it. To do so, we will try to execute it like this:

errorCommand.ExecuteNonQuery();

Once this is executed, SQL Server would complain, saying there is no such table present. To catch it you can simply use the try catch block with the SqlException in the catch block to be caught. For a working code, you can see the following code block in the live example of my article. That explains the usage of the try catch block with the SqlException here.

Working example

In the article there is an associated example for you to download if you want to work it out. You must use a SQL Server, database and the relevant tables to ensure the program works. If the server name does not match the database name or the tables then the program won't run. There was no way for me to attach a database in the example. Since the databases require a SQL Server database that will be always available using a connection, I won't use this database again, so I have not provided the database connection string.

Database table

The database table in my system was like the following:

SELECT without WHERE

You can run the console and you'll see the results on your screen. This is the code where the SELECT query ran and the output of the columns returned was printed.

SELECT with a WHERE

We can at the same time add a few more parameters to the SELECT query, so that only the data we want would be extracted from the database. For example, if we add a WHERE clause to the SELECT query, the following result would be generated.

 

Inserting the data

Once done, you can move on to the next stage. This part is related to the second command, where we add the records to the table. This statement, when executed, would add the data to the table. The table is now like this:

In this example, what happens is that the preceding code is used in our application. I will also provide the code used in this console application. Comments have been added with each block so you understand the how the code works.

Source code

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqlTest_CSharp {
    class Program {
        static void Main(string[] args) {
            // Create the connection to the resource!
            // This is the connection, that is established and
            // will be available throughout this block.
            using(SqlConnection conn = new SqlConnection()) {
                // Create the connectionString
                // Trusted_Connection is used to denote the connection uses Windows Authentication
                conn.ConnectionString = "Server=[server_name];Database=[database_name];Trusted_Connection=true";
                conn.Open();
                // Create the command
                SqlCommand command = new SqlCommand("SELECT * FROM TableName WHERE FirstColumn = @0", conn);
                // Add the parameters.
                command.Parameters.Add(new SqlParameter("0", 1));

                /* Get the rows and display on the screen!
                 * This section of the code has the basic code
                 * that will display the content from the Database Table
                 * on the screen using an SqlDataReader. */

                using(SqlDataReader reader = command.ExecuteReader()) {
                    Console.WriteLine("FirstColumn\tSecond Column\t\tThird Column\t\tForth Column\t");
                    while (reader.Read()) {
                        Console.WriteLine(String.Format("{0} \t | {1} \t | {2} \t | {3}",
                        reader[0], reader[1], reader[2], reader[3]));
                    }
                }
                Console.WriteLine("Data displayed! Now press enter to move to the next section!");
                Console.ReadLine();
                Console.Clear();

                /* Above code was used to display the data from the Database table!
                 * This following section explains the key features to use
                 * to add the data to the table. This is an example of another
                 * SQL Command (INSERT INTO), this will teach the usage of parameters and connection.*/

                Console.WriteLine("INSERT INTO command");

                // Create the command, to insert the data into the Table!
                // this is a simple INSERT INTO command!

                SqlCommand insertCommand = new SqlCommand("INSERT INTO TableName (FirstColumn, SecondColumn, ThirdColumn, ForthColumn) VALUES (@0, @1, @2, @3)", conn);

                // In the command, there are some parameters denoted by @, you can
                // change their value on a condition, in my code they're hardcoded.

                insertCommand.Parameters.Add(new SqlParameter("0", 10));
                insertCommand.Parameters.Add(new SqlParameter("1", "Test Column"));
                insertCommand.Parameters.Add(new SqlParameter("2", DateTime.Now));
                insertCommand.Parameters.Add(new SqlParameter("3", false));

                // Execute the command and print the values of the columns affected through
                // the command executed.

                Console.WriteLine("Commands executed! Total rows affected are " + insertCommand.ExecuteNonQuery());
                Console.WriteLine("Done! Press enter to move to the next step");
                Console.ReadLine();
                Console.Clear();

                /* In this section there is an example of the Exception case
                 * Thrown by the SQL Server, that is provided by SqlException
                 * Using that class object, we can get the error thrown by SQL Server.
                 * In my code, I am simply displaying the error! */
                Console.WriteLine("Now the error trial!");

                // try block
                try {
                    // Create the command to execute! With the wrong name of the table (Depends on your Database tables)
                    SqlCommand errorCommand = new SqlCommand("SELECT * FROM someErrorColumn", conn);
                    // Execute the command, here the error will pop up!
                    // But since we're catching the code block's errors, it will be displayed inside the console.
                    errorCommand.ExecuteNonQuery();
                }
                // catch block
                catch (SqlException er) {
                    // Since there is no such column as someErrorColumn (Depends on your Database tables)
                    // SQL Server will throw an error.
                    Console.WriteLine("There was an error reported by SQL Server, " + er.Message);
                }
            }
            // Final step, close the resources flush dispose them. ReadLine to prevent the console from closing.
            Console.ReadLine();
        }
    }
}

The preceding code is the source code used in this application to work.

Points of Interest

SQL Server does not require that you install the SQL Server on your machine. You can connect to an instance of SQL Server that is present in a seperate environment, but you need to ensure the connection has been established and that you can connect to the server that would provide the data. Databases can be present in multiple locations, the only thing necessary for connecting to them would be the correct connection string.

The Connection String must be accurate so that the server can provide the exact data.

Important point:

The tables and the database schema provided here is assosiated with the database and tables I had. The results on your screen might (surely) differ. Results, errors, thrown here might not be the same as on your system. This is just an example! And depends on the database tables and their data and properties.