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

Question

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.

Answer 1

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();
    }

}

Answer 2

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

Rgds, Edge.