ADO.NET技术学习（一）

  当下的ADO.NET已经和原来基于COM的数据访问模型没有什么关系了，而且ADO已经脱离了原来的ADO的概念，此外前者还增加了许多传统ADO中找不到的对应类型。

  传统的ADO主要是针对于C/S架构的系统，而ADO.net则考虑到了断开式应用，并引进了DataSet数据模型。并且其全面支持XML语言，能够突破防火墙的限制。

    ADO.NET包括一些核心对象，Connection，Command，DataAdapter，DataReader，Parameter，Transaction等，DataAdapter是在数据库和和调用者之间传递DataSet的，而DataReader仅仅是向前的i形式传递只读数据。

                                              

微软提供了Oracle\SQL SERVER\OLEDB\ODBC系列等众多的数据库提供程序，其都在System.Data的命名空间下，不过需要注意的是，没有直接映射到Jet的引擎，若和ACESS数据库交换，则使用ODBC和OLEDB数据提供程序，而OLEDB实际上是在后台调用各种的COM来实现数据交互，这可能会影像程序的性能，基本上当没有对应的.NET 的数据提供程序时，我们才会用OLEDB来进行数据库数据的交换，但是在.NET4的世界中应用的很少。

（如果是和微软的SQL SERVER6.5或更低的版本交互的话则必须使用oledb）。sqlClient只可以和7.0以上的版本进行交互。7.0后绕开了oledb其带来的效率有了较大的提升。

    关于System.Data.Oracle.Client.dll

在较早的.net版本中包含这个程序集，而在.NET4开始，已经被弃用，是不是觉得像是商业战争之间的禁止？不是这样的，起始Oracle自己提供了一个自定义的.NET程序集，可以通过在Oracle的网站获取。

     除此之外其还对各种开源的和商业的数据库提供第三方数据库提供程序，如SQLite，MySQL，PostgreSQL，Sybase等。

       ADO.NET的数据提供程序工厂模型

     .net数据提供程序工厂模型能让我们用多种的数据访问类型构建代码库，而适当的使用配置文件就可以更改和获取提供程序和连接字符。

     结合一个简单的例子来完成对ADO.连接层的一些技术的学习：

首先建立一个数据库，数据库中包含了Inventory，Oders，和Coustoms表，还包含了几个存储过程，数据库的建立就不详细介绍了，后面会附上数据如文件：

第一个，使用ADO.NET的数据提供工厂模型：

  当我们引入System.Configuration命名空间后添加App配置文件

<configuration>
  <appSettings>
    <!--数据提供程序 -->
    <!-- <add key="provider" value="System.Data.OleDb" />-->
    <add key="provider" value="System.Data.SqlClient" />
  </appSettings>

  <!-- 定义数据库的链接字符 -->
  <connectionStrings>
    <add name ="AutoLotSqlProvider"  connectionString =
  "Data Source=(local)\SQLEXPRESS;
   Integrated Security=SSPI;Initial Catalog=AutoLot"/>

    <add name ="AutoLotOleDbProvider"  connectionString =
  "Provider=SQLOLEDB;Data Source=(local)\SQLEXPRESS;
  Integrated Security=SSPI;Initial Catalog=AutoLot"/>
    
  </connectionStrings>
</configuration>

