IDC资讯中心
在本篇文章中，我们将介绍visual c#对数据库的一个基本操作，即：如何往数据库中添加记录。我们将通过一些数据库操作的例子，来具体说明一下。为了更清楚的说明这个问题，在选用数据库方面采用了二种当前比较典型的数据库，其一是本地数据库–access 2000，另外一个是远程数据库–sql server 7.0。首先介绍如何用visual c#来添加access 2000数据库的记录。
一．用visual c#来添加access 2000数据库的记录
（一）.程序设计和运行的环境设置：
（1）视窗2000服务器版
（2）microsoft data acess component 2.6 以上版本 ( mdac 2.6 )
（3）本文程序使用的数据库的介绍：
程序中使用的数据库名称为sample.mdb，在此数据库中有一张数据表books。此数据表的结构如下：
| 字段名称 | 字段类型 | 代表意思 |
| bookid | 数字 | 序号 |
| booktitle | 文本 | 书籍名称 |
| bookauthor | 文本 | 书籍作者 |
| bookprice | 数字 | 价格 |
| bookstock | 数字 | 书架号 |
（二）.程序设计难点和应该注意的问题：
如何正确的往数据库中添加记录是本文要讨论的一个重点和难点，下面就是解决这一问题的具体思路：
（1）创建并打开一个 oledbconnection对象。
（2）创建一个插入一条记录的sql语句。
（3）创建一个oledbcommand对象。
（4）通过此oledbcommand对象完成对插入一条记录到数据库的操作。
以下是在程序中实现的具体语句：
| string strconn = " provider = microsoft.jet.oledb.4.0 ; data source = sample.mdb " ; oledbconnection myconn = new oledbconnection ( strconn ) ; myconn.open ( ) ; string strinsert = " insert into books ( bookid , booktitle , bookauthor , bookprice , bookstock ) values ( " ; strinsert += t_bookid.text + ", " ; strinsert += t_booktitle.text + ", " ; strinsert += t_bookauthor.text + ", " ; strinsert += t_bookprice.text + ", " ; strinsert += t_bookstock.text + ")" ; oledbcommand inst = new oledbcommand ( strinsert , myconn ) ; inst.executenonquery ( ) ; myconn.close ( ) ; |
（三）.用visual c#来插入记录的程序源代码( add.cs )和执行后的界面：
下图是add.cs编译后的执行界面：
add.cs源程序代码：
| using system ; using system.drawing ; using system.componentmodel ; using system.windows.forms ; using system.data.oledb ; using system.data ; //导入程序中使用到的名称空间 public class dataadd : form { private button lastrec ; private button nextrec ; private button previousrec ; private button firstrec ; private container components ; private label title ; private button t_new ; private button save ; 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 dataset mydataset ; private bindingmanagerbase mybind ; //定义在程序中要使用的组件 public dataadd ( ) { //连接到一个数据库 getconnected ( ) ; // 对窗体中所需要的内容进行初始化 initializecomponent ( ); } //释放程序使用过的所以资源 public override void dispose ( ) { base.dispose ( ) ; components.dispose ( ) ; } public static void main ( ) { application.run ( new dataadd ( ) ) ; } 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 得到一个数据集 oledbdataadapter mycommand = new oledbdataadapter ( strcom , myconn ) ; //把dataset绑定books数据表 mycommand.fill ( mydataset , "books" ) ; //关闭此oledbconnection myconn.close ( ) ; } catch ( exception e ) { messagebox.show ( "连接错误! " + e.tostring ( ) , "错误" ) ; } } private void initializecomponent ( ) { components = new system.componentmodel.container ( ) ; nextrec = new button ( ) ; lastrec = new button ( ) ; previousrec = new button ( ) ; firstrec = new button ( ) ; t_bookprice = new textbox ( ) ; l_booktitle = new label ( ) ; l_bookprice = new label ( ) ; l_bookauthor = new label ( ) ; t_bookid = new textbox ( ) ; save = new button ( ) ; title = new label ( ) ; t_bookauthor = new textbox ( ) ; t_booktitle = new textbox ( ) ; t_new = new button ( ) ; l_bookstock = new label ( ) ; t_bookstock = new textbox ( ) ; l_bookid = new label ( ) ; //以下是对数据浏览的四个按钮进行初始化 firstrec.location = new system.drawing.point ( 65 , 312 ) ; firstrec.forecolor = system.drawing.color.black ; firstrec.size = new system.drawing.size ( 40 , 24 ) ; firstrec.font = new system.drawing.font("仿宋", 8f ); firstrec.text = "首记录"; firstrec.click += new system.eventhandler(gofirst); previousrec.location = new system.drawing.point ( 135 , 312 ) ; previousrec.forecolor = system.drawing.color.black ; previousrec.size = new system.drawing.size(40, 24) ; previousrec.font = new system.drawing.font ( "仿宋" , 8f ) ; previousrec.text = "上一条" ; previousrec.click += new system.eventhandler ( goprevious ) ; nextrec.location = new system.drawing.point ( 205 , 312 ); nextrec.forecolor = system.drawing.color.black ; nextrec.size = new system.drawing.size ( 40 , 24 ) ; nextrec.font = new system.drawing.font ( "仿宋" , 8f ) ; nextrec.text = "下一条" ; nextrec.click += new system.eventhandler ( gonext ); lastrec.location = new system.drawing.point ( 275 , 312 ) ; lastrec.forecolor = system.drawing.color.black ; lastrec.size = new system.drawing.size ( 40 , 24 ) ; lastrec.font = new system.drawing.font ( "仿宋" , 8f ) ; lastrec.text = "尾记录" ; lastrec.click += new system.eventhandler ( golast ) ; //以下是对显示标签进行初始化 l_bookid.location = new system.drawing.point ( 24 , 56 ) ; l_bookid.text = "书本序号：" ; l_bookid.size = new system.drawing.size ( 112, 20 ) ; l_bookid.font = new system.drawing.font ( "仿宋" , 10f ) ; 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 ( 112 , 20 ) ; l_booktitle.font = new system.drawing.font ( "仿宋" , 10f ) ; l_booktitle.textalign = system.drawing.contentalignment.middlecenter ; l_bookprice.location = new system.drawing.point ( 24 , 212 ) ; l_bookprice.text = "价 格：" ; l_bookprice.size = new system.drawing.size ( 112 , 20 ) ; l_bookprice.font = new system.drawing.font ( "仿宋" , 10f ) ; 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 ( 112 , 20 ) ; l_bookstock.font = new system.drawing.font ( "仿宋" , 10f ) ; l_bookstock.tabindex = 16 ; l_bookstock.textalign = system.drawing.contentalignment.middlecenter ; l_bookauthor.location = new system.drawing.point ( 24 , 160 ) ; l_bookauthor.text = "作 者：" ; l_bookauthor.size = new system.drawing.size ( 112 , 20 ) ; l_bookauthor.font = new system.drawing.font ( "仿宋" , 10f ) ; l_bookauthor.textalign = system.drawing.contentalignment.middlecenter ; title.location = new system.drawing.point ( 32 , 16 ) ; title.text = "利用vsiual c#来增加数据记录！" ; title.size = new system.drawing.size ( 336 , 24 ) ; title.forecolor = system.drawing.color.green ; title.font = new system.drawing.font ( "仿宋" , 14f , system.drawing.fontstyle.bold ) ; //以下是对为显示数据记录而设定的标签和文本框进行初始化，并把记录绑定在不同的绑定到文本框"text"属性上 t_bookid.location = new system.drawing.point ( 184 , 56 ) ; t_bookid.size = new system.drawing.size ( 80 , 20 ) ; t_bookid.databindings.add ( "text" , mydataset , "books.bookid" ) ; t_bookstock.location = new system.drawing.point ( 184 , 264 ) ; t_bookstock.size = new system.drawing.size ( 80 , 20 ) ; t_bookstock.databindings.add ( "text" , mydataset , "books.bookstock" ) ; t_booktitle.location = new system.drawing.point ( 184 , 108 ) ; t_booktitle.size = new system.drawing.size ( 176 , 20 ) ; t_booktitle.databindings.add( "text" , mydataset , "books.booktitle" ) ; t_bookprice.location = new system.drawing.point ( 184 , 212 ) ; t_bookprice.size = new system.drawing.size ( 80 , 20 ) ; t_bookprice.databindings.add ( "text" , mydataset , "books.bookprice" ) ; t_bookauthor.location = new system.drawing.point ( 184 , 160 ) ; t_bookauthor.size = new system.drawing.size ( 128 , 20 ) ; t_bookauthor.databindings.add ( "text" , mydataset , "books.bookauthor" ) ; t_new.location = new system.drawing.point ( 62 , 354 ) ; t_new.size = new system.drawing.size ( 96 , 32 ) ; t_new.text = "新建记录" ; t_new.click += new system.eventhandler ( t_newclick ) ; save.location = new system.drawing.point ( 222 , 354 ) ; save.size = new system.drawing.size ( 96 , 32 ) ; save.tabindex = 4 ; save.text = "保存记录" ; save.click += new system.eventhandler ( saveclick ) ; this.text = "利用vsiual c#来增加数据记录的程序窗口！" ; this.autoscalebasesize = new system.drawing.size ( 5 , 13 ) ; this.formborderstyle = formborderstyle.fixed3d ; this.clientsize = new system.drawing.size ( 390 , 400 ) ; //在窗体中加入下列组件 this.controls.add ( lastrec ) ; this.controls.add ( nextrec ) ; this.controls.add ( previousrec ) ; this.controls.add ( firstrec ) ; this.controls.add ( title ) ; this.controls.add ( t_new ) ; this.controls.add ( save ) ; 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 ) ; //把对象dataset和"books"数据表绑定到此mybind对象 mybind= this.bindingcontext [ mydataset , "books" ] ; } protected void saveclick ( object sender , system.eventargs e ) { try { //判断所有字段是否添完，添完则执行，反之弹出提示 if ( t_bookid.text != "" && t_booktitle.text != "" && t_bookauthor.text != "" && t_bookprice.text != "" && t_bookstock.text != "" ) { string strconn = " provider = microsoft.jet.oledb.4.0 ; data source = sample.mdb " ; oledbconnection myconn = new oledbconnection ( strconn ) ; myconn.open ( ) ; string strinsert = " insert into books ( bookid , booktitle , bookauthor , bookprice , bookstock ) values ( " ; strinsert += t_bookid.text + ", " ; strinsert += t_booktitle.text + ", " ; strinsert += t_bookauthor.text + ", " ; strinsert += t_bookprice.text + ", " ; strinsert += t_bookstock.text + ")" ; oledbcommand inst = new oledbcommand ( strinsert , myconn ) ; inst.executenonquery ( ) ; myconn.close ( ) ; } else { messagebox.show ( "必须填满所有字段值！" , "错误！" ) ; } } catch ( exception ed ) { messagebox.show ( "保存数据记录发生 " + ed.tostring ( ) , "错误！" ) ; } } protected void t_newclick ( object sender , system.eventargs e ) { t_bookid.text = "" ; t_booktitle.text = "" ; t_bookauthor.text = "" ; t_bookprice.text = "" ; t_bookstock.text = "" ; } //按钮"尾记录"对象事件程序 protected void golast ( object sender , system.eventargs e ) { mybind.position = mybind.count – 1 ; } //按钮"下一条"对象事件程序 |
成功编译上面代码，就可以往access 2000数据库里面插入记录了。
