# ADO.NET

## 一、连接数据库：Connection对象

> Connection对象是一个连接对象，主要功能是建立与物理数据库的连接。

以SQL Server为例，如果要连接SQL Server数据库，则必须使用`System.Data.SqlClient`命名空间下的`SqlConnection`类。

### 1、创建SqlConnection对象

```csharp
string connStr = "server=10.201.6.50;database=LTTCERPMAIN;uid=xxx;pwd=xxx";
SqlConnection conn = new SqlConnection(connStr);
```

### 2、打开连接：Open方法

```csharp
conn.Open(); //打开连接
```

### 3、连接状态：State属性

```csharp
public override ConnectionState State { get; }
```

**枚举类型：ConnectionState，其值包含Connecting、Open及Closed等。**

```csharp
//判断当前连接状态
if (conn.State == ConnectionState.Open)
{
    MessageBox.Show("连接数据库成功");
}
```

### 4、关闭连接：Close方法或Dispose方法

```csharp
conn.Close(); 
//or 
conn.Dispose(); //关闭连接
```

> **二者区别**：
>
> 1. Close方法用于关闭一个连接；而Dispose方法不仅关闭一个连接，而且还清理连接所占用的资源。
> 2. 当使用Close方法关闭连接后，可以再调用Open方法打开连接，不会产生任何错误；而使用Dispose方法关闭连接，就不可以再次直接用Open方法打开连接，必须重新初始化连接再打开。

**谨记**：编写应用程序时，对数据库操作完成后，要及时关闭数据库的连接，以防止在对数据库进行其他操作时，数据库被占用。

## 二、执行SQL语句：Command对象

> Command对象是一个数据命令对象，主要功能是向数据库发送查询、更新、删除、修改操作的SQL语句。

Command对象有3个重要的属性，分别是Connection属性、CommandText属性和CommandType属性。

### 1、配置SqlCommond相关属性

- #### Connection属性


**用于设置SqlCommand使用的SqlConnection**

```csharp
SqlCommand cmd = new SqlCommand(); //创建一个SqlCommand对象
cmd.Connection = conn; //设置Connection属性
```

- #### CommandText属性


**用于设置要对数据源执行的SQL语句或存储过程等，默认为执行SQL语句。**

```csharp
SqlCommand cmd = new SqlCommand(); //创建一个SqlCommand对象
cmd.CommandText = "select * from userTest where age > 10"; //设置CommandText属性，要执行的SQL语句
```

- #### CommandType属性


**用于设置指定CommandText的类型。枚举类型：CommandType，其值包括Text、StoredProcedure及TableDirect，默认为Text。**

```csharp
SqlCommand cmd = new SqlCommand(); //创建一个SqlCommand对象
cmd.CommandType = CommandType.Text; //SQL文本命令：Text，使其只执行SQL语句文本形式
cmd.CommandType = CommandType.StoredProcedure; //存储过程的名称：StoredProcedure，使其执行存储过程
cmd.CommandType = CommandType.TableDirect; //表的名称：TableDirect，使其处理某个表
```

> 根据`CommandType`属性的取值来决定`CommandText`属性的取值，分为3种情况：
>
> 1. 如果`CommandType`属性取值为`Text`，则`CommandText`属性指出SQL语句的内容。
> 2. 如果`CommandType`属性取值为`StoredProcedure`，则`CommandText`属性指出存储过程的名称。
> 3. 如果`CommandType`属性取值为`TableDirect`，则`CommandText`属性指出表的名称。

### 2、执行SQL语句

- #### ExecuteNonQuery方法

**使用SqlCommand向数据库发送增、删、改命令时，通常使用ExecuteNonQuery方法执行发送的SQL语句，返回受影响的行数。**

