熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

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

2013-11-13 11:07:38  來源: .NET編程 
 在前面的一篇文章《Visual C#中輕松浏覽數據庫記錄》中我們介紹了用Visual C#如何把數據表中的字段值綁定到文本框的屬性上和如何操作數據記錄指針隨意浏覽數據表中的記錄本文就接著上一篇的內容來介紹用Visual C#如何來修改和刪除數據記錄

  程序設計和運行的環境設置

  ()視窗服務器版
  ()Microsoft Access Data Component 以上版本 ( MADC )
  ()本文程序使用的數據庫的介紹

  為了方便起見在選用數據庫方面選用了本地數據庫Access 當然你也可以選用其他類型的數據庫只需要更改文章後面的程序源代碼中數據庫的引擎並更改對應的代碼就可以了

  本程序中使用的數據庫名稱為samplemdb在此數據庫中有一張數據表books此數據表的結構如下

  字段名稱   字段類型   代表意思
  Bookid     數字     序號
  booktitle    文本    書籍名稱
  bookauthor   文本    書籍作者
  bookprice    數字     價格
  bookstock    數字    書架號

  二程序設計難點和應該注意的問題

  在程序設計中的重點和難點就是如何用Visual C#刪除記錄和如何修改記錄下面就這二個問題進行必要的論述

  ()如何用Visual C#正確刪除數據表中的記錄

  在用Visual C#刪除記錄的時候要注意的是必須從二個方面徹底刪除記錄即從數據庫和用Visual C#編程時產生的一個DataSet對象中徹底刪除在程序設計的時候如果只是刪除了DataSet對象中的記錄信息這種刪除是一種偽刪除這是因為當他退出程序又重新運行程序會發現那個要刪除的記錄依然還存在這是因為DataSet對象只是對數據表的一個鏡像並不是真正的記錄本身但如果只是從數據庫中刪除記錄因為我們此時程序用到的數據集合是從DataSet對象中讀取的子DataSet對象中依然保存此條記錄的鏡像所以就會發現我們根本沒有刪除掉記錄但實際上已經刪除了此時只有退出程序重新運行才會發現記錄已經刪除了本文使用的方法是刪除以上二個方面的記錄或記錄鏡像信息當然你也可以使用其他的方法譬如首先從數據庫中刪除記錄然後重新建立數據連接重新創建一個新的DataSet對象這種方法雖然也可以達到相同目的但顯然相對繁雜些所以本文采用的是第一種方法直接刪除在程序中具體的實現語句如下

//連接到一個數據庫
string strCon = Provider = MicrosoftJetOLEDB ; Data Source = samplemdb ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
myConnOpen ( ) ;
string strDele = DELETE FROM books WHERE bookid= + t_bookidText ;
OleDbCommand myCommand = new OleDbCommand ( strDele myConn ) ;
//從數據庫中刪除指定記錄
myCommandExecuteNonQuery ( ) ;
//從DataSet中刪除指定記錄信息
myDataSetTables [ books ] Rows [ myBindPosition ] Delete ( ) ;
myDataSetTables [ books ] AcceptChanges ( ) ;
myConnClose ( ) ;

  ()用Visual C#來修改數據表中的記錄

  在用Visual C#修改記錄和刪除記錄在程序設計中大致差不多具體的實現方式也是通過SQL語句調用來實現的下面就是在程序中修改記錄的具體語句

//連接到一個數據庫
string strCon = Provider = MicrosoftJetOLEDB ; Data Source = samplemdb ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
myConnOpen ( ) ;

//從數據庫中修改指定記錄
string strUpdt = UPDATE books SET booktitle =
+ t_booktitleText + bookauthor =
+ t_bookauthorText + bookprice =
+ t_bookpriceText + bookstock =
+ t_bookstockText + WHERE bookid = + t_bookidText ;

OleDbCommand myCommand = new OleDbCommand ( strUpdt myConn ) ;
myCommandExecuteNonQuery ( ) ;
myConnClose ( ) ;

  ()在了解了如何用Visual C#刪除和修改記錄以後結合《Visual C#中輕松浏覽數據庫記錄》一文的內容就可以得到用Visual C#完成刪除和修改數據記錄的比較完整程序代碼以下是本文中介紹程序的運行後的程序界面


