void ResetTable(SqlConnection connection)
{
ScriptExecutor exe = new ScriptExecutor();
int c = (int)new SqlCommand(@"use itiSchoolDB SELECT count(*) as IsExists FROM dbo.sysobjects where id = object_id('Classroom')", connection).ExecuteScalar();
if (c > 0)
{
new SqlCommand("DROP TABLE Classroom", connection).ExecuteScalar();
}
int s = (int)new SqlCommand(@"use itiSchoolDB SELECT count(*) as IsExists FROM dbo.sysobjects where id = object_id('Student')", connection).ExecuteScalar();
if (s > 0)
{
new SqlCommand("DROP TABLE Student", connection).ExecuteScalar();
}
int t = (int)new SqlCommand(@"use itiSchoolDB SELECT count(*) as IsExists FROM dbo.sysobjects where id = object_id('Teacher')", connection).ExecuteScalar();
if (t > 0)
{
new SqlCommand("DROP TABLE Teacher", connection).ExecuteScalar();
}
exe.ScriptExe(DbInit.CreateTableTeacher(), connection);
exe.ScriptExe(DbInsert.InsertTeacherTable(), connection);
exe.ScriptExe(DbInit.CreateTableStudent(), connection);
exe.ScriptExe(DbInsert.InsertStudentTable(), connection);
exe.ScriptExe(DbInit.CreateTableClassroom(), connection);
exe.ScriptExe(DbInsert.InsertClassroomTable(), connection);
}
public void T5_check_if_tables_are_filled()
{
using (SqlConnection connection = new SqlConnection(DbInit.GetConnectionString()))
{
connection.Open();
ResetTables(connection);
ScriptExecutor exe = new ScriptExecutor();
exe.ScriptExe(DbInsert.InsertTeacherTable(), connection);
exe.ScriptExe(DbInsert.InsertStudentTable(), connection);
exe.ScriptExe(DbInsert.InsertClassroomTable(), connection);
DataTable datatableStudent = new DataTable();
DataTable datatableTeacher = new DataTable();
DataTable datatableClassroom = new DataTable();
string teachers = "select count(*) from Teacher";
string students = "select count(*) from Student";
string classrooms = "select count(*) from Classroom";
SqlCommand cmd1 = new SqlCommand(teachers, connection);
SqlCommand cmd2 = new SqlCommand(students, connection);
SqlCommand cmd3 = new SqlCommand(classrooms, connection);
int sqlresult1 = (Int32)cmd1.ExecuteScalar();
int sqlresult2 = (Int32)cmd2.ExecuteScalar();
int sqlresult3 = (Int32)cmd3.ExecuteScalar();
sqlresult1.Should().Be(9);
sqlresult2.Should().Be(11);
sqlresult3.Should().Be(8);
connection.Close();
}
}