Connect mobile application to SQL server

AndrewWilson.2511AndrewWilson.2511 EGMember ✭✭
edited June 2015 in Xamarin.Forms

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!

«1

Answers

  • Billy12ShovelsBilly12Shovels USUniversity ✭✭✭

    @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?

  • AlessandroCaliaroAlessandroCaliaro ITMember ✭✭✭✭✭

    @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.4183DerekPapworth.4183 GBMember ✭✭

    @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.2511AndrewWilson.2511 EGMember ✭✭

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

  • FredyWengerFredyWenger CHInsider ✭✭✭✭✭

    @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...

  • 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.

  • R888R888 INMember

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

  • JPatel77JPatel77 ZAMember ✭✭

    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_ReitHansi_Reit ATMember ✭✭

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

  • Hansi_ReitHansi_Reit ATMember ✭✭

    Thank you so much for you awnser!

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

  • Hansi_ReitHansi_Reit ATMember ✭✭

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

  • FredyWengerFredyWenger CHInsider ✭✭✭✭✭

    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... :sunglasses:
    Good luck for your "check-it-out" :smile:

  • bmcbmc IEMember
    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 ?
  • FredyWengerFredyWenger CHInsider ✭✭✭✭✭

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

  • amd.guizmoamd.guizmo MXMember

    @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...

  • AdriantheagxAdriantheagx GBMember ✭✭

    @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 :sunglasses:

    Years later, your comment is still helpful @FredyWenger.

  • FredyWengerFredyWenger CHInsider ✭✭✭✭✭

    You're welcome :smiley:

  • NguyenHoang.1174NguyenHoang.1174 USMember ✭✭

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

  • datasunrisedatasunrise USMember

    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

  • phani155phani155 INMember ✭✭

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

  • phani155phani155 INMember ✭✭

    code for sql connection with webservices

  • FredyWengerFredyWenger CHInsider ✭✭✭✭✭

    @phani155
    You cannot connect to a web service via SQL :smirk:

    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

  • phani155phani155 INMember ✭✭

    @FredyWenger
    can you send me the best example link.

  • FredyWengerFredyWenger CHInsider ✭✭✭✭✭

    @phani155
    There is not "a best example", the "best example" is depending on you concrete needs :wink:
    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"

  • phani155phani155 INMember ✭✭

    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?

  • NamyslawSzymaniukNamyslawSzymaniuk USMember ✭✭✭✭

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

  • FredyWengerFredyWenger CHInsider ✭✭✭✭✭

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

  • OnurERYILMAZOnurERYILMAZ USMember ✭✭

    @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.

  • FredyWengerFredyWenger CHInsider ✭✭✭✭✭

    @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 :sunglasses:

  • OnurERYILMAZOnurERYILMAZ USMember ✭✭

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

  • FredyWengerFredyWenger CHInsider ✭✭✭✭✭

    You're welcome :smiley:

  • Eybidi1Eybidi1 TRMember
    edited 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

  • KatekkoKatekko BRMember

    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 !!

  • Nadjib_BaitNadjib_Bait DZMember ✭✭✭✭
    edited 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****

  • AlessandroCaliaroAlessandroCaliaro ITMember ✭✭✭✭✭
    > @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?
  • FredyWengerFredyWenger CHInsider ✭✭✭✭✭

    @Katekko

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

Sign In or Register to comment.