Use the C # language to operate the ADO database

Accessing the database is part of most applications, and with the release of C # and ADO. NET, this process has become quite simple. This article will show the following four basic database operations:

1. Read data. This includes different data types, such as integers, strings, and dates.

2. Write Data. Just like read data, we will write these common data types. This is achieved through SQL statements.

3. update or modify data. We still use simple SQL statements.

4. delete data. use SQL.

These operations are performed on a Microsoft Access 2000 database, but SQL or other ADO data sources can be used by simply changing the connection string.

Step 1

To use the ADO class, we need to include ADO. NET namespace and some exquisite date classes. add the following lines of code to the database. it should be placed under the namespace to introduce code lines and above the class definition.

Using System. Data; // declare the variable
Using System. Data. ADO; // Database
Using System. Globalization; // Date

Depending on the type of the project you are participating in, you may need to add. data namespace reference. you can judge whether the compiler produces errors after you add the code above. to add System. data namespace. You can:

1. Right-click the Solution explorer-References branch.

2. Select "add reference"

3. Select the. NET Framework tag.

4. Double-click the System. data. dll entry.

5. Click OK.

6. System. Data should now appear in the reference list of Solution explorer.

Because the connection string is used in most operations, we recommend that you set it as a member of the class you want to write. note: In your program, the path of the database file may be different.

　　File: //Sex
Public const string DB_CONN_STRING =
"Driver = {Microsoft Access Driver (*. mdb)};" +
"DBQ = D: \ CS \ TestDbReadWrite \ SimpleTest. mdb ";

Read data

Now everything is interesting. you can use the ADODataReader class to read data. (See Chris Maunder's article "ADO. NET ADODataReader class to get more information about this class .) to read data, follow these steps:

1. We use ADOConnection to open a database.

ADOConnection conn =
New ADOConnection (DB_CONN_STRING );
Conn. Open ();

2. we compile an SQL statement to define the data to be retrieved. the result of this data execution is to return an ADODataReader object. note the out keyword in the Execute method. this means that parameters are passed through references in C.

ADODataReader dr;
ADOCommand cmd =
New ADOCommand ("SELECT * FROM Person", conn );
Cmd. Execute (out dr );

3. We cyclically traverse every record in the ADODataReader until we finish our work. Note: The data is directly returned as a string and the data domain name is used to specify the read data domain.

While (dr. Read ())
{
System. Console. WriteLine (dr ["FirstName"]);
}

4. We accept the ticket

However, as a good programmer, we also need to add many try/catch/finally statements to ensure that we have handled all the errors.
Try
{
... Database operations...
}
Catch (Exception ex)
{
System. Console. WriteLine ("READING :");
System. Console. WriteLine ("ERROR:" + ex. Message );
System. Console. WriteLine ("SQL:" + sSqlCmd );
System. Console. WriteLine ("Conn.:" + DB_CONN_STRING );
}
Finally
{
// Close the connection
If (conn. State = DBObjectState. Open)
Conn. Close ();
}

Read different data types

Dr ["stuff"] This statement can usually return a data. however, to return an int or DateTime object, data conversion is usually required. this can usually be achieved by using one of many built-in converters of ADODataReader. that is:

Int nOrdinalAge = dr. GetOrdinal ("Age ");
Int nAge = dr. GetInt32 (nOrdinalAge );

DateTime tUpdated = (DateTime) dr ["Updated"];

Note the usage of GetOrdinal to locate the data field and read data by name. if the data field is blank (no data has been filled in), the code above throws an exception. to capture this situation, we can use the IsNull method to check whether data exists, as shown below:

Int nOrdinalAge = dr. GetOrdinal ("Age ");
If (dr. IsNull (nOrdinalAge ))
{
System. Console. WriteLine ("Age: Not given! ");
}
Else
{
Int nAge = dr. GetInt32 (nOrdinalAge );
System. Console. WriteLine ("Age:" + nAge );
}

Insert, modify, delete, and other SQL commands

The insert, modify, and delete operations can be easily implemented using SQL statements. The following code executes an SQL command to insert a record.

// SQL command
String sSQLCommand =
"Insert into Person (Age, FirstName, Description, Updated)" +
"VALUES (55, 'bob', 'is a penguin', '2017/25 20:30:15 ');";
// Create a command object
ADOCommand uploadder = new ADOCommand (
SSQLCommand,
DB_CONN_STRING );
Uploadder. ActiveConnection. Open ();
// Execute the SQL command
Int nNoAdded = uploadder. ExecuteNonQuery ();
System. Console. WriteLine (
"Row (s) Added =" + nNoAdded + "");

Note: try/catch Code does not appear in the above example, but should include the above Code.

Insert

The above Code creates an SQL command and executes it to insert a record. Some precautions when writing SQL commands are as follows:

1. The value data should be expressed directly. There is no single quotation mark (').
　　
2. String representation should be included in single quotes ('blah ').
　　
3. Make sure that the string does not contain any embedded single (double) quotation marks. This will confuse things.
　　
4. the date and time data are expressed in the universal format enclosed in single quotes ('yyyy/MM/dd hh: MM: ss ').

Modify

The UPDATE command specifies the data to be modified and the action to be modified. the return value of ExecuteNonQuery () indicates the number of changed records. Therefore, if five Peter's entries are in the form, the following code returns 5.

String sSQLCommand =
"UPDATE Person SET Age = 27 WHERE FirstName = 'Peter '";

Delete

The DELETE command specifies the record to be deleted. the return value of ExecuteNonQuery () indicates the number of changed records. Therefore, if two Bobo statements are in the form, the following code returns 2. both Bobo are deleted.

String sSQLCommand =
"Delete from Person WHERE FirstName = 'bobobo '";