Connect to SQL Server in C# (example using Console application)

In this article, I will provide your working example to create a database connection in C# using console application example or you can say an example, to connect to SQL Server using C#, you can use same C# code in Console application or Windows or ASP.NET Web-Form/MVC, we are using Console application example in this post.

First of all, open your Visual Studio and navigate to File->New->Project -> Select "Windows" from left pane and select "Console app" from the right pane, provide a name to your project and click "OK"

Once the Console Application template is generated by Visual Studio, Navigate to Program.cs from Solution Explorer ( You can open it by Clicking "View"-> "Solution Explorer").

Now to connect to SQL Server, we need to create an instance of SQLConnection and pass a connection string to it.

The SqlConnection Object is used to handle the part of physical communication between the C# application and the SQL Server Database.

An instance of the SqlConnection class in C# has supported the Data Provider for SQL Server Database.

The SqlConnection instance takes Connection String as argument and pass the value to the Constructor statement.

Here is the sample SQL server connection string should look like

connetionString="Data Source=ServerName;
Initial Catalog=DatabaseName;User ID=UserName;Password=Password"

If you have a named instance of SQL Server, you'll need to add that as well.

"Server=localhost\sqlexpress"

When the connection is established, SQL Commands will execute with the help of the Connection Object and retrieve or manipulate the data in the database. Once the Database activities is over , Connection should be closed and release the Data Source resources 

Here is the console application example to connect to local database and open the connection

using System;
using System.Data.SqlClient;

namespace ConnectingToSQLServer
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Getting Connection ...");

            var datasource = @"DESKTOP-PC\SQLEXPRESS";//your server
            var database = "Students"; //your database name
            var username = "sa"; //username of server to connect
            var password = "password"; //password

            //your connection string 
            string connString = @"Data Source=" + datasource + ";Initial Catalog="
                        + database + ";Persist Security Info=True;User ID=" + username + ";Password=" + password;

            //create instanace of database connection
            SqlConnection conn = new SqlConnection(connString);
           

            try
            {
                Console.WriteLine("Openning Connection ...");

                //open connection
                conn.Open();

                Console.WriteLine("Connection successful!");
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e.Message);
            }

            Console.Read();
        }
    }
}

Output 

Getting Connection ...
Openning Connection ...
Connection successful!

Add New Values In Database Table using C#

Once you are connected to database in C# using Console Application, you can also insert new values in database, by creating query as a string and then executing it. Suppose this is our sample database(Students) -> Student_details table data, before inserting values

As you can see from above table, "Id" is Primary Key column and "Identity" is set to true, so we don't need to pass it's value as it will be incremented atuomatically.

We need to pass "Name, Email, Class" column values, so we will have C# code as below

                //create a new SQL Query using StringBuilder
                StringBuilder strBuilder = new StringBuilder();
                strBuilder.Append("INSERT INTO Student_details (Name, Email, Class) VALUES ");
                strBuilder.Append("(N'Harsh', N'harsh@gmail.com', N'Class X'), ");
                strBuilder.Append("(N'Ronak', N'ronak@gmail.com', N'Class X') ");

                string sqlQuery = strBuilder.ToString();
                using (SqlCommand command = new SqlCommand(sqlQuery, conn)) //pass SQL query created above and connection
                {
                    command.ExecuteNonQuery(); //execute the Query
                    Console.WriteLine("Query Executed.");
                }

You will need to add namespace "System.Text" for using StringBuilder.

In the above code, we are creating a SQL Query using StringBuilder object and then passing the SQL Query to SqlCommand instace and execute it.

Once the command is executed, your database table will addded 2 rows and table looks like this

Similarly, to Update the values in database you can build SQL Query and execute it.

                strBuilder.Clear(); // clear all the string

                //add Query to update to Student_Details table
                strBuilder.Append("UPDATE Student_details SET Email = N'suri@gmail.com' WHERE Name = 'Surendra'");
                sqlQuery = strBuilder.ToString();
                using (SqlCommand command = new SqlCommand(sqlQuery, conn))
                {                   
                    int rowsAffected = command.ExecuteNonQuery(); //execute query and get updated row count
                    Console.WriteLine(rowsAffected + " row(s) updated");
                }

After executing above code, you will see updated Student_details table, output will be as shown below:

Complete C# Code for adding/updating and connecting to database looks like below

