asked on

Change column types in a datagridview prgrammaticaly while using a Linq query for the data.

Hello Experts.

I am using a Linq query to get the data to bind to a datagridview as shown below, but the "Admins, Users, Ltd Users, New Users" columns are all boolean and I would like to have those columns be of checkbox type. How do you choose the column type during runtime?

Thanks.

Dim dc As New clsPDAppsDataClassesDataContext
 
        '// Fill the Permissions DataGrdViews
        '//Global
        Dim qPermision = (From p In dc.GetTable(Of sys_Permission)() _
                      Order By p.Name Ascending _
                      Select p).ToList()
 
        With dgvGlobalPermissionsDataGridView
 
            .DataSource = qPermision
 
            .Columns.Item(0).ReadOnly = True
            .Columns.Item(0).HeaderText = "ID"
            .Columns.Item(0).Width = 50
            .Columns.Item(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.None
            .Columns.Item(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns.Item(0).Visible = False
 
            .Columns.Item(1).ReadOnly = True
            .Columns.Item(1).HeaderText = "Name"
            .Columns.Item(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
            .Columns.Item(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
 
            .Columns.Item(2).ReadOnly = True
            .Columns.Item(2).HeaderText = "Admins"
            .Columns.Item(2).Width = 50
            .Columns.Item(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.None
            .Columns.Item(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
 
            .Columns.Item(3).HeaderText = "Users"
            .Columns.Item(3).Width = 50
            .Columns.Item(3).AutoSizeMode = DataGridViewAutoSizeColumnMode.None
            .Columns.Item(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
 
            .Columns.Item(4).HeaderText = "Ltd Users"
            .Columns.Item(4).Width = 50
            .Columns.Item(4).AutoSizeMode = DataGridViewAutoSizeColumnMode.None
            .Columns.Item(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
 
            .Columns.Item(5).ReadOnly = True
            .Columns.Item(5).HeaderText = "New Users"
            .Columns.Item(5).Width = 50
            .Columns.Item(5).AutoSizeMode = DataGridViewAutoSizeColumnMode.None
            .Columns.Item(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
 
            .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .AllowUserToResizeColumns = False
            .AllowUserToResizeRows = False
            .AllowUserToAddRows = False
            .RowHeadersVisible = False
            .AllowUserToOrderColumns = False
            .AllowUserToDeleteRows = False
            .ForeColor = Color.Black
            .BackgroundColor = Color.White
            .DefaultCellStyle.SelectionBackColor = Color.White
            .DefaultCellStyle.SelectionForeColor = Color.Black
            .ColumnHeadersDefaultCellStyle.SelectionForeColor = Color.Black
            .ColumnHeadersDefaultCellStyle.SelectionBackColor = Color.WhiteSmoke
            .ColumnHeadersDefaultCellStyle.ForeColor = Color.Black
            .ColumnHeadersDefaultCellStyle.BackColor = Color.WhiteSmoke
            .AlternatingRowsDefaultCellStyle.BackColor = Color.Cornsilk
            .AlternatingRowsDefaultCellStyle.SelectionBackColor = Color.Cornsilk
 
        End With
        '... End Global Permissions DataGridView

Open in new window

Birky

Hi,

I found it easiest to actually create the columns programatically and then add them to the datagrid. I also had problems with the columns reordering by themselves and this sorted it out.

Hope this helps

Friend WithEvents clnAdmin As System.Windows.Forms.DataGridViewCheckBoxColumn
 
' In a sub like CreateDGVColumns
Me.clnAdmin = New System.Windows.Forms.DataGridViewCheckBoxColumn
With Me.clnAdmin
    .DataPropertyName = "Admin"  'The fieldname to bind to
    .HeaderText = "Admins"
    .Name = "clnAdmin"
    .Width = 75
End With
dgvGlobalPermissionsDataGridView.AutoGenerateColumns = False
dgvGlobalPermissionsDataGridView.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.clnAdmin}

Open in new window

missionarymike

ASKER

Thanks for the reply.
How do I add multiple columns like this that are checkbox style (all except the ID and Name columns are of boolean type)?
How do I add the newly created column to the qPermission query and bind the data?
Mike

ASKER CERTIFIED SOLUTION

Birky

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

missionarymike

ASKER

Ok. That seems pretty straight forward. How can I take the info from the DGV and update back into the DB using Linq? Do I need to loop back through all the columns and update individually or can I perform a mass update. The user wont be able to add records, only change the boolean values of those 4 columns.
Mike

Birky

Hi,

Not too sure. I havent used LINQ much. I mainly use SQL statements with SqlDataAdapters to populate Datatables. With datatables you need to loop through each record and see if it has changed then activate the appropiate command based on wether it is added, modifed or deleted.

' Create a data adapter
Me.prDA = New SqlDataAdapter
With Me.prDA
    .SelectCommand = SelectAll    ' Select SqlCommand
    .InsertCommand = Insert       ' Insert SqlCommand
    .UpdateCommand = Update       ' Update SqlCommand
    .DeleteCommand = Delete       ' Delete SqlCommand
End With
 
' Populate Data table
Me.prDA.SelectCommand.Connection.Open)
Me.prDA.FillSchema(Me.prDT, SchemaType.Mapped)
Me.prDA.Fill(Me.prDT)
 
' Bind Datagrid
Dim BS as New Window.forms.BindingSource
BS.Datasource = Me.prDT
dgvGlobalPermissionsDataGridView.DataSource = BS
 
' Update changes to the data table
Dim DT As DataTable = prDT.GetChanges
If DT IsNot Nothing Then
    ' Loops through all the changes in theis datatable and saves them
    For Each row As DataRow In DT.Rows
        Select Case row.RowState
            Case DataRowState.Added
                With Me.prDA.InsertCommand
                    .Parameters("@Admin").Value = row("Admin") ' Parameters to update
                    .Connection.Open()
                    .ExecuteNonQuery()
                    .Connection.Close()
                End With
            Case DataRowState.Modified
                With Me.prDA.UpdateCommand
                    .Parameters("@Admin").Value = row("Admin") ' Parameters to update
                    .Parameters("@ID").Value = row("ID", DataRowVersion.Original)
                    .Connection.Open()
                    .ExecuteNonQuery()
                    .Connection.Close()
                 End With
             Case DataRowState.Deleted
                 With Me.prDA.DeleteCommand
                    .Parameters("@ID").Value = row("ID", DataRowVersion.Original)
                    .Connection.Open()
                    .ExecuteNonQuery()
                    .Connection.Close()
                End With
        End Select
    Next
    Me.ReBindDataSource()
End If

Open in new window

missionarymike

ASKER

Thanks. I will take that and work with it. It will take me a while, but I will get back with you. Thanks for the quick responses.
Mike

missionarymike

ASKER

I used another option to perform the update.

Thanks.