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

Source: Internet
Author: User
Tags odbc ole

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.