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.

Answer 3

First, your code was so long and old-style. Your code should be like this

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

When you create this class, it means you create an Object Company which has 3 fields;

Company.CompanyId, Company.CompanyName, Company.CompanyType

So all you have to do now is connecto to SQL server, excute query to get data from database and fill in an Object Company.

Example :

class myConnection
    {
        public static SqlConnection GetConnection()
        {
            var company = new Company();
            string str = "Data Source=localhost/serer Ip;Initial Catalog = YourDatabaseName;uid =sa;pwd = YourPassword";

            SqlConnection con = new SqlConnection(str);          
            SqlCommand cmd = new SqlCommand("SELECT * FROM Company WHERE CompanyID = 1", conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Company.CompanyId = reader["CompanyID"];
                Company.CompanyName = reader["Name"];
                Company.CompanyType = reader["Type"];
            }
        }
    } 

Answer 4

Firstly, i would recommend you to check this question: Using an ORM or plain SQL?. If you are not going to execute exotic and heavy queries, ORM is preferable option for you

I prefer Entity Framework, but it's up to you to choose any of them.

Then if you want to go deeper, look at Repository and Unit of work patters. There is nice article about implementing them in .net: Implementing the Repository and Unit of Work Patterns

Answer 5

String conString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
OracleConnection con = new OracleConnection(conString);
string cmdStr = @" SELECT * FROM TABLE WHERE ROW = :param";
OracleCommand cmd = new OracleCommand(cmdStr, con);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.SelectCommand = cmd;
cmd.Parameters.Add("param", "yourValueHere");

DataSet ds = new DataSet("dataSet");
da.Fill(ds, "dataAdapter");
return ds;

is a good way of implementing database classes. Also remember to tag your method with

[DataObjectMethod(DataObjectMethodType.Select, true)]

if you want it to be implementable in your WPFs.

Answer 6

My home brew ADO object mapper: Build a simple class,

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

Use this method for object mapping:

public List<T> GetData<T>(string SQL, CommandType commandType, string ConnectionString) 
{
    var objProps = Activator.CreateInstance<T>().GetType().GetProperties();
    var returnList = new List<T>();
    using (SqlConnection con = new SqlConnection(ConnectionString)) {
        using (SqlCommand cmd = new SqlCommand(SQL, con)) {
            cmd.CommandType = commandType;
            cmd.CommandTimeout = 30000;
            try
            {                  
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                var columns = reader.GetSchemaTable().Rows.Cast<DataRow>().Select(row => row["ColumnName"].ToString().ToLower()).ToList();
                while (reader.Read())
                {
                    var thisRow = Activator.CreateInstance<T>();
                    foreach (var prop in objProps)
                    {                         
                        if (columns.Contains(prop.Name.ToLower()))
                        {
                            prop.SetValue(thisRow, reader[prop.Name]);
                        }
                    }
                    returnList.Add(thisRow);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally {
                if (con.State != ConnectionState.Closed)
                {
                    con.Close();
                }
            }
        }
    }
    return returnList;
}

Call the method like this:

var companyInfo = GetData<Company>("ProcName", CommandType.StoredProcedure, "con-str");

Answer 7

Use below which we are using in as Class File :

/// <summary>
/// Open the Connection when creating the Object
/// </summary>
class DataAccess
{
    public SqlConnection sqlConn ;
    public int gConnTimeOut = 0 ;

    public DataAccess()
    {
        string strConn = "";            

        Classes.GlobVaribles objConStr = Classes.GlobVaribles.GetInstance();
        strConn = objConStr.gConString;
        gConnTimeOut = objConStr.gQueryTimeOut;

        if (strConn == "")
        {
            XmlAccess XmlFile = new XmlAccess();
            strConn = XmlFile.Xml_Read("gConStr");
            gConnTimeOut = int.Parse(XmlFile.Xml_Read("gQueryTimeOut"));

            objConStr.gConString = strConn;
            objConStr.gQueryTimeOut = gConnTimeOut;
        }

        sqlConn =  new SqlConnection(strConn);            
        sqlConn.Open();
    }

    /// </summary>
    /// Can use to select one value from SELECT statment
    /// </summary>
    public string SQLER(string strSQL)
    {
        if (sqlConn.State.ToString() == "Closed") { sqlConn.Open(); }

        strSQL = SQLFormat(strSQL);
        SqlCommand sqlCmd = new SqlCommand(strSQL, sqlConn);

        string strResult = sqlCmd.ExecuteScalar().ToString();
        sqlCmd.Dispose();

        return strResult;

    }

    /// </summary>
    /// Return Data Set        
    /// </summary>
    public DataSet SQLDT(string strSQL)
    {
        //conn.Close();

        //if (conn.State.ToString() == "Closed") { conn.Open(); }
        if (sqlConn.State.ToString() == "Closed") { sqlConn.Open(); }
        SqlCommand comm = new SqlCommand();
        comm.CommandTimeout = gConnTimeOut;
        SqlDataAdapter adapt = new SqlDataAdapter();
        comm.CommandText = strSQL;
        comm.Connection = sqlConn;
        adapt.SelectCommand = comm;

        DataSet dtset = new DataSet();
        adapt.Fill(dtset);
        return dtset;

    }

   /// <summary>
    /// Can use for Execute SQL commands (Insert/Delete/Update)
    /// </summary>
    public int SQLCX(string strSQL)
    {
        try
        {
            if (sqlConn.State.ToString() == "Closed") { sqlConn.Open(); }

            strSQL = SQLFormat(strSQL);
            SqlCommand sqlCmd = new SqlCommand(strSQL, sqlConn);
            sqlCmd.CommandTimeout = gConnTimeOut;
            int intResult = sqlCmd.ExecuteNonQuery();
            sqlCmd.Dispose();

            return intResult;
        }
        catch (Exception objError)
        {
            MessageBox.Show("System Error - " + objError.Message.ToString(),"Application Error",MessageBoxButtons.OK,MessageBoxIcon.Error );
            return -1;
        }

    }

    /// <summary>
    /// Returns a SQL DataReader
    /// </summary>       
    public SqlDataReader DataReader(string strSQL)
    {
        if (sqlConn.State.ToString() == "Closed") { sqlConn.Open(); }
        strSQL = SQLFormat(strSQL);
        SqlCommand sqlCmd = new SqlCommand(strSQL, sqlConn);
        SqlDataReader dataRed = null;

        dataRed = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
        sqlCmd.Dispose();
        return dataRed;
    }

    /// <summary>
    /// Retrun the No of Records
    /// </summary>
    public int GetNumOfRec(string strSQL)
    {
        /// Use for get No of Records in SELECT command
        try
        {
            int intResult = -1;
            if (sqlConn.State.ToString() == "Closed") { sqlConn.Open(); }

            strSQL = SQLFormat(strSQL);
            SqlCommand sqlCmd = new SqlCommand(strSQL, sqlConn);
            intResult = (int)sqlCmd.ExecuteScalar();
            sqlCmd.Dispose();

            return intResult;
        }
        catch (Exception objError)
        {
            MessageBox.Show("System Error - " + objError.Message.ToString(), "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return -1;
        }
    }

    /// </summary>
    /// Fill Listview 
    /// </summary>
    public void ListViewFill(string strSQL, System.Windows.Forms.ListView lstView)
    {
        if (sqlConn.State.ToString() != "Open") { sqlConn.Open(); }
        SqlDataAdapter adapter = new SqlDataAdapter(strSQL, sqlConn);            
        DataSet ds = new DataSet("glorders");
        adapter.SelectCommand.CommandTimeout = gConnTimeOut;
        adapter.Fill(ds, "glorders");

        DataTable dt = ds.Tables[0];
        int colCount = dt.Columns.Count;

       lstView.Items.Clear();
       Color shaded = Color.FromArgb(240, 240, 240);
       int j = 0;

        foreach (DataRow row in dt.Rows)
        {
            string[] subitems = new string[colCount];

            object[] o = row.ItemArray;


            for (int i = 0; i < colCount; i++)
            {
                subitems[i] = o[i].ToString();                      
            }

            ListViewItem item = new ListViewItem(subitems);
            lstView.Items.Add(item);

            if (j++ % 2 == 1)
            {
                item.BackColor = shaded;
                item.UseItemStyleForSubItems = true;
            }
        }

        dt.Dispose();
        ds.Dispose();
        adapter.Dispose();
    }

    /// </summary>
    /// Fill ComboBox
    /// </summary>
    public void ComboFill(string strSQL, System.Windows.Forms.ComboBox dbCombo)
    {
        SqlDataReader dbReader = null;
        dbReader = DataReader(strSQL);
        dbCombo.Items.Clear();
        while (dbReader.Read()) { dbCombo.Items.Add(dbReader[0].ToString().Trim()); }
        dbReader.Dispose();
    }

    private string SQLFormat(string strSQL)
    {
        strSQL = strSQL.Replace("\r", " ");
        strSQL = strSQL.Replace("\n", " ");
        strSQL = strSQL.Replace("\t", " ");
        strSQL = strSQL.Replace("  ", " ");
        return strSQL;
    }
}