SQLSERVER（二）

1.学好ADO.NET课程的收获

　　1.1掌握C#访问数据库的基本方法，如连接和断开数据库

　　1.2掌握数据查询的各种方法，如增删改查

　　1.3重点理解和掌握OOP原则在数据访问种的应用，如写通用读写类的意义

　　1.4重点理解和掌握基于OOP查询与对象封装，查询使用的最多

2.MIS系统部署方案

　　2.1完整的应用程序包括客户端和数据库服务端

　　　　2.1.1安装了程序的客户端，C/S架构，可能有多个

　　　　2.1.2数据库服务端，如SQLServer、Oracle、MySql

　　　　2.1.3AQO.NET就是连接客户端和服务端的一种数据库访问技术

　　2.2理解ADO.NET

　　　　2.2.1AxtiveX  Data  Objects(ADO)

　　　　2.2.2是.NET平台下应用程序和数据源进行交互的一组面向对象类库。这里的数据源不仅仅指数据库，可以是excel、ini、txt等数据文件

　　　　2.2.3简单理解就是——>>>数据访问组件

3.ADO.NET主要组件

主要由两部分组成

　　3.1.NET数据提供程序：用于连接到数据库、执行命令和检索结果。

　　   主要有四大对象

　　　　3.1.1Connection对象：负责连接数据库

　　　　3.1.2Command对象：负责对数据源执行命令

　　　　3.1.3DataReader对象：从数据源种读取只进且只读的数据流

　　　　3.1.4DataAdapter对象：用数据源填充DataSet并解析更新

　　3.2DataSet内存数据集

　　　　DataSet:独立与任何数据源，不直接和数据库交互

4..NET Framework数据提供程序

　　访问不同数据库需要有不同的访问对象

　　.NET Framework数据提供程序有以下

　　SQL Server数据库——>>>System.Data.SqlClient

　　Access、Excel——>>>System.Data.OleDb

 5.连接数据库的准备工作

　　需要SQLServer服务器端口查看与修改

　　打开软件SQL Server配置管理器——>>>确保MSSQLSERVER协议的TCP/IP启用，确定好端口号(默认1433)

6.正确的连接服务器

　　四个条件：服务器IP地址、数据库名称、登陆账号、登陆密码

　　一个必要：ADO.NET组件

7.Conncetion对象

　　7.1作用：建立应用程序和数据库的点与点连接

　　7.2属性:ConnectionString(连接字符串)

　　　　封装连接字符串的四个条件

　　　　Server=服务器名称或IP地址;DataBase=数据库名称;User ID=登陆账号;PassWord=登陆密码

　　　　使用SQLServer用户验证登陆的字符串示例(经常使用)：Server=192.168.1.2;DataBase=StudentManageDB;Uid=xiaoyang;Pwd=pwd01!

　　　　使用windows集成验证登录的字符串示例(仅限于本机)：Data Source=.;Initial Catalog=StudentManageDB;Intergrated Security=True

string conString="Server=wxj\\SQLExpress;DataBase=StudentManageDB;Uid=xiaoYang;Pwd=pwd01!";
string conString=@"Server=wxj\SQLExpress;DataBase=StudentManageDB;Uid=xiaoYang;Pwd=pwd01!";

　　　　【注意1】数据库命名实例要写完整

　　　　【注意2】注意使用“\\”或者字符串前面加“@”。C#不识别单个的“\“

　　　　【注意3】如果是默认实例，则可以用”.“或”localhost“代替服务器名称或ip地址

　　7.3方法

　　　　Open();   //打开连接

　　　　Close();    //关闭连接

