ADO.NET

1、基本概念
1.1 什么是ADO.NET
ADO.NET是一组允许.NET开发人员使用标准的、结构化的、甚至无连接的方式与数据交互的技术。ADO.NET可以处理多种类型的数据源。

1.2 ADO.NET的核心

ADO.NET在System.Data命名空间下。该命名控件下包含两组重要的类：

1.2.1 负责处理实际数据（DataSet）

DataSet是ADO.NET非连接结构的核心组件。DataSet支持独立于任何数据源的数据访问，因此，ADO.NET可以用于多种不同的数据源。DataSet包含一个DataTable对象集合，这些对象由数据行、数据列以及主键、外键、约束和有关DataTable对象中的关系信息组成。

1.2.2 负责与外部数据系统通信（Data Provider），具体的类包括：

• Connection——提供与数据源的连接
• Command——访问返回数据、修改数据、运行存储过程等数据命令
• DataReader——从数据源中提供快速的、只读的数据流
• DataAdapter——提供连接DataSet对象和数据源的桥梁

Command使用Connection对象连接的数据源，就可以向数据源发送命令。DataAdapter使用Command对象在数据源中执行命名，以便将数据加载到DataSet中，再使用DataSet中数据的更改与数据源保持一致。

 

2、连接字符串

ADO.NET类库通过不同的数据源编写了不同的数据提供程序，因此ADO.NET支持处理多种类型数据源的能力。而且ADO.NET为不同的数据源提供了一致的访问，而如果要访问正确的数据源就必须使用连接字符串。

连接字符串，就一组被格式化的键值对，连接字符串包含了连接数据源的相关信息。不同数据源的连接字符串也不同

2.1 SQL Server连接字符串

2.1.1 标准连接

Data Source=LocalHost;Initial Catalog=myDataBase;User Id=admin;Password=admin;

注意：如果数据源是SQL Server是Express版本，则在连接时需要在服务器名后加SQLEXPRESS。例如：Data Source=LocalHostSQLEXPRESS

2.2 MySQL连接字符串

Data Source=LocalHost;Database=myDataBase;User Id=admin;Password=admin;Port=3308;

2.3 Oracle连接字符串

Data Surce=orcl;user=admin;Password=admin;

2.4 ConnectionStringBuilder

除了通过字符串方式创建连接字符串外，.NET还提供了ConnectionStringBuilder用于创建和管理连接字符串。

以SQL Server为例，使用ConnectionStringBuilder创建连接字符串：

SqlConnectionStringBuilder stringBuilder = new SqlConnectionStringBuilder();

stringBuilder.DataSource = "LocalHost";

stringBuilder.InitialCatalog = "myDataBase";

stringBuilder.IntegratedSecurity = true;

 

3、ADO.NET核心类

3.1 Connection

Connection主要作用是建立与数据源的连接。所有的Connection都继承自DbConnection类，DbConnection类是抽象类。

注意：连接要及时关闭。当打开一个连接后，连接会一直处于打开状态，直到调用Dispose()方法，垃圾回收机制才会关闭和释放连接。一个重要的误区：当连接对象超出其作用域时，就会关闭连接。实际上，超出作用域只会释放连接对象，而不会关闭连接。

3.1.1 属性

• State——描述了当前连接的数据源的连接状态，ConnectionState枚举类型
• ConnectionString——设置或获取连接字符串
• Database——获取当前连接的数据库名称
• DataSource——获取当前连接的服务器名称

3.1.2 方法

• void Open()——根据ConnectionString打开数据库连接
• void Dispose()——释放由Component使用的资源
• void Close()——关闭与数据库的连接

3.1.3 实例

class Program

{

    static Main(string[] args)

    {

        string connectionString = "Data Source=LocalHost;Initial Catalog=myDataBase;User Id=admin;Password=admin";

        SqlConnection connection = new SqlConnection();

        connection.ConnectionString = connectionString;  //指定连接数据源

        connection.Open()   //打开数据源

        if (connection.State == ConnectionState.Open)

        {

            ... ...

        }

        connection.Close()

    }

}

3.2 连接池

创建一个数据库连接时会消耗一定的时间和资源，所以若程序中经常创建关闭数据库连接将影响程序效率。ADO.NET提供一种优化方法——连接池，连接池是一块内存空间，里面存放着一定数量的数据库服务器物理连接。当需要连接数据库服务器时，只需去连接池中获取一条空闲的连接，而不是创建一个新的连接。

