GitHub
I'm in the process of doing a training course in C#. I've just gone over covering Polymorphic OOP / composition over inheritance etc...
The latest exercise I completed in the course was to create two base classes, DBConnector & DBCommand that can be used for any database. The verbatim requirements were as follows -
To access a database, we need to open a connection to it first and close it once our job is done. Connecting to a database depends on the type of the target database and the database management system (DBMS). For example, connecting to a SQL Server database is different from connecting to an Oracle database. But both these connections have a few things in common:
- They have a connection string
- They can be opened
- They can be closed
- They may have a timeout attribute (so if the connection could not be opened within the timeout, an exception will be thrown).Your job is to represent these commonalities in a base class called
DbConnection. This class should have two properties:
-ConnectionString:string
-Timeout:TimeSpan
DbConnectionwill not be in a valid state if it doesn’t have a connection string. So you need to pass a connection string in the constructor of this class. Also, take into account the scenarios where null or an empty string is sent as the connection string. Make sure to throw an exception to guarantee that your class will always be in a valid state. Our DbConnection should also have two methods for opening and closing a connection. We don’t know how to open or close a connection in a DbConnection and this should be left to the classes that derive from DbConnection. These classes (e.g. SqlConnection or OracleConnection) will provide the actual implementation. So you need to declare these methods as abstract. Derive two classes SqlConnection and OracleConnection from DbConnection and provide a simple implementation of opening and closing connections using Console.WriteLine(). In the real-world, SQL Server provides an API for opening or closing a connection to a database. But for this exercise, we don’t need to worry about it.
And for the DBCommand -
Now that we have the concept of a DbConnection, let’s work out how to represent a DbCommand. Design a class called DbCommand for executing an instruction against the database. A DbCommand cannot be in a valid state without having a connection. So in the constructor of this class, pass a DbConnection. Don’t forget to cater for the null. Each DbCommand should also have the instruction to be sent to the database. In case of SQL Server, this instruction is expressed in T-SQL language. Use a string to represent this instruction. Again, a command cannot be in a valid state without this instruction. So make sure to receive it in the constructor and cater for the null reference or an empty string. Each command should be executable. So we need to create a method called Execute(). In this method, we need a simple implementation as follows: Open the connectionRun the instruction Close the connectionNote that here, inside the DbCommand, we have a reference to DbConnection. Depending on the type of DbConnection sent at runtime, opening and closing a connection will be different. For example, if we initialize this DbCommand with a SqlConnection, we will open and close a connection to a Sql Server database. This is polymorphism. Interestingly, DbCommand doesn’t care about how a connection is opened or closed. It’s not the responsibility of the DbCommand. All it cares about is to send an instruction to a database. For running the instruction, simply output it to the Console. In the real-world, SQL Server (or any other DBMS) provides an API for running an instruction against the database. We don’t need to worry about it for this exercise. In the main method, initialize a DbCommand with some string as the instruction and a SqlConnection. Execute the command and see the result on the console. Then, swap the SqlConnection with an OracleConnection and see polymorphism in action.
This is the first exercise in the course I've struggled with & would appreciate a more learned eye to have a look at it. I don't feel that I've done particularly well in this exercise. As I feel the need to have called base.Open(); inside both derived classes is a bit of a bad design, but I'm not 100% sure how else I could go about it.
These are my base classes -
public class DBConnection
{
private readonly string _connectionString;
public TimeSpan Timeout { get; set; }
public DBConnection(string connectionString)
{
if (string.IsNullOrWhiteSpace(connectionString))
throw new InvalidOperationException("Connection String is required.");
this._connectionString = connectionString;
}
public virtual void Open()
{
var startTime = DateTime.Now;
// connection code would go here...
var endTime = DateTime.Now;
CheckTimeout(startTime, endTime);
}
public virtual void Close()
{
}
private void CheckTimeout(DateTime startTime, DateTime endTime)
{
if (endTime - startTime >= this.Timeout)
throw new TimeoutException("The connection timed out...");
}
}
public class DBCommand
{
private readonly DBConnection _dBConnection;
private readonly string _sql;
public DBCommand(DBConnection dBConnection, string sql)
{
_dBConnection = dBConnection ?? throw new InvalidOperationException("DBConnection required.");
this._sql = sql ?? throw new InvalidOperationException("sql required.");
}
public void Execute()
{
_dBConnection.Open();
Console.WriteLine($"Executing: {_sql}");
_dBConnection.Close();
}
}
And here are my derived classes -
public class SqlConnection : DBConnection
{
public SqlConnection(string connectionString)
: base(connectionString)
{
base.Timeout = TimeSpan.FromSeconds(60);
}
public override void Close()
{
Console.WriteLine("Closing connection to SQL Server...");
}
public override void Open()
{
base.Open();
Console.WriteLine("Opening connection to SQL Server...");
Console.WriteLine($"Timeout is set to {base.Timeout}");
}
}
public class OracleConnection : DBConnection
{
public OracleConnection(string connectionString)
: base(connectionString)
{
}
public override void Close()
{
Console.WriteLine("Closing connection to Oracle...");
}
public override void Open()
{
base.Open();
Console.WriteLine("Opening connection to Oracle...");
Console.WriteLine($"Oracle has no timeout");
}
}
And finally my test code -
static void UsingDbCommand()
{
var sqlConnection = new SqlConnection("My.SQL.ConnectionString");
var sqlCommand = new DBCommand(sqlConnection, "DROP TABLE tblUsers -- On SQL Server");
sqlCommand.Execute();
var oracleConnection = new OracleConnection("My.Oracle.ConnectionString");
var oracleCommand = new DBCommand(oracleConnection, "DROP TABLE tblUsers -- On Oracle");
oracleCommand.Execute();
try
{
var fakeSQLConnection = new SqlConnection("fasdf");
var fakeSQLCommand = new DBCommand(fakeSQLConnection, null);
fakeSQLCommand.Execute();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
