ado一部分程序（精編版）

1、using system;using system.collections.generic; using system.text;using system.data;using system.data.sqlclient; namespaceadodemo2public classsqlhelper/ <summary>/ 用來獲取連接字符串/ </summary>public string myconstrget/return "data source=pc201301221117;initial catalog=adodb;user id=sa;pwd=s

2、asa"return system.configuration.configurationmanager.connectionstrings"myconnstring" .co nnectionstring.tostring();/ <summary>/ 創(chuàng)建于數(shù)據(jù)庫(kù)連接。/ </summary>/ <returns>連接的狀態(tài) </returns> public string createsqlconnection()sqlconnection mycon = new sqlconnection(myconstr)

3、; mycon.open();string s = mycon.state.tostring(); return s;/ <summary>/ 基于sql命令的數(shù)據(jù)庫(kù)操作/ </summary>/ <param name="sql"> insert delete update</param>/ <returns>執(zhí)行結(jié)構(gòu) int </returns>public int executesqlreturnint(string sql)sqlconnection con1 = new sqlconnecti

4、on(myconstr);/打開連接狀態(tài)。tryif(con1.state=connectionstate.closed|con1.state =connectionstate.broken)con1.open();/sqlcommand cmd = new sqlcommand();/cmd.connection = con1;/cmd.commandtext = sql;sqlcommand cmd = new sqlcommand(sql, con1); int i = cmd.executenonquery();return i;catchreturn 0;finallycon1.cl

5、ose();/ <summary>/ 基于帶參數(shù)的 sql命令的數(shù)據(jù)庫(kù)操作。/ </summary>/ <param name="sql">sql 命令</param>/ <param name="pars">參數(shù)數(shù)組 </param>/ <returns>執(zhí)行后的結(jié)構(gòu)int</returns>public int executesqlreturnint(string sql, sqlparameter pars)sqlconnection con1 = ne

6、w sqlconnection(myconstr);/打開練接狀態(tài)。tryif(con1.state=connectionstate.closed|con1.state = connectionstate.broken)con1.open();/sqlcommand cmd = new sqlcommand();/cmd.connection = con1;/cmd.commandtext = sql;sqlcommand cmd = new sqlcommand(sql, con1);#region給sql語(yǔ)句添加參數(shù)，參數(shù)傳遞給cmd的parameters屬性里foreach (sqlpa

7、rameterp in pars)cmd.parameters.add(p);#endregionint i = cmd.executenonquery(); return i;catchreturn 0;finallycon1.close();/ <summary>/ 返回結(jié)果集的數(shù)據(jù)庫(kù)查詢方法/ </summary>/ <param name="sql"> select * from</param>/ <returns>dataset</returns>public datasetselectsql

8、returndatasets( tring sql)sqlconnection conn = new sqlconnection(myconstr); sqldataadapter sda = new sqldataadapter(sql, conn); datasetds = new dataset();sda.fill(ds); return ds;public sqldatareaderselectsqlrreturnreaders(tring sql)sqlconnection con = new sqlconnection(myconstr);if(con.state=connect

9、ionstate.broken|con.state= connectionstate.closed)con.open();sqlcommand cmd = new sqlcommand(sql,con ); sqldatareadersdr= cmd.executereader(); return sdr;/ <summary>/ 基于帶參數(shù)的 sql命令的 dataadapter 操作/ </summary>/<paramname="sql">比 如 sql="select*fromuserswhere user=user&q

10、uot;</param>/ <returns></returns>publicdataset selectsqlreturndatasets(tringsql,sqlparameterpars, commandtypetype)sqlconnection conn = new sqlconnection(myconstr); sqldataadapter sda = new sqldataadapter(sql, conn); if (pars != null && pars.length > 0)foreach (sqlparamet

11、erp in pars)sda.selectcommand.parameters.add(p);sda.selectcommand.commandtype = type; datasetds = new dataset();sda.fill(ds);return ds;/ <summary>/基于sql命令或者存儲(chǔ)過程，查詢數(shù)據(jù)庫(kù)操作/ </summary>/ <param name="sql"></param>/ <returns></returns>public sqldatareaderselec

