ADO.NET最佳实践

1.防止 SQL攻击（name'--   ' 1=1 --)

 string strSql = "select * from ScoreTable where UserName='"+tbUserName.Text.Replace("'", "''")
    +"' and PassWord= '"+tbPassWord.Text.Replace("'", "''")+"'";
   SqlCommand com = new SqlCommand(strSql,con);

 

ADO.NET 中的连接池

 

• 如果使用的是OleDbConnection类，则连接池将由提供程序自动处理，您不必自己进行管理。

• 如果使用的是SqlConnection类，则连接池被隐式管理，但也提供选项允许您自己管理池。

– 在连接字符串中指定：

•pooling =true;// 默认为true

•connection lifetime=5;// 默认为0

•min pool size=1;// 默认为0

•max pool size=50“;// 默认为100

 

 

用 DataAdapter 优化连接

•DataAdapter 的 Fill 和 Update 方法在连接关闭的情况下自动打开为相关命令属性指定的连接。如果 Fill 或 Update 方法打开了连接，Fill 或 Update 将在操作完成的时候关闭它。为了获得最佳性能，仅在需要时将与数据库的连接保持为打开。同时，减少打开和关闭多操作连接的次数。

• 如果只执行单个的 Fill 或 Update 方法调用，建议允许 Fill 或 Update 方法隐式打开和关闭连接。如果对Fill 和/或 Update 调用有很多，建议显式打开连接，调用 Fill 和/或 Update，然后显式关闭连接。

 

始终关闭 Connection 和 DataReader

• 完成对 Connection 或 DataReader 对象的使用后，总是显式地关闭它们。尽管垃圾回收最终会清除对象并因此释放连接和其他托管资源，但垃圾回收仅在需要时执行。

在 C# 中使用“Using”语句

•using 语句在离开自己的作用范围时，会自动调用被“使用”的对象的 Dispose。

 

连接异常

 

•DataException 类:表示使用 ADO.NET 组件发生错误时引发的异常

•DBConcurrencyException 类:在更新操作过程中受影响的行数等于零时，由 DataAdapter 所引发的异常。

•SqlException 类:当 SQL Server 返回警告或错误时引发的异常。无法继承此类。

 

SqlException 类

 

• 属性

• 任何时候只要 SQL Server .NET 数据提供程序遇到服务器生成的错误，就会创建该类。SqlException 始终包含至少一个SqlError实例。

• 严重程度等于或小于 10 的消息是信息性消息，它们指示由用户输入信息中的错误所导致的问题。严重程度 11 至 16 的消息是由用户生成的，可以由用户更正。严重程度 17 至 25 的消息指示软件或硬件错误。当发生严重程度为 17、18 或 19 的错误时，虽然可能无法执行特定语句，但仍可以继续工作。

• 当严重程度等于或小于 19 时，SqlConnection 保持打开状态。当严重度等于或大于 20 时，服务器通常会关闭 SqlConnection。但是，用户可以重新打开连接并继续操作。在这两种情况下，执行命令的方法都会生成SqlException。

 

Command 对象的使用

 

方法	描述
Cancel	取消数据命令的执行
CreateParameter	创建一个新的参数
ExecuteNonQuery	执行命令并返回受影响的行数
ExecuteReader	执行命令并返回生成的 DataReader
ExecuteScalar	执行查询并返回结果集中的第一行的第一列
ExecuteXmlReader	执行命令并返回生成的 XMLReader
Prepare	在数据源上创建一个准备好的命令版本

 

DataReader

 

• 当数据命令返回结果集时，用DataReader 来检索数据

•DataReader 对象返回一个来自数据命令的只读的、只能向前的数据流

• 内存中每次仅有一个数据行，因此开销很少

 

ExecuteScalar 和 ExecuteNonQuery

 

• 如果想返回像 Count(*)、Sum(Price) 或 Avg(Quantity) 的结果那样的单值，可以使用Command.ExecuteScalar。

• 因为单独一步就能完成，所以ExecuteScalar不仅简化了代码，还提高了性能；要是使用DataReader就需要两步才能完成（即，ExecuteReader + 取值）。

