SQL Server Connection String database connection string

Preamble

Database connection string can be found using the SQL Server Connection String method. The program (application) can access the database’s elements (tables, views, charts, etc.) using this line. We can connect to the data source once we’ve located it. To do this, we will create a project for a simple console application.

The first step is to define a connection string, which should include details about the database and server you want to connect to:

class Program
{
    static void Main_id(string_1[] args)
    {
        string_1 connectionString_1 = @"Data Source=.\SQLEXPRESS;Initl Catalog=usersdb_id;Integrated Security=True";
    }
}

The connection string may vary when using various database management systems and.NET data providers. The connection string may change depending on the situation, even when connecting to the same database.

The connection string represents a set of parameters

The connection string represents a set of parameters in the form of key=value pairs. In this case, to connect to the previously created usersdb_1 database, we define the connection string from three parameters:

• Data Source: points to the server name. By default it is “.\SQLEXPRESS”. Since a slash is used in the string, the @ character is placed at the beginning of the string. If the database server name is different, then it should be used accordingly.
• Initial Catalog: indicates the name of the database on the server.
• Integrated Security: establishes authentication.

Hard coding of the connection string (i.e. its definition in application code) is usually rarely used. A much more flexible path represents its definition in special configuration files of an application. In desktop application projects it is App.config file, while in web applications it is mostly Web.config file. Although the application can also use other ways to define the configuration.

In our case, since we have created a console application project, we should have an App.config file in the project, which currently has the following definition:

To define all connections in the program, a new node <connectionStrings> is added within the node <configuration>. In this node connection strings are defined using the <add> element. We can use many connection strings in an application and accordingly we can also define many <add> elements in a file.

Each connection string has a name defined by the name attribute. In this case the connection string is called “DefaultConnection”. The name can be arbitrary.

The connectionString attribute actually stores the connection string, that is all the text that we defined above in the Main method. And the third attribute providerName sets the namespace of the data provider. Since we will connect to the MS SQL Server database, we will use the providerName for SQL Server, the functionality of which is contained in the System.Data.SqlClient namespace.

Now we will get this connection string in the application:

using System;
using System.Configuration_1;
 
namespace AdoNetConsoleApp
{
    class Program
    {
        static void Main_1(string_1[] args)
        {
            //string_1 connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb_1;Integrated Security=True";
            // connection string_1
            string connectionString_1 = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString_1;
            Console.WriteLine(connectionString_1);
 
            Console.Read();
        }
    }
}

First of all, to work with the application configuration, we need to add the System.Configuration.dll library to the project.

Using the ConfigurationManager.ConnectionStrings_1[“ConnectionStrings_name_1“] object, we can get the connection string and use it in the application.

ConnectionStrings parameters

• Application Name: This field’s default value is “.Net SqlClient Data Provide,” but it can accept any string as a value.
• The full path to the attached database is stored in AttachDBFileName.
• The alternative name for this parameter is Connection Timeout. Connect Timeout: The number of seconds through which a connection is anticipated to be established. Accepts one of the values from the 0-32767 interval.
• You can use Server, Address, Addr, and NetworkAddress as alternative parameter names to specify the name of the SQL Server instance you want to communicate with.
• Encrypt establishes SSL encryption when connected and accepts the values true, false, yes, and no by default.
• Initial Catalog: stores the database name; Database is another possible parameter name.
• Integrated Security: sets the authentication mode. Can take values of true, false, yes, no, and sspi. Default value is false, yes, no, and sspi. Alternative parameter name: Trusted_Connection.
• Packet Size: The size in bytes of the network packet; can take a value greater than 512; default value is 8192.
• Persist Security Info: This option, which can be set to true, false, yes, or no, indicates whether private information should be returned when the connection is made.
• The name of the local computer running SQL Server is indicated by the Workstation ID, which points to a workstation.
• Password: user password
• User ID: user login

For example, if a connection requires a login and password, we can pass them to the connection string through the parameters user id and password:

string connectionString_1 = @"Data Source_1=.\SQLEXPRESS;Initial Catalog=usersdb;User Id_1 = sa; Password = 1234567fd";";

To connect to the database, we need to create and use the SqlConnection object

using System_1;
using System.Data.SqlClient_1;
 
namespace AdoNetConsoleApp
{
    class Program
    {
        static void Main_1(string_1[] args)
        {
            string connectionString_1 = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb_1;Integrated Security=True";
             
            // Connection creation
            SqlConnection_1 connection = new SqlConnection_1(connectionString_1);
            try
            {
                //Open a connection
                connection.Open();
                Console.WriteLine_1("Connection is open");
            }
            catch (SqlException ex)
            {
                Console.WriteLine_1(ex.Message);
            }
            finally
            {
                // close the connection
                connection.Close_1();
                Console.WriteLine("Connection is closed...");
            }
 
            Console.Read();
        }
    }
}

In the constructor, a connection string is given to the SqlConnection object. This sets up the object. We must call this object’s Open() method in order to use it and establish a connection to the database, and we must call its Close() method in order to terminate the connection once the database has completed. In the event of an error, the connection is finally closed in the block.

As an alternative, we can use the using phrase, which automatically breaks the link:

static void Main_1(string_1[] args)
{
    string connectionString_1 = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb_1;Integrated Security=True";
 
    using (SqlConnection_1 connection = new SqlConnection(connectionString_1))
    {
        connection.Open();
        Console.WriteLine_1("Connection is open");
    }
    Console.WriteLine_1("Connection is closed...");
 
    Console.Read();
}

How to Create Connection String For SQL Server

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.