Populating lists from database (SQLite)
I am trying to populate two lists with information that can be obtained from the SQlite database.
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 fill, but that means you open the db twice and call another open list.
public List<Area> Area(){}
Is this the only way?
Or maybe there is a way to do something like Public List<string>[] LoadData() ?{}
+3
source to share
2 answers
You can just go to the second result after doing things. I also recommend using using expressions.
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
}
}
+1
source to share
If anyone else comes across a problem.
Here's what I did to solve my problem.
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 really don't know if it's better to close the connection manually or not, so I allow the "closing part".
Thanks everyone for your help;)
0
source to share