任何关于从数据库获取数据的更好功能的建议

本文关键字：更好 功能 数据 数据库 获取 任何关 | 更新日期: 2023-09-27 18:33:54

要从Firebird基地获取我的数据（用户和书籍），我使用以下方法：

public static Dictionary<string, Users> getUsersFromDB()
{
  Dictionary<string, Users> users_list = new Dictionary<string, Users>();
  string reqestCommand = "SELECT id, user_type, user_nick, user_pass, user_fname, user_lname, user_identify, e_mail,address, registered FROM USERS";
  string connectionString = connectionPath();
  using (FbConnection fbDB = new FbConnection(connectionString))
  {
    fbDB.Open();
    FbCommand reqest = new FbCommand(reqestCommand, fbDB);
    using (FbDataReader reader = reqest.ExecuteReader())
    {
      while (reader.Read())
      {
        string key;
        Users user = new Users();
        user.ID = reader.GetInt32(0);
        user.type = reader.GetString(1).Trim();
        key = reader.GetString(2).Trim();
        user.nick = key;
        user.password = reader.GetString(3).Trim();
        user.fName = reader.GetString(4).Trim();
        user.lName = reader.GetString(5).Trim();
        user.identify = reader.GetString(6).Trim();
        user.email = reader.GetString(7).Trim();
        user.address = reader.GetString(8).Trim();
        user.registerDate = reader.GetString(9).Trim();
        user.localOrInDb = "inDatabase";
        users_list.Add(key, user);
      }
    }
    fbDB.Close();
  }
  return users_list;
}
public static Dictionary<Guid, Books> getBooksFromDB()
{
  Dictionary<Guid, Books> books_list = new Dictionary<Guid, Books>();
  string reqestCommand = "SELECT book_id, book_title, book_author, book_genre, book_language, book_rel_date, book_page_number, book_available_amount, book_type FROM BOOKS";
  string connectionString = connectionPath();
  using (FbConnection fbDB = new FbConnection(connectionString))
  {
    fbDB.Open();
    FbCommand reqest = new FbCommand(reqestCommand, fbDB);
    using (FbDataReader reader = reqest.ExecuteReader())
    {
      while (reader.Read())
      {
        Guid key;
        Books book = new Books();
        Guid theGuid = new Guid(reader.GetString(0).Trim());
        key = book.ID = theGuid;
        book.title = reader.GetString(1).Trim();
        book.author = reader.GetString(2).Trim();
        book.genre = reader.GetString(3).Trim();
        book.language = reader.GetString(4).Trim();
        book.rel_date = reader.GetString(5).Trim();
        book.page_number = reader.GetInt32(6);
        book.amount = reader.GetInt32(7);
        book.type = reader.GetString(8).Trim();
        book.localOrInDb = "inDatabase";
        books_list.Add(key, book);
      }
    }
    fbDB.Close();
  }
  return books_list;
}

我们怎么看，它们几乎是一样的，所以问题：

是否可以从它们中生成一个函数？还是将它们分开会更好？

任何关于从数据库获取数据的更好功能的建议

你可以传递一个参数，该参数从Reader创建目标类的实例：

public static Dictionary<K,T> GetFromDb<K,T>(Func<Reader,KeyValuePair<K,T> maker) {
    var res = new Dictionary<K,T>();
    // ... your code ...
        while (reader.Read()) {
            res.Add(maker(reader));
        }
    return res;
}

现在你可以像这样调用你的GetFromDb：

var users = GetFromDb<string,User>(reader => {
    string key;
    Users user = new Users();
    user.ID = reader.GetInt32(0);
    user.type = reader.GetString(1).Trim();
    key = reader.GetString(2).Trim();
    user.nick = key;
    user.password = reader.GetString(3).Trim();
    user.fName = reader.GetString(4).Trim();
    user.lName = reader.GetString(5).Trim();
    user.identify = reader.GetString(6).Trim();
    user.email = reader.GetString(7).Trim();
    user.address = reader.GetString(8).Trim();
    user.registerDate = reader.GetString(9).Trim();
    user.localOrInDb = "inDatabase";
    return new KeyValuePair<string,User>(key, user);
});

查看此帖子

https://stackoverflow.com/a/1464929/988830

您可以使用扩展方法抽象读取器。

但是，您必须每次都创建FB连接。如果您尝试将其放入一个函数并从另一个函数访问它，那么您将收到阅读器关闭的异常。

使用

使用ID是可 http://msdn.microsoft.com/en-us/library/system.idisposable.aspx这将关闭连接。

另一种选择开始使用像实体框架或NHibernate这样的orm，它将用你的对象映射表。

相关文章：