|
|
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 ClrDemoNet40
|
|
|
{
|
|
|
/// <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
|
|
|
}
|
|
|
}
|