如何将列表<Generic>转换为字典选择查询？

Question

此方法检索整个数据。我正试图将此方法转换为使用字典的方法，但效果不太好。INSERT、UPDATE和DELETE已完成，但SELECT出现问题。

我想转换到我的方法，如下面的链接源。https://gist.github.com/thorsman99/e788dd9cce36c26edd9076c9dac288dd

public static List<TestModel> GetList(string id, string subject, string UseYN, string createDate1, string createDate2)
{
using(SQLiteConnection connection = new SQLiteConnection(_connection))
{
connection.Open();
using(SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = @"SELECT ID, Subject,  CreateDate, UpdateDate FROM Test";
command.Parameters.Add(new SQLiteParameter(@"ID"         , DbType.String) { Value = id          });
command.Parameters.Add(new SQLiteParameter(@"Subject"    , DbType.String) { Value = subject     });
command.Parameters.Add(new SQLiteParameter(@"CreateDate1", DbType.String) { Value = createDate1 });
command.Parameters.Add(new SQLiteParameter(@"CreateDate2", DbType.String) { Value = createDate2 });
SQLiteDataReader reader = command.ExecuteReader();
List<TestModel> list = new List<TestModel>();
while(reader.Read())
{
TestModel item = new TestModel();
item.ID         =                    reader["ID"        ].ToString();
item.Subject    =                    reader["Subject"   ].ToString();
item.CreateDate = Convert.ToDateTime(reader["CreateDate"]);
item.UpdateDate = Convert.ToDateTime(reader["UpdateDate"]);
list.Add(item);
}
return list;
}
}
}

Answer 1

我移动了connection.Open to directly before the。ExecuteReader`连接应在尽可能短的时间内打开。我将Dictionary的声明移到了using块之外，然后将return也移到了外部。这也是为了尽快关闭连接。

我用方块把这两个组合成一个。只是稍微简化了代码并节省了缩进。

对于字典，我用"ID"作为关键字。我认为这是主键，因此是唯一的。

public Dictionary<string, TestModel> GetTestModelDictionary(string id, string subject, string UseYN, string createDate1, string createDate2)
{
Dictionary<string, TestModel> dict = new Dictionary<string, TestModel>();
using (SQLiteConnection connection = new SQLiteConnection(_connection))
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = @"SELECT ID, Subject,  CreateDate, UpdateDate FROM Test";
command.Parameters.Add(new SQLiteParameter(@"ID", DbType.String) { Value = id });
command.Parameters.Add(new SQLiteParameter(@"Subject", DbType.String) { Value = subject });
command.Parameters.Add(new SQLiteParameter(@"CreateDate1", DbType.String) { Value = createDate1 });
command.Parameters.Add(new SQLiteParameter(@"CreateDate2", DbType.String) { Value = createDate2 });
connection.Open();
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TestModel item = new TestModel();
item.ID = reader["ID"].ToString();
item.Subject = reader["Subject"].ToString();
item.CreateDate = Convert.ToDateTime(reader["CreateDate"]);
item.UpdateDate = Convert.ToDateTime(reader["UpdateDate"]);
dict.Add(item.ID, item);
}
}
return dict;
}