12、tsqlreturndatareaders(tring sql,sqlparameter pars,commandtypetype)sqlconnection con = new sqlconnection(myconstr);if(con.state=connectionstate.closed|con.state= connectionstate.broken)con.open();sqlcommand cmd = new sqlcommand(sql, con); if (pars != null | pars.length > 0)foreach (sqlparameterp i

13、n pars)cmd.parameters.add(p);cmd.commandtype = type;sqldatareaderreader= cmd.executereaderc( ommandbehavior.closeconnection);return reader;public datatable executesqlreturndatatables( tring sql)sqlconnection con = new sqlconnection(myconstr); sqldataadapter sda = new sqldataadapter(sql, con); datase

14、t ds= new dataset ();sda.fill(ds, "mytable"); return ds.tables"mytable" ;publicdatatable executesqlreturndatatables(tringsql,sqlparameter pars,commandtypetype)sqlconnection con = new sqlconnection(myconstr); sqldataadapter sda = new sqldataadapter(sql, con); datasetds = new datas

15、et();if (pars != null && pars.length > 0)foreach (sqlparameterp in pars)sda.selectcommand.parameters.add(p);if (type != null )sda.selectcommand.commandtype = type;sda.fill(ds, "mytable");return ds.tables"mytable" ;form1.csusing system.data.sqlclient;namespaceadodemo2pu

16、blic partial class form1 : formsqlhelper sh=new sqlhelper() ;public form1()initializecomponent();private void form1_load(object sender,eventargs e)label1 .text= sh.createsqlconnection(); string sql= "select * from users"sqldatareadersdr=sh.selectsqlrreturnreader(sql); sdr.read();this.label

17、3.text=sdr.getstring(0).tostring(); this.label4.text = sdr.getstring(1).tostring();private void button1_click( object sender,eventargs e)string sql = "insert into users values( 趙' int i=sh.executesqlreturnint(sql); this.label2.text = i.tostring() ;云','456')"private void btn

18、query_click(object sender,eventargs e)string sql = "select * from users"datasetds=sh.selectsqlreturndataset(sql); datatabledb= ds.tables0;this.datagridview1.datasource = db;private void label4_click( object sender,eventargs e)2014.4usermanager.csusing system;using system.collections.generi

19、c; using system.componentmodel; using system.data;using system.drawing; using system.text;using system.windows.forms;using system.data.sqlclient;namespaceadodemopublic partial classusermanager : formpublic usermanager()initializecomponent();sqlhelperhelper = new sqlhelper() ;privatevoidgbusermanager

20、_enter(objectsender, eventargs e)/ <summary>/綁定相應(yīng)的控件，dategridview cbdepartment/ </summary>/ <param name="sender"></param>/ <param name="e"></param>private void usermanager_load(object sender, eventargse)userbind();string sql2 ="select depa

21、rtmentid,departmentname from tbdepartment" ;cbdepartment.datasource=helper.executesqlreturndatatable(sql2);cbdepartment.displaymember = "departmentname" cbdepartment.valuemember= "departmentid" ;private void userbind()string sql1 = "select id as ' 編號(hào)',username a

22、s '用戶名稱',birthday as ' 生日','部門名稱 '= (select departmentname from tbdepartmentwheretbuser.departmentid=tbdepartment.departmentid ) from tbuser" ;datagridview1.datasource= helper.executesqlreturndatatable(sql1);privatevoidgbuserdisplay_enter(objectsender, eventargs e)/ <

23、summary>/添加用戶/ </summary>/ <param name="sender"></param>/ <param name="e"></param>private void btadd_click( object sender, eventargs e)stringsql="insertinto tbuser(username,birthday,departmentid) values(username,birthday,departmentid)"sq

