Walkthrough: Creating and Accessing an ADO.NET Data Service in Visual Studio

This walkthrough demonstrates how to create a simple ADO.NET Data Service that is hosted in an ASP.NET Web application and then access it from a Windows Forms application.

In this walkthrough you will:

• Create a Web application to host an ADO.NET Data Service.

• Create an Entity Data Model that represents the Customers table in the Northwind database.

• Create an ADO.NET Data Service.

• Create a client application and add a reference to the ADO.NET Data Service.

• Enable data binding to the service and generate the user interface.

• Optionally add filtering capabilities to the application.

Prerequisites

You need the following components to complete this walkthrough:

• The Northwind sample database.

  If you do not have this database on your development computer, you can download it from the Microsoft Download Center. For instructions, see Downloading Sample Databases (LINQ to SQL).

Creating the Service

To create an ADO.NET Data Service, you will add a Web project, create an Entity Data Model, and then create the service from the model.

In the first step, you will add a Web project to host the service.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

To create the Web project

1. On the Visual Studio File menu, click New Project.

2. In the New Project dialog box, expand the Visual Basic or Visual C# node and click Web, and then select ASP.NET Web Application.

3. In the Name field, type NorthwindWeb, and then click OK to create the project.

In this step, you will create an Entity Data Model that represents the Customers table in the Northwind database.

To create the Entity Data Model

1. On the Project menu, click Add New Item.

2. In the Add New Item dialog box, select ADO.NET Entity Data Model.

3. In the Name field, type NorthwindModel, and then click Add.

   The Entity Data Model Wizard will appear.

4. In the Entity Data Model Wizard, select Generate from database, and then click Next.

5. On the Choose Your Data Connection page, perform one of the following steps:

   • If a data connection to the Northwind sample database is available in the drop-down list, click it.

     -or-

   • Click New Connection to configure a new data connection. For more information, see How to: Create Connections to SQL Server Databases.

6. If the database requires a password, select the option to include sensitive data, and then click Next.

   Note

   If a dialog box appears, click Yes to save the file to your project.

7. On the Choose Your Database Objects page, expand the Tables node, select the check box next to Customers, and then click Finish.

   The entity model diagram will be displayed, and a NorthwindModel.edmx file will be added to your project.

In this step, you will create and test the data service.

To create the data service

1. On the Project menu, click Add New Item.

2. In the Add New Item dialog box, select ADO.NET Data Service.

3. In the Name field, type NorthwindCustomers, and then click Add.

   The NorthwindCustomers.svc file will appear in the Code Editor.

4. In the Code Editor, locate the first TODO: comment and replace the code with the following:

   Inherits DataService(Of NORTHWINDModel.NORTHWINDEntities)
   

   public class NorthwindCustomers : DataService<NORTHWINDModel.NORTHWINDEntities>
   

   Note

   Depending on the version of the Northwind database that you are using, you may have to change "NORTHWIND" to "NORTHWND". You can also use IntelliSense to discover the correct name.

5. Replace the comments in the InitializeService event handler with the following code:

   config.SetEntitySetAccessRule("*", EntitySetRights.All)
   

   config.SetEntitySetAccessRule("*", EntitySetRights.All);
   

6. Press Ctrl+F5 to run the service. A browser window will open and the XML schema for the service will be displayed.

7. In the Address bar, type Customers at the end of the URL for NorthwindCustomers.svc, and then press ENTER.

   An XML representation of the data in the Customers table will be displayed.

   Note

   In some cases, Internet Explorer will misinterpret the data as an RSS feed. You must make sure that the option to display RSS feeds is disabled. For more information, see Troubleshooting Service References.

8. Close the browser window.

In the next steps, you will create a Windows Forms client application to consume the service.

Creating the Client Application

To create the client application, you will add a second project, add a service reference to the project, configure a data source, and create a user interface to display the data from the service.

In the first step, you will add a Windows Forms project to the solution and set it as the startup project.

To create the client application

1. On the Visual Studio File menu, click Add, and then click New Project.

