using MS SQL in my iphone app iOS

Hi All,

I am completely new to this field.  Currently going through a couple books now on Ios development.  I have a question just so I know where to focus my attention.

I currently have this winform project that accesses a MS SQL database where the SQL database does all the heavy lifting calculations with Stored Procedures.

I'm thinking adding a iPhone app to display this data in charts, graphs, grids, and so on.

Before I get into any actual project, can I access MS SQL in iOS?

I've been googling it and I don't see a clearcut way... like a few lines of code from my c# to connect and call querys.

I see something about writing a webservice... but people are saying it is difficult and shouldn't go this route.

Other searches are saying use SQLite... but then I'll have to re-do ALLLLL my stored procedure and learn a new SQL database code, while I have a hard enough time with MS SQL.

What is the norm for apps to communication to a DB?  And can I just connect to my MS SQL?

thanks for the info!!!
solarissfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sognoctCommented:
solarissfAuthor Commented:
still kind of confused.  the full version of sql can expose certain stored procedure with security via this web services capability?  and how does this relate to xml?
darbid73Commented:
Hi Solarissf,

I too am a .net programmer and understand what you are going through.

The communication with MS SQL is a very specific language and if we needed to write apps or programs that communicated with different databases like MS, Oracle, Apple etc then we would have to understand all their languages.  The current solution is that you build a layer on top of your database which takes all those sql queries that you have and turns them into a text format like XML or JSON.  Now in this format any operating system with any programming language can read and write to this database through this layer.

In your case  to answer you two questions;

Before I get into any actual project, can I access MS SQL in iOS?
No.

What is the norm for apps to communication to a DB?
The norm is to use a web service.

 
And can I just connect to my MS SQL?
You cannot directly connect to MSSQL from iOS.

What you are looking for is Windows Communication Foundation (WCF).  You will have 3 "sections" in the future you will have your database, you will have a WCF part and you will have a web service that is running on a computer which is the first program or client that gets and sends data over the network/internet.

I hope this helps you to get started in the right direction.  WCF and a web service in .net is not hard but like all MS stuff there is no one place that explains in a simple way how to do the job.  I think this is because there is just too many choices.  If you are not a C#'er you are going to have difficulties as there is not as many help points in VB.  

So my advice to you is you have a sharp learning curve ahead of you , but once you have the framework set up then adding a new query is not that hard.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Try Now
sognoctCommented:
darbid73 is right, you can't directly connect to apple device, but you can connect to a service, the service can do almost everithing with your database.

here is a simple service example that connects to db and exports data as JSON data:
http://www.mysouth.asia/blog/?p=403

Iphone works better with json than xml (even if there is a good xml parser).

for how to call the iis web service :
call SOAP service from iphone
solarissfAuthor Commented:
What you are looking for is Windows Communication Foundation (WCF).  You will have 3 "sections" in the future you will have your database, you will have a WCF part and you will have a web service that is running on a computer which is the first program or client that gets and sends data over the network/internet.

The current solution is that you build a layer on top of your database which takes all those sql queries that you have and turns them into a text format like XML or JSON.


thank you both for the advice.  Just trying to comprehend everything.
so my hypothetical database will sit on one of those sites for $5.99 per month...
the layer that you are talking about building on top of the database, turning a message into JSON.  which part is this called, the WCF or webservice.

And the iphone that is requesting the information... which part is that called?

Will security be an issue, are these messages somehow encoded or is there any authentication layer?

And lastly, whatever I have to build on top of the server... a regular hosting site doesnt let you install anything else correct?
darbid73Commented:
I know you have read a lot already and also that you would like to at least know and understand theoretically how this all works and fits together.  You are really going into something that we are not going to satisfy all your questions in this thread alone.

so my hypothetical database will sit on one of those sites for $5.99 per month...
 Eventually you will need a publicly accessible point to reach your code, but in the beginning you just need a PC on a network and your iphone on the same network.

And the iphone that is requesting the information... which part is that called?
The iphone is going to call for example "http get" or just a simple URL (which till have a response you need to read) in fact once you have your WCF running you will be able to call the methods from firefox or safari and see the result it returns.

Will security be an issue, are these messages somehow encoded or is there any authentication layer?
 YES and YES and YES - but you are at the limit of my expertise there.  I have never dealt with this issue.


And lastly, whatever I have to build on top of the server... a regular hosting site doesnt let you install anything else correct?
I don t understand what your question is?
solarissfAuthor Commented:
well at least I have step 1 done... the iphone and pc on the same network... my living room. ha

I understand this part... the phone will send "http get"

so from what youre saying security will be an issue... but there is a way I can look to encode it.  I guess as long as its possible to make random people aren't accessing my DB then I can keep searching around for that part.

I'll go read up on the WCF part... then come back with a more pertinent question.
thanks
sognoctCommented:
For security you need to use https requests... But do it when code is enough strong and well formed. For now just service and client consumer is a lot of work
solarissfAuthor Commented:
thanks sonoct... so your saying I can basically switch the requests to a secure mode near the end of the project?
sognoctCommented:
Yes, but need also to do a lot of debug for correctness
solarissfAuthor Commented:
so i've been trying to follow some tutorials on the topics in question.

So far in this particular one... using apache/mysql I made a "fake" web server on my local mac.

Then using a text editor, I created a page in PHP that receives an url with parameters from "an iPhone".
This PHP page then connects to the database... sends a query, gets the results... then sends the results back to the "iPhone".  Then using JSON will interpret the results into a viewable screen on the users front end phone.

Do I have the concept right?
Is the web service you were originally speaking about, me creating this PHP file that (sitting on top of the web server) will receive outside messages, then connect to database?  And this php will sit on the server waiting for requests?  Am I on the right track?

And to make things more confusing for me... I have multiple choices for this layer of web service... I can do what I did on the tutorial... create a php file in a text editor. (which Ive never used php before)

Or using asp.net (hopefully c# since thats what i'm experienced in) create a webservice there.  And somehow use POST to pass in parameters from the "iPhone"

???
darbid73Commented:
You are on the right track and you have spent a lot of time testing.  As you are talking MS and c# I would have thought your server would be a PC box.  This is exactly what I am thinking you should try, play with and eventually implement give your initial question.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
solarissfAuthor Commented:
dfarbid73
I've been working on the restful service link you sent me.  I've been able to get it mostly working.  This looks to be what I was looking for.  I might come back with a bit more questions :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.