作者：王天

在以前的一篇文章《Visual C＃中輕松瀏覽數(shù)據(jù)庫記錄》中，我們介紹了用Visual C#如何把數(shù)據(jù)表中的字段值綁定到文本框的屬性上和如何操作數(shù)據(jù)記錄指針，隨意瀏覽數(shù)據(jù)表中的記錄。本文就接著上一篇的內(nèi)容，來介紹用Visual C#如何來修改和刪除數(shù)據(jù)記錄。

一.程序設(shè)計和運(yùn)行的環(huán)境設(shè)置：
（1）.視窗2000服務(wù)器版
（2）.Microsoft Access Data Component 2.6 以上版本 ( MADC 2.6 )
（3）.本文程序使用的數(shù)據(jù)庫的介紹：

為了方便起見，在選用數(shù)據(jù)庫方面選用了本地數(shù)據(jù)庫Access 2000，當(dāng)然你也可以選用其他類型的數(shù)據(jù)庫，只需要更改文章后面的程序源代碼中數(shù)據(jù)庫的引擎，并更改對應(yīng)的代碼就可以了。本程序中使用的數(shù)據(jù)庫名稱為sample.mdb，在此數(shù)據(jù)庫中有一張數(shù)據(jù)表books。此數(shù)據(jù)表的結(jié)構(gòu)如下：
字段名稱 字段類型 代表意思
Bookid 數(shù)字 序號
booktitle 文本 書籍名稱
bookauthor 文本 書籍作者
bookprice 數(shù)字 價格
bookstock 數(shù)字 書架號

二.程序設(shè)計難點(diǎn)和應(yīng)該注意的問題：
在程序設(shè)計中的重點(diǎn)和難點(diǎn)就是如何用Visual C#刪除記錄和如何修改記錄。下面就這二個問題進(jìn)行必要的論述：
（1）.如何用Visual C#正確刪除數(shù)據(jù)表中的記錄：

在用Visual C#刪除記錄的時候要注意的是：必須從二個方面徹底刪除記錄，即從數(shù)據(jù)庫和用Visual C#編程時產(chǎn)生的一個DataSet對象中徹底刪除。在程序設(shè)計的時候，如果只是刪除了DataSet對象中的記錄信息，這種刪除是一種偽刪除。這是因為當(dāng)他退出程序，又重新運(yùn)行程序，會發(fā)現(xiàn)，那個要刪除的記錄依然還存在。這是因為DataSet對象只是對數(shù)據(jù)表的一個鏡像，并不是真正的記錄本身。但如果只是從數(shù)據(jù)庫中刪除記錄，因為我們此時程序用到的數(shù)據(jù)集合是從DataSet對象中讀取的，子DataSet對象中依然保存此條記錄的鏡像。所以就會發(fā)現(xiàn)，我們根本沒有刪除掉記錄，但實(shí)際上已經(jīng)刪除了。此時只有退出程序，重新運(yùn)行，才會發(fā)現(xiàn)記錄已經(jīng)刪除了。本文使用的方法是刪除以上二個方面的記錄或記錄鏡像信息。當(dāng)然你也可以使用其他的方法，譬如：首先從數(shù)據(jù)庫中刪除記錄，然后重新建立數(shù)據(jù)連接，重新創(chuàng)建一個新的DataSet對象。這種方法雖然也可以達(dá)到相同目的，但顯然相對繁雜些，所以本文采用的是第一種方法--直接刪除。在程序中具體的實(shí)現(xiàn)語句如下：
//連接到一個數(shù)據(jù)庫
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 ) ;
//從數(shù)據(jù)庫中刪除指定記錄
myCommand.ExecuteNonQuery ( ) ;
//從DataSet中刪除指定記錄信息
myDataSet.Tables [ "books" ] . Rows [ myBind.Position ] . Delete ( ) ;
myDataSet.Tables [ "books" ] . AcceptChanges ( ) ;
myConn.Close ( ) ;

（2）.用Visual C#來修改數(shù)據(jù)表中的記錄：
在用Visual C#修改記錄和刪除記錄，在程序設(shè)計中大致差不多，具體的實(shí)現(xiàn)方式也是通過SQL語句調(diào)用來實(shí)現(xiàn)的。下面就是在程序中修改記錄的具體語句：
//連接到一個數(shù)據(jù)庫
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = sample.mdb " ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
myConn.Open ( ) ;

