Connect mobile application to SQL server

AndrewWilson.2511 · June 2015

Hello everyone,

I am new in developing mobile applications. I am trying to start a simple project on Android/iOS/Windows phones. The application should contain a login page that the user enters username and password and the application should check these info on the local SQL server and check whether the user is registered or not. I have couple of questions and just need your help

1) How to create the server tasks (PHP, Json...etc) and what should the server send to the application?
2) How to connect the xamarin.form project to that SQL server to check the info the user entered?

your help will be really appreciated
Thank you in advance!

Billy12Shovels · June 2015

@AndrewWilson.2511 Hello, I don't connect to a SQL server directly from the application. I use the REST Web Service that my office created to connect to the SQL server, and then I connect to the Web Service with my application.

I'm not too sure if you can even connect to a SQL server directly from an app?

Does that make any sense?

AlessandroCaliaro · June 2015

@AndrewWilson.2511 To connect to SQLServer you could use System.Data.SqlClient
https://msdn.microsoft.com/it-it/library/system.data.sqlclient(v=vs.110).aspx

This should be simple for windows phone.
For Android and iOS I think you should have business version.

Otherwise, as @CodyRousseau says, web services are the solution.

I have also found this on nugget
https://www.nuget.org/packages/System.Data.SqlClient/

I don't know if it's compatible with iOS and Android...

DerekPapworth.4183 · June 2015

@AndrewWilson.2511 similar to Copy, I've created a web service app (asp.net) that provides services to my Xamarin Forms apps so the mobile app will login to SQL Server via the web service and indeed perform all SQL actions (Select, Insert, Delete, etc) using the web service framework. I would suggest that's the way to go for you and/or welcome to cover in more detail my solution (which is generic SQL server forms access) in case that's of interest to you.

Derek

AndrewWilson.2511 · June 2015

Is REST web service another separate project that I need to implement?

AlessandroCaliaro · June 2015

I think you should take a look here

http://developer.xamarin.com/guides/cross-platform/application_fundamentals/web_services/
http://adventuresinxamarinforms.com/2014/11/28/creating-a-xamarin-forms-app-part-8-consuming-a-restful-web-service/

FredyWenger · June 2015

@AndrewWilson.2511:
You have to distinguish between access to a local database (the database is installed on the mobile device and only the app on the mobile device have access to it), then you can use e.g. databases like sqllite or if you want to access a central database (e.g. a sql-server) from an app (where different mobile devices have access), then you have to implement a webservice (suggestion is json) on a server and access it via http.
A local database is normally used to store data persistent on one device (e.g. user-settings or from a central database queried data for offline-use).
If you have a central database, that you want to access, you have to implement a web-service (direct access not possible).
Hope this helps...

BhagatSehkhawat · August 2016

hi
I am new in Xamarin so can you help me please.
how to develop a app.
1 With Connect Sql Server.
2 How to use wcf webservice in Xamarin.

AlessandroCaliaro · August 2016

1 System.Data.SqlClient
2 https://developer.xamarin.com/guides/cross-platform/application_fundamentals/web_services/walkthrough_working_with_WCF/

R888 · August 2016

any running sample project for insert data in to sql server 2008 using xamarin with visual studio 2013

FredyWenger · August 2016

@BhagatSehkhawat, @R888
Please read my posting from June 2015...
Normally you have to create a webservice that accesses the data on a SQL-Server.
In your webservice you implement methods that can be called from you app.
@BhagatSehkhawat:
In your question, the wcf webservice ( 1 ) have to access the SQL-Server ( 2 ) and you have to implement methods in the wcf webservice that can be called from your app (via httpclient in the app).
But, if you don't already have the wcf webservice, I strongly recommend you, you create a Json-webservice instead of a wcf webservice (as this is the furture.
So @both:
You have to create a webservice first (out of the "Xamarin-Universe"), where you implement your methods.
=> E.g. an asp.net webservice -> search on the internet for informations
Then, you have to implement the access to your webservice in your app (therefore you will find many informations in the Xamarin forums)
Hope this helps

