CODEWITHSUNDEEP
c#databaseclass-design
tesicg
tesicg

Reputation: 4053

How to define base class that handles database connection?

I have a console application with a base class as following:

public abstract class PaymentSystemBase : IPayable
{
    private SqlConnection _connection;

    protected PaymentSystemBase()
    {
        CreateDatabaseConnection();
    }

    protected void CreateDatabaseConnection()
    {
        if(_connection == null)
        {
            string connectionString = ConfigurationManager.AppSettings["connString"];
            var connection = new SqlConnection(connectionString);
            _connection = connection;
            connection.Open();
        }
    }

    public SqlConnection Connection
    {
        get { return _connection;  }
    }

    public abstract void ProcessPayment();
}

And have a few classes that derive from PaymentSystemBase:

public class PS1 : PaymentSystemBase
{
    public override void ProcessPayment()
    {
         // Work with database using Connection from PaymentSystemBase
    }
}

public class PS2 : PaymentSystemBase
{
    public override void ProcessPayment()
    {
         // Work with database using Connection from PaymentSystemBase
    }
}

In main program:

var lstPayments = new List<IPayable>
{
    new PS1(),
    new PS2()
};

var processPayments = new ProcessPayments(lstPayments);
processPayments.Process();

Where:

public class ProcessPayments
{
    private List<IPayable> _paymentSystems;

    public ProcessPayments(List<IPayable> paymentSystem)
    {
        _paymentSystems = paymentSystem;
    }

    public void Process()
    {
        foreach (var paymentSystem in _paymentSystems)
        {
            paymentSystem.ProcessPayment();
        }
    }
}

My question is how to use the same connection from PaymentSystemBase class and close it after processing? As I can see the connection was created again every time when PS1 and PS2 were created.

Upvotes: 0

Views: 2827

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

You shouldn't try to share the connection object. The connection objects themselves are actually quite lightweight, being an abstraction built on top of the actual physical connections, that the ADO.NET connection pool takes care of creating.

So you base class should be something like:

public abstract class PaymentSystemBase : IPayable
{
    private static string _connectionString =
        ConfigurationManager.ConnectionStrings["connString"].ConnectionString

    public static string ConnectionString
    {
        get { return _connection;  }
    }

    public abstract void ProcessPayment();
}

And then your derived classes should be:

public class PS1 : PaymentSystemBase
{
    public override void ProcessPayment()
    {
        using(var conn = new SqlConnection(PaymentSystemBase.ConnectionString))
        {
           using(var cmd = new SqlCommand("...",conn)
           {
               //Prepare command
               conn.Open();
               cmd.ExecuteXXX();
               //Process results, etc
           }
        }
    }
}

You'll notice that I've also switched where the connection string is loaded from via the ConfigurationManager class from AppSettings to ConnectionStrings, which is a dedicated part of the configuration system for storing connection strings. This wasn't actually required but it is more conventional.

Upvotes: 3

Related Questions