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 version (2) .microsoft Data Acze Component 2.6 or more version (MDAC 2.6) (3) .. Net Framework SDK Beta 2 For more clear instructions, in the database On the selection, the currently a 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", in which the data structure of "Person", "Person" table is defined in the table: Field Name Field Type Field Meaning ID Digital Sequence XM Text Name XB Text Gender NL Text Age Zip Text Postal Code Remote Database SQL Server 2000 Database Server Name is "Server1", the database name is "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 "Visual C # Data Binding", you have learned how to bind some fields in the data set to an attribute of the WinForm component, so that the programmer can be customized according to the WinForm component. Data display form, 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 a 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 OLEDBDATAADAPTER MyCommand = New OLEDBDataAdapter (STRCOM, MyConn); file: // Binds the DataSet Books Datasheet MyCommand. Fill (MyDataSet, "Person"); file: // Close this OLEDBConnection myconn.close (); mybind = this.bindingContext [myDataSet, "Person"]; the following code is to create a name as the SQL Server 2000 database. "Mybind" BindingManagerBase object. // 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 = "proviker = SQLOLEDB.1; PERSIST security info = false; user id = sa; initial Catalog = DATA1; DATA Source = Server1 "; OLEDBConnection myconn = new oledbconnection (strcon); myconn.open (); string strands =" select * from person "; file: // Create a Dataset MyDataSet = New DataSet (); File : // Get a data set OLEDBDataAdapter mycommand = new oledbdataadapter (STRCOM, MyConn); file: // Binds DataSet Person Data Table MyCommand.Fill (MyDataSet, "Person"); file: // Turn this OLEDBCONNECTION MyConn .Close (); mybind = this.bindingContext [MyDataSet, "Person"]; BindingManagerBase object with the same data source, by changing the "position" attribute value of this object, the data of the components that bind the data displayed Variations to achieve 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;} 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 the Access 2000 database as template: protected void delete_record (Object sender, system.eventargs e) {DialogResult r = messagebox .Show ("Whether to delete the current record!", "Delete the current record!", MessageButtons.yesno, messageboxicon.question; int s = (int) r; 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 strdle = "Delete from person where id =" t_id.text; oledbcommand mycommand = new oledbcommand (strdle, myconn); file: // Remove the specified record in the database MyCommand.executenonQuery (); file: // Remove the specified from DataSet Record myDataSet.Tables ["Person"]. Rows [mybind.position]. Delete (); mydataset.tables ["person"]. Acceptchange (); 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 of the current binding component, this code is SQL Server 2000 database is template, the difference between the two is only in data link: protecte D void delete_record (Object sender, system.eventargs e) {DialogResult r = messagebox.show ("Do you delete the current record! "," Delete the current record! ", Messageboxicon.quest; int s = (int)); if (ss == 6) // Press the" OK "button {TRY {// Set the data connection string, meaning open SQL Server database, server name is Server1, 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 strDele = "DELETE FROM person WHERE id =" t_id.Text; OleDbCommand myCommand = new OleDbCommand (strDele, myConn); file: // from the database Delete Specify Record MyCommand.executenonQuery (); file: // Remove the specified record in the DataSet MYDATASET.TABLES ["Person"]. Rows [mybind.position]. Delete (); mydataset.tables ["Person"]. Acceptchange ); MyConn.close ();} catch (exception ed) {messagebox.show ("Delete Record error message:" ed.tostring (), "error!");}}} In this two-segment code, the "DatsSet" object is also necessary to modify the "Datset" object while changing the database. The following figure is the run interface for deleting the data record in the program: four. Insert data record: The basic idea of inserting a record operation and deleting a record operation is consistent, that is, through ADO.NET to first insert data record to the database, and then make the necessary modifications to the "DataSet" object . The following code is to modify the current record as a model with Access 2000 database: protected void update_record (Object sender, system.eventargs e) {int i = mybind.position; try {file: // Connect to A database strong strcon = "provider = microsoft.jet.Oledb.4.0; data source = db.mdb"; OLEDBConnection myconn = new oledbconnection (STRCON); myconn.open (); mydataset.tables ["Person"]. ROWS [ Mybind.position]. BeGineDit (); file: // Modify the specified record from the database string strunt = "Update Person Set XM = '" T_Xm.Text ", XB ='" T_XB.Text ", NL = " t_nl.text ", zip = " t_books.text " where id = " t_id.text; oledbcommand mycommand = new oledbcommand (structt, myconn); mycommand.executenonquery (); mydataset.tables [" "] rows [mybind.position]. Endedit (); mydataset.tables [" person "]. acceptchange (); myconn.close ();} catch (exception ed) {messagebox.show (" Modify specified record error : " Ed.tostring ()," Error! ");} Mybind.position = i;} Due to the difference between SQL Server 2000 data record modification and Access 2000 data record modification operation only Different data Link, the specific code can refer to the code in "Delete Data Record", which is not available here. Fives. Insert data record: and the previous two operations are consistent in the idea, which is to first insert data into the database via ADO.NET, and then make the necessary modifications to the "DataSet" object. The following code is to insert a data record with the Access 2000 database to the model protected void insert_record (Object sender, system.eventargs e) {try: // During all fields, it is completed, and the other is executed, and the prompt IF. (T_id.text! = "" && t_xm.text! = "&& t_xb.text! =" && t_nl.text! = "&& t_books.text! =" ") {String myconn1 =" provider = Microsoft. Jet.OLEDb.4.0; data source = db.mdb "; OLEDBConnection myconn = new oledbconnection (MyConn1); myconn.open (); string strinsert =" Insert Into Person (ID, XM, XB, NL, Zip) VALUES (" ; Strinsert = T_ID.Text ", '"; Strinsert = T_XM.Text "" "; Strinsert = T_Xb.Text " ""; Strinsert = T_nl.Text ","; Strinsert = T_books.text ")"; OLEDBCOMMAND INST = New OLEDBCOMMAND (Strinsert, MyConn); inst.executenonque (); 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! ");}} Catch (Exception ED) {MessageBox.show (" Save Data Record " Ed.toTString ()," Error! ");}} The SQL Server 2000 database is also inserted into record operations and Access 2000 databases The difference between the insertion record operation is only only for different data links, and the specific code can refer to the code in "Delete Data Record", which is not available here. six. Visual C # database programming completion source code and program running main interface: Master the above points, write a complete database programming program is very easy, below is the full code of database programming of Visual C # (data01.cs) This code is designed with the Access 2000 database as the model, as follows: use system; use system.drawing; using system.componentmodel; using system.windows.Forms; use system.data.Oledb; use system.data; public class Data: Form {private System.ComponentModel.Container components = null; private Button lastrec; private Button nextrec; private Button previousrec; private Button firstrec; private TextBox t_books; private TextBox t_nl; private ComboBox t_xb; private TextBox t_xm; private TextBox t_id ; private Label l_books; private Label l_nl; private Label l_xb; private Label l_xm; private Label l_id; private Label label1; private DataSet myDataSet; private Button button1; private Button button2; private Button button3; private Button button4; private BindingManagerBase myBind; public Data () {file: // Connect to a database getConnected (); // InitializeComponent () to the content required in the form;} file: // Clear in the program Used Resources Protected Override Void Dispose (Bool Disposing) {if (disponents! = Null) {components.dispose ();}}}}}}}}} .Run (new data ());} public void getConnected () {try {file: // 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 data set OLEDBDataAdapter myCommand = New OLEDBDataAdapter (STRCOM, MyConn); file: // Binds DataSet Books Data Table MyCommand.Fill (MyDataSet, "Person"); File: // Turns this OLEDBCONNECTION MyConn.close ();} catch (Exception E) { Messagebox.show ("Connection Error!" E.TOString (), "Error");}} private void initializationComponent () {file: // Add control, slight this.name = "data"; this.text = " Visual C # database programming! "; This.ResumeLayout (false); mybind = this.bindingContext [myDataSet," Person "];} protected void new_record (Object sender, system.eventargs e) {t_id.text = (MyBind. Count 1) .tostring (); t_xm.text = ""; t_xb.text = "; t_nl.text ="; t_books.text = "";} protected void insert_record (Object Sender, System.EventArgs E) {TRY {file: // Determines if all fields are added, then the addition is executed, the reverse pop-up prompt IF (t_id.text! = "&& t_xm.text! =" && t_xb.text! = "&& t_nl. TEXT! = "&& t_books.text! =" ") {String myconn1 =" provider = microsoft.jet.Oledb.4.0; data source = db.mdb "; oledbconnection myconn = new oledbconnection (MyConn1); MyConn.open String ST RINSERT = "INSERT INTO PERSON (ID, XM, XB, NL, ZIP) VALUES ("; Strinsert = T_ID.Text ", '"; Strinsert = T_Xm.Text "', '"; Strinsert = T_XB .Text "',"; Strinsert = T_nl.text ","; Strinsert = T_Books.Text ")"; OLEDBCOMMAND INST = New OLEDBCOMMAND (Strinsert, MyConn); inst.executenonque (); myconn.close (); MyDataSet.Tables ["Person"]. BegineDit (); mydataset.tables ["person"]. rows [mybind.position]. endedit (); mydataset.tables ["Person"]. ACCEPTCHANGES ();} Else {messagebox.show ("must fill all field values!", "Error!");}} Catch (Exception ED) {MessageBox.show ("Save Data Record" Ed.toString () , "Error!");}} Protected void update_record (Object sender, system.eventargs e) {int i = mybind.position; try {file: // Connect to a database strCon = "provike = microsoft.jet.Oledb .4.0; Data Source = db.mdb "; OLEDBConnection myconn = new oledbconnection (strcon); myconn.open (); mydataset.tables [" person "]. Rows [mybind.position]. BegineDit (); file: // Modify the specified record from the database string strupdt = "Update Person Set XM = '" T_XM.Text ", XB ='" T_XB.Text ", NL =" T_NL.Text ", Zip =" T_Books.Text "Where id =" t_id.text; oledbcommand mycommand = new oledbcommand (structt, myconn); mycommand.executenonquery (); mydataset.tables ["P Erson "]. rows [mybind.position]. Endedit (); mydataset.tables [" person "]. acceptchange (); myconn.close ();} catch (exception ed) {messagebox.show (" Modify specified record error : " Ed.toString ()," Error! ");} Mybind.position = i;} 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) r; if (ss == 6) // Press the "OK" button {Try {file: // Connect to a database strcon = "provider = microsoft.jet.OleDb.4.0; data source = db.mdb "; OleDbConnection myConn = new OleDbConnection (strCon); myConn.Open (); string strDele =" DELETE FROM person WHERE id = " t_id.Text; OleDbCommand myCommand = new OleDbCommand (strDele, myConn); file: // Remove the specified record in the database MyCommand.executenonQuery (); file: // Remove the specified record in the DataSet MyDataSet.Tables ["Person"]. Rows [mybind.position]. Delete (); mydataset.tables ["Person "]. Acceptchange (); myconn.close ();} catch (exception ed) {messagebox.show (" Delete Record error message: " ed.tostring ()," Error! ");}}} File: / / Button "Tail Record" Opcher Event Program Protected Void Golast (Object Sender, System.EventArgs E) {MyBind.position = MyBind.count - 1;} File: // Button "Next" Objects Protage Protected Void Gonext (Object Sender, System.EventArgs E) {if (mybind.position == mybind.count -1) MessageBox.show ("has arrived in the last record!", "Information Tips!", MessageBoxButtons.ok, MessageBoxicon.i Nformation; else mybind.position = 1;} file: // button "Previous" Object Event Program Protected Void Goprevious (Object Sender, System.EventArgs E) {if (MyBind.Position == 0) MessageBox.show "I have reached the first record! " , "message notification! ", Messageboxicon.information; else mybind.position - = 1;} file: // button" first record "object event program protected void gofirst (Object sender, system.eventargs e) {mybind.position = 0 }} For program code with the SQL Server 2000 database as the model, just put the data link in DATA01.CS, namely: string myconn1 = "
