SQLSever视图和存储过程

一.视图（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