How To Create Connection With Database in SQL Server

Creating and Managing Connections

To connect to a database for retrieving the data and updating the database, performs the following tasks.

1.    Create a connection object

2.    Create a command object

3.    Open the Connection object,

4.    Execute SQL statements in command object

5.    Close the connection object

Creating a CONNECTION object:

The connection component of a data providers is used to establish a connection with a Data Source.

Read : What is ADO.NET? 

To create a connection between User Interface and Data Source, Create a connection object.

Read: Connected and Disconnected Architecture of ADO.NET

Use SqlConnection class to open the connection of Microsoft SQL Server Database.

After creating the connection object, Use ConnectionString property provides the information that defines a connection to a database by using a string of a parameter.

SqlConnection connection = new SqlConnection();

conncetion.ConnectionString = “DataSource = SQLServer1”;

Initial Catalog = HR; UserID = sa; password : #1234”;

If UserID and Password parameters of connection string is used then Integrated Security parameter is used with Connection String property.

For SqlConnection class, A System.Data.SqlClient namespace should be included. It is a collection of classes used to access a SQL Server Database.

Creating a Command object:

To execute SQL statements, create an instance of SqlCommand class in System.Data.SqlClient namespace.

We can execute SQL statements or stored procedure through command objects.

SqlConnection connection = new SqlConnection();

connection.ConnectionString = “DataSource = SQLSERVER; Initial Catalog = HR; UserID = loginID, Password = 1234”;

SqlCommand cmd = new SqlCommand(“Select from TableName”, connection);

Opening the Connection object:

To open the connection, use Open() method. connection.Open();

Execute SQL Statements in Command Object

To execute the query passed in command object use SqlDataReader object. 

It is initialized by calling the ExecuteReader() method of SqlCommand class.

The SqlDataReader object provides the means of reading stream of Rows from a SQL Server Database.

SqlDataReader myReader = cmd.ExecutReader();

The SqlDataReader object provides means of reading stream of rows from a SQL Server Database.

myReader.Read();

Closing the Connection object:

After executing the commands that you want to execute, close the connection by Close() method.

connection.Close();

Final Code to Connect the UI Database

SqlConnection connection = new SqlConnection();

conncetion.ConnectionString = “DataSource = SQLServer1”;

Initial Catalog = HR; UserID = sa; password : #1234”;

SqlCommand cmd = new SqlCommand(“Select from TableName”, connection);

SqlDataReader myReader = cmd.ExecutReader();

myReader.Read();

connection.Close();

Handling Connection Events:

The connection objects have events associated with it that are used to retrieving information message from a DataSource or to determine. If the state of a connection object has changed.

The ConnectionState enumeration of System.Data namespace describe the current state of the connection to a DataSource.

The various members of ConnectionState are :

Broken: The connection to DataSource is broken. It can occur only after the connection has been opened.

Closed: The connection to DataSource is closed.

Connecting : The connction object is connecting to the DataSource.

Executing : The connection object executes a command.

Fetching : The connection object retrieves the data.

Open : The connection to DataSource is opened.

There are two key events for SqlConnection class are: 

StateChange Event:

It occurs, when the state of the connection changes. An arguments of type StateChangeEventArgs contains Data related to particular event. The property of StateChangeEventArgs.

CurrentState : To get the New State of connection

OriginalState : To get the original State of the connection.

Final code:

Connection.StateChange += new StateChangeEventHandler(OnStateChange);

protected static void OnStateChange(Object sender StateChangeEventArgs args)

{

Console.WriteLine(“The connection State has change from {0} to {1}”, args.originalState, args.Current state);

}

InfoMessage Event:

It occurs when an information message or warning is returned from a DataSource. 

An argument of type SqlInfoMessageEvents Args contains the Data related to that particular event. The property of SqlInfoMessageEventsArgs.

Error:

To get collection of warning sent through server.

Message: To get the full text of error sent from Database.

Source: To get the name of object that is generated the error.

Final Code:

Connection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessages)

Protected static void OnInfoMessage(object sender SqlInfoMessageEventArgs args)

{

          Foreach(SqlError err in args.Errors)

          {

Console.WriteLine(“The {0} error has occurred”, err.Message);

}

}

Connection Pooling

Connection Pooling enables DataSource to reuse connection for a user. It also improves application performance and enhances scalability.

The .NET Framework Data Provider for SQL Server provides connection pooling for ADO.NET client application.

When we open a connection, A connection pool is created that associate the pool with the connection string in connection.

Connection Pooling is controlled by certain parameters that are placed into the connection string property of the SqlConnection class.

These parameters are:

Connection TimeOut(Time in Second) : It is the time to wait while a connection in DataSource is attempted.

Min Pool Size:

Minimum number of connection pool.

Max Pool Size:

Maximum no of connection pool.

Pooling:

When true, it cause the request for a new connection to be drawn from pool.

Connection Reset:

When the connection is removed from the pool, the database connection will be reset.

Load Balancing TimeOut, Connection Lifetime:

Maximum time in second that a pooled connection should live. It is useful in load balanced cluster configuration to force a balance between a server that is online and a server that has just started.

Enlist:

When the value of Enlist is true, the connection is automatically enlisted into creation thread’s current transaction context.

Now, if the pooling property is false, then the pooler opens a new connection.

And, if the pooling property is true, then the pooler acquires (the act of gain) a connection from a pool.

To implement connection pooling, follows the rules.

The connectionString and userID must be same for every users or the service that will participate in pool.

Final Code:

SqlConnection connection = new SqlConnection();

Connection.ConnectionString = “DataSource = SQLSERVER ; userID = sa; password=1234; InitialCatalog = HR; connection Timeout = 20; pooling = true”’

Conection.Open();

The System.Data.dll assembles implements the SQL Server .NET Data provider in the System.DataSqlClient namespace.