Using LINQ to SQL Classes with WPF Flexgrid and MVVM

Using LINQ, along with an SQL Database, can make binding the Flexgrid to your data extremely painless. In this instance, I will use my local SQL “cars” database (SQLExpress) and I will be working with strictly one table which is named carModels, shown here. Let’s begin by adding a Flexgrid to your XAML, by dragging the Flexgrid out of the toolbox and onto the MainWindow. Your XAML will look like this…

 <Grid>  

     <c1:C1FlexGrid x:Name="_fg" HorizontalAlignment="Left" Height="261" Margin="31,53,0,0" VerticalAlignment="Top" Width="458"/>  

 </Grid>  

Right click on your Project, and Add a New Item. Under the Data option, we will want to select LINQ to SQL classes. I will name mine carsClasses.dbml. You will now be presented with the Object Relational Designer. In your Server Explorer, drill down to the table(s) that you want to work with. And drag and drop the table onto the designer. This will create an entity class, as well as the DataContext which we can use to communicate between the grid and the table. For further explanation of this Designer, you can check out the MSDN. Now, we will move back over to the code behind of the MainWindow. We will declare a new DataContext:

public MainWindow()  
{  
    InitializeComponent();  

    carsClassesDataContext dc = new carsClassesDataContext();  
}  

From here, we will be able to run a query for anything within the DataContext. Since my DataContext only has one entity class (carModels), we will query as such:

 public MainWindow()  
 {  
     InitializeComponent();  

     carsClassesDataContext dc = new carsClassesDataContext();  
     var query = from s in dc.carModels select s;  

 }  

The query, just like a normal SQL query, will return information from the entity class. Since you can use a list to populate the Flexgrid, you can simply bind the query results to the Flexgrid’s Itemsource now, and you will see data.

 public MainWindow()  
 {  
     InitializeComponent();  

     carsClassesDataContext dc = new carsClassesDataContext();  
     var query = from s in dc.carModels select s;  
     _fg.ItemsSource = query.ToList();  

 }  

Let’s take it one step further to make the data a little bit easier to work with in the grid by converting the query results into a ListCollectionView.

 public MainWindow()  
 {  
     InitializeComponent();  

     carsClassesDataContext dc = new carsClassesDataContext();  
     var query = from s in dc.carModels select s;  
     ListCollectionView view = new ListCollectionView(query.ToList());  

 }  

The ListCollectionView will open up even more options for customizing your view. We can add Grouping to the grid with the following:

view.GroupDescriptions.Add(new PropertyGroupDescription("make"));  

and binding the ItemSource back to the view.

 public MainWindow()  
 {  
     InitializeComponent();  

     carsClassesDataContext dc = new carsClassesDataContext();  
     var query = from s in dc.carModels select s;  
     ListCollectionView view = new ListCollectionView(query.ToList());  
     view.GroupDescriptions.Add(new PropertyGroupDescription("make"));  
     _fg.ItemsSource = view;  

 }  

Terrific! Before we wrap up, let’s make the grid look a little prettier. Once the ItemSource of the Flexgrid is set, we can use MVVM to customize the grid columns and binding. Let’s switch back to the XAML view, select the Flexgrid, and within the Options, choose the Columns Collection ellipses. Within the Columns collection editor, you can add several customized columns to your grid, and give them each a header title. Click OK, and you will see several columns added to the XAML.

        <c1:C1FlexGrid x:Name="_fg" HorizontalAlignment="Left" Height="261" Margin="31,53,0,0" VerticalAlignment="Top" Width="458">  
            <c1:C1FlexGrid.Columns>  
                <c1:Column Header="Make"/>  
                <c1:Column Header="Model"/>  
                <c1:Column Header="Horsepower"/>  
                <c1:Column Header="Engine"/>  
                <c1:Column Header="Doors"/>  
                <c1:Column Header="Year"/>  
            </c1:C1FlexGrid.Columns>  
        </c1:C1FlexGrid>  

We can now bind each column to their specific class.

        <c1:C1FlexGrid x:Name="_fg" HorizontalAlignment="Left" Height="261" Margin="31,53,0,0" VerticalAlignment="Top" Width="458">  
            <c1:C1FlexGrid.Columns>  
                <c1:Column Header="Make" Binding="{Binding make}"/>  
                <c1:Column Header="Model" Binding="{Binding model}"/>  
                <c1:Column Header="Horsepower" Binding="{Binding hp}"/>  
                <c1:Column Header="Engine" Binding="{Binding engine}"/>  
                <c1:Column Header="Doors" Binding="{Binding doors}"/>  
                <c1:Column Header="Year" Binding="{Binding year}"/>  
            </c1:C1FlexGrid.Columns>  
        </c1:C1FlexGrid>  

Be sure to also disable the AutoGenerateColumns property of the Flexgrid. This will generate the columns directly from the ItemSource with no binding or customization attached (just like we saw before). Give it one more run. Using SQL to LINQ classes can be a very simple way for you to get up and running using the Flexgrid, and the ListViewCollection enables you to do useful customization with very little coding. For more on Creating LINQ to SQL Classes, please refer to this MSDN Tutorial.