Author: Li Yang http://oraasp.vicp.net/article/article.aspx?ID=21
Database Access is the most common part of the program. This operation becomes simpler with C # and ADO.NET. This article will demonstrate four most basic database operations.
● Read the data. These include multiple data types: integer, string, date type.
● Write data. As read data, we also need to write multiple types of data. This can be done through the SQL statement.
● Update or modify the data. We will use the SQL statement again.
● Delete data. Implement with SQL.
The above operations are based on the Microsoft Access 2000 database, however, we have to make simple modifications to the connection string to use SQL or other ADO data.
Start operation
Before using the ADO class, we will include ADO.NET's namespaces and some common data classes. Add the following code to the place you want to perform database operations. Its specific location should be after named space line, before the class declare.
Using system.data; // state variables
Using system.data.ado; // Database
Using system.globalization; // Date
You may also add parameters to System.Data namespace, depending on the type of engineering. The compilation information of the code you add will remind you. Add a SYSTEM.DATA namespace:
● Right click on the Solution Explorer - Parameter Options; ● Select Add parameters;
Since the connection string is used in most operations, I suggest you contain it in the class used.
Note: The path to the database file in the program may be different below:
// attributes
Public const string db_conn_string = "driver = {Microsoft Access Driver (* .mdb)};"
"Dbq = d: //cs/testdbreadwrite//simpletest.mdb";
Read data
It is more interesting now. Reading is done through the AdodaReader class (see the ado.net adodarader class "The ADO.NET AdoDaReader Class" for more). The steps to read are as follows:
● Open the database with ADO connection
Adoconnection Conn = New Adoconnection; DB_CONN_STRING
Cn.open ();
● Create a SQL statement to confirm the data you want to get. This command is executed and returns an AdodaAder object. Note the OUT keyword in the Execute method. This is the way in the C # transmitting parameters.
AdodataReader DR;
Adocommand cmd = New Adocommand ("Select * from Person", Conn;
Cmd.execute (OUT DR);
● Loop traverse each record in the AdodataReader until it is completed. Note: The data is returned directly as a string. The field name shows the fields to read.
While (Dr.Read ())
{
System.console.writeLine (DR ["firstname"]);
}
● Clear
However, as an excellent programmer, we should put the code in Try / Catch / Finally, ensuring that we can control all unexpected. 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"] can usually return a type of string. But to get an integer or DateTime object, you need to list this data. One of the many examples built in a simple example or in AdodaAde can be explained. E.g:
Int nordinalage = Dr.Getordinal ("age");
INT NAGE = Dr.Getint32 (Nordinal);
DateTime Tupdated = (datetime) DR ["Updated"];
Note the usage of the Getordinal field through the name. If the field is empty (no fill value), the above code will trigger an exception. In this case, we use the isnull method to verify that the data exists.
Int nordinalage = Dr.Getordinal ("age");
IF (Dr.Isnull (Nordinal))
{
System.Console.writeline ("age: not given!");
}
Else
{
INT NAGE = Dr.Getint32 (Nordinal);
System.Console.writeline ("age:" nage);
}
Insert, modify, delete, and other SQL commands
Insert, modify, and delete it is easy to implement with SQL statements. The following code is inserted into a record through a SQL command:
// SQL Command
String ssqlcommand = "INSERT INTO PERSON (AGE, FIRSTNAME, DESCRIPTION, UPDATED"
"VALUES (55, 'Bob', 'Is a Penguin', '2001/12/25 20:30:15');";
// Create The Command Object
Adocommand cmdadder = new adocommand (SSQLCommand, DB_CONN_STRING);
Cmdadder.activeConnection.Open ();
// Execute the SQL Command
INT nnoadded = cmdadder.executenonquery ();
System.console.writeline ("/ nrow (s) added =" nnoadded "/ n"); Note: Try / catch does not appear in the above examples, actually written.
insert
The above code is inserted into a record through a SQL statement. This command is executed later. The command format is required to note:
● Direct assignment value, different single quotes ('); ● String must be enclosed in single quotes; ● The string cannot contain any single quotes or double quotes; ● Date and time must be international format Included in single quotes. ('YYYYY / MM / DD HH: MM: SS')
modify
The UPDATE command indicates that the record to be modified and modified. EXECUTENONQUERY () Returns the number of changes to changes, so if there are 5 peter in the table, it will return 5.
String ssqlcommand = "Update Person Set Age = 27 where firstname = 'peter'";
delete
The DELETE command displays the record to be deleted. This may be a few. ExecuteNonQuery () Returns the number of records to change, so that if there are 2 BOBOs in the table, it returns 2. These two BOBOs will be deleted.
String ssqlcommand = "delete from person where firstname = 'bobo'";
About sample program
The sample is a simple control program that performs all the actions provided in the Microsoft Access database. In Visual Studio.NET IDE, you can compile it as an engineering file as an engineering file. Change the value of db_conn_strin in mainconsole.cs, let it point to SimpleTest.mdb, compile it.
in conclusion
Now you can perform the underlying database operation in C #. Looking for time learning SQL, you should also read more articles about it work. If you feel tired, go to www.mctainsh.com to see the updated code.