JPatel77 · December 2016

Hi,
To connect to a SQL Server from a Xamarin app (using Visual Studio, so some instructions might change), I followed this process:
1. Add in a System.Data reference to your project
2. Add in a using directive "using System.Data.SqlClient"
3. Define connection string and open the connection using SqlConnection(connectionString)
4. Manipulate database as normal

My code connects to a SQL database located on a server (not localhost) and then searches for an account number to check if it exists. The code is:

string connectionString = @"Server=<ipAddress>;Database=<DBName>;User Id=<username>;Password=<password>;Trusted_Connection=true";
string databaseTable = "<yourDBTableName>";
string referenceAccountNumber = "0001134919";
string selectQuery = String.Format("SELECT * FROM {0} WHERE [Account_Number] = '{1}' ", databaseTable, referenceAccountNumber);
        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {                   
                //open connection
                connection.Open();

                SqlCommand command = new SqlCommand(selectQuery, connection);

                command.Connection = connection;
                command.CommandText = selectQuery;
                var result = command.ExecuteReader();
        //check if account exists
                var exists = result.HasRows;
            }
        }
        catch (Exception exception)
        {
            #region connection error
            AlertDialog.Builder connectionException = new AlertDialog.Builder(this);
            connectionException.SetTitle("Connection Error");
            connectionException.SetMessage(exception.ToString());
            connectionException.SetNegativeButton("Return", delegate { });
            connectionException.Create();
            connectionException.Show();
            #endregion
        }`

Ensure that you have enable Internet Permission for your application.

Hope this helps.

Hansi_Reit · December 2016

What is the difference between using a webservice like json or wcf and directly connect to a sql server?

FredyWenger · December 2016

@Hansi_Reit
If you connect directly to a SQL server, you establish a connection from your app over the internet directly to the SQL server and therefore also have to configure your firewall to allow this (what is a security risk).
Your app then have to connect to the SQL server thru the firewall!
This architecture is called client-server and should not be used over the internet.
With a Json or WCF web service, you create a web service with clear defined methods that you define. Only the web service have a (clear defined and more secure) connection to the SQL server thru the inner firewall (it's not possible to connect to the SQL server thru the internet).
Your app then connect to the web service by calling the methods that you have defined. The web service query or update the data on the SQL server and give back the data to your app.
This can be e.g. a list of queried data or simply a return code (error or O.K.).
This architecture is called three tier architecture as a web service take place in the middle of the chain.
Note: You also can connect other clients (as your app) to the web service (like a web client, but also a windows client).

Hansi_Reit · December 2016

Thank you so much for you awnser!

So a webservice is the same as a API am I right?

Hansi_Reit · December 2016

@FredyWenger thx for your realy good awnsers!
I will take a look at it

FredyWenger · December 2016

API stands for Aplication Programming Interface what normally is used for other purposes (e.g. an interface to c# to access a specific hardware).
But... in the broadest sense... yes... also a web service can be seen as API...
Good luck for your "check-it-out"

bmc · January 2017

What if I am only building Touch apps for use on a LAN where sql server backend also resides, can I avoid the need for wcf in this scenario ? - just let Xarmarin talk direct to sql ?

FredyWenger · January 2017

@bmc
Yes you can... ( (c) by Obama...)
See posting from JPatel77 above.

amd.guizmo · March 2017

@JPatel77 said:
Hi,
To connect to a SQL Server from a Xamarin app (using Visual Studio, so some instructions might change), I followed this process:
1. Add in a System.Data reference to your project
2. Add in a using directive "using System.Data.SqlClient"
3. Define connection string and open the connection using SqlConnection(connectionString)
4. Manipulate database as normal

My code connects to a SQL database located on a server (not localhost) and then searches for an account number to check if it exists. The code is:
string connectionString = @"Server=<ipAddress>;Database=<DBName>;User Id=<username>;Password=<password>;Trusted_Connection=true";
string databaseTable = "<yourDBTableName>";
string referenceAccountNumber = "0001134919";
string selectQuery = String.Format("SELECT * FROM {0} WHERE [Account_Number] = '{1}' ", databaseTable, referenceAccountNumber);
        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {                   
                //open connection
                connection.Open();

                SqlCommand command = new SqlCommand(selectQuery, connection);

                command.Connection = connection;
                command.CommandText = selectQuery;
                var result = command.ExecuteReader();
      //check if account exists
                var exists = result.HasRows;
            }
        }
        catch (Exception exception)
        {
            #region connection error
            AlertDialog.Builder connectionException = new AlertDialog.Builder(this);
            connectionException.SetTitle("Connection Error");
            connectionException.SetMessage(exception.ToString());
            connectionException.SetNegativeButton("Return", delegate { });
            connectionException.Create();
            connectionException.Show();
            #endregion
        }`