//【1】引入命名空间
using System.Data;
using System.Data.SqlClient;
namespace _024ADONET
{
    class Program
    {
        static void Main(string[] args)
        {
            //【2】定义连接字符串
            string connectionString = "Server=.;DataBase=BOE1;Uid=sa;Pwd=222";
            //【3】创建连接对象
            SqlConnection conn = new SqlConnection(connectionString);
            //【4】打开连接
            conn.Open();
            if (conn.State==ConnectionState.Open)
            {
                Console.WriteLine("Connection is Opened!");
            }
            //【5】关闭连接
            conn.Close();
            if (conn.State==ConnectionState.Closed)
            {
                Console.WriteLine("Connection is Closed!");
            }
            Console.ReadLine();
        }
    }
}

SQL Connection连接字符串

 8.Command对象

　　8.1作用：向数据库发送SQL语句

　　　　封装“连接对象”和要执行的“SQL语句”

　　　　对数据库执行具体的操作，提供“增、删、改、查”的方法

　　8.2属性

　　　　CommandText：需要封装的sql语句和存储过程名称

　　　　Connection：Command对象使用的数据库连接对象

　　8.3方法

　　　　ExecuteNonQuery()；　　//返回受影响行数【增、删、改】    【int型】

　　　　ExecuteScalar()；　　//返回单一结果的查询          【object型】

　　　　ExecuteReader();　　//返回只读数据列表的查询　  返回数据集

　　8.4ExecuteNonQuery()方法使用要点

　　　　执行insert、update、delete类型的语句

　　　　执行后返回受影响的行数，一般是大于0的整数，等于0说明没有影响，等于-1说明语法错误

　　8.5C#调试SQL语句，可以打断点，可视化字符串，复制到SQL Server中

 1 //【1】引入命名空间
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 namespace _024ADONET
 5 {
 6     class Program
 7     {
 8         static void Main(string[] args)
 9         {
10             //【2】定义连接字符串
11             string connectionString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=1001";
12             //【3】创建连接对象
13             SqlConnection conn = new SqlConnection(connectionString);
14             //【4】打开连接
15             conn.Open();
16             if (conn.State==ConnectionState.Open)
17             {
18                 Console.WriteLine("Connection is Opened!");
19             }
20             //cmd对象
21             SqlCommand cmd = new SqlCommand();
22             cmd.Connection = conn;
23             //执行【插入一条语句】
24             string sql = $"insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)" +
25                 $"values('{"王洛一"}','{"男"}','{"1993-8-16"}',{4103251987756789},{22},'{"15234567890"}','{"苏州"}',{2})";
26             //创建Command对象
27             cmd.CommandText = sql;
28             int result = cmd.ExecuteNonQuery();
29             if (result == 1)
30             {
31                 Console.WriteLine("添加成功");
32             }
33             else
34             {
35                 Console.WriteLine("添加失败");
36             }
37             //执行【修改一个实体】
38             string sql1 = $"update Students set StudentName='{"刘伟一"}',Age=23 where StudentName='{"王洛一"}'";
39             cmd.CommandText = sql1;
40             int result1 = cmd.ExecuteNonQuery();
41             if (result1 == 1)
42             {
43                 Console.WriteLine("修改成功");
44             }
45             else
46             {
47                 Console.WriteLine("修改失败");
48             }
49             //执行【删除一个实体】
50             string sql2 = $"delete from Students where StudentName='{"刘伟一"}'";
51             cmd.CommandText = sql2;
52             int result2 = cmd.ExecuteNonQuery();
53             if (result2 == 1)
54             {
55                 Console.WriteLine("删除成功");
56             }
57             else
58             {
59                 Console.WriteLine("删除失败");
60             }
61             //执行【一次插入多条sql语句】
62             string sql_1 = $"insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)" +
63                 $"values('{"王洛2"}','{"男"}','{"1993-8-16"}',{4103251387756789},{22},'{"15234567890"}','{"苏州"}',{2})";
64             string sql_2 = $"insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)" +
65                 $"values('{"王洛3"}','{"男"}','{"1993-8-16"}',{4103251487756789},{22},'{"15234567890"}','{"苏州"}',{2})";
66             string sql_3 = $"insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)" +
67                 $"values('{"王洛4"}','{"男"}','{"1993-8-16"}',{4103251587756789},{22},'{"15234567890"}','{"苏州"}',{2})";
68             string manySql = sql_1 + ";" + sql_2 + ";" + sql_3;
69             cmd.CommandText = manySql;
70             int result4 = cmd.ExecuteNonQuery();
71             Console.WriteLine($"插入{result4}成功");
72             //【5】关闭连接
73             conn.Close();
74             if (conn.State==ConnectionState.Closed)
75             {
76                 Console.WriteLine("Connection is Closed!");
77             }
78             Console.ReadLine();
79         }
80     }
81 }

