ADO.NET新手上路教程_ado trustservercertificate

前言

利用ADO.NET连接数据库进行相关操作可以说是每个.NET新手必须学习的一项内容。从学习的角度来看，我个人其实反对新手一开始就学Entity Framework之类的ORM框架，因为Entity Framework本质上还是基于ADO.NET的二次封装，所以扎实掌握SQL和ADO.NET才能让新手更好的学习之后的ORM框架。下面就来介绍一下ADO.NET中常用的几个类。

数据准备

为了方便，我这里选用VS2015自带的LocalDB作为数据源，在其中新建了一个数据库DBSchool，然后新建一张数据表[TStudent]，其字段结构如下图所示：

其中Id字段为自增主键，其余字段用于描述学生信息，然后在数据表中添加三条数据，我们的实验数据就准备好了，如下图所示：

1、数据库的连接

想要对数据库进行操作，首先肯定得连接数据库，连接数据库就需要相应的数据库连接字符串，本文的连接字符串如下所示：

@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DBSchool;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
1

在ADO.NET中，SqlConnection类主要负责连接和关闭数据库，下面代码演示了数据库的连接和关闭：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static string ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DBSchool;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

        static void Main(string[] args)
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            connection.Open();
            if (connection.State == ConnectionState.Open)
            {
                Console.WriteLine("数据库连接已开启");
                Console.WriteLine("数据源：{0}", connection.DataSource);
                Console.WriteLine("数据库名：{0}", connection.Database);
                Console.WriteLine("连接时间：{0}", connection.ConnectionTimeout);
            }

            // 关闭连接
            connection.Close();
            connection.Dispose();
            if (connection.State == ConnectionState.Closed)
            {
                Console.WriteLine("数据库连接已关闭");
            }
            Console.ReadKey();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

运行结果如下所示：

数据库连接已开启
数据源：(localdb)\MSSQLLocalDB
数据库名：DBSchool
连接时间：30
数据库连接已关闭
1
2
3
4
5

一般connection.Open()用于开启数据库连接，connection.Close()、connection.Dispose()用于关闭数据库连接，在这两者之间的区域就是你对数据库操作的具体代码，但一般我们不推荐上面这种写法，取而代之的是利用using关键字将一段数据库操作代码包装起来，代码如下所示：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static string ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DBSchool;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

        static void Main(string[] args)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                Console.WriteLine("数据库连接已开启");
                Console.WriteLine("数据源：{0}", connection.DataSource);
                Console.WriteLine("数据库名：{0}", connection.Database);
                Console.WriteLine("连接时间：{0}", connection.ConnectionTimeout);
            }
            Console.ReadKey();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

运行结果如下所示：

数据库连接已开启
数据源：(localdb)\MSSQLLocalDB
数据库名：DBSchool
连接时间：30
1
2
3
4

2、数据库的增删改

数据库的增删改操作主要利用SqlCommand实现，其中的几个重要属性和方法如下表所示：

2.1、添加数据：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static string ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DBSchool;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

        static void Main(string[] args)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                // SQL参数
                SqlParameter[] parameters =
                {
                    new SqlParameter("@Sid", SqlDbType.NVarChar, 20),
                    new SqlParameter("@Sname", SqlDbType.NVarChar, 10),
                    new SqlParameter("@Sgender", SqlDbType.NVarChar, 2),
                    new SqlParameter("@Sage", SqlDbType.Int),
                    new SqlParameter("@Sphone", SqlDbType.NVarChar, 15),
                };

                // 参数赋值
                parameters[0].Value = "1004";
                parameters[1].Value = "吴六";
                parameters[2].Value = "男";
                parameters[3].Value = 20;
                parameters[4].Value = "15874513687";

                // 设置SQL语句
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = "insert into [TStudent] values(@Sid,@Sname,@Sgender,@Sage,@Sphone)";
                command.Parameters.AddRange(parameters);

                // 执行SQL
                try
                {
                    command.ExecuteNonQuery();
                    command.Parameters.Clear();
                    Console.WriteLine("添加数据成功");
                }
                catch
                {
                    Console.WriteLine("添加数据失败");
                }
            }
            Console.ReadKey();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

