Use C # to complete simple operations for the ADO.NET database
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:
// attributespublic const string db_conn_string = "driver = {Microsoft Access Driver (* .mdb)};" "DBQ = D: ////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; conn.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); 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 nordinal ("age"; int nage = tr.getint32 (Nordinal); DateTime tupdated = (datetime) DR ["Updated"]; Note To locate 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 nordinal ("age"); if ("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 (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 commandint nnoadded = cmdadder.executenonQuery (); system.console.writeline ("/ nrow (s) added =" nnoadded "/ n"); Note: TRY / CATCH does not appear in the above example It is actually written.
insert
The above code is inserted into a record through a SQL statement. This command is executed later. Note in the command format is: ● Numeric Direct assignment, different single quotes ('); ● Strings must be enclosed in single quotes (' Blah '); ● No single quotes or double quotes in the string; ● Date and time should be included in single quotes in an international format. ('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 programs
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.
