using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.Common;
using System.Collections;

using Microsoft.SqlServer.Server;

namespace ClrDemoNet45
{
    /// <summary>
    /// SQL Server 调用 CLR 示例程序集
    /// 注意：1、方法必须是公共静态方法，
    ///       2、返回值必须是 SqlInt32、System.Int32、System.Nullable<System.Int32> 或 void
    ///       3、加上SqlProcedure等特性
    /// </summary>
    public class ClrDemo
    {
        #region 存储过程
        /// <summary>
        /// 向调用客户端发送文本
        /// </summary>
        [SqlProcedure]
        public static void UseClr()
        {
            SqlContext.Pipe.Send("Hello,SQL Server CLR!");
        }

        /// <summary>
        /// 获取一个随机整数
        /// 注意：方法的返回值，似乎不起使用
        /// 返回值：方法一是使用输出参数 方法二是使用 SqlContext.Pipe.Send
        /// </summary>
        [SqlProcedure]
        public static int GetRandom()
        {
            Random random = new Random();

            return random.Next(0, 1000);
        }

        /// <summary>
        /// 返回总数据行数
        /// 使用 SqlContext.Pipe.ExecuteAndSend 直接发送 SqlCommand 结果到客户端(存储过程结果)
        /// </summary>
        /// <returns></returns>
        [SqlProcedure]
        public static void ReturnCount()
        {
            using (SqlConnection conn = new SqlConnection("Context Connection=true"))
            {
                conn.Open();
                string sqlText = "SELECT ISNULL(COUNT(*),0) AS StudentCount FROM dbo.Student;";

                SqlCommand command = new SqlCommand(sqlText, conn);
                command.CommandType = CommandType.Text;
                SqlContext.Pipe.ExecuteAndSend(command);
                conn.Close();
            }
        }

        /// <summary>
        /// 获取总数据行数
        /// 使用 out 参数，返回值。
        /// </summary>
        [SqlProcedure]
        public static int GetCount(out int totalCount)
        {
            using (SqlConnection conn = new SqlConnection("Context Connection=true"))
            {
                conn.Open();
                string sqlText = "SELECT ISNULL(COUNT(*),0) AS StudentCount FROM dbo.Student;";

                SqlCommand command = new SqlCommand(sqlText, conn);
                command.CommandType = CommandType.Text;

                totalCount = (int)command.ExecuteScalar();
                conn.Close();

                return totalCount;
            }
        }

        /// <summary>
        /// 查询数据表
        /// SqlContext.Pipe.ExecuteAndSend 发送DataTable
        /// </summary>
        [SqlProcedure]
        public static void GetStudents()
        {
            using (SqlConnection conn = new SqlConnection("Context Connection=true"))
            {
                conn.Open();
                string sqlText = "SELECT * FROM dbo.Student;";

                SqlCommand command = new SqlCommand(sqlText, conn);
                command.CommandType = CommandType.Text;

                SqlContext.Pipe.ExecuteAndSend(command);
                conn.Close();
            }
        }

        /// <summary>
        /// 使用 SqlDataRecord 发送单行数据
        /// </summary>
        [SqlProcedure]
        public static void UseSqlDataRecord()
        {
            //创建 metadata 作为 SqlDataRecord 列
            List<SqlMetaData> columns = new List<SqlMetaData>()
            {
                new SqlMetaData("Id", SqlDbType.Int),
                new SqlMetaData("SchoolName", SqlDbType.NVarChar, 50),
                new SqlMetaData("ClassNumber", SqlDbType.Int),
                new SqlMetaData("StudentNumber", SqlDbType.Int)
            };

            SqlDataRecord record = new SqlDataRecord(columns.ToArray());

            // 设置列的值  
            record.SetInt32(0, -1);
            record.SetString(1, "太康七高");
            record.SetInt32(2, 20);
            record.SetInt32(3, 55);

            // 发送到客户端
            SqlContext.Pipe.Send(record);
        }

