Result Set Stored on Models - C# .NET 2.0

Question

I have a function that gets the data in the database. Below are the code.

        public DataTable getAllTransaction(OleDbConnection conn)
        {
            OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
            string query = "";
            DataTable tblResult = new DataTable();

            query = @"SELECT t.id AS `Transaction ID`,
                                  c.id AS `Client ID`,
                                  c.clientname AS `Client Name`,
                                  t.cashvalue AS `Cash Value`,
                                  t.amount AS `Amount`,
                                  t.transdate AS `Transaction Date`,
                                  t.remarks AS `Remarks`
                             FROM client AS c 
                            INNER JOIN `transaction` AS t 
                               ON c.id=t.clientid";
            oleAdapter.SelectCommand = new OleDbCommand(query, conn);
            oleAdapter.Fill(tblResult);

            return tblResult;
        }

My problem is, how could I store the result set into model (e.g. I don't want to return DataTable). Below is my Model Class.

Class TransactionModel
{
    public int transID { get; set; }
    public int clientID { get; set; }
    public string clientName { get; set; }
    public double cashValue { get; set; }
    public double amout { get; set; }
    public DateTime transDate { get; set; }
    public string remarks { get; set; }
}

Just a general comment for all answers, LINQ isn't readily available for .NET 2. You'd have to do some form of a hack as I understand? http://stackoverflow.com/questions/2138/linq-on-the-net-2-0-runtime — LukeHennerley, Mar 20 '13 at 10:37

Darren · Accepted Answer · 2013-03-20T10:56:46.503

2

You could use LINQ and do:

var tranModel = from r in tblResult.Tables[0]
                select new TransactionModel 
                {
                   transId = r.Field<int>("transID"),
                   clientId = r.Field<int>("clientId"),
                   clientName = r.Field<string>("ClientName")
                }

Note since you are using .NET 2.0. LINQ is not directly available. You will have to use Something like LINQBridge: http://www.albahari.com/nutshell/linqbridge.aspx

Another alternative is to loop through all of the rows in tblResult and have a generic list of TransactionModel. For instance:

 List<TransactionModel> tModels = new List<TransactionModel>();
 foreach (var row in tblResult.Tables[0].Rows) 
 {
     tModels.Add(new TransactionModel 
                 {
                   transId = row["TransId"],
                   clientId = row["ClientId"],     
                   clientName = row["clientName"]
                 });
 }

edited Mar 20 '13 at 10:56

answered Mar 20 '13 at 10:25

Darren

68,902
24
138
144

`LINQ` isn't available in .NET 2, is it? – LukeHennerley Mar 20 '13 at 10:35
@LukeHennerley very good point! OP will have to use something like LINQBridge – Darren Mar 20 '13 at 10:56
Is it ideally to use Models instead of manipulating data from DataTable? What are the benefits if I used model instead of DataTable and what is the down side? – Bryan Mar 20 '13 at 12:51
1

@Bryan - using a model provides a more concrete type. Also passing a model around is a lot cleaner than passing a DataTable. – Darren Mar 20 '13 at 13:20

score 2 · Answer 2 · answered Mar 20 '13 at 11:05

Because LINQ isn't available in .NET 2, you'd have to loop through the items yourself and transform them into your type. Something like this:

DataTable transactions = getAllTransactions();
List<TransactionModel> model = new List<TransactionModel>();

foreach (DataRow transaction in transactions.Rows)
{
    TransactionModel tran = new TransactionModel
                            {
                                transId = transaction.Field<int>("transID"),
                                clientId = transaction.Field<int>("clientId"),
                                clientName = transaction.Field<string>("ClientName")
                                //etc...
                            };

    model.Add(tran);
}

score 1 · Answer 3 · answered Mar 20 '13 at 10:28

Do something like:

List<TransactionModel> TransactionItems = tblResult.AsEnumerable().Select(r => 
new TransactionModel
    {
        transID  = r.Field<int>("TransactionID"),
        clientID = r.Field<int>("clientID"),
        and so on.....
    }).ToList();

return items;

Result Set Stored on Models - C# .NET 2.0

3 Answers3