《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 页 - - - - - - - - -