Populate lists from database (SQLite)

Question

I'm trying to fill two lists with informations get on the database SQlite.

public class Investor // List
{
    public int iID { get; set; }
    public string iName { get; set; }
    public string iDisplayName { get; set; }
    public string iArea { get; set; }
}

public class Area // List
{
    public int aID { get; set; }
    public string aName { get; set; }
    public string aDisplayName { get; set; }
}

And

public class training : MonoBehaviour {

public List<Investor> Investor()
{
    var listOfInvestor = new List<Investor>();

    string conn = "URI=file:" + Application.dataPath + "/db_01.s3db";
    IDbConnection dbconn;
    dbconn = (IDbConnection) new SqliteConnection(conn);
    dbconn.Open(); 
    IDbCommand dbcmd = dbconn.CreateCommand();

    string sqlQuery = "SELECT * "+" FROM investor; SELECT * "+" FROM Area;";
    dbcmd.CommandText = sqlQuery;
    IDataReader reader = dbcmd.ExecuteReader();



    while(reader.Read())
    {
        var investor = new Investor();

        investor.iID = Convert.ToInt32(reader["i_id"]);
        investor.iName = reader["i_name"].ToString();
        investor.iDisplayName = reader["i_display_name"].ToString();
        investor.iArea = reader["i_area"].ToString();

        listOfInvestor.Add(investor);
    }

    reader.Close();
    reader = null;
    dbcmd.Dispose();
    dbcmd = null;
    dbconn.Close();
    dbconn = null;

    return listOfInvestor;

}

I can get the second one fill, but that mean open twice the db and call another public List.

public List<Area> Area(){}

It's the only way ?

Or maybe there is a way to do something like Public List<string>[] LoadData() ?{}

Answer 1

In case of somebody else get the problem.

Here's what I did to solve my issue.

public class Investor // List
{
public int iID { get; set; }
public string iName { get; set; }
public string iDisplayName { get; set; }
public string iArea { get; set; }
}

public class Area // List
{
public int aID { get; set; }
public string aName { get; set; }
public string aDisplayName { get; set; }
}

public class Data
{
public List<Investor> Investor { get; set; }
public List<Area> Area { get; set; }
}

And then

public Data LoadData()
{
    var listOfInvestor = new List<Investor>();
    var listOfArea = new List<Area>();

    string conn = "URI=file:" + Application.dataPath + "/db_01.s3db";

    var dbconn  = new SqliteConnection(conn);
    var dbcmd = dbconn.CreateCommand();
    using (dbconn) 
    using (dbcmd)

    {
        dbconn.Open(); 

        string sqlQuery = "SELECT * "+" FROM investor; SELECT * "+" FROM area;";
        dbcmd.CommandText = sqlQuery;
        IDataReader reader = dbcmd.ExecuteReader();



        while(reader.Read())
        {
            var investor = new Investor();

            investor.iID = Convert.ToInt32(reader["i_id"]);
            investor.iName = reader["i_name"].ToString();
            investor.iDisplayName = reader["i_display_name"].ToString();
            investor.iArea = reader["i_area"].ToString();

            listOfInvestor.Add(investor);


        }
        reader.NextResult();
        while(reader.Read())
        {
            var area = new Area();

            area.aID = Convert.ToInt32(reader["a_id"]);
            area.aName = reader["a_name"].ToString();
            area.aDisplayName = reader["a_display_name"].ToString();

            listOfArea.Add(area);
        }

        reader.Close();
        reader = null;
        dbcmd.Dispose();
        dbcmd = null;
        dbconn.Close();
        dbconn = null;

        return new Data() {Investor = listOfInvestor, Area = listOfArea };
    }     
}

I don't really know if it's better to close the connection manually or not so I let the "closing part".

Thanks all for your help ;)

Answer 2

You can simply advance to the second result after doing things. I also recommend the use of Using Statements.

var listOfInvestor = new List<Investor>();

            string conn = "URI=file:" + Application.dataPath + "/db_01.s3db";
            using (var dbConnection = (IDbConnection)new SqlLiteConnection(conn))
            using (var dbcmd = dbConnection.CreateCommand())
            {
                dbConnection.Open();

                string sqlQuery = "SELECT * " + " FROM investor; SELECT * " + " FROM Area;";
                dbcmd.CommandText = sqlQuery;
                IDataReader reader = dbcmd.ExecuteReader();

                //Read first result set
                while (reader.Read())
                {
                    var investor = new Investor();

                    investor.iID = Convert.ToInt32(reader["i_id"]);
                    investor.iName = reader["i_name"].ToString();
                    investor.iDisplayName = reader["i_display_name"].ToString();
                    investor.iArea = reader["i_area"].ToString();

                    listOfInvestor.Add(investor);
                }


                //Repeat for your other result set
                reader.NextResult();
                while (reader.Read())
                {
                    //Do areas stuff here
                }
            }