2.2、修改记录

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static string ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DBSchool;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

        static void Main(string[] args)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                // SQL参数
                SqlParameter[] parameters =
                {
                    new SqlParameter("@Sid", SqlDbType.NVarChar, 20),
                    new SqlParameter("@Sname", SqlDbType.NVarChar, 10)
                };

                // 参数赋值
                parameters[0].Value = "1004";
                parameters[1].Value = "吴老六";

                // 设置SQL语句
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = "update [TStudent] set Sname=@Sname where Sid=@Sid";
                command.Parameters.AddRange(parameters);

                // 执行SQL
                try
                {
                    command.ExecuteNonQuery();
                    command.Parameters.Clear();
                    Console.WriteLine("修改数据成功");
                }
                catch
                {
                    Console.WriteLine("修改数据失败");
                }
            }
            Console.ReadKey();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

2.3、删除记录

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static string ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DBSchool;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

        static void Main(string[] args)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                // SQL参数
                SqlParameter[] parameters =
                {
                    new SqlParameter("@Sid", SqlDbType.NVarChar, 20)
                };

                // 参数赋值
                parameters[0].Value = "1004";

                // 设置SQL语句
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = "delete from [TStudent] where Sid=@Sid";
                command.Parameters.AddRange(parameters);

                // 执行SQL
                try
                {
                    command.ExecuteNonQuery();
                    command.Parameters.Clear();
                    Console.WriteLine("删除数据成功");
                }
                catch
                {
                    Console.WriteLine("删除数据失败");
                }
            }
            Console.ReadKey();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

在以上的增删改代码中，一般推荐使用SqlParameter封装相关参数，一是方便SQL的书写和阅读，二是能够有效避免SQL注入问题。

3、数据库的查询

数据库的查询用得最为广泛，关于查询操作，ADO.NET中一般有三种写法，下面逐一介绍。
3.1、查询方法一

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static string ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DBSchool;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

        static void Main(string[] args)
        {
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                // 设置SQL语句
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = "select * from [TStudent]";

                // 获取查询结果，填充到DataTable
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
            }

            foreach (DataRow row in dataTable.Rows)
            {
                Console.Write(row["Id"].ToString() + "\t");
                Console.Write(row["Sid"].ToString() + "\t");
                Console.Write(row["Sname"].ToString() + "\t");
                Console.Write(row["Sgender"].ToString() + "\t");
                Console.Write(row["Sage"].ToString() + "\t");
                Console.Write(row["Sphone"].ToString() + "\t");
                Console.WriteLine();
            }
            Console.ReadKey();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

3.2、查询方法二

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static string ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DBSchool;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

        static void Main(string[] args)
        {
            DataTable dataTable = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter("select * from [TStudent]", ConnectionString))
            {
                adapter.Fill(dataTable);
            }

            foreach (DataRow row in dataTable.Rows)
            {
                Console.Write(row["Id"].ToString() + "\t");
                Console.Write(row["Sid"].ToString() + "\t");
                Console.Write(row["Sname"].ToString() + "\t");
                Console.Write(row["Sgender"].ToString() + "\t");
                Console.Write(row["Sage"].ToString() + "\t");
                Console.Write(row["Sphone"].ToString() + "\t");
                Console.WriteLine();
            }
            Console.ReadKey();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

3.3、查询方法三

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static string ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DBSchool;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

        static void Main(string[] args)
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            connection.Open();

            // 设置SQL语句
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.Text;
            command.CommandText = "select * from [TStudent]";

            // 遍历数据行
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.Write(reader.GetInt32(reader.GetOrdinal("Id")) + "\t");
                Console.Write(reader.GetString(reader.GetOrdinal("Sid")) + "\t");
                Console.Write(reader.GetString(reader.GetOrdinal("Sname")) + "\t");
                Console.Write(reader.GetString(reader.GetOrdinal("Sgender")) + "\t");
                Console.Write(reader.GetInt32(reader.GetOrdinal("Sage")) + "\t");
                Console.Write(reader.GetString(reader.GetOrdinal("Sphone")) + "\t");
                Console.WriteLine();
            }
            
            // 关闭连接
            connection.Close();
            connection.Dispose();
            Console.ReadKey();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

