Procedure for querying and accessing a database using ADO. NET

Database Connection operations:

1. Define the connection string:

String connString = "Data Source = server name; Initial Catalog = database name; User ID = User name; Pwd = password" (no password can be omitted)

String connString = @ "Data Source =.; Initial Catalog = database name; Integrated Security = True ";

2. Create a Connection object:

SqlConnection conn = new SqlConnection (connString );

3. Open the connection to the database:

Conn. Open ();

To use a Command object (which allows you to send requests to the database, retrieve and operate data in the database), follow these steps:

1. Create a database connection

SqlConnection conn = new SqlConnection (connString );

2. Define SQL statements

String SQL = "";

3. CREATE Command statement

SqlCommand cmd = new SqlCommand (SQL, conn );

4. Run the command (you must open the database before executing the command)

Int num = (int) cmd. ExecuteScalar ();

 

 

Use ADO. NET to query and operate databases

DataReader reads data row by row:

Main members of DataReader:

Attribute
Description
 
HasRows
Returned results?
 
Method
Description
 
Read
Forward to the next row
 
Close
Disable a DataReader object
 

Steps for using DataReader:

1. Create a Command object:

2. call the ExecuteReader () method of the Command object to create a DataReader object (assuming that there is already a Command object named comm, you can create a DataReader object like this: SqlDataReader sda = conn. executeReader ();)

3. use the Reader () method of DataReader to read data row by row (this method returns a Boolean value. If a row of records can be read, True is returned; otherwise, False is returned): sda. reader ();

4. reading the data of a column in the current row can be used to read the value of a column in square brackets like an array, such as: (type) sda [], the column index can be used like an array in square brackets, starting from 0, or using a column name. The column value to be read must undergo type conversion, for example: (string) sda ["StudentName"];

5. Close the DataReader object and call its Close () method, for example, sda. Close ();

For example, read the nationality from the database and put it into the nationality drop-down list box (query the data in the database ):

// Define an SQL statement

String SQL = "select nationality from nationlityInfo ";

// Create a Command object

SqlCommand command = new SqlCommand (SQL, DBHelper. connection );

Try

{

// Open the database connection

DBHelper. connection. Open ();

SqlDataReader reader = command. ExecuteReader ();

// Cyclically read the nationality into the nationality combo box

While (reader. Read ())

{

CboNat. Items. Add (reader ["nationality"]. ToString ());

}

Reader. Close ();

}

Catch (Exception ex)

{// Print exception

Console. WriteLine (ex. Message );

}

Finally

{// Close the database

DBHelper. connection. Close ();

}

 

 

 

 

Add, delete, and modify data in the database (use the ExecuteNonQuery () method of the Command object)

The ExecuteNonQuery () method is used to execute specified SQL statements, such as update, insert, and delete. It returns the number of rows affected by the SQL statement.

To use the ExecuteNonQuery () method of the Command object, follow these steps:

1. Create a Connection object

2. Define the SQL statement to be executed

3. Create a Command object

4. ExecuteNonQuery () method

5. Perform subsequent processing based on the returned results