question

Dmitriy-2994 avatar image
0 Votes"
Dmitriy-2994 asked karenpayneoregon answered

How to connect .mdf file to a WPF app as a data source?

I am supposed to create a WPF app with some CRUD functionality where the data comes from a .mdf file. I have a folder Books which sits in my C Drive and it contains two files: Books.mdf and Books_log.ldf I have created an rudimentary (no real functionality) WPF app called BooksApp. What is the proper way of connecting my BooksApp to the Books.mdf so I can perform the CRUD operations through my BooksApp? As far as I know, the Books.mdf file contains at least one table "Books" with the columns "Book ISBN", "Book Title", "Authors", "Publication date" and "Edition".

I am using MSN Visual Studio 2019.

dotnet-csharpwindows-wpf
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
1 Vote"
karenpayneoregon answered

Please note, I don't have a pre-done code sample and if the following is acceptable you will need to study up on what's presented below.

Keep it simple, create a class project with data operations.

Two directions,

  • Use SqlClient data provider

  • Use Entity Framework Core

In either case, setup classes which represent each table in a database.

Configure them to implement INotifyPropertyChanged interface so that when you data bind to controls in a WPF window the data is notified of the change so now you can use the modified (or deletes or added) data to save back to the database.

Example class

 public class ExcelItem : INotifyPropertyChanged
 {
     private string _column1;
     private int _column2;
    
     public string Column1
     {
         get => _column1;
         set
         {
             _column1 = value;
             OnPropertyChanged();
         }
     }
    
     public int Column2
     {
         get => _column2;
         set
         {
             _column2 = value;
             OnPropertyChanged();
         }
     }
    
     public event PropertyChangedEventHandler PropertyChanged;
     protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
     {
         PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
     }
 }

I recommend using Entity Framework Core combined with WPF as CRUD is built-in while a data provider you need to write each action..

If you go with SqlClient see the following for a connection string (EF Core does this for you)

In regards to data binding, the following allows a Grid to auto update, there are several ways to set the data source, in this case via the DataContext of the window done in code behind then properties from the class (such as the one above) are bound to controls.

 <StackPanel Orientation="Vertical">
     <DataGrid x:Name="EmployeeGrid"
         Height="162"
         Margin="0,5,0,15"
         AutoGenerateColumns="False"
         CanUserAddRows="False" 
         RowEditEnding="EmployeeGrid_OnRowEditEnding" 
         CommandManager.PreviewCanExecute="Grid_PreviewCanExecute">
    
         <DataGrid.Columns>
    
             <DataGridTemplateColumn>
    
                 <DataGridTemplateColumn.CellTemplate>
                     <DataTemplate>
                         <Button
                             Width="55"
                             Click="ViewCurrentEmployee"
                             Content="View" />
                     </DataTemplate>
                 </DataGridTemplateColumn.CellTemplate>
             </DataGridTemplateColumn>
    
             <DataGridTextColumn
                 Width="*"
                 Binding="{Binding FirstName, ValidatesOnDataErrors=True, UpdateSourceTrigger=PropertyChanged}"
                 Header="First" />
    
             <DataGridTextColumn
                 Width="*"
                 Binding="{Binding LastName, ValidatesOnDataErrors=True, UpdateSourceTrigger=PropertyChanged}"
                 Header="Last" />
    
         </DataGrid.Columns>
    
    
     </DataGrid>
    
     <Grid Height="32">
         <Grid.ColumnDefinitions>
             <ColumnDefinition Width="50" />
             <ColumnDefinition Width="217" />
             <ColumnDefinition Width="98" />
             <ColumnDefinition Width="195" />
         </Grid.ColumnDefinitions>
    
    
         <Label
             Grid.Column="0"
             Height="26"
             Margin="5,0,0,0"
             VerticalAlignment="Center"
             Content="Search" />
    
         <TextBox x:Name="LastNameSearchTextBox"
             Grid.Column="1"
             Width="92"
             Height="22"
             Margin="11,2,0,2"
             HorizontalAlignment="Left"
             VerticalAlignment="Center"
             Text=""
             TextChanged="LastNameSearchTextBox_TextChanged"
             TextWrapping="Wrap" />
    
         <Button x:Name="SaveButton"
             Grid.Column="1"
             Grid.ColumnSpan="2"
             Width="70"
             Height="27"
             Margin="162,3,0,2"
             HorizontalAlignment="Left"
             Click="SaveChangesButton_Click"
             Content="Save"
             IsEnabled="False" />
    
    
         <Button
             Grid.Column="2"
             Grid.ColumnSpan="2"
             Width="70"
             Height="27"
             Margin="0,3,194,2"
             HorizontalAlignment="Right"
             Click="CloseButton_Click"
             Content="Exit" />
    
     </Grid>
     <Button
         Height="27"
         Margin="60,10,241,0"
         Command="{x:Static local:MainWindow.IterateRoutedCommand}"
         Content="Iterate" />
    
 </StackPanel>



Suppose I did the above with a class Employee and had a List of them the data content is set and we get notification in the event OnRowEditEnding, So below the user finished editing a cell and we save it immediately.


 private void EmployeeGrid_OnRowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
 {
    
     if (e.EditAction == DataGridEditAction.Commit)
     {
         _context.SaveChanges();
     }
    
 }




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Edge-6488 avatar image
2 Votes"
Edge-6488 answered Dmitriy-2994 commented

Here is one example for you to learn from :
https://www.youtube.com/watch?v=_1Hdk5pi8C4

Rgds, Edge.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks, but creating a new Database file from scratch is not exactly the same as what I need - to connect an existing database file to my WPF app.

0 Votes 0 ·