测试文件如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace DataProviderFactory
{
    class Program
    {
        static void Main(string[] args)
        {
            // 通过 ConfigurationManager.AppSettings来获取索引器读取provider和cnStr的值
            Console.WriteLine("***** Fun with Data Provider Factories *****\n");
            string dp =
              ConfigurationManager.AppSettings["provider"];
            string cnStr =
              ConfigurationManager.ConnectionStrings["AutoLotSqlProvider"].ConnectionString;

            // 我们可以通过静态的方法GetFactory()来获取DbProviderFactories中的数据提供类别，进而可以获得
            //这个类别的相关的数据对象
            DbProviderFactory df = DbProviderFactories.GetFactory(dp);

            #region Use the factory!
            // 构造连接对象
            using (DbConnection cn = df.CreateConnection())
            {
                Console.WriteLine("Your connection object is a: {0}", cn.GetType().Name);
                cn.ConnectionString = cnStr;
                cn.Open();
                if (cn is SqlConnection)
                {
                   
                    Console.WriteLine(((SqlConnection)cn).ServerVersion);
                }

                // 得到命令对象
                DbCommand cmd = df.CreateCommand();
                Console.WriteLine("Your command object is a: {0}", cmd.GetType().Name);
                cmd.Connection = cn;
                cmd.CommandText = "Select * From Inventory";

                // Print out data with data reader.              
                using (DbDataReader dr = cmd.ExecuteReader())
                {
                    Console.WriteLine("Your data reader object is a: {0}", dr.GetType().Name);

                    Console.WriteLine("\n***** Current Inventory *****");
                    while (dr.Read())
                        Console.WriteLine("-> Car #{0} is a {1}.",
                          dr["CardID"], dr["Make"].ToString());
                }
            }
            #endregion

            Console.ReadLine();
        }
    }
}

     
  这种较为通用的方式使得我们不能较为直接的访问特定的DBMS的漂亮功能，然而配置文件中的<appSettings>元素中的<connectionString>可以较好的解决这个问题，

如上面的访问SQL的版本的方法中，使用的cn is SQL·····来进行判断，较好的解决了这一问题。

   连接层的第一步就是使用连接对象，是要考一个符合一定格式的连接字符串进行创建，着洗信息用来确定想要的连接的计算机名和必要的安全配置，机器数据库的名字和其他一些数据提供程序特有的信息。

    使用ConnectionStringBuilder对象

实际上若我们通过编程的方式，来连接字符串是非常有用的，因为用一个文本字符串的话不容易维护，而且呢容易出问题。微软的ADO.NET数据提供程序提供了连接字符串构造函数对象，他能使用强类型化的属性来制定名称/值对。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace AutoLotDataReader
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("***** Fun with Data Readers *****\n");

            //通过构造函数来构建字符串
            SqlConnectionStringBuilder cnStrBuilder =
              new SqlConnectionStringBuilder();
            cnStrBuilder.InitialCatalog = "AutoLot";
            cnStrBuilder.DataSource = @"(local)\SQLEXPRESS";//服务器设置
            cnStrBuilder.ConnectTimeout = 30;
            cnStrBuilder.IntegratedSecurity = true;

            // 打开一个数据库连接
            using (SqlConnection cn = new SqlConnection())
            {
                cn.ConnectionString = cnStrBuilder.ConnectionString;
                cn.Open();
                ShowConnectionStatus(cn);

                // 创建一个查询语句
                string strSQL = "Select * From Inventory;Select * from Customers";

                using (SqlCommand myCommand = new SqlCommand(strSQL, cn))
                {
                    #region iterate over the inventory & customers
                    // 获取DataReader对象
                    using (SqlDataReader myDataReader = myCommand.ExecuteReader())
                    {
                        do
                        {
                            while (myDataReader.Read())
                            {
                                Console.WriteLine("***** Record *****");
                                for (int i = 0; i < myDataReader.FieldCount; i++)
                                {
                                    Console.WriteLine("{0} = {1}",
                                      myDataReader.GetName(i),
                                      myDataReader.GetValue(i).ToString());
                                }
                                Console.WriteLine();
                            }
                        } while (myDataReader.NextResult());
                    }
                    #endregion
                }
            }
            Console.ReadLine();
        }

        #region Helper function
        static void ShowConnectionStatus(SqlConnection cn)
        {
            // 显示数据库连接信息
            Console.WriteLine("***** Info about your connection *****");
            Console.WriteLine("Database location: {0}", cn.DataSource);
            Console.WriteLine("Database name: {0}", cn.Database);
            Console.WriteLine("Timeout: {0}", cn.ConnectionTimeout);
            Console.WriteLine("Connection state: {0}\n", cn.State.ToString());
        }
        #endregion
    }
}

  使用命令对象：

      我们可以使用Sqlcommand类型的CommandType属性来指定命令类型，他的值有CommandType枚举定义，当然，我们需要输入指令时，可以使用CommandText来为其Sqlcommand对象设置属性，当使用存储过程时，则应该使用CommandProcedure，默认情况下，命令为CommandText。

     使用数据读取器

     当我们需要读取大量的数据，仅仅用于读取显示数据时我们可以使用DataReader不需要缓存到本地的内存，减少内存的占有率，通过执行ExecuteReader方法获取数据读取器的对象，数据读取器表示从数据库读取的当前记录，包含一个索引器方法。如上例。注意：这个方法近能够执行查询，并不能执行更行，删除，修改等的命令，其可以使用ExecuteNonQuery（）方法，起返回的是受影响的行数。

      构建可以重用的数据库访问库：

