# Unit Testing Datalayer written in ADO.net

I have datalayer code as below which is implementing an interface as: Note: I have hand written this code on Notepad so it may contain some typo errors, which you can gracefully ignore. I am searching for an approach here.

public interface ISimpleInterface{
void DosSomething(long someValue);
}

public class ConcreteImplementation: ISimpleInterface
{
public void DosSomething(long someValue){
//add code for creating new command, connection objects here
using(var connection=new NpgsqlConnection(ConnectionString)){
connection.Open();
long ID=GetNextValueFromSeq1(connection);
using(var command=new NpgsqlCommand()){
command.Connection=connection;
command.CommandText="Insert INTO testTable(col1, col2) VALUES (:pcol1, :pcol2)";
new NpgsqlParameters{ ParameterName="pcol1", ParameterType=NpgsqlDbType.BigInt, Value=ID},
new NpgsqlParameters{ ParameterName="pcol1", ParameterType=NpgsqlDbType.BigInt, Value=someValue}
);
}
}
}

long GetNextValueFromSeq1(NpgsqlConnection connection){
//code to get next value from Sequence1 goes here
}
}


A typical Unit Test class might be:

public class ConcreteImplementationTests{

public void DoSomething_SimpleInsert_InsertsRecord(long someValue){
//code for deleting or truncating table  testTable goes here

var sut=new ConcreteImplementation();

sut.DoSomething(13);

//write a select command to check whether a record has been inserted in testTable

//If there is one record in table then test passes, else it fails.
}
}


Note: My concerns are that how I can assert that Dosomething method correctly takes value from sequence and inserts in table, because in real life, a developer might even forget to get value from GetNextValueFromSeq1 method. He/she might not know this thing unless/until at a later stage when running the application they get a run time exception.

I am also open to any suggestions in improving my code so as long as it provides greater testability, test maintainability.

In real life, the DosSomething method might even have a list of long values.

I think this ID should be generated at database level (autoincrement). But let's ignore this, for testing you have to make sure your methods do not have a dependency on eachother.

It would be better if you write it like this

public void DoSomething(long someValue, long id)
{
// Insert new value
}

public long GetInsertId()
{
// Return ID
}


Now you can test these methods independently

public void GetInsert_ShouldReturnNewId()
{
// Your requirements for a new ID should be tested here
}

public void DoSomething_ShouldInsertNewRecord()
{
var sut=new ConcreteImplementation();
var id = sut.GetInsertId();

sut.DoSomething(13, id);
}


By using this approach the developer MUST provide an ID. The developer can obtain an ID by calling your new method. Just keep in mind that if you want to make your code more testable you have to make your methods independant. They must work on there own.

• In my real project I have around 20 sequencies. So, if I leave this decision on developer using the api, he/she might mistakenly get value from other Sequence. – shankbond Mar 31 '14 at 16:12

I would use constructor injection to get a class that can provide you with an ID. That way you can easily mock it in your unittests and you can change the implementations at a later stage if you want to or need to.

 public class ConcreteImplementation: ISimpleInterface
{

private IIdentityProvider _identityProvider;
public ConcreteImplementation(IIdentifierProvider identityProvider)
{

_identityProvider = identityProvider;
}

public void DosSomething(long someValue){
...
using(var connection=new NpgsqlConnection(ConnectionString)){
...
var ID = _identityProvider.GetNextValueFromSeq1(connection);
...
);
}
}
}

}

public interface IIdentityProvider {
long GetNextValueFromSeq1(NpgsqlConnection connection);
}