s1/c# Language and database Technology Fundamentals/14-using ADO to access the database

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