Background: Im trying to access data from a PostgreSQL database for a personal project. I have opted to use the Npgsql library to run commands and retrieve data from the database. I have already created all the tables in the database and in some parts of the project, I have to read data from the database. The code to read it once is rather bulky so I am trying to make a method that can return a "List" of rows that I can iterate through until I find what I'm looking for. Right now there is example on how to do it but none of those examples are generic in nature, for example:

 //The variable names are terrible in this example i found online
 public static List<CustomeObject> getListCustomeObject(String query)
     {
         NpgsqlConnection con = Connexion.Connection();
             List<CustomeObject> list = new List<CustomeObject>();
             NpgsqlCommand Lcmd = new NpgsqlCommand(query, con);
             NpgsqlDataReader lect = Lcmd.ExecuteReader();
             if (lect.HasRows)
             {
                 while (lect.Read())
                 {
                     CustomeObject a = new CustomeObject();
                     a.Minimal = (Double)lect["montant_minimal"];
                     a.Maximal = (Double)lect["montant_maximal"];
                     list.Add(a);
                 }
                 lect.Close();
             }
             return list;
         }
     }

In this example, inside the "while (lect.Read())" loop, the NpgsqlDataReader has access to a row of data that's columns can be accessed directly in the form of " lect["column_name"] ".

How can I take a Snapshot of the NpgsqlDataReader row that can be added to a list and used elsewhere?

Perhaps so that its more generic, something like this:

public static List<_____________> getListCustomeObject(String query)
     {
         NpgsqlConnection con = Connexion.Connection();
             List<_____________> list = new List<_____________>();
             NpgsqlCommand Lcmd = new NpgsqlCommand(query, con);
             NpgsqlDataReader lect = Lcmd.ExecuteReader();
             if (lect.HasRows)
             {
                 while (lect.Read())
                 {
                     _____________ a = lect.SomehowGetTheRowData();
                     list.Add(a);
                 }
                 lect.Close();
             }
             return list;
         }
     }

public void doSomething()
     {
         List<_____________> resultList = getListCustomeObject("SELECT * FROM table");
         string userId = (string)resultList[0]["userId"];
     }

(this is one of the first few questions I've asked on this site so if I'm being vague, I'm sorry. I'll gladly rephrase it or provide additional info if it helps answer my question)

CodePudding user response：

You can use the GetValues method on NpgsqlDataReader to copy out the row values into an object array.

CodePudding user response：

Thanks to @D-Shih suggestion, the library "Dapper" will work perfectly because it solves the issue before it becomes one.

With Dapper, you can immediately cast a row to the attributes of a ModelObject, that object can then be added to a list and then BAMN... you have a list of usable objects with reaschable attributes.

For example: Short tutorial on the use of Dapper

/// <summary>  
/// get the db connection  
/// </summary>  
/// <param name="connStr"></param>  
/// <returns></returns>  
public static IDbConnection OpenConnection(string connStr)  
{  
    var conn = new NpgsqlConnection(connStr);  
    conn.Open();  
return conn;  
}  
/// <summary>  
/// print the data  
/// </summary>  
public static void PrintData()  
{  
    IList<Customer> list;  
    //2.query  
    using (var conn = OpenConnection(_connStr))  
    {  
        var querySQL = @"SELECT id, firstname, lastname, email, createtime FROM public.customer;";  
        list = conn.Query<Customer>(querySQL).ToList();  
    }  
    if (list.Count > 0)  
    {  
        foreach (var item in list)  
        {//print  
            Console.WriteLine($"{item.FirstName}'s email is {item.Email}");  
        }  
    }  
    else  
    {  
        Console.WriteLine("the table is empty!");  
}  
}