C# and SQL Server Introduction

SQL Server is a database that can be used in conjunction with C# to create desktop, web and console based applications. It can be used to store data, as well as configuration information. C#, together with SQL, can be used to query the data stored within an SQL Server database, as well as insert, update and delete the data.

Connecting to an SQL Server Database

In order to access data within an SQL Server database with C#, a connection to the database must first be established. To facilitate this, the package 'System.Data.SqlClient' needs to be added to the project. This can be done in a number of different ways, depending on what Integrated Development Environment (IDE) is being used. Visual Studio incorporates NuGet Package Manager, which allows for packages to be searched for and installed. It also has an integrated package manager console, where this package can be added using the following command.

Install-Package System.Data.SqlClient

For IDEs that don't have a built in package manager, PowerShell can be used. The following command can be used to install the above mentioned package. Before running this command it is necessary to navigate to the folder where the project resides. It should be noted that this command will also work in Linux, through a Terminal window.

dotnet add package System.Data.SqlClient

Once added, a 'using' statement for the ‘System.Data.SqlClient’ namespace needs to be included.

// Database variables.
string server = "MSSQLSERVERDEMO";
string database = "Demo";
string username = "DemoUN";
string password = "DemoPW";

// Database connection variable.
SqlConnection connect = new SqlConnection(
    "Server=" + server + ";" +
    "Database=" + database + ";" +
    "User Id=" + username + ";" +
    "Password=" + password);

try
{

    // Connect to database.
    connect.Open();

    // Message confirming successful database connection.
    Console.WriteLine("Database connection successful.");

}
catch (Exception e)
{

    // Message confirming unsuccessful database connection.
    Console.WriteLine("Database connection unsuccessful.");

    // Stop program execution.
    System.Environment.Exit(1);

}

In this example, a ‘try-catch’ block is used to catch any exceptions that may arise in connecting to the database. A ‘try-catch’ block can be used to handle any exceptions in a user friendly manner. The ‘System.Environment.Exit(1)’ statement, after the confirmation of an unsuccessful database connection, stops execution of the program completely, so no further statements are executed.

It should be noted that it isn’t necessary to have the server and database information in separate variables before using it in the database connection. It much depends on personal preference and whether the variables are going to be re-used elsewhere in the program. The database connection variable can be re-written as follows.

// Database connection variable.
SqlConnection connect = new SqlConnection(
    "Server=MSSQLSERVERDEMO; Database=Demo;" +
    "User Id=DemoUN; Password=DemoPW");

Further Resources