Perform CRUD Operations using OData Services in .NET

In this article, we will see how to perform CRUD Operations using OData Services in .NET applications.

OData (Open Data Protocol) is a web protocol for performing CRUD operations which is built upon web technologies like HTTP, Atom Publishing Protocol (AtomPub) and JSON to provide access to the data to various applications, services and stores.

We can use WCF Data Service to expose OData. For this demonstration, we will create a table with the Name Customers under database “CallCenter” in SQL Server 2008 R2. The table script is given below –

CREATE TABLE Customers
(
    CustomerID INT PRIMARY KEY,
    ContactName VARCHAR(50),
    Address VARCHAR(50),
    City VARCHAR(50),
    ContactNo VARCHAR(50)
)

Now let’s start by creating a WCF Data Service which will expose our CallCenter database entities using ADO.NET Entity Data Model. To create a WCF Data Service, let’s open Visual Studio 2012 and create a New Web Site. Choose “WCF Service” and name it as “ODataService_CRUDOperations”.

Now right click  “ODataService_CRUDOperations” from solution explorer and add a new item. Choose “ADO.NET Entity Data Model”. Name it as “CallCenter”



As soon as you add the Entity Data model, VS will ask you to add it in the “App_Code” folder. Click on YES. Now VS will display the entity data model wizard. Choose “Generate from database” option and click on “Next” button.

Choose the CallCenter connection string. In case you don’t have the connection string available, create a new connection and connect with CallCenter database. Now let’s make a choice of “Customers” table from table section as shown below –



Click on “Finish” button. You will see our Customer entity on CallCenter.edmx designer surface.
The next step is to create a WCF Data Service. Right click the project and add a new item. Choose “WCF Data Service”. Name it as “CRUDService”. You will see the CRUDService.cs file with some default code. Replace the DataService<” /* TODO: put your data source class name here */”> with the name “CallCenterEntities” as shown below –



Right click “CRUDService.svc” file from solution explorer and set click on “Set as startup page”. Press – “F5” which will run the WCF Data service. This will show you the default output as shown below –



Now go back to the CRUDService.cs file. Uncomment and modify the code from “InitializeService” method as shown below –



Now press “F5” and you will see the following output–



In the URL, type “Customers” and press enter key as shown below –



Seeing XML Feed instead of XML Data?

In case, you see the XML feed instead of XML data, follow this procedure –
Go to Internet Explorer. From the Tools menu, select Internet Options, click the Content tab, click Settings, and clear Turn on feed viewing.

You will now get all the customers data in XML format.

You can try a couple of options –

1) Fetch a record using Primary key value  http://localhost:16712/CRUDService.svc/Customers(1)
2) Fetch a record using City column filter  http://localhost:16712/CRUDService.svc/Customers?$filter=City eq 'London'

Likewise you can perform different operations as per your business needs. Let’s add a Windows Forms application with the name “CallCenterUI” in our solution as shown below –



Now let’s design a Windows Forms application to display the Orders placed by the Customer. For this we will use the following controls –

1. TextBox Control.
2. Label Control.
3. Button Control.

Once your design is ready, it should look similar to the following –



Now let’s add OData that is a WCF Data Service reference into our NorthwindUI application. To add a service reference, right click on the CallCenterUI project and click on Add Service Reference. It will show you the Add Service Reference dialog box where we will add our CRUDService URI as shown below –



Give a name to our service proxy as “CallCenterPRoxy” and add the reference. Now let’s go to the code file of our form and write a method “Navigate” as shown below. Before that declare a few class level variables which we will be using in our program. I am not following any best practices here and am sticking to the article scope.

int currentIndex;
List<CallCenterProxy.Customer> customersQuery = null;

Now let’s see the code of Navigate Method –

