Method to Retrieve Data from a MySQL Database I have a method that retrieves information relating to a Contact that was made between two Companies. I am looking to improve my code in any way and am intrigued in hearing any recommendations you may have on how this method can be improved. I use it throughout my program but modify it according to the DataModel I am attempting to load.

string constr = ConfigurationManager.ConnectionStrings["sqlString"].ConnectionString;
using (var sqlCon = new MySqlConnection(constr))
{
    sqlCon.Open();
    using (var myQuery = sqlCon.CreateCommand())
    {
        myQuery.CommandText = @"SELECT DISTINCT contacts.contactID, contacts.companyID, 
        companies.name, contacts.donestatus, contacts.telephone, employees.ID, employees.name,
        people.ID, people.firstname, people.lastname, 
        contacts.contractID, contacts.date, contacts.time, 
        presets.presettext, contacts.enteredby, contacts.description
        FROM contacts
        LEFT OUTER JOIN companies ON contacts.companyID = companies.ID
        LEFT OUTER JOIN employees ON contacts.employeeID = employees.ID
        LEFT OUTER JOIN people ON contacts.personID = people.ID
        JOIN presets ON contacts.type = presets.presetIDFoxPro
        WHERE presets.presetreferencefoxpro = 8
        AND contacts.date &gt; @date
        ORDER BY contacts.date DESC";
        myQuery.Parameters.AddWithValue("@date", date);
        using (var myReader = myQuery.ExecuteReader())
        {
            var listOfContacts = new ObservableCollection&lt;ContactModel&gt;();
            while (myReader.Read())
            {
                var details = new ContactModel
                {
                    ID = Convert.ToInt32(myReader[0]),
                    CompanyID = DBNullCheck(myReader[1]),
                    CompanyName = myReader[2].ToString().Trim(),
                    DoneStatus = Convert.ToInt32(myReader[3]),
                    Telephone = myReader[4].ToString().Trim(),
                    EmployeeID = DBNullCheck(myReader[5]),
                    EmployeeName = myReader[6].ToString().Trim(),
                    PersonID = DBNullCheck(myReader[7]),
                    PersonName = myReader[8].ToString().Trim() + " " + myReader[9].ToString().Trim(),
                    ContractID = DBNullCheck(myReader[10]),
                    Date = Convert.ToDateTime(myReader[11]),
                    Time = Convert.ToDateTime(myReader[12]),
                    TypeOfContact = myReader[13].ToString().Trim(),
                    EnteredBy = myReader[14].ToString().Trim(),
                    Notes = myReader[15].ToString(),
                };
                listOfContacts.Add(details);
            }
            return listOfContacts;
        }
    }
}


I would be happy if you could recommend improvements to my C# code or my MySQL statement alike.
In my review I assume you cannot use EF or alike...

I don't see any particular really bad practices here but there are a few minor things that make me uncomfortable when I look at your code. As the important things are correct (everything is disposed and you use parameters to prevent sql injection) there is not much left to review. But hey! this a good news ;-)



The my-prefix ;-) It doesn't hurt but it doesn't look professional either. You know everything there is yours so there's no need to emphasize it with my.



Try to name things by what they really are instead of giving them misleading names. If you do sqlCon.CreateCommand() then name the variable command or cmd not a query. It isn't one. Your sql is a query and it would be fine to name it like so.



Returning an ObservableCollection is a questionable practice. I suggest to use a List instead and update the real observable collection with the new data.



The listOfContacts. It's a collection and for collections we usually just use a plural noun. In this case just contants would be enough.



The reader[0] &amp; magic numbers. You should use constants here like reader[Column.ID]. It looks much better now, doesn't it?

class Column
{
    public const int ID = 0;
    ...
}





PersonName = myReader[8].ToString().Trim() + " " + myReader[9].ToString().Trim(),



PersonName or FullName should be a readonly property of the ContactModel and it should have two normal properties for the first and last names. Your repository shouldn't care and know how to build a person name. It should just pass the data to the ContactModel.