How to load data from MySql database into list(Console-Based app)?

Question

I have created a database with 1 table "emp" and have some data in it. Now every time i start the app, i want a list to fetch the data from db and save it in list because i want to perform some calculations like tax and Gross-Salary on data at runtime for display only(don't want to save it in db ). I have tried many times but i am unable to understand how this can be done. This is my code:

Main Class:

    static void Main(string[] args)
    {
        empDB empDB1 = new empDB();
        List<emplyee> empLST1 = new List<emplyee>();

        if (empLST1 == null)
        {
            empDB1.loadLST(out empLST1);
        }
    }

empDB Class:

class empDB
{
    private string ConnectionString = @"server=localhost;DATABASE=hris;uid=root;Password=123456;";

    internal void loadLST(out List<emplyee> loadedLST)
    {
        string query = "select name, grade from emp";
        try
        {
            MySqlConnection con = new MySqlConnection(ConnectionString);
            con.Open();

            MySqlDataReader rdr = null;
            MySqlCommand cmd = new MySqlCommand(query, con);

            rdr = cmd.ExecuteReader();

            while(rdr.Read())
            {
                List<employee> returnedLst = new List<employee>();
                returnedLst.Add(rdr["name"].ToString(), rdr["grade"].ToString());
            }
            loadedLst = returnedLst;
        }
        catch(Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

I have no idea even if my approach is right or not. I have googled it a few times but i just started working in .net a few days ago so i don't understand how to do it.

Okay i tried this and it also dosn't work:

internal void GetDatabaseList()
{
    List<employee> databases = new List<employee>();
    MySqlConnection con = new MySqlConnection(ConnectionString);
    {
        con.Open();
        DataTable tbl = con.GetSchema("Databases");
        con.Close();

        foreach (DataRow row in tbl.Rows)
        {
            databases.Add(row["hris"].ToString());
        }
    }
}

Why don't use Entity Framework? Is "raw" ADO .NET is a requirement? — Dennis
– Dennis, Commented Nov 24, 2014 at 8:07
@Dennis Yes it is required, as i haven't learned Entity Framework, so i am not allowed to use it either. — Mansoor Akram
– Mansoor Akram, Commented Nov 24, 2014 at 8:10

Taksil · Accepted Answer · 2014-11-24 09:27:13Z

1

static void Main(string[] args)
{
    empDB empDB1 = new empDB();
    List<emplyee> empLST1 = new List<emplyee>();

    **if (empLST1 == null)
    {
        empDB1.loadLST(out empLST1);
    }**
}

this will always be false because you defined empLST1 as a new List, meaning its not null

try this

public class Employee
{
    public string Name { get; set; }
    public string Grade { get; set; }
}

static void Main(string[] args)
{
    empDB empDB1 = new empDB();
    List<Employee> empLST1 = new List<Employee>();

    empDB1.loadLST(ref empLST1);
}

public class empDB
{
    public void loadLst(ref List<Employee> loadedLST)
    {
        string query = "select name, grade from emp";
        try
        {
            MySqlConnection con = new MySqlConnection(ConnectionString);
            con.Open();

            MySqlDataReader rdr = null;
            MySqlCommand cmd = new MySqlCommand(query, con);

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                Employee emp = new Employee();
                emp.Name = rdr["name"].ToString();
                emp.Grade = rdr["grade"].ToString();

                loadedLST.Add(emp);
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

edited Nov 24, 2014 at 9:27

answered Nov 24, 2014 at 9:09

Taksil

462 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mansoor Akram Over a year ago

Okay got it, do you have any solution for loadLST method?

Dennis · Accepted Answer · 2014-11-24 08:59:39Z

Assuming, that that employee class looks like this:

class employee
{
    public string Name { get; set; }
    public string Grade { get; set; }
}

I'd rewrite loadLST like this:

internal List<employee> loadLST()
{
    string query = "select name, grade from emp";

    // we should dispose IDisposable implementations:
    // connection, command and data reader
    using (var con = new MySqlConnection(ConnectionString))
    {
        con.Open();

        using (var cmd = new MySqlCommand(query, con))
        using (var rdr = cmd.ExecuteReader())
        {
            // it is hard to maintain manual mapping
            // between query results and objects;
            // let's use helper like Automapper to make this easier
            Mapper.CreateMap<IDataReader, employee>();
            Mapper.AssertConfigurationIsValid();

            return Mapper.Map<List<employee>>(rdr);
        }
    }
}

Improvements:

IDisposable implementations must be disposed explicitly (see this and this)
to avoid manual mapping code, which maps the result from data reader and object (employee instance in your case), the code uses Automapper package
exception handling and out parameter are thrown away. There's no need for exception handling and out parameter here, unless you're writing method like TryToDoSomething (and even in that case your method must return bool to indicate the state of operation, and catch only specific exceptions instead of Exception).

Also note, that your code doesn't match naming guidelines (e.g., employee should be Employee).

@MansoorAkram: of course you have to install NuGet package (see description at automapper.org).

Collectives™ on Stack Overflow

How to load data from MySql database into list(Console-Based app)?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related