private void Navigate(int index)
{
    var context=new CallCenterProxy.CallCenterEntities(new Uri("http://localhost:16712/CRUDService.svc"));
    customersQuery = (from customer in context.Customers
                         select customer).ToList();
    if (customersQuery.Count>0)
    {
        txtCustomerID.Text = customersQuery[index].CustomerID.ToString();
        txtContactName.Text = customersQuery[index].ContactName;
        txtAddress.Text = customersQuery[index].Address;
        txtCity.Text = customersQuery[index].City;
        txtContactNo.Text = customersQuery[index].ContactNo;
    }
}
Now let’s call the navigate method in our Form_Load event as shown below –

private void Form1_Load(object sender, EventArgs e)
{
    currentIndex = 0;
    Navigate(currentIndex);
}

We have set the currentIndex variable value to zero, so that we can fetch the first record when the form gets displayed. I already have few customers in my table. Now run your project and you will see the first record as shown below –



Add some code for the navigation buttons.

First Record –

private void btnFirst_Click(object sender, EventArgs e)
{
    currentIndex = 0;
    Navigate(currentIndex);
}
  
Last Record –

private void btnLast_Click(object sender, EventArgs e)
{
    currentIndex = customersQuery.Count - 1;
    Navigate(currentIndex);
}
  
Previous Record –

private void btnPrevious_Click(object sender, EventArgs e)
{
    if (currentIndex != 0)
    {
        currentIndex--;
        Navigate(currentIndex);
    }
}
  
Next Record –

private void btnNext_Click(object sender, EventArgs e)
{
    if (currentIndex!=customersQuery.Count-1)
    {
        currentIndex++;
        Navigate(currentIndex);
    }
}
  
Add code for our “NEW” button click which will clear all the Text Boxes and set the focus to the first text box which is CustomerID, as shown below –

private void btnNew_Click(object sender, EventArgs e)
{
    txtCustomerID.Text = "";
    txtContactName.Text = "";
    txtAddress.Text = "";
    txtCity.Text = "";
    txtContactNo.Text = "";
    txtCustomerID.Focus();
}

Write the code for “SAVE” button which will insert the data in our Customers table as shown below –

private void btnSave_Click(object sender, EventArgs e)
{
    CallCenterProxy.Customer newCustomerRecord = new CallCenterProxy.Customer()
    {
        CustomerID=int.Parse(txtCustomerID.Text),
        ContactName=txtContactName.Text,
        Address=txtAddress.Text,
        City=txtCity.Text,
        ContactNo=txtContactNo.Text
    };
    var context = new CallCenterProxy.CallCenterEntities(new Uri("http://localhost:16712/CRUDService.svc"));
    context.AddObject("Customers", newCustomerRecord);
    context.SaveChanges();
}
  
Now write some code for the “MODIFY” button which will update the information of existing customer as shown below –

private void btnUpdate_Click(object sender, EventArgs e)
{
var context = new CallCenterProxy.CallCenterEntities(new Uri("http://localhost:16712/CRUDService.svc"));
CallCenterProxy.Customer oldCustomerRecord = (from customer in context.Customers
                                              where customer.CustomerID==int.Parse(txtCustomerID.Text)
                  select customer).SingleOrDefault();
oldCustomerRecord.CustomerID=int.Parse(txtCustomerID.Text);
oldCustomerRecord.ContactName=txtContactName.Text;
oldCustomerRecord.Address=txtAddress.Text;
oldCustomerRecord.City=txtCity.Text;
oldCustomerRecord.ContactNo = txtContactNo.Text;
context.UpdateObject(oldCustomerRecord);
context.SaveChanges();
}
  
And finally we write code for the “DELETE” button which will remove the customer record from our table as shown below –

private void btnDelete_Click(object sender, EventArgs e)
{
    var context = new CallCenterProxy.CallCenterEntities(new Uri("http://localhost:16712/CRUDService.svc"));
    CallCenterProxy.Customer oldCustomerRecord = (from customer in context.Customers
                                                  where customer.CustomerID == int.Parse(txtCustomerID.Text)
                                                  select customer).SingleOrDefault();
    context.DeleteObject(oldCustomerRecord);
    context.SaveChanges();
}
  
That’s all. Now run your project and try to navigate through the customer records. Try insert, update and delete operations.

Conclusion

In this article, we have seen how to perform CRUD Operations using OData Services in .NET applications.