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();
}
}
public void T1_check_if_firstName_lastname_column_in_student_table_are_created_and_filled()
{
using (SqlConnection connection = new SqlConnection(DbInit.GetConnectionString()))
{
connection.Open();
ResetTables(connection);
ScriptExecutor exe = new ScriptExecutor();
DataTable StudentDataTableBefore = new DataTable();
string queryBefore = "use itiSchoolDB select * from Student";
SqlCommand cmdBefore = new SqlCommand(queryBefore, connection);
SqlDataAdapter daBefore = new SqlDataAdapter(cmdBefore);
daBefore.Fill(StudentDataTableBefore);
StudentDataTableBefore.Columns.Count.Should().Be(6);
exe.ScriptExe(DbModificationScheme.UpdateTableStudent(), connection);
DataTable datatable = new DataTable();
string query = "select * from Student";
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(datatable);
datatable.Columns.Contains("Guid").Should().BeTrue();
datatable.Columns.Contains("Name").Should().BeTrue();
datatable.Columns.Contains("Semestre").Should().BeTrue();
datatable.Columns.Contains("Orientation").Should().BeTrue();
datatable.Columns.Contains("BirthDate").Should().BeTrue();
datatable.Columns.Contains("MainTeacher").Should().BeTrue();
datatable.Columns.Contains("Lastname").Should().BeTrue();
datatable.Columns.Contains("Firstname").Should().BeTrue();
datatable.Columns.Count.Should().Be(8);
string students = "use itiSchoolDB select Name, Firstname, Lastname from Student";
SqlCommand cmd1 = new SqlCommand(students, connection);
SqlDataReader reader = cmd1.ExecuteReader();
string fullNameConcat = "";
string baseName = "";
try
{
while (reader.Read())
{
baseName = (String.Format("{0}", reader["Name"]));
fullNameConcat = (String.Format("{0} {1}", reader["Firstname"], reader["Lastname"]));
fullNameConcat.Should().Be(baseName);
}
}
finally
{
reader.Close();
}
connection.Close();
}
}
public void T2_Return_Classroom_Where_You_Can_Find_Student_Whose_Name_Starts_With_Thibau_When_They_Are_With_Their_MainTeacher()
{
using (SqlConnection connection = new SqlConnection(DbInit.GetConnectionString()))
{
connection.Open();
ScriptExecutor exe = new ScriptExecutor();
List <string> response = exe.ScriptReader(RequestsScripts[1], connection, "Name").ToList();
Assert.AreEqual(response.Count, 2);
Assert.Contains("E07", response);
Assert.Contains("E03", response);
}
}
public void T2_check_if_teacher_table_is_created()
{
using (SqlConnection connection = new SqlConnection(DbInit.GetConnectionString()))
{
connection.Open();
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);
string cmdText = @"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='Teacher ') SELECT 1 ELSE SELECT 0";
SqlCommand DateCheck = new SqlCommand(cmdText, connection);
int x = Convert.ToInt32(DateCheck.ExecuteScalar());
x.Should().Be(1);
DataTable datatable = new DataTable();
string query = "select * from Teacher";
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(datatable);
datatable.Columns.Contains("Guid").Should().Be(true);
datatable.Columns.Contains("Name").Should().Be(true);
datatable.Columns.Contains("Course").Should().Be(true);
datatable.Columns.Contains("BirthDate").Should().Be(true);
datatable.Columns.Contains("IsInternal").Should().Be(true);
connection.Close();
}
}
public void T4_Return_Classroom_Name_Alphabeticaly_With_An_IL_Teacher_And_A_Projector()
{
using (SqlConnection connection = new SqlConnection(DbInit.GetConnectionString()))
{
connection.Open();
ScriptExecutor exe = new ScriptExecutor();
List <string> response = exe.ScriptReader(RequestsScripts[3], connection, "Name").ToList();
response.Count().Should().Be(3);
Assert.AreEqual("E01", response[0]);
Assert.AreEqual("E02", response[1]);
Assert.AreEqual("E07", response[2]);
}
}
public void T1_Find_All_Student_Whose_Teacher_Is_Born_In_1973_11_17()
{
using (SqlConnection connection = new SqlConnection(DbInit.GetConnectionString()))
{
connection.Open();
ScriptExecutor exe = new ScriptExecutor();
List <string> response = exe.ScriptReader(RequestsScripts[0], connection, "Name").ToList();
Assert.AreEqual(response.Count, 3);
Assert.Contains("Damien Gidon", response);
Assert.Contains("Vin Diesel", response);
Assert.Contains("Thibault Cam", response);
}
}
public void T1_check_if_database_is_created()
{
ScriptExecutor exe = new ScriptExecutor();
using (SqlConnection connection = new SqlConnection(DbInit.GetConnectionString()))
{
connection.Open();
bool databaseExists;
string checkIfExistCommand = string.Format("SELECT database_id FROM sys.databases WHERE Name = '{0}'", _databaseName);
SqlCommand sqlCmd = new SqlCommand(checkIfExistCommand, connection);
object resultObj = sqlCmd.ExecuteScalar();
int databaseID = 0;
if (resultObj != null)
{
int.TryParse(resultObj.ToString(), out databaseID);
}
databaseExists = (databaseID > 0);
if (databaseExists == false)
{
exe.ScriptExe(DbInit.CreateDatabse(), connection);
resultObj = sqlCmd.ExecuteScalar();
if (resultObj != null)
{
int.TryParse(resultObj.ToString(), out databaseID);
}
databaseID.Should().BeGreaterThan(0);
}
else
{
String dropBaseCommand = "ALTER DATABASE " + _databaseName + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE " + _databaseName;
SqlCommand sqlCommand = new SqlCommand(dropBaseCommand, connection);
sqlCommand.ExecuteScalar();
resultObj = sqlCmd.ExecuteScalar();
exe.ScriptExe(DbInit.CreateDatabse(), connection);
if (resultObj != null)
{
int.TryParse(resultObj.ToString(), out databaseID);
}
databaseID.Should().BeGreaterThan(0);
}
}
}
public void T3_Return_Teachers_Courses_Who_Have_At_Least_One_Student()
{
using (SqlConnection connection = new SqlConnection(DbInit.GetConnectionString()))
{
connection.Open();
ScriptExecutor exe = new ScriptExecutor();
List <string> response = exe.ScriptReader(RequestsScripts[2], connection, "Course").ToList();
Assert.AreEqual(response.Count, 5);
Assert.Contains("Programmation", response);
Assert.Contains("Ex-Dirlo <3", response);
Assert.Contains("PFH", response);
Assert.Contains("Programmation", response);
Assert.Contains("Antiquaire", response);
}
}
public void T1_Create_StudentsView()
{
using (SqlConnection connection = new SqlConnection(DbInit.GetConnectionString()))
{
connection.Open();
ScriptExecutor exe = new ScriptExecutor();
exe.ScriptExe(DbViews.Views(), connection);
string cmdText = @"USE itiSchoolDB IF EXISTS (SELECT * FROM sys.objects WHERE type = 'V' AND name = 'StudentsView') SELECT 1 ELSE SELECT 0";
SqlCommand DateCheck = new SqlCommand(cmdText, connection);
int x = Convert.ToInt32(DateCheck.ExecuteScalar());
x.Should().Be(1);
// Check if already Exist
exe.ScriptExe(DbViews.Views(), connection);
}
}
public void T1_Create_Proc_FindStudent_With_Specific_String_In_Their_Name()
{
using (SqlConnection connection = new SqlConnection(DbInit.GetConnectionString()))
{
connection.Open();
ScriptExecutor exe = new ScriptExecutor();
exe.ScriptExe(DbStoredProcedures.StoredProceduresFindStudent(), connection);
string cmdText = @"USE itiSchoolDB IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'FindStudent') SELECT 1 ELSE SELECT 0";
SqlCommand DateCheck = new SqlCommand(cmdText, connection);
int x = Convert.ToInt32(DateCheck.ExecuteScalar());
x.Should().Be(1);
// Check if already Exist
exe.ScriptExe(DbStoredProcedures.StoredProceduresFindStudent(), connection);
}
}
public void T3_Create_Proc_UpStudent(string[] redoublants, int nbValidated, string[] validatedNames, int sizeTable)
{
using (SqlConnection connection = new SqlConnection(DbInit.GetConnectionString()))
{
connection.Open();
ScriptExecutor exe = new ScriptExecutor();
exe.ScriptExe(DbStoredProcedures.StoredProceduresUpStudent(), connection);
string cmdText = @"USE itiSchoolDB IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'UpStudent') SELECT 1 ELSE SELECT 0";
SqlCommand DateCheck = new SqlCommand(cmdText, connection);
int x = Convert.ToInt32(DateCheck.ExecuteScalar());
x.Should().Be(1);
// Check if already Exist
exe.ScriptExe(DbStoredProcedures.StoredProceduresUpStudent(), connection);
ResetTable(connection);
// Test if proc is working
StringBuilder str = new StringBuilder("use itiSchoolDB");
str.Append(" DECLARE @Students AS StudentList");
for (int i = 0; i < redoublants.Length; i++)
{
str.Append(" INSERT INTO @Students VALUES ('" + redoublants[i] + "')");
}
str.Append(" EXEC UpStudent @Students");
List <string> results = exe.ScriptReader(str.ToString(), connection, "Name").ToList();
results.Count.Should().Be(nbValidated);
foreach (string suc in validatedNames)
{
Assert.Contains(suc, results);
}
// Check if validated students have been deleted
exe.ScriptReader("use itiSchoolDB SELECT * from Student", connection, "Name").Count().Should().Be(sizeTable);
}
}
public void T2_check_if_firstName_lastname_column_in_teacher_table_are_created_and_filled_For_Teachers_Born_After_1975()
{
using (SqlConnection connection = new SqlConnection(DbInit.GetConnectionString()))
{
connection.Open();
ResetTables(connection);
ScriptExecutor exe = new ScriptExecutor();
DataTable TeacherDataTableBefore = new DataTable();
string queryBefore = "use itiSchoolDB select * from Teacher";
SqlCommand cmdBefore = new SqlCommand(queryBefore, connection);
SqlDataAdapter daBefore = new SqlDataAdapter(cmdBefore);
daBefore.Fill(TeacherDataTableBefore);
TeacherDataTableBefore.Columns.Count.Should().Be(6);
exe.ScriptExe(DbModificationScheme.UpdateTableTeacher(), connection);
DataTable datatable = new DataTable();
string query = "select * from Teacher";
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(datatable);
datatable.Columns.Contains("Guid").Should().BeTrue();
datatable.Columns.Contains("Name").Should().BeTrue();
datatable.Columns.Contains("Course").Should().BeTrue();
datatable.Columns.Contains("Orientation").Should().BeTrue();
datatable.Columns.Contains("BirthDate").Should().BeTrue();
datatable.Columns.Contains("IsInternal").Should().BeTrue();
datatable.Columns.Contains("Lastname").Should().BeTrue();
datatable.Columns.Contains("Firstname").Should().BeTrue();
datatable.Columns.Count.Should().Be(8);
string teachersWithFullName = "use itiSchoolDB select Name, Firstname, Lastname from Teacher where BirthDate > Convert(date, '1975-12-12' )";
string teachersWithoutFullName = "use itiSchoolDB select Name, Firstname, Lastname from Teacher where BirthDate <= Convert(date, '1975-12-12' )";
SqlCommand cmd1 = new SqlCommand(teachersWithFullName, connection);
SqlCommand cmd2 = new SqlCommand(teachersWithoutFullName, connection);
SqlDataReader reader1 = cmd1.ExecuteReader();
SqlDataReader reader2 = cmd2.ExecuteReader();
string fullNameConcat = "";
string baseName = "";
try
{
while (reader1.Read())
{
baseName = (String.Format("{0}", reader1["Name"]));
fullNameConcat = (String.Format("{0} {1}", reader1["Firstname"], reader1["Lastname"]));
fullNameConcat.Should().Be(baseName);
}
while (reader2.Read())
{
baseName = String.Format("{0}", reader2["Name"]);
fullNameConcat = string.IsNullOrWhiteSpace(String.Format("{0} {1}", reader2["Firstname"], reader2["Lastname"])) ? null : String.Format("{0} {1}", reader2["Firstname"], reader2["Lastname"]);
fullNameConcat.Should().BeNull();
}
}
finally
{
reader1.Close();
reader2.Close();
}
connection.Close();
}
}
