I have already developed an application which returns DataTable everywhere.
Now my client wants to convert (use some part using service stack), so I need to return DTO (objects) in my application.
I don't want to change my existing stored procedures or even not want to use LINQ as much as possible (I am not too much aware with LINQ).
For small functionality, I can use Linq no issue.
My question is: how can I change my DataTable to objects of that class?
The sample code is below:
string s = DateTime.Now.ToString();
DataTable dt = new DataTable();
dt.Columns.Add("id");
dt.Columns.Add("name");
for (int i = 0; i < 5000000; i++)
{
DataRow dr = dt.NewRow();
dr["id"] = i.ToString();
dr["name"] = "name" + i.ToString();
dt.Rows.Add(dr);
dt.AcceptChanges();
}
List<Class1> clslist = new List<Class1>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Class1 cls = new Class1();
cls.id = dt.Rows[i]["id"].ToString();
cls.name = dt.Rows[i]["name"].ToString();
clslist.Add(cls);
}
Response.Write(s);
Response.Write("<br>");
Response.Write(DateTime.Now.ToString());
I know, the above method is time-consuming, and I am trying to find an alternate solution.
Is there any alternative way (I guess, LINQ to DataTable) by which it directly converts the rows of tables to List<Class1>?
So that I can return objects in my service stack and go ahead.
It is Vb.Net version:
Public Class Test
Public Property id As Integer
Public Property name As String
Public Property address As String
Public Property createdDate As Date
End Class
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim x As Date = Now
Debug.WriteLine("Begin: " & DateDiff(DateInterval.Second, x, Now) & "-" & Now)
Dim dt As New DataTable
dt.Columns.Add("id")
dt.Columns.Add("name")
dt.Columns.Add("address")
dt.Columns.Add("createdDate")
For i As Integer = 0 To 100000
dt.Rows.Add(i, "name - " & i, "address - " & i, DateAdd(DateInterval.Second, i, Now))
Next
Debug.WriteLine("Datatable created: " & DateDiff(DateInterval.Second, x, Now) & "-" & Now)
Dim items As IList(Of Test) = dt.AsEnumerable().[Select](Function(row) New _
Test With {
.id = row.Field(Of String)("id"),
.name = row.Field(Of String)("name"),
.address = row.Field(Of String)("address"),
.createdDate = row.Field(Of String)("createdDate")
}).ToList()
Debug.WriteLine("List created: " & DateDiff(DateInterval.Second, x, Now) & "-" & Now)
Debug.WriteLine("Complated")
End Sub
Amit, I have used one way to achieve this with less coding and more efficient way.
but it uses Linq.
I posted it here because maybe the answer helps other SO.
Below DAL code converts datatable object to List of YourViewModel and it's easy to understand.
public static class DAL
{
public static string connectionString = ConfigurationManager.ConnectionStrings["YourWebConfigConnection"].ConnectionString;
// function that creates a list of an object from the given data table
public static List<T> CreateListFromTable<T>(DataTable tbl) where T : new()
{
// define return list
List<T> lst = new List<T>();
// go through each row
foreach (DataRow r in tbl.Rows)
{
// add to the list
lst.Add(CreateItemFromRow<T>(r));
}
// return the list
return lst;
}
// function that creates an object from the given data row
public static T CreateItemFromRow<T>(DataRow row) where T : new()
{
// create a new object
T item = new T();
// set the item
SetItemFromRow(item, row);
// return
return item;
}
public static void SetItemFromRow<T>(T item, DataRow row) where T : new()
{
// go through each column
foreach (DataColumn c in row.Table.Columns)
{
// find the property for the column
PropertyInfo p = item.GetType().GetProperty(c.ColumnName);
// if exists, set the value
if (p != null && row[c] != DBNull.Value)
{
p.SetValue(item, row[c], null);
}
}
}
//call stored procedure to get data.
public static DataSet GetRecordWithExtendedTimeOut(string SPName, params SqlParameter[] SqlPrms)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
SqlConnection con = new SqlConnection(connectionString);
try
{
cmd = new SqlCommand(SPName, con);
cmd.Parameters.AddRange(SqlPrms);
cmd.CommandTimeout = 240;
cmd.CommandType = CommandType.StoredProcedure;
da.SelectCommand = cmd;
da.Fill(ds);
}
catch (Exception ex)
{
return ex;
}
return ds;
}
}
Now, The way to pass and call method is below.
DataSet ds = DAL.GetRecordWithExtendedTimeOut("ProcedureName");
List<YourViewModel> model = new List<YourViewModel>();
if (ds != null)
{
//Pass datatable from dataset to our DAL Method.
model = DAL.CreateListFromTable<YourViewModel>(ds.Tables[0]);
}
Till the date, for many of my applications, I found this as the best structure to get data.
You may want to have a look at the code here. Although it doesn't answer your question directly you could adapt the generic class types that are used to map between data classes and business objects.
Also by using generic you run the conversion process as quickly as possible.
Is it very expensive to do this by json convert? But at least you have a 2 line solution and its generic. It does not matter eather if your datatable contains more or less fields than the object class:
Dim sSql = $"SELECT '{jobID}' AS ConfigNo, 'MainSettings' AS ParamName, VarNm AS ParamFieldName, 1 AS ParamSetId, Val1 AS ParamValue FROM StrSVar WHERE NmSp = '{sAppName} Params {jobID}'"
Dim dtParameters As DataTable = DBLib.GetDatabaseData(sSql)
Dim paramListObject As New List(Of ParameterListModel)()
If (Not dtParameters Is Nothing And dtParameters.Rows.Count > 0) Then
Dim json = Newtonsoft.Json.JsonConvert.SerializeObject(dtParameters).ToString()
paramListObject = Newtonsoft.Json.JsonConvert.DeserializeObject(Of List(Of ParameterListModel))(json)
End If
Source: Stackoverflow.com