Question on converting a DataGridViewRow to a custom object

Question

Question on converting a DataGridViewRow to a custom object

Min T 81

so im working on a winform with a datagridview.

im filling that DGV with a sql call to a table FOO which has 50 columns in it.

public DataTable GetItemDBTable()
        {
            CGenericDBSQLAccessAdapter dataAdapter = new CGenericDBSQLAccessAdapter(true);
            dataAdapter.conn_string = CCommon.m_AppConfig.str_db_connect;
            DataSet ds = new DataSet();

            int n_Count = dataAdapter.RunQueryCommand("SELECT * FROM FOO");

            return dataAdapter.m_sql_tbl;
        }




dataGridView1.DataSource = _controller.GetItemDBTable();

the DGV itself will only show about 7 of those columns, so i have the columns added to the Columns collection through the designer and set

dataGridView1.AutoGenerateColumns = false;

so i can show what columns i want with specific column names.

what id like to do is when a user picks a row, i want to pull that data into a class model. this forum post looks close to what i want to achieve that says to cast via the DataBoundItem property of the row similar to this, where FOO is defined as a model for the FOO table:

FOO item = dataGridView1.SelectedRows[0].DataBoundItem as FOO ;

which doesnt seem to work even though the question is similar to mine .

am i interpreting this wrong? can i cast from a row of data into a custom object if the fields match up?

or do i need to do :

sql call
get a reader
loop through and fill a list of objects based on that call
and then set the DGV datasource to that list instead? (similar to this post)

Accepted answer

1 additional answer

Your answer

Answer 1

Once your DataTable has been populated use the following to create a list.

public static class GenericExtensions
{
    public static List<TSource> ToList<TSource>(this DataTable table) where TSource : new()
    {
        List<TSource> list = new List<TSource>();

        var typeProperties = typeof(TSource).GetProperties().Select(propertyInfo => new
        {
            PropertyInfo = propertyInfo,
            Type = Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType
        }).ToList();

        foreach (var row in table.Rows.Cast<DataRow>())
        {

            TSource current = new TSource();

            foreach (var typeProperty in typeProperties)
            {
                if (!table.Columns.Contains(typeProperty.PropertyInfo.Name))
                {
                    continue;
                }
                object value = row[typeProperty.PropertyInfo.Name];
                object safeValue = value is null || DBNull.Value.Equals(value) ?
                    null :
                    Convert.ChangeType(value, typeProperty.Type);

                typeProperty.PropertyInfo.SetValue(current, safeValue, null);
            }

            list.Add(current);

        }

        return list;
    }
}

Example class used for the above which implements change notification as we lose that which a DataTable has.

public class Employee : INotifyPropertyChanged
{
    private int _id;
    private string _firstName;
    private string _lastName;
    private DateTime _hiredDate;

    public int Id
    {
        get => _id;
        set
        {
            if (value == _id) return;
            _id = value;
            OnPropertyChanged();
        }
    }

    public string FirstName
    {
        get => _firstName;
        set
        {
            if (value == _firstName) return;
            _firstName = value;
            OnPropertyChanged();
        }
    }

    public string LastName
    {
        get => _lastName;
        set
        {
            if (value == _lastName) return;
            _lastName = value;
            OnPropertyChanged();
        }
    }

    public DateTime HiredDate
    {
        get => _hiredDate;
        set
        {
            if (value.Equals(_hiredDate)) return;
            _hiredDate = value;
            OnPropertyChanged();
        }
    }

    public override string ToString() => $"{Id,-5}";


    public event PropertyChangedEventHandler PropertyChanged;
    protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }
}

Then in a class load the data

public static List<Employee> ReadEmployees()
{
    DataTable dt = new DataTable();
    using (var cn = new SqlConnection(ConnectionString))
    {
        using (var cmd = new SqlCommand() {Connection = cn})
        {
            cmd.CommandText = "SELECT id, FirstName, LastName, HiredDate FROM dbo.employee;";
            cn.Open();
            dt.Load(cmd.ExecuteReader());

        }
    }

    return dt.ToList<Employee>();
}

