For database programming is always an important part of the programming language, it is also a difficult point. The content of database programming is very rich, but the most basic programming is that, such as: connecting to the database, getting required data, browsing, deleting, modifying, and inserting data records. Among them, it is focused on the data operation of the data record. This article focuses on exploring the Visual C # database basic programming, namely: how to browse records, modify records, delete records, and insert records.
One. Program design and running environment settings:
(1). Window 2000 Server Edition
(2). Microsoft Data Acess Component 2.6 or above (MDAC 2.6)
(3) .. Net Framework SDK Beta 2
For more clear explanation, on the selection of the database, the currently typical database is used, one is the local database Access 2000, and the other is the remote database SQL Server 2000. The local database name is "db.mdb" where the data structure of the data table "Person", "Person" table is as follows:
Field Name Field Type Field Meaning
ID number serial number
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 "DATA1", the login ID is "sa", the password is empty, the database also defines a "Person" table, the data structure is as follows.
two. How to browse data:
In the "Visual C # data binding", you have already understood how some fields in the data set is on an attribute of the WinForm component so that the programmer can customize the data display according to the WinForm component. And at this time, the WinForm component display content can change as the record pointer changes. At this time, it is visible that the key to browse data records is how to change the record pointer. To implement this operation, you should use the BindingManagerBase class. The main role of this class is to manage objects that implement the same data source. Specifically, it is possible to keep the components that have been bonded to the same data source on the Windows form to remain synchronized. A property "position" is defined in the BindingManagerBase class, and the data pointer in the BindingManagerBase object can be changed by this attribute. Creating a BindingManagerBase object must be used to use the BindingContext class, in which each object you have inherited by inheritance by the Control class has a single BindingContext object, and the BindingManagerBase object that implements the data binding component in most creation forms is using the FORM class. BindingContext is obtained. The following code is a BindingManagerBase object that is "MyBind" with the Access 2000 database as a model.
// Create an OLEDBCONNECTION
String strcon = "provider = microsoft.jet.Oledb.4.0; data source = db.mdb";
OLEDBCONNECTION MyConn = New OLEDBCONNECTION (STRCON);
String strcom = "select * from person"; file: // Create a DataSet
MyDataSet = new dataset ();
Myconn.open ();
FILE: // Get a dataset with OLEDBDataAdapter
OLEDBDataAdapter mycommand = new oledbdataadapter (strcom, myconn);
FILE: // Bind the DataSet Books Data Sheet
MyCommand.Fill (MyDataSet, "Person");
File: // Turn this OLEDBCONNECTION
MyConn.close ();
mybind = this.bindingContext [MyDataSet, "Person"];
The following code is a BindingManagerBase object named "mybind" as a model with SQL Server 2000 database.
// Set the data connection string, this string means to open the SQL Server database, the server name is Server1, the database is DATA1
String strcon = "provider = sqloledb.1; persist security info = false; user id = sa; initial catalog = data1; data source = server1";
OLEDBCONNECTION MyConn = New OLEDBCONNECTION (STRCON);
Myconn.open ();
String strcom = "SELECT * from Person";
File: // Create a DataSet
MyDataSet = new dataset ();
FILE: // Get a dataset with OLEDBDataAdapter
OLEDBDataAdapter mycommand = new oledbdataadapter (strcom, myconn);
File: // Bind the DataSet Person Data Sheet
MyCommand.Fill (MyDataSet, "Person");
File: // Turn this OLEDBCONNECTION
MyConn.close ();
mybind = this.bindingContext [MyDataSet, "Person"];
A BindingManagerBase object that is the same data source is obtained. By changing the "Position" attribute value of this object, the data that is bound to the component is displayed, thereby implementing navigation data records.
. Navigation button "Previous" implementation method:
Protected Void Goprevious (Object Sender, System.EventArgs E)
{
IF (mybind.position == 0)
Messagebox.show ("has reached the first record!", "Information Tips!", MessageBoxButtonS.ok, MessageBoxicon.information;
Else
Mybind.position - = 1;
}
Protected Void Gonext (Object Sender, System.EventArgs E) {
IF (mybind.position == mybind.count -1)
Messagebox.show ("has reached the last record!", "Information Tips!", MessageBoxButtons.ok, MessageBoxicon.information;
Else
Mybind.position = 1;
}
Protected Void GoLast (Object Sender, System.EventArgs E)
{
Mybind.position = mybind.count - 1;
}
Protected Void Gofirst (Object Sender, System.EventArgs E)
{
Mybind.position = 0;
}
Note: "Count" is another important attribute of the BindingManagerBase object, which is the total number of data set records.
three. Implementation Removal Record:
When the data record is operated, there must be very clear:
One: When you operate on the data record, I think there are some The programmer must have such a doubt that when requesting the dataset of the database server, the "DataSet" object is generated to manage the data set, so if these requests for the database server are very much, there will be a lot. "DataSet" object, it will inevitably make the database server crash. This idea is natural, but it is not practical, because the "dataset" object is not generated at the server, but is generated at the client. So in the face of numerous data requests, the impact on the database server is not very big.
Its two: Remember when writing a three-layer data model with Delphi, each time a modification of the database is actually a modification of the data set generated by the second layer, and must actually modify the database, but also to call An additional method. When using the ADO.NET to process the database, although the direct object is a database, the content in the "DataSet" object does not change, while the binding data component displayed is derived from the "DataSet" object. This will create an illusion, that is, the revised record does not modify, the recorded record does not delete it. Therefore, when you operate the data record, after modifying the database, you must make the necessary modifications to the "DataSet" object so that the "DataSet" object and the database content are consistent, synchronized. The following code is the program code that deletes the record of the current binding component displayed. This code is a template with the Access 2000 database:
protected void delete_record (Object Sender, System.EventArgs E)
{
DialogResult r = messagebox.show ("Do you delete the current record!", "Delete the current record!", MessageBoxButtons.yesno, MessageBoxicon.question; int ss = (int));
IF (ss == 6) // Press the "OK" button
{
Try {
File: // Connect to a database
String strcon = "provider = microsoft.jet.Oledb.4.0; data source = db.mdb";
OLEDBCONNECTION MyConn = New OLEDBCONNECTION (STRCON);
Myconn.open ();
String strde = "delete from person where id =" t_id.text;
OLEDBCommand mycommand = new oledbcommand (strdele, myconn);
File: // Remove the specified record from the database
mycommand.executenonquery ();
File: // Remove the specified record from the DataSet
MyDataSet.tables ["Person"]. Rows [mybind.position]. delete ();
MyDataSet.Tables ["Person"]. acceptchanges ();
MyConn.close ();
}
Catch (Exception ED)
{
Messagebox.show ("Delete Record Error Message:" Ed.toTString (), "Error!");
}
}
}
The following code is the program code that deletes the recorded record displayed by the current binding component. This code is the SQL Server 2000 database as a template, the difference is only the data link:
protected void delete_record (Object Sender, System.EventArgs E)
{
DialogResult r = messagebox.show ("Whether to delete the current record!", "Delete the current record!", MessageBoxButtons.yesno, MessageBoxicon.question;
INT SS = (int))
IF (ss == 6) // Press the "OK" button
{
Try {
// Set the data connection string, meaning open the SQL Server database, the server name is Server1, the database is DATA1
String strcon = "provider = sqloledb.1; persist security info = false; user id = sa; initial catalog = data1; data source = server1";
OLEDBCONNECTION MyConn = New OLEDBCONNECTION (STRCON);
Myconn.open ();
String strde = "delete from person where id =" t_id.text; oledbcommand mycommand = new oledbcommand (strdele, myconn);
File: // Remove the specified record from the database
mycommand.executenonquery ();
File: // Remove the specified record from the DataSet
MyDataSet.tables ["Person"]. Rows [mybind.position]. delete ();
MyDataSet.Tables ["Person"]. acceptchanges ();
MyConn.close ();
}
Catch (Exception ED)
{
Messagebox.show ("Delete Record Error Message:" Ed.toTString (), "Error!");
}
}
}
In this two-segment code, the "DATSSET" object is also necessary to change the database while changing the database.
The following figure is the run interface for deleting the data record in the program:
