ADONET数据库操作对象

SqlConnection对象

SqlConnection简介

要建立ADO.NET与数据库的连接，需要使用SqlConnection对象来实现，该对象需要一个数据库连接字符串。

实例化SqlConnection

实例化SqlConnection对象时必须使用数据库连接字符串，才能与SQL Server服务器建立连接。 实例化SqlConnection对象有两种方法：

//new关键字实例化SqlConnection对象
SqlConnection sqlConn = new SqlConnection(connStr);
或
//使用SqlConnection对象的ConnectionString属性指定数据库连接字符串
SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = connStr;

打开连接

实例化SqlConnection对象后，还需使用SqlConnection对象的同步方法Open()或异步方法OpenAsync()打开连接。

使用同步方法Open()打开数据库连接

//读取数据库连接字符串
string connstr = ConfigurationManager.ConnectionStrings["DotNetConDb"].ConnectionString;
//构造函数实例化SqlConnection
SqlConnection sqlConn = new SqlConnection();
//指定数据库连接字符串
sqlConn.ConnectionString = connstr;
//打开数据库连接
sqlConn.Open();

异步方法OpenAsync()打开数据库连接

在最新的ADO.NET中，提供了异步方法，之前操作数据库的方法，都提供了对应的异步版本。

//读取数据库连接字符串
string connstr = ConfigurationManager.ConnectionStrings["DotNetConDb"].ConnectionString;
//构造函数实例化SqlConnection
SqlConnection sqlConn = new SqlConnection();
//指定数据库连接字符串
sqlConn.ConnectionString = connstr;
//异步方法打开数据库连接
await sqlConn.OpenAsync();

Console.ReadKey();

关闭和释放

关闭连接

ADO.NET中的数据库打开连接在执行数据库操作之后，为了下一次的连接更安全，则要关闭连接或释放资源（同时会关闭连接）。数据库的连接是先打开再关闭。 数据库的关闭使用Close()同步方法或异步方法CloseAsync()异步方法。

//读取数据库连接字符串
string connstr = ConfigurationManager.ConnectionStrings["DotNetConDb"].ConnectionString;
//构造函数实例化SqlConnection
SqlConnection sqlConn = new SqlConnection();
//指定数据库连接字符串
sqlConn.ConnectionString = connstr;
//同步方法打开数据库连接
//sqlConn.Open();

//异步方法打开数据库连接
await sqlConn.OpenAsync();

//同步关闭连接
sqlConn.Close();
//异步关闭连接
await sqlConn.CloseAsync();

使用同步方法打开数据库连接，则也要使用同步方法关闭连接。若使用异步方法打开数据库连接，则也要使用异步方法关闭连接。

释放资源

如果使用了Dispose()同步方法或DisposeAsync()异步方法，除了做与Close()方法一样的功能之外，还会清空数据库连接字符串及与数据库相关的资源。

//同步释放资源
sqlConn.Dispose();
//异步释放资源
await sqlConn.CloseAsync();

Dispose()要比Close()释放的资源多，Dispose()方法释放资源之后，是不能再使用Open()打开连接的。

使用using释放资源

使用using语句可以指定一个范围（作用域），实例化的对象只在此范围内活动，一旦离开这个范围，则就会调用该对象的Dispose()方法来释放资源。

using(SqlConnection conn = new SqlConnection(connstr))
{
    //打开数据库连接
    await conn.OpenAsync();
}

using语句内部没有调用Dispose()方法，但是在执行完using语句后，Sqlconnection对象的State处于Closed状态，数据库连接字符串为空，表示执行了Dispose()方法释放了资源。

SqlCommadn对象

数据库连接之后，实例化SqlCommand对象，将T-SQL语句或存储过程挂接到SqlCommand对象上，用于执行数据库操作。

SqlCommand实例化

使用构造函数实例化

构造函数实例化SqlCommand对象，需要至少2个必须参数：

• cmdText: 这是一个string类型的参数，用于指定命令文本，可以是T-sql语句，还可以是存储过程的名称。
• connection: 这是一个SqlConnection类型的数据库连接对象，将指定的SQLServer数据库与要执行的T-sql或存储过程绑定在一起，此时，我们就知道需要在哪个数据库上执行什么操作。

//读取数据库连接字符串
string connstr = ConfigurationManager.ConnectionStrings["DotNetConDb"].ConnectionString;

using(SqlConnection conn = new SqlConnection(connstr))
{
    //打开数据库连接
    await conn.OpenAsync();
    //T-sql语句
    string sql = "select * from Student";
    //实例化SqlCommand对象(传入两个参数，第1个是T-sql语句，第2个参数是SqlConnection对象。
    SqlCommand sqlCommand = new SqlCommand(sql, conn);
}

