I’m building an application that manages employee and inventory information. I’m using WinForms, SQLServer, and Dapper. (I know, Winforms is dead, etc. This is largely a learning project, so the next time around I’ll use a MVVM UI and develop that skill). Anyway, my plan was to query all employees when the Employee tab is initialized and display the employees in a sortable DataGridView in the top left quadrant of the application, where there are boolean columns indicating status of certain trainings and licensure.
There would be a click-event on each row which would retrieve the row number, then use that row number to get the EmployeeID (the sql key), and then display that employee’s information in list boxes and text boxes, such as Email addresses, phone numbers, Issued Equipment, Vehicle history, etc. List<EmailModel> and List<PhoneModel> are EmployeeModel properties. As for issued equipment and vehicle history, I get these using many-to-many relationship tables in SQL. My plan is to use Dapper to execute a stored procedure to retrieve this information and LINQ to aggregate the objects. I’m having a little bit of trouble getting the mapping right, though. But I’m almost there.
I’m wondering, however, if my approach violates any significant design principles. Would it be better to perform individual queries for each listbox and textbox? Or is there another way to do it that I haven’t thought of?
You probably want to try and keep the network calls to a minimum. I would not recommend making a database query for each control that you want to update. Your approach to using Stored Procedures to generate an entity that you can map via LINQ seems to be sound. As opposed to event binding on the row clicks, I would suggest taking advantage of the DataGridView.SelectionChanged event.
Overall, it sounds like you have a good plan though. Is there any particular reason that you are using Dapper as opposed to writing the stored procedures in TSQL yourself and then using Winforms LinqToSql to manage your data context?
In addition, the idea of having your stored procedure return an entity that operates similarly to a ViewModel will greatly help your transition into MVVM from Winforms.
Is there any particular reason that you are using Dapper as opposed to writing the stored procedures in TSQL yourself and then using Winforms LinqToSql to manage your data context?
Mainly because I didn’t know that was an option. I haven’t found that video tutorial yet. Can you say just a little bit more about that process, just to point me in the right direction?