插入、修改、删除的C#_SQL

            //执行【一次插入多条sql语句】
            string sql_1 = $"insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)" +
                $"values('{"王洛5"}','{"男"}','{"1993-8-16"}',{4103251387756789},{22},'{"15234567890"}','{"苏州"}',{2})";
            string sql_2 = $"insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)" +
                $"values('{"王洛6"}','{"男"}','{"1993-8-16"}',{4103251487756789},{22},'{"15234567890"}','{"苏州"}',{2})";
            string sql_3 = $"insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)" +
                $"values('{"王洛7"}','{"男"}','{"1993-8-16"}',{4103251587756789},{22},'{"15234567890"}','{"苏州"}',{2})";
            string manySql = sql_1 + ";" + sql_2 + ";" + sql_3;
            cmd.CommandText = manySql;
            int result4 = cmd.ExecuteNonQuery();
            Console.WriteLine($"插入{result4}条实体成功");

插入多条实体记录

9.获取标识列

　　9.1问题引出

　　　　在Students表中添加一个新的学员对象，并返回新增学员的学号

　　　　提示：学号是自动表示列，即插入新纪录以后返回该记录的标识列

　　9.2问题解决

　　　　在insert语句后面添加select @@identity查询

　　　　在执行ExecuteScalar()方法，同时执行insert和select

　　9.3示例

insert into Students(StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)values('王二','男','1989-9-8',22,12342211,'15676545678','河南',1,);
select @@Identity

获取新增标识列

　　9.4说明

　　　　@@identity是数据库中的一个全局变量，里面保存着最近一次生成的标识列的值

            string sql = $"insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)" +
                $"values('{"王洛一"}','{"男"}','{"1993-8-16"}',{4103251956789},{22},'{"15234567890"}','{"苏州"}',{2})";
            //创建Command对象
            cmd.CommandText = sql+";select @@identity";
            object re = cmd.ExecuteScalar();

返回标识列

10.执行增、删、改步骤总结

1. 创建Connection对象
2. 组合sql语句insert、update、delete
3. 创建Command对象，并封装Connection和Sql语句
4. 打开连接
5. 执行ExecuteNonQuery()方法，返回受影响行数
6. 关闭连接

  11.常见错误

　　数据库服务器无法连接问题

1. 1. 检查SQLServer服务是否打开
   2. 检查连接字符串Server对应服务器名称是否正确(注意默认实例、命名实例的名称)
   3. 如果是连接局域网内其他计算机，请检查防火墙是否有拦截，或通过“计算机管理”检查
   4. 连接字符串中分号写错，关键字之间修改成英文半角的分号
   5. 用户sa登录失败：密码错误
   6. 列名无效：列名写错或无列
   7. 除了数值型，其他诸如字符串、日期都必须加单引号·　　
   8. 日期正确的格式：1998-9-9或1998-09-09  不能是1998年9月9日
   9. 占位符与列个数必须一致
   10. 占位符将“{}”错写为“[]”

  12.C#查询语法

　　12.1返回单一结果查询

　　　　使用ExecuteScalar()方法：返回第一行第一列的内容

string sql="select Count(*) from Students";
SqlCommand cmd=new SqlCommand(sql,conn);
conn.open();
object result=cmd.ExecuteScalar();
conn.Close();
//result=15,表明表内一共有15位学员

查询学员总数

