Reputation: 103
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() ?{}
Upvotes: 3
Views: 1128
Reputation: 103
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 ;)
Upvotes: 0
Reputation: 63136
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
}
}
Upvotes: 1