Section 3 uses OleDbDataAdapte to operate databases!
Good friend! The sky is bright again. After a night, my mind is no longer clear, but I still don't want to rest! Let's talk about our database operations! We have already discussed how to operate databases, but almost all of them are done through OleDbCommand and OleDbDataReader. This time we will talk about how to operate databases through OleDbDataAdapter! Because OleDbDataAdapter is a bridge between DataSet and the data source, and DataSet I think everyone knows it in ADO. NET, so it is necessary to know how to operate the database through it!
Okay, no nonsense! Previously, we used OleDbDataAdapter to execute the "SELECT" statement. I am recalling the code! (I simplified it as needed)
OleDbConnection conn = getConn (); // getConn (): Get the connection object
OleDbDataAdapter adapter = new OleDbDataAdapter ("select * from notes order by posttime desc", conn );
System. Data. DataSet mydataset = new System. Data. DataSet (); // defines DataSet
Adapter. Fill (mydataset, "notes ");
Conn. Close ();
The entire process is divided into the following steps:
1. Establish a database connection (I use my own function and have code in the previous article)
2. instantiate OleDbDataAdapter object!
3. Create a DataSet object and add the table obtained by executing the SQL statement to it.
4. Close the database connection
Through the above steps, we can use DataBind to bind the data we get to a specific control!
Next let's take a look at how to delete a specific database record through OleDbDataAdapter! (DELETE)
Because it is more difficult to delete, modify, and add data, let's take a look at the routine first, and then try it out. The program is as follows:
// Delete a specific record and delete a field with a string ID
Public Boolean DelNote (string delid)
{
Boolean tempvalue = false;
// Connect to the database
Try
{
OleDbConnection conn = getConn (); // getConn (): Get the connection object
String selectstr = "select * from notes where id =" + delid;
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter (selectstr, conn );
// You must create OleDbCommandBuilder!
OleDbCommandBuilder mybuilder = new OleDbCommandBuilder (myDataAdapter );
DataSet ds = new DataSet (); // create a DataSet () instance
MyDataAdapter. Fill (ds, "notes ");
// The following can be simplified. Since I selected all records at first, the set method is used.
Foreach (DataRow dr in ds. Tables ["notes"]. Rows)
{
If (dr ["id"]. ToString (). Equals (delid ))
{
Dr. Delete ();
}
}
MyDataAdapter. Update (ds, "notes ");
// If the execution is successful, TRUE is returned. Otherwise, FALSE is returned.
Conn. Close ();
Tempvalue = true;
Return (tempvalue );
}
Catch (Exception e)
{
Throw (new Exception ("database deletion error:" + e. Message ));
}
}
This program implements the same functions as the delete routine we used previously. I changed it to myDataAdapter to achieve the same effect!
To use myDataAdapter to perform the delete operation, take the following steps:
1. Establish a database connection (via: OleDbConnection conn = getConn ();)
2. instantiate OleDbDataAdapter object! The delete statement is not used here. Instead, the select statement is used to obtain the records to be deleted.
3. Create a DataSet object and add the records obtained by executing the select statement to it.
4. Create OleDbCommandBuilder object! And associate it with our previous OleDbDataAdapter object! Statement: OleDbCommandBuilder mybuilder = new OleDbCommandBuilder (myDataAdapter );
5. delete a specific record in the DataSet table.
6. Execute the Update command of the OleDbDataAdapter object to Update the database. The statement is as follows: myDataAdapter. Update (ds, "notes ");
7. Close database connection
In step 1 above, we have created an OleDbCommandBuilder object. Note that it is required !!! We associate it with OleDbDataAdapter to monitor the occurrence of RowUpdating events! After we delete a specified record, We need to Update the database by executing the Update command of the OleDbDataAdapter object!
In fact, the above structure is not only applicable to delete operations, but also for insert and update operations, as long as we change step 1 above to the corresponding operation statement!
Note:
In the help provided by ms, it uses the following method:
String mySelectText = "SELECT * FROM Categories order by CategoryID ";
String mySelectConn = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = nwind_r1_mdb ";
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter (mySelectText, mySelectConn );
MyDataAdapter. DeleteCommand. CommandText = "delete from Categories WHERE CategoryName = Produce ";
MyDataAdapter. DeleteCommand. Connection = myDataAdapter. SelectCommand. Connection;
However, unfortunately, I used a variety of methods to test this method for many times and failed to test it. I always reported errors. Finally, I came to the conclusion that: another error occurred in MS's help. I don't know what's going on! If a friend has succeeded in this way, I hope to tell you that I did not find the correct method for one night!
|