點擊小圖放大本文中程序運行後的界面

  三用Visual C#實現刪除和修改數據庫記錄的完整源程序代碼

using System ;
using SystemDrawing ;
using SystemComponentModel ;
using SystemWindowsForms ;
using SystemDataOleDb ;
using SystemData ;
public class DataEdit : Form { private SystemComponentModelContainer 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 label ;
private SystemDataDataSet myDataSet ;
private BindingManagerBase myBind ;
private bool isBound = false ;
//定義此變量是判斷組件是否已經綁定數據表中的字段

public DataEdit ( ) {
// 對窗體中所需要的內容進行初始化
InitializeComponent ( ) ;
//連接到一個數據庫
GetConnected ( ) ;
}
//清除程序中用到的所有資源
public override void Dispose ( ) {
baseDispose ( ) ;
componentsDispose ( ) ;
}
public void GetConnected ( )
{
try{
//創建一個 OleDbConnection對象
string strCon = Provider = MicrosoftJetOLEDB ; Data Source = samplemdb ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
string strCom = SELECT * FROM books ;
//創建一個 DataSet對象
myDataSet = new DataSet ( ) ;
myConnOpen ( ) ;
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom myConn ) ;
myCommandFill ( myDataSet books ) ;
myConnClose ( ) ;
//判斷數據字段是否綁定到 TextBoxes
if ( !isBound )
{
//以下是為顯示數據記錄而把數據表的某個字段綁定在不同的綁定到文本框Text屬性上
t_bookidDataBindingsAdd ( Text myDataSet booksbookid ) ;
t_booktitleDataBindingsAdd ( Text myDataSet booksbooktitle ) ;
t_bookauthorDataBindingsAdd ( Text myDataSet booksbookauthor ) ;
t_bookpriceDataBindingsAdd ( Text myDataSet booksbookprice ) ;
t_bookstockDataBindingsAdd ( Text myDataSet booksbookstock ) ;
//設定 BindingManagerBase
//把對象DataSet和books數據表綁定到此myBind對象
myBind = thisBindingContext [ myDataSet books ] ;
isBound = true ;
}
}
catch ( Exception e )
{
MessageBoxShow ( 連接數據庫發生錯誤為 + eToString ( ) 錯誤! ) ;
}
}
public static void Main ( ) {
ApplicationRun ( new DataEdit ( ) ) ;
}
private void InitializeComponent ( )
{
thiscomponents = new SystemComponentModelContainer ( ) ;
thist_bookid = new TextBox ( ) ;
thispreviousrec = new Button ( ) ;
thisl_bookauthor = new Label ( ) ;
thisdelete = new Button ( ) ;
thist_booktitle = new TextBox ( ) ;
thist_bookauthor = new TextBox ( ) ;
thist_bookprice = new TextBox ( ) ;
thisl_bookprice = new Label ( ) ;
thist_bookstock = new TextBox ( ) ;
thisl_bookstock = new Label ( ) ;
thisl_booktitle = new Label ( ) ;
thisupdate = new Button ( ) ;
thisnextrec = new Button ( ) ;
thislastrec = new Button ( ) ;
thisfirstrec = new Button ( ) ;
thislabel = new Label ( ) ;
thisl_bookid = new Label ( ) ;
t_bookidLocation = new SystemDrawingPoint ( ) ;
t_bookidSize = new SystemDrawingSize ( ) ;

t_booktitleLocation = new SystemDrawingPoint ( ) ;
t_booktitleSize = new SystemDrawingSize ( ) ;

t_bookauthorLocation = new SystemDrawingPoint ( ) ;
t_bookauthorSize = new SystemDrawingSize ( ) ;

t_bookpriceLocation = new SystemDrawingPoint ( ) ;
t_bookpriceSize = new SystemDrawingSize ( ) ;

t_bookstockLocation = new SystemDrawingPoint ( ) ;
t_bookstockSize = new SystemDrawingSize ( ) ;
//以下是設定在程序中使用到的Label屬性
l_bookidLocation = new SystemDrawingPoint ( ) ;
l_bookidText = 序 號 ;
l_bookidSize = new SystemDrawingSize ( ) ;
l_bookidFont = new SystemDrawingFont ( 宋體 f ) ;
l_bookidTextAlign = SystemDrawingContentAlignmentMiddleCenter ;
l_booktitleLocation = new SystemDrawingPoint ( ) ;
l_booktitleText = 書 名 ;
l_booktitleSize = new SystemDrawingSize ( ) ;
l_booktitleFont = new SystemDrawingFont ( 宋體 f ) ;
l_booktitleTextAlign = SystemDrawingContentAlignmentMiddleCenter ;
l_bookauthorLocation = new SystemDrawingPoint ( ) ;
l_bookauthorText = 作 者;
l_bookauthorSize = new SystemDrawingSize ( ) ;
l_bookauthorFont = new SystemDrawingFont ( 宋體 f ) ;
l_bookauthorTextAlign = SystemDrawingContentAlignmentMiddleCenter ;
l_bookpriceLocation = new SystemDrawingPoint ( ) ;
l_bookpriceText = 價 格 ;
l_bookpriceSize = new SystemDrawingSize ( ) ;
l_bookpriceFont = new SystemDrawingFont ( 宋體 f ) ;
l_bookpriceTextAlign = SystemDrawingContentAlignmentMiddleCenter ;

l_bookstockLocation = new SystemDrawingPoint ( ) ;
l_bookstockText = 書 架 號 ;
l_bookstockSize = new SystemDrawingSize ( ) ;
l_bookstockFont = new SystemDrawingFont ( 宋體 f ) ;
l_bookstockTextAlign = SystemDrawingContentAlignmentMiddleCenter ;

//以下設定程序中用到的功能按鈕的屬性及對應的事件
deleteLocation = new SystemDrawingPoint ( ) ;
deleteForeColor = SystemDrawingColorBlack ;
deleteSize = new SystemDrawingSize ( ) ;
deleteFont = new SystemDrawingFont ( 宋體 f ) ;
deleteText = 刪除記錄 ;
deleteClick += new SystemEventHandler ( GoDelete ) ;

updateLocation = new SystemDrawingPoint ( ) ;
updateForeColor = SystemDrawingColorBlack ;
updateSize = new SystemDrawingSize ( ) ;
updateFont = new SystemDrawingFont ( 宋體 f ) ;
updateText = 修改記錄 ;
updateClick += new SystemEventHandler ( GoUpdate ) ;

firstrecLocation = new SystemDrawingPoint ( ) ;
firstrecForeColor = SystemDrawingColorBlack ;
firstrecSize = new SystemDrawingSize ( ) ;
firstrecFont = new SystemDrawingFont ( 宋體 f ) ;
firstrecText = 首記錄 ;
firstrecClick += new SystemEventHandler ( GoFirst ) ;

previousrecLocation = new SystemDrawingPoint ( ) ;
previousrecForeColor = SystemDrawingColorBlack ;
previousrecSize = new SystemDrawingSize ( ) ;
previousrecFont = new SystemDrawingFont ( 宋體 f ) ;
previousrecText = 上一條 ;
previousrecClick += new SystemEventHandler ( GoPrevious ) ;

nextrecLocation = new SystemDrawingPoint ( ) ;
nextrecForeColor = SystemDrawingColorBlack ;
nextrecSize = new SystemDrawingSize ( ) ;
nextrecFont = new SystemDrawingFont ( 宋體 f ) ;
nextrecText = 下一條 ;
nextrecClick += new SystemEventHandler ( GoNext ) ;

lastrecLocation = new SystemDrawingPoint ( ) ;
lastrecForeColor = SystemDrawingColorBlack ;
lastrecSize = new SystemDrawingSize ( ) ;
lastrecFont = new SystemDrawingFont ( 宋體 f ) ;
lastrecText = 尾記錄 ;
lastrecClick += new SystemEventHandler ( GoLast ) ;

labelLocation = new SystemDrawingPoint ( ) ;
labelText = 用Visual C#來修改和刪除數據庫中的記錄 ;
labelSize = new SystemDrawingSize ( ) ;
labelForeColor = SystemDrawingSystemColorsDesktop ;
labelFont = new SystemDrawingFont ( 宋體 f ) ;
//設定程序的主窗體的屬性
thisText = 用Visual C#來修改和刪除數據庫中的記錄! ;
thisAutoScaleBaseSize = new SystemDrawingSize ( ) ;
thisFormBorderStyle = FormBorderStyleFixedSingle ;
thisClientSize = new SystemDrawingSize ( ) ;
//在主窗體中加入組件
thisControlsAdd ( delete ) ;
thisControlsAdd ( update ) ;
thisControlsAdd ( lastrec ) ;
thisControlsAdd ( nextrec ) ;
thisControlsAdd ( previousrec ) ;
thisControlsAdd ( firstrec ) ;
thisControlsAdd ( t_bookstock ) ;
thisControlsAdd ( t_bookprice ) ;
thisControlsAdd ( t_bookauthor ) ;
thisControlsAdd ( t_booktitle ) ;
thisControlsAdd ( t_bookid ) ;
thisControlsAdd ( l_bookstock ) ;
thisControlsAdd ( l_bookprice ) ;
thisControlsAdd ( l_bookauthor ) ;
thisControlsAdd ( l_booktitle ) ;
thisControlsAdd ( l_bookid ) ;
thisControlsAdd ( label ) ;

}
//刪除記錄對應的事件
protected void GoDelete ( object sender SystemEventArgs e )
{
try{
//連接到一個數據庫
string strCon = Provider = MicrosoftJetOLEDB ; Data Source = samplemdb ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
myConnOpen ( ) ;
string strDele = DELETE FROM books WHERE bookid= + t_bookidText ;
OleDbCommand myCommand = new OleDbCommand ( strDele myConn ) ;
//從數據庫中刪除指定記錄
myCommandExecuteNonQuery ( ) ;
//從DataSet中刪除指定記錄
myDataSetTables [ books ] Rows [ myBindPosition ] Delete ( ) ;
myDataSetTables [ books ] AcceptChanges ( ) ;
myConnClose ( ) ;
}
catch ( Exception ed )
{
MessageBoxShow ( 刪除記錄錯誤信息 + edToString ( ) 錯誤! ) ;
}
}
//修改記錄按鈕對應的事件
protected void GoUpdate ( object sender SystemEventArgs e )
{
int i = myBindPosition ;
try{
//連接到一個數據庫
string strCon = Provider = MicrosoftJetOLEDB ; Data Source = samplemdb ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
myConnOpen ( ) ;

//從數據庫中修改指定記錄
string strUpdt = UPDATE books SET booktitle =
+ t_booktitleText + bookauthor =
+ t_bookauthorText + bookprice =
+ t_bookpriceText + bookstock =
+ t_bookstockText + WHERE bookid = + t_bookidText ;

OleDbCommand myCommand = new OleDbCommand ( strUpdt myConn ) ;
myCommandExecuteNonQuery ( ) ;
myConnClose ( ) ;
}
catch ( Exception ed )
{
MessageBoxShow ( 修改指定記錄錯誤 + edToString ( ) 錯誤! ) ;
}
myBindPosition = i ;
}
//尾記錄按鈕對應的事件
protected void GoLast ( object sender SystemEventArgs e )
{
myBindPosition = myBindCount ;
}
//下一條按鈕對應的事件
protected void GoNext ( object sender SystemEventArgs e )
{
if ( myBindPosition == myBindCount )
MessageBoxShow ( 已經到尾記錄! ) ;
else
myBindPosition += ;
}
//上一條按鈕對應的事件
protected void GoPrevious ( object sender SystemEventArgs e )
{
if ( myBindPosition == )
MessageBoxShow ( 已經到首記錄! ) ;
else
myBindPosition = ;
}
//首記錄按鈕對應的事件
protected void GoFirst ( object sender SystemEventArgs e )
{
myBindPosition = ;
}
}

  四編譯源程序代碼生成執行文件

  得到了Datacs源程序代碼以後經過下面編譯命令編譯成功後即可得到執行文件dataexe
csc /t:winexe /r:systemwindowsformsdll /r:systemdatadll datacs

  總結

  本文主要介紹了如何用Visual C#來刪除和修改記錄以及在處理這些操作的時候應該注意的一些重要問題及處理的方法如果你的機器已經滿足本文要求的運行環境那麼在生成的執行文件目錄中建立一個samplemdb數據庫在數據庫中創建一個books數據表數據表的結構可按照本文上面提供的結構來建立在這一切都完畢後就可以運行程序享受Visual C#給我們帶來的數據操作的快感了


From:http://tw.wingwit.com/Article/program/net/201311/15222.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.