Introduction:
- ADO.NET is a module of .Net Framework which is used to establish connection between application and data sources.
- Data sources can be such as SQL Server and XML. ADO.NET consists of classes that can be used to connect, retrieve, insert and delete data.
.NET Framework Data Providers
- These are the components that are designed for data manipulation and fast access to data.
- It provides various objects such as Connection, Command, DataReader and DataAdapter that are used to perform database operations.
- The .NET Framework Data Provider for SQL Server classes is located in the System.Data.SqlClient namespace.
- We can include this namespace in our C# application by using the following syntax.
- using System.Data.SqlClient;
This namespace contains the following important classes.
| Class | Description | |
| SqlConnection | It is used to create SQL Server connection. This class cannot be inherited. | |
| SqlCommand | It is used to execute database queries. This class cannot be inherited. | |
| SqlDataAdapter | It represents a set of data commands and a database connection that are used to fill the DataSet. This class cannot be inherited. | |
| SqlDataReader | It is used to read rows from a SQL Server database. This class cannot be inherited. | |
| SqlException | This class is used to throw SQL exceptions. It throws an exception when an error is occurred. This class cannot be inherited. |
ADO.NET SqlConnection Class
- It is used to establish an open connection to the SQL Server database.
- It is a sealed class so that cannot be inherited.
- Connection does not close explicitly even it goes out of scope.
- Therefore, you must explicitly close the connection by calling Close() method.
SqlConnection Constructors
| Constructors | Description |
| SqlConnection() | It is used to initializes a new instance of the SqlConnection class. |
| SqlConnection(String) | It is used to initialize a new instance of the SqlConnection class and takes connection string as an argument. |
SqlConnection Methods
| Method | Description |
| BeginTransaction() | It is used to start a database transaction. |
| ChangeDatabase(String) | It is used to change the current database for an open SqlConnection. |
| ChangePassword(String, String) | It changes the SQL Server password for the user indicated in the connection string. |
| Close() | It is used to close the connection to the database. |
| CreateCommand() | It enlists in the specified transaction as a distributed transaction. |
| GetSchema() | It returns schema information for the data source of this SqlConnection. |
| Open() | It is used to open a database connection. |
| ResetStatistics() | It resets all values if statistics gathering is enabled. |