sting sql="select StudentName from Students where StudentId=10006";
SqlCommand cmd=new SqlCommand(sql,conn);
conn.Open();
object result=cmd.ExecuteScalar();
string Name=result.ToString();
//Name即为所求

返回学号为特定值的学员姓名

　　　　单一结果查询步骤总结如下：

1. 1. 创建Connection对象
   2. 组合sql语句：select 单一结果查询
   3. 创建Command对象，并封装Connection和Sql语句
   4. 打开连接
   5. 执行ExecuteScalar()方法，返回单一结果(object类型)
   6. 关闭连接

　　12.2返回一个结果集查询

　　　　返回只读结果集的查询步骤总结如下：

1. 1. 创建Connection对象
   2. 组合select类型的sql语句
   3. 创建Command对象，并封装Connection和sql语句
   4. 打开连接
   5. 执行ExecuteReader()方法，返回DataReader对象
   6. 逐行读取查询结果【重点】
   7. 关闭读取器【重点】
   8. 关闭连接

　　　　返回一个数据集，不知道有多少行，就逐行读取，一直读到没有为止，读完关闭读取器，关闭连接

　　　　特别注意：

1. 1. DataReader对象采取循环的方式检查并读取数据
   2. 在没有读取完毕之前，数据库的连接将始终处于打开状态
   3. 关闭连接前，必须首先关闭读取器，且两者前后关闭

 1 using System;
 2 using System.Collections.Generic;
 3 //【1】引用命名空间
 4 using System.Data;
 5 using System.Data.SqlClient;
 6 namespace _025结果集查询
 7 {
 8     class Program
 9     {
10         static void Main(string[] args)
11         {
12             //【2】连接字符串&&数据库连接对象
13             string connString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=1001";
14             SqlConnection conn = new SqlConnection(connString);
15             //【3】sql命令&&Command对象
16             string sql = "select StudentId,StudentName,Gender from Students where Gender='男'";
17             SqlCommand cmd = new SqlCommand(sql,conn);
18             //【4】打开数据库
19             conn.Open();
20             List<string> readResult = new List<string>();
21             readResult.Add("Id\t姓名\t性别");
22             //【5】初始化读取器
23             SqlDataReader objReader = cmd.ExecuteReader();
24             while (objReader.Read())
25             {//【6】遍历读取结果
26                 readResult.Add($"{objReader["StudentId"]}\t{objReader["StudentName"]}\t{objReader["Gender"]}");
27             }
28             //【7】关闭读取器
29             objReader.Close();
30             //【8】关闭连接
31             conn.Close();
32             foreach (var item in readResult)
33             {
34                 Console.WriteLine(item);
35             }
36             Console.ReadLine();
37         }
38     }
39 }

DataReader读取结果集

　　12.3多个结果集查询

　　　　多条Sql语句中间用“;”隔开

　　　　第一个结果集直接循环读取，后面的结果集采用cmd.NextResult()先判断结果集是否存在再循环读取

 1 using System;
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 namespace _026读取多个结果集
 5 {
 6     class Program
 7     {
 8         static void Main(string[] args)
 9         {
10             string connString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=1001";
11             SqlConnection conn = new SqlConnection(connString);
12 
13             string sql = "select StudentId,StudentName from Students where Gender='男';select * from StudentClass";
14             SqlCommand cmd = new SqlCommand(sql,conn);
15 
16             conn.Open();
17             SqlDataReader reader = cmd.ExecuteReader();
18             Console.WriteLine("学生ID\t学生姓名\t");
19             while (reader.Read())
20             {
21                 Console.WriteLine($"{reader["StudentId"]}\t{reader["StudentName"]}\t");
22             }
23             //判断是否有下一个结果集
24             if (reader.NextResult())
25             {
26                 Console.WriteLine("------------------------------");
27                 Console.WriteLine($"班级ID\t班级名");
28                 while (reader.Read())
29                 {//继续读取
30                     Console.WriteLine($"{reader["ClassId"]}\t{reader["ClassName"]}");
31                 }
32             }
33             reader.Close();
34             conn.Close();
35             Console.ReadLine();
36         }
37     }
38 }

