SQL Server Database Connection In C# Using ADO.NET

Introduction

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

ADO.NET library defines classes to work with databases in . NET. Let's learn how to use these classes in our C# application.

Background

C# console application is the simplest app to create to test our database connectivity. In this article, I'll create a console application, use ADO.NET SQL data provider classes to connect to a SQL Server database using C#, and access, update, and execute SQL commands using ADO.NET.

You can use the same code in your Windows Forms or WPF application.

Working with Data in the .NET Framework

Database connectivity and functionality are defined in the ADO.NET namespaces of the .NET Framework. ADO.NET comes with several data providers, including SqlClient, OleDB, and ODBC. .NET framework also provides in-memory data access using LINQ. In this article, we will use the SqlClient data provider of ADO.NET.

SQL data provider of ADO.NET is defined in the System.Data.SqlClient namespace. We'll also use the System. Data and System.Data.Sql namespace that provides additional database functionality.

If you're new to ADO.NET, here is a detailed article, What is ADO.NET.

System.Data.SqlClient

This assembly (namespace) of .NET Framework contains all of the classes required to connect to a SQL Server database and read, write, and update. The namespace provides classes to create a database connection, adapters, and SQL commands that provide the functionality to execute SQL queries. The SqlError class is used for error, and the success report is returned after query execution.

In this article, we will work with the SQL Server database only.

Connecting to a database

Connection to a database requires a connection string. This string has 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, including the server name and type.

The sqlConnection class represents a database connection. The following code creates a SqlConnection object by passing a SQL Server connection string.

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

In this connection string

Server: Name of the server to connect to.
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 use the username and word in order to connect to the database.

In my case, the authentication was Windows, so I needed 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.

Learn How to Generate Connection Strings using Visual Studio

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, it 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.  
}

Learn more about Connection Pooling in ADO.NET.

Why use "using" in code

In C#, there are some objects that use the resources of the system. That needs to be removed, closed, flushed, disposed of, and so on. In C#, you can either write the code to create a new instance of the resource, use it, close it, flush it, and dispose of it. On the other hand, you can simply just use this using a 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.

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

Once the code steps out of this block, the resources would be closed and disposed of on their own. The framework would be taken care of 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 a 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 put any command into it, and the underlying code will 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

Parameterizing the query is done using the SqlParameter ed into the command. For example, you might want to search for the records where the criteria match. You can denote that criterion 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 at 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 a 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 that 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 will 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.

SqlError
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 use 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 until 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 an 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 will 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 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 a 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 to dispose of 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 separate 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.

Note

The tables and the database schema provided here is associated 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.