How to set connectionstring for SqlConnection Object to connect to sql server database with example

Setting Connection String for Sql Server Database

Introduction:

In this article you will learn about

• how to set connection string parameter in SqlConnection Object, 
• how to instantiate the SqlConnection, 
• How to open the connection, 
• How to pass the connection object to other ADO.NET objects, 
• How it perform operations with other Ado.Net Objects,
• How to close the connection.

      You can also find Introduction to Ado.Net Part1 and Part2 to know briefly about Ado.Net Data Providers.

Description:

               While doing any crud operations with sql database first we have to create connection to sql server database.  To Connect with sql server database from .net applications Ado.Net Provides two providers OledbConnetion and SqlConnection Object.

SqlConnection Object:

           To interact with database, you must have connection to it. The Connection Object helps to identify the datasource, database, User Id and Password etc connection string parameters required to connect to a database.  So this SqlConnection object is used by SqlCommand object so that they will know which database to execute query.

SqlConnection object exist in System.Data.SqlClient namespace which is defined in System.Data assembly.

SqlConnection object has following constructors

i.e SqlConnection()
    SqlConnection(string ConnectionString)

Here Connection String is a collection of attributes where it contains following attributes

- Data Source
- User Id and Password
- Trusted_Connection or Integrated Security
- Database or Initial Catalog
- Provider

I explained these briefly in Connection String Parameters for sql and Oracle databases.

Sql Server Database provides two modes for connecting to it from .Net applications.

Sql Server Authentication.

"Provider=SqlOledb;User Id=Sa;Password=;
Database=[;Data Source=]"

Windows Authentication.
In windows authentication no need to provide User Id and Password parameters

"Provider=SqlOledb; Trusted_Connection= true/ Integrated Security= SSPI;
Database=[;Data Source=]"

SqlConnection Object goes through these sequence of operations while communicating with data sources.

            

      1). Instantiate of SqlConnection.

      2). Open the Connection.

      3). Pass the connection to other Ado.Net objects.

      4). Doing database operations along with other Ado.Net Objects.

      5). Closing the Connection 

In the below code i explained these five steps in clear manner

using System; using System.Collections.Generic; using System.Linq; using System.Text; // Importing namespaces required using System.Data; using System.Data.SqlClient; namespace LINQ_Practice { class SqlConnentionDemo { static void Main(string[] args) { // 1. Instantiation of SqlConnection Object // Windows Authentication //SqlConnection ObjCon = new SqlConnection("data source=(local); trusted_Connection=true; initial catalog=cs9db"); // Sql Server Authentication SqlConnection ObjCon = new SqlConnection("data source=(local); userid=sa;password=9290812112; initial catalog=cs9db"); SqlDataReader ObjRdr = null; try { // 2. Opening the connection ObjCon.Open(); //3. Passing connection to other Ado.Net Objects. SqlCommand ObjCmd = new SqlCommand("Select * from Dept", ObjCon); // 4. Performing database operations with other Ado.Net Objects. ObjRdr = ObjCmd.ExecuteReader(); // Priniting the Result while (ObjRdr.Read()) { Console.WriteLine(ObjRdr[2]); } Console.ReadLine(); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { // if reader is open then closing the reader if (ObjRdr != null) { ObjRdr.Close(); } //5. If Connection is open then closing the conneciton if (ObjCon.State != ConnectionState.Closed) { ObjCon.Close(); } } } } }

As you saw in code at 1. we are creating an instance of SqlConnection class by specifying Connection String in both Sql Server and windows authentication modes. We can use one mode. at step 2. we opened the connection with database by calling Open( ) method of SqlConnection Object.

        At step 3 we added SqlConnection Object to SqlCommand as parameter. At step 4 Connection object perform database operations along with SqlCommand and SqlDataReader object. We will see SqlCommand and SqlDataReader in later. At Step 5 we closed the connection. It is always good practice to know the status of the connection before closing the connection.

Conclusion:

I hope you understand the purpose and need of SqlConnection Object, How to pass Connection String parameters as key/value pairs while instantiation, How it is used in database operations, Finally how to close the Connection.

 Don't forget to Comment and Share if you like this post for our friends. Thank You.