Creating a class to interact with a SQL database

Question

I'm familiar with the main Object Oriented concepts in programming and currently I'm teaching myself how to design classes.

I have a very simple class calld Company. Here's the code I have so far

using System;

namespace Addressbook
{
    public class Company
    {
        private string _companyId;
        private string _companyName;
        private string _companyType;
        private string[] _companyDetails;

        public Company()
        {

        }


        public string CompanyId
        {
            set
            {
                this._companyId = value;
            }
        }

        public string CompanyName
        {
            set
            {
                this._companyName = value;
            }
        }

        public string CompanyType
        {
            set
            {
                this._companyType = value;
            }
        }


        public string[] GetCompanyDetails()
        {

            return null;
        }

    }
}

What I'm now trying to do is implementing some methods to it and that's where I'm sort of lost.

The first method I'm thinking of is called GetCompanyDetails() which would gather data from a SQL database and then display it. Possibly in a DataGridView or something.

My problem is I can't figure out how I should write this method. Do I put all the SQL queries and connections inside it? Or do I just pass instances of them as parameters? What's the type I should return from the method?

Can someone please give me some guidelines on this?

And also, if you have links to any good tutorials/guides on this subject matter, please post them too.

Thank you.

Answer 1

Create class, which represents records in your table

public class Company
{       
    public string CompanyId { get; set; }
    public string CompanyName{ get; set; }
    public string CompanyType{ get; set; }       
}

Get and map it with Dapper:

public IEnumerable<Company> GetCompanies()
{
   using (var connection = new SqlConnection(connectionString))
   {
       connection.Open();
       return connection.Query<Company>("SELECT * FROM Companies");
   }
}

If you don't want to deal with ADO.NET, look at heavy-weight ORMs like Linq 2 Sql, Entity Framework, NHibernate.

Answer 2

Have a look at the patterns in the section "Data Source Architectural Patterns" at: http://martinfowler.com/eaaCatalog/index.html. An ActiveRecord pattern may be what you need to get going, although you might end-up going the DataMapper/ORM route anyway.

In the Agile spirit, I would keep connection and transaction management initially very simple. Maybe just a private function within this class. I would then have each method define its query in SQL or a SP and execute it and do something with the results. This keeps the data access logic together in the appropriate place.

You might want to make your 'getter' function(s) shared/static so that an instance is not required to get going. Then you can write something like:

var companies = Company.GetCompanyDetails();

Whilst super simplistic, this approach will get you going while still allowing you scope to extend. Once things start to get more complex, this approach will prove too simple. At this point you'll need to extend and refactor considering more robust connection/transaction management, object creation and querying.