How to assign Datatable to List<> in C#?

Question

This is how i return Datatable 
Then how to assign that datatable to list<> (is it possible to assign datatable to list<>) ??


 public DataTable Vehiclelist(string Vehicle)
        {
            SqlCommand newCmd = conn.CreateCommand();
            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;
            newCmd.CommandText = "SELECT * from  where  Vehicle_No ='" + Vehicle+ "'";

            SqlDataAdapter da = new SqlDataAdapter(newCmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            conn.Close();
            return dt;
    }

Above code segment read data from table and return as Datatable Then tell how to assign this datatable to List<> in Code behind or else tell how to read data as List<> from database

List of what? You have to be more specific here. Do you have a class that can map to the results? — rikitikitik, May 23 '13 at 06:08
If you have a DataTable, you can convert it to list as: Datatable.AsEnumerable().ToList(); — Janman, May 23 '13 at 06:09
Have a look at this link --> http://stackoverflow.com/questions/1427484/convert-datatable-to-listt ...and Darin's answer — deostroll, May 23 '13 at 06:11

Darin Dimitrov · Answer 1 · 2013-05-23T06:13:35.020

Contrary to DataTable, List<T> is a strongly typed generic collection. So you could start by defining a model that will represent each element of this collection:

public class Vehicle
{
    public int Number { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }
    ...
}

and then:

public List<Vehicle> Vehiclelist(string Vehicle)
{
    var result = new List<Vehicle>();

    using (SqlConnection conn = new SqlConnection("PUT YOUR CONNECTION STRING HERE"))    
    using (SqlCommand cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = "SELECT * FROM Vehicles WHERE Vehicle_No = @Vehicle_No";
        cmd.Parameters.AddWithValue("@Vehicle_No", Vehicle);
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                result.Add(new Vehicle
                {
                    Number = reader.GetInt32(reader.GetOrdinal("Vehicle_No")),
                    Make = reader.GetString(reader.GetOrdinal("Vehicle_Make")),
                    Model = reader.GetString(reader.GetOrdinal("Vehicle_Model"))
                });
            }
        }
        return result;
    }
}

Also notice the usage of a parametrized query to avoid the SQL injection that your code was vulnerable to.

And to simplify the process of mapping the database columns to CLR types you may consider using an ORM such as Dapper or the ADO.NET Entity Framework.

Dimitar Dimitrov · Answer 2 · 2013-05-23T06:17:56.850

Do you mean something like this:

var results = dt.AsEnumerable()
                .Select(row => new {VehicleNo = row["Vehicle_No"].ToString()})
                .ToList();

This will get you a list (it will be of anonymous type), however if you have a concrete type that you can map to, you can instantiate it as well:

List<Vehicle> results = dt.AsEnumerable()
                          .Select(row => new Vehicle {VehicleNo = row["Vehicle_No"].ToString()})
                          .ToList();

score 0 · Answer 3 · answered May 23 '13 at 06:15

0

List list = dt.AsEnumerable().ToList();

answered May 23 '13 at 06:15

Qasim Javaid Khan

650
4
10

How to assign Datatable to List<> in C#?

3 Answers3