2. In the New Project dialog box, expand the Visual Basic or Visual C# node and click Windows, and then select Windows Forms Application.

3. In the Name field, type NorthwindClient, and then click OK to open the project.

4. In Solution Explorer, select the NorthwindClient project.

5. On the Project menu, click Set as Startup Project.

In this step, you will add a service reference to the ADO.NET Data Service in the Web project.

To add a service reference

1. On the Project menu, click Add Service Reference.

2. In the Add Service Reference dialog box, click Discover.

   The URL for the NorthwindCustomers service will appear in the Address field.

3. Click OK to add the service reference.

In this step, you will configure a data source to enable data binding to the service.

To enable data binding to the service

1. On the Data menu, click Show Data Sources.

2. In the Data Sources window, click Add New Data Source.

3. On the Choose a Data Source Type page of the Data Source Configuration Wizard, click Object, and then click Next.

4. On the Select the Object You Wish to Bind to page, expand the NorthwindClient node, and then expand the NorthwindClient.ServiceReference1 node.

5. Select Customers, and then click Finish.

In this step, you will create the user interface that will display the data from the service.

To create the user interface

1. Drag the Customers node from the Data Sources window to the form.

   A DataGridView control, a BindingSource component, and a BindingNavigator component are added to the form.

2. Double-click the form to open the Code Editor, and add the following code to the Form1_Load event handler:

   Dim proxy As New ServiceReference1.NORTHWINDEntities _
    (New Uri("https://localhost:14735/NorthwindCustomers.svc"))
   Me.CustomersBindingSource.DataSource = proxy.Customers
   

   ServiceReference1.NORTHIWNDEntities proxy = new
    ServiceReference1.NORTHIWNDEntities(new
    Uri("https://localhost:14735/NorthwindCustomers.svc"));
   this.customersBindingSource.DataSource = proxy.Customers;
   

   Note

   Depending on the version of the Northwind database that you are using, you may have to change "NORTHWIND" to "NORTHWND". You can also use IntelliSense to discover the correct name.

3. In Solution Explorer, right-click the NorthwindCustomers.svc file and click View in Browser. Internet Explorer will open and the XML schema for the service will be displayed.

4. Copy the URL from the Internet Explorer address bar.

5. In the code that you added in step 2, select https://localhost:14735/NorthwindCustomers.svc and replace it with the URL that you just copied.

6. Press F5 to run the application. The customer information will be displayed.

You now have a working application that will display a list of customers from the NorthwindCustomers service. If you want to expose additional data through the service, you can modify the Entity Data Model to include additional tables from the Northwind database.

In the next optional step, you will learn how to filter the data that is returned by the service.

Adding Filtering Capabilities

In this step, you will customize the application to filter the data by the customer's city.

To add filtering by city

1. In Solution Explorer, double-click Form1.vb or Form1.cs to display the Windows Forms Designer.

2. Drag a TextBox control and a Button control from the Toolbox to the form.

3. Double-click the Button control, and add the following code in the Button1_Click event handler:

   Dim proxy As New ServiceReference1.NORTHWINDEntities _
    (New Uri("https://localhost:14735/NorthwindCustomers.svc"))
   Dim city As String = TextBox1.Text
   
   If city <> "" Then
       Me.CustomersBindingSource.DataSource = From c In _
    proxy.Customers Where c.City = city
   End If
   

   ServiceReference1.NORTHWINDEntities proxy = new
    ServiceReference1.NORTHWINDEntities(new
    Uri("https://localhost:14735/NorthwindCustomers.svc"));
   string city = textBox1.Text;
   
   if (city != "")
   {
       this.customersBindingSource.DataSource = from c in
    proxy.Customers where c.City == city select c;
   }
   

4. In the previous code, replace https://localhost:14735/NorthwindCustomers.svc with the URL from the Form1_Load event handler.

5. Press F5 to run the application.

6. In the text box, type London, and then click the button. Only the customers from London will be displayed.

See Also

Tasks

How to: Add, Update, or Remove an ADO.NET Service Reference

Other Resources

ADO.NET Data Services Framework