Ensure that you have enable Internet Permission for your application.

Hope this helps.

i try to do this but i can't add in a System.Data reference to my project. I'm using visual studio 2017 with Xamarin, proyect is Cross-Platform portable

thanks for your time...

Adriantheagx · June 2017

@FredyWenger said:
@BhagatSehkhawat, @R888
Please read my posting from June 2015...
Normally you have to create a webservice that accesses the data on a SQL-Server.
In your webservice you implement methods that can be called from you app.
@BhagatSehkhawat:
In your question, the wcf webservice ( 1 ) have to access the SQL-Server ( 2 ) and you have to implement methods in the wcf webservice that can be called from your app (via httpclient in the app).
But, if you don't already have the wcf webservice, I strongly recommend you, you create a Json-webservice instead of a wcf webservice (as this is the furture.
So @both:
You have to create a webservice first (out of the "Xamarin-Universe"), where you implement your methods.
=> E.g. an asp.net webservice -> search on the internet for informations
Then, you have to implement the access to your webservice in your app (therefore you will find many informations in the Xamarin forums)
Hope this helps

Years later, your comment is still helpful @FredyWenger.

FredyWenger · June 2017

You're welcome

NguyenHoang.1174 · August 2017

Hi
Thank you @amd.guizmo
I connect success xamarion ios.

datasunrise · August 2017

Hello everyone,

Datasunrise provide the mssql firewall service to secure database.Mssql Firewall prevents data from unauthorized hackers and check continuously your data in the database if any small changes occur by malicious sides report directly to the administer, they take the step for security purpose.

Tagged: mssql firewall

phani155 · September 2017

can anyone send me the code to connect to the webservice for xamarin android...

phani155 · September 2017

code for sql connection with webservices

FredyWenger · September 2017

@phani155
You cannot connect to a web service via SQL

Scenario 1: You connect from your app directly to your SQL server (what I would not recommend)
-> In this scenario, you query your SQL server directly form the app and retrieve the result directly in [email protected]@phani155
--> Like wrote above -> this is not recommended

Scenario 2: You connect from app to your web service and your web service connect to your SQL server
-> In this scenario, you first build your web service (JSON strongly recommended) and implement your methods
--> Then, you connect your app to your web service, call one of your methods in your web service query your SQL server and your web service give back the results (via JSON) to your app
In any way, you have to write your code yourself...
If you query the forum, you will find plenty of examples

phani155 · September 2017

@FredyWenger
can you send me the best example link.

FredyWenger · September 2017

@phani155
There is not "a best example", the "best example" is depending on you concrete needs
So you have to "read thru" yourself
The road to your "best links" (just copy the text between the "" and paste it in your browser:
"site:https://forums.xamarin.com xamarin.forms webservice json"

phani155 · September 2017

string connectionstring= "Data Source = ""; Initial Catalog = ""; Persist Security Info = True; User ID = """;
SqlConnection con = new SqlConnection(connectionstring);

            con.Open();
            SqlCommand com = new SqlCommand("INSERT INTO tblVALUES(@AssetID,@AssetName,@ConfigID)", con);
            com.Parameters.AddWithValue("@AssetID", astid);
            com.Parameters.AddWithValue("@AssetName", astname);

            com.Parameters.AddWithValue("@ConfigID", confid);

            com.ExecuteNonQuery();

            con.Close();
            Toast.MakeText(this, ToString(), ToastLength.Short).Show();


    }
    catch (SqlException ex)
    {
        throw ex;
        Toast.MakeText(this, ex.ToString(), ToastLength.Short).Show();
    }

