SQL Server 与 C# 交互

SQL Server 的权限管理

SQL 语句我就不多说了。这篇文章仅仅用来记录如何让 C# 与 SQL Server 进行连接。在 SQL Server 中，可以创建登录名（在 MySQL 中没有），然后可以创建基于登录名的用户名。为每一个用户名分配具体的权限。换句话来说，登录名是登录数据库的权限而用户名是对数据库的操作的权限。SQL Server 中使用映射可以把对各登录名中的用户映射到数据库对象中。

。

创建登录名以及用户

create login <登录名> with password=<密码>
create user <用户名> from login <登录名>

在实训的作业中，我需要为每个学生用户创建一个登录名同时也不需要访问其他的数据库所以可以创建一个与登录名相同的用户名。创建角色也有助于将多个权限的集合赋予角色的子对象（数据库用户）:

ALTER ROLE <角色名> ADD MEMBER <用户名>

注意： 修改角色需要应用程序的用户具有权限：GRANT ALTER ANY USER ON <角色名> TO <用户名>

角色的权限与他的用户：

C# 与 SQL Server 通信

C# 中用的最广泛的是 ADO.Net 模型。ADO.Net 的基础设施：

DataSet 是 ADO.NET 的非连接（断开）结构的核心组件。DataSet 的设计目的很明确：为了实现独立于任何数据源的数据访问。因此，ADO.NET结构可以用于多种不同的数据源，用于 XML 数据，或用于管理应用程序本地的数据。DataSet 包含一个或多个 DataTable 对象的集合，这些对象由数据行和数据列以及主键、外键、约束和有关 `DataTable 对象中数据的关系信息组成。

ADO.NET 结构的另一个核心元素是 .NET 数据提供程序（Data Provider）。具体包括：

• Connection 对象提供与数据源的连接。
• Command 对象使您能够访问用于返回数据、修改数据、运行存储过程以及发送或检索参数信息的数据库命令。
• DataReader 对象从数据源中提供快速的，只读的数据流。
• DataAdapter 对象提供连接 DataSet 对象和数据源的桥梁。DataAdapter 使用 Command 对象在数据源中执行 SQL 命令，以便将数据加载到 DataSet 中，并使对 DataSet 中数据的更改与数据源保持一致。

Data Provider

Data Provider 用于连接数据库、执行 SQL 语句和检索结果。

和 Java 使用不同的 JDBC 一致。如果了连接 SQL Server 需要用到微软提供的驱动 mssql-jdbc-6.4.0.jre8.jar。一个典型的连接（SQL Server 需要打开 TCP/IP 监听以及监听指定的端口）程序如下：

package test;
import java.sql.Connection;
import java.sql.*;
public class test1 {
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		String dcn = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String url = "jdbc:sqlserver://localhost:1433;databaseName=宿舍数据库";
		try {
			Class.forName(dcn);
			Connection conn = DriverManager.getConnection(url, "appuser", "123456789");
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}

}

而在 C# 中需要使用不同的名命空间中的类。连接数据库并操作数据库的几个步骤：

• 连接字符串：在 SQL Server 中，使用 key=value;key=value 格式的字符串，一般使用，连接 SQL Server 数据库可以使用字符串（使用 SQL 身份验证）：

1. 连接非 Express 版本的数据库：Server=.;Database=宿舍数据库;User Id=appuser;pwd=123456789
2. 连接 Express 版本的数据库：Server=<计算机名>\SQLEXPRESS;Database=宿舍数据库;User Id=appuser;pwd=123456789

使用类 SqlConnectionStringBuilder 也可以构造连接的字符串：

实例名.InitialCatalog = "宿舍数据库";
实例名.Password = "123456789";
实例名.UserID = "appuser";
实例名.DataSource = ".";

• 连接：使用 System.Data.SqlClient名命空间（针对 Microsoft SQL Server 7.0 及以上）的 SqlConnection 对象就可以创建一个持续的连接。有几个属性是需要注意的：

1. State：Closed: 连接处于关闭状态。Open: 连接处于打开状态。Connecting: 连接对象正在与数据源连接。Executing: 连接对象正在执行命令。Fetching: 连接对象正在检索数据。Broken: 与数据源的连接中断。它们都是 ConnectionState 的枚举值。

如同文件描述符一样，打开一个连接是非常是耗时的且占用数据库的资源。使用 using 语句块 可以自动释放连接对象。

• 构造 SQL 语句：

为了防止像

strSQL = "SELECT * FROM users WHERE (name = '" + userName + "') and (pw = '"+ passWord +"');"
-- 填入 userName = "' OR '1'='1"; 与 passWord = "' OR '1'='1";
将得到这样的一条SQL语句：
strSQL = "SELECT * FROM users WHERE (name = '' OR '1'='1') and (pw = '' OR '1'='1');" 

的 SQL 注入攻击，就需要用到参数化的 SQL 参数：

var pri_sno = dataGridView2[0, e.RowIndex].Value;
var pri_arrive_time = dataGridView2[3, e.RowIndex].Value;
var up_received_time = dataGridView2[4, e.RowIndex].Value;
var cmd = new SqlCommand($"update express set Mreceive=@receive where Sno=@sno and Marrive=@arrive", m_conn);
                    cmd.Parameters.AddRange(new SqlParameter[]
                    {
                        new SqlParameter("@sno", SqlDbType.VarChar, 50){ Value=pri_sno},
                        new SqlParameter("@arrive", SqlDbType.Date){ Value=pri_arrive_time},
                        new SqlParameter("@receive", SqlDbType.Date){ Value=up_received_time}
                    });

在这个例子中 SqlCommand 的对象可以指定已经打开的连接，他的属性 Parameters 保存类所有的参数的类型、长度和值。所有的参数名都是以 @ 开头。

• 执行 SQL 语句：

SqlCommand 的实例可以执行不同的 SQL 语句类型：

1. ExecuteNonQuery: 执行不返回数据行的操作，并返回一个int类型的数据。注意： 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。 对于其他所有类型的语句，返回值 为 -1。
2. ExecuteReader: 执行查询，并返回一个 DataReader 对象。
3. ExecuteScalar: 执行查询，并返回查询结果集中第一行的第一列（object类型）。如果找不到结果集中第一行的第一列，则返回 null 引用。

• 获取返回的结果（多个元组）：

var r = cmd.ExecuteReader();
                if (r.Read())
                {
                    // r.FieldCount : 返回的各个元组的字段数量
                    // r[<字段名>].ToString().Trim()：去掉首尾空白的对应 Cell 的值
                    // r.GetName(i)：第 i + 1 虚表的列名
                    // r.GetValue(i)：第 i + 1 列的值
                }

注意：如果连接对象会被复用，那么需要先关闭通过这个连接创建的所有的 DataReader 对象。

Data Adapter

为了满足实际的需求，例如将查询的结果显示在 GUI 界面中，我们需要将某个查询的结果填充（Fill）到 DataSet 中或者某个 DataTable，DataSet 可以绑定很对的数据源，数据库也是一种。为了动态的填充数据库并且将用户的修改保存到用户的基本表中，可以通过适配器指定 CRUD 的指令。

当对于 DataGridView （定义了 Rows 和 Columns）的修改可以反应到绑定的基本表中，注意基本表必须有主键。DataAdapter 可以将查询的数据填充到 DataGridView 绑定的数据集，同时定义了 CRUD 的 SQL 语句，需要的时候自动执行。

例1：更新和插入

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace DatabaseManager
{
    public partial class TestForDataGridView : Form
    {
        private SqlDataAdapter adapter;
        private SqlConnection conn;
        public TestForDataGridView()
        {
            InitializeComponent();
            
        }
        public TestForDataGridView(SqlConnectionStringBuilder sb):this()
        {
            
            conn = new SqlConnection(sb.ConnectionString);
            // 打开连接
            conn.Open();
            adapter = GetAdapter(sb);
        }
        public SqlDataAdapter GetAdapter(SqlConnectionStringBuilder sb)
        {
            SqlDataAdapter adapter = null;
            // 适配器
            adapter = new SqlDataAdapter("select Uname, Utype,Udetail from users", conn);
            // 填充表 DataTable(是 DataSet 的 Tables 的元素)
            adapter.Fill(this.dataSet1, "users");
            var table = this.dataSet1.Tables["users"];
            // 指定显示的 GridView
            this.dataGridView1.DataSource = new BindingSource() { DataSource = table };
            // 其他的数据信息
            SqlCommand sqlcmd = new SqlCommand("update users set Udetail=@newdetail where Uname=@uname", conn);
            sqlcmd.Parameters.AddRange(new SqlParameter[]
            {
                    new SqlParameter("@newdetail", SqlDbType.VarChar, 50, "Udetail"){ SourceVersion= DataRowVersion.Current},
                    new SqlParameter("@uname", SqlDbType.VarChar, 50, "Uname"){SourceVersion= DataRowVersion.Original}
            });
            adapter.UpdateCommand = sqlcmd;
            // 新建信息
            SqlCommand sqlcmd_create = new SqlCommand("insert into users(Uname,Utype,Udetail) values(@uname,@type,@detail)", conn);
            sqlcmd_create.Parameters.AddRange(new SqlParameter[]
            {
                    new SqlParameter("@uname", SqlDbType.VarChar, 50, "Uname"){ SourceVersion= DataRowVersion.Current},
                    new SqlParameter("@type", SqlDbType.TinyInt){SourceVersion= DataRowVersion.Original, SourceColumn="Utype"},
                    new SqlParameter("@detail", SqlDbType.VarChar,50,"Udetail"){SourceVersion= DataRowVersion.Current}
            });
            adapter.InsertCommand = sqlcmd_create;
            return adapter;
        }

        private void button1_Click(Object sender, EventArgs e)
        {
            dataGridView1.EndEdit();
            int eff = 0;
            var u = this.dataSet1.Tables["users"];
            var ud = u.Select(null, null, DataViewRowState.ModifiedCurrent);
            var id = u.Select(null, null, DataViewRowState.Added);
            eff = adapter.Update(ud);
            eff += adapter.Update(id);
            
        }
    }
}

注意几点：

1. InsertCommand、UpdateCommand 等提供的是插入、更新 DataSet 的数据的 SQL 指令 SqlCommand 的对象
2. adapter.Fill 可以将适配器得到的数据填充到一个指定的 DataSet 对象。
3. SourceVersion 指定的是这个 SQL 参数的值使用的是旧值还是新值，对于需要修改主码的语句很有用！
4. SourceColumn 表示的是当前的参数是 DataTable 中那一列的值（对一个元组来说）

DataGridView 有一个问题需要注意：在 DataGridView 的 CellEndEdit 事件中直接更新保存是不可取的。例如这个响应这个事件的委托：

private void dataGridView1_CellEndEdit(Object sender, DataGridViewCellEventArgs e)
        {
            MessageBox.Show(dataSet1.Tables["users"].Rows[e.RowIndex].RowState.ToString());
            adapter.Update(xxx);
        }

第一次修改：

第二次修改：

在一次修改的时候为 Unchanged，也就说还没有真正的完成修改，第二次修改同一个 Cell，显示 Modified 是因为上一次被修改了，但是第二次修改还未完成。所以，最终写入的上一次的修改。所以正确的方法应该使用外部的按钮来强制提交操作，例如其中的 button1_Click。

SQL Server 连接池

详细的内容可以参考：ADO.NET入门教程（五） 细说数据库连接池

参考

• (Stackoverflow) 登录名与用户的区别
• (MSDN) Microsoft SQL Server 权限的使用
• (connectionstrings.com)连接字符串
• (MSDN)DataGridView EndCellEdit 事件的响应
• (MSDN)DataRowVersion
• (MSDN)EndEdit 方法
• (Stackoverflow)提交对 DataGridView 的修改到数据库
• (MSDN) Cell 修改事件引发的顺序
• (Cnblogs)liuhaorain 系列：ADO.NET入门教程