Database programming is always a difficult point and key points, VB.NET and C #, which do not have functions for the database, and their processing of the database is class libraries programmed to the database through the .NET Framework SDK. Microsoft's MDAC is implemented. In the previous article, "Discuss Data Binding in VB.NET", we have explored data binding technology, which is very important to program database programming below. This is difficult to accommodate in the database due to the content contained in the database programming. This article discusses the basic programming of the database with VB.NET, namely, how to browse the data, how to add, insert record, how to delete records, and how to change the record.
One. Program design and running environment settings: (1). Window 2000 server version (2) .microsoft Data Acass Component 2.6 or more version (MDAC 2.6) (3) .. Net Framework SDK Beta 2
two. Database's data dictionary: For more comprehensive introduction, two typical databases are selected in terms of the selection of the database. One is the local database, which is the database ACCESS 2000 mainly introduced; another is the remote database SQL Server 2000 . Where the Access 2000's database name is "db.mdb", only one data table "Person" is stored in this database, and this data table structure is as follows:
Field Name Field Type Field Meaning ID Digital Sequence XM Text Name XB Text Gender NL Text Age Zip Text Postal Code
The database server name of the remote database SQL Server 2000 is "Server1", the database name is "DATA1", the ID of the login is "sa", the password is empty, only a "Person" data table is stored in the database, the data structure is rough As above.
three. How VB.NET implements browsing for data records:
After completing the WinForm component in the form, the key to implementing the browsing operation of the data record is to find how to locate the data record pointer. To achieve this process, you need to use the BindingManagerBase class in the .NET Framework SDK, BindingManagerBase class in system.windows.froms, BindingManagerBase is an abstract class, and he mainly manages all binding objects that bind the same data table. Two properties "position" and "count" are defined in the BindingManagerBase class, and the first attribute is to define the current data pointer, and the second attribute is mainly how much the current data set has the number of records. After the data binding has been completed, the browsing of the data record is implemented by the mating of the two attributes. So how to create a BindingManagerBase object belonging to your own, this is to use another class - BindingContext. In fact, for BindingManagerBases that belong to inheriting objects from the Control class are created by BindingContext. The following is a specific example of a BindingManagerBase object that is "MyBind" with Access 2000 for the operational database.
'Creating a data connection
DIM STRCON AS STRING = "provider = microsoft.jet.Oledb.4.0;
Data Source = db.mdb "
DIM myconn as oledbconnection = new oledbconnection () MyConn.connectionstring = STRCON
DIM STRCOM As String = "SELECT *" "
'Creating a DataSet
mydataset = new dataset ()
myconn.open ()
'Get a data set through the OLEDBDataAdapter object
DIM MyCommand as oledbdataadapter = new OLEDBDataAdapter
(STRCOM, MyConn)
'Bind the DataSet Books Data Sheet
MyCommand.Fill (MyDataSet, "Person")
'Close this data connection
myconn.close ()
'Creating a BindingManagerBase object
mybind = me.bindingcontext (MyDataSet, "Person")
For the SQL Server database, creating a BindingManagerBase object and Access 2000 is roughly the same, the only difference is that when creating a data connection, the following is the SQL Server 2000 as the operational database, the database server name is "Server1", the database name is "DATA1", The ID of the login is "sa", the password is empty, and only a "Person" data table is stored in the database, create the program code of the BindingManagerBase object:
'Creating a data connection
DIM STRCON AS STRING = "provider = SQLOLEDB.1;
Persist security info = false; user ID = sa;
Initial Catalog = Data1; Data Source = Server1 "
DIM myconn as oledbconnection = new OLEDBCONNECTION ()
MyConn.connectionstring = strcon
DIM STRCOM As String = "SELECT *" "
'Creating a DataSet
MyDataSet = new dataset ()
myconn.open ()
'Get a data set through the OLEDBDataAdapter object
DIM MyCommand as oledbdataadapter = new oledbdataadapter (STRCOM,
Myconn)
'Bind the DataSet Books Data Sheet
MyCommand.Fill (MyDataSet, "Person")
'Close this data connection
myconn.close ()
'Creating a BindingManagerBase object
mybind = me.bindingcontext (MyDataSet, "Person")
After getting a BindingManagerBase object, you can use the "Position" property and "count" attribute, you can implement the browsing of the data set. Here is the "previous", "next", "tail record", " First record. 'Button "Tail Record" Object Event Program
Private sub lastrec_click (byval sender as object, _
Byval e as system.eventargs) Handles LastRec.click
mybind.position = mybind.count - 1
End Sub
'Button "Next" Object Event Program
Private sub nextrec_click (Byval Sender as Object, _
Byval e as system.eventargs) Handles NextRec.click
IF mybind.position = mybind.count - 1 THEN
Messagebox.show ("has arrived at the last record!", "Information Tips!",
MessageboxButton.ok, MessageBoxicon.information)
Else
mybind.position = mybind.position 1
END IF
End Sub
'Button "Previous" Object Event Program
Private sub previousRec_Click (Byval Sender as Object, _
Byval e as system.eventargs) Handles PreviousRec.click
IF (mybind.position = 0) THEN
Messagebox.show ("has reached the first record!", "Information Tips!",
MessageboxButton.ok, MessageBoxicon.information)
Else
mybind.position = mybind.position - 1
END IF
End Sub
'Button "First Record" Object Event Program
Private sub numbertrec_click (byval sender as object, _
Byval e as system.eventargs) Handles FirstRec.click
mybind.position = 0
End Sub
four. VB.NET to delete data records:
When you are making a program, we may have this confusion that our data set is a DataSet object that is returned. If the DataSet object is very large, or there are many customers connected to this database server, this will cost the server. A lot of resources, the server will always crash one day for a long time. In fact, this fear is unnecessary, because the location of the DataSet object we operate is not in the server, but the client, so that the above concerns are not necessary. However, delete, modify the database, the object we operate at this time is the server side database, and does not modify the local DataSet object, so when delete, modify the operation, for the data, in the server-side database After deletion, after modification, you should still be related to the local DataSet object. Based on these knowledge, you can obtain program code for deleting an ACCESS 2000 and SQL Server 2000 database: . Remove records in the Access 2000 database:
Private sub button4_click (Byval Sender as Object, _
Byval e as system.eventargs) Handles Button4.click
'Connect to a database
DIM STRCON AS STRING = "provider = microsoft.jet.Oledb.4.0;
Data Source = db.mdb "
DIM MyConn AS OLEDBConnection = New OLEDBConnection (STRCON)
myconn.open ()
DIM strdle as string = "delete from person where id =" t_id.text
DIM MyCommand as oledbcommand = new oledbcommand (strdele, myconn)
'Remove the specified record from the database
Mycommand.executenonQuery ()
'Remove the specified record from the DataSet
MyDataSet.Tables ("Person") .Rows (mybind.position) .delete ()
MyDataSet.Tables ("Person") .acceptchanges ()
myconn.close ()
End Sub
Private sub button4_click (Byval Sender as Object, _
Byval e as system.eventargs) Handles Button4.click
'Connect to a database
DIM STRCON AS STRING = "provider = SQLOLEDB.1;
Persist security info = false; user ID = sa;
Initial Catalog = Data1; Data Source = Server1 "
DIM MyConn AS OLEDBConnection = New OLEDBConnection (STRCON)
myconn.open ()
DIM strdele as string = "delete from person where id =" t_id.textdim mycommand as oledbcommand = new oledbcommand (strdele, myconn)
'Remove the specified record from the database
Mycommand.executenonQuery ()
'Remove the specified record from the DataSet
MyDataSet.Tables ("Person") .Rows (mybind.position) .delete ()
MyDataSet.Tables ("Person") .acceptchanges ()
myconn.close ()
End Sub
Fives. VB.NET to modify data records:
Master the SQL statement and master the implementation of the above deletion of data records, which is not very difficult to use VB.NET to modify data records. Below is the code that records the record modified by Access 2000 for the operation database, as follows:
Private sub button3_click (Byval Sender as Object, _
Byval e as system.eventargs) Handles Button3.click
DIM I as integer = mybind.position
'Connect to a database
DIM STRCON AS STRING = "provider = microsoft.jet.Oledb.4.0;
Data Source = db.mdb "
DIM MyConn AS OLEDBConnection = New OLEDBConnection (STRCON)
myconn.open ()
MyDataSet.Tables ("Person") .Rows (mybind.position) .beginedit ()
'Modify the specified record from the database
DIM STRUPDT As String = "Update Person Set XM = '"
T_XM.Text "', XB ='" _
T_XB.Text "', NL =" _
T_NL.Text ", Zip =" _
T_Books.Text "Where id =" t_id.text
Dim mycommand as oledbcommand = new oledbcommand (struffdt, myconn)
Mycommand.executenonQuery ()
MyDataSet.Tables ("Person") .Rows (mybind.position) .endedit ()
MyDataSet.Tables ("Person") .acceptchanges ()
myconn.close ()
mybind.position = i
End Sub
After introducing the two typical data operations, we don't really find that it is actually programming the database. What kind of database type is selected is not very important for program development, because when using VB.NET, it is time to develop For different databases, the main difference between its development code is only that the data link is different from the data link, so it is not an alone to describe the code for modifying the record operation for SQL Server 2000 for the SQL Server 2000. I think this should be very hard. six. VB.NET to insert data records:
Inserting records and modifications to the database and deleting records are basically similar, mainly using the SQL statement, the following is the code of the recorded record with Access 2000 as the operation database:
Private sub button2_click (byval sender as object, _
Byval e as system.eventargs) Handles Button2.click
'Judging whether all fields have been added, add it, then execute, reverse the prompt
IF (t_id.text <> "" and t_xm.text <> "" "
And t_xb.text <> "" "and t_nl.text <>" ""
And t_books.text <> "") THEN
DIM myconn1 as string = "provider = microsoft.jet.Oledb.4.0;
Data Source = db.mdb "
DIM MyConn as oledbconnection = new OLEDBCONNECTION (MyConn1)
myconn.open ()
Dim strinsert as string = "INSERT INTO PERSON (ID, XM,
XB, NL, ZIP) VALUES ("& _)
T_ID.Text ", '" & _
t_xm.text "','" & _
t_xb.text "" "& _
T_nl.text "," & _
T_Books.Text ")"
DIM INST AS OLEDBCOMMAND = New OLEDBCOMMAND (Strinsert, MyConn)
Inst.executenonQuery ()
myconn.close ()
MyDataSet.Tables ("Person") .Rows (mybind.position) .beginedit ()
MyDataSet.Tables ("Person") .Rows (mybind.position) .endedit ()
MyDataSet.Tables ("Person") .acceptchanges ()
Else
Messagebox.show ("must fill all field values!", "Error!")
END IF
End Sub
The difference between the SQL Server 2000 database is inserted and the Access 2000 database insertion record operation is only only for different data links, and the specific code can refer to the code in "Deleting Data Record", which is not available here.
