从数据库（SQLite）中填充列表

Question

我正在尝试用从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; }
}

并且

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;

}

我可以让第二个填充，但这意味着需要打开两次数据库并调用另一个公共列表。 这是唯一的方法吗？ 或者也许有一种方法可以做类似于 Public List[] LoadData() 的事情？

- Kyan

Answer 1

在完成某些操作后，您可以直接进入第二个结果。我还建议使用Using语句。

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

- Mitchel Sellers

---

谢谢你的回答，但是我无法处理区域相关的事情，因为它会返回到public List<Investor> Investor()而不是一个假设的List<Area> Area()。 - Kyan

它们如何相互关联？如果您需要返回数据集，则需要更改返回类型。您可以返回一个类，其中包含List<Area> Areas {get; set;}和List<Investor> Investors {get; set;}属性集合。 - Mitchel Sellers

Answer 2

如果其他人遇到了这个问题。

以下是我解决问题的方法。

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

然后

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

我不确定手动关闭连接是否更好，所以我让“关闭部分”自己处理。

感谢大家的帮助；）

- Kyan