多条结果集查询

13.数据库操作基本方法总结

1. 创建Connection对象
2. 打开连接
3. 创建SQL语句(insert、update、delete、select)
4. 创建Command对象
5. 执行操作
6. 关闭连接

14.实现代码复用===>>>SQLHelper

　　14.1代码复用的基本形式：编写一个通用的方法

　　14.2代码复用技术

　　　　原则：提取不变的，封装变化的

　　　　技巧：不变的作为“方法体”，变化的作为方法“参数”

　　数据库连接很耗费时间，应避免反复打开与关闭数据库

 1 using System.Data;
 2 using System.Data.SqlClient;
 3 namespace UseSQlHelper
 4 {
 5     class SQLHelper
 6     {
 7         private static string ConnStr = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=1001";
 8 
 9         /// <summary>
10         /// 返回单一结果查询
11         /// </summary>
12         /// <param name="sql"></param>
13         /// <returns></returns>
14         public static object GetSignalResult(string sql)
15         {
16             SqlConnection conn = new SqlConnection(ConnStr);
17             SqlCommand cmd = new SqlCommand(sql,conn);
18             conn.Open();
19             object re = cmd.ExecuteScalar();
20             conn.Close();
21             return re;
22         }
23 
24         /// <summary>
25         /// 更新数据操作(删、增、改)
26         /// </summary>
27         /// <param name="sql"></param>
28         /// <returns></returns>
29         public static int GetUpdate(string sql)
30         {
31             SqlConnection conn = new SqlConnection(ConnStr);
32             SqlCommand cmd = new SqlCommand(sql,conn);
33             conn.Open();
34             int re = cmd.ExecuteNonQuery();
35             conn.Close();
36             return re;
37         }
38 
39         /// <summary>
40         /// 返回一个结果集的查询
41         /// </summary>
42         /// <param name="sql"></param>
43         /// <returns></returns>
44         public static SqlDataReader GerReader(string sql)
45         {
46             SqlConnection conn = new SqlConnection(ConnStr);
47             SqlCommand cmd = new SqlCommand(sql, conn);
48             conn.Open();
49             return cmd.ExecuteReader(CommandBehavior.CloseConnection);
50         }
51     }
52 }

SQLHelper V1.0

15.回顾面向对象的基本原则

　　单一职责原则(对象职责明确原则)

　　　　要求：一个对象只做好一件事情，必须专注，职责过多容易引起变化的原因就多，程序就不稳定(高内聚、低耦合的延伸)

　　开放封闭原则(核心原则)

　　　　要求：需求变化时尽量少的修改类的设计，而是通过扩展类 来完成，即封闭修改，开放扩展

　　依赖倒置原则(OOP精髓)

　　　　要求：基于接口编程，高层模块调用接口，底层模块实现接口，防止底层变化直接影响高层

　　接口隔离原则

　　　　要求：尽可能多的使用专用的小接口，而不是总接口，避免接口过于复杂

　　里氏替换原则

　　　　要求：在继承关系子类可以替换子类，虚拟机可以根据父类变量，动态的找到具体的子类对象

16.为什么需要数据访问类

　　16.1问题的引出

　　　　在界面类中包括界面操作代码、数据访问代码和其它代码，界面操作代码属于前台代码，数据访问代码属于后台代码

　　16.2前后台代码混编的缺点

