ADO.NET高效数据访问

最新推荐文章于 2024-07-19 17:59:24 发布

Neter_yx

最新推荐文章于 2024-07-19 17:59:24 发布

阅读量85

点赞数

本文链接：https://blog.csdn.net/weixin_42528820/article/details/116129060

版权

使用ADO.NET执行带参数的SQL语句

1.使用ADO.NET执行带参数SQL语句

存储过程综合查询的编写

存储过程综合运用

使用存储过程实现全校考试信息的统计

USE SMDB 
GO
IF
	EXISTS ( SELECT * FROM sysobjects WHERE name = 'usp_ScoreQuery' ) DROP PROCEDURE usp_ScoreQuery 
GO
	CREATE PROCEDURE usp_ScoreQuery @className VARCHAR ( 20 ),
	@stuCount INT OUTPUT,
	@absentCount INT OUTPUT,
	@avgDB INT OUTPUT,
	@avgCSharp INT OUTPUT AS
IF
	( LEN( @className ) = 0 ) BEGIN-- Query All School
	SELECT
		m.StudentId,m.StudentName,n.ClassName,SQLServerDB,CSharp 
	FROM Students m
		INNER JOIN StudentClass n ON m.ClassId= n.ClassId
		INNER JOIN ScoreList l ON m.StudentId= l.StudentId 
        
        --查询考试统计信息
        
        select @stuCount=COUNT(*),@avgDB=AVG(SQLServerDB),
        @avgCSharp=AVG(CSharp) from ScoreList
        
        select @absentCount=COUNT(*) from Students 
        where StudentId not in
        (select StudentId from ScoreList)

        --查询没有参加考试的学生姓名集合
        select StudentName from Students where StudentId 
        not in(select StudentId from ScoreList)

	END 
    ELSE 

    BEGIN
	SELECT
		m.StudentId,m.StudentName,	n.ClassName,SQLServerDB,CSharp 
	FROM
		Students m
		INNER JOIN StudentClass n ON m.ClassId= n.ClassId
		INNER JOIN ScoreList l ON m.StudentId= l.StudentId 
	WHERE
		ClassName =@className 
               
               
        --查询考试统计信息        
        select @stuCount=COUNT(*),@avgDB=AVG(SQLServerDB),
        @avgCSharp=AVG(CSharp) from ScoreList 
        
        select @absentCount=COUNT(*) from Students 
        inner join StudentClass on StudentClass.ClassId=Students.ClassId
        where Students.StudentId not in
        (select StudentId from ScoreList) and ClassName =@className 

        --查询没有参加考试的学生姓名集合
        select StudentName from Students 
        inner join StudentClass on StudentClass.ClassId=Students.ClassId
        where Students.StudentId 
        not in(select StudentId from ScoreList)
        and ClassName =@className 
END 
GO

数据访问类编写1

        //通用数据访问类
        #region 调用存储过程执行多结果查询
        public static SqlDataReader GetReader(string procedureName, SqlParameter[] parameters) 
        {
            string constr = "";
            SqlConnection connection = new SqlConnection(constr);
            SqlCommand command = new SqlCommand();
            try
            {
                connection.Open();
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;//设置当前操作是执行存储过程
                command.CommandText = procedureName;//设置存储过程名称
                command.Parameters.Add(parameters);//添加存储过程参数数组
                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                connection.Close();
                throw ex;
            }
        }
        #endregion