使用属性实例化

使用空的SqlCommand()构造函数实例化SqlCommand对象，然后使用属性指定命令文本、类型和SqlConnection对象。

//读取数据库连接字符串
string connstr = ConfigurationManager.ConnectionStrings["DotNetConDb"].ConnectionString;
//使用属性实例化SqlCommand
using(SqlConnection conn = new SqlConnection(connstr))
{
    //打开数据库连接
    await conn.OpenAsync();
    //T-sql语句
    string sql = "select * from Student";
    SqlCommand sqlCommand = new SqlCommand();
    sqlCommand.CommandText = sql; //指定SQL语句或存储过程
    sqlCommand.CommandType = System.Data.CommandType.Text; //命令类型是SQL语句
    sqlCommand.Connection = conn; //指定SqlConnection对象
}

CommandType: 指定的命令类型，这是一个枚举类型：

• Text: 指定要执行的是T-sql语句。
• StoredProcedure: 指定要执行的是存储过程。
• TableDirect: 指定一个表名。

CreateCommand()方法

Sqlconnection对象上，有一个CreateCommand()方法，要可以创建SqlCommand对象。

//使用CreateCommand()方法创建SqlCommand()对象
using (SqlConnection conn = new SqlConnection(connstr))
{
    //打开数据库连接
    await conn.OpenAsync();
    //T-sql语句
    string sql = "select * from Student";
    SqlCommand sqlCommand = conn.CreateCommand();
    sqlCommand.CommandText = sql; //指定SQL语句或存储过程
    sqlCommand.CommandType = System.Data.CommandType.Text; //命令类型是SQL语句
    sqlCommand.Connection = conn; //指定SqlConnection对象
}

使用CreateCommand()方法与使用new SqlCommand()生成的SqlCommand()对象是一样的。只不过，CreateCommand()方法生成的SqlCommand对象只能使用属性设置T-Sql/存储过程和SqlConnection对象。

SqlDataReader对象

有了SqlConnection和SqlCommand对象，就可以执行T-Sql语句或存储过程了。我们可以使用SqlDataReader对象以只读数据流的方式获取数据库表中的数据。 SqlDataReader对象也称为数据读取器，只能用来获取数据，且是以数据流的方式获取，是ADO.NET中获取数据速度最快的对象。

工作原理

SqlDataReader是不能实例化的，它是作为一种类型存在的，用于接收SqlCommand对象的ExecuteReader()方法返回的SqlDataReader类型的对象。 SqlDataReader 对象得到的是数据流，不能直接从中拿数据，而是需要配合do...while或while循环语句得到其中的数据。SqlDataReader对象中有个一个Read()方法，该方法返回的是bool类型的值，相当于一个指针，当Read()返回true时，表示指针的位置有数据，可以在循环体中获取，下一次再使用Read()方法时，指针会向下移动，如果存在数据，则返回True,不存在数据，则返回False。

获取数据

同步方法

using(SqlConnection connection = new SqlConnection(connstr))
{
    //打开数据库连接
    connection.Open();
    //T-sql语句
    string sql = "select * from Student";
    SqlCommand sqlCommand = connection.CreateCommand();
    sqlCommand.CommandText = sql; //指定SQL语句或存储过程
    sqlCommand.CommandType = System.Data.CommandType.Text; //命令类型是SQL语句
    sqlCommand.Connection = connection; //指定SqlConnection对象

    //获取数据流
    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
    while(sqlDataReader.Read())
    {
        Console.WriteLine(sqlDataReader["Id"] + "\t" + sqlDataReader["Name"] + "\t" + sqlDataReader["Age"]);
    }
    //关闭数据流
    sqlDataReader.Close();
}   	

在SqlDataReader对象中，得到的数据是以object类型存放的，因此，获取数据后需要转换。要获取其中存放的数据，可使用Read()方法，该方法会以指针的形式在数据流中获取数据，然后再使用行中列的索引值来得到该列的数据。 Read()方法返回的类型是bool,返回值为true表示当前指针位置有记录数据。当Read()为True时，表示可以在循环体内使用所以或其它方法获取行记录中的数据。 SqlDataReader对象使用完成后，必须使用Close()方法关闭数据流。

异步方法

