[转帖]ADO.NET轻松操纵数据库 - C# 论坛

楼主

问题点数：0 回复次数：0

[转帖]ADO.NET轻松操纵数据库

ADO.NET提供了Connection来连接数据库，同时也提供了Command对象来查询数据库。同Connection对象一样，Command也有两种：OleDbCommand和SqlCommand.其区别同Connection对象。

要操纵数据库，必须先使用Connection来连接到数据库，再创建一个Command来查询。有几种创建方式，例：

SqlCommand cmd;

string strCon="server=localhost;database=Northwind;Trusted_Connection=Yes;"; string strqry="select * from Categories"; SqlConnection con=new SqlConnection(strCon); con.Open(); 1cmd=con.CreateCommand(); //这里使用用Connection对象的CreateCommand方法来创建一个Command对象。 cmd.CommandText=strqry; //SqlDataReader reader=cmd.ExecuteReader();

2cmd=new SqlCommand(); //直接使用new 关键字来创建 cmd.CommandText=strqry; cmd.Connection=con;//设置与数据库的连接

　3cmd=new SqlCommand(strqry,con);　//直接在new的时候带两个参数来创建

　执行方式：

（主要有这么几种，cmd.ExecuteReader();cmd.ExecuteNonQuery();cmd.ExecuteScalar();cmd.ExecuteXmlReader();）

　１，ExecuteReader();返回一个SqlDataReader对象或OleDbDataReader对象，这个看你的程序的需要去　　做。可以通过这个对象来检查查询结果，它提供了“游水”式的执行方式，即从结果中读取一行之后，移动到另一行，则前一行就无法再用。有一点要注意的是执行之后，要等到手动去调用Read()方法之后，DataReader对象才会移动到结果集的第一行，同时此方法也返回一个Bool值，表明下一行是否可用，返回True则可用，返回False则到达结果集末尾。

使用DataReader可以提高执行效率，有两种方式可以提高代码的性能：一种是基于序号的查找，一个是使用适当的Get方法来查找。因为查询出来的结果一般都不会改变，除非再次改动查询语句，因此可以通过定位列的位置来查找记录。用这种方法有一个问题，就是可能知道一列的名称而不知道其所在的位置，这个问题的解决方案是通过调用DataReader 对象的GetOrdinal()方法，此方法接收一个列名并返回此列名所在的列号。例：

int id=reader.GetOrdinal("CategoryName"); while(reader.Read()) { Response.Write(reader[id]);

reader.Close(); }

至于第二种方式很直观，例：

while(reader.Read()) { 　　Response.Write(reader.GetInt32(0).ToString()+" "+reader.GetString(1).ToString()+" "); }

　DataReader的GetInt32()和GetString()通过接收一个列号来返回一个列的值，这两种是最常用的，其中　　还有很多其它的类型。

(注：DataReader对象在调用Close()方法即关闭与数据库的连接，如果在没有关闭之前又重新打开第二个连接，则会产生一条异常信息)

　2.,ExecuteNonQuery()这个方法并不返回一个DataReader对象，而是返回一个int类型的值，即在执行之后在数据库中所影响的行数。

　例：

　int affectrows=cmd.ExecuteNonQuery(); Response.Write(affectrows +" 条记录受影响");

3,ExecuteScalar() 这个方法不接受任何参数，仅仅返回查询结果集中的第一行第一列，而忽略了其它的行和列，而且返回的是一个object类型，在使用之前必须先将它强制转换为所需类型。如果返回的仅仅是一个单独的数据元，则可以使用此方法来提高代码的性能。例：

string strCon="server=localhost;database=Northwind;Trusted_Connection=Yes;"; string strqry="select count(*) from Categories"; SqlConnection con=new SqlConnection(strCon); con.Open(); SqlCommand cmd=con.CreateCommand(); int i=Convert.ToInt32(cmd.ExecuteScalar());　//必须强制转换

　4,ExecuteXmlReader() 此方法用于ＸＭＬ操作，返回一个XmlReader对象，由于系统默认没有引用 System.Xml名空间，因此在使用前必须前引入。例：

string strCon="server=localhost;database=Northwind;Trusted_Connection=Yes;"; SqlConnection con=new SqlConnection(strCon); con.Open(); SqlCommand cmd = new SqlCommand("select * from Categories FOR XML AUTO, XMLDATA", con); XmlReader xr=cmd.ExecuteXmlReader(); Response.Write(xr.AttributeCount);　　//这里获取当前节点上的属性个数

