When writing database tests you will find that you need to excute a SQL statement as part of the test.
For example, you might want to check the number of rows in a table before and after running an ‘insert’ sproc or to execute the sproc from your C# tests.
The DBTestUnit.Util.DALHelper class provides three methods that allow SQL commands to be run against your database.
All take two strings as parameters – the dbInstance (defines which db to run the SQL command against) and the SQL text to run.
Which one to use depends on the expected output:
- No value to be returned.
For example to carry out an insert of a test record into a table:
string sqlText = "INSERT INTO HumanResources.Department (Name, GroupName) VALUES ('TestDeptName', 'TestGroupName')"; DBTestUnit.Util.DALHelper.Execute(dbInstance, sqlText);
- Scalar value to be returned.
For example, to get the row count of a table:
string sqlText = "SELECT COUNT(*) FROM HumanResources.Department"; int rowCount = System.Convert.ToInt32(DALHelper.ExecuteScalar(dbInstance, sqlText));
- Dataset to be returned
sqlText = "SELECT * FROM HumanResources.Department"; DataSet ds =DBTestUnit.Util.DALHelper.ExecuteDataSet(dbInstance, sqlText);