2 Get data
After connecting the database, you can extract data from the database. In the sample program, click the "Data" button from the Database to see the prompt information on the status bar - "Data has been retrieved, a total of 324."
Figure 5-10 Getting Data
Extracting data is also a more complicated process, requiring several objects to work together to complete.
(1) Define an OLEDBDataAdapter object, which is responsible for extracting data from the database:
Private da as oledbdataadapter = Nothing
(2) Define an OLEDBCOMMAND object, this object is responsible for sending a SQL command to the database:
Private comm as oledbcommand = Nothing
(3) Define a DataTable object, this object is used to store data, equivalent to the table in the database.
Private DT AS DataTable
These three objects plus the OLEDBCONNECTION objects described earlier, and data can be extracted. This is achieved by the SUB process onGetData ():
1 private sublessddata ()
IF da is nothing then
3 da = new OLEDBDataAdapter
4 end if
5
6 if common is nothingin
7 Comm = New OLEDBCommad
8 Comm.Connection = conn 'Specify connection objects
9 comm.commandtype = commandType.text 'Indicates using SQL commands
10 Comm.comMandText = "SELECT * from client" "Specifies the contents of the SQL command
11 END IF
12 'connects the OLEDBCommand object to OLEDBDataAdapter.
13 da.selectCommand = CommMM
14
15 if dt is nothingin
16 'Create a DataTable object, store the data from the database
17 DT = New DataTable
18 END IF
19
20 'filling data
21 da.fill (DT)
twenty two
23 'Activation "Display Record in Grid" button
24 me.btnshowdataingrid.enabled = true
25
26 'display information
27 me.statusbar1.text = "Data has been retrieved," & Dt.Rows.count & "Bar"
Twist
29 End Sub
Note that the eighth sentences, the 13th sentences, and the 21 sentence, these three statements have achieved cooperation between objects.
The meaning of the 8th sentence is that the OLEDBCOMMAND object is connected to the database with the OLEDBConnection object, and the SQL command is sent to the database via this connection.
The 13th sentence indicates that the OLEDBADAPTER object uses the OLEDBCOMMAND object to send the "Select ..." command to extract the data to the database.
The 21st sentence indicates that the OLEDBADAPTER object will be retrieved into the DataTable object. The relationship between these four objects can be described with the following graph:
Figure 5-11 ADO.NET object partnership
In Figure 5-11, the program is called "command stream" to the database to the database (such as a connection database, executing SQL command, etc.), which can be seen that the command is primarily determined by OLEDBADAPTER, it entrusts the OLEDBCommand object to do this specific Command, and OLEDBCommand needs to pass this command to the database itself through the OLEDBConnection object, and then pass this command to the database itself. This command is executed by the Database Management System (DBMS).
If the executed command is a command to extract the data, the database is ready to pass the required data, and the connection to OLEDBConnection object is passed to OLEDBADAPTER, which is responsible for putting the data "to" DataTable object. The data transmitted is referred to as "data stream" in Figures 5-11 to indicate the broken line. The endpoint of transmitting data is DATATABLE, while OLEDBADAPTER can be regarded as a transfer station of a data, and its Fill method will transfer the data to the DataTable object. Once the data is from the database "moving" to DataTable, these data is self-taking the gateway, with the data in the database "Old".
prompt:
Here is just a brief introduction to a most common data flow, please refer to Chapter VII for more detailed introduction.
3 display record
After the data arrives after DataTable, it cannot be automatically displayed on the form because DataTable is a non-visual object.
Click "Display Record in the mesh" in the sample program, and the operation results are shown in Figure 5-12.
Figure 5-12 Display data
Display data in the DataTable is implemented by the following code in the visual control DataGrid:
DataGrid1.datasource = DT
By setting the DataSource attribute of the DataGrid object is DataTable, it is a DataTable with the DataGrid. Any changes in data in DataTable will be displayed in the DataGrid synchronously. Similarly, users can also directly modify data directly through DataGrid. All modifications he do will be saved to DataTable.
There is a dedicated term to describe this association established between DataTable and DataGrid, this is "Data Binding".
Data binding is a very important concept that will be completely explained in the true meaning of this concept in Section 7.5.
4 editing and modification record
Any database application that can only be used if data can only be displayed, and it is limited. The DataGrid provided by .NET is a powerful data binding control that directly supports an increase, deletion and modification of records.
Figure 5-13 Modify
As shown in Figure 5-13, click on a cell in the grid to directly modify the contents of this field, and display a small pencil icon on the left end of the current row.
You can add a record directly to the last line, you can add a record, as shown in Figure 5-14:
Figure 5-14 New Record
Click on the leftmost column, you can select a line, then press the "DEL" button on the keyboard to delete the record.
Figure 5-15 Select a line of records to delete
Although the record can be increased, deleted, changed, if the program is passed directly, when running the program again, it is found to be saved. What is the reason?
The most fundamental reason for modifying cannot be saved is that once the data is removed from the database, these data is independent of the database, in other words, the removed data is stored in memory, and the modification of it does not cause the content of the database. This data independence gives us great flexibility. note:
When you use the DataGrid to display data in the database in VB6, it will be saved directly into the database, ie at any time, data as seen on the form is data in the database (unless "disconnected" mode is employed). The database technology used by VB6 is ADO, and VB.NET is used by ADO.NET, both of which are fundamentally different. VB6 programmers should pay attention to this important difference.
However, it is really necessary to save the modified data permanently. To do this, this function can be implemented using the Update () method of the OLEDBDataAdapter object.
Try a try:
In the sample program, add the click event for the "Save" button:
'Save the change to the database
Da.UPDATE (DT)
Then run the program, modify, delete, or add several records, click the "Save" button. Observe the operation.
It can be seen that when the Update () method is called directly, an exception is triggered, as shown in Figure 5-15:
Figure 5-16 Update exception
What is the reason?
The extraction and preservation of data is responsible by OLEDBDataAdapter, which completes four typical data processing tasks through the attributes of the four OLEDBCommand object types:
Extract record: SELECTCOMMAND
Add record: InsertCommand
Delete record: DeleteCommand
Modify record: UpdateCommand
When we extract records from the database, it has passed an OLEDBCOMMAND object to OLEDBDataAdapter, and the SQL command that its execution is:
SELECT * FROM Client
But we didn't provide a valid OLEDBCOMMAND object to other three operations, so OLEDBDataAdapter could not save data in the sample program.
OLEDBCOMMAND object is dedicated to execute SQL commands, INSERTCOMMAND executes the insert command in SQL, DeleteCommand executes the delete command in SQL, UpdateCommand executes the Update command in SQL.
prompt:
In Chapter 6, the common commands of SQL will be introduced, learn to write a SQL statement to complete a number of data processing work.
You can create an OLEDBCOMMAND object manually and set its SQL command, then pass it to the OLEDBDataAdapter object, but this is a trouble and error-wrong job, .net provides an OLEDBCommandBuilder class to do this. See the following code:
Private CB AS OledbcommandBuilder
'save data
Private subsidedata ()
IF cb is nothing then
CB = New OLEDBCOMMAVANDBUILDER
Cb.DataAdapter = DA
'Setting Update Command
Da.UpdateCommand = CB.GetUpdateCommand
'Set the insert command
Da.insertCommand = CB.GetInsertCommand
'Set the delete command
Da.deleteCommand = Cb.getDeleteCommand
END IF
'Save the change to the database
Da.UPDATE (DT)
End Sub
Note that the SELECTCOMMAND attribute of the OLEDBDataAdapter object is not seen in the code above, because this property is already set when extracting data. When you click the Save button, call the SUB process, and the data can be saved correctly to the database.
Figure 5-17 Save the data to the database
At this point, a basicly complete database application has been developed.
It looks, everything is not complicated. But behind the simple, it is supported by complex technologies, and everything is not "simple".
5 small knot
By developing a small database application, the reader should have a general understanding of how to develop database applications in .NET, the function of this applet is still limited, such as the function of finding data, and excessively dependent In the powerful control of DataGrid, it has been encapsulated in many technical details. No matter how simple our little program is, this is a good starting point.
"Blind Touch" is a well-known idiom, due to the huge and complex software technology system, the self-study process is the process of "blind touch". "Blindness" learned something is a one-sided, partial, should try to avoid the occurrence.
If the .NET database programming as a "elephant", then the purpose of this chapter is to first understand what the elephant is like, with an overall impression, then we use the magnifying glass to take care of " Every part of the elephant, after understanding, further summarize the overall elephants in general. In this way, the "elephant" can be studied, and the "blind touch" will not happen.
In the following chapters, various aspects of the database programming will be detailed. The readers will be deeply enhanced by the operational mechanism within ADO.NET through the lack of learning, which greatly enhances the ability to develop database applications.
-------------------------------------------------- --------------------------------- Reprinted please indicate the source and the author.