Example form code

public partial class Form2 : Form
{
    private BindingList<Employee> _bindingList;
    private readonly BindingSource _bindingSource = new BindingSource();
    public Form2()
    {
        InitializeComponent();

        _bindingList = new BindingList<Employee>(EmployeeOperations.ReadEmployees());
        _bindingSource.DataSource = _bindingList;
        dataGridView1.DataSource = _bindingSource;
        dataGridView1.Columns["id"].Visible = false;

    }

    private void CurrentButton_Click(object sender, EventArgs e)
    {
        Employee employee = _bindingList[_bindingSource.Position];
    }

    private void AllButton_Click(object sender, EventArgs e)
    {
        foreach (var employee in _bindingList)
        {
            Console.WriteLine(employee.FirstName);
        }
    }
}

Is there an easier way? Sure, use Entity Framework Core.

Answer 2

@Min T, Welcome to Microsoft Q&A, based on my test, I reproduced your problem.

I recommend that you use the following code to convert datagridviewrow to a custom object.

First, we need to convert datatable to list.

 private void Form1_Load(object sender, EventArgs e)
        {
            DataTable table = new DataTable();
            table.Columns.Add("ID", typeof(int));
            table.Columns.Add("Name", typeof(string));
            table.Columns.Add("Age", typeof(int));
            table.Columns.Add("Address",typeof(string));
            table.Columns.Add("Description", typeof(string));
            table.Rows.Add(1001, "test1", 23, "ad1", "dr1");
            table.Rows.Add(1002, "test2", 23, "ad1", "dr1");
            table.Rows.Add(1003, "test1", 23, "ad1", "dr1");
            string[] selectedColumns = new[] { "ID", "Name","Age" };
            DataTable dt = new DataView(table).ToTable(false, selectedColumns);
            List<Student> list = ConvertDataTable<Student>(dt);
            dataGridView1.DataSource = list;


        }

Second, we could use DataBoundItem to convert datagridviewrow to custom object.

 private void button1_Click(object sender, EventArgs e)
        {
            Student item = dataGridView1.SelectedRows[0].DataBoundItem as Student;
            MessageBox.Show(item.ID + "*" + item.Name + "*" + item.Age);
       
        }
        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;
        }
        private static T GetItem<T>(DataRow dr)
        {
            Type temp = typeof(T);
            T obj = Activator.CreateInstance<T>();
            foreach (DataColumn column in dr.Table.Columns)
            {
                foreach (PropertyInfo pro in temp.GetProperties())
                {
                    if (pro.Name == column.ColumnName)
                        pro.SetValue(obj, dr[column.ColumnName], null);
                    else
                        continue;
                }
            }
            return obj;
        }
        
             
 public class Student
    {
        public int ID { get; set; }

        public string Name { get; set; }

        public int Age { get; set; }
    }

Tested result:

User's image

Hope my solution could help you.

Best Regards,

Jack

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Min T 81 Reputation points

2023-03-16T12:18:33.05+00:00

~~this doesnt seem to work, its got a compile error saying Cast isnt a method for DataGridViewRow(CS1929).~~ as for the rest, based on your example id have to basically setup a conversion for each field which is what i was hoping to avoid as the model will have around 50 fields with only about 7 being shown in the DGV. but if there is no other way since im not using EF.

EDIT: ive gotten to compile ok . but ive still questions about my other concern, do i need to cast every field in FOO in the Cast method or is there an easier way with this method. else, i feel like its easier to just select from SQL resultset directly into a list of FOO objects and just use that as the datasource for the DGV
Jack J Jun 25,296 Reputation points

2023-03-17T06:54:28.18+00:00

@Min T, thanks for feedback, based on the Microsoft Learning How to: Access Objects Bound to Windows Forms DataGridView Rows, we need to set a connection for the datagridview.DataSource. Then we could use DataBoundItem to get object. I have updated my answer and you could have a check.

Share via

Question on converting a DataGridViewRow to a custom object

1 additional answer

Your answer