Working with ADO.NET Data Service in a .NET Framework Client Application

Introduction

In this article we will be looking at how to consume and make use of the ADO.NET Data Service in a .NET framework client application. We will demonstrate the process by creating a simple Web application which will use an existing ADO.NET Data Service. This demo application will involve the basic CRUD operations onto the data source. CRUD stands for Create Read Update and Delete.

If you want to know what an ADO.NET data service is or how to create one, then I would recommend you to go through my previous article “ADO.NET Data Services in the .NET Framework“.

Consuming ADO.NET Data Service

The ADO.NET data service is a specialized WCF service which is a .svc file. Once the data service is hosted then any .NET framework application can consume it, either it could be a console, Windows or Web application. The first step in consuming the ADO.NET data service is to add a service reference. To add a service reference follow the below steps:

  1. Right click on the client project and select “Add a Service Reference”
  2. In the “Add Service Reference” dialog box, provide the URL of the hosted ADO.NET Data Service and click on the “Go” button. This will discover the ADO.NET Data Service. Fig 1.0 shows the sample screenshot:


    Fig 1.0

  3. Click on the OK button. This will create a service reference proxy on the client application project. Note that it would have created a type BarDatabaseEntities deriving from the .NET framework base class DataServiceContext. This class will be used to perform the CRUD operations.

BarDataService — ADO.NET Data Service

I have created a website containing an ADO.NET Data Service called BarDataService.svc. The data source will be a simple .mdf SQL database containing a single table called Consumers. I have populated a few records in the Consumers table. The data model representation to the Data Service is provided through an ADO.NET Entity Data Model. Fig 2.0 shows the screenshot of the data model displayed on the .edmx file:


Fig 2.0

I have skipped the creation of the ADO.NET Data Service in this article since this article will deal only with the client application development and working with the Data Service in it.

CRUD Operations

Add a new ASP.NET Web Application project to the solution and add the service reference to the BarDataService. This will create the service reference proxy in the client web application.

The basic operations that we are going to perform in this Web application is displaying the list of Customers, creating a new customer, updating an existing customer and deleting a customer using the BarDataService.

Create web page called Default.aspx. Below is the .aspx designer code:

  <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" EnableEventValidation="false" Inherits="BarWebApp._Default" %>

  <%@ Register assembly="System.Web.Entity, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" namespace="System.Web.UI.WebControls" tagprefix="asp" %>

  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  <html >
  <head runat="server">
      <title>Bar Website</title>
      <style type="text/css">
          .style1
          {
              width: 116px;
          }
      </style>
  </head>
  <body>
      <form id="form1" runat="server">
      <div>
          <h2>
              Customer Registration</h2>
      </div>
      <div>
          <table style="height: 187px; width: 398px">
              <tr>
                  <td class="style1">
                      First Name:
                  </td>
                  <td>
                      <asp:TextBox ID="FirstNameTextBox" runat="server" Width="192px"></asp:TextBox>
                  </td>
                  <tr>
                      <td class="style1">
                          Last Name:
                      </td>
                      <td>
                          <asp:TextBox ID="LastNameTextBox" runat="server" Width="192px"></asp:TextBox>
                      </td>
                  </tr>
                  <tr>
                      <td class="style1">
                          Age:
                      </td>
                      <td>
                          <asp:TextBox ID="AgeTextBox" runat="server" Width="43px"></asp:TextBox>
                      </td>
                  </tr>
                  <tr>
                      <td class="style1">
                          Favourite Liquor:
                      </td>
                      <td>
                          <asp:TextBox ID="LiquorTextBox" runat="server" Width="192px"></asp:TextBox>
                      </td>
                  </tr>
                  <tr>
                      <td class="style1">
                      </td>
                      <td>
                          <asp:Button ID="RegisterButton" runat="server" Text="Register"
                              onclick="RegisterButton_Click" />
                      </td>
                  </tr>
          </table>
      </div>
      <br />
      <div>
          <h2>
              Customer List
          </h2>
      </div>
      <asp:GridView ID="CustomerListGridView" runat="server"
          AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333"
          GridLines="None" Width="455px"
          onrowdeleting="CustomerListGridView_RowDeleting"
          onrowediting="CustomerListGridView_RowEditing"
          onrowupdating="CustomerListGridView_RowUpdating"
          onrowcancelingedit="CustomerListGridView_RowCancelingEdit">
          <RowStyle BackColor="#EFF3FB" />
          <Columns>
              <asp:BoundField DataField="CustomerId" HeaderText="Customer ID" />
              <asp:BoundField DataField="FirstName" HeaderText="First Name" />
              <asp:BoundField DataField="LastName" HeaderText="Last Name" />
              <asp:BoundField DataField="Age" HeaderText="Age" />
              <asp:BoundField DataField="FavouriteLiqour" HeaderText="Liquor" />
              <asp:CommandField ShowEditButton="True"/>
              <asp:CommandField ShowDeleteButton="True" />
          </Columns>
          <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
          <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
          <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
          <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
          <EditRowStyle BackColor="#2461BF" />
          <AlternatingRowStyle BackColor="White" />
      </asp:GridView>
      <div>
      </div>
      </form>
  </body>
  </html>

