c# - Connecting Windows Phone 8 to Sql server

Question

As I'm not that skilled in Windows Phone 8 development I would like to discuss/ask what is the best way to connect my Windows Phone 8 to Sql-Server Database. I tried to search through the internet and found only few guides which didn't worked for me Linq-to-Sql using WebService. This is where I failed.

Firstly I would like to display data - this is most important, I don't need to edit them yet. But in future it is inevitable.

If the editing would work I need to edit them directly on Sql Server to which I'm connected. I also checked Sql Server Compact guide but that can only work under CE 4.0 (When exporting data from Sql Server to Sql Compact) which doesn't support Windows Phone 8. But even if it would work it simply copy the Sql server database to Sql Compact and doesn't work with data directly on Sql Server (which is understandable because it is Sql Server Compact).

So as I was searching deeply the only way to do that is using WebService I followed some step-by-step guides on YouTube but as I mentioned before, the problem was with displaying data as the guide led me to using ListBox because it was for Windows Phone 7.1 and in Windows Phone 8 is only LongListSelector.

I also found question Connect Windows Phone and Windows 8 apps to SQL Server which was quiet helpful for me.

I think I need some step-by-step guide how-to. So I would like to ask you if there is any step-by-step guide how to connect WP8 and Sql Server? If someone would be that kind and edit my code here to make it work.

Thank you for your time reading this and answers/comments.

M.S.

Answer 1

Well, to achieve your goal, I would do:

1. Build a REST webservice with ASP.NET Web API (http://www.asp.net/web-api) which returns objects (those objects will be translated to json automatically). For example:

   public class MyObject 
   {
     public string Content { get; set; }
   }
   

   Controller for it:

   public class TestController : ApiController
   {
     [HttpGet]
     public MyObject Example() {
       return new MyObject() { Content = "Hello World!" };
     }
   }
   

2. Use a HTTP client in your win phone project:

   HttpClient client = new HttpClient();
   client.BaseAddress = new Uri("http://mywebservice.com");
   client.DefaultRequest.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
   
   using (var result = await client.GetStreamAsync("test/example"))
   {
     var serializer = new JsonSerializer(); // this is json.net serializer
     using (var streamReader = new StreamReader(result)) {
       using (var jsonReader = new JsonTextReader(streamReader))
       {
         var obj = serializer.Deserialize<MyObject>(jsonReader);
         // you can access obj.Content now to get the content which was created by the webservice
         // in this example there will be "Hello World!"
       }
     }
   }
   

Sure you can create much more complex objects which will be (de)serialized. Just take a look at the Web API tutorials.

Within your webservice you can now access any database you want.

Edit If you need a more detailed answer, leave me a comment.