Result Set Stored on Models - C # .NET 2.0

Question

I have a function that receives data in a database. Below is 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 can I save the result set to the model (for example, I don't want to return a 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; } } 

Answer 1

You can 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 that you are using .NET 2.0. LINQ is not directly accessible. You will need to use Something like LINQBridge : http://www.albahari.com/nutshell/linqbridge.aspx

Another alternative is to loop all the lines in tblResult and a generic TransactionModel list. 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"] }); } 

Answer 2

Since LINQ is not available in .NET 2 , you will have to iterate over the elements yourself and convert them to your type. Something like that:

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

Answer 3

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;