一.视图（View）
1. 为什么要学习视图？
在没有视图之前，我们都是写各种各样的SQL语句，有点，非常灵活。后面我们学习应用程序开发的时候，通过C#发送过来的SQL语句
到达数据库的时候，会执行什么过程呢？
数据库接收到各种应用程序发送的SQL语句的时候，通常的流程是这样的：
【1】语法检查-->【2】优化（根据你的查询条件内容，和索引情况等，综合优化一下）-->【3】编译-->【4】执行
我们想，常见的查询，尤其是组合查询，能不能把查询固化到数据库服务器端，然后客户端使用的时候，只是调用一下呢？
当然可以，这个就是我们要学习的存储过程和视图。
视图其实就是一个查询，本身没有数据，就是把我们要查询的SQL语句，经过优化后经过编译存储到数据库服务器端。
视图我们本身也可以把它看成一个“没有数据的表”。
2.视图的创建与使用。这里提前创建了一个CourseManageDB数据库
use CourseManageDB go --普通查询 select CourseId,CourseName,CourseContent,ClassHour,Credit,Course.CategoryId from Course inner join CourseCategory on Course.CategoryId=CourseCategory.CategoryId; --把上述查询添加到视图中 if exists(select * from sysobjects where name=‘queryCourseInfo_view‘) drop view queryCourseInfo_view go --创建视图 create view queryCourseInfo_view as select CourseId,CourseName,CourseContent,ClassHour,Credit,Course.CategoryId from Course inner join CourseCategory on Course.CategoryId=CourseCategory.CategoryId; go --视图创建好后，我们可以把它看成表一样查询 select * from queryCourseInfo_view where CourseId>1005;
--实际开发中，可能会比较复杂:case when --创建一个复杂的视图 if exists(select * from sysobjects where name=‘queryCourseInfo_view2‘) drop view queryCourseInfo_view2 go create view queryCourseInfo_view2 as select Course.CategoryId,CourseId,CourseName,CourseContent,Credit,TeacherId,CategoryName,ClassHour=case when ClassHour>400 then 500 when ClassHour<400 then 400 else 300 end from Course inner join CourseCategory on Course.CategoryId=CourseCategory.CategoryId; go --使用视图 select * from queryCourseInfo_view2;
在C#中调用视图，视图的查询和查询表一样，把表名换成视图名称即可。
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Models; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace DAL { /// <summary> /// 通用数据访问类 /// </summary> [Serializable] public class SQLHelper { private static string connString = ConfigurationManager.ConnectionStrings["connString"].ToString(); /// <summary> /// 通用增删改方法 /// </summary> /// <param name="cmdText">查询语句</param> /// <param name="param">参数数组</param> /// <param name="IsProcedure">判断是否是存储过程</param> /// <returns>返回受影响的行数</returns> public static int Update(string cmdText, SqlParameter[] param = null, bool IsProcedure = false) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(cmdText, conn); try { conn.Open(); if (param != null) { cmd.Parameters.AddRange(param); } if (IsProcedure == true) { cmd.CommandType = CommandType.StoredProcedure; } return cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } } /// <summary> /// 启用事务来执行多条sql语句 /// </summary> /// <param name="listSql"></param> /// <returns></returns> public static bool UpdateByTrans(List<string> listSql) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; try { conn.Open(); cmd.Transaction = conn.BeginTransaction();//开启事务 foreach (string sql in listSql) { cmd.CommandText = sql; cmd.ExecuteNonQuery(); } cmd.Transaction.Commit();//提交事务（提交后会自动清除事务） return true; } catch (Exception ex) { if (cmd.Transaction != null) cmd.Transaction.Rollback();//执行某一条sql语句时出错，回滚事务（回滚事务后会自动清除事务） cmd.Transaction = null;//清楚事务 throw ex; } finally { //为了以防万一，最后加一条判断，清除事务 if (cmd.Transaction != null) cmd.Transaction = null; conn.Close(); } } /// <summary> ///查询单条数据 /// </summary> /// <param name="cmdText">查询语句</param> /// <param name="param">参数数组</param> /// <param name="IsProcedure">判断是否是存储过程</param> /// <returns>返回单条数据查询结果</returns> public static object GetSingleResult(string cmdText, SqlParameter[] param = null, bool IsProcedure = false) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(cmdText, conn); try { conn.Open(); if (param != null) { cmd.Parameters.AddRange(param); } if (IsProcedure == true) { cmd.CommandType = CommandType.StoredProcedure; } return cmd.ExecuteScalar();//可以执行一个增删改语句的同时再执行一个查询语句（比如添加，再查询添加的标识列） } catch (Exception) { throw; } finally { conn.Close(); } } /// <summary> ///返回查询结果集 /// </summary> /// <param name="cmdText">查询语句</param> /// <param name="param">参数数组</param> /// <param name="IsProcedure">判断是否是存储过程</param> /// <returns>返回查询结果集</returns> public static SqlDataReader GetReader(string cmdText, SqlParameter[] param = null, bool IsProcedure = false) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(cmdText, conn); try { conn.Open(); if (param != null) { cmd.Parameters.AddRange(param); } if (IsProcedure == true) { cmd.CommandType = CommandType.StoredProcedure; } return cmd.ExecuteReader(CommandBehavior.CloseConnection);//reader关闭，conn也将关闭（conn关闭，reader将读取不到数据，会报错） } catch (Exception) { throw; } } /// <summary> /// 基于DataSet来实现数据的查询(视图的操作类似于表) /// </summary> /// <param name="cmdText">查询语句或者存储过程</param> /// <param name="param">参数数组</param> /// <param name="IsProCedure">是否是存储过程</param> /// <param name="tableName">给表起的名</param> /// <returns>返回查询到的表数据</returns> public static DataSet GetTableData(string cmdText, bool IsProCedure = false, string tableName = null, SqlParameter[] param = null) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(cmdText, conn); try { conn.Open(); if (param != null) { cmd.Parameters.AddRange(param); } if (IsProCedure == true) { cmd.CommandType = CommandType.StoredProcedure; } //创建数据适配器 SqlDataAdapter da = new SqlDataAdapter(cmd); //创建数据集对象 DataSet ds = new DataSet(); if (tableName != null) { da.Fill(ds, tableName); } else { da.Fill(ds); } return ds; } catch (Exception) { throw; } } /// <summary> /// 获取系统时间 /// </summary> /// <returns></returns> public static DateTime GetTime() { return Convert.ToDateTime(SQLHelper.GetSingleResult("select getdate()")); } } }
/// <summary> /// 基于DataSet来实现数据的查询(视图的操作类似于表) /// </summary> /// <param name="cmdText">查询语句或者存储过程</param> /// <param name="param">参数数组</param> /// <param name="IsProCedure">是否是存储过程</param> /// <param name="tableName">给表起的名</param> /// <returns>返回查询到的表数据</returns> public static DataSet GetTableData(string cmdText, bool IsProCedure = false, string tableName = null, SqlParameter[] param = null) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(cmdText, conn); try { conn.Open(); if (param != null) { cmd.Parameters.AddRange(param); } if (IsProCedure == true) { cmd.CommandType = CommandType.StoredProcedure; } //创建数据适配器 SqlDataAdapter da = new SqlDataAdapter(cmd); //创建数据集对象 DataSet ds = new DataSet(); if (tableName != null) { da.Fill(ds, tableName); } else { da.Fill(ds); } return ds; } catch (Exception) { throw; } }
/// <summary> /// 通过存储过程来查询班级或者全校成绩 /// </summary> /// <param name="tableName">表名</param> /// <returns></returns> public DataSet GetScoreList(string tableName = null) { //视图可以看成一张表，直接像sql语句一样用，直接查询 string cmdText = "select * from view_GetScoreList"; return SQLHelper.GetTableData(cmdText,false,tableName); }
3. 视图和普通的SQL语句查询比较
【1】视图本身就是一个提前写好的查询。但是这个查询保存到数据库服务器端。应用程序通过调用可以直接执行。
【2】普通的SQL语句查询。它在应用程序端。要执行，需要通过程序把SQL语句发送到数据库服务器端。
【3】后期的查询维护不同。如果我们要通过应用程序发送SQL语句，你必须要修改程序。但是如果我们使用视图呢？
可以适当的直接在数据库服务器上修改。
4. 视图本身的特点：本身不保存任何数据，只是保存到服务器端的一段编译好的SQL语句而已，并且已经优化好和编译好。
5. 关于使用建议：我们在学习阶段，必须会使用脚本写视图。如果是正式开发阶段，我们可以适当的使用可视化方式创建视图。
创建完毕后，把SQL语句优化好，再复制到你要创建的视图中。这个仅仅是为了省时间。必要的时候可以使用。
6. 视图还有一个非常重要的功能：比如我们在项目中会根据不同的角色，做不同的数据查询，这时候，我们在必要的时候完全可以
针对不同的用户角色，设计不同的视图，保证数据的安全。
二.存储过程
1. 概念：存储过程就是编写到数据库服务器端的SQL语句，存储过程的内容不仅仅是可以实现查询，也可以实现CRUD同时操作。
使用选择：如果仅仅是查询，建议你使用视图。尤其是针对不同的角色需要调用不同的视图。这时候视图是非常方便的。
2. 分类：系统存储过程（请大家自己学习一下，知道即可，可以随时查询）-->系统给我们提前定义好的，我们可以直接使用。
用户自定义存储过程（我们主要用的）
3. 好处：
【1】执行效率高。已经编译好，并保存到服务器端。只需要调用，必要的时候传递参数即可。
减轻客户端的压力。
【2】安全型好。客户端调用，只需要发送指令即可。数据安全有保障。
【3】维护方便。如果后续有改动，可以直接在服务器端修改即可，而客户端程序不用修改。
SQLSever存储过程
带输入参数的存储过程：
--存储过程编写 if exists(select * from sysobjects where name=‘Course__Procedure1‘) drop procedure Course__Procedure1 go --创建存储过程 create procedure Course__Procedure1 --定义传入参数 --@CourseId int, @CourseName nvarchar(50), @CourseContent nvarchar(500), @ClassHour int, @Credit int, @CategoryId int, @TeacherId int as ---在这里编写具体的存储过程内容 insert into Course(CourseName,CourseContent,ClassHour,Credit,CategoryId,TeacherId) values(@CourseName,@CourseContent,@ClassHour,@Credit,@CategoryId,@TeacherId); go --调用存储过程 exec Course__Procedure1 ‘opencv从入门到放弃‘,‘opencv从入门到高级教程‘,20,3,10,10 --查询 select * from Course where CourseName=‘opencv从入门到放弃‘ ;
带参数默认值的存储过程
-编写带参数默认值的存储过程 if exists(select * from sysobjects where name=‘Course__Procedure2‘) drop procedure Course__Procedure2 go create procedure Course__Procedure2 ---添加存储过程参数 @courseName varchar(50)=‘opencv高级教程‘, @courseContent varchar(500)=‘opencv高级教程，包你学会不放弃‘, @ClassHour int, @Credit int, @CategoryId int, @TeacherId int as ---在这里添加具体的存储过程内容 insert into Course(CourseName,CourseContent,ClassHour,Credit,CategoryId,TeacherId) values(@CourseName,@CourseContent,@ClassHour,@Credit,@CategoryId,@TeacherId); go --调用带默认参数值的存储过程,前两个参数使用默认值 exec Course__Procedure2 @ClassHour=10,@Credit=5,@CategoryId=12,@TeacherId=20 --调用带默认参数值的存储过程参数，第二个参数使用默认值 exec Course__Procedure2 @courseName=‘opencv图像处理教程‘,@ClassHour=10,@Credit=5,@CategoryId=12,@TeacherId=20 select * from Course where courseName=‘opencv图像处理教程‘
带输出参数的存储过程，使用output修饰
--输出参数的存储过程：使用output if exists(select * from sysobjects where name=‘Course__Procedure3‘) drop procedure Course__Procedure3 go create procedure Course__Procedure3 ---定义输出参数 @Count int output as --编写具体存储过程内容 select @Count= Count(*) from Course; go --调用存储过程，声明变量用declare declare @Count int --首先定义输出参数 exec Course__Procedure3 @Count output --使用参数 select 商品总数=@Count
if exists(select * from sysobjects where name=‘select_usp_course1‘) drop procedure select_usp_course1 go create procedure select_usp_course1 --定义输入输出参数 @CountNum int output, ---定义输出参数 @CourseName nvarchar(50), --@CourseContent nvarchar(500), @CategoryId int --@TeacherId int, --@ClassHour int=1000,---带默认参数 --@Credit int=100---带默认值 as select * from Course where Coursename=@CourseName; select * from CourseCategory where CategoryId=@CategoryId; select @CountNum=COUNT(*) from Course; go declare @CountNum int ;--声明输出变量 exec select_usp_course1 @CountNum output,‘C#入门‘,12
在C#中调用写好的存储过程
#region 学员成绩管理 /// <summary> /// 通过存储过程实现查询全校考试信息（通过存储过程） /// </summary> /// <returns>返回一个字典集合</returns> public Dictionary<string, string> QueryExamInfo() { string cmdText = "pro_QueryExamInfo"; //定义输出参数，参数和数据库存储过程参数一致 SqlParameter outparam1 = new SqlParameter("@stuCount", SqlDbType.Int); SqlParameter outparam2 = new SqlParameter("@agvCsharp", SqlDbType.Float); SqlParameter outparam3 = new SqlParameter("@agvServerDB", SqlDbType.Float); SqlParameter outparam4 = new SqlParameter("@absenceCount", SqlDbType.Int); outparam1.Direction = ParameterDirection.Output; outparam2.Direction = ParameterDirection.Output; outparam3.Direction = ParameterDirection.Output; outparam4.Direction = ParameterDirection.Output; SqlParameter[] outparam = new SqlParameter[] { outparam1, outparam2, outparam3, outparam4 }; SqlDataReader reader = SQLHelper.GetReader(cmdText, outparam, true); Dictionary<string, string> examInfoDic = null; //获取参考学生人数，平均成绩 if (reader.Read()) { examInfoDic = new Dictionary<string, string>() { ["stuCount"] = reader["stuCount"].ToString(), ["agvCsharp"] = reader["agvCsharp"].ToString(), ["agvServerDB"] = reader["agvServerDB"].ToString(), ["absenceCount"] = reader["absenceCount"].ToString() }; } reader.Close(); return examInfoDic; } public List<string> QueryAllAbsenceStudent() { string cmdText = "pro_QueryAllAbsenceStudent"; List<string> stuNameList = new List<string>(); SqlDataReader reader = SQLHelper.GetReader(cmdText); while (reader.Read()) { stuNameList.Add(reader["StudentName"].ToString()); } reader.Close(); return stuNameList; } /// <summary> /// 按照班级查询考试信息 /// </summary> /// <param name="ClassName"></param> /// <returns></returns> public Dictionary<string, string> QueryAbsenceInfoById(int ClassId) { string cmdText = "pro_QueryAbsenceInfoByClassId"; //定义输入参数 SqlParameter inputparam0 = new SqlParameter("@ClassId", ClassId); //定义输出参数 SqlParameter outparam1 = new SqlParameter("@stuCount",SqlDbType.Int); SqlParameter outparam2 = new SqlParameter("@agvCsharp",SqlDbType.Float); SqlParameter outparam3 = new SqlParameter("@agvServerDB",SqlDbType.Float); SqlParameter outparam4 = new SqlParameter("@absenceCount",SqlDbType.Int); inputparam0.Direction = ParameterDirection.Input; outparam1.Direction = ParameterDirection.Output; outparam2.Direction = ParameterDirection.Output; outparam3.Direction = ParameterDirection.Output; outparam4.Direction = ParameterDirection.Output; SqlParameter[] param = new SqlParameter[] { inputparam0, outparam1, outparam2, outparam3, outparam4 }; SqlDataReader reader = SQLHelper.GetReader(cmdText, param, true); Dictionary<string, string> examInfoDic = null; //获取参考学生人数，平均成绩 if (reader.Read()) { examInfoDic = new Dictionary<string, string>() { ["stuCount"] = reader["stuCount"].ToString(), ["agvCsharp"] = reader["agvCsharp"].ToString(), ["agvServerDB"] = reader["agvServerDB"].ToString(), ["absenceCount"] = reader["absenceCount"].ToString() }; } reader.Close(); return examInfoDic; } /// <summary> /// 按照班级查询缺考人员 /// </summary> /// <param name="ClassName"></param> /// <returns></returns> public List<String> QueryAbsenceStudents(int ClassId) { string cmdText = "pro_QueryClassExamInfo"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@ClassId",ClassId) }; SqlDataReader reader = SQLHelper.GetReader(cmdText,param,true); List<string> StuNamelist = new List<string>(); while (reader.Read()) { StuNamelist.Add(reader["StudentName"].ToString()); } reader.Close(); return StuNamelist; } #endregion