Tutorial: Create a ADO.net Data Service

I struggled with this for days. The main issue was that I was using the DASP SQL connection string exactly. In my dev-box I assumed that these were part of the VS2008 SQLExpress and I didn't look more closely.



I am 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 Northwnd database sample from microsoft (Do a google search for it)

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 Servername:$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 containter 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://someDomain.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 annonymous.
11) Point to: http://someDomain.com/NorthWindDataService.svc
-You should get an xml page.
-Query customers with by pointing to: http://someDomain.com/NorthWindDataService.svc/Customers()

THE END

 

Ya, I didn't realize that the ADO.net connection string had a second connection string embedded in it. I wrongly assumed that the "metadata..." part of the connection string, generated in VS2008 to connect to the SQLExpress DB, was to specify a DB container within VS2008. In fact, I still don't see why that couldn't have been encapsulated in the Entity container itself if it is specific to the entity. So instead I set the connection string as it appears in the DASP control panel. This was why I was getting the NoEndPointException.

One question I do have is do I leave the password in plain text in the connection string of the web.config file? Is this not publically accessible?

I thought I could put ******'s in for the password and then DASP would athenticate it if the call was coming from within the DASP domain but that didn't work.

Finally: Ken Cox, a Microsoft MVP and author of ASP.net fo Dummies did a great ADO.net tutorial series (with video) which can show you all the steps needed to build a ADO.net driven page. http://cid-5ba3283f955d0a0f.profile.live.com/Lists/cns!5BA3283F955D0A0F!118/

Best
RDL

 

Fortuitous post!

I was about to do the same thing - thanks for getting through any issues on discountasp.net hosted services.

FYI - the reason the Entity Model uses two connections - one is the physical conneciton to the database, the other is the connection to the entity model. They kind of reuse that connection model. You could concievable use the same Entity connection, and just switch out underlying physical connectons. That way the same model could run over different back ends - that's the idea.

 

P.S. DON'T do what I did and set your endpoint to:

http://youdomain.com

And then in your web browser (habitually) point to:

www.yourdomain.com

Your service won't respond.

 

Yes, just noticed that. Adding www is important. Wonder why.
Thanks for your post.
Simone

 

i am using ado, vs2010, silverlight, framework 4 .. and for the life of me, i can not connect to my database on the discountasp site, but i can connect to the database on my laptop .. same exact string .. i think, in reading these posts, that ado requres more than the standard connect string .. can anyone help me with this?

Thanks

 

I think I am getting close .. lol .. when I do my deploy the deploy site is sharedecisions.net/appname

in my web config release I have the following prefix:

i still dont get any data when the app runs:

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

when I run the app I use the following url:

http://sharedecisi.web704.discountasp.net/sqlGrid/sqlGridTestPage.aspx

 

Make Sure You Dont Have Double Security

<security>
<authentication>
<anonymousAuthentication enabled="false" />
<basicAuthentication enabled="true" />
</authentication>
</security>

I got my problem solved .. one for the good guys ..
if you have double authentication, the ado net service will not work
the above can go ito the web config and/or you can use IIs 7 to modify the site.

Sorry to be such a pain in the but to everyone