3.2.1 创建连接池

连接池具有类型区分。在同一时刻的同一个程序中可以有多个连接池，连接池通过进程、应用程序域、连接字符串以及windows标识共同组成签名来标识区分。但对同一个应用程序域来说，一般只用连接字符串标识区分。当打开一条连接时，如果该连接的类型签名与现有的连接池匹配，则创建一个新的连接池，反之，则不创建新的连接池。

实例：典型创建连接实例

using(SqlConnection conn1 = new SqlConnction("DataSource=(local);Integrated Security=SSPI;Initial Catalog=Northwind"))

{

    conn1.Open();

}

using(SqlConnection conn2 = new SqlConnction("DataSource=(local);Integrated Security=SSPI;Initial Catalog=pubs"))

{

    conn2.Open();

}

using(SqlConnection conn3 = new SqlConnction("DataSource=(local);Integrated Security=SSPI;Initial Catalog=Northwind"))

{

    conn3.Open();

}

上面的实例中，创建了三个SqlConnection对象，conn1与conn3的连接字符串完全相同，所以可以共享一个连接池，而conn2与conn1和conn3不同，所以需要创建新的连接池。

3.2.2 连接的分配

当调用Connection对象Open()方法时，连接池管理器首先根据连接请求中的标识查找匹配的连接池，然后分配连接。分配情况如下：

• 如果连接池中有空间连接，则返回给连接
• 如果连接池中连接已用完，则创建一个新的连接并添加到连接池中
• 如果连接池中连接已达到最大连接数，则请求进入等待队列，直到有空闲连接

连接池管理器具有检测并移除无效连接的功能，当某个连接长时间处于空闲状态，或检测到与服务器的连接已经断开，则连接池管理会自动将该连接从连接池中移除。

3.2.3 属性

• MaxPoolSize——设置或获取连接池最大连接数
• MinPoolSize——设置或获取连接池最小连接数
• Pooling——是否启动连接池，默认为ture
• ConnectTimeOut——连接请求等待超时时间，单位为秒

3.2.4 实例

class Program

{

    static void Main(string[] args)

    {

        SqlConnectionStringBuilder strBuilder = new SqlConncetionStringBuilder();

        strBuilder.DataSource = "LocalHost";

        strBuilder.InitialCatalog = "MyDatabase";

        strBuilder.IntegratedSecurity = true;

        // 设置连接池相关属性

        strBuilder.Pooling = true;   //开启连接池，默认为true

        strBuilder.MaxPoolSize = 10;    //设置最大连接数

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

        strBuilder.ConnectionTimeOut = 20;    //设置连接等待时间

        using (SqlConnection connection = new SqlConnection(strBuilder.ConnectionString))

        {

             ... ...

        }

    }

}

3.3 Command

 Command可以执行SQL语句，完成对数据库的增、删、改、查等数据操作。所有Command类都继承自DbCommand，并且实现了IDbCommand接口。

3.3.1创建Command

Command可以通过Connection对象的CreateCommand()方法创建。

SqlCommand cmd = con.CreateCommand();

3.3.2 属性

Command最主要的CommandText，该属性用于接收要执行的SQL语句。

cmd = "select * from table";

3.3.3 方法

• ExecuteNonQuery()——提交无查询结果的SQL语句。例如：update、insert、delete等。返回值为数据库中被影响的行数
  
• ExecuteReader()——提交select语句，返回值为DataReader(ADO.NET核心类之一)类型的数据，通过Read()方法逐行读取查询结果
• ExecuteScalar()——提交selec语句，返回查询结果的第一行第一列
  

3.3.4 参数化查询

Parameter定义了命令和存储过程的输入、输出和返回值参数。在设计与数据库连接并存取数据时，通过Parameter传输参数，可有效的防止SQL注入。

使用Parameter的情况下，数据库服务器不会将参数的内容视为SQL指令的一部分，而是在数据库完成SQL指令的编译后，才套用参数执行，因此就算参数中存在破坏性指令，也不会被数据库执行。

实例：使用Parameter实现参数化查询

cmd.CommandText = "select * from users where name = @Name and password = @Password";

SqlParameter para1 = new SqlParameter("@Name", "zhang"),

SqlParameter para2 = new SqlParameter("@Password", "123"),

cmd.Parameters.Add(para1);

cmd.Parameters.Add(para2);

cmd.ExecuteScalar();

注意：要注意参数在数据库中的类型，将参数值转换成与数据库字段相同类型。

