In this article, I will provide your working example to create a database connection in C# using console application example.

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"

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();
        }
    }
}

c-sharp-connecting-to-sql-server-min(1).png

Output 

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

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

Property Description
Name The name of the connection string. Maps to the name attribute.
ProviderName The fully qualified provider name. Maps to the providerName attribute.
ConnectionString The connection string. Maps to the connectionString attribute.

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.