        /// <summary>
        /// 返回自定义结果集
        /// 使用 SendResultsStart SendResultsRow SendResultsEnd
        /// </summary>
        [SqlProcedure]
        public static void UseSendResultsRow()
        {
            //创建 metadata 作为 SqlDataRecord 列
            List<SqlMetaData> columns = new List<SqlMetaData>()
            {
                new SqlMetaData("Id", SqlDbType.Int),
                new SqlMetaData("SchoolName", SqlDbType.NVarChar, 50),
                new SqlMetaData("ClassNumber", SqlDbType.Int),
                new SqlMetaData("StudentNumber", SqlDbType.Int)
            };

            SqlDataRecord record = new SqlDataRecord(columns.ToArray());

            //开始发送
            SqlContext.Pipe.SendResultsStart(record);

            // 发送10行数据到客户端
            for (int i = 1; i <= 10; i++)
            {
                // 设置列的值  
                record.SetInt32(0, i);
                record.SetString(1, $"太康{i}高");
                record.SetInt32(2, 20 + i);
                record.SetInt32(3, 100 + i);

                // 发送当前行到客户端
                SqlContext.Pipe.SendResultsRow(record);
            }

            // 发送结束
            SqlContext.Pipe.SendResultsEnd();
        }

        /// <summary>
        /// 汇总学校学生数量
        /// </summary>
        [SqlProcedure]
        public static void SummaryData()
        {
            using (SqlConnection conn = new SqlConnection("Context Connection=true"))
            {
                conn.Open();
                string sqlText = "UPDATE dbo.School SET StudentNumber=(SELECT COUNT(*) FROM dbo.Student WHERE SchoolId = School.Id);";

                SqlCommand command = new SqlCommand(sqlText, conn);
                command.CommandType = CommandType.Text;

                SqlContext.Pipe.ExecuteAndSend(command);
                conn.Close();
            }
        }