3.3.5 实例

执行insert操作

class Program

{

    static void Main(string[] args)

    {

        using (SqlConnection conn = new SqlConnection("Data Source=LocalHost;Initial Catalog=myDataBase;User Id=admin;Password=admin"))

        {

            conn.Open();

            using (SqlCommand cmd = conn.CreateCommand)

            {

                cmd.CommandText = "select * from table";

                cmd.Parameters.Add(new SqlParameter("@Name", "zhang"));

                cmd.Parameters.Add(new SqlParameter("@Password", "123"));

                cmd.ExecuteNonQuery();

            }

            conn.Close();

        }

    }

}

执行select操作

class Program

{

    static void Main(string[] args)

    {

        using (SqlConnection conn = new SqlConnection("Data Source=LocalHost;Initial Catalog=myDataBase;User Id=admin;Password=admin"))

        {

            conn.Open();

            using (SqlCommand cmd = conn.CreateCommand)

            {

                cmd.CommandText = "insert into table(name, password) values(@Name, @Password)";

                using (SqlDataReader reader = cmd.ExecuteReader())

                {

                    while (reader.read())

                    {

                        ... ...

                    }

                }

            }

            conn.Close();

        }

    }

}

3.4 DataAdapter

DataAdapter是一种功能强大的数据适配器。可以用于操作多种数据源的数据。实际上，DataAdapter的操作是通过Command来实现的，在DataAdapter对象中包含着多种Command属性，所以，可以把DataAdapter看作是封装了一些特殊功能增强版的Command。

DataAdapter一般与ADO.NET的另一个核心类DataSet联合使用。DataAdapter支持连接、查询数据源数据，并填充DataSet，以及将DataSet数据更新至数据。

3.4.1 属性

• AcceptChangeDuringFill——确定获取的行的RowState，默认为true
• DeleteCommand——获取或设置一个Transact-SQL语句或存储过程，以从数据集中删除记录
• InsertCommand——获取或设置一个Transact-SQL语句或存储过程，以从数据集中插入记录
• SelectCommand——获取或设置一个Transact-SQL语句或存储过程，从数据集中查询记录
• UpdateCommand——获取或设置一个Transact-SQL语句或存储过程，更新数据源中的数据
• ContinueUpdate——控制遇到错误之后是否继续提交更新，默认为false

3.4.2 方法

• int Fill(DataTable dataTable)——用于将查询结果填充至DataTable或DataSet中
• int Update(DataTable dataTable)——向数据库提交DataTable或DataSet中更改后的数据。返回数据库被影响的行数
  

3.4.3 实例

class Program

{

    static void Main(string[] args)

    {

        using (SqlConnection conn = new SqlConnection("Data Source=LocalHost;Initial Catalog=myDataBase;User Id=admin;Password=admin"))

        {

            conn.Open();

            SqlDataAdapter myAdp = SqlDataAdapter("select * from table", conn);

            DataSet ds =new DataSet();

            myAdp.Fill(ds);

        }

    }

}

3.5 CommandBuilder

DataAdapter对象在数据发生变化的时候，并不能自动生成数据库所需的Transact-SQL语句，所以如果要使用DataAdapter的Update()方法，就必须使用CommandBuilder。CommandBuilder能为单个表的数据改变自动生成Transact-SQL语句。

3.5.1 CommandBuilder的使用

CommandBuilder一般与DataAdapter联合使用。首先创建CommandBuilder对象，并将其与DataAdapte对象进行绑定，然后调用DataAdapter的Update方法。

SqlCommandBuilder MyCb = new SqlCommandBuilder(MyAdapter);

MyAdapter.Update(Dataset, TableName);

注意：CommandBuilder和DataAdapter关联时，就会自动生成DeleteCommand、InsertCommand和UpdateCommand命令。但是必须是一个表，不能是联合查询表。

3.5.2 实例

class Program

{

    static void Main(string[] args)

    {

        using (SqlConnection conn = new SqlConnection("Data Source=LocalHost;Initial Catalog=myDataBase;User Id=admin;Password=admin"))

        {

            conn.Open();

            SqlDataAdapter myAdp = SqlDataAdapter("select * from table", conn);

            DataTable dt =new DataTable();

            myAdp.Fill(dt);

            DataRow row = dt.NewRow();

            row["Field1"] = "admin";

            row["Field2"] = "123";

            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(myAdp);

            myAdp.Update(dt);

        }

    }

}