//從數(shù)據(jù)庫中修改指定記錄
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）.在了解了如何用Visual C#刪除和修改記錄以后，結(jié)合《Visual C＃中輕松瀏覽數(shù)據(jù)庫記錄》文的內(nèi)容，就可以得到用Visual C#完成刪除和修改數(shù)據(jù)記錄的比較完整程序代碼，以下是本文中介紹程序的運(yùn)行后的程序界面：


點(diǎn)擊小圖放大，本文中程序運(yùn)行后的界面

三.用Visual C#實(shí)現(xiàn)刪除和修改數(shù)據(jù)庫記錄的完整源程序代碼：
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 ;
//定義此變量，是判斷組件是否已經(jīng)綁定數(shù)據(jù)表中的字段

public DataEdit ( ) {
// 對窗體中所需要的內(nèi)容進(jìn)行初始化
InitializeComponent ( ) ;
//連接到一個數(shù)據(jù)庫
GetConnected ( ) ;
}
//清除程序中用到的所有資源
public override void Dispose ( ) {
base.Dispose ( ) ;
components.Dispose ( ) ;
}
public void GetConnected ( )
{
try{
//創(chuàng)建一個 OleDbConnection對象
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = sample.mdb " ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
string strCom = " SELECT * FROM books " ;
//創(chuàng)建一個 DataSet對象
myDataSet = new DataSet ( ) ;
myConn.Open ( ) ;
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;
myCommand.Fill ( myDataSet , "books" ) ;
myConn.Close ( ) ;
//判斷數(shù)據(jù)字段是否綁定到 TextBoxes
if ( !isBound )
{
//以下是為顯示數(shù)據(jù)記錄而把數(shù)據(jù)表的某個字段綁定在不同的綁定到文本框"Text"屬性上
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" ) ;
//設(shè)定 BindingManagerBase
//把對象DataSet和"books"數(shù)據(jù)表綁定到此myBind對象
myBind = this.BindingContext [ myDataSet , "books" ] ;
isBound = true ;
}
}
catch ( Exception e )
{
MessageBox.Show ( "連接數(shù)據(jù)庫發(fā)生錯誤為：" + e.ToString ( ) , "錯誤！" ) ;
}
}
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 ) ;
//以下是設(shè)定在程序中使用到的Label屬性
l_bookid.Location = new System.Drawing.Point ( 24 , 56 ) ;
l_bookid.Text = "序 號：" ;
l_bookid.Size = new System.Drawing.Size ( 142 , 20 ) ;
l_bookid.Font = new System.Drawing.Font ( "宋體" , 12f ) ;
l_bookid.TextAlign = System.Drawing.ContentAlignment.MiddleCenter ;
l_booktitle.Location = new System.Drawing.Point ( 24 , 108 ) ;
l_booktitle.Text = "書 名：" ;
l_booktitle.Size = new System.Drawing.Size ( 142 , 20 ) ;
l_booktitle.Font = new System.Drawing.Font ( "宋體" , 12f ) ;
l_booktitle.TextAlign = System.Drawing.ContentAlignment.MiddleCenter ;
l_bookauthor.Location = new System.Drawing.Point ( 24 , 160 ) ;
l_bookauthor.Text = "作 者：";
l_bookauthor.Size = new System.Drawing.Size ( 142 , 20 ) ;
l_bookauthor.Font = new System.Drawing.Font ( "宋體" , 12f ) ;
l_bookauthor.TextAlign = System.Drawing.ContentAlignment.MiddleCenter ;
l_bookprice.Location = new System.Drawing.Point ( 24 , 212 ) ;
l_bookprice.Text = "價 格：" ;
l_bookprice.Size = new System.Drawing.Size ( 142 , 20 ) ;
l_bookprice.Font = new System.Drawing.Font ( "宋體" , 12f ) ;
l_bookprice.TextAlign = System.Drawing.ContentAlignment.MiddleCenter ;

l_bookstock.Location = new System.Drawing.Point ( 24 , 264 ) ;
l_bookstock.Text = "書 架 號：" ;
l_bookstock.Size = new System.Drawing.Size ( 142 , 20 ) ;
l_bookstock.Font = new System.Drawing.Font ( "宋體" , 12f ) ;
l_bookstock.TextAlign = System.Drawing.ContentAlignment.MiddleCenter ;