运行结果如下所示：

1       1001    张三    男      20      13745258478
2       1002    李四    女      21      18754124736
3       1003    王五    女      22      14963587415
1
2
3

上面三种方法都可以进行数据库查询操作，但也需要注意它们之间的区别。方法一和方法二查询后将结果放入内存中的DataTable，接下来我们只需要操作DataTable即可。方法三则是在数据读取完毕之前一直保持与数据库的连接，直到读取操作完成。一般更推荐方法一和方法二的写法。

4、SQL参数化操作

同志们一定发现了，在上面的代码中有一个类的出现频率很高，那就是SqlParameter。之前也介绍过SqlParameter的作用主要是防止SQL注入问题。那么到底什么是SQL注入问题？下面来看一个WinForm实现用户登录的demo。首先建立一张用户表[TUser]，加入两条数据，如下图所示：

登录界面代码

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        private static string ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DBSchool;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

        public Form1()
        {
            InitializeComponent();
        }

        private void btnLogin_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtUserName.Text) || string.IsNullOrEmpty(txtPassword.Text))
            {
                MessageBox.Show("请输入用户名和密码", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                // 设置SQL语句
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = "select * from [TUser] where UserName='" + txtUserName.Text + "' and Password='" + txtPassword.Text + "'";

                // 填充DataTable
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);

                // 判断登录是否成功
                if (dataTable.Rows.Count > 0)
                {
                    MessageBox.Show("登陆成功");
                }
                else
                {
                    MessageBox.Show("登陆失败");
                }
            }
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

WinForm登录界面如下图所示，输入admin和123456后点击按钮，发现能够正常登陆，这看起来好像没什么问题。

现在我们将密码改一改，输入abcd、' or '1'='1，如下图所示：

事实就是数据库中根本就没这条记录，但最后竟然也能登陆！！！这其实就是一个典型的SQL注入问题。问题就出在下面这条语句：

command.CommandText = "select * from [TUser] where UserName='" + txtUserName.Text + "' and Password='" + txtPassword.Text + "'";
1

很多同志都喜欢这么写，其实这是一个很大的安全隐患。这种情况我们要么使用存储过程，要么使用参数化的SQL语句，而SqlParameter就是干这个活的。我们将登陆代码改一改，如下所示：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        private static string ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DBSchool;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

        public Form1()
        {
            InitializeComponent();
        }

        private void btnLogin_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtUserName.Text) || string.IsNullOrEmpty(txtPassword.Text))
            {
                MessageBox.Show("请输入用户名和密码", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                // SQL查询参数
                SqlParameter[] parameters =
                {
                    new SqlParameter("@UserName", SqlDbType.NVarChar, 20),
                    new SqlParameter("@Password", SqlDbType.NVarChar, 20),
                };
                parameters[0].Value = txtUserName.Text;
                parameters[1].Value = txtPassword.Text;

                // 设置SQL语句
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = "select * from [TUser] where UserName=@UserName and Password=@Password";
                command.Parameters.AddRange(parameters);

                // 填充DataTable
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);

                // 判断登录是否成功
                command.Parameters.Clear();
                if (dataTable.Rows.Count > 0)
                {
                    MessageBox.Show("登陆成功");
                }
                else
                {
                    MessageBox.Show("登陆失败");
                }
            }
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

首先输入admin、123456，如下图所示，发现能够正常登陆。

然后输入abcd、' or '1'='1，如下图所示，发现登陆失败。

使用参数化的SQL语句有两个好处：一是书写和阅读都很清晰，二是能够有效避免SQL注入问题。以后同志们在传递参数做查询时，一定要记得利用SqlParameter封装你的查询参数。