Any suggestions for better function to get data from DataBase

Question

To get my data(users and books) from Firebird base I am using the following methods:

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

How can we see, they are almost same, so question:

Is it possible to make from them one function? Or would it be better to leave them separate?

You could overload the function, passing in a parameter to determine which database it calls and the logic to do so but honestly, as the column names are different and the object returned are different I'd leave them separated. Seems cleaner and more explicit to me. — ramsey_tm, May 12 '12 at 15:04
Using hard-coded indexes like this: user.lName = reader.GetString(5).Trim(); makes my skin crawl. Could you use column names instead? user.lName = reader["lname"].ToString().Trim(); (cast as appropriate). — Steve Wellens, May 12 '12 at 15:11
And then, check if returned field is null before calling Get... — Val Bakhtin, May 12 '12 at 15:22

score 2 · Accepted Answer · answered May 12 '12 at 15:08

You can pass a parameter that makes an instance of your target class from 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;
}

Now you can call your GetFromDb like this:

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

score 1 · Answer 2 · edited May 23 '17 at 12:04

Check this post

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

You can abstract the reader using extension method.

However you have to create the FBconnection everytime. If you try to put it in a function and access it from another one then you will get exception that reader is closed.

using uses IDisposible http://msdn.microsoft.com/en-us/library/system.idisposable.aspx which closes the connection.

Another option Start using orm like Entity framework or NHibernate which will map the table with your object.

Any suggestions for better function to get data from DataBase

2 Answers2