2022年操作数据库中的数据

《2022年操作数据库中的数据》由会员分享，可在线阅读，更多相关《2022年操作数据库中的数据（26页珍藏版）》请在金锄头文库上搜索。

1、1 目录web.config 里面的连接字符串. 24.1 连接 SQL Server 数据库 . 25.1 使用 SqlCommand 对象执行数据库的操作. 45.2 使用 SqlDataReader 对象读取数据. 65.3 数据列、数据行和数据表. 75.4 构建 DataSet对象 . 95.5 使用 SqlDataAdapter 对象填充数据集 . 11 5.6 添加表之间的关系对象DataRelation . 12 5.7 添加表之间的约束对象C onstraint . 15 5.8 使用 DataView 对象过滤数据集. 17 5.9 以 XML 格式读取数据. 18 5.1

2、0 DataSet 对象和 XML 格式数据之间的转换 . 20 5.11 使用访问数据库的参数. 21 5.12 使用 ADO.NET 中的事务 . 24 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 26 页 - - - - - - - - - 2 web.config里面的连接字符串 /1 、使用 mv2008自带的 sqlexpress数据库 /2 、使用 sql 数据库 /3 、使用 access 数据库的 ODBC 数据源 /4 、使用 mv2008自带的

3、sqlexpress数据库 4.1 连接 SQL Server 数据库using System; using System.Collections; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System

4、.Xml.Linq; using System.Data.SqlClient; using System.Configuration; / /从Web.config 文件内取得连接字符串，并保存在静态变量“SQLSERVERCONNECTIONSTRING”中；/ publicclassASPNET3DBBOOKSystem publicstaticstring SQLSERVERCONNECTIONSTRING = ConfigurationManager.ConnectionStringsSQLSERVERCONNECTIONSTRING.ConnectionString; 名师资料总结

5、- - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 26 页 - - - - - - - - - 3 / 通入 Page_Load的方法来执行连接数据库的操作publicpartialclassConnectionSQLServer : System.Web.UI.Page protectedvoid Page_Load( object sender, EventArgs e) if (!Page.IsPostBack) ConnectionDB(); / 定义 ConnectionDB

6、的方法privatevoid ConnectionDB() try / 创建访问数据库的连接实例myconnection, 并调用上面取得的静态变量作为参数；SqlConnection myConnection = newSqlConnection ( ASPNET3DBBOOKSystem.SQLSERVERCONNECTIONSTRING); / 创建查询语句 cmdtext ；string cmdText = select count(*) as usercount from table1; / 以查询语句作为参数，创建执行查询语句的实例mycommand SqlCommand myCo

