using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ClrDemoNet35;
using Xunit;
namespace ClrDemoTest
{
public class ClrDemoNet35
{
#region 存储过程
[Fact]
public void UseClrTest()
{
using (SqlConnection connection = new SqlConnection(CallSQL.SqlConnectionString))
{
connection.Open();
string sqlText = "Pro_ClrDemoNet35_UseClr";
SqlCommand command = new SqlCommand(sqlText, connection);
command.CommandType = CommandType.StoredProcedure;
var result = command.ExecuteScalar();
var cc = command.Notification;
connection.Close();
Assert.Null(result);
}
}
[Fact]
public void GetRandomTest()
{
using (SqlConnection connection = new SqlConnection(CallSQL.SqlConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand()
{
Connection = connection,
CommandType = CommandType.StoredProcedure,
CommandText = "Pro_ClrDemoNet35_GetRandom",
};
var result = command.ExecuteScalar();
connection.Close();
Assert.Null(result);
}
}
///
/// 能获取到方法(存储过程)返回值
/// 使用查询语句
///
[Fact]
public void GetRandom_HasReturn_Test()
{
using (SqlConnection connection = new SqlConnection(CallSQL.SqlConnectionString))
{
connection.Open();
string sqlText = @" DECLARE @return_value int;
EXEC @return_value = [dbo].[Pro_ClrDemoNet35_GetRandom];
SELECT @return_value AS Random;";
SqlCommand command = new SqlCommand(sqlText, connection);
command.CommandType = CommandType.Text;
var randomValue = (int)command.ExecuteScalar();
connection.Close();
Assert.InRange(randomValue, 0, 1000);
}
}
//
/// 能获取到方法(存储过程)返回值
/// 使用存储过程参数(Direction=ParameterDirection.ReturnValue)
///
[Fact]
public void GetRandom_ReturnValue_Test()
{
using (SqlConnection connection = new SqlConnection(CallSQL.SqlConnectionString))
{
connection.Open();
string sqlText = @"Pro_ClrDemoNet35_GetRandom";
SqlCommand command = new SqlCommand(sqlText, connection);
command.CommandType = CommandType.StoredProcedure;
var returnValuePara = new SqlParameter()
{
ParameterName = "@return",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.ReturnValue
};
command.Parameters.Add(returnValuePara);
command.ExecuteNonQuery();
connection.Close();
var randomValue = (int)returnValuePara.Value;
Assert.InRange(randomValue, 0, 1000);
}
}
///
/// 返回总数据行数
/// 接收 SqlContext.Pipe.ExecuteAndSend 发送的 SqlCommand 结果
///
[Fact]
public void ReturnCountTest()
{
using (SqlConnection connection = new SqlConnection(CallSQL.SqlConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand()
{
Connection = connection,
CommandType = CommandType.StoredProcedure,
CommandText = "Pro_ClrDemoNet35_ReturnCount",
};
var returnValuePara = new SqlParameter()
{
ParameterName = "@return",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.ReturnValue
};
command.Parameters.Add(returnValuePara);
var studentCount = (int)command.ExecuteScalar();
connection.Close();
Assert.True(studentCount >= 0);
}
}
///
/// 获取总数据行数,使用 out 参数,返回值。
///
[Fact]
public void GetCountTest()
{
using (SqlConnection connection = new SqlConnection(CallSQL.SqlConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand()
{
Connection = connection,
CommandType = CommandType.StoredProcedure,
CommandText = "Pro_ClrDemoNet35_GetCount",
};
var returnValuePara = new SqlParameter()
{
ParameterName = "@return",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.ReturnValue
};
command.Parameters.Add(returnValuePara);
SqlParameter outPara = new SqlParameter()
{
ParameterName = "@TotalCount",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.Output,
};
command.Parameters.Add(outPara);
command.ExecuteScalar();
connection.Close();
var outParaValue = (int)outPara.Value;
var returnParaValue = (int)returnValuePara.Value;
Assert.Equal(outParaValue, returnParaValue);
}
}
///
/// 查询数据表
/// 接收 SqlContext.Pipe.ExecuteAndSend 发送的 DataTable
///
[Fact]
public void GetStudentTest()
{
using (SqlConnection connection = new SqlConnection(CallSQL.SqlConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand()
{
Connection = connection,
CommandType = CommandType.StoredProcedure,
CommandText = "Pro_ClrDemoNet35_GetStudents",
};
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
da.Fill(dt);
//转换为实体类
List students = new List();
foreach (DataRow row in dt.Rows)
{
var student = new Student()
{
Id = (int)row["Id"],
Name = row["Name"] == DBNull.Value ? "" : (string)row["Name"],
SchoolId = (int)row["SchoolId"],
Age = (int)row["Age"],
};
students.Add(student);
}
connection.Close();
Assert.True(dt.Columns.Count > 0);
}
}
///
/// 返回自定义结果集
/// 使用 SendResultsStart SendResultsRow SendResultsEnd
///
[Fact]
public void UseSqlDataRecordTest()
{
using (SqlConnection connection = new SqlConnection(CallSQL.SqlConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand()
{
Connection = connection,
CommandType = CommandType.StoredProcedure,
CommandText = "Pro_ClrDemoNet35_UseSqlDataRecord",
};
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
da.Fill(dt);
//转换为实体类
var schools = new List();
foreach (DataRow row in dt.Rows)
{
var school = new School()
{
Id = (int)row["Id"],
SchoolName = row["SchoolName"] == DBNull.Value ? "" : (string)row["SchoolName"],
ClassNumber = (int)row["ClassNumber"],
StudentNumber = (int)row["StudentNumber"],
};
schools.Add(school);
}
connection.Close();
Assert.True(dt.Columns.Count > 0);
}
}
///
/// 返回自定义结果集
/// 使用 SendResultsStart SendResultsRow SendResultsEnd
///
[Fact]
public void UseSendResultsRowTest()
{
using (SqlConnection connection = new SqlConnection(CallSQL.SqlConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand()
{
Connection = connection,
CommandType = CommandType.StoredProcedure,
CommandText = "Pro_ClrDemoNet35_UseSendResultsRow",
};
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
da.Fill(dt);
//转换为实体类
var schools = new List();
foreach (DataRow row in dt.Rows)
{
var school = new School()
{
Id = (int)row["Id"],
SchoolName = row["SchoolName"] == DBNull.Value ? "" : (string)row["SchoolName"],
ClassNumber = (int)row["ClassNumber"],
StudentNumber = (int)row["StudentNumber"],
};
schools.Add(school);
}
connection.Close();
Assert.True(dt.Columns.Count > 0);
}
}
///
/// 汇总学校学生数量
/// 返回受影响的行数
///
[Fact]
public void SummaryDataTest()
{
using (SqlConnection connection = new SqlConnection(CallSQL.SqlConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand()
{
Connection = connection,
CommandType = CommandType.StoredProcedure,
CommandText = "Pro_ClrDemoNet35_SummaryData",
};
var affectedRows = command.ExecuteNonQuery();
connection.Close();
Assert.True(affectedRows >= 0);
}
}
///
/// 汇总学校学生数量
/// 返回执行结果(模拟复杂业务)
///
[Fact]
public void SummaryStudentCountTest()
{
using (SqlConnection connection = new SqlConnection(CallSQL.SqlConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand()
{
Connection = connection,
CommandType = CommandType.StoredProcedure,
CommandText = "Pro_ClrDemoNet35_SummaryStudentCount",
};
var dataReader = command.ExecuteReader();
bool sucess = false;
string message = "";
if (dataReader.Read())
{
sucess = dataReader.GetBoolean(0);
message = dataReader.GetString(1);
}
connection.Close();
Assert.True(sucess);
}
}
#endregion
#region 自定义函数
[Theory]
[InlineData(1)]
[InlineData(-1)]
public void QuerySchoolStudentTest(int schoolId)
{
using (SqlConnection connection = new SqlConnection(CallSQL.SqlConnectionString))
{
connection.Open();
string sqlText = $"SELECT [dbo].[Fun_ClrDemoNet35_QuerySchoolStudentCount] (@schoolId);";
SqlCommand command = new SqlCommand(sqlText, connection);
command.CommandType = CommandType.Text;
SqlParameter schoolIdPara = new SqlParameter()
{
ParameterName = "@schoolId",
Value = schoolId,
Direction = ParameterDirection.Input,
};
command.Parameters.Add(schoolIdPara);
var result = (int)command.ExecuteScalar();
connection.Close();
Assert.True(result >= 0);
}
}
///
/// 表值函数:返回一个数据集合,必须用一个填充数据的方法,在属性中用FillRowMethodName来表示,且返回值应该为IEnumerable类型
/// 查询指定学校的所有学生
///
[Fact]
public void QueryStudentTest()
{
using (SqlConnection connection = new SqlConnection(CallSQL.SqlConnectionString))
{
SqlCommand command = new SqlCommand()
{
Connection = connection,
CommandType = CommandType.Text,
CommandText = "SELECT * FROM dbo.Fun_ClrDemoNet35_QueryStudent();"
};
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
//执行操作数据库
connection.Open();
da.Fill(dt);
connection.Close();
//转换为实体类
var schools = new List();
foreach (DataRow row in dt.Rows)
{
var school = new School()
{
Id = (int)row["Id"],
SchoolName = row["SchoolName"] == DBNull.Value ? "" : (string)row["SchoolName"],
ClassNumber = (int)row["ClassNumber"],
StudentNumber = (int)row["StudentNumber"],
};
schools.Add(school);
}
Assert.True(schools.Count >= 0);
}
}
#endregion
}
}