Design pattern to implement Database operations [closed]

Question

I am trying to implement design pattern in my project and have been reading material over net.

we have a sales application ( in C# ) where in we are storing entities like

Customer 
Contacts
Sales Order
Lead
Opportunity

This is how i am planning to implement design pattern.

Entity would be the base class and it has common variables

namespace DesignModel.Model
{
    class Entity
    {
        protected IDBOperation dbOperation;
        public string id { get; set; }
        public int isSync { get; set; }
        public string timestamp { get; set; }

        public void insert()
        {
            dbOperation.insertData();

        }

        public void update()
        {
            dbOperation.updateData();
        }
        public void delete()
        {
            dbOperation.deleteData();

        }
    }
}

Here is the Contact class that is extending Entity class. Contact class would have its own properties too.

namespace DesignModel.Model
{
    class Contact : Entity
    {
        public Contact()
        {
             dbOperation = new DBOperations();

        }
        public string name { get; set; }
        public string company { get; set; }
        public string jobtitle { get; set; }

    }
}

Now what i have derived from the nature of this application is each entity has following operations for database interaction and to fetch data from server.

for database operation

insert
update
delete

to get and send data from server

getData
sendData

based on that i have created an interface named IDBOperation

namespace DesignModel.Database
{
    interface IDBOperation<T>
    {
        void insertData(T entity) ;
        void updateData(T entity);
        void deleteData(T entity);

        void displayData();

    }
}

and concrete class DBOperations that implements IDBOperation

namespace DesignModel.Database
{
    class DBOperations : IDBOperation<Entity>

    {

        public void insertData(Entity e) {

            Debug.WriteLine("insertData "+e.id );

        }

        public void deleteData(Entity e)
        {
            Debug.WriteLine("deleteData" + e.id);

        }

        public void updateData(Entity e)
        {
            Debug.WriteLine("updateData" + e.id);

        }
        public void displayData()
        {

        }
    }

and at last Contact class

namespace DesignModel.Model
{
    class Contact : Entity
    {
        public Contact()
        {
             dbOperation = new DBOperations();

        }
        public string name { get; set; }
        public string company { get; set; }
        public string jobtitle { get; set; }

    }
}

i am using contact as follows

    Contact contactData = new Contact();
    contactData.id = "1234";
    contactData.isSync = 1;
    contactData.jobtitle = "Sales Manger";
    contactData.insert(contactData);

I am little confused where i have been implementing it correctly or not.

the reason why i kept implementation in DBOperation because if new interface method comes i have to implement it at one place oppose to if i am implementing method in customer, contact and sales order classes.

one thing that worries me is Entity parameter in insertData inside DBOperation not sure whether i will be able to add entire contact into db or not.

so if anyone help me in providing pointers to correctly implement the design pattern in this use case then it will me more helpful

Answer 1

I think you might be looking for an ORM, which provides you with an intermediate layer between your application and the database for the sake of clarity.

Since you already mentioned that EntityFramework is not an option, you should consider developing your own library. It's going to take a while, but you surely won't have any headaches while hunting down the bugs of a library you didn't write or don't understand.

---

In your code, you are calling the delete, insert and update methods on the instances themselves, which is a bad idea per se, since these methods are executed on the database and not on that particular instance. Instead, you could solve this by refactoring your code.

I suggest you recreate the structure of your database server in your application too: you have one database server, within a server there are multiple databases (eg. abstract class for DatabaseEntity) and within a database there can be multiple tables (eg. abstract class for TableEntity). If you execute actions on a table, you might want to do this the following way:

public class MyServer
{
    // ....
}

public class MyDatabase : DatabaseEntity
{
    // ....
}

public class Account : TableEntity
{
    public int Identifier { get; set; }
}

MyServer Server = new MyServer();

Account MyAccount = Server.Database<MyDatabase>.Select<Account>(x => x.Identifier == 123);

MyAccount.Identifier = 456;
Server.Database<MyDatabase>.Update<Account>(MyAccount);

Server.Database<MyDatabase>.Insert<Account>(new Account());

Within that particular Insert<> function, you can scan through the properties and convert their names and values into an INSERT INTO query and execute it. You can also create an attribute for the primary key, which will be used as global identifier when eg. updating an instance. Most likely the hardest part is going to be the Select<> method. You literally have to examine the expression tree in the parameters, so that this expression:

Server.Database<MyDatabase>.Select<Account>(x => x.Identifier == 123);

can become pure SQL (or whatever db you're using):

SELECT * FROM `MyDatabase.Account` WHERE `Identifier` = 123;

With your own solution, you can shape the structure of your library to perfectly suit all your needs and to keep your code clean and well understandable.

Also, I currently have a weekend ORM project on Github based on the previously mentioned structure, you should check out the source code. Feel free to adapt it to your needs or use useful snippets from it.

Answer 2

You have circular dependency between model(Contact) and CRUD(IDBOperation),

Contact contactData = new Contact();
contactData.id = "1234";
contactData.isSync = 1;
contactData.jobtitle = "Sales Manger";
contactData.insert(contactData);

Stick to the Single responsibility principle, model should not have CreateReadUpdateDelete logic inside.
DAO pattern can be a way to go.