Bind DataGridView with FK (Master-Slave architecture), but allowing column customization at VS Edito-CodePudding

The DataModel at the DB implements a Master-Slave architecture which is presented at the system with a WinForm with several controls for the MasterTable, and a Datagridview for the Slave one. All the controls for MasterTable fields' are binded through a BindingSource component, and the Datagridview for the SlaveTable uses its own BingindSource for the Fk constraint; by doing so, the user can navigate through the MasterTable's records via the MasterTable's Binding "position" in such a way that the main controls are update with the record's data, and the Datagridview is as well updated based on the MasterTable's Pk, thanks to the Fk constraint (implemented as a DataRelation and added to the MasterTable's DataSet and, for consequence, present at it's BindingSource).

Looking at the web and the forums (like here), I've found that the main controls could be binded programmatically with DataBinding.Add() if I wish to, i.e., give format to the text displayed at the Textboxes, as follows:

......

mainBind = new BindingSource();
slaveBind = new BindingSource();

mainBind.DataSource = mainDS;
mainBind.DataMember = "MasterTable";
slaveBind.DataSource = mainBind;
slaveBind.DataMember = "idFk"; //defined as DataRelation between Master-Slave tables

DataGrid.DataSource = slaveBind; 

Text1.DataBindings.Add("Text", mainBind, "field1", true, DataSourceUpdateMode.OnPropertyChanged, 19, "$##.00");                     
Text2.DataBindings.Add("Text", mainBind, "field2", true, DataSourceUpdateMode.OnPropertyChanged, DateTime.Now, "dd/MM/yyyy");

.....

With this approach, the system works as intended.

Now, as far as I've understood, one cannot avoid to add each control programmatically to it's BindingSource if you are not willing to use, for example, a DataSource via the wizard (or the Entity Framework). Is this true?

By the other hand (which follows my real question here), by binding the Datagridview to the slaveBind and it's Fk's member, all the fields from the SlaveTable are actually loaded and displayed so, If I need to manipulated them (like make some columns not visible), I need to either modify the SELECT clause or programmatically modify each column (which include things like alignment or display format), as follows:

.....

DataGrid.Columns[0].Visible = false; //field1
DataGrid.Columns[1].Visible = false; //field2
DataGrid.Columns[2].Visible = false; //field3

DataGrid.Columns[3].Width = 184; //field4
DataGrid.Columns[3].DefaultCellStyle.Format = "N1";

.....

So, if I would like another approach like to add myself the columns to the Datagridview via the VS editor and then bind them, I need to remove the fk's binding source for it to work (if not, the automatic data load prevails); by consequence, I loose as well the possibility to keep tied the MasterTable's Pk to the data displayed at the Datagridview.

Is it a way to avoid to custom each Datagridview's column programmatically in this scenario?

Thanks a lot in advanced for anyone willing to support this issue.

p.s. I saw a video in YouTube not long time ago which implemented the Master-Slave architecture by cleaning and refilling the Datagridview each time the MasterTable records were traversed: is this the only solution around?

CodePudding user response：

So, after looking for something somehow related but not exactly my issue (I was trying to configure my first Datagridview's column as a ComboBoxColumn - (source1)(source2) -), I've found out that it is possible to add Columns at the VS Designer and after you've configured all the Columns at the Designer (like number format, text alignment, etc), you can Bind each Column programmatically and, just then, you can Bind the Datagridview with the Fk's BindingSource (p.s. it is important to set the Datagridview's AutoGenerateColumns property = false).

In other words; when the slaveBind becomes the DatagridView's DataSource, it allows the DatagridView to see all the fields for both the MasterTable's and the SlaveTable's ones! (thanks to it's DataRelation member - aka, the Fk -), and, at the same time, keeps the Datagridview's data tied to the MasterTable's Pk, which is the main behavior that is being looked for.

So, for the sake of completion, here it goes the code that I used:

DataGridViewComboBoxColumn dgComboBox = new DataGridViewComboBoxColumn();

DataRelation relMPkToSId = new DataRelation("idFk", mainDS.Tables["MasterTable"].Columns["id_pk"], slaveDS.Tables["SlaveTable"].Columns["idPk"]);
mainDS.Relations.Add(relMPkToSId);

mainBind = new BindingSource();
slaveBind = new BindingSource();

mainBind.DataSource = mainDS;
mainBind.DataMember = "MasterTable";
slaveBind.DataSource = mainBind;
slaveBind.DataMember = "idFk"; //defined as DataRelation between Master-Slave tables

//Bind the Form's controls to the MasterTable's fields
myText1.DataBindings.Add("Text", mainBind, "masterField1", true, DataSourceUpdateMode.OnPropertyChanged, 19, "$##.00");                     
myText2.DataBindings.Add("Text", mainBind, "masterField2", true, DataSourceUpdateMode.OnPropertyChanged, DateTime.Now, "dd/MM/yyyy");

.....

myDataGrid.AutoGenerateColumns = false; //important
// configure ComboBoxColumn
dgComboBox.DataSource = comboDS.Tables["myCatalog1"]; //another DS which provides the ComboBox datalist
dgComboBox.DisplayMember = "displayName";
dgComboBox.ValueMember = "internal_id";
dgComboBox.HeaderText = "Title:";
dgComboBox.DataPropertyName = "slaveField1";  //target field at SlaveTable where the the selected ComboBox value is stored
myDataGrid.Columns.Insert(0, dgComboBox); //insert the ComboBoxColumn at the Datagridview's first position
myDataGrid.Columns[0].Name = "dgColumn1"; //give the ComboBoxColumn a reference name
myDataGrid.Columns["dgColumn1"].Width = 184; //configure the ComboBox's width                    
myDataGrid.Columns["dgColumn1"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft; //configure the ComboBox's alignment

//Bind the rest of the columns generated at the VS Designer
myDataGrid.Columns["column2"].DataPropertyName = "slaveField2";
myDataGrid.Columns["column3"].DataPropertyName = "slavefield3";

.....

myDataGrid.DataSource = slaveBind;

I hope this result will be of help for others in my same scenario. Regards,