7、mmand = new SqlCommand (cmdText,myConnection); / 上面都是在进行一些实例化，下面才是开始真正的执行操作 myConnection.Open(); int result = (int )myCommand.ExecuteScalar();/ 定义一个整形变量来接收返回的值 Response.Write(tabale1表记录的条数为 + result.ToString() + 条。 ); Response.Write(SQL SERVER 数据库的连接状态： + myConnection.State.ToString(); Response.Writ

8、e(SQL SERVER 数据库连接的通信包的大小：+ myConnection.PacketSize.ToString(); Response.Write(SQL SERVER 数据库连接的数据库名： + myConnection.Database.ToString(); Response.Write(SQL SERVER 数据库连接的工作站点： + myConnection.WorkstationId.ToString(); myConnection.Close(); Response.Write(SQL SERVER 数据库的连接状态： + myConnection.State.ToSt

9、ring(); catch ( Exception ex) Response.Write(ex.Message); ; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 26 页 - - - - - - - - - 4 5.1 使用 SqlCommand 对象执行数据库的操作using System; using System.Collections; using System.Configuration; using System.Data; using System.L

10、inq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; using System.Xml; publicpartialclasssqlcommandpage : System.Web.UI.P

11、age / 新建一个类 GetConnectionDataString，用来取得数据库的连接字符串publicclassGetConncetionDataString publicstaticstring ConnectionDataString = ConfigurationManager.ConnectionStringsSQLSERVERCONNECTIONSTRING.ConnectionString; / 页面载入的时候执行的函数protectedvoid Page_Load( object sender, EventArgs e) if (!Page.IsPostBack) /使用

12、 executescaler()方法执行数据库查询 lbScalarMessage.Text = ExecuteScalarMysqlCommand().ToString(); / 调用 ExecuteNonQueryMysqlCommand(NewName)函数，用来更新数据库，并将第一行的数据更新为“my name is yxshu”, 并返回结果显示在第二个标签中 ExecuteNonQueryMysqlCommand( 余项树 ); lbNonQueryMessage.Text = ExecuteScalarMysqlCommand().ToString(); / 调用 Execute

13、XmlReaderMySqlCommand() 函数，用来读取数据库内的数据并以Xml格式返回结果显示在tbXmlReadMessage 里面 tbXmlReadMessage.Text = ExecuteXmlReaderMySqlCommand().ToString(); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 26 页 - - - - - - - - - 5 / 定义检索的方法ExecuteScalarMysqlCommand()并返回检索到的值privat

14、eobject ExecuteScalarMysqlCommand() string TextCommand = select tbName from Table1; SqlConnection MySqlConnection = newSqlConnection( GetConncetionDataString.ConnectionDataString); SqlCommand MySqlCommand = new SqlCommand (TextCommand,MySqlConnection); MySqlConnection.Open(); object scalarobject = M

15、ySqlCommand.ExecuteScalar(); MySqlConnection.Close(); return (scalarobject); / 定义更新数据库的函数ExecuteNonQueryMysqlCommand(NewName) / /要求带有一个新名称的参数/ / privatevoid ExecuteNonQueryMysqlCommand( String NewName) String TextCommand = UPDATE Table1 SET tbName = +NewName+ WHERE (tbID = 1); SqlConnection MySqlCon

16、nection = newSqlConnection( GetConncetionDataString.ConnectionDataString); SqlCommand MySqlCommand = new SqlCommand (TextCommand, MySqlConnection); try MySqlConnection.Open(); MySqlCommand.ExecuteNonQuery(); catch ( Exception ex) Response.Write(ex.Message); finally MySqlConnection.Close(); / 定义一个 Ex

17、ecuteXmlReaderMySqlCommand() 函数，用来读取数据库内的数据并以Xml格式返回结果privatestring ExecuteXmlReaderMySqlCommand() string TextCommand = select tbid,tbName from table1 for xml auto; SqlConnection MySqlConnection = newSqlConnection( GetConncetionDataString.ConnectionDataString); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - -

18、 - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 26 页 - - - - - - - - - 6 SqlCommand MySqlCommand = new SqlCommand (TextCommand, MySqlConnection); MySqlConnection.Open(); XmlReader MyXmlReader = MySqlCommand.ExecuteXmlReader(); String MyXmlReaderString = ; while (MyXmlReader.Read() MyXmlReaderString

19、+= MyXmlReader.ReadOuterXml() + n; MyXmlReader.Close(); MySqlConnection.Close(); return(MyXmlReaderString); 5.2 使用 SqlDataReader 对象读取数据using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.W

20、eb.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; publicpartialclasssqlDataReader : System.Web.UI.Page protectedvoid Page_Load( object sender, EventArgs e) if (!Page.IsPostBack) lbda

21、tareadermessage.Text=FormatDataReader(ExecuteReader(); / 取得连接字符串publicclassGetConncetionDataString publicstaticstring ConnectionDataString = 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 26 页 - - - - - - - - - 7 ConfigurationManager.ConnectionStringsSQLSERVERC

22、ONNECTIONSTRING.ConnectionString; / /从数据库取得数据，并返回/ / 返回从数据库取得的结果dr; privateSqlDataReader ExecuteReader() string cmdtext= SELECT top 5 ID, userRow, email FROM user order by id desc; / 下面的方法要注意，和以前不一样SqlConnection MySqlConnection = newSqlConnection( GetConncetionDataString.ConnectionDataString); SqlCo

23、mmand mysqlcommand = new SqlCommand (cmdtext,MySqlConnection); MySqlConnection.Open(); SqlDataReaderdr=mysqlcommand.ExecuteReader(CommandBehavior.CloseConnection); return(dr); / /格式 化返回来的结果/ / / privatestring FormatDataReader(SqlDataReader datareader) string readerstring= ; while (datareader.Read()

24、readerstring+= 序号 = +datareaderID .ToString()+ ; readerstring+=用户名 = +datareaderuserRow .ToString()+ ; readerstring+=电子邮件 = +datareaderemail.ToString()+ ; readerstring+=; datareader.Close(); return(readerstring); 5.3 数据列、数据行和数据表using System; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名

25、师精心整理 - - - - - - - 第 7 页，共 26 页 - - - - - - - - - 8 using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.We

26、bControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; publicpartialclasscreatDataTable : System.Web.UI.Page / 首先要做做的就是从数据库取得数据/ 第一步，取得连接字符串publicclassGetConncetionDataString publicstaticstring ConnectionDataString = ConfigurationManager.ConnectionStringsSQLSERVERCONNECTIONSTRING.Co

27、nnectionString; / 第二步，连接数据库并取得其中的数据privatestring GetData() string cmdText = SELECT ID, userRow, password, email, createdate, address, roleID, rolename, userrole_userID, userrole_roleID FROM user; SqlConnection mysqlconnection = newSqlConnection( GetConncetionDataString.ConnectionDataString); SqlComm

28、and mysqlcommand = new SqlCommand (cmdText, mysqlconnection); mysqlconnection.Open(); SqlDataReader mydr = mysqlcommand.ExecuteReader(); / 取数据库到此结束/ 创建 DataTable 对象，其是将数据保存在内存中，方便取用，属于断开式DataTable mydataTable = new DataTable (); mydataTable.Columns.Add(ID ); mydataTable.Columns.Add(userRow ); mydata

29、Table.Columns.Add(password ); mydataTable.Columns.Add(email); mydataTable.Columns.Add(createdate); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 26 页 - - - - - - - - - 9 mydataTable.Columns.Add(address); mydataTable.Columns.Add(roleID); mydataTable.Columns.Add

30、(rolename); mydataTable.Columns.Add(userrole_userID); mydataTable.Columns.Add(userrole_roleID); while (mydr.Read() DataRow mydatarow = mydataTable.NewRow(); mydatarowID = mydrID .ToString(); mydatarowuserRow = mydruserRow .ToString(); mydatarowpassword = mydrpassword .ToString(); mydatarowemail = my

31、dremail.ToString(); mydatarowcreatedate = mydrcreatedate.ToString(); mydatarowaddress = mydraddress.ToString(); mydatarowroleID = mydrroleID.ToString(); mydatarowrolename = mydrrolename.ToString(); mydatarowuserrole_userID = mydruserrole_userID.ToString(); mydatarowuserrole_roleID = mydruserrole_rol

32、eID.ToString(); mydataTable.Rows.Add(mydatarow); mydr.Close(); mysqlconnection.Close(); lbsql.Text = cmdText; gv.DataSource = mydataTable; gv.DataBind(); returnnull ; protectedvoid Page_Load( object sender, EventArgs e) if (!Page.IsPostBack) GetData(); 5.4 构建 DataSet 对象using System; using System.Col

33、lections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 26 页 - - - - - - - - - 10 using System.Web.UI.HtmlControls; using System

34、.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; publicpartialclasscreateDataSet : System.Web.UI.Page publicclassGetConncetionDataString publicstaticstring ConnectionDataString = ConfigurationManager.ConnectionStringsSQLSERVERCONNECTI

35、ONSTRING.ConnectionString; / 第二步，连接数据库并取得其中的数据privatestring createrDataSet() string cmdText = SELECT ID, userRow, password, email, createdate, address, roleID, rolename, userrole_userID, userrole_roleID FROM user; SqlConnection mysqlconnection = newSqlConnection( GetConncetionDataString.ConnectionDa

36、taString); SqlCommand mysqlcommand = new SqlCommand (cmdText, mysqlconnection); mysqlconnection.Open(); SqlDataReader mydr = mysqlcommand.ExecuteReader(); / 取数据库到此结束/ 创建 DataTable 对象，其是将数据保存在内存中，方便取用，属于断开式DataTable mydataTable = new DataTable (); mydataTable.Columns.Add(id ); mydataTable.Columns.Add

37、(userRow ); mydataTable.Columns.Add(password ); mydataTable.Columns.Add(email); mydataTable.Columns.Add(createdate); mydataTable.Columns.Add(address); mydataTable.Columns.Add(roleID); mydataTable.Columns.Add(rolename); mydataTable.Columns.Add(userrole_userID); mydataTable.Columns.Add(userrole_roleID

38、); while (mydr.Read() DataRow mydatarow = mydataTable.NewRow(); mydatarowID = mydrID .ToString(); mydatarowuserRow = mydruserRow .ToString(); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 26 页 - - - - - - - - - 11 mydatarowpassword = mydrpassword .ToString();

39、 mydatarowemail = mydremail.ToString(); mydatarowcreatedate = mydrcreatedate.ToString(); mydatarowaddress = mydraddress.ToString(); mydatarowroleID = mydrroleID.ToString(); mydatarowrolename = mydrrolename.ToString(); mydatarowuserrole_userID = mydruserrole_userID.ToString(); mydatarowuserrole_roleI

40、D = mydruserrole_roleID.ToString(); mydataTable.Rows.Add(mydatarow); mydr.Close(); mysqlconnection.Close(); lbsql.Text = cmdText; / 和5.03 例子的区别在这个地方/新建一个数据集，并将上面初始化的表添加到数据集中DataSet mydataset = new DataSet (); mydataset.Tables.Add(mydataTable); gv.DataSource = mydataset; gv.DataBind(); returnnull; pr

41、otectedvoid Page_Load( object sender, EventArgs e) if (!Page.IsPostBack) createrDataSet(); 5.5 使用 SqlDataAdapter 对象填充数据集using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using Sy

42、stem.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 26 页 - - - - - - - - - 12 using System.Xml.Linq; using System.Data.SqlClient; publicpartialclasscreateSqlDataAda

43、pter : System.Web.UI.Page publicclassGetConncetionDataString publicstaticstring ConnectionDataString = ConfigurationManager.ConnectionStringsSQLSERVERCONNECTIONSTRING.ConnectionString; /SqlDataAdapter数据适配器，是DataSet 和SQL Server 数据库之间的桥接器privatevoid creatDataAdapter() string cmdText = SELECT ID, userR

44、ow, password, email, createdate, address, roleID, rolename, userrole_userID, userrole_roleID FROM user; SqlConnection mysqlconnection = newSqlConnection( GetConncetionDataString.ConnectionDataString); SqlDataAdaptermysqldatadapter = new SqlDataAdapter(cmdText,mysqlconnection); DataSet mydataset = ne

45、w DataSet (); mysqlconnection.Open(); mysqldatadapter.Fill(mydataset); mysqlconnection.Close(); lbsql.Text = cmdText; gv.DataSource = mydataset; gv.DataBind(); protectedvoid Page_Load( object sender, EventArgs e) if (!Page.IsPostBack) creatDataAdapter(); 5.6 添加表之间的关系对象DataRelation using System; usin

46、g System.Collections; using System.Configuration; using System.Data; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 26 页 - - - - - - - - - 13 using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls;

47、 using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; publicpartialclasscreateDataRelation : System.Web.UI.Page / 第一步，取得连接字符串publicclassGetConncetionDataString publicstaticstring ConnectionDataString = ConfigurationManager.Con

48、nectionStringsSQLSERVERCONNECTIONSTRING.ConnectionString; / 第二步，连接数据库并取得其中的数据privatestring createrDataSet() stringcmdText = SELECT ID, userRow, password, email, createdate, address, roleID, rolename, userrole_userID, userrole_roleID FROM user; SqlConnection mysqlconnection = newSqlConnection( GetCon

49、ncetionDataString.ConnectionDataString); SqlCommand mysqlcommand = new SqlCommand (cmdText, mysqlconnection); / 第三步，新建三个DataTable 对象DataTable mydatatable1 = new DataTable (); DataTable mydatatable2 = new DataTable (); DataTable mydatatable3 = new DataTable (); mydatatable1.Columns.Add(ID ); mydatata

50、ble1.Columns.Add(userRow ); mydatatable1.Columns.Add(password ); mydatatable1.Columns.Add(email); mydatatable1.Columns.Add(createdate); mydatatable1.Columns.Add(address); mydatatable2.Columns.Add(roleID); mydatatable2.Columns.Add(rolename); mydatatable3.Columns.Add(userrole_userID); mydatatable3.Col

51、umns.Add(userrole_roleID); / 第四步，新建一个dataset,并把上面创建的三个表加入其中DataSet mydataset = new DataSet (); mydataset.Tables.Add(mydatatable1); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 26 页 - - - - - - - - - 14 mydataset.Tables.Add(mydatatable2); mydataset.Tables.Add

52、(mydatatable3); / 第五步，新建表的关系对象user 和role 对象DataRelation mydatarelation1 = newDataRelation(user,mydatatable1.ColumnsID , mydatatable3.Columnsuserrole_userID); DataRelation mydatarelation2 = newDataRelation(role,mydatatable2.ColumnsroleID, mydatatable3.Columnsuserrole_roleID); mysqlconnection.Open();

53、SqlDataReader mysqldatareader = mysqlcommand.ExecuteReader(); while (mysqldatareader.Read() DataRow mydatarow1 = mydatatable1.NewRow(); DataRow mydatarow2 = mydatatable2.NewRow(); DataRow mydatarow3 = mydatatable3.NewRow(); mydatarow1ID = mysqldatareaderID .ToString(); mydatarow1userRow = mysqldatar

54、eaderuserRow .ToString(); mydatarow1password = mysqldatareaderpassword .ToString(); mydatarow1createdate = mysqldatareadercreatedate.ToString(); mydatarow1address = mysqldatareaderaddress.ToString(); mydatarow1email = mysqldatareaderemail.ToString(); mydatarow2roleID = mysqldatareaderroleID.ToString

55、(); mydatarow2rolename = mysqldatareaderrolename.ToString(); mydatarow3userrole_userID = mysqldatareaderuserrole_userID.ToString(); mydatarow3userrole_roleID = mysqldatareaderuserrole_roleID.ToString(); mydatatable1.Rows.Add(mydatarow1); mydatatable2.Rows.Add(mydatarow2); mydatatable3.Rows.Add(mydat

56、arow3); mysqldatareader.Close(); mysqlconnection.Close(); lbsql.Text = cmdText; gv.DataSource = mydataset; gv.DataBind(); returnnull ; protectedvoid Page_Load( object sender, EventArgs e) if (!Page.IsPostBack) createrDataSet(); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - -

57、- - - 第 14 页，共 26 页 - - - - - - - - - 15 5.7 添加表之间的约束对象C onstraint using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebCont

58、rols; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; publicpartialclassConstraint : System.Web.UI.Page / 第一步，取得连接字符串publicclassGetConncetionDataString publicstaticstring ConnectionDataString = ConfigurationManager.ConnectionStringsSQLSERVERCONNECTIONSTR

59、ING.ConnectionString; / 第二步，连接数据库并取得其中的数据privatestring createrconstraint() string cmdText = SELECT ID, userRow, password, email, createdate, address, roleID, rolename, userrole_userID, userrole_roleID FROM user; SqlConnection mysqlconnection = newSqlConnection( GetConncetionDataString.ConnectionData

60、String); SqlCommand mysqlcommand = new SqlCommand (cmdText, mysqlconnection); / 第三步，新建三个DataTable 对象DataTable mydatatable1 = new DataTable (); mydatatable1.Columns.Add(ID ); mydatatable1.Columns.Add(userRow ); mydatatable1.Columns.Add(password ); mydatatable1.Columns.Add(email); 名师资料总结 - - -精品资料欢迎下载

61、 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 26 页 - - - - - - - - - 16 mydatatable1.Columns.Add(createdate); mydatatable1.Columns.Add(address); mydatatable1.Columns.Add(roleID); mydatatable1.Columns.Add(rolename); mydatatable1.Columns.Add(userrole_userID); mydatatable1.Columns.

62、Add(userrole_roleID); / 加入唯一性约束UniqueConstraint ucuser= new UniqueConstraint(newDataColumn mydatatable1.ColumnsID ,mydatatable1.Columnsemail); mysqlconnection.Open(); SqlDataReader mysqldatareader = mysqlcommand.ExecuteReader(); while (mysqldatareader.Read() DataRow mydatarow1 = mydatatable1.NewRow(

63、); mydatarow1ID = mysqldatareaderID .ToString(); mydatarow1userRow = mysqldatareaderuserRow .ToString(); mydatarow1password = mysqldatareaderpassword .ToString(); mydatarow1createdate = mysqldatareadercreatedate.ToString(); mydatarow1address = mysqldatareaderaddress.ToString(); mydatarow1email = mys

64、qldatareaderemail.ToString(); mydatarow1roleID = mysqldatareaderroleID.ToString(); mydatarow1rolename = mysqldatareaderrolename.ToString(); mydatarow1userrole_userID = mysqldatareaderuserrole_userID.ToString(); mydatarow1userrole_roleID = mysqldatareaderuserrole_roleID.ToString(); mydatatable1.Rows.

65、Add(mydatarow1); mysqldatareader.Close(); mysqlconnection.Close(); lbsql.Text = cmdText; gv.DataSource = mydatatable1; gv.DataBind(); returnnull; protectedvoid Page_Load( object sender, EventArgs e) if (!Page.IsPostBack) createrconstraint(); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名

66、师精心整理 - - - - - - - 第 16 页，共 26 页 - - - - - - - - - 17 5.8 使用 DataView 对象过滤数据集using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web

67、.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; publicpartialclassmDataView : System.Web.UI.Page publicclassGetConncetionDataString publicstaticstring ConnectionDataString = ConfigurationManager.ConnectionStringsSQLSERVERCONNECTIONSTRING

68、.ConnectionString; /SqlDataAdapter数据适配器，是DataSet 和SQL Server 数据库之间的桥接器privatevoid createDataView() string cmdText = SELECT ID, userRow, password, email, createdate, address, roleID, rolename, userrole_userID, userrole_roleID FROM user; SqlConnection mysqlconnection = newSqlConnection( GetConncetionD

69、ataString.ConnectionDataString); SqlDataAdapter mysqldatadapter = new SqlDataAdapter(cmdText, mysqlconnection); DataSet mydataset = new DataSet (); mysqlconnection.Open(); mysqldatadapter.Fill(mydataset); mysqlconnection.Close(); lbsql.Text = cmdText; if (mydataset = null | mydataset.Tables.Count =

70、0) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 17 页，共 26 页 - - - - - - - - - 18 return; DataView mydataview = mydataset.Tables0.DefaultView; mydataview.RowFilter = (ID20)and(userRow like %user%); mydataview.Sort = ID DESC ; gv.DataSource = mydataview; gv.DataBind(

71、); gv2.DataSource = mydataset; gv2.DataBind(); protectedvoid Page_Load( object sender, EventArgs e) if (!Page.IsPostBack) createDataView(); 5.9 以 XML 格式读取数据using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Sec

72、urity; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; using System.Xml; using System.Text; publicpartialclassXML : System.Web.UI.Page publicclassGetConncetionDataStr

73、ing publicstaticstring ConnectionDataString = 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 18 页，共 26 页 - - - - - - - - - 19 ConfigurationManager.ConnectionStringsSQLSERVERCONNECTIONSTRING.ConnectionString; privatevoid getxmldata() string cmdtext = SELECT * FROM use

74、r FOR XML auto; SqlConnection mysqlconnection = newSqlConnection( GetConncetionDataString.ConnectionDataString); SqlCommand mysqlcommand = new SqlCommand (cmdtext, mysqlconnection); mysqlconnection.Open(); XmlReader myxmlreader = mysqlcommand.ExecuteXmlReader(); StringBuilder xmlstring = new StringB

75、uilder(); while (myxmlreader.Read() xmlstring.Append(myxmlreader.ReadOuterXml(); myxmlreader.Close(); mysqlconnection.Close(); lbsql.Text = cmdtext; tbxmlresult.Text = xmlstring.ToString(); protectedvoid Page_Load( object sender, EventArgs e) if (!Page.IsPostBack) getxmldata(); / 点击按钮的动作后台protectedv

76、oid btnoutputtoxmlfile_Click(object sender, EventArgs e) string cmdtext = SELECT ID, userRow, password, email, createdate, address, roleID, rolename, userrole_userID, userrole_roleID FROM user FOR XML auto; SqlConnection mysqlconnection = newSqlConnection( GetConncetionDataString.ConnectionDataStrin

77、g); SqlCommand mysqlcommand = new SqlCommand (cmdtext, mysqlconnection); mysqlconnection.Open(); XmlReader myxmlreader = mysqlcommand.ExecuteXmlReader(); Response.Write(); Response.Write(); while (myxmlreader.Read() Response.Write(myxmlreader.ReadOuterXml(); ; myxmlreader.Close(); 名师资料总结 - - -精品资料欢迎

78、下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 19 页，共 26 页 - - - - - - - - - 20 mysqlconnection.Close(); Response.Write(); Response.ContentType = text/xml; Response.End(); 5.10 DataSet 对象和 XML 格式数据之间的转换using System; using System.Collections; using System.Configuration; using System.Da

79、ta; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; publicpartialclassDataSetAndXML : System.Web.UI.Pa

80、ge publicclassGetConncetionDataString publicstaticstring ConnectionDataString = ConfigurationManager.ConnectionStringsSQLSERVERCONNECTIONSTRING.ConnectionString; privatevoid datasettoxmlstring() string cmdtext = select * from user; SqlConnection mysqlconnection = newSqlConnection( GetConncetionDataS

81、tring.ConnectionDataString); SqlDataAdapter mysqldataadapter = new SqlDataAdapter(cmdtext, mysqlconnection); DataSet mydataset = new DataSet (); mysqlconnection.Open(); mysqldataadapter.Fill(mydataset); mysqlconnection.Close(); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - -

82、- - - 第 20 页，共 26 页 - - - - - - - - - 21 lbsql.Text = cmdtext; tbxmlresult.Text = mydataset.GetXml(); protectedvoid Page_Load( object sender, EventArgs e) if (!Page.IsPostBack) datasettoxmlstring(); 5.11 使用访问数据库的参数using System; using System.Collections; using System.Configuration; using System.Data;

83、 using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; publicpartialclassDataBaesePara : System.Web.UI.Page

84、publicclassGetConncetionDataString publicstaticstring ConnectionDataString = ConfigurationManager.ConnectionStringsSQLSERVERCONNECTIONSTRING.ConnectionString; / 用参数向数据库里写入数据privateint addrole(string name, string name2) stringcmdtext = INSERT INTO user (userRow, password, ID, email, createdate, addre

85、ss, roleID, rolename, userrole_userID, userrole_roleID) VALUES (userRow, password, ID, email, createdate, address, roleID, rolename, userrole_userID, userrole_roleID) ; SqlConnection mysqlconnection = new名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 21 页，共 26 页 - -

86、- - - - - - - 22 SqlConnection( GetConncetionDataString.ConnectionDataString); SqlCommand mysqlcommand = new SqlCommand (cmdtext, mysqlconnection); / 设定参数值SqlParameter mysqlparameter1 = new SqlParameter ( ID, SqlDbType .Int); mysqlparameter1.Value = name; mysqlcommand.Parameters.Add(mysqlparameter1)

87、; SqlParameter mysqlparameter2 = new SqlParameter ( userRow, SqlDbType.VarChar); mysqlparameter2.Value = name2; mysqlcommand.Parameters.Add(mysqlparameter2); SqlParameter mysqlparameter3 = new SqlParameter ( password , SqlDbType.VarChar); mysqlparameter3.Value = name2; mysqlcommand.Parameters.Add(my

88、sqlparameter3); SqlParametermysqlparameter4 = new SqlParameter(email , SqlDbType .VarChar); mysqlparameter4.Value = name2; mysqlcommand.Parameters.Add(mysqlparameter4); SqlParameter mysqlparameter5 = new SqlParameter ( createdate, SqlDbType.VarChar); mysqlparameter5.Value = name2; mysqlcommand.Param

89、eters.Add(mysqlparameter5); SqlParameter mysqlparameter6 = new SqlParameter ( address , SqlDbType.VarChar); mysqlparameter6.Value = name2; mysqlcommand.Parameters.Add(mysqlparameter6); SqlParameter mysqlparameter7 = new SqlParameter ( roleID , SqlDbType.VarChar); mysqlparameter7.Value = name2; mysql

90、command.Parameters.Add(mysqlparameter7); SqlParameter mysqlparameter8 = new SqlParameter ( rolename , SqlDbType.VarChar); mysqlparameter8.Value = name2; mysqlcommand.Parameters.Add(mysqlparameter8); SqlParameter mysqlparameter9 = new SqlParameter ( userrole_userID, SqlDbType.VarChar); mysqlparameter

91、9.Value = name2; mysqlcommand.Parameters.Add(mysqlparameter9); SqlParameter mysqlparameter10 = new SqlParameter(userrole_roleID, SqlDbType.VarChar); mysqlparameter10.Value = name2; mysqlcommand.Parameters.Add(mysqlparameter10); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - -

92、- - - 第 22 页，共 26 页 - - - - - - - - - 23 mysqlconnection.Open(); int result = mysqlcommand.ExecuteNonQuery(); mysqlconnection.Close(); return result; privatevoid showmessage() SqlConnection mysqlconnection = newSqlConnection( GetConncetionDataString.ConnectionDataString); string cmdtext2 = select *

93、from user; SqlCommand mysqlcommand = new SqlCommand (cmdtext2, mysqlconnection); mysqlconnection.Open(); SqlDataAdapter mysqldataapdapter = new SqlDataAdapter(cmdtext2, mysqlconnection); DataSet mydataset = new DataSet (); mysqldataapdapter.Fill(mydataset); mysqlconnection.Close(); gv.DataSource = m

94、ydataset; gv.DataBind(); protectedvoid Page_Load( object sender, EventArgs e) protectedvoid btadd_Click(object sender, EventArgs e) if (tbname.Text.Trim().Length 0|tbname2.Text.Trim().Length0) addrole(tbname.Text.Trim(),tbname2.Text.Trim(); showmessage(); lbmessage.Visible = true ; lbmessage.Text =

95、添加新的角色成功; else showmessage(); lbmessage.Visible = true ; lbmessage.Text = 添加新的角色失败; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 23 页，共 26 页 - - - - - - - - - 24 5.12 使用 ADO.NET中的事务using System; using System.Collections; using System.Configuration; using System.Dat

96、a; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; publicpartialclassTransaction : System.Web.UI.Page

97、publicclassGetConncetionDataString publicstaticstring ConnectionDataString = ConfigurationManager.ConnectionStringsSQLSERVERCONNECTIONSTRING.ConnectionString; privatevoid executetransaction(string newname, string newothername) string cmdtext = UPDATE user SET userRow = + newname + WHERE (ID = 789789

98、789) ; SqlConnection mysqlconnection = newSqlConnection( GetConncetionDataString.ConnectionDataString); SqlCommand mysqlcommand = new SqlCommand (cmdtext, mysqlconnection); /打开数据库连接，并定义操作的事务 mysqlconnection.Open(); SqlTransaction mysqltransaction = mysqlconnection.BeginTransaction(); /事务的开始 mysqlcom

99、mand.Transaction = mysqltransaction; try / 执行数据库操作，并使用事务 mysqlcommand.ExecuteNonQuery();/ 第一次执行操作名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 24 页，共 26 页 - - - - - - - - - 25 mysqlcommand.CommandText = UPDATE user SET userRow = + newothername + WHERE (ID = 4854174)

100、; / 设置新的操作语句 mysqlcommand.ExecuteNonQuery();/ 第二次执行对数据库的操作 mysqltransaction.Commit();/ 提交数据库事务 Response.Write( 修改数据库中的两条记录成功); catch / 回滚事务 mysqltransaction.Rollback(); Response.Write( 修改 user 表中的两条记录失败); finally mysqlconnection.Close(); privateSqlDataReader ExecuteReader() stringcmdtext = SELECT to

101、p 5 ID, userRow, email FROM user order by id desc ; / 下面的方法要注意，和以前不一样SqlConnection MySqlConnection = newSqlConnection( GetConncetionDataString.ConnectionDataString); SqlCommand mysqlcommand = new SqlCommand (cmdtext, MySqlConnection); MySqlConnection.Open(); SqlDataReader dr = mysqlcommand.ExecuteRe

102、ader(CommandBehavior.CloseConnection); return (dr); privatestring FormatDataReader(SqlDataReader datareader) string readerstring = ; while (datareader.Read() readerstring += 序号 = + datareaderID .ToString() + ; readerstring += 用户名 = + datareaderuserRow .ToString() + ; readerstring += 电子邮件 = + datarea

103、deremail.ToString() + ; readerstring += ; datareader.Close(); return (readerstring); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 25 页，共 26 页 - - - - - - - - - 26 protectedvoid Page_Load( object sender, EventArgs e) if (!Page.IsPostBack) Label1.Text = FormatDataReader(ExecuteReader(); / 执行 ADO.NET 中的事务操作 executetransaction(myname, othername); Label2.Text = FormatDataReader(ExecuteReader(); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 26 页，共 26 页 - - - - - - - - -