用Visual C＃來修改和刪除數據庫記錄

2024-07-21 02:22:30

字體：大中小

來源：轉載

供稿：網友

中國最大的web開發資源網站及技術社區，
一.程序設計和運行的環境設置：
（1）.視窗2000服務器版
（2）.microsoft access data component 2.6 以上版本 ( madc 2.6 )
（3）.本文程序使用的數據庫的介紹：

為了方便起見，在選用數據庫方面選用了本地數據庫access 2000，當然你也可以選用其他類型的數據庫，只需要更改文章后面的程序源代碼中數據庫的引擎，并更改對應的代碼就可以了。本程序中使用的數據庫名稱為sample.mdb，在此數據庫中有一張數據表books。此數據表的結構如下：
字段名稱字段類型代表意思bookid數字序號booktitle文本書籍名稱bookauthor文本書籍作者bookprice數字價格bookstock數字書架號
二.程序設計難點和應該注意的問題：
在程序設計中的重點和難點就是如何用visual c#刪除記錄和如何修改記錄。下面就這二個問題進行必要的論述：
（1）.如何用visual c#正確刪除數據表中的記錄：

在用visual c#刪除記錄的時候要注意的是：必須從二個方面徹底刪除記錄，即從數據庫和用visual c#編程時產生的一個dataset對象中徹底刪除。在程序設計的時候，如果只是刪除了dataset對象中的記錄信息，這種刪除是一種偽刪除。這是因為當他退出程序，又重新運行程序，會發現，那個要刪除的記錄依然還存在。這是因為dataset對象只是對數據表的一個鏡像，并不是真正的記錄本身。但如果只是從數據庫中刪除記錄，因為我們此時程序用到的數據集合是從dataset對象中讀取的，子dataset對象中依然保存此條記錄的鏡像。所以就會發現，我們根本沒有刪除掉記錄，但實際上已經刪除了。此時只有退出程序，重新運行，才會發現記錄已經刪除了。本文使用的方法是刪除以上二個方面的記錄或記錄鏡像信息。當然你也可以使用其他的方法，譬如：首先從數據庫中刪除記錄，然后重新建立數據連接，重新創建一個新的dataset對象。這種方法雖然也可以達到相同目的，但顯然相對繁雜些，所以本文采用的是第一種方法--直接刪除。在程序中具體的實現語句如下：
//連接到一個數據庫
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 ) ;
//從數據庫中刪除指定記錄
mycommand.executenonquery ( ) ;
//從dataset中刪除指定記錄信息
mydataset.tables [ "books" ] . rows [ mybind.position ] . delete ( ) ;
mydataset.tables [ "books" ] . acceptchanges ( ) ;
myconn.close ( ) ;

（2）.用visual c#來修改數據表中的記錄：
在用visual c#修改記錄和刪除記錄，在程序設計中大致差不多，具體的實現方式也是通過sql語句調用來實現的。下面就是在程序中修改記錄的具體語句：
//連接到一個數據庫
string strcon = " provider = microsoft.jet.oledb.4.0 ; data source = sample.mdb " ;
oledbconnection myconn = new oledbconnection ( strcon ) ;
myconn.open ( ) ;

//從數據庫中修改指定記錄
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#刪除和修改記錄以后，結合《visual c＃中輕松瀏覽數據庫記錄》文的內容，就可以得到用visual c#完成刪除和修改數據記錄的比較完整程序代碼，以下是本文中介紹程序的運行后的程序界面：

點擊小圖放大，本文中程序運行后的界面
三.用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 ;
//定義此變量，是判斷組件是否已經綁定數據表中的字段

