The SqlConnection Object:
In this article we would see the how to use SqlConnection object to create a physical connection in between .NET application and SQL Server database.
Introduction:
An SqlConnection object represents a unique session to a SQL Server data source. It is responsible to make the physical connection with the data source. It is very much optimize to use SQL Server. Connection object keeps information about the server, database security, and protocol etc. Each connection represents overhead and is very expensive resource so we should make a connection to retrieve, manipulate, and change the data and close the connection after using. Lets consider a Web site that is being hit with hundreds of thousands of hits a day. Applications that grab connections and don't close connection can have seriously negative impacts on performance and scalability. The purpose of creating an SqlConnection object is to enable other ADO.NET code to work with a database. Other ADO.NET objects, such as an SqlCommand and an SqlDataAdapter take a connection object as a parameter.
Creating a SqlConnection Object:
To connect to database we have to provide connection string to the SqlConnection object. Connection string contains Data Source (server name), Initial Catalog (database name), and security information. To create connection string one can use SqlConnectionStringBuilder object.
Connection String parameters:
|
Connection String Parameter Name
|
Description
|
|
Data Source
|
Identifies the server. Could be local machine, machine domain name, or IP Address.
|
|
Initial Catalog
|
Data base name.
|
|
Integrated Security
|
Set to SSPI to make connection with user's Windows login
|
|
User ID
|
Name of user configured in SQL Server.
|
|
Password
|
Password matching SQL Server User ID.
|
Integrated Security: It windows based authentication and is secure when you are on a single machine doing development.
e.g.
SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog= Northwind; Integrated Security=SSPI;");
SQL Authentication: However, we use SQL server based authentication in which we have to provide user ID and password with permissions set specifically for the application you are using.
The following shows a connection string, using the User ID and Password parameters:
e.g.
SqlConnection conn = new SqlConnection("Data Source=DatabaseServer; Initial Catalog=Northwind; User ID=YourUserID; Password=YourPassword");
Notice how the Data Source is set to Database Server to indicate that you can identify a database located on a different machine, over a LAN, or over the Internet. Additionally, User ID and Password replace the Integrated Security parameter.
The sequence flow of operations occurring in the lifetime of an SqlConnection is as follows:
- Create object of SqlConnection
SqlConnection conn = new SqlConnection("Data Source=DatabaseServer; Initial Catalog=Northwind; User ID=YourUserID; Password=YourPassword");
- Open the connection.
conn.Open();
- Pass the connection object to other ADO.NET objects.
SqlCommand cmd = new SqlCommand("select * from Orders", conn);
- Perform database operations with the other ADO.NET objects.
SqlDataReader reader = cmd.ExecuteReader();
- Close the connection.
reader.Close();
Summary:
SqlConnection objects let. NET code knows what database to connect to and how to make the physical connection. They are instantiated by passing a connection string with a set of key/value pairs that define the connection. The steps you use to manage the lifetime of a connection are create, open, pass, use, and close. Be sure to close your connection properly when you are done with it to ensure you don't have a connection resource leak.