Below is the C# programming code of the code behind file Default.aspx.cs:

  public partial class _Default : System.Web.UI.Page
  {
      BarDatabaseEntities _entities = null;
      private const string SERVICE_URI = "http://localhost:59290/ADONETDataService/BarDataService.svc/";

      protected void Page_Load(object sender, EventArgs e)
      {
          //Bind data only on fresh load
          if (!Page.IsPostBack)
              BindCustomerData();
      }

      private void BindCustomerData()
      {
          //Query the data and bind it to the gridview
          _entities = new BarDatabaseEntities(new Uri(SERVICE_URI));
          //construct the URL and execute it. Note the query string options provided.
          var customers = _entities.Execute<Customers>(new Uri("Customers?$orderby=CustomerId", UriKind.Relative));
          CustomerListGridView.DataSource = customers.AsEnumerable();
          CustomerListGridView.DataBind();
      }

      protected void CustomerListGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
      {
          GridViewRow row = ((GridView)sender).Rows[e.RowIndex];
          int customerId = Convert.ToInt32(((TextBox)row.Cells[0].Controls[0]).Text);
          _entities = new BarDatabaseEntities(new Uri(SERVICE_URI));

          var customer = (from c in _entities.Customers
                          where c.CustomerId == customerId
                          select c).First();
          customer.FirstName = ((TextBox)row.Cells[1].Controls[0]).Text;
          customer.LastName = ((TextBox)row.Cells[2].Controls[0]).Text;
          customer.Age = Convert.ToInt32(((TextBox)row.Cells[3].Controls[0]).Text);
          customer.FavouriteLiqour = ((TextBox)row.Cells[4].Controls[0]).Text;

          //Updates the customer object
          _entities.UpdateObject(customer);
          //Saves the changes to the database
          _entities.SaveChanges();

          CustomerListGridView.EditIndex = -1;
          BindCustomerData();
      }

      protected void CustomerListGridView_RowEditing(object sender, GridViewEditEventArgs e)
      {
          CustomerListGridView.EditIndex = e.NewEditIndex;
          BindCustomerData();
      }

      protected void CustomerListGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
      {
          GridViewRow row = ((GridView)sender).Rows[e.RowIndex];
          int customerId = Convert.ToInt32(row.Cells[0].Text);
          _entities = new BarDatabaseEntities(new Uri(SERVICE_URI));

          var customer = (from c in _entities.Customers
                          where c.CustomerId == customerId
                          select c).First();

          //Delete the customer object
          _entities.DeleteObject(customer);
          //Save the changes to the database
          _entities.SaveChanges();

          BindCustomerData();
      }

      protected void CustomerListGridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
      {
          CustomerListGridView.EditIndex = -1;
          BindCustomerData();
      }

      protected void RegisterButton_Click(object sender, EventArgs e)
      {
          _entities = new BarDatabaseEntities(new Uri(SERVICE_URI));
          //Create a customer entity
          Customers customer = new Customers();
          customer.FirstName = FirstNameTextBox.Text;
          customer.LastName = LastNameTextBox.Text;
          customer.Age = Convert.ToInt32(AgeTextBox.Text);
          customer.FavouriteLiqour = LiquorTextBox.Text;
          //Add the customer object
          _entities.AddToCustomers(customer);
          //Save the changes to the database
          _entities.SaveChanges();
      }
  }

In the above code I have in-lined my comments. Also notice once any Add, Update or Delete of the customer object is carried out, the SaveChanges() method is called. This is the method which is responsible for saving the data back to the database through the ADO.NET data service.

Let us run the application now. Enter the values in the fields First Name, Last Name, Age and Liquor and click on register. Notice that the record is added to the database and displayed in the GridView as shown in Fig 3.0 and Fig 3.1.


Fig 3.0


Fig 3.1

Now go ahead and update a record in the GridView. For example update the customer David Boon’s age to 50 and click on update. You will notice the age getting updated in the database, likewise the delete also works cool.

Conclusion

I hope this article covers the Create Update Delete and Read operations using the ADO.NET Data Service in a .NET framework client application. I have also attached the source code of the developed application along with this article.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read