using System;
using System.Data.SqlClient;
using System.Text;

namespace ConnectingToSQLServer
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Getting Connection ...");

            var datasource = @"DESKTOP-PC\SQLEXPRESS";//your server
            var database = "Students"; //your database name
            var username = "sa"; //username of server to connect
            var password = "password"; //password

            //your connection string 
            string connString = @"Data Source=" + datasource + ";Initial Catalog="
                        + database + ";Persist Security Info=True;User ID=" + username + ";Password=" + password;

            //create instanace of database connection
            SqlConnection conn = new SqlConnection(connString);


            try
            {
                Console.WriteLine("Openning Connection ...");

                //open connection
                conn.Open();

                Console.WriteLine("Connection successful!");

                //create a new SQL Query using StringBuilder
                StringBuilder strBuilder = new StringBuilder();
                strBuilder.Append("INSERT INTO Student_details (Name, Email, Class) VALUES ");
                strBuilder.Append("(N'Harsh', N'harsh@gmail.com', N'Class X'), ");
                strBuilder.Append("(N'Ronak', N'ronak@gmail.com', N'Class X') ");

                string sqlQuery = strBuilder.ToString();
                using (SqlCommand command = new SqlCommand(sqlQuery, conn)) //pass SQL query created above and connection
                {
                    command.ExecuteNonQuery(); //execute the Query
                    Console.WriteLine("Query Executed.");
                }

                strBuilder.Clear(); // clear all the string

                //add Query to update to Student_Details table
                strBuilder.Append("UPDATE Student_details SET Email = N'suri@gmail.com' WHERE Name = 'Surendra'");
                sqlQuery = strBuilder.ToString();
                using (SqlCommand command = new SqlCommand(sqlQuery, conn))
                {                   
                    int rowsAffected = command.ExecuteNonQuery(); //execute query and get updated row count
                    Console.WriteLine(rowsAffected + " row(s) updated");
                }

            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e.Message);
            }

            Console.Read();
        }
    }
}

Once executing above code, you will see output as below

Note: In the above example, we are using Raw SQL Query to connect to the SQL Server database in C# Console application, but while creating real-world application, we use Entity-Framework with .NET or EF Core with .NET Core to connect to database, as it gives us better grasp to connect and query database, with extra level of security. You can also, take a look at following articles: Performing CRUD Operation in ASP.NET MVC Using Entity Framework

You can also create a connection string in another file like XML, to store connection strings in an external configuration file, create a separate file that contains only the connectionStrings section. Do not include any additional elements, sections, or attributes. This example shows the syntax for an external configuration file.

<connectionStrings>  
  <add name="Name"   
   providerName="System.Data.ProviderName"   
   connectionString="Valid Connection String;" />  
</connectionStrings>  

In the main application configuration file, you use the configSource attribute to specify the fully qualified name and location of the external file. This example refers to an external configuration file named connections.config.

<?xml version='1.0' encoding='utf-8'?>  
<configuration>  
    <connectionStrings configSource="connections.config"/>  
</configuration>  

Properties of the connection string

Here are possible connection string to connect to the database in C#

Standard security

Server=myServerAddress;Database=myDataBase;User Id=myUsername;
Password=myPassword;

Trusted connection string in C#

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Connection to SQL server instance

Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;
Password=myPassword;

Connect via an IP address

connetionString="Data Source=IP_ADDRESS,PORT;
Network Library=DBMSSOCN;Initial Catalog=DatabaseName;
User ID=UserName;Password=Password"

LocalDB auto-matic connection string

Server=(localdb)\v11.0;Integrated Security=true;

Attach a database file on connect to a local SQL Server Express instance

Server=.\SQLExpress;AttachDbFilename=C:\MyFolder\MyDataFile.mdf;Database=dbname;
Trusted_Connection=Yes;

Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Connect to SQL server using windows authentication

"Server= localhost; Database= databaseName;
Integrated Security=SSPI;"

Trusted connection from CE device

connetionString="Data Source=ServerName;
Initial Catalog=DatabaseName;Integrated Security=SSPI;
User ID=myDomain\UserName;Password=Password;

That's it, there can be more ways of connection strings but these are widely used one.

You can also like to read:

Connect to database using Entity Framework in ASP.NET MVC

Paging, Sorting and filtering in ASP.NET MVC

GridView in ASP.NET Core MVC