xr.Close();

　执行完毕之后，照样要显式地调用Close()方法，否则会抛出异常。

　使用参数化的查询

　　先看一段ＳＱＬ语句：select CategoryID,Description from Categories where CategoryID=? 其中的问号就是一个参数。但在使用的时候必须是带有＠前缀的命名参数，因为.NET数据提供程序不支持这个通用的参数标记“？”.使用参数化的查询可以大大地简化编程，而且执行效率也比直接查询字符串要高，也更方便，很多情况下都需要更改查询字符串，这种方式就提供了方便，只需更改参数的值即可。例：

　string strCon="server=localhost;database=Northwind;Trusted_Connection=Yes;"; SqlConnection con=new SqlConnection(strCon); con.Open(); string strqry="select * from Categories where CategoryID=@CategoryID";　//带参数的查询 SqlCommand cmd=new SqlCommand(strqry,con); cmd.Parameters.Add("@CategoryID",SqlDbType.Int,4);　//给参数赋于同数据库中相同的类型 cmd.Parameters["@CategoryID"].Value="3";　//给参数赋值，可灵活改变 SqlDataReader r=cmd.ExecuteReader();　 while(r.Read()) { Response.Write(r.GetString(2)+" ");　//取出指定参数列的值 } con.Close();　//切记关闭

　使用存储过程进行查询

　先看段存储过程的形式：create procedure cateproc (@CategoryID int(4)) as select * from Categories where CategoryID=@CategoryID? return。

　这个是数据库中的存储过程实现方式，要在程序中调用存储过程，一种方法是使用Command对象的　CommandType属性来实现。CommandType有三个枚举值：Text,TableDirect,StoredProcedure。只需将CommandType属性设为第三个值即可实现调用存储过程。例：

　string strCon="server=localhost;database=Northwind;Trusted_Connection=Yes;"; SqlConnection con=new SqlConnection(strCon); con.Open(); SqlCommand cmd=con.CreateCommand(); cmd.CommandText="cateproc"; cmd.CommandType=CommandType.StoredProcedure; cmd.Parameters.Add("@CategoryID",SqlDbType.Int,4); cmd.Parameters["CategoryID"].Value="2"; SqlDataReader r=cmd.ExecuteReader(); while(r.Read()) { Response.Write(r.GetString(2)+" "); } con.Close();

　其实在程序中实现调用存储过程的方式跟参数化查询很类似，有点旧鞋翻新的味道。

　cmd.CommandType=CommandType.StoredProcedure;这种方式有个缺点，就是当要查询的表，视图或存储过程的名称中有特殊的字符（如空格）的话，则将无法识别。因此还有一种方式就是:

　cmd.CommandText="{Call cateproc(?)}";　//这里是调用存储过程，问号为参数 cmd.CommandType=CommandType.Text;　//关键是这里。

　设置命令执行超时

　命令超时是指Command对象在等待结果的时间，（默认为３０秒）如果在３０秒内没执行查询，则Command抛出一个异常。也可以自己进行设置。例：cmd.CommandTimeout=60;

　取消执行查询

　有时因某种原因，需要临时取消命令的执行，可调用Command对象的Cancel()方法来退出执行，如果在未执行查询之前，Cancel()将不做任何事。

首先，ADO.NET中使用了DataAdapter 来处理与数据库的联机与脱机。当时开发人员设计了DataAdapter是为了能够处理脱机数据，方便操作，关于这一点，只要调用其Fill()方法即可,这时会在DataSet中创建一个新的名为“Table“的DataTable.要重新指定名可用DataAdapter.Fill(DataSet,“Tabelname“)。此时connection也关闭了。DataAdapter既可以用来提交查询，并将结果存储到DataSet中，也可以用来向数据库传递更改。仅仅使用其Update方法即可达到向数据库提交存储地DatSet中的更改。 DataAdapter将查询的结果存储在DataSet或DataTable对象中，当执行这一过程的时候，DataAdapter使用了一个Command来与数据库通讯，并在内部使用了DataReader来获取查询结果，最后才将结果复制到DataSet新行中去。这也是Fill的过程。如果有两个DataAdapter对象，都使用相同的Connection对象，在创建的时候就会创建两个Connection对象，而不是同一个，这种情况的解决方案是：

SqlConnection con=new SqlConnection("server=localhost;database=Northwind;Trusted_Connection=Yes;") SqlDataAdapter da=new SqlDataAdapter("select CategoryID,Description from Categories",con); SqlDataAdapter da=new SqlDataAdapter("select CategoryID,Description from Customers",con);

