如何将DataTable转换为Object类

本文关键字:Object 转换 DataTable | 更新日期: 2023-09-27 18:13:27

我已经开发了一个应用程序,它在任何地方都返回DataTable

现在我的客户端想转换(使用服务堆栈的某些部分),所以我需要在我的应用程序中返回DTO (objects)

我不想改变我现有的存储过程,甚至不想尽可能多地使用LINQ(我对LINQ不太了解)。

对于小功能,我可以使用Linq。

我的问题是:我怎么能改变我的DataTable类的对象?

示例代码如下:
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());

我知道,上面的方法很耗时,我正在努力寻找一个替代的解决方案。

是否有任何替代方法(我猜,LINQ到DataTable),它直接将表的行转换为List<Class1> ?

这样我就可以返回服务栈中的对象,然后继续

如何将DataTable转换为Object类

Initialize DataTable:

DataTable dt = new DataTable(); 
dt.Columns.Add("id", typeof(String)); 
dt.Columns.Add("name", typeof(String)); 
for (int i = 0; i < 5; i++)
{
    string index = i.ToString();
    dt.Rows.Add(new object[] { index, "name" + index });
}
查询本身:

IList<Class1> items = dt.AsEnumerable().Select(row => 
    new Class1
        {
            id = row.Field<string>("id"),
            name = row.Field<string>("name")
        }).ToList();

Amit,我已经使用了一种更少编码和更有效的方法来实现这一目标。

但是它使用Linq

我把它贴在这里,因为也许答案有助于其他SO。

下面的DAL代码将数据表对象转换为YourViewModel的列表,很容易理解。

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

现在,传递和调用方法的方法如下:

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

到目前为止,对于我的许多应用程序,我发现这是获取数据的最佳结构

我看着这个,意识到:这是从一种类型的对象到另一种;基本上,我们正在尝试做适当的反射。

有适当的方法来构建不同字段之间的关系,但给出类定义是完成的,它可以很容易地由Newtonsoft完成。Json

Process: DataSet/DataTable (Serialize) ==>Json(反序列化)==>目标对象列表在本例中作为OP,只需执行:

string serializeddt = JsonConvert.SerializeObject(dt, Formatting.Indented);

现在数据表被序列化成一个普通字符串。然后这样做:

List<Class1> clslist = JsonConvert.DeserializeObject<List<Class1>>(serialized, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore });

现在你应该有一个列表,所有的DataTable行作为单独的对象。

从2023年开始,一个更简单和更少的代码

public class ClassName
{
   public int Id { get; set; }
   public String FirstName { get; set; }
   public String LastName { get; set; }
}

对于类对象List

DataTable dt = new DataTable();
var _objectList = JArray.FromObject(dt).ToObject<List<Venues>>();
对于类对象
DataTable dt = new DataTable();
var _object = JArray.FromObject(dt)[].ToObject<Venues>();

是Vb。净版:

Public Class Test
Public Property id As Integer
Public Property name As String
Public Property address As String
Public Property createdDate As Date

结束课

  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

通过json转换是否非常昂贵?但至少你有一个两行的解,而且是通用的。无论您的数据表包含的字段比对象类多还是少,都没有关系:

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