Convert DataTable to Array

Question

I want to convert a DataTable with 10 rows or more into an Array like this:

        SqlConfiguration clist2 = new SqlConfiguration();
        clist2.QueryString = "SELECT caption_id,caption,description FROM sws_template_detail WHERE template_id = 1";

         DataTable db = clist2.GetRecords;

         ListItem datalist = new ListItem();

         foreach(DataRow row in db.Rows)
         {
             datalist = new ListItem
             {
                 id = row["caption_id"].ToString(),
                 title = row["caption"].ToString(),
                 description = row["description"].ToString()
             };
         }

         var section = new Section
         {
             title = "Title",
             items = new ListItem[]
             {
                 datalist
             }
         };

But the resulting data is only 1 row, how can I solve it?

You need to add the ListItem to the array inside the foreach loop. — Magnus, Mar 11 '22 at 09:30
Don't use a DataTable to begin with if you want a list of items. Use an ORM like EF Core or [Dapper](https://github.com/DapperLib/Dapper) to retrieve results in the form you want. With [Dapper](https://github.com/DapperLib/Dapper) you could write just `var items=connection.Query("select caption_id as Id, caption as Title, description from ....").ToArray();` — Panagiotis Kanavos, Mar 11 '22 at 09:34
`DataTable db = clist2.GetRecords;` <-- I want to know what's going on here... is `GetRecords` _really_ a property-getter? (Or is it a method and you just forgot the parentheses?) — Dai, Mar 11 '22 at 09:37

Dai · Answer 1 · 2022-03-11T09:46:34.247

You can do it in 1 line (well, 1 statement):

var section = new Section
{
    title = "Title",
    items = dataTable.Rows
        .Cast<DataRow>()
        .Select( row => new ListItem()
        {
            id          = row["caption_id" ].ToString(),
            title       = row["caption"    ].ToString(),
            description = row["description"].ToString()
        } )
        .ToArray()
};

Though there's a lot of code smells going on here...

Why is an class named SqlConfiguration being used to execute a SQL query via a property named QueryString.
Public members in C#/.NET should be PascalCase not camelCase.
- So it should be Section.Title, Section.Items, ListItem.Id, ListItem.Title, and ListItem.Description.
Don't use object-initializers for required members of an object, because there's no compile-time guarantees that they'll be populated.
- If a ListItem must have an Id, Title, and Description then they should be passed-in as constructor parameters.
Using array-types (like ListItem[]) is usually not a good idea because array-types have the worst set of attributes in comparison to others: they're fixed-size but also mutable.
- Whereas usually you want something resizable-and-mutable (e.g. List<T>) or completely immutable (e.g. ImmutableArray<T> or at least IReadOnlyList<T>).

	Mutable elements	Resizable	Variance
`T[]` (Array types )	Yes	No	Unsafe
`List<T>`	Yes	Yes	Invariant
`ImmutableArray<T>`	No	No	Invariant
`IReadOnlyList<T>`	No	No	Covariant safe

Hai Dai, its worked for me, thanks very much – Iqbal Mar 14 '22 at 01:53 — Iqbal, Mar 14 '22 at 01:53

score 0 · Answer 2 · answered Mar 11 '22 at 09:34

try this

var items_array=new List<ListItem>();
         foreach(DataRow row in db.Rows)
         {
items_array.add(new ListItem
             {
                 id = row["caption_id"].ToString(),
                 title = row["caption"].ToString(),
                 description = row["description"].ToString()
             });
         }

         var section = new Section
         {
             title = "Title",
             items = items_array.toArray()
         };

Hai Salim Basoy, its worked for me too, thanks very much – Iqbal Mar 14 '22 at 01:54 — Iqbal, Mar 14 '22 at 01:54

score -1 · Answer 3 · answered Mar 11 '22 at 09:47

Loading the data into a DataTable and then converting it into a List wastes both CPU and RAM. You can use an ORM like EF Core or Dapper to execute a query and return the results in the shape you want. For example, using Dapper, what you want is a single line:

var sql=@"select caption_id as Id, caption as Title, description 
FROM sws_template_detail 
WHERE template_id = 1";

var items=connection.Query<ListItem>(sql).ToArray();

Query<T> returns the results as an IEnumerable<T>. This is converted to an array using ToArray().

Dapper allows you to easily write parameterized queries instead of concatenating strings to construct a query:

var sql=@"select caption_id as Id, caption as Title, description 
FROM sws_template_detail 
WHERE template_id = @id";

var items=connection.Query<ListItem>(sql,new {id=1}).ToArray();

The query can be executed asynchronously using QueryAsync;

var items=(await connection.QueryAsync<ListItem>(sql,new {id=1}))
          .ToArray();

Convert DataTable to Array

3 Answers3