而不是将查询字符串，单独写成一行。

有时候可能并不希望DataSet中的架构与数据库中的架构相同，这种情况的解决方案之一是可以采用别名的方法，即select id as Product ID,amount as Product Amount from product;另外一种解决方案就是使用DataAdapter提供的TableMappings集合机制，通过它就可以将查询结果映射到DataSet结构中，这种方法更方便，更灵活。TableMappings属性返回一个DataTableMappingsCollection对象，其中包含了一组DataTableMappings，只要DataSet中相应的表名称与数据库中的表名相同，即可以使用它来创建一个映射(DataSet中可以有多个表)。TableMappings里还有一个ColumnMappings属性，其用法与TableMappings相似。其原理是DataAdapter从数据库读取数据后，利用DataReader从结果集中获取列名称，有一点特别要注意，即只能获取列名称而无法获取表名称，DataAdapter事先假定表名称为Table,接着遇到映射语句则进行表映射。不说了，看代码：

DataColumnMapping colMap; SqlConnection con=new SqlConnection("server=localhost;database=Northwind;Trusted_Connection=Yes;"); SqlDataAdapter da=new SqlDataAdapter("select CategoryID,Description from Categories",con); DataSet ds=new DataSet();

DataTableMapping tblMap=da.TableMappings.Add("Table","Ca"); //这里Table为关键，映射表名为Ca

colMap=tblMap.ColumnMappings.Add("CategoryID","ID"); //映射列表 colMap=tblMap.ColumnMappings.Add("Description","描述"); // Response.Write(tblMap.DataSetTable.ToString()); da.Fill(ds); DataTable dt=ds.Tables["Ca"]; //这里是映射后的表名，如果仍为数据库的表名，则无效，特别注意 this.DataGrid1.DataSource=dt; this.DataGrid1.DataBind();

运行代码后就会发现DataGrid1上的列名为ID和描述 (^_^)

（注：使用DataTableMapping 之前要前导入名空间System.Data.Common;）

还可以使用AddRange方法来简化表和列的映射：(一些代码同上面)

....... DataTableMapping tblMap=da.TableMappings.Add("Table","Ca"); DataColumnMapping[] colMapArray=new DataColumnMapping[]{new DataColumnMapping("CategoryID","产品号"),new DataColumnMapping("Description","描述")}; tblMap.ColumnMappings.AddRange(colMapArray); ......

这种映射关系只能从数据库中读取展示给用户，如果要将映射后的Table的更改提交给数据库，这时库发现其中列与库中列不同，便会发生异常，DataAdapter 同时也提供了MissingMappingAction属性来处理。

DataAdapter1.MissingMappingAction=MissingMappingAction.Passthrough/Ignore/Error

它接受MissingMappingAction的枚举值，Passthrough这个值表示如果在库中找不到与DataSet中相同列的话，就将此列映射到库中，Ignore枚举值表示忽略示出现的列，Error表示找不到相应的列则抛出异常。

分页：

分页在应用中是常有的事，而DataAdapter本身也提供了分页的简单功能，如：DataAdapter1.Fill(dataset,startrow,rownum,“tablename“)这种功能用于数据量较小的查询就可以，但当有大量数据的时候，就会发现这种分页的问题的存在。它的原理是假如有一百行数据，分成十页，每页十行，当获取每一页的时候，返回前１０行，再接着，第二页，删除了前１０行而获取接下的１０行，在这一次中，只是为了获取１０行数据，但数据库却返回了２０行，依此类推，第１０页的时候就会返回１００行，而DataAdapter本身就帮我们删除了９０行，因为我们看上去返回的还是１０行，这种性能太低。因此本篇继续介绍另外一种性能较高的分页方法。实际上这种分页方法是将上一页最后一行的键值存储下来，直接在SQL语句中就过滤掉了，不象前面那种到DataAdapter这边才过滤掉。例：

con=new SqlConnection("server=localhost;database=Northwind;Trusted_Connection=Yes;"); da=new SqlDataAdapter("select top 50 CustomerID,CompanyName from Customers where　CustomerID>'BOTTM'",con); ds=new DataSet(); da.Fill(ds,"Categories"); this.DataGrid1.DataSource=ds.Tables["Categories"]; this.DataGrid1.DataBind(); con.Close();

这里假如上一页最后一个键值为”BOTTM”，可以将它用参数替代掉，这样就查出了在'BOTTM'之后的５０行。这种方法实现简单效率也高。