ADO - SqlConnection Class

The ADO.NET SqlConnection class is a part of the ADO.NET library in C# and is used to establish a connection to a database. It provides methods and properties to interact with a database server.

What is the ADO.NET SqlConnection class?

The ADO.NET SqlConnection class represents a connection to a SQL Server database. It provides methods for opening, closing, and managing the connection, as well as executing SQL commands and retrieving results from the database.

How to instantiate the SqlConnection object?

To instantiate the SqlConnection object, you can create a new instance of the class by providing a connection string as a parameter to the constructor. The connection string contains information such as the server name, database name, authentication method, and other connection-specific settings.

string connectionString = "Data Source=(local);Initial Catalog=YourDatabaseName;Integrated Security=True";
SqlConnection connection = new SqlConnection(connectionString);

Using the SqlConnection object:

After instantiating the SqlConnection object, you can use its methods and properties to interact with the database. Some common operations include opening the connection using the Open() method, executing SQL commands using SqlCommand, and retrieving data using SqlDataReader or DataAdapter.

connection.Open();
// Perform database operations
connection.Close();

Why is it important to close a database connection?

Closing a database connection is important because it releases the resources associated with the connection, such as network connections, memory, and database locks. If connections are not closed properly, it can lead to resource leaks and may impact the performance and scalability of the application.

How to properly close a connection?

To properly close a connection, you can call the Close() method of the SqlConnection object. It releases the resources and closes the connection to the database.

connection.Close();

Alternatively, you can also use the using statement, which automatically calls the Dispose() method of the SqlConnection object, ensuring that the connection is properly closed even if an exception occurs.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    // Perform database operations
}

What is the problem with hard-coding the connection string in the application?

Hard-coding the connection string in the application can lead to maintenance issues. If the connection string needs to be changed, you would have to modify and recompile the application code. It also poses a security risk as sensitive information like passwords may be exposed in the code.

How to store and retrieve the connection string from the configuration file?

It is recommended to store the connection string in a configuration file, such as the app.config or web.config file. This allows for easy configuration changes without modifying the application code.

To store the connection string in the configuration file, you can add an entry inside the <connectionStrings> section:

<connectionStrings>
    <add name="MyConnection" connectionString="Data Source=(local);Initial Catalog=YourDatabaseName;Integrated Security=True" />
</connectionStrings>

To retrieve the connection string from the configuration file, you can use the ConfigurationManager.ConnectionStrings property:

string connectionString = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);

Make sure to include the necessary namespace for the ConfigurationManager class:

using System.Configuration;

By storing the connection string in the configuration file, you can easily change it without modifying the code, enhancing the flexibility and maintainability of your application.