Using ad hoc SQL commands in DBTestUnit tests


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.

These are:

  • ExecuteScalar
  • ExecuteDataSet
  • Execute

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);
    

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: