public void ResultCheckerTest02()
{
var excelFile = Util.GetLocalFileFullPath("TestData.xlsx");
// import Data
var dt = SSDTHelper.ExcelReader.Read(excelFile, "DataTypeAndFormatPattern");
var loader = new SSDTHelper.DataLoader();
loader.ConnectionString = Config.ConnectionString;
loader.Load(dt);
using (var cn = new System.Data.SqlClient.SqlConnection(Config.ConnectionString))
{
cn.Open();
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM DataTypeAndFormatPattern ORDER BY ID";
cmd.CommandType = CommandType.Text;
using (var dr = cmd.ExecuteReader())
{
var sheetName = "ResultCheckSheet";
string message;
var ismatch = SSDTHelper.ResultCheker.IsMatch(dr, excelFile, sheetName, out message);
Assert.AreEqual(true, ismatch, message);
}
}
}
}
public void spCalculateMonthlySales_Execute()
{
var excelFile = Util.GetLocalFileFullPath("spCalculateMonthlySalesTest.xlsx");
// init data
var dt = SSDTHelper.ExcelReader.Read(excelFile, "DailySales");
var loader = new SSDTHelper.DataLoader();
loader.ConnectionString = Config.ConnectionString;
loader.Load(dt);
using (var cn = new SqlConnection(Config.ConnectionString))
{
cn.Open();
// execute
var param = new {
year = 2017,
month = 1
};
cn.Execute("spCalculateMonthlySales", param: param, commandType: System.Data.CommandType.StoredProcedure);
// read result
var resultset = cn.Query("SELECT * FROM MonthlySales ORDER BY ShopID, SalesMonth");
// check result set
var message = "";
var ismatch = SSDTHelper.ResultCheker.IsMatch(resultset, excelFile, "ResultCheckSheet", out message);
Assert.AreEqual(true, ismatch, message);
}
}
public void CompareTest2()
{
var dt = SSDTHelper.ExcelReader.Read(Util.GetLocalFileFullPath("TestData.xlsx"), new string[] { "People", "Salary" });
var loader = new SSDTHelper.DataLoader();
loader.ConnectionString = Config.ConnectionString;
loader.Load(dt, true);
using (var cn = new System.Data.SqlClient.SqlConnection(Config.ConnectionString))
{
cn.Open();
var result = cn.Query(@"SELECT Id , NAME, Age FROM People ORDER BY ID");
string message;
var ismatch = SSDTHelper.DataComparer.IsMatch(dt[0], result, out message);
Assert.AreEqual(true, ismatch, message);
var result2 = cn.Query(@"SELECT * FROM Salary ORDER BY ID");
string message2;
var ismatch2 = SSDTHelper.DataComparer.IsMatch(dt[1], result2, out message2);
Assert.AreEqual(true, ismatch2, message2);
}
}
public void Load_SingleTable()
{
var dt = SSDTHelper.ExcelReader.Read(Util.GetLocalFileFullPath("TestData.xlsx"), "People");
var loader = new SSDTHelper.DataLoader();
loader.ConnectionString = Config.ConnectionString;
loader.Load(dt);
using (var cn = new System.Data.SqlClient.SqlConnection(Config.ConnectionString))
{
cn.Open();
var sql = @"SELECT * FROM People ORDER BY ID";
var result = cn.Query(sql).ToList();
if (result.Count != 3)
{
Assert.True(false, $"3行あるはずが{result.Count}行しかない");
return;
}
if (result[0].Id != 1 || result[0].Name != "Hoge" || result[0].Age != 20)
{
Assert.True(false, $"1行目のデータが何か違う");
return;
}
if (result[1].Id != 2 || result[1].Name != "Fuga" || result[1].Age != 99)
{
Assert.True(false, $"2行目のデータが何か違う");
return;
}
if (result[2].Id != 3 || result[2].Name != "FugaFuga" || result[2].Age != 0)
{
Assert.True(false, $"3行目のデータが何か違う");
return;
}
Assert.True(true);
}
}