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

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
                }
            }