public dataedit ( ) {
// 對窗體中所需要的內容進行初始化
initializecomponent ( ) ;
//連接到一個數據庫
getconnected ( ) ;
}
//清除程序中用到的所有資源
public override void dispose ( ) {
base.dispose ( ) ;
components.dispose ( ) ;
}
public void getconnected ( )
{
try{
//創建一個 oledbconnection對象
string strcon = " provider = microsoft.jet.oledb.4.0 ; data source = sample.mdb " ;
oledbconnection myconn = new oledbconnection ( strcon ) ;
string strcom = " select * from books " ;
//創建一個 dataset對象
mydataset = new dataset ( ) ;
myconn.open ( ) ;
oledbdataadapter mycommand = new oledbdataadapter ( strcom , myconn ) ;
mycommand.fill ( mydataset , "books" ) ;
myconn.close ( ) ;
//判斷數據字段是否綁定到 textboxes
if ( !isbound )
{
//以下是為顯示數據記錄而把數據表的某個字段綁定在不同的綁定到文本框"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" ) ;
//設定 bindingmanagerbase
//把對象dataset和"books"數據表綁定到此mybind對象
mybind = this.bindingcontext [ mydataset , "books" ] ;
isbound = true ;
}
}
catch ( exception e )
{
messagebox.show ( "連接數據庫發生錯誤為：" + 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 ) ;
//以下是設定在程序中使用到的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 ;

//以下設定程序中用到的功能按鈕的屬性及對應的事件
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#來修改和刪除數據庫中的記錄" ;
label1.size = new system.drawing.size ( 296 , 24 ) ;
label1.forecolor = system.drawing.systemcolors.desktop ;
label1.font = new system.drawing.font ( "宋體" , 14f ) ;
//設定程序的主窗體的屬性
this.text = "用visual c#來修改和刪除數據庫中的記錄！" ;
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 ) ;

}
//"刪除記錄"對應的事件
protected void godelete ( object sender, system.eventargs e )
{
try{
//連接到一個數據庫
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 ) ;
//從數據庫中刪除指定記錄
mycommand.executenonquery ( ) ;
//從dataset中刪除指定記錄
mydataset.tables [ "books" ] . rows [ mybind.position ] . delete ( ) ;
mydataset.tables [ "books" ] . acceptchanges ( ) ;
myconn.close ( ) ;
}
catch ( exception ed )
{
messagebox.show ( "刪除記錄錯誤信息： " + ed.tostring ( ) , "錯誤！" ) ;
}
}
//"修改記錄"按鈕對應的事件
protected void goupdate ( object sender , system.eventargs e )
{
int i = mybind.position ;
try{
//連接到一個數據庫
string strcon = " provider = microsoft.jet.oledb.4.0 ; data source = sample.mdb " ;
oledbconnection myconn = new oledbconnection ( strcon ) ;
myconn.open ( ) ;

//從數據庫中修改指定記錄
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 ;
}
//"尾記錄"按鈕對應的事件
protected void golast ( object sender , system.eventargs e )
{
mybind.position = mybind.count - 1 ;
}
//"下一條"按鈕對應的事件
protected void gonext ( object sender , system.eventargs e )
{
if ( mybind.position == mybind.count - 1 )
messagebox.show ( "已經到尾記錄！" ) ;
else
mybind.position += 1 ;
}
//"上一條"按鈕對應的事件
protected void goprevious ( object sender , system.eventargs e )
{
if ( mybind.position == 0 )
messagebox.show ( "已經到首記錄！" ) ;
else
mybind.position -= 1 ;
}
//"首記錄"按鈕對應的事件
protected void gofirst ( object sender , system.eventargs e )
{
mybind.position = 0 ;
}
}

四.編譯源程序代碼，生成執行文件：
得到了data.cs源程序代碼以后，經過下面編譯命令編譯成功后，即可得到執行文件data.exe：
csc /t:winexe /r:system.windows.forms.dll /r:system.data.dll data.cs
五.總結：
本文主要介紹了如何用visual c#來刪除和修改記錄。以及在處理這些操作的時候，應該注意的一些重要問題及處理的方法。如果你的機器已經滿足本文要求的運行環境，那么在生成的執行文件目錄中建立一個sample.mdb數據庫，在數據庫中創建一個books數據表，數據表的結構可按照本文上面提供的結構來建立，在這一切都完畢后，就可以運行程序，享受visual c#給我們帶來的數據操作的快感了。

上一篇：Connection Strings

下一篇：多維數祖數據綁定！