Build a CRUD REST API with JSON Data Connectors for ADO.NET & Entity Framework

REST (Representational State Transfer) is an architectural style that defines a set of constraints to be used for creating web services. REST API is a way of accessing web services in a simple and flexible way without having any processing. One can also perform CRUD (Create, Read, Update, Delete) operations over RESTful API Services.

In this blog, we will perform CRUD operations over RESTful API using ComponentOne JSON DataConnectors, which are part of the Data Services Edition product. The JSON DataConnectors for ADO.NET and Entity Framework Core can be used for effective data connectivity built over ADO.NET architecture. They are high-performance data providers that enable a common interface for accessing different kinds of data sources based on established data-access technologies. The steps to enable CRUD operations in a desktop .NET application are as follows:

1. Create the XAML UI to Display Data
2. Create the Web API Configuration
3. Connect the FlexGrid to JSON Data using ADO.NET

Ready to Test it Out? Download ComponentOne Today!

Create the XAML UI to Display Data

Here, we will use the FlexGrid for WPF control to visualize our CRUD operations. The WPF FlexGrid is a datagrid control designed to showcase tabular/object model data. Add the FlexGrid in XAML so that it will be visible on the UI. Set ItemsSource of FlexGrid and bind the columns with the respective properties.

<Grid>
        <Grid.RowDefinitions>
            <RowDefinition Height="auto"></RowDefinition>
            <RowDefinition></RowDefinition>
        </Grid.RowDefinitions>
 
        <WrapPanel>
            <Button x:Name="_btnSyncChanges"
                    Background="White"
                    Cursor="Hand"
                    Width="200"
                    Content="Click here to Sync Changes with API"
                    Click="OnSyncChanges"></Button>
        </WrapPanel>
 
        <Grid x:Name="_syncView"
              Background="Black"
              Opacity="0.8"
              Grid.RowSpan="2"
              Visibility="Collapsed"
              Panel.ZIndex="1">
            <TextBlock HorizontalAlignment="Center"
                       FontSize="16"
                       Foreground="White"
                       VerticalAlignment="Center">Syncing...</TextBlock>
        </Grid>
 
        <c1:FlexGrid x:Name="_flexGrid" Margin="5, 0, 5, 5"
                     SelectionForeground="White"
                     SelectionBackground="Purple"
                     NewRowPosition="Bottom"
                     NewRowPlaceholder="Click here to add new product"
                     AutoGenerateColumns="False"
                     ItemsSource="{Binding Products}" Grid.Row="1">
            <!--Add Columns to the FlexGrid and bind these to the Product properties-->
            <c1:FlexGrid.Columns>
                <c1:GridColumn Binding="id" IsReadOnly="True" ColumnName="id" Width="*"></c1:GridColumn>
                <c1:GridColumn Binding="name" ColumnName="name" Width="*"></c1:GridColumn>
                <c1:GridColumn Binding="color" ColumnName="color" Width="*"></c1:GridColumn>
                <c1:GridNumericColumn Binding="price" Format="N2" ColumnName="price" Width="*"></c1:GridNumericColumn>
                <c1:GridColumn Binding="category" ColumnName="category" Width="*"></c1:GridColumn>
            </c1:FlexGrid.Columns>
        </c1:FlexGrid>
    </Grid>

Create the Web API Configuration

To establish a connection to a JSON data source using the C1JsonConnection class. It requires a connection string to be provided as an argument. To connect to a local file, you can create a connection string by setting the Uri property to JSON file in addition to the DataModel and JsonPath properties.
Create an App.config file to store the Connection strings for the RESTful APIs. Add a new Application Configuration File in the project with the below ConnectionStrings:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="GetProducts" connectionString="Data Model=Document; Uri='https://localhost:44321/products'; Json Path='$.products'"></add>
    <add name="AddUpdateProduct" connectionString="Data Model=Document; Uri='https://localhost:44321/products'; api config file='api_config.xml'; Json Path='$.products'"></add>
  </connectionStrings>
</configuration>

Using a configuration file, you can also set the APIConfigFile property in ConnectionString to support CRUD operations for JSON sources. We must have a Configuration file. You need to create an XML file in the project for API configurations of the AddUpdateProduct connection string. To create a configuration for the HTTP JSON source, you need to configure the schema. Each schema should be separated into four sections: SelectOperation, InsertOperation, UpdateOperation, and DeleteOperation.

