Creating an ADO.net Data Service

For the purposes of this demonstration we are using:

IIS 7.0, MS SQL 2008, Visual Studio 2008, C#

ASSUME VARIABLES:
$DASP_SQL_SERVER = tcp:sql2k801.discountasp.net
$DASP_SQL_DB_NAME = SQL2008_99999_nw
$DASP_SQL_USER_NAME = SQL2008_99999_nw_user
$DASP_PASSWORD = somepassword

//Set Up Northwind Database
1) Download the Northwind database sample from Microsoft

2) Attach the Northwind database to your MS SQL 2008 Server on DASP
– Make sure you can connect from MS SQL Data base manager
– Copy the connection string supplied for later use.

3) Open VS2008, Create new web project

4) Create Data Connection
– In the “Server Explorer Tab”: Right-Click[Data Connections]->Add Connection
– Enter Server name:$DASP_SQL_SERVER
– Select “Use SQL Server Authentication”
– Enter Username/Password: $SQL_USER_NAME, $DASP_PASSWORD
– Choose Radio Button “Select or enter a database name”
– From drop down box select: $SQL_DB_NAME
– Click: “Test Connection” button. (Sensure connection succeeded).

//Set up a Entity
5) In “Solution Explorer” tab Righ-Click[Web Project]->Add New Item
– Select: ADO.NET Entity Data Model
– Name it NorthWindModel.edmx
– Next > Select “Generate From Database”
– For Which data connection find the DASP connection in the drop down box
– Select: “Yes, include the sensitive data in the connection string”
– Name: NorthWindEntities (NOTE that “I” is missing from WND in the db name)
– Next> Select the “Customers” table from the db.
– For “Model Name Space” enter “NorthWindNameSpace”. Finish >
– Click on the “NorthWindModel.edmx” page which shows the DB fields
– Jot down the “Entity container Name” in the properties box in the bottom left
– It should be: “NorthWindEntitites”
– This will create a connection string in web.config (replace password characters with “*”):
<add name=”NorthWindEntities” connectionString=”metadata=res://*/NorthWindModel.csdl|res://*/NorthWindModel.ssdl|res://*/NorthWindModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=tcp:sql2k801.discountasp.net;Initial Catalog=SQL2008_99999_nw;Persist Security Info=True;User ID=SQL2008_99999_nw_user;Password=******;MultipleActiveResultSets=True&quot;” providerName=”System.Data.EntityClient” />

//Set up the Service
6) In “Solution Explorer” tab Righ-Click[Web Project]->Add New Item
– Select: ADO.NET Data Service
– Name: NorthWindDataService.svc. ADD >

7) Open WebDataService.svc.cs
– Modify the WebDataService class to look like this:

public class NorthWindDataService : DataService<NorthWindEntities>
  {
    public static void InitializeService(IDataServiceConfiguration config)
      {
config.SetEntitySetAccessRule("*", EntitySetRights.All);
config.UseVerboseErrors = true;
      }
    }

8 ) Open Web.config and replace your service model tag with (but change the DOMAIN!):

<system.serviceModel>
  <serviceHostingEnvironment aspNetCompatibilityEnabled="true">
    <baseAddressPrefixFilters>
      <add prefix="http://YourHostedDomainName.com"/>
    </baseAddressPrefixFilters>
  </serviceHostingEnvironment>
</system.serviceModel>

9) Right click Web project folder -> Publish to DASP using FTP
– Use FTP address and FTP username (See DASP Control Panel->Account Management->Account Info)
– Ensure you use the right FTP address!

10) Start the IIS 7 Manager.
– Connect to IIS 7 on DASP
– In IIS click “Authentication”
– Disable anonymous.
11) Point to: http://YourHostedDomainName.com/NorthWindDataService.svc
– You should get an xml page.
– Query customers with by pointing to: http://YourHostedDomainName.com/NorthWindDataService.svc/Customers()

—

For continuing discussion on this article please see this forum thread.

Special thanks to guest blogger, Rob Leclerc, Ph.D.