SQLDataAdapter to Class in C#

Question

I have here my code for a SQL Query in C#.

string user_select()
{
    SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\avasi\Desktop\PROIECTE_USV\FoodKing_PC\FoodKing_PC\SQL_Database\fkDatabase.mdf;Integrated Security=True;Connect Timeout=30");
    SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM tabel_utilizatori WHERE user_name ='" + this.user_name + "' AND user_password ='" + this.user_password + "'", con);
    DataTable dt = new DataTable();
    sda.Fill(dt);

}

Can you help me to move the data from that SQLDataAdapter to the data members of this class?

    class utilizator
    {
        int user_id;
        string user_name;
        string user_password;
        string user_email;
        string user_phone;
        string user_avatar;
        bool user_firstlogin;
        int user_type;
}

I wouldn't user SqlDataAdapter nor DataTable. See https://stackoverflow.com/questions/41040189/fastest-way-to-map-result-of-sqldatareader-to-object — Ole EH Dufour, May 18 '18 at 12:51
Think about it. The SqlDataAdapter loops over your data to fill a DataTable, now you need to loop over the DataTable rows to create a list of your class objects. You could use the SqlDataReader instead and remove a loop, or better study how to use an ORM to remove all the low level code that interacts with the database (I suggest Dapper) — Steve, May 18 '18 at 12:55

score 0 · Answer 1 · answered May 18 '18 at 12:55

Use can do something like this

var ListofUtilizator= dt.AsEnumerable().Select(r =>
    new utilizator
    {
        user_id= r.Field<int>("user_id"),
        user_name= r.Field<string>("user_name")
    }).ToList();

In C# use Utilizator Capital letter for the classname

score 0 · Accepted Answer · answered May 18 '18 at 12:59

@user9811734, below is the method which convert your data table to list of your class :

private static List<T> ConvertDataTable<T>(DataTable dt)  
{  
    List<T> data = new List<T>();  
    foreach (DataRow row in dt.Rows)  
    {  
        T item = GetItem<T>(row);  
        data.Add(item);  
    }  
    return data;  
}

to make a call use :

utilizator entityutilizator = new utilizator();
entityutilizator = ConvertDataTable(#your datatable#); -- here as per your code it's 'dt'

SQLDataAdapter to Class in C#

2 Answers2