Ado. NET provides two components for accessing and processing data:. NET Framework data providers and Datasets (datasets)
- The. NET Framework Data Provider is a component designed specifically for data processing and fast, forward-only, read-only access. Use it to connect to a data source, execute commands, and retrieve results, and manipulate the data source directly.
- Datasets are designed specifically for data access independent of any data source. It allows you to manipulate data without having to work directly with the data source, or you can bind the data to the control.
. NET Framework Data Providers
. NET Framework Data Providers |
Description |
The. NET Framework Data Provider for SQL Server |
Provides access to Microsoft SQL Server data. Using the System.Data.Sql.Client namespace |
The. NET Framework Data Provider for OLE DB |
Provides access to data in the data source exposed by OLE DB. Using the System.Data.Sql.OleDb namespace |
The. NET Framework Data Provider for ODBC |
Provides access to data in data sources that are exposed using ODBC. Using the System.Data.Odbc namespace |
The. NET Framework Data Provider for Oracle |
For Oracle data sources, the. NET Framework Data Provider for Oracle supports Oracle client software version 8.1.7 and later. Using the System.Data.Oracle.Client namespace |
EntityClient provider |
Provides data access to an Entity Data Model (EDM) application. Using System.Data.Entity.Client |
Four core objects of a. NET Framework Data Provider
Object |
Description |
Connection |
Establish a connection to a specific data source |
Command |
Executing commands on a data source |
DataReader |
Reads a forward-only and read-only stream from the data source |
DataAdapter |
Populate the dataset with a data source and resolve updates |
. NET data provider and the corresponding connection class
. NET Data Provider |
Connection class |
Name space |
SQL data Provider |
SqlConnection |
System.Data.SqlClient |
OLE DB data Provider |
OleDbConnection |
System.Data.OleDb |
ODBC data Provider |
OdbcConnection |
System.Data.Odbc |
Oracle Data Provider |
OracleConnection |
System.Data.OracleClient |
Common properties and methods for connection objects
Property |
Description |
ConnectionString |
Set/Get connection string for application connection database |
Method |
Description |
void Open () |
To open a database connection using the settings specified by the ConnectionString property |
void Close () |
To close a connection to a database |
The following three steps are required to establish an application-to-database connection:
1. Define the connection string
Different database connection strings, which have different formats.
Grammar:
The connection string format for SQL Server databases is generally as follows.
Data source= server name, Initial catalog= database name, user id= username; pwd= Password
The database connection string consists of several parameters, where:
- Data Source: Specifies the name or IP address of the database server to which the application is connected. If you use this machine as the database server for your application, the value of this parameter can be ".", "(local)", or "127.0.0.1".
- Initial Catalog: Specifies the name of the database to which the application will access.
- User name of the USERID:SQL server database
- Pwd:sql the password for the server database user name
For example, the string that the application connects to the native pubs database can be written as:
String connstring= "Data source=.; Initial catalog=pubs; User Id=sa; PWD=BDQN ";
Note: If the password is empty, you can omit the PWD one.
2. Create a Connection object
Create a Connection object using a defined connection string.
Grammar:
SqlConnection connection=new SqlConnection (connstring);
3. Open a connection to the database
Call the Open () method of the Connection object to turn on the database connection.
Grammar:
Connection. Open ();
In the above three steps, the order of the first to second step can be reversed, you can first create a Connection object, set its ConnectionString property, for example:
SqlConnection connection=new SqlConnection ();
String connstring= "Data source=.;i Nitial catalog=pubs; User Id=sa; PWD=BDQN ";
connection.connectionstring=connstring;
To obtain a connection string using the Visual Studio Service Explorer:
(1) In Visual Studio, select the view → Server Explorer option in the menu bar, or press the shortcut key Ctrl+alt+s.
(2) in the Open Server Explorer, right-click the data connection option and select the Add Connection option from the shortcut menu that appears.
(3) In the Add Connection dialog box that pops up, select the data source, enter the server name, select Authentication, select the database to connect to, and then add a database connection in Server Explorer when you are sure.
(4) Select the newly added connection, and in the Properties window you will be able to find the connection string, which you can select to copy into your code.
Note: After you open the database connection, make sure that you close the database connection after you execute the command.
Exception handling
Try-catch statements
Grammar:
Try
{
Contains code that may appear to be abnormal
}
catch (exception type handled)
{
Code to handle exceptions
}
There are many types of exceptions, and we only need to focus on the exception class for this course. This is. NET provides an exception class that represents an error that occurs while the application is running.
try-catch-finally statements
Try
{
Open a database connection
Connection. Open ();
Console.WriteLine ("Open database Connection succeeded");
}
catch (Exception ex)
{
Console.WriteLine ("Exception occurred" +ex. Message);
}
Finally
{
To close a database connection
Connection. Close ();
Console.WriteLine ("Close database connection succeeded");
}
Using the Command object
The Command object is responsible for executing commands and returning results from the data source, similar to the action of a lorry, after a bridge has been built on both sides of the river, and the command object can play such a role.
. NET data providers and their corresponding command classes
. NET Data Providers |
Connection class |
Name space |
SQL Data Provider |
SqlCommand |
System.Data.SqlClient |
OLE DB data Provider |
OleDbCommand |
System.Data.OleDb |
ODBC Data Provider |
OdbcCommand |
System.Data.Odbc |
Oracle Data Provider |
OracleCommand |
System.Data.OracleClient |
Once the database connection has been established, the corresponding command object can be used to perform the operation of the database. The syntax for creating a Command object is as follows.
Grammar:
SqlCommand command=new SqlCommand (String sql,sqlconnection conn);
Two parameters are required to create a command object. The first parameter is the SQL statement that will be executed, and the second parameter is the connection object that has been created.
Main properties and methods of COMAMND objects
Property |
Description |
Connection |
Database connection used by command object |
CommandText |
The SQL statement executed |
Method |
Description |
int ExecuteNonQuery () |
Executes statements that do not return rows, such as update, etc. |
SqlDataReader ExecuteReader () or SqlDataReader ExecuteReader (CommandBehavior behavior) |
Executes the query command, returning the DataReader object |
Object ExecuteScalar () |
Returns a single value, such as execution count (*) |
This chapter focuses on the Command object ExecuteScalar () method. The ExecuteScalar () method returns only the value of the first column in the first row of the query results. This method is typically called when the query results have only one numeric value, such as when a query is completed using an aggregate function. In general, the return value of this method is required for display type conversion before it can be used.
To use the command object, you must have an available connection object, using the command object in the following steps.
(1) Creating a database connection
Create a Connection object according to the previous steps
(2) Define the SQL statement to execute
Assigns the SQL statement executed to the database to a string
(3) Create command object
Creates a command object using an existing Connection object and SQL statement string.
(4) Execute SQL statement
To execute a command using a method of the Command object
Database Query Example
Example 3 always follow the previous steps to achieve, custom a method with three parameters, the first parameter is the user name, the second parameter is the password, the third parameter uses a reference type to return the processed information. This method uses the connection object to connect to the database, and after the database connection succeeds, the query results are returned by the Command object's ExecuteScalar () method. Note that when you use the ExecuteScalar () method, you need to convert the return value of the method to an integer and then assign the value. If the return value is greater than 1, the description is used to exist; otherwise this user does not exist.
s1/c# Language and database Technology Fundamentals/14-using ADO to access the database