using (SqlConnection conn = new SqlConnection(connstr))
{
    //打开数据库连接
    await conn.OpenAsync();
    //T-sql语句
    string sql = "select * from Student";
    SqlCommand sqlCommand = conn.CreateCommand();
    sqlCommand.CommandText = sql; //指定SQL语句或存储过程
    sqlCommand.CommandType = System.Data.CommandType.Text; //命令类型是SQL语句
    sqlCommand.Connection = conn; //指定SqlConnection对象

    //获取数据流
    SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync();
    while(await sqlDataReader.ReadAsync())
    {
        Console.WriteLine(sqlDataReader["SName"] + "\t" + sqlDataReader["SAge"] + "\t" + sqlDataReader["SSex"]);
    }
    //关闭数据流
    await sqlDataReader.CloseAsync();
}

异步方法也是以流的方式获取数据，若在Winform或WPF应用程序中，点击按钮执行异步代码时，不会让界面停顿。

SqlDataReader常用方法

GetOrdinal()方法

在SqlDataReader对象中，使用GetOrdinal()方法可以根据表中指定的列名（字段名）得到对应的索引值，索引值是从0开始。如 GetOrdinal("SId"）获取SId字段所在的索引值。

GetName()方法

GetName()方法可以根据索引值得到对应的列名，与GetOrdinal()方法正好是一个相反的操作。如 GetName(0)获取索引为0的字段名。

获取指定类型的值

Get<.NET数据类型>(索引): 如 GetInt32()、GetString()

GetValue()方法

根据索引值返回表中列的值，但是获取值的类型是object，是经过装箱后的结果，有性能损失。

根据列名获取值

根据列名获取：使用sdr[列名]获取，如sdr["SName"], 返回的类型是object。 先获取索引，再根据索引获取值：sdr.GetString(sdr.GetOrdinal("SName"))

GetFieldValue<T>()方法

• 同步方法

在SqlDataReader对象中，GetFieldValue<T>泛型同步方法比以上方法好很多，不存在装箱、拆箱的问题，因为GetFieldValue<T>是泛型方法，强类型的，获取字段数据时，类型是确定的。 比如：sdr.GetFieldValue<string>(1）,索引1字段SName，类型为string。将T替换为string,是强类型的。

• 异步方法

GetFieldValueAsync<T>()

IsDBNull()方法

使用SqlDataReader对象中的IsDBNull()同步或IsDBNullAsync()异步方法，可以判断所读取列的值是否为NULL值。

SqlDataReader常用数学

FieldCount属性

使用FieldCount属性，可以返回当前SqlDataReader对象获取的表中共有多少个字段，返回类型是int。

HasRows属性

用来判断得到的数据流中是否存在一行或多行数据。为True,则表示有数据。

RecordsAffected属性

可以得到执行T-Sql语句后受影响的记录数。如果没有受影响的记录数或执行失败，则为0，如果为SELECT查询语句，则为-1.

IsClosed属性

测试当前SqlDataReader对象是否关闭，如果处于关闭则值为True.

SqlParameter对象

SqlParameter参数对象的使用，在SqlCommand对象中对T-SQL语句和存储过程参数化，实现相关的数据库操作。

添加参数的方法

SqlCommand对象中，有一个Parameters属性，该属性的类型是SqlParameterCollection，是一个SqlParameter 对象的集合。其有3个方法：

• Add()方法：将单个的SqlParameter对象添加到集合中
• AddRange()方法：将SqlParameter类型的数组添加到集合中
• AddWithValue()方法：将参数名称和对应的值添加到集合中

Add()方法

using(SqlConnection sqlConn = new SqlConnection(connstr))
{
    //打开数据库连接
    await sqlConn.OpenAsync();
    //T-sql语句
    string sql = "select * from Student WHERE SName LIKE '%'+@name+'%' AND SAge>@age";
    //创建sqlCommand对象
    SqlCommand sqlCommand = sqlConn.CreateCommand();
    sqlCommand.CommandText = sql;
    sqlCommand.CommandType = System.Data.CommandType.Text;
    sqlCommand.Connection = sqlConn;

    //配置参数
    SqlParameter sqlParameter1 = new SqlParameter("@name",SqlDbType.NVarChar);
    SqlParameter sqlParameter2 = new SqlParameter("@age",SqlDbType.Int);
    //将参数添加到集合
    sqlCommand.Parameters.Add(sqlParameter1);
    sqlCommand.Parameters.Add(sqlParameter2);
    //给参数赋值
    sqlCommand.Parameters["@name"].Value = "张";
    sqlCommand.Parameters["@age"].Value = 0;

    //获取数据流
    SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync();
    while(await sqlDataReader.ReadAsync())
    {
        Console.WriteLine($"姓名={await sqlDataReader.GetFieldValueAsync<string>(1)},年龄={await sqlDataReader.GetFieldValueAsync<int>(2)}");
    }
    await sqlDataReader.CloseAsync();
}

AddRange()添加参数

using(SqlConnection sqlConn = new SqlConnection(connstr))
{
    //打开数据库连接
    await sqlConn.OpenAsync();
    //T-sql语句
    string sql = "select * from Student WHERE SName LIKE '%'+@name+'%' AND SAge>@age";
    //创建sqlCommand对象
    SqlCommand sqlCommand = sqlConn.CreateCommand();
    sqlCommand.CommandText = sql;
    sqlCommand.CommandType = System.Data.CommandType.Text;
    sqlCommand.Connection = sqlConn;
    //配置参数
    SqlParameter[] sqlParameters = new SqlParameter[]
    {
        new SqlParameter("@name",SqlDbType.NVarChar),
        new SqlParameter("@age",SqlDbType.Int)
    };
    //添加到参数集合中
    sqlCommand.Parameters.AddRange(sqlParameters);
    //给参数赋值
    sqlCommand.Parameters["@name"].Value = "张";
    sqlCommand.Parameters["@age"].Value = 0;
    //获取数据流
    SqlDataReader sdr = await sqlCommand.ExecuteReaderAsync();
    while(await sdr.ReadAsync())
    {
        Console.WriteLine($"姓名={await sdr.GetFieldValueAsync<string>(1)},年龄={await sdr.GetFieldValueAsync<int>(2)}");
    }
    await sdr.CloseAsync();
}

AddWithValue()添加参数

可以在Parameters属性的AddWithValue()方法上直接添加参数并赋值，该方法具有2g个参数： AddWithValue(string parameterName, object value)

using (SqlConnection sqlCon = new SqlConnection(connstr))
{
    //打开数据库连接
    await sqlCon.OpenAsync();
    //T-sql语句
    string sql = "select * from Student WHERE SName LIKE '%'+@name+'%' AND SAge>@age";
    //创建sqlCommand对象
    SqlCommand sqlCommand = sqlCon.CreateCommand();
    sqlCommand.CommandText = sql;
    sqlCommand.CommandType = CommandType.Text;
    sqlCommand.Connection = sqlCon;
    
    //直接添加参数并赋值
    sqlCommand.Parameters.AddWithValue("@name", "张");
    sqlCommand.Parameters.AddWithValue("@age", 0);
    //获取数据流
    SqlDataReader sdr = await sqlCommand.ExecuteReaderAsync();
    while(await sdr.ReadAsync())
    {
        Console.WriteLine($"姓名={await sdr.GetFieldValueAsync<string>(1)},年龄={await sdr.GetFieldValueAsync<int>(2)}");
    }
    await sdr.CloseAsync();
}

与以上两个方法相比，使用AddWithValue()方法更为简单，代码量更少，但是不能对参数类型、长度进行设置。

SqlDataAdapter对象

SqlDataAdapter对象，此对象称为数据适配器对象，是DataSet与SQL Server之间通信的桥梁。 DataSet是数据库对象，相当于内存中的数据库，其中可以存放多个DataTable对象，每个DataTable对象就是一张表。SqlDataAdapter对象也是通过SqlCommand对象来实现对数据库操作的，其功能比SqlCommand对象强大，可以将SqlDataAdapter对象理解为超强的SqlCommand。通过SqlDataAdapter数据适配器，可以实现DataSet和SQLServer数据库之间的数据传输。它们之间是断开连接的，实现了离线操作数据库。对于SqlDataAdapter对象，无需手动打开和关闭数据库连接，它会自动维护数据库连接状态。

基本含义

SqlDataAdapter从数据库中拉取的数据都是填充到DataSet对象中。DataSet中可以包含多个DataTable。SqlDataAdapter是一个密封的类，不可继承，具有3个带参数的构造函数，实例化时需要传入参数。 在SqlDataAdapter类中，还有与表操作对应的添加、删除、修改和查询的SqlCommand对象：

• SelectCommand: 查询数据的命令
• InsertCommand: 插入数据的命令
• DeleteCommand: 删除数据的命令
• UpdateCommand: 更新数据的命令

实例化

对于无参数的SqlDataAdapter类，实例化后没有传入任何参数，所以需要使用SqlDataAdapter对象的属性指定T-SQL/存储过程和数据库连接字符串。 使用SqlDataAdapter对象的SelectCommand属性指定SqlCommand对象。

using (SqlConnection sqlCon = new SqlConnection(connstr))
{
    //打开数据库连接
    await sqlCon.OpenAsync();
    //T-sql语句
    string sql = "DELETE FROM Student";
    //实例化SqlDataAdapter对象,无参数
    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
    //指定SelectCommand，包含T-Sql语句和SqlConnection对象
    sqlDataAdapter.SelectCommand = new SqlCommand(sql, sqlCon);
}

构造函数的使用

SqlDataAdapter对象具有3个带参数的构造函数。

SqlDataAdapter(SqlCommand)

SqlDataAdapter(SqlCommand selectCommand)表示在实例化SqlDataAdapter对象时，在构造函数中传入一个SELECT 的SqlCommand对象，用于获取数据。

//获取数据库连接字符串
string connstr = ConfigurationManager.ConnectionStrings["DotNetConDb"].ConnectionString;
using (SqlConnection sqlConn = new SqlConnection(connstr))
{
    //打开数据库连接
    await sqlConn.OpenAsync();
    //T-sql语句
    string sql = "select * from Student";
    //实例化SqlCommand对象
    SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
    //实例化SqlDataAdapter对象
    SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
}

SqlDataAdapter(string, SqlConnection)

SqlDataAdapter(string selectCommandText, SqlConnection selectConnection)构造函数有2个参数，一个是string类型的SELECT 语句，另一个是数据库连接对象的SqlConnection。

//获取数据库连接字符串
string connstr = ConfigurationManager.ConnectionStrings["DotNetConDb"].ConnectionString;
using(SqlConnection sqlConn = new SqlConnection(connstr))
{
    //打开数据库连接
    await sqlConn.OpenAsync();
    //T-sql语句
    string sql = "select * from Student";
    //实例化sqldataAdapter对象,将T-sql语句和SqlConnection对象传入
    SqlDataAdapter sda = new SqlDataAdapter(sql, sqlConn);
}

SqlDataAdapter(string,string)

SqlDataAdapter(string selectCommandText, string selectConnectionString)构造函数带有2个string类型的参数：

• selectCommandText表示select语句
• selectConnectionString表示数据库连接字符串

此构造函数更先进，抛开了SqlCommand和SqlConnection对象，直接使用t-sql语句和数据库连接字符串实例化SqlDataAdapter对象。

//获取数据库连接字符串
string connstr = ConfigurationManager.ConnectionStrings["DotNetConDb"].ConnectionString;
string sql = "select * from Student";   
//实例化SqlDataAdapter对象,将T-sql语句和连接字符串传入
SqlDataAdapter sda = new SqlDataAdapter(sql, connstr);

填充数据

SqlDataAdapter拉取的数据需要填充到DataSet对象中，使用Fill()方法执行数据填充功能。

//获取数据库连接字符串
string connstr = ConfigurationManager.ConnectionStrings["DotNetConDb"].ConnectionString;
string sql = "select * from Student";   
//实例化SqlDataAdapter对象,将T-sql语句和连接字符串传入
SqlDataAdapter sda = new SqlDataAdapter(sql, connstr);

//实例化DataSet对象
DataSet ds = new DataSet();
//填充DataSet对象
sda.Fill(ds);

使用Fill()方法填充到DataSet对象时，其实是填充到了DataSet数据集中第1个DataTable中，其索引值为0。因此，也可以直接使用Fill()方法填充到单独的DataTable中：

//获取数据库连接字符串
string connstr = ConfigurationManager.ConnectionStrings["DotNetConDb"].ConnectionString;
string sql = "select * from Student";   
//实例化SqlDataAdapter对象,将T-sql语句和连接字符串传入
SqlDataAdapter sda = new SqlDataAdapter(sql, connstr);

//实例化DataTable对象
DataTable dt = new DataTable();
//填充DataTable对象
sda.Fill(dt);

获取数据

DataTable中的行使用DataRow类表示，列使用DataColumn类表示。使用foreach循环DataTable中的DataRow对象，然后根据每行中的列名得到数据：

//获取数据库连接字符串
string connstr = ConfigurationManager.ConnectionStrings["DotNetConDb"].ConnectionString;
string sql = "select * from Student";   
//实例化SqlDataAdapter对象,将T-sql语句和连接字符串传入
SqlDataAdapter sda = new SqlDataAdapter(sql, connstr);

//实例化DataTable对象
DataTable dt = new DataTable();
//填充DataTable对象
sda.Fill(dt);
//获取数据
foreach(DataRow dr in dt.Rows)
{
    Console.WriteLine($"姓名={dr["SName"]},年龄={dr["SAge"]}");
}