```csharp
SqlCommand cmd = new SqlCommand(); //创建一个SqlCommand对象
cmd.Connection = conn; //设置Connection属性
cmd.CommandText = "update userTest set address='测试' where id=1"; //设置CommandText属性，要执行的SQL语句
cmd.CommandType = CommandType.Text; //设置CommandType属性为Text，使其只执行SQL语句文本形式
int count = cmd.ExecuteNonQuery(); //执行SQL语句
MessageBox.Show(count.ToString()); //返回结果为1
```

- #### ExecuteReader方法

**执行查询操作，返回一个具有多行多列的结果集，即生成一个包含数据的SqlDataReader对象的实例。**

```csharp
SqlCommand cmd = new SqlCommand(); //创建一个SqlCommand对象
cmd.Connection = conn; //设置Connection属性
cmd.CommandText = "select * from userTest"; //设置CommandText属性，要执行的SQL语句
cmd.CommandType = CommandType.Text; //设置CommandType属性为Text，使其只执行SQL语句文本形式
SqlDataReader sdr = cmd.ExecuteReader(); //使用ExecuteReader方法实例化一个SqlDataReader对象
 while (sdr.Read()) //循环读取SqlDataReader，输出用户名称
{
    MessageBox.Show(sdr["name"].ToString());
}
```

- #### ExecuteScalar方法

**执行查询操作，返回单个值，即返回结果集中第一行的第一列。，通常与聚合函数一起使用。**

```csharp
SqlCommand cmd = new SqlCommand(); //创建一个SqlCommand对象
cmd.Connection = conn; //设置Connection属性
cmd.CommandText = "select name from userTest"; //设置CommandText属性，要执行的SQL语句
cmd.CommandType = CommandType.Text; //设置CommandType属性为Text，使其只执行SQL语句文本形式
object obj = cmd.ExecuteScalar(); //执行SQL语句，返回第一行第一列值
MessageBox.Show(obj + ""); //输出结果：杭三
```

## 三、读取数据：DataReader对象

> DataReader对象是数据读取器对象，提供只读向前的游标，如果应用程序需要每次从数据库中取出最新数据，或者只需要快速读取数据，并不需要修改数据，则可以使用DataReader对象进行读取。

### 1、读取数据

**在使用DataReader对象读取数据时，可以使用ExecuteReader方法，根据SQL语句的结果创建一个SqlDataReader对象，然后再调用SqlDataReader对象的Read方法读取数据。**

```csharp
string connStr = "server=10.201.6.50;database=LTTCERPMAIN;uid=xxx;pwd=xxx";
SqlConnection conn = new SqlConnection(connStr); ////创建一个SqlConnection对象，连接数据库
SqlCommand cmd = new SqlCommand(); //创建一个SqlCommand对象
cmd.Connection = conn; //设置Connection属性
cmd.CommandText = "select * from userTest"; //设置CommandText属性，要执行的SQL语句
cmd.CommandType = CommandType.Text; //设置CommandType属性为Text，使其只执行SQL语句文本形式
SqlDataReader sdr = cmd.ExecuteReader(); //使用ExecuteReader方法实例化一个SqlDataReader对象
 while (sdr.Read()) //循环读取SqlDataReader，输出用户名称
{
    MessageBox.Show(sdr["name"].ToString());
}
```

**注意**：在使用SqlDataReader对象之前，必须打开数据库连接。如果针对一个SqlConnection，创建多个SqlDataReader对象，则创建下一个SqlDataReader对象之前，要通过Close方法关闭上一个SqlDataReader对象。

### 2、判断查询结果中是否有值：HasRows属性

**通过SqlDataReader对象的HasRows属性获取一个值，用于指示SqlDataReader是否包含一行或多行，即判断查询结果中是否有值，如果有值，HasRows为true；否则，HasRows为false。**

```csharp
SqlCommand cmd = new SqlCommand(); //创建一个SqlCommand对象
cmd.Connection = conn; //设置Connection属性
cmd.CommandText = "select * from userTest"; //设置CommandText属性，要执行的SQL语句
cmd.CommandType = CommandType.Text; //设置CommandType属性为Text，使其只执行SQL语句文本形式
SqlDataReader sdr = cmd.ExecuteReader(); //使用ExecuteReader方法实例化一个SqlDataReader对象
if (sdr.HasRows)
{
    MessageBox.Show("查询结果中有值！");
}
```