//以下設(shè)定程序中用到的功能按鈕的屬性及對應(yīng)的事件
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 ( "宋體" , 9f ) ;
delete.Text = "刪除記錄" ;
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 ( "宋體" , 9f ) ;
update.Text = "修改記錄" ;
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 ( "宋體" , 9f ) ;
firstrec.Text = "首記錄" ;
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 ( "宋體" , 9f ) ;
previousrec.Text = "上一條" ;
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 ( "宋體" , 9f ) ;
nextrec.Text = "下一條" ;
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 ( "宋體" , 9f ) ;
lastrec.Text = "尾記錄" ;
lastrec.Click += new System.EventHandler ( GoLast ) ;

label1.Location = new System.Drawing.Point ( 60 , 20 ) ;
label1.Text = "用Visual C#來修改和刪除數(shù)據(jù)庫中的記錄" ;
label1.Size = new System.Drawing.Size ( 296 , 24 ) ;
label1.ForeColor = System.Drawing.SystemColors.Desktop ;
label1.Font = new System.Drawing.Font ( "宋體" , 14f ) ;
//設(shè)定程序的主窗體的屬性
this.Text = "用Visual C#來修改和刪除數(shù)據(jù)庫中的記錄！" ;
this.AutoScaleBaseSize = new System.Drawing.Size ( 5 , 13 ) ;
this.FormBorderStyle = FormBorderStyle.FixedSingle ;
this.ClientSize = new System.Drawing.Size ( 394 , 425 ) ;
//在主窗體中加入組件
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 ) ;

}
//"刪除記錄"對應(yīng)的事件
protected void GoDelete ( object sender, System.EventArgs e )
{
try{
//連接到一個數(shù)據(jù)庫
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 ) ;
//從數(shù)據(jù)庫中刪除指定記錄
myCommand.ExecuteNonQuery ( ) ;
//從DataSet中刪除指定記錄
myDataSet.Tables [ "books" ] . Rows [ myBind.Position ] . Delete ( ) ;
myDataSet.Tables [ "books" ] . AcceptChanges ( ) ;
myConn.Close ( ) ;
}
catch ( Exception ed )
{
MessageBox.Show ( "刪除記錄錯誤信息： " + ed.ToString ( ) , "錯誤！" ) ;
}
}
//"修改記錄"按鈕對應(yīng)的事件
protected void GoUpdate ( object sender , System.EventArgs e )
{
int i = myBind.Position ;
try{
//連接到一個數(shù)據(jù)庫
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = sample.mdb " ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
myConn.Open ( ) ;

//從數(shù)據(jù)庫中修改指定記錄
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 ( "修改指定記錄錯誤： " + ed.ToString ( ) , "錯誤！" ) ;
}
myBind.Position = i ;
}
//"尾記錄"按鈕對應(yīng)的事件
protected void GoLast ( object sender , System.EventArgs e )
{
myBind.Position = myBind.Count - 1 ;
}
//"下一條"按鈕對應(yīng)的事件
protected void GoNext ( object sender , System.EventArgs e )
{
if ( myBind.Position == myBind.Count - 1 )
MessageBox.Show ( "已經(jīng)到尾記錄！" ) ;
else
myBind.Position += 1 ;
}
//"上一條"按鈕對應(yīng)的事件
protected void GoPrevious ( object sender , System.EventArgs e )
{
if ( myBind.Position == 0 )
MessageBox.Show ( "已經(jīng)到首記錄！" ) ;
else
myBind.Position -= 1 ;
}
//"首記錄"按鈕對應(yīng)的事件
protected void GoFirst ( object sender , System.EventArgs e )
{
myBind.Position = 0 ;
}
}


四.編譯源程序代碼，生成執(zhí)行文件：
得到了Data.cs源程序代碼以后，經(jīng)過下面編譯命令編譯成功后，即可得到執(zhí)行文件data.exe：
csc /t:winexe /r:system.windows.forms.dll /r:system.data.dll data.cs

五.總結(jié)：
本文主要介紹了如何用Visual C#來刪除和修改記錄。以及在處理這些操作的時候，應(yīng)該注意的一些重要問題及處理的方法。如果你的機(jī)器已經(jīng)滿足本文要求的運(yùn)行環(huán)境，那么在生成的執(zhí)行文件目錄中建立一個sample.mdb數(shù)據(jù)庫，在數(shù)據(jù)庫中創(chuàng)建一個books數(shù)據(jù)表，數(shù)據(jù)表的結(jié)構(gòu)可按照本文上面提供的結(jié)構(gòu)來建立，在這一切都完畢后，就可以運(yùn)行程序，享受Visual C#給我們帶來的數(shù)據(jù)操作的快感了。