时间：2021-07-01 10:21:17 帮助过：4人阅读
写死的方法：
select Dep [部门], SUM(case Material when ‘材料1‘ then Mcount else 0 end)[材料1], SUM(case Material when ‘材料2‘ then Mcount else 0 end)[材料2], SUM(case Material when ‘材料3‘ then Mcount else 0 end)[材料3] from DepCount group by Dep;
动态行列转换：
--申明一个字符串变量，以供动态拼装 declare @sql varchar(8000) --拼装SQL命令 set @sql = ‘select Dep [部门]‘ --动态地获得材料，为每个材料构建一个列 select @sql = @sql + ‘,sum(case Material when ‘‘‘+Material+‘‘‘ then MCount else 0 end) [‘+Material+‘]‘ from (select distinct Material from DepCount) as a --最终加上选择源和GROUP BY语句 select @sql = @sql+‘ from DepCount group by Dep‘ --执行SQL命令 exec(@sql)
动态语句可以根据实际材料种类来查找。
但是动态语句执行效率低，SQL命令长度根据实际表的内容变化，无法保证命令一定能运行。
3 ADO NET支持哪几种数据源
SQL Server 数据库、Oracle数据库、OLEDB提供商、ODBC提供商等
ADO NET和数据库的连接
1 请简要叙述数据库连接池的机制
数据库连接池，顾名思义就是一个存储数据库连接额缓冲池。由于连接和断开一个数据库的花销很大，反复连接和断开数据库的性能影响非常严重。而在.NET程序中，有时候无法预测下一次数据库访问的需求何时到来，所以通常的做法是，使用完一个连接后立即关闭它，这就需要ADO.NET的内部机制来维护这个访问池。连接池有选择地保留程序释放的数据库连接，以便以后使用。只要用户在连接上调用Open，池进程就会检查池中是否有可用的连接。如果某个池连接可用，会将该连接返回给调用者，而不是打开新连接。应用程序在该连接调用Close时，池进程会将连接返回到活动连接池中，而不是真正关闭连接。连接返回到池中后，即可在下一个Open调用中重复使用。
默认情况下，数据库连接池处于启用状态，但用户可以通过代码显示表明不使用数据库连接池。
using (SqlConnection connection = new SqlConnection( "Integrated Security=SSPI;Initial Catalog=TestDatabase; Pooling=false")) { //不使用数据库连接池 connection.Open(); }
连接字符串中关于线程池的可选参数：
|
名 称 |
默 认 值 |
说 明 |
|
Connection Lifetime |
0 |
当连接返回到池中时，将对它的创建时间和当前时间进行比较，如果时间间隔超过由Connection Lifetime指定的值（以秒为单位），则会毁坏该连接。在聚集配置中可以使用它来强制在运行服务器和刚联机的服务器之间达到负载平衡。如果值为0，则将使池连接具有最大的超时期限 |
|
Connection Reset |
‘true‘ |
确定从池中移除数据库连接时是否将其重置。对于Microsoft SQL Server 7.0，如果设置为false，将避免在获取连接时经历一个额外的往返过程，但必须注意的是连接状态（如数据库上下文）不会被重置 |
|
Enlist |
‘true‘ |
当为true时，如果存在事务上下文，池管理程序将自动在创建线程的当前事务上下文中登记连接 |
|
Max Pool Size |
100 |
池中允许的最大连接数 |
|
Min Pool Size |
0 |
池中维护的最小连接数 |
|
Pooling |
‘true‘ |
当为true时，将从相应的池中取出连接，或者在必要时创建连接并将其添加到相应的池中 |
数据库连接池和线程池等其他缓冲池不同，由于数据源和连接参数选择的不同，每个数据库连接并不是完全通用的，ADO.NET通过连接字符串判断池内连接是否符合新的数据库连接需求。如果池中有相同字符串的连接，如果有则直接分配给用户，如果不存在就新建连接。
using (SqlConnection connection = new SqlConnection( "Integrated Security=SSPI;Initial Catalog=TestDatabase")) { //假设这是系统启动后的第一个数据库连接请求 //一个新的连接将被建立 connection.Open(); } using (SqlConnection connection = new SqlConnection( "Integrated Security=SSPI;Initial Catalog=TestDatabase1")) { //由于连接字符串和上一个连接不同， //保存在数据库连接池中的连接不能被使用 connection.Open(); } using (SqlConnection connection = new SqlConnection( "Integrated Security=SSPI;Initial Catalog=TestDatabase")) { //连接字符串和第一个连接相同 //保存在连接池中的第一个连接被使用 connection.Open(); }
事实上，ADO.NET的组件本身并不直接包含连接池，而针对不同类别机制的数据源制定不同的连接池方案。对于SqlClient、OracleClient命名控件下的组件，使用的连接池是由托管代码直接编写的，可以理解为连接池直接在.NET框架中运行。而对于OLE DB和ODBC的数据源来说，连接池的实现完全依靠OLD DB和ODBC提供商实现，ADO.NET只与其约定相应规范。
2 如何提高连接池内连接的重用率
连接池重用率低下的原因
由于数据库连接池仅按照数据库连接字符串来判断连接是否可重用，所以连接字符串内的任何改动都会导致连接不能重用。而就系统内部而言，数据库连接字符串中最常被修改的两个属性就是数据库名和用户名/密码。对于使用多数据库的系统来说，只有同一数据库的连接才会被共用。而对于多用户的系统而言，只有同一用户的申请才能共用数据库连接。图9.4和图9.5展示了这两种情况。
如何提高数据库连接池重用率
在进行设计时，我们期望的最佳效果是连接池内的连接可以供所有用户重用，并且能够针对同一数据源内的不同数据库。对于实际系统来说，解决这两个问题通常意味着数据库中的额外工作，以及安全性方面的隐患。系统设计师在进行设计时，需要权衡利弊关系，根据实际情况来制定措施。下面提供一种能有效提供数据库连接池重用率，但会带来一点小安全隐患的方案。
同一数据库共享一个连接
同一用户共享一个连接
1）建立跳板数据库
在数据库内建立一个所有权限用户都能够访问的跳板数据库，在进行数据库连接时先连接到该数据库，然后再使用use"数据库名"这样的SQL语句来选择需要访问的数据库，这样就能够避免因为访问的数据库不一致而导致连接字符串不一致的情况，下面的代码演示了这一做法：
//这里使用"Entry"数据库作为跳板数据库 //然后再使用databaseName指定的数据库 SqlCommand cmd = new SqlCommand(); using (SqlConnection connection = new SqlConnection( "Server=localhost;uid=xxx;pwd=xxx;database=Entry")) { connection.Open(); command.ExecuteNonQuery("USE " + databaseName); }
2）不使用数据库用户系统来管理系统权限
这样做的结果是永远使用管理员的账号来连接数据库，而在做具体工作时再根据用户的实际权限，使用代码限定操作。带来的好处就是，数据库看连接字符串不会因为实际用户的不同而不同。下图展示了采用上述方案后数据库连接池的使用情况。
为了提高数据库连接池的重用率，唯一的方法就是尽量保证系统访问数据库所使用的连接字符串不变。例如建立跳板数据库，使所有连接都首先尝试访问跳板数据库。另外，统一使用超级用户账号可以进一步统一连接字符串，但这为系统带来了安全上的隐患。
3 一个连接字符串可以包含哪写属性
| 关 键 字 | 默 认 值 | 说 明 |
| Application Name | N/A | 应用程序的名称，或者“.Net SqlClient Data Provider”（如果不提供应用程序名称） |
| Asynch | ‘false‘ | 如果设置为true，则启用异步操作支持。可识别的值为true、false、yes和no |
| AttachDBFilename | N/A | 主数据库文件的名称，包括可连接数据库的完整路径名。该路径可以是绝对路径，也可以是相对路径，这取决于是否使用DataDirectory替换字符串。如果使用DataDirectory，则对应的数据库文件必须存在于替换字符串指向的目录的子目录中。注意：远程服务器、HTTP及UNC路径名不受支持。必须按照如下方式使用关键字“database”（或其别名之一）指定数据库名称："AttachDbFileName=|DataDirectory|\data\YourDB.mdf;integrated security=true;database=YourDatabase" |
| extended properties | ||
| Initial File Name | ||
| Connect Timeout | 15 | 在终止尝试并产生错误之前，等待与服务器的连接的时间长度（以秒为单位） |
| Connection Timeout | ||
| Context Connection | ‘false‘ | 如果应对SQL Server进行进程内连接，则为true |
| Current Language | N/A | SQL Server语言记录名称 |
| Data Source | N/A | 要连接的SQL Server实例的名称或网络地址。可以在服务器名称之后指定端口号：server=tcp:servername, portnumber。指定本地实例时，始终使用（local）。若要强制使用某个协议，请添加下列前缀之一：np:(local)、tcp:(local)或lpc:(local) |
| Server | ||
| Address | ||
| Addr | ||
| Network Address | ||
| Encrypt | ‘false‘ | 当该值为true时，如果服务器端安装了证书，则SQL Server将对所有在客户端和服务器之间传送的数据使用SSL加密。可识别的值为true、false、yes和no |
| Failover Partner | N/A | 在其中配置数据库镜像的故障转移合作伙伴服务器的名称。.NET Framework 1.0或1.1版不支持Failover Partner关键字 |
| Initial Catalog | N/A | 数据库的名称 |
| Database | ||
| Integrated Security | ‘false‘ | 当为false时，将在连接中指定用户ID和密码。当为true时，将使用当前的Windows账户凭据进行身份验证。可识别的值为true、false、yes、no以及与true等效的SSPI |
| Trusted_Connection | ||
| MultipleActiveResultSets | ‘true‘ | 如果为true，则应用程序可以维护多活动结果集（MARS）。如果为false，则应用程序必须在执行该连接上的任何其他批处理之前处理或取消一个批处理中的多个结果集。可识别的值为true和false。.NET Framework 1.0或1.1版不支持该关键字 |
| Network Library Net | ‘dbmssocn‘ | 用于建立与SQL Server实例的连接的网络库。支持的值包括dbnmpntw（命名管道）、dbmsrpcn（多协议）、dbmsadsn（Apple Talk）、dbmsgnet（VIA）、dbmslpcn（共享内存）及dbmsspxn （IPX/SPX）和dbmssocn（TCP/IP）。相应的网络DLL必须安装在要连接的系统上。如果不指定网络而使用一个本地服务器（比如“.”或“（local)”），则使用共享内存 |
| Packet Size | 8192 | 用来与SQL Server的实例进行通信的网络数据包的大小，以字节为单位 |
| Password- 或 -Pwd | N/A | SQL Server账户登录的密码。为保持高安全级别，可以使用Integrated Security或Trusted_Connection关键字 |
| Persist Security Info | ‘false‘ | 当该值设置为false或no（强烈推荐）时，如果连接是打开的或者一直处于打开状态，那么安全敏感信息（如密码）将不会作为连接的一部分返回。重置连接字符串将重置包括密码在内的所有连接字符串值。可识别的值为true、false、yes和no |
| Replication | ‘false‘ | 如果使用连接来支持复制，则为true |
| TrustServerCertificate | ‘false‘ | 如果设置为true，则使用SSL对通道进行加密，但不通过证书链对可信度进行验证。如果将TrustServerCertificate设置为true并将Encrypt设置为false，则不对通道进行加密。可识别的值为true、false、yes和no |
| Type System Version | N/A | 指示应用程序期望的类型系统的字符串值。 |
| 可能的值有：Type System Version=SQL Server 2000;Type System Version=SQL Server 2005;Type System Version=Latest;如果设置为SQL Server 2000，将使用SQL Server 2000类型系统。 | ||
| 与SQL Server 2005实例连接时，执行下列转换：XML到NTEXT；UDT到VARBINARY；VARCHAR(MAX)、NVARCHAR(MAX)和VARBINARY(MAX)分别到TEXT、NEXT和IMAGE。 | ||
| 如果设置为SQL Server 2005，将使用SQL Server 2005类型系统。对ADO.NET的当前版本不进行任何转换。如果设置为Latest，将使用此客户端-服务器对无法处理的最新版本。这个最新版本将随着客户端和服务器组件的升级自动更新 | ||
| User ID | N/A | SQL Server登录账户。为保持高安全级别，可以使用Integrated Security或Trusted_Connection关键字 |
| User Instance | ‘false‘ | 一个值，用于指示是否将连接从默认的SQL Server速成版实例重定向到调用方账户下运行的运行时启动的实例 |
| Workstation ID | 本地计算机名称 | 连接到SQL Server的工作站的名称 |
4 CommandBehavior.CloseConnection有何作用
CommandBehavior.CloseConnection解决了流读取数据模式下，数据库连接不能有效关闭的情况。当某个XXXDataReader对象在生成时使用了CommandBehavior.CloseConnection，那数据库连接将在XXXDataReader对象关闭时自动关闭。
1 partial class UseCommandBehavior 2 { 3 /// <summary> 4 /// 测试方法 5 /// </summary> 6 /// <param name="args"></param> 7 static void Main(string[] args) 8 { 9 //建立连接 10 SqlConnection con = new SqlConnection(conn_String); 11 try 12 { 13 //测试使用了CommandBehavior.CloseConnection的方法 14 Console.WriteLine("测试使用了CommandBehavior.CloseConnection的方法："); 15 SqlDataReader sdr = GetReader_CloseConnection(con); 16 while (sdr.Read()) { } 17 sdr.Close(); 18 Console.WriteLine("读取完毕后的连接状态：" + con.State.ToString()); 19 //测试没有使用CommandBehavior.CloseConnection的方法 20 Console.WriteLine("测试没有使用CommandBehavior.CloseConnection的方法："); 21 SqlDataReader sdr1 = GetReader_NoCloseConnection(con); 22 while (sdr1.Read()) { } 23 sdr1.Close(); 24 Console.WriteLine("读取完毕后的连接状态：" + con.State.ToString()); 25 Console.Read(); 26 } 27 finally 28 { 29 //确保连接被关闭 30 if (con.State != ConnectionState.Closed) 31 con.Close(); 32 } 33 } 34 } 35 36 partial class UseCommandBehavior 37 { 38 const String conn_String = "Server=localhost;Integrated Security=true;database=NetTest"; 39 const String Sql = "select * from dbo.DepartCost"; 40 /// <summary> 41 /// 使用CommandBehavior.CloseConnection 42 /// </summary> 43 /// <param name="con">为了测试需要，传入连接对象</param> 44 /// <returns></returns> 45 static SqlDataReader GetReader_CloseConnection(SqlConnection con) 46 { 47 try 48 { 49 //打开连接，执行查询 50 //并且返回SqlDataReader 51 con.Open(); 52 SqlCommand cmd = con.CreateCommand(); 53 cmd.CommandText = Sql; 54 SqlDataReader dr = cmd.ExecuteReader 55 (CommandBehavior.CloseConnection); 56 return dr; 57 } 58 finally 59 { 60 //因为使用了CommandBehavior.CloseConnection， 61 //这里不需要关闭连接 62 //con.Close(); 63 } 64 } 65 /// <summary> 66 /// 不使用CommandBehavior.CloseConnection 67 /// </summary> 68 /// <param name="con">为了测试需要，传入连接对象</param> 69 /// <returns></returns> 70 static SqlDataReader GetReader_NoCloseConnection(SqlConnection con) 71 { 72 try 73 { 74 //打开连接，执行查询 75 //并且返回SqlDataReader 76 con.Open(); 77 SqlCommand cmd = con.CreateCommand(); 78 cmd.CommandText = Sql; 79 SqlDataReader dr = cmd.ExecuteReader(); 80 return dr; 81 } 82 finally 83 { 84 //为了使返回的SqlDataReader可用，这里不能关闭连接 85 //con.Close(); 86 } 87 } 88 }View Code
输出：
测试使用了CommandBehavior.CloseConnection的方法：
读取完毕后的连接状态：Closed
测试没有使用CommandBehavior.CloseConnection的方法：
读取完毕后的连接状态：Open
使用ADO NET读写数据库
1 ADO NET支持哪两种方式来访问关系数据库
连接式的访问
读取数据时保持和数据库的连接，并且在使用时独占整个连接，逐步地读取数据。这种模式较适合大数据量并且不能准确预测需要读取多少记录的情况。使用XXXCommand和XXXDataReader对象来读取数就是一个典型的连续式数据访问，这种模式的缺点是数据库连接较长时间地保持在打开状态。
示例：连接模式读取
1 /// <summary> 2 /// 数据访问层类型 3 /// </summary> 4 public class DataHelper 5 { 6 static readonly String conn_String ="Server=localhost;Integrated Security=true;database=NetTest"; 7 /// <summary> 8 /// 使用给定的sql来访问数据库 9 /// 返回SqlDataReader对象，提供连接式访问 10 /// </summary> 11 /// <param name="sql">SQL命令</param> 12 /// <returns>SqlDataReader对象</returns> 13 public static SqlDataReader GetReader(String sql) 14 { 15 SqlConnection con = new SqlConnection(conn_String); 16 try 17 { 18 //打开连接，执行查询 19 //并且返回SqlDataReader 20 con.Open(); 21 using (SqlCommand cmd = con.CreateCommand()) 22 { 23 cmd.CommandText = sql; 24 SqlDataReader dr = cmd.ExecuteReader 25 (CommandBehavior.CloseConnection); 26 return dr; 27 } 28 } 29 //连接数据库随时可能发生异常 30 catch (Exception ex) 31 { 32 if (con.State != ConnectionState.Closed) 33 con.Close(); 34 return null; 35 } 36 } 37 38 } <