• 使用不返回行的 SQL 语句时，例如修改数据（例如INSERT、UPDATE 或 DELETE）或仅返回输出参数或返回值，请使用ExecuteNonQuery。这避免了用于创建空DataReader的任何不必要处理。

 

使用 SqlCommand 的最佳实践

 

• 存储过程是SQLServer数据库的一个重要特色

• 存储过程执行效率比SQL文本命令要高的多

• 提高了程序的复用性

• 存储过程中可以使用变量和条件

• 可以在存储过程中使用参数

• 如果调用存储过程，将SqlCommand的CommandType属性指定为StoredProcedure的CommandType。这样通过将该命令显式标识为存储过程，就不需要在执行之前分析命令。

 

  使用 Prepare 方法

•         对于重复作用于数据源的参数化命令，Command.Prepare 方法能提高性能。

•         对于一些数据源（例如 SQL Server 2000），命令是隐式优化的，不必调用 Prepare。

•         对于其他（例如 SQL Server 7.0）数据源，Prepare 会比较有效。

 

测试 Null

•         如果表（在数据库中）中的列允许为空，就不能测试参数值是否“等于”空。

•         SELECT * FROM Customers

WHERE ((LastName = @LastName) OR (LastName IS NULL AND @LastName IS NULL))

把 Null 作为参数值传递

•         对数据库的命令中，当把空值作为参数值发送时，不能使用 null（Visual Basic庐 .NET 中为 Nothing）。而需要使用 DBNull.Value。

 

事务处理

ADO.NET 的事务模型已经更改。

w        在 ADO 中，当调用 StartTransaction 时，调用之后的任何更新操作都被视为是事务的一部分。

w        但是，在 ADO.NET 中，当调用 Connection.BeginTransaction 时，会返回一个 Transaction 对象，需要把它与 Command 的 Transaction 属性联系起来。这种设计可以在一个单一连接上执行多个根事务。

w        如果未将 Command.Transaction 属性设置为一个针对相关的 Connection 而启动的 Transaction，那么 Command 就会失败并引发异常。

 

DataReader 的常见问题:

•         在访问相关 Command 的任何输出参数之前，必须关闭 DataReader。完成读数据之后总是要关闭DataReader。

•         当访问列数据时，使用类型化访问器

•         一个单一连接每次只能打开一个 DataReader。

•         默认情况下，DataReader 每次 Read 时都要把整行加载到内存。这允许在当前行内随机访问列。如果不需要这种随机访问，为了提高性能，就把 CommandBehavior.SequentialAccess 传递给 ExecuteReader 调用

•         如果已经完成读取来自 DataReader 的数据，但仍然有大量挂起的未读结果，就在调用 DataReader 的 Close 之前先调用 Command 的 Cancel。

// 计算使用连接池的时间

              dtStart = DateTime.Now;

              for(int i=1;i<=nConNum;i++)

              {

//using 对象的使用

                   using(SqlConnection con = new SqlConnection(strConusePool))

                   {

                       con.Open();

                       con.Close();

                   }

              }

  二进制大对象 (BLOB)

•         用 DataReader 检索二进制大对象 (BLOB) 时，应该把 CommandBehavior.SequentialAccess 传递给 ExecuteReader 方法调用。

•         SequentialAccess 将 DataReader 的行为设置为只加载请求的数据。然后还可以使用 GetBytes 或 GetChars 控制每次加载多少数据。

 

 

 事务处理

 

public void DoDemo()
  {
   SqlConnection con = new SqlConnection("Server=localhost; Integrated Security=SSPI; database=mydatabase");
   SqlCommand cmdUpdateScore = new SqlCommand("UpdateScore",con);

   cmdUpdateScore.CommandType = CommandType.StoredProcedure;
   cmdUpdateScore.Parameters.Add( new SqlParameter("@username", "周润发") );
   cmdUpdateScore.Parameters.Add( new SqlParameter("@score", "700" ));
  
   con.Open();
   SqlTransaction trans = con.BeginTransaction();
   cmdUpdateScore.Transaction=trans;
   try
   {
    cmdUpdateScore.ExecuteNonQuery();
    trans.Commit(); // No error so commit the transaction
   }
   catch
   {
    trans.Rollback(); // Rollback the update
    
   }

  }

 

 

 class Demo
 {
  public void DoDemo()
  {
   DataSet ds = new DataSet();
   ExecuteOptions oExecute = new ExecuteOptions();
   ds = oExecute.ExecuteandFill();
   //Display all sales transactions on the console
   
   //An action query doesnt generate any results for processing
   oExecute.ExecuteNonQuery();

   //Use the reader to walkthrough the results in a forward-only manner
   oExecute.ExecuteReader();
   
   //Return the total number of sale transactions in the database
   int nSum=oExecute.ExecuteScalar();
   Console.WriteLine("Record count is " +nSum.ToString());
   
  }
 }
 class ExecuteOptions
 {
  public SqlDataReader ExecuteReader()
  {
   SqlConnection con = new SqlConnection("Server=localhost; Integrated Security=SSPI; database=mydatabase; Max Pool Size=75; Min Pool Size=5");
   SqlCommand cmdTitle = new SqlCommand("select Username,password,score from scoretable",con);
   cmdTitle.CommandType=CommandType.Text;

   SqlDataReader dr;
   con.Open();
   dr =cmdTitle.ExecuteReader(CommandBehavior.CloseConnection);
   return dr;
  }
  public int ExecuteScalar()
  {
   SqlConnection con = new SqlConnection("Server=localhost; Integrated Security=SSPI; database=mydatabase;Max Pool Size=75; Min Pool Size=5");
   SqlCommand cmdTitleCount = new SqlCommand("select count(*) from scoretable",con);
   cmdTitleCount.CommandType=CommandType.Text;
   con.Open();
   return Convert.ToInt32(cmdTitleCount.ExecuteScalar().ToString());
  }
  public void ExecuteNonQuery()
  {
   SqlConnection con = new SqlConnection("Server=localhost; Integrated Security=SSPI; database=mydatabase;Max Pool Size=75; Min Pool Size=5");
   SqlCommand cmdUpdateSales = new SqlCommand("Update scoretable set score = score+200 where username='成龙'",con);
   cmdUpdateSales.CommandType=CommandType.Text;
   con.Open();
   cmdUpdateSales.ExecuteNonQuery();
  }
  public DataSet ExecuteandFill()
  {
   SqlConnection con = new SqlConnection("Server=localhost; Integrated Security=SSPI; database=mydatabase;Max Pool Size=75; Min Pool Size=5");
   SqlDataAdapter da = new SqlDataAdapter("select username, password,score from scoretable",con);
   DataSet ds = new DataSet();
   con.Open();
   da.Fill(ds);
   return ds;
  }

 

Q：请问组合条件的查询能用存储过程吗？

A：可以啊。给一个网络上的例子供参考。

/*********************************************************************/

/* proc name : P_am_postjob_search */

/* */

/* Description: 申请单查询 */

/* */

/* parameters: @searchtime 申请时间 */

/* @search_poststatus 发布状态 */

/* @search_applystatus 招聘状态 */

/* @job_name 职位名 */

/* @Re_deptid 申请部门 */

/* date: 2003/11/5 */

/* */

/* history: */

/*********************************************************************/

create proc P_am_postjob_search

(

@searchtime varchar(5),

@search_poststatus char(1),

@search_applystatus char(1),

@job_name varchar(50),

@Re_deptid varchar(20)

)

as

 

declare @m_strSqlsearchtime varchar(255)

declare @m_strSqlpoststatus varchar(255) --Sql语句的条件部分

declare @m_strSqlapplystatus varchar(255) --Sql语句的条件部分

declare @m_strSqljob_name varchar(255) --Sql语句的条件部分

declare @m_strSqlRe_deptid varchar(255) --Sql语句的条件部分

 

select @m_strSqlsearchtime= case

when @searchtime='0' then /*申请时间*/

'select Re_NO,post_id,job_name,Re_num,Re_deptid,Re_applydate,job_poststatus,job_posttype,job_posttype,job_applystatus from am_postjob where 1=1'

else

'select Re_NO,post_id,job_name,Re_num,Re_deptid,Re_applydate,job_poststatus,job_posttype,job_posttype,job_applystatus from am_postjob where '+@searchtime+'>datediff(dd,Re_applydate,getdate())'

end

 

select @m_strSqlpoststatus= case

when @search_poststatus<>'n' /*发布状态*/

then ' and job_poststatus='''+@search_poststatus+''''

else ''

end

select @m_strSqlapplystatus= case

when @search_applystatus<>'n' /*招聘状态*/

then ' and job_applystatus='''+@search_poststatus+''''

else

''

end

select @m_strSqljob_name= case

when @job_name<>'' /*职位关键字*/

then ' and job_name like ''%'+@job_name+'%'''

else

''

end

select @m_strSqlRe_deptid

= case

when @Re_deptid<>'' /*申请部门ID*/

then ' and Re_deptid='''+@Re_deptid+''''

else

''

end

exec ( @m_strSqlsearchtime + @m_strSqlpoststatus + @m_strSqlapplystatus + @m_strSqljob_name + @m_strSqlRe_deptid +'order by post_id asc')

：能否详细讲解一下，自动增量冲突的解决办法？jyming22@sohu.com
A: 要避免这种情况，建议在使用数据源上自动增量的列以及 DataSet 上自动增量的列时，把 DataSet 中的列创建为 AutoIncrementStep 值等于 -1 并且 AutoIncrementSeed 值等于 0，另外，还要确保数据源生成的自动增量标识值从 1 开始，并且以正阶值递增。因此，DataSet 为自动增量值生成负数，与数据源生成的正自动增量值不冲突。另外一个选择是使用 Guid 类型的列，而不是自动增量的列。生成 Guid 值的算法应该永远不会使数据源中生成的 Guid 值与 DataSet 中生成的 Guid 值一样。

如果自动增量的列只是用作唯一值，而且没有任何意义，就考虑使用 Guid 代替自动增量的列。它们是唯一的，并且避免了使用自动增量的列所必需的额外工作。

：有关二制大对象Blob的使用。

A: 请参考如下代码：

Byte[] blob = null;
FileStream fs = null;
const string sConn = "server=(local);Initial
Catalog=Northwind;UID=ctester;PWD=password";
try {
SqlConnection conn = new SqlConnection(sConn);
SqlCommand cmd = new SqlCommand("SELECT Picture FROM Categories WHERE
CategoryName='Builder'", conn);
cn.Open();
SqlDataReader sdr = cmd.ExecuteReader();
sdr.Read();

blob = new Byte[(sdr.GetBytes(0, 0, null, 0, int.MaxValue))];
sdr.GetBytes[0, 0, blob, 0, blob.Length);
sdr.Close();
conn.Close();
fs = new FileStream("c://Builder.doc", FileMode.Create, FileAccess.Write);

fs.Write(blob, 0, blob.Length);
fs.Close();
} catch (SqlException e){
Console.WriteLine("SQL Exception: " + e.Message);
} catch (Exception e) {
Console.WriteLine("Exception: "+ e.Message);
}

 请问怎么把一个表的数据，约束，父子表关系等一起赋值到数据集（或数据表），谢谢

A: 向 DataSet 添加多个结果集时，每个结果集都放在一个单独的表中。当创建 DataTable 对象时，Fill 操作通常只创建列名元数据。但是，你能通过设置数据适配器的MissingSchemaAction属性来重载Fill的默认的行为。例如，要使Fill建立的表包含主键信息、唯一约束、列属性、是否允许空值、列的最大长度、只读列、自动增加列等等，只需要指定DataAdapter.MissingSchemaAction为MissingSchemaAction.AddWithKey。作为选择，你能在调用DataAdapter.Fill前调用DataAdapter.FillSchema来确保数据集被填充时大纲已经准备好了。
　　调用FillSchema将再次访问服务器并检索附加的大纲信息。为了提高性能，最好指定数据集的大纲，或者在调用Fill前设置数据适配器的MissingSchemaAction。

怎么使用ADO.NET把Excel表到入数据库？

A:    string strFileName ;//Excel文件名称

         DataSet myDataset = new DataSet();

         OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"

                                     + "Data Source="+strFileName+";"+"Extended Properties='Excel 8.0;'");

         OleDbCommand Command  = new OleDbCommand("SELECT       * FROM [Sheet1$]",con);

         System.Data.OleDb.OleDbDataAdapter myData = new OleDbDataAdapter(Command);

         myData.Fill(myDataset);

         数据填充到DataSet中后，你就可以把它写入数据库了。