在实际应用中我们可以把数据库的访问逻辑单独的隔离到一个.NET的.dll程序集中，这样可以实现代码重用，可以避免编写相同的逻辑，相同的链接逻辑，及命令和读取逻辑等，减少时间的浪费。同时这样也可以满足其他的.NET程序员来使用。

测试实例如下：

    

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace AutoLotConnectedLayer
{

    //定义的强类型的数据类 NewCar类
    public class NewCar
    {
        public int CarID { get; set; }
        public string Color { get; set; }
        public string Make { get; set; }
        public string PetName { get; set; }
    }

    public class InventoryDAL
    {
        //定义一个全局的SQL链接类
        private SqlConnection sqlCn = null; 

        #region 打开和关闭方法
        public void OpenConnection(string connectionString)
        {
            sqlCn = new SqlConnection();
            sqlCn.ConnectionString = connectionString;
            sqlCn.Open();
        }

        public void CloseConnection()
        {
            sqlCn.Close();
        }
        #endregion

        #region 插入方法，无参数化的查询方式，通过上面的强类型方式
        public void InsertAuto(NewCar car)
        {
            //按照格式生成SQL语句
            string sql = string.Format("Insert Into Inventory" +
              "(CarID, Make, Color, PetName) Values" +
              "('{0}', '{1}', '{2}', '{3}')", car.CarID, car.Make, car.Color, car.PetName);

            // 对数据库进行操作
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }
        //public void InsertAuto(int id, string color, string make, string petName)
        //{
        //    // Format and execute SQL statement.
        //    string sql = string.Format("Insert Into Inventory" +
        //      "(CarID, Make, Color, PetName) Values" +
        //      "('{0}', '{1}', '{2}', '{3}')", id, make, color, petName);

        //    using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
        //    {
        //        cmd.ExecuteNonQuery();
        //    }
        //}
        #endregion

        #region 插入方法，直接使用SQL数据库中的参数实现过程
        public void InsertAuto(int id, string color, string make, string petName)
        {
            string sql = string.Format("Insert Into Inventory" +
                "(CarID, Make, Color, PetName) Values" +
                "(@CarID, @Make, @Color, @PetName)");

            // 这个过程实现参数化查询
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                // Fill params collection.
                SqlParameter param = new SqlParameter();//构建SqlParameter的实例化对象param
                param.ParameterName = "@CarID";//参数名
                param.Value = id;//对应数据库中的字段
                param.SqlDbType = SqlDbType.Int;//数据类型
                cmd.Parameters.Add(param);//向Parameters中增加参数

                param = new SqlParameter();
                param.ParameterName = "@Make";
                param.Value = make;
                param.SqlDbType = SqlDbType.Char;
                param.Size = 10;
                cmd.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@Color";
                param.Value = color;
                param.SqlDbType = SqlDbType.Char;
                param.Size = 10;
                cmd.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@PetName";
                param.Value = petName;
                param.SqlDbType = SqlDbType.Char;
                param.Size = 10;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
            }
        }
        #endregion

        #region 删除操作方法
        public void DeleteCar(int id)
        {
            // 找到Car的ID，然后删除
            string sql = string.Format("Delete from Inventory where CarID = '{0}'",
              id);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Exception error = new Exception("Sorry! That car is on order!", ex);
                    throw error;
                }
            }
        }
        #endregion

        #region 修改操作方法
        public void UpdateCarPetName(int id, string newPetName)
        {
            // 修改pet值
            string sql =
              string.Format("Update Inventory Set PetName = '{0}' Where CarID = '{1}'",
              newPetName, id);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }
        #endregion

        #region 选择查询方法
        public DataTable GetAllInventoryAsDataTable()
        {
            DataTable inv = new DataTable();

            string sql = "Select * From Inventory";
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                //使用Reader中的数据对Datatable进行填充
                inv.Load(dr);
                dr.Close();
            }
            return inv;
        }
        public List<NewCar> GetAllInventoryAsList()
        {
            // 储存记录
            List<NewCar> inv = new List<NewCar>();

            string sql = "Select * From Inventory";
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    inv.Add(new NewCar
                    {
                        CarID = (int)dr["CarID"],
                        Color = (string)dr["Color"],
                        Make = (string)dr["Make"],
                        PetName = (string)dr["PetName"]
                    });
                }
                dr.Close();
            }
            return inv;
        }
        #endregion

        #region 查找pet值
        public string LookUpPetName(int carID)
        {
            string carPetName = string.Empty;

            using (SqlCommand cmd = new SqlCommand("GetPetName", this.sqlCn))
            {
                //设置为存储类型
                cmd.CommandType = CommandType.StoredProcedure;

                // 输入参数
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@carID";
                param.SqlDbType = SqlDbType.Int;
                param.Value = carID;
                param.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(param);

                // 输出参数
                param = new SqlParameter();
                param.ParameterName = "@petName";
                param.SqlDbType = SqlDbType.Char;
                param.Size = 10;
                param.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(param);

                // 处理存储过程
                cmd.ExecuteNonQuery();

                // 返回输出参数
                carPetName = (string)cmd.Parameters["@petName"].Value;
            }
            return carPetName;
        }

        #endregion

        #region Tx method
        public void ProcessCreditRisk(bool throwEx, int custID)
        {
            // 首先查询当前消费者ID的名字
            string fName = string.Empty;
            string lName = string.Empty;
            SqlCommand cmdSelect = new SqlCommand(
              string.Format("Select * from Customers where CustID = {0}", custID), sqlCn);
            using (SqlDataReader dr = cmdSelect.ExecuteReader())
            {
                if (dr.HasRows)
                {
                    dr.Read();
                    fName = (string)dr["FirstName"];
                    lName = (string)dr["LastName"];
                }
                else
                    return;
            }
            //同时执行，涉及到存储过程的特点
            SqlCommand cmdRemove = new SqlCommand(
              string.Format("Delete from Customers where CustID = {0}", custID), sqlCn);

            SqlCommand cmdInsert = new SqlCommand(string.Format("Insert Into CreditRisks" +
                             "(CustID, FirstName, LastName) Values" +
                             "({0}, '{1}', '{2}')", custID, fName, lName), sqlCn);

            SqlTransaction tx = null;
            try
            {
                tx = sqlCn.BeginTransaction();

                cmdInsert.Transaction = tx;
                cmdRemove.Transaction = tx;

                cmdInsert.ExecuteNonQuery();
                cmdRemove.ExecuteNonQuery();

                if (throwEx)
                {
                    throw new Exception("Sorry!  Database error! Tx failed...");
                }

                tx.Commit();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                // 若出现任何错误，则进行事务回滚
                tx.Rollback();
            }
        }
        #endregion
    }

}