i used this code in visual studio for xamarin.android for connecting to the sql server. But the connection is timeout when debugging the application. This code cant work for my app. Any other alternative?

NamyslawSzymaniuk · September 2017

+1 don't ever try to think about mobile app + SQL server architecture directly.
Everything should be done through API (decicated backend).

FredyWenger · September 2017

@phani155
I have searched a video for you (where the theme is explained step by step):

OnurERYILMAZ · September 2017

@FredyWenger Hi, thanks for the usefull informations you're wrote in this post. I would like to use web service in my application. But what if I don't want to open my server to the Internet. I want to share my sql server only on local network devices, I don't want anyone to access my sql server through the Internet. So, in this case should I also need to use web service(is there any option to constraint my sql server accessible only in local network?) ?
Because what I understand, if I use a web service, I have to open my sql server to the world for http access?

Thank you.

FredyWenger · September 2017

@OnurERYILMAZ
You can use a web service only in a LAN or only from the Internet or both.
A web service is a peace of middleware that does the link between your app and the SQL server.
If the web service should be accessed over the Internet it normally is installed in a DMZ:
App <-> Internet <-> outer Firewall <-> web service <-> inner Firewall <-> SQL-Server
So you can rule exactly over the firewall, what can be accessed from the internet.
Normally only your web service can be accessed from the internet and only your web service can access your SQL server (no one can access directly your SQL server from internet).
If you install the web service only in the LAN (what you wrote, you want to do), you simply install the web service on a machine in your LAN (so... no one can access your web service (and your SQL server) from internet.
This way, you can use the web service only in LAN and if you later - for whatever reason - want to allow access from Internet, install it on a server in the DMZ.
So it's anyway a good idea, to use a web service as it is a "clean" architecture.
Notes:

For the web service, you can create a standalone console application (no need for IIS)
You also can use encryption over https (with a certificate of a provider like Thawte), but this is especially an option , if you want to allow access the web service over the internet -> in this case the data over the internet are encrypted)

I hop, this helps

OnurERYILMAZ · September 2017

@FredyWenger Thank you so much, it's very great explanation.

FredyWenger · September 2017

You're welcome

Eybidi1 · November 2017

add System.Data.SqlClient and System.Data.Common
and find project code find using and add them again
SqlConnection Con = new SqlConnection("Data Source=İp adress ;Initial Catalog=Dataase name (not table);Persist Security Info=True;User ID=User Name;Password=Password);
//Check connection open or not
Con.Open();
if (Con != null)
{
message = "Connection open";
Toast.MakeText(ApplicationContext, message, ToastLength.Long).Show();// like messagebox
}
if it is corectly connection why we don't you use other code too

Katekko · November 2017

Yo guys

I hope someone can help me, I'm trying to publish my app using release mode, everything works fine in build. But when I try to do some connection to my database (sql server) it can not make the connection! Would anyone have any idea what it would be like?

ps: I can run in debbug mode normally !!

Amar_Bait · November 2017

Don't shock me...

Are you gonna publish an app to the Stores, that accesses an SQL database directly, with a hardcoded connection string? ****super triggered****

AlessandroCaliaro · November 2017

> @Katekko said:
> Yo guys
>
> I hope someone can help me, I'm trying to publish my app using release mode, everything works fine in build. But when I try to do some connection to my database (sql server) it can not make the connection! Would anyone have any idea what it would be like?
>
> ps: I can run in debbug mode normally !!

Maybe some permission problems? Which is the exception?

FredyWenger · November 2017

@Katekko

You really should use a WebService to access your SQL server!
I suggest you, to read the postings in this thread carefully

Xamarin

Quick Links

Connect mobile application to SQL server

Answers