Use Visual C # to modify and delete database records

visual| Data | database I. Design and operation of the environment settings:
(1). Windows 2000 Server Edition
(2). Microsoft Access Data Component version 2.6 (MADC 2.6)
(3). Introduction to the database used by this program:

For convenience, the use of the database in the selection of local database Access 2000, of course, you can also choose other types of database, only need to change the source code of the following article in the database engine, and change the corresponding code can be. The database name used in this program is Sample.mdb, and there is a datasheet in this database books. The data table is structured as follows:
Field Name field type represents meaning BookID numeric serial number booktitle text book name bookauthor text book author Bookprice digital Price Bookstock Digital Bookshelf
two. Programming difficulties and issues to be noted:
The key and difficulty in programming is how to delete records with Visual C # and how to modify them. Here are some of the two questions that need to be addressed:
(1). How to correctly delete records in a datasheet using Visual C #:

When deleting records in Visual C #, it is important to note that the records must be completely removed from the two aspects, that is, from the database and from a DataSet object that is produced in Visual C # programming. In programming, this deletion is a pseudo deletion if only the record information in the DataSet object is deleted. This is because when he quits the program and runs the program again, he finds that the record to be deleted still exists. This is because the DataSet object is just a mirror image of the datasheet, not the true record itself. But if you just delete the records from the database, because the data set that we are using is read from the DataSet object, the mirror of the record is still saved in the child DataSet object. So it turns out that we didn't delete the records at all, but we actually deleted them. At this point, only exit the program, rerun, you will find that the record has been deleted. The method used in this article is to delete the record or record mirroring information in the above two areas. Of course, you can also use other methods, such as: first delete the record from the database, and then re-establish the data connection, recreate a new DataSet object. Although this method can achieve the same goal, but obviously relatively complex, so this article is the first method-delete directly. The specific implementation statements in the program are as follows:
Connect to a database
String Strcon = "Provider = microsoft.jet.oledb.4.0;" Data Source = Sample.mdb ";
OleDbConnection myconn = new OleDbConnection (Strcon);
MyConn.Open ();
String Strdele = "DELETE from books WHERE bookid=" + t_bookid. Text;
OleDbCommand mycommand = new OleDbCommand (Strdele, myconn);
Delete the specified record from the database
Mycommand.executenonquery ();
Deletes the specified record information from the dataset
Mydataset.tables ["Books"]. Rows [Mybind.position]. Delete ();
Mydataset.tables ["Books"]. AcceptChanges ();
Myconn.close ();

(2). To modify records in a datasheet by using Visual C #:
modifying records and deleting Records in Visual C # is roughly the same in programming, and is implemented through SQL statement calls. Here is the specific statement that modifies the record in the program:
Connect to a database
String Strcon = "Provider = microsoft.jet.oledb.4.0;" Data Source = Sample.mdb ";
OleDbConnection myconn = new OleDbConnection (Strcon);
MyConn.Open ();

Modify the specified record from the database
String STRUPDT = "UPDATE books SET booktitle = '"
+ T_booktitle. Text + "', Bookauthor = '"
+ T_bookauthor. Text + "', Bookprice ="
+ T_bookprice. Text + ", Bookstock ="
+ T_bookstock. Text + "WHERE bookid =" + T_bookid. Text;

OleDbCommand mycommand = new OleDbCommand (STRUPDT, myconn);
Mycommand.executenonquery ();
Myconn.close ();


(3). After you have learned how to delete and modify records in Visual C #, you can get a more complete program code for deleting and modifying data records in Visual C #, combined with the easy browsing of database records in Visual C #. The following is the running program interface for the program described in this article:

Click on small image to enlarge, this article in the program after the operation of the interface
Three. Implement the complete source code for deleting and modifying database records in Visual C #:
Using System;
Using System.Drawing;
Using System.ComponentModel;
Using System.Windows.Forms;
Using System.Data.OleDb;
Using System.Data;
public class Dataedit:form {private System.ComponentModel.Container components;
Private Button Delete;
Private Button Update;
Private Button Lastrec;
Private Button Nextrec;
Private Button Previousrec;
Private Button Firstrec;
Private TextBox T_bookstock;
Private TextBox T_bookprice;
Private TextBox T_bookauthor;
Private TextBox T_booktitle;
Private TextBox T_bookid;
Private Label L_bookstock;
Private Label L_bookprice;
Private Label L_bookauthor;
Private Label L_booktitle;
Private Label L_bookid;
Private Label Label1;
Private System.Data.DataSet myDataSet;
Private BindingManagerBase Mybind;
private bool Isbound = false;
Define this variable to determine if a component has been bound to a field in a datasheet

Public Dataedit () {
Initialize the content that is required in the form
InitializeComponent ();
Connect to a database
Getconnected ();
}
All resources used by the purge program
public override void Dispose () {
Base. Dispose ();
Components. Dispose ();
}
public void getconnected ()
{
try{
Create a OleDbConnection object
String Strcon = "Provider = microsoft.jet.oledb.4.0;" Data Source = Sample.mdb ";
OleDbConnection myconn = new OleDbConnection (Strcon);
String strcom = "SELECT * from Books";
Create a DataSet object
myDataSet = new DataSet ();
MyConn.Open ();
OleDbDataAdapter mycommand = new OleDbDataAdapter (strcom, myconn);
Mycommand.fill (myDataSet, "books");
Myconn.close ();
Determines whether data fields are bound to textboxes
if (!isbound)
{
The following is to bind a field in a datasheet to a different bound to the text box "text" property for displaying data records
T_bookid. Databindings.add ("Text", myDataSet, "Books.bookid");
T_booktitle. Databindings.add ("Text", myDataSet, "Books.booktitle");
T_bookauthor. Databindings.add ("Text", myDataSet, "Books.bookauthor");
T_bookprice. Databindings.add ("Text", myDataSet, "Books.bookprice");
T_bookstock. Databindings.add ("Text", myDataSet, "Books.bookstock");
Set BindingManagerBase
Bind the object dataset and the Books data table to this Mybind object
Mybind = this. BindingContext [myDataSet, "books"];
Isbound = true;
}
}
catch (Exception e)
{
MessageBox.Show ("Connection Database error occurs:" + e.tostring (), "Error!") " ) ;
}
}
public static void Main () {
Application.Run (New Dataedit ());
}
private void InitializeComponent ()
{
this.components = new System.ComponentModel.Container ();
This.t_bookid = new TextBox ();
This.previousrec = new Button ();
This.l_bookauthor = new Label ();
This.delete = new Button ();
This.t_booktitle = new TextBox ();
This.t_bookauthor = new TextBox ();
This.t_bookprice = new TextBox ();
This.l_bookprice = new Label ();
This.t_bookstock = new TextBox ();
This.l_bookstock = new Label ();
This.l_booktitle = new Label ();
This.update = new Button ();
This.nextrec = new Button ();
This.lastrec = new Button ();
This.firstrec = new Button ();
This.label1 = new Label ();
This.l_bookid = new Label ();
T_bookid. Location = new System.Drawing.Point (184, 56);
T_bookid. Size = new System.Drawing.Size (80, 20);

T_booktitle. Location = new System.Drawing.Point (184, 108);
T_booktitle. Size = new System.Drawing.Size (176, 20);

T_bookauthor. Location = new System.Drawing.Point (184, 160);
T_bookauthor. Size = new System.Drawing.Size (128, 20);

T_bookprice. Location = new System.Drawing.Point (184, 212);
T_bookprice. Size = new System.Drawing.Size (80, 20);

T_bookstock. Location = new System.Drawing.Point (184, 264);
T_bookstock. Size = new System.Drawing.Size (80, 20);
The following are the label properties that are set to be used in the program
L_bookid. Location = new System.Drawing.Point (24, 56);
L_bookid. Text = "Serial number:";
L_bookid. Size = new System.Drawing.Size (142, 20);
L_bookid. Font = new System.Drawing.Font ("Song Body", 12f);
L_bookid. TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
L_booktitle. Location = new System.Drawing.Point (24, 108);
L_booktitle. Text = "title:";
L_booktitle. Size = new System.Drawing.Size (142, 20);
L_booktitle. Font = new System.Drawing.Font ("Song Body", 12f);
L_booktitle. TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
L_bookauthor. Location = new System.Drawing.Point (24, 160);
L_bookauthor. Text = "Author:";
L_bookauthor. Size = new System.Drawing.Size (142, 20);
L_bookauthor. Font = new System.Drawing.Font ("Song Body", 12f);
L_bookauthor. TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
L_bookprice. Location = new System.Drawing.Point (24, 212);
L_bookprice. Text = "Price:";
L_bookprice. Size = new System.Drawing.Size (142, 20);
L_bookprice. Font = new System.Drawing.Font ("Song Body", 12f);
L_bookprice. TextAlign = System.Drawing.ContentAlignment.MiddleCenter;

L_bookstock. Location = new System.Drawing.Point (24, 264);
L_bookstock. Text = "Bookshelf number:";
L_bookstock. Size = new System.Drawing.Size (142, 20);
L_bookstock. Font = new System.Drawing.Font ("Song Body", 12f);
L_bookstock. TextAlign = System.Drawing.ContentAlignment.MiddleCenter;

The properties and corresponding events for the function buttons used in the following settings program
Delete. Location = new System.Drawing.Point (104, 352);
Delete. ForeColor = System.Drawing.Color.Black;
Delete. Size = new System.Drawing.Size (80, 24);
Delete. Font = new System.Drawing.Font ("Song Body", 9f);
Delete. Text = "Delete Record";
Delete. Click + + new System.EventHandler (Godelete);

Update. Location = new System.Drawing.Point (204, 352);
Update. ForeColor = System.Drawing.Color.Black;
Update. Size = new System.Drawing.Size (80, 24);
Update. Font = new System.Drawing.Font ("Song Body", 9f);
Update. Text = "Modify Record";
Update. Click + + new System.EventHandler (goupdate);

Firstrec. Location = new System.Drawing.Point (64, 312);
Firstrec. ForeColor = System.Drawing.Color.Black;
Firstrec. Size = new System.Drawing.Size (40, 24);
Firstrec. Font = new System.Drawing.Font ("Song Body", 9f);
Firstrec. Text = "First record";
Firstrec. Click + + new System.EventHandler (Gofirst);

Previousrec. Location = new System.Drawing.Point (136, 312);
Previousrec. ForeColor = System.Drawing.Color.Black;
Previousrec. Size = new System.Drawing.Size (40, 24);
Previousrec. Font = new System.Drawing.Font ("Song Body", 9f);
Previousrec. Text = "previous One";
Previousrec. Click + + new System.EventHandler (goprevious);

Nextrec. Location = new System.Drawing.Point (208, 312);
Nextrec. ForeColor = System.Drawing.Color.Black;
Nextrec. Size = new System.Drawing.Size (40, 24);
Nextrec. Font = new System.Drawing.Font ("Song Body", 9f);
Nextrec. Text = "Next Bar";
Nextrec. Click + + new System.EventHandler (GoNext);

Lastrec. Location = new System.Drawing.Point (280, 312);
Lastrec. ForeColor = System.Drawing.Color.Black;
Lastrec. Size = new System.Drawing.Size (40, 24);
Lastrec. Font = new System.Drawing.Font ("Song Body", 9f);
Lastrec. Text = "tail record";
Lastrec. Click + + new System.EventHandler (golast);

Label1. Location = new System.Drawing.Point (60, 20);
Label1. Text = "Use Visual C # to modify and delete records in a database";
Label1. Size = new System.Drawing.Size (296, 24);
Label1. ForeColor = System.Drawing.SystemColors.Desktop;
Label1. Font = new System.Drawing.Font ("Song Body", 14f);
Set the properties of a program's main form
This. Text = "Use Visual C # to modify and delete records in the database!" " ;
This. AutoScaleBaseSize = new System.Drawing.Size (5, 13);
This. FormBorderStyle = FormBorderStyle.FixedSingle;
This. ClientSize = new System.Drawing.Size (394, 425);
To add a component to the main form
This. Controls.Add (delete);
This. Controls.Add (update);
This. Controls.Add (LASTREC);
This. Controls.Add (NEXTREC);
This. Controls.Add (PREVIOUSREC);
This. Controls.Add (FIRSTREC);
This. Controls.Add (T_bookstock);
This. Controls.Add (T_bookprice);
This. Controls.Add (T_bookauthor);
This. Controls.Add (T_booktitle);
This. Controls.Add (T_bookid);
This. Controls.Add (L_bookstock);
This. Controls.Add (L_bookprice);
This. Controls.Add (L_bookauthor);
This. Controls.Add (L_booktitle);
This. Controls.Add (L_bookid);
This. Controls.Add (Label1);

}
"Delete Record" corresponding event
protected void Godelete (object sender, System.EventArgs e)
{
try{
Connect to a database
String Strcon = "Provider = microsoft.jet.oledb.4.0;" Data Source = Sample.mdb ";
OleDbConnection myconn = new OleDbConnection (Strcon);
MyConn.Open ();
String Strdele = "DELETE from books WHERE bookid=" + t_bookid. Text;
OleDbCommand mycommand = new OleDbCommand (Strdele, myconn);
Delete the specified record from the database
Mycommand.executenonquery ();
Deletes the specified record from the dataset
Mydataset.tables ["Books"]. Rows [Mybind.position]. Delete ();
Mydataset.tables ["Books"]. AcceptChanges ();
Myconn.close ();
}
catch (Exception ed)
{
MessageBox.Show ("Delete logging error message:" + ed.) ToString (), "Error!" " ) ;
}
}
Events for the Modify record button
protected void Goupdate (object sender, System.EventArgs e)
{
int i = mybind.position;
try{
Connect to a database
String Strcon = "Provider = microsoft.jet.oledb.4.0;" Data Source = Sample.mdb ";
OleDbConnection myconn = new OleDbConnection (Strcon);
MyConn.Open ();

Modify the specified record from the database
String STRUPDT = "UPDATE books SET booktitle = '"
+ T_booktitle. Text + "', Bookauthor = '"
+ T_bookauthor. Text + "', Bookprice ="
+ T_bookprice. Text + ", Bookstock ="
+ T_bookstock. Text + "WHERE bookid =" + T_bookid. Text;

OleDbCommand mycommand = new OleDbCommand (STRUPDT, myconn);
Mycommand.executenonquery ();
Myconn.close ();
}
catch (Exception ed)
{
MessageBox.Show ("Modify the specified record error:" + ed.) ToString (), "Error!" " ) ;
}
Mybind.position = i;
}
Events for the "tail record" button
protected void Golast (object sender, System.EventArgs e)
{
Mybind.position = mybind.count-1;
}
Events corresponding to the Next button
protected void GoNext (object sender, System.EventArgs e)
{
if (mybind.position = = mybind.count-1)
MessageBox.Show ("Already to the tail record!") " ) ;
Else
Mybind.position + 1;
}
Events corresponding to the previous button
protected void Goprevious (object sender, System.EventArgs e)
{
if (mybind.position = 0)
MessageBox.Show ("Already to the first record!") " ) ;
Else
Mybind.position-= 1;
}
Events for the "first record" button
protected void Gofirst (object sender, System.EventArgs e)
{
mybind.position = 0;
}
}

four. Compile source code and generate execution file:
After the Data.cs source code is obtained, the following compile command compiles successfully to get the execution file Data.exe:
Csc/t:winexe/r:system.windows.forms.dll/r:system.data.dll Data.cs
Five. Summary:
This article mainly describes how to use Visual C # to delete and modify records. And in dealing with these operations, should pay attention to some of the important issues and treatment methods. If your machine has met the operating environment required by this article, Then build a Sample.mdb database in the generated execution file directory, create a Books data table in the database, the structure of the datasheet can be built according to the structure provided above, and after all this is done, you can run the program and enjoy Visual C # Bring us the thrill of the data manipulation.