<Api_Config>
    <Table name="products">
    <SelectOperation>
      <Uri>https://localhost:44321/products</Uri>
      <Method>Get</Method>
      <Response>
        <TableName name="products" type="string"/>
        <Column name="id" isKey="true" type="int">id</Column>
        <Column name="name" type="string">name</Column>
        <Column name="color" type="string">color</Column>
        <Column name="category" type="string">category</Column>
        <Column name="price" type="double">price</Column>
      </Response>
    </SelectOperation>
        <InsertOperation>
            <Uri>https://localhost:44321/products</Uri>
            <Method>Post</Method>
            <Body>
        <TableName name="products" type="string"/>
        <Column name="id" isKey="true" type="int">id</Column>
        <Column name="name" type="string">name</Column>
        <Column name="color" type="string">color</Column>
        <Column name="category" type="string">category</Column>
        <Column name="price" type="double">price</Column>
            </Body>
        </InsertOperation>
        <UpdateOperation>
            <Uri>https://localhost:44321/products</Uri>
            <Method>PUT</Method>
            <Body>
        <TableName name="products" type="string"/>
        <Column name="id" isKey="true" type="int">id</Column>
        <Column name="name" type="string">name</Column>
        <Column name="color" type="string">color</Column>
        <Column name="category" type="string">category</Column>
        <Column name="price" type="double">price</Column>
            </Body>
        </UpdateOperation>
        <DeleteOperation>
            <Uri>https://localhost:44321/products/{id}</Uri>
            <Method>DELETE</Method>
      <Paramter name="id" type="int">id</Paramter>
        </DeleteOperation>
    </Table>
</Api_Config>

Connect the Flexgrid to JSON Data using ADO.NET

Next, we will connect the Flexgrid to JSON data connectors using ADO.NET to showcase the CRUD operations.

Start by creating a ViewModel class, which acts as the data source and controller for the View. Then, add a DataTable property, which should be bound to FlexGrid’s ItemsSource, and add methods to fetch the data from API using C1JsonDataAdapter with the help of the “GetProducts” connection string. Now, we can Read the data from RESTful API on FlexGrid.

public class ProductsViewModel : INotifyPropertyChanged
    {
        private DataTable _products;
 
        public DataTable Products
        {
            get
            {
                return _products;
            }
            set
            {
                _products = value;
                PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(nameof(Products)));
            }
        }
 
        public event PropertyChangedEventHandler? PropertyChanged;
 
        public async Task LoadProducts()
        {
            Products = await GetProductsTable();
            Products.DefaultView.Sort = "id";
            Products.Columns["id"].AutoIncrement = true;
            Products.Columns["id"].AutoIncrementSeed = Convert.ToInt64(Products.Compute("max([id])", string.Empty)) + 1;
            Products.Columns["id"].AutoIncrementStep = 1;
            Products.Columns["id"].Unique = true;
        }
        private async Task<DataTable> GetProductsTable()
        {
            var connString = ConfigurationManager.ConnectionStrings["GetProducts"].ConnectionString;
            using (var connection = new C1JsonConnection(connString))
            {
                await connection.OpenAsync();
                using (var adapter = new C1JsonDataAdapter(connection, "Select * from products"))
                {
                    var productsTable = new DataTable();
                    adapter.Fill(productsTable);
                    return productsTable;
                }
            }
        }
    }
}

Next, we will implement the Create, Update, and Delete operations. So, the changes made on the UI (Flexgrid) modify the API. Create a Sync() method in the ViewModel class to implement Create, Update, and Delete operations:

public async Task Sync()
        {
            var connString = ConfigurationManager.ConnectionStrings["AddUpdateProduct"].ConnectionString;
            using (var connection = new C1JsonConnection(connString))
            {
                await connection.OpenAsync();
                using (var adapter = new C1JsonDataAdapter())
                {
                    //Create
                    adapter.InsertCommand = new C1JsonCommand(connection);
                    adapter.InsertCommand.CommandText = "Insert into products (name, color, price, category) values (@Name, @Color, @Price, @Category);";
                    adapter.InsertCommand.Parameters.Add("@Name", "name");
                    adapter.InsertCommand.Parameters.Add("@Color", "color");
                    adapter.InsertCommand.Parameters.Add("@Price", "price");
                    adapter.InsertCommand.Parameters.Add("@Category", "category");
                    adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
 
                    //Update
                    adapter.UpdateCommand = new C1JsonCommand(connection);
                    adapter.UpdateCommand.CommandText = "Update products set name=@Name, color=@Color, price=@Price, category=@Category where id=@Id";
                    adapter.UpdateCommand.Parameters.Add("@Id", "id");
                    adapter.UpdateCommand.Parameters.Add("@Name", "name");
                    adapter.UpdateCommand.Parameters.Add("@Color", "color");
                    adapter.UpdateCommand.Parameters.Add("@Price", "price");
                    adapter.UpdateCommand.Parameters.Add("@Category", "category");
                    adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
 
                    //Delete
                    adapter.DeleteCommand = new C1JsonCommand(connection);
                    adapter.DeleteCommand.CommandText = "Delete from products where id=@Id";
                    adapter.DeleteCommand.Parameters.Add("@Id", "id");
                    adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
 
                    adapter.Update(Products);
                }
            }
        }

Next, we can add a button to the UI to sync all changes. This will Fetch all change(s) and notify the user about it. Then, call the Sync() method of ViewModel so that all the changes are updated to RESTful APIs.

