Database access is the most common part of applications. With the introduction of C # and ADO. net, this operation becomes easier. This article will demonstrate four basic database operations.
● Read data. Data types include integer, string, and date.
● Write data. Like reading data, we also need to write multiple types of data. This can be done through SQL statements.
● Update or modify data. We will use the SQL statement again.
● Delete data. Use SQL.
The above operations are based on the Microsoft Access 2000 database. However, we need to make a simple modification to the connection string before using SQL or other ADO data.
Start Operation
Before using the ADO class, we will include the namespace of ADO. NET and some common data classes. Add the following code to the location where you want to perform database operations. Its specific location should be after the namespace row and before the class declaration.
Using system. Data; // State Variables
Using system. Data. Ado; // Database
Using system. Globalization; // Date
You may also need to add parameters to the system. Data namespace, depending on the type of the project. The compilation information of the code you added will remind you of this. To add a system. Data namespace:
● Right-click Solution Explorer -- Parameter options;
● Select Add parameters;
● Select the. NET Framework bar;
● Double-click the system. Data. dll entry;
● Select OK;
● System. Data should appear in the parameter list of Solution Explorer.
Since connection strings are used in most operations, we recommend that you include them in the classes used.
Note: the path of the database file in the program may be different from the following:
// Attributes
Public const string db_conn_string =
"Driver = {Microsoft Access Driver (*. mdb)};" +
"DBQ = D: // CS // testdbreadwrite // simpletest. mdb ";
Read data
Now the operation is interesting. Read is done through the adodatareader class (see Chris Maunder's article "the ADO. Net adodatareader class" to learn more ). The read procedure is as follows:
● Use ADO to connect to open a database
Adoconnection conn = new adoconnection (db_conn_string );
Conn. open ();
● Create an SQL statement to confirm the data to be obtained. After this command is executed, an adodatareader object is returned. Note the out keyword in the execute method. This is the way to pass parameters in C.
Adodatareader Dr;
Adocommand cmd = new adocommand ("select * From person", Conn );
Cmd. Execute (out Dr );
● Traverse each record in the adodatareader cyclically until it is complete. Note: Data is directly returned as a string. The field name displays the fields to be read.
While (dr. Read ())
{
System. Console. writeline (Dr ["firstname"]);
}
● Clear
However, as a good programmer, we should place the code in try/catch/finally to ensure that we can control all exceptions.
Try
{
... The 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
["Stuff"] a string of a certain type is usually returned. However, to obtain an integer or datetime object, you need to list the data. It can be illustrated in a simple example or in many built-in examples of adodatareade. For example:
Int nordinalage = dr. getordinal ("Age ");
Int Nage = dr. getint32 (nordinalage );
Datetime tupdated = (datetime) Dr ["updated"];
Note the usage of getordinal field positioning by name. If the field is empty (no value is entered), the above Code will throw an exception. In this case, we use the isnull method to check whether the data exists.
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
Insert, modify, and delete are easily implemented using SQL statements. The following code inserts a record using an SQL command:
// SQL command
String ssqlcommand = "insert into person (age, firstname,
Description, updated) "+
"Values (55, 'bob', 'is a penguin ',
'2014/1/25 20:30:15 ');";
// Create the command object
Adocommand uploadder = new adocommand (
Ssqlcommand,
Db_conn_string );
Uploadder. activeconnection. open ();
// Execute the SQL command
Int nnoadded = uploadder. executenonquery ();
System. Console. writeline ("/nrow (s) added =" + nnoadded + "/N ");
Note: Try/catch does not appear in the above example, but actually needs to be written.
Insert
The above code inserts a record using an SQL statement. This command will be executed later. Note the following in the Command Format:
● Values are directly assigned with different single quotes (');
● The string must be enclosed in single quotes ('blah ');
● The string cannot contain any single or double quotation marks;
● The date and time must be included in single quotes in international format. ('Yyyyy/MM/dd hh: mm: ss ')
Modify
The update command indicates the record to be repaired and modified. The value returned by executenonquery () shows the number of records of changes, so that if there are five Peter in the table, it returns 5.
String ssqlcommand = "Update person set age = 27 where firstname = 'Peter '";
Delete
The DELETE command displays the records to be deleted. This may be several. The value returned by executenonquery () shows the number of records with changes, so that if there are two Bobo In the table, 2 is returned. Both bobrs are deleted.
String ssqlcommand = "delete from person where firstname = 'bobobo '"; |
