2022年操作数据库中的数据 .pdf

《2022年操作数据库中的数据 .pdf》由会员分享，可在线阅读，更多相关《2022年操作数据库中的数据 .pdf（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