24、lparameter pars = new sqlparameternewsqlparameter("username",this.txtusername .text ),newsqlparameter("birthday" ,this.txtbirthday.text ),newsqlparameter("departmentid",this.cbdepartment .selectedvalue .tostring ();int count =helper.executesqlreturnint(sql, pars); if(co

25、unt != 0)userbind();messagebox.show(" 添加成功 " );elsemessagebox.show(" 添加失敗 " );/ <summary>/刪除一條記錄的事件，雙擊datagridview控件/ </summary>/ <param name="sender"></param>/ <param name="e"></param>privatevoiddatagridview1_doubleclick( o

26、bjectsender, eventargs e)if(messagebox.show(" 是否刪除當(dāng)前記錄","刪除記錄", messageboxbuttons.okcancel) = dialogresult .ok)stringid= this.datagridview1.currentrow.cells0.value.tostring();string sql = "delete from tbuser where id="+ id; int count=helper.executesqlreturnint(sql);if(c

27、ount != 0)userbind();messagebox.show(" 刪除成功 " );elsemessagebox.show(" 刪除失敗 " );privatevoiddatagridview1_click( objectsender, eventargs e)stringid= this.datagridview1.currentrow.cells0.value.tostring();string sql= "select * from tbuser where id=" +id; sqldatareadersdr=he

28、lper.executesqlreturndatareader(sql);if(sdr.read()this.txtusername.text=sdr"username".tostring ();this.txtbirthday.text=convert .todatetime ( sdr"birthday" ).toshortdatestring();this.cbdepartment.selectedvalue=sdr"departmentid" .tostring();private void button1_click( ob

29、ject sender,eventargs e)stringid=this.datagridview1.currentrow.cells 0.value .tostring ();if(messagebox.show(" 是否真的修改id為" +id+ " 的當(dāng) 前 記 錄 " ," 修 改 記 錄 " ,messageboxbuttons.okcancel)=dialogresult .ok)stringsql="updatetbusersetusername=username,birthday=birthday,depa

30、rtmentid=depar tmentid where id=id" ;sqlparameter pars = new sqlparameternewsqlparameter("username",this.txtusername.text ),newsqlparameter("birthday" ,this.txtbirthday .text ),newsqlparameter("departmentid",this.cbdepartment .selectedvalue.tostring () ), new sqlpa

31、rameter("id" ,id);intcount=helper.executesqlreturnint(sql,pars);if (count != 0)userbind();messagebox.show(" 修改成功 " );elsemessagebox.show(" 修改失敗 " );數(shù)據(jù)庫(kù)操作類sqlhelper using system;using system.collections.generic; using system.text;using system.data.sqlclient; using system

32、.data; namespaceadodemoclasssqlhelper/ <summary>/屬性，連接字符串，只能被讀取，不能給賦值/ </summary>public string myconnectionstringget/ return "data source=pc201301221117;initial catalog=adodb; user id=sa;pwd=sasa"returnsystem.configuration. configurationmanager.connectionstrings"m yconstr&

33、quot;.connectionstring.tostring();/ <summary>/返回連接（架的橋）的狀態(tài)/ </summary>/ <returns></returns>public string getconnectionstate()string r;sqlconnectionconn=new sqlconnection(myconnectionstring);if(conn.state=connectionstate.closed|conn.state = connectionstate.broken)conn.open();r

34、 = conn.state.tostring (); return r;/ <summary>/不帶參數(shù)的 sql語(yǔ)句執(zhí)行方法/ </summary>/ <param name="sql"> insert delete update</param>/ <returns>受影響的行數(shù) int </returns> public int executesqlreturnint(string sql)sqlconnectionconn=new sqlconnection(myconnectionstring)

35、;trysqlcommandcmd=newsqlcommand(sql,conn);if(conn.state=connectionstate.broken| conn.state = connectionstate.closed)conn.open();int count = cmd.executenonquery(); return count;catchreturn 0;/ <summary>/帶參數(shù)的 sql語(yǔ)句執(zhí)行方法/ </summary>/<paramname="sql">insertintovalueswhere user

36、name=username</param>/<paramname="pars">行參數(shù)組sqlparameter</param>/ <returns>受影響的行數(shù) </returns>publicintexecutesqlreturnint(stringsql,sqlparameterpars)sqlconnectionconn=new sqlconnection(myconnectionstring);trysqlcommandcmd=newsqlcommand(sql,conn);if(conn.state=c

37、onnectionstate.broken| conn.state = connectionstate.closed)conn.open();if (pars != null| pars.length > 0)foreach (sqlparameterp in pars)cmd.parameters.add(p);int count = cmd.executenonquery(); return count;catchreturn 0;/ <summary>/不帶參數(shù) sql語(yǔ)句/ </summary>/ <param name="sql"></param>/ <returns> dataset </returns>public data