## 四、数据库适配器：DataAdapter对象

> DataAdapter对象是一个数据库适配器对象，是DataSet与数据源之前的桥梁。其包含4个属性：
>
> - SelectCommand属性：向数据库发送查询SQL语句。
> - InsertCommand属性：向数据库发送插入SQL语句。
> - UpdateCommand属性：向数据库发送更新SQL语句。
> - DeleteCommand属性：向数据库发送删除SQL语句。

### 1、填充DataSet数据集：Fill方法

```csharp
//dataSet:要用记录和架构填充的DataSet; srcTable:用于表映射的源表名称
public int Fill(DataSet dataSet, string srcTable)
```

返回值：已在DataSet中成功添加或刷新的行数，这不包括受不返回行的语句影响的行数。

使用方法如下：

```csharp
private void button2_Click(object sender, EventArgs e)
{
    //创建SqlConnection对象，连接数据库
    string connStr = "server=10.201.6.50;database=LTTCERPMAIN;uid=xxx;pwd=xxx";
    SqlConnection conn = new SqlConnection(connStr);

    //创建SqlCommand对象，执行SQL语句，查询测试用户数据
    SqlCommand cmd = new SqlCommand("select * from userTest", conn);

    //创建SqlDataAdapter对象，并设置SelectCommand属性，向数据库发送查询SQL语句
    SqlDataAdapter sda = new SqlDataAdapter();
    sda.SelectCommand = cmd;

    //通过Fill方法将查询结果填充到DataSet中
    DataSet ds = new DataSet();
    sda.Fill(ds, "table_01");
    dataGridView1.DataSource = ds.Tables[0];
}
```

### 2、更新数据源：Update方法

**Update方法更新数据库时，DataAdapter将调用DeleteCommand、InsertCommand以及UpdateCommand属性。**

```c#
//dataTable:用于更新数据源的DataTable
public override int Update(DataSet dataSet)
```

返回值：DataSet中更新成功的行数。

**在调用Update方法之前，要实例化一个CommandBuilder类，它能自动根据DataAdapter的SelectCommand的SQL语句判断其他的InsertCommand、UpdateCommand和DeleteCommand，因此，直接使用Update方法即可更新数据。**

使用方法如下：

```csharp
private void button3_Click(object sender, EventArgs e)
{
    //获取测试用户表数据
    DataTable dt = dataGridView1.DataSource as DataTable;

    //添加一行数据
    DataRow drNew = dt.NewRow();
    drNew["name"] = "王五";
    drNew["sex"] = "男";
    drNew["age"] = 25;
    drNew["address"] = "王府井xxx";
    dt.Rows.Add(drNew);

    //创建SqlConnection对象，连接数据库
    string connStr = "server=10.201.6.50;database=LTTCERPMAIN;uid=xxx;pwd=xxx";
    SqlConnection conn = new SqlConnection(connStr);

    SqlTransaction tran = null;
    try
    {
                //创建SqlDataAdapter对象，并设置SelectCommand属性，向数据库发送查询SQL语句
                SqlDataAdapter sda = new SqlDataAdapter("select * from userTest", conn);

                //实例化一个SqlCommandBuilder对象
                SqlCommandBuilder cmdbuilder = new SqlCommandBuilder(sda);

                //打开数据库连接，更新数据源，插入一行数据
                conn.Open();
                tran = conn.BeginTransaction();
                sda.SelectCommand.CommandTimeout = 600;
                sda.SelectCommand.Transaction = tran;
                sda.Update(dt);
         tran.Commit();
    }
    catch (Exception ex)
    {
         tran?.Rollback();
         MessageBox.Show(ex.Message);
    }
    finally
    {
         //关闭数据库连接
         conn.Close();
    }
}
```