        /// <summary>
        /// 汇总学生数量
        /// (只为演示效果，实际开发一条语句即可)
        /// </summary>
        [SqlProcedure]
        public static void SummaryStudentCount()
        {
            bool isSucess = true;
            string message = "ok";

            try
            {
                using (SqlConnection conn = new SqlConnection("Context Connection=true"))
                {
                    conn.Open();
                    //1、拉所有学校
                    SqlDataAdapter da = new SqlDataAdapter("SELECT Id FROM dbo.School;", conn);
                    DataTable schoolTable = new DataTable();
                    da.Fill(schoolTable);

                    foreach (DataRow row in schoolTable.Rows)
                    {
                        var schoolId = (int)row["Id"];
                        var studentCount = 0;

                        using (SqlCommand queryStudentCountCommand = new SqlCommand())
                        {
                            //查询该学校学生人数
                            string queryStudentCountText = "SELECT ISNULL(COUNT(*),0) AS StudentCount  FROM dbo.Student where [SchoolId]=@SchoolId;";

                            SqlParameter schoolIdPara = new SqlParameter()
                            {
                                ParameterName = "@SchoolId",
                                Direction = ParameterDirection.Input,
                                SqlDbType = SqlDbType.Int,
                                Value = schoolId,
                            };

                            queryStudentCountCommand.Connection = conn;
                            queryStudentCountCommand.CommandText = queryStudentCountText;
                            queryStudentCountCommand.CommandType = CommandType.Text;

                            queryStudentCountCommand.Parameters.Add(schoolIdPara);

                            studentCount = (int)queryStudentCountCommand.ExecuteScalar();
                        }

                        //更新
                        using (SqlCommand updateStudentCountCommand = new SqlCommand())
                        {
                            string updateStudentCountText = "UPDATE dbo.School SET StudentNumber=@StudentNumber WHERE Id=@SchoolId;";

                            SqlParameter updateSchoolIdPara = new SqlParameter()
                            {
                                ParameterName = "@SchoolId",
                                Direction = ParameterDirection.Input,
                                SqlDbType = SqlDbType.Int,
                                Value = schoolId,
                            };

                            SqlParameter updateStudentCountPara = new SqlParameter()
                            {
                                ParameterName = "@StudentNumber",
                                Direction = ParameterDirection.Input,
                                SqlDbType = SqlDbType.Int,
                                Value = studentCount,
                            };

                            updateStudentCountCommand.Connection = conn;
                            updateStudentCountCommand.CommandText = updateStudentCountText;
                            updateStudentCountCommand.CommandType = CommandType.Text;

                            updateStudentCountCommand.Parameters.Add(updateSchoolIdPara);
                            updateStudentCountCommand.Parameters.Add(updateStudentCountPara);

                            updateStudentCountCommand.ExecuteNonQuery();
                        }

                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                isSucess = false;
                message = ex.Message;
            }

            List<SqlMetaData> columns = new List<SqlMetaData>()
            {
                new SqlMetaData("Sucess", SqlDbType.Bit),
                new SqlMetaData("Message", SqlDbType.NVarChar, 500),
            };

            SqlDataRecord record = new SqlDataRecord(columns.ToArray());
            record.SetBoolean(0, isSucess);
            record.SetString(1, message);

            SqlContext.Pipe.Send(record);
        }
        #endregion

        #region 自定义函数
        /// <summary>
        /// 标量函数
        /// 查询指定学校的学生数量
        /// </summary>
        [SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = false)]
        public static int QuerySchoolStudentCount(int schoolId)
        {
            using (SqlConnection conn = new SqlConnection("Context Connection=true"))
            {
                conn.Open();
                string sqlText = "SELECT ISNULL(COUNT(*),0) AS StudentCount  FROM dbo.Student where [SchoolId]=@SchoolId;";

                SqlParameter schoolIdPara = new SqlParameter()
                {
                    ParameterName = "@SchoolId",
                    Direction = ParameterDirection.Input,
                    SqlDbType = SqlDbType.Int,
                    Value = schoolId,
                };

                SqlCommand command = new SqlCommand()
                {
                    Connection = conn,
                    CommandText = sqlText,
                    CommandType = CommandType.Text,
                };

                command.Parameters.Add(schoolIdPara);

                var studentCount = (int)command.ExecuteScalar();

                conn.Close();

                return studentCount;
            }
        }

        /// <summary>
        /// 表值函数:返回一个数据集合，必须用一个填充数据的方法，在属性中用FillRowMethodName来表示，且返回值应该为IEnumerable类型
        /// 查询指定学校的所有学生
        /// </summary>
        [SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillRow_ReturnData", IsDeterministic = false)]
        public static IEnumerable QueryStudent()
        {
            IList returnDataList = new List<School>();
            returnDataList.Add(new School() { Id = 1, SchoolName = "太康一高", ClassNumber = 10, StudentNumber = 150 });
            returnDataList.Add(new School() { Id = 2, SchoolName = "太康二高", ClassNumber = 20, StudentNumber = 250 });
            returnDataList.Add(new School() { Id = 3, SchoolName = "太康三高", ClassNumber = 30, StudentNumber = 350 });
            return returnDataList;
        }

        /// <summary>
        /// 填充方法
        /// </summary>
        /// <param name="returnDataObj"></param>
        /// <param name="name"></param>
        /// <param name="password"></param>
        public static void FillRow_ReturnData(object returnDataObj, out SqlInt32 Id, out SqlString SchoolName, out SqlInt32 ClassNumber, out SqlInt32 StudentNumber)
        {
            School school = returnDataObj as School;
            Id = school.Id;
            SchoolName = school.SchoolName;
            ClassNumber = school.ClassNumber;
            StudentNumber = school.StudentNumber;
        }

        /// <summary>
        /// 聚合函数
        /// 查询指定学校的所有学生
        /// </summary>
        [SqlFunction]
        public static void QuaryAvgStudent(int schoolId)
        {
            using (SqlConnection conn = new SqlConnection("Context Connection=true"))
            {
                conn.Open();
                string sqlText = "SELECT ISNULL(COUNT(*),0) AS StudentCount  FROM dbo.Student where [SchoolId]=@SchoolId;";

                SqlParameter schoolIdPara = new SqlParameter()
                {
                    ParameterName = "@SchoolId",
                    Direction = ParameterDirection.Input,
                    SqlDbType = SqlDbType.Int,
                    Value = schoolId,
                };

                SqlCommand command = new SqlCommand()
                {
                    Connection = conn,
                    CommandText = sqlText,
                    CommandType = CommandType.Text,
                };

                command.Parameters.Add(schoolIdPara);

                SqlContext.Pipe.ExecuteAndSend(command);
                conn.Close();
            }
        }
        #endregion
    }
}