　　　　程序编写人员必须非常熟悉后台数据的设计(即写C#界面的必须得会写SQL语句且对数据库设计很懂)

　　　　业务逻辑复杂时很难查找错误且不利于后期维护(即出现问题了，因为都写在一起，很乱，不知道时前台得Bug还是后台得Bug)

　　　　不合符面向对象得设计思想——>>>对象职责明确原则

　　16.3问题解决

　　　　将数据显示代码和数据访问代码分离

　　　　根据当前需要访问的后台实体，创建一个对应的数据访问类

　　　　(StudentService类，里面写添加学员方法、删除学员方法、修改学员信息方法、查询学员信息方法)

　　　　将对该实体操作的方法封装到对应的数据访问类中

17.对象职责明确原则总结

　　原则：分离“界面代码”和“数据访问代码”

　　好处：不管是WinForm还是Web，当数据发生变化时，数据访问部分一般不需要任何变化；同时前台设计人员和后台编写人员可以很好的分离

　　注意：写程序的时候，界面中不能出现任何SQL语句，数据访问后台代码中也不应该有其他业务逻辑代码

18.为什么需要实体类

　　18.1问题引入

　　　　如果仅使用数据访问类，会存在一个问题，方法属性很多的时候定义和使用不方便，打个比方，插入一条记录到数据库，需要十个属性，会导致界面类中定义的SQL语句很长，很容易写错，这样写的话其实后台方法的编写还是依赖数据库来完成

　　18.2问题解决思路

　　　　为数据访问类提供一个规范，稳定对象的接口；  不同开发人员只需要按照规范接口即可同步开发

　　18.3问题解决办法

　　　　使用“实体类”作为方法参数，稳定对外接口

　　　　用实体类对象替换多个方法参数

19.实体类的设计

　　概念与形式

　　　　只包含属性和构造方法的类称为实体类

　　　　实体类属性和数据库实体属性一一对应(字段名称和数据类型一致)

C#数据类型	数据库数据类型
实体类属性类型	数据库数据类型
string	char,nchar,varchar,nvarchar
int	int,smallint
DateTime	datetime,smalldatetime
float	float
bool	bit
decimal	decimal,money

 　　数据库有多少张表，一般就有几个实体类，实体类放在“Models”文件夹内

 20.实体类总结-1

　　20.1深入理解实体类

　　　　实体类(例如模块Models中的类Student)除了和数据表(数据库SMDB中的类Students)对应外，通常都有对应的数据访问类(即实体类Student对应数据访问类StudentService，在StudentService类中有添加、删除、修改学员和根据具体需求查询学员的具体方法)

　　　　实体类和对应的数据访问类，其实就是一个对象的属性和方法的分离，实体类对应属性，数据访问类对应方法，这种分离是为了更好的体现系统可维护性

　　　　实体类的使用使得程序开发人员可以完全脱离对数据库的依赖

　　　　界面开发人员和后台数据访问类的开发人员可以明确分工(一个人写前端，例如界面和实体类，另一个写后端，例如SQLHelper和各个数据访问类)

　　20.2实体类的主要作用

　　　　封装数据：将用户的输入数据或后台查询数据，封装为实体对象，简化接口

　　　　传递数据：在用户界面和数据访问类之间传递消息

　　20.3有关数据访问类和实体类的一些思考

　　　　以数据库中的学生表为例，在实体类中定义“Student”类，类中包含学生的各种属性，在数据访问类中定义“StudentService”类，类中包含添加、删除学员的方法，这个就是一个对象属性和方法的分离

　　　　在StudentService数据访问类中，需要using Models，因为方法的参数中需要引用Studentd封装好的一个对象；在界面层则需要引用using DAL和using Models，在界面层不仅要定义一个Student对象，还需要使用StudentService的方法

　　　　而与数据库通信的最底层SQLHelper类，只会在数据访问模块DAL中的StudentService类中被使用，sql语句也是在该类中定义

 21.对象属性的封装与解析

　　插入时，是封装一个对象，根据已设定好的属性值进行插入；

　　查询时，是解析出一个已经封装好的对象的属性。

　　好处：稳定了数据访问接口，明确职责

　　前后台人员可分离实现同步开发：

　　　　数据访问方法只关心如何封装对象，而不关心谁使用

　　　　界面数据展示部分只关心如何解析对象，而不关心如何查询并封装对象

　　　　实现面向对象开发：高内聚、低耦合、职责明确