private async void OnSyncChanges(object sender, RoutedEventArgs e)
{
    try
    {
        await _flexGrid.FinishRowEditing(false);
        var changes = _viewModel.Products.GetChanges();
 
        if (changes == null)
        {
            MessageBox.Show("No changes to sync", "No changes", MessageBoxButton.OK, MessageBoxImage.Information);
            return;
        }
        _syncView.Visibility = Visibility.Visible;
        await Task.Delay(500); //simulate delay
 
        string message = "Sync Complete.\n";
        //Check for Deleted Rows
        var deletedRows = changes.AsEnumerable().Where(x => x.RowState == DataRowState.Deleted).ToList();
        if (deletedRows.Count > 0)
            message += $"Deleted {deletedRows.Count} row(s).\n";
        //Check for Added Rows
        var addedRows = changes.AsEnumerable().Where(x => x.RowState == DataRowState.Added).ToList();
        if (addedRows.Count > 0)
            message += $"Added {addedRows.Count} row(s).\n";
        //Check for Modified Rows
        var modifiedRows = changes.AsEnumerable().Where(x => x.RowState == DataRowState.Modified).ToList();
        if (modifiedRows.Count > 0)
            message += $"Modified {modifiedRows.Count} row(s).";
         
        await _viewModel.Sync();
         
        _syncView.Visibility = Visibility.Collapsed;
        MessageBox.Show(message, "Sync Successfull", MessageBoxButton.OK, MessageBoxImage.Information);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Sync Error", MessageBoxButton.OK, MessageBoxImage.Error);
    }
}

That's it!

Now, the WPF application is ready. You can edit, add, or remove data from Flexgrid, and it will reflect to the RESTful Services after the data is synchronized, as shown in the below GIF.

For full implementation, you can download the sample.

Alternately, Using EntityFramework Core for CRUD Operations

We used the standard ADO.NET approach above. Let's see how the same functionality works using Entity Framework Core. This section will teach us to perform CRUD operations using EntityFramework Core.

First, create a ProductContext class derived from DbContext for the EntityFramework. It represents a session with the underlying database where you can perform CRUD (Create, Read, Update, Delete) operations.

Then, add a DbSet<TEntity> property, which should be bound to the FlexGrid’s ItemsSource. Override Methods of DbContext to initialize and configure Entity with the help of the “AddUpdateProduct” connection string. Now, we can read the data from RESTful API on FlexGrid and Modify RESTful API data by saving the changes on DbContext.

public partial class ProductContext : DbContext
    {
        public virtual DbSet<Product> Products { get; set; }
 
        public ProductContext()
        {
            Database.AutoTransactionsEnabled = false;
        }
 
        public ProductContext(DbContextOptions<ProductContext> options)
            : base(options)
        {
            Database.AutoTransactionsEnabled = false;
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseJson(ConfigurationManager.ConnectionStrings["AddUpdateProduct"].ConnectionString);
            }
        }
 
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Product>(entity =>
            {
                 
                entity.ToTable("products");
                entity.HasChangeTrackingStrategy(ChangeTrackingStrategy.Snapshot);
                entity.HasKey(x => x.Id);
                entity.Property(e => e.Id).HasColumnName("id");
                entity.Property(e => e.Name).HasColumnName("name");
                entity.Property(e => e.Color).HasColumnName("color");
                entity.Property(e => e.Category).HasColumnName("category");
                entity.Property(e => e.Price).HasColumnName("price");
            });
        }
    }

Next, handle the Click event of the button, which is placed in the XAML designer to sync all changes. Fetch all change(s) and notify the user about it. Then, call the SaveChangesAsync() method of DbContext so that all the changes are updated to RESTful APIs.

private async void OnSyncChanges(object sender, RoutedEventArgs e)
{
    try
    {
        await _flexGrid.FinishRowEditing(false);
        var change = _context.ChangeTracker.DebugView;
 
        _syncView.Visibility = Visibility.Visible;
        await Task.Delay(500); //simulate delay
 
        string message = "Sync Complete.\n";
        //Check for Deleted Rows
        var deletedRows = change.ShortView.Split("\r\n").Where(x=>x.Contains("Deleted"));
        if (deletedRows.Count() > 0)
            message += $"Deleted {deletedRows.Count()} row(s).\n";
        //Check for Added Rows
        var addedRows = change.ShortView.Split("\r\n").Where(x => x.Contains("Added"));
        if (addedRows.Count() > 0)
            message += $"Added {addedRows.Count()} rows.\n";
        //Check for Modified Rows
        var modifiedRows = change.ShortView.Split("\r\n").Where(x => x.Contains("Modified"));
        if (modifiedRows.Count() > 0)
            message += $"Modified {modifiedRows.Count()} rows.\n";
        await _context.SaveChangesAsync();
        _syncView.Visibility = Visibility.Collapsed;
 
        if (deletedRows.Count() == 0 & addedRows.Count() == 0 & modifiedRows.Count() == 0)
        {
            MessageBox.Show("No changes to sync", "No changes", MessageBoxButton.OK, MessageBoxImage.Information);
            return;
        }
 
        MessageBox.Show(message, "Sync Successfull", MessageBoxButton.OK, MessageBoxImage.Information);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Sync Error", MessageBoxButton.OK, MessageBoxImage.Error);
    }
}

And that's it! Notice how much less code is necessary. For full implementation, you can download the sample.
Next, you can try the same implementation with FlexGrid for WinForms and let us know about your experience.

Ready to Test it Out? Download ComponentOne Today!