ADO.Net知识整理

1.        数据库连接

a)        Access数据库(OLE DB连接)

Access2007连接

Provider=Microsoft.ACE.OLEDB.12.0;UserID=Admin;Data Source=[路径].accdb;Jet OLEDB:DatabasePassword=;

Access2003连接

Provider=Microsoft.Jet.OLEDB.4.0;Mode=ReadWrite;PersistSecurity Info=False;Data Source=[路径]

Provider=Microsoft.Jet.OLEDB.4.0;DataSource=[路径];User Id=admin;Password=;

b)        SQL-Server数据库

DataSource=.;Initial Catalog=claa;Integrated Security=True

Provider=sqloledb;DataSource=Aron1;Initial Catalog=pubs;User Id=sa;Password=123;

注：这里只提供了OLE DB的连接方式，而且根据不同的安全级别也有不同的连接方式，还有ODBC的连接方式，连接字符串都有差异，具体可以MSDN。

2.        常用类

a)        Connection连接数据库(OleDbConnection, SqlConnection)

?  连接字符串是以分号（;）分隔的一系列 名称/值 对的选项，这些选项的顺序并不重要，大小写也不重要。

OleDbConnectionconn = new OleDbConnection(“[连接字符串]”);

?  将连接字符串放到config文件中便于保存和随时修改

<connectionStrings>

  <add name="Northwind"connectionString=" Microsoft.Jet.OLEDB.4.0;DataSource=[路径];User Id=admin;Password=;"/>   

</connectionStrings>

获取连接字符串：

stringconnStr = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

b)       Command执行SQL语句(OleDbCommand,SqlCommand )

属性：

CommandText获取或设置要执行的SQL语句或存储过程

CommandTimeout设置超时时间

CommandType命令类型(Text/StoredProcedure/TableDirect)

Connection一般默认为Null，为与数据源连接。

Parameters  SQL语句或存储过程的参数，就是在sql语句中使用一个占位符，然后将值存到ParameterCollection集合中，或者执行存储过程时要传递给存储过程的参数和返回结果要接收的参数。

方法：

ExecuteNonQuery()执行数据库的增、删、改，返回受影响行数。

ExecuteScalar()执行查询，返回首行首列。

ExecuteReader()执行查询，返回DataReader对象。

实例化方式：

OleDbCommandcmd = new OleDbCommand(sql.ToString(), conn);

OleDbCommandcmd = conn.CreateCommadn();

cmd.CommandText= sql.ToString();

c)        DataReader只读、只进的结果集，一条条度数据，连接一直保持着(OleDbDataReader)

d)       DataSet内存中的数据集(不分OleDb和Sql)

三种使用DataSet的方式：

?  通过DataAdapter桥梁用数据源中的数据表数据填充

?  以编程方式在DataSet 中创建 DataTable、DataRelation 和 Constraint，并使用数据填充表。

?  使用 XML 加载和保持 DataSet内容。

实例化DataSet：

可以通过调用 DataSet 构造函数来创建 DataSet 的实例。可以选择指定一个名称参数。如果没有为 DataSet 指定名称，则该名称会设置为“NewDataSet”。

DataSetcustomerOrders = new DataSet("CustomerOrders");

e)        DataAdapter数据适配器(OleDbDataAdapter, SqlDataAdapter)

DataAdapter填充DataSet：

SqlDataAdaptercustAdapter = new SqlDataAdapter(

  "SELECT * FROM dbo.Customers",customerConnection);

OleDbDataAdapterordAdapter = new OleDbDataAdapter(

  "SELECT * FROM Orders",orderConnection);

DataSetcustomerOrders = new DataSet();

custAdapter.Fill(customerOrders,"Customers");

ordAdapter.Fill(customerOrders,"Orders");

DataRelationrelation = customerOrders.Relations.Add("CustOrders",

 customerOrders.Tables["Customers"].Columns["CustomerID"],

 customerOrders.Tables["Orders"].Columns["CustomerID"]);

f)         DataTable 

DataTable填充DataSet

DataSetcustomerOrders = new DataSet("CustomerOrders");

DataTableordersTable = customerOrders.Tables.Add("Orders");

DataColumnpkOrderID = ordersTable.Columns.Add("OrderID", typeof(Int32));

ordersTable.Columns.Add("OrderQuantity",typeof(Int32));

ordersTable.Columns.Add("CompanyName",typeof(string));

ordersTable.PrimaryKey= new DataColumn[] { pkOrderID };

注：将一个新的 DataTable 对象添加到该 DataSet 中，然后将三个 DataColumn 对象添加到该表中。最后，该代码将一个列设置为主键列。

g)        DataView数据视图，可以对数据表中的数据进行查找和筛选，可用于将数据传递给控件。

3.        访问数据库方式

a)        DataReader方式

该方式下执行SQL语句Command命令，执行完后将结果一条条地返回。

b)        DataAdapter + DataSet方式

该方式是通过DataAdapter这个桥梁，将数据库中返回的所有数据填充到DataSet中。

4.        连接池

虽然连接耗时很短，但确实需要时间，如果交互一多显然会影响性能。连接池可以很好滴解决。

连接池保证已经打开的数据库连接，这些连接在使用相同数据源的会话间共享，这样就省了不断创建和销毁连接的时间。当客户用 Open（）方法请求打开连接时，连接直接由连接池提供而不是再次创建。当客户调用 Close（）方法或 Dispose（）方法释放连接时，它并没有被真的释放而是重新回到池中等待下一次请求。连接池就是这样一个容器：它存放了一定数量的与数据库服务器的物理连接。。

连接池是具有类别区分的。也就是说，同一个时刻同一应用程序域可以有多个不同类型的连接池。细致的讲，是由进程、应用程序域、连接字符串以及windows标识（在使用集成的安全性时）共同组成签名来标识区分的。但对于同一应用程序域来说，一般只由连接字符串来标识区分，也就是说，连接字符串不同，不能使用同一个连接池，一个程序中可能有多个连接池，一个连接池中可以有多个连接。

连接池的行为可以通过连接字符串来控制，主要包括四个重要的属性：

?  Connection Timeout：连接请求等待超时时间。默认为15秒，单位为秒。

?  Max Pool Size: 连接池中最大连接数。默认为100。

?  Min Pool Size: 连接池中最小连接数。默认为0。

?  Pooling: 是否启用连接池。ADO.NET默认是启用连接池的，因此，你需要手动设置Pooling=false来禁用连接池。     

SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder();

           connStr.DataSource = @".\SQLEXPRESS";

           connStr.InitialCatalog = "master";

           connStr.IntegratedSecurity = true;

           connStr.Pooling = true; //开启连接池

           connStr.MinPoolSize = 0; //设置最小连接数为0

           connStr.MaxPoolSize = 50; //设置最大连接数为50            

           connStr.ConnectTimeout = 10; //设置超时时间为10秒

           using( SqlConnection conn = new SqlConnection(connStr.ConnectionString)){...}

高效使用连接池的基本原则

?  在最晚的时刻申请连接，在最早的时候释放连接。

?  关闭连接时先关闭相关用户定义的事务。

?  确保并维持连接池中至少有一个打开的连接。

?  尽力避免池碎片的产生。主要包括集成安全性产生的池碎片以及使用许多数据库产生的池碎片。

提示：池碎片是许多 Web 应用程序中的一个常见问题，应用程序可能会创建大量在进程退出后才会释放的池。 这样，将打开大量的连接，占用许多内存，从而导致性能降低。

异常情况

ADO.NET需要手动的关闭使用完的连接。一个重要的误区是：当连接对象超出局部作用域范围时，就会关闭连接。实际上，当超出作用域时，释放的只是连接对象而非连接资源。所以这个时候连接池的连接还是打开状态。

5.        防止SQL注入攻击

预防SQL注入攻击方式很多，输入字符串长度限制、控件数据验证、检测特殊字符。ADO.net中比较好的方式就是使用参数化的命令或者使用存储过程。

参数化命令：参数化命令是在 SQL文本中使用占位符的命令，占位符表示需要动态替换的值，它们通过 Command 对象的 Parameters 集合来传送。

SELECT * FROM Customerswhere CustomerID = @CustID

cmd.Parameters.Add("@CustID", OleDbType.UnsignedBigInt).Value = link.ClassId;

或cmd.Parameters.AddWithValue("@CustID"， link.ClassId);

存储过程：安全、易于维护、提升性能。

    string connStr =WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

    SqlConnection conn = newSqlConnection(connStr);


    // 调用存储过程，必须指定Command.CommandType

    SqlCommand cmd = newSqlCommand("InsertEmployee", conn);

    cmd.CommandType =System.Data.CommandType.StoredProcedure;


    // 向存储过程传递参数

    // 需要精确指定【数据类型】和【参数的大小】以便和数据库中的细节相匹配

    // 使用参数的 Value 属性进行赋值

    cmd.Parameters.Add(newSqlParameter("@TitleOfCourtesy",SqlDbType.NVarChar,25));

    cmd.Parameters["@TitleOfCourtesy"].Value= Title;

    cmd.Parameters.Add(newSqlParameter("@LastName", SqlDbType.NVarChar, 20));

    cmd.Parameters["@LastName"].Value= LastName;

    cmd.Parameters.Add(newSqlParameter("@FirstName", SqlDbType.NVarChar, 10));

   cmd.Parameters["@FirstName"].Value = FirstName;

    // 【输出参数】也使用相同的方式添加

    // 但是必须指定它的 Direction 属性为 OutPut

    cmd.Parameters.Add(newSqlParameter("@EmployeeID", SqlDbType.Int, 4));

   cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;

    // 执行数据库命令

    using (conn)

    {
        conn.Open();
        int rtv = cmd.ExecuteNonQuery();

        // 获取存储过程的输出参数

        int empID =(int)cmd.Parameters["@EmployeeID"].Value;

    }

6.        小结

对于应用程序与数据库之间的映射有很多解决方案，常用的有ADO.net和ORM。ADO.net在执行效率上比较高，但必须自己写里面的每一个细节。ORM(对象关系映射Object Relation Mapping)常用的就是NHibernate，免去了里面的细节，对于简单操作也很方便，但是效率较低。

虽然ADO.net中类方法很多很全，不能也不需要一一列举，只要知道主要的几个类和一些特点和类之间的区别，然后查MSDN即可。开始我也是准备很详细地来了解这个东西，但是发现真正可以写的不多，很多也是看别人博客，把写的好的记录了下来。想针对这个写一个类库，仔细一想也没有必要。所以，等以后有新的重点我再慢慢加入。

由于我写的这些都是先在word里面整理然后，copy到博客里面的，格式细节就将就下吧。