Connect Xamarin.Android to Microsoft SQL Server 2012 with WCF web service

I am trying to connect an Microsoft SQL Server 2012 database to an Xamarin.Android app.

As internet researches told me that it is not possible to connect directly to an external database in Xamarin and that I need a layer between my app and the database, I tried to implement that needed WebService with the help of the following video:

Unfortunately, when I attempt to run this web service (by pressing F5 in Visual Studio), he opens the WCF-TestClient (as it should be), but maybe after half of the progress bar ("is adding"), an error view is showed, saying:

Warnung: Es wurde kein Code generiert. Wenn Sie versucht haben, einen Client zu generieren, könnte die Ursache hierfür sein, dass die Metadatendokumente keine gültigen Verträge oder Dienste enthielten oder dass erkannt wurde, dass sich alle Verträge/Dienste in /reference-Assemblys befinden. Stellen Sie sicher, dass alle Metadatendokumente an das Tool übergeben wurden. Warnung: Wenn Datenverträge aus Schemas generiert werden sollen, müssen Sie sicherstellen, dass die Option /dataContractOnly verwendet wird.

Sorry for this German. I try to translate it for you:

Warning: No code has been generated. If you tried to generate a client, a possible reason for this could be, that the meta data documents do not contain valid contracts or services or that all contracts/services are lying in /reference-Assemblys. Please make sure, that all meta data documents are transfered to the tool.
Warning: If data contracts are generated from a schema, you have to make sure, that the option /dataContractOnly is used.

Please let me share my code:

Service1.svc.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace TestWcfService
{
    public class Service1 : IService1
    {
        // Connection string 
        private readonly string conStr = "data source = [Server-IP]\\[instance_name]; initial catalog = [catalog_name]; user id = [dbusername]; password = [dbpw]; Connect Timeout = 60";

        public List<AdrSuche1> GetAdrSuche1()
        {
            List<AdrSuche1> adrList = new List<AdrSuche1>();
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM [table_name]", con)
            {
                CommandType = CommandType.Text
            };

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                AdrSuche1 adrSuche1 = new AdrSuche1
                {
                    Adrsuche1 = reader["column_name"].ToString()
                };

                adrList.Add(adrSuche1);
            }

            return adrList.ToList();
        }
    }
}

IService1.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;

namespace TestWcfService
{
    public interface IService1
    {       
        [OperationContract]
        [WebGet(UriTemplate = "GetAdrSuche1")]
        List<AdrSuche1> GetAdrSuche1();
    }

    [DataContract]
    public class AdrSuche1
    {
        string adrsuche1;

        [DataMember]
        public string Adrsuche1
        {
            get { return adrsuche1; }
            set { adrsuche1 = value; }
        }
    }
}

What is the problem? Can anyone maybe help me integrating Web Api or WCF Service so I can read data from external database and display them on my android app?

Best regards

Best Answers

Answers

  • TBMSamTBMSam Member ✭✭
    edited November 2018

    Hello Ahsan,

    thank you for your reply.

    Unfortunately, you got me a little wrong:

    I do not want to create a new database (particularly to use Azure database as expected, a payment is required, or at least by signing up one has to give credit card data which I don't have).

    I have an existing database given by my employer which runs on Microsoft SQL Server 2012 and need to connect / read from this.

  • ChristianKroesChristianKroes USMember ✭✭

    If you want to connect to a SQL Server directly, let me know, you can do that... (Android and iOS)

  • TBMSamTBMSam Member ✭✭

    Hi @ChristianKroes ,

    really? :) can you show me how to do that?^^

    Best regards

  • ChristianKroesChristianKroes USMember ✭✭

    Hi @TBMSam,

    in your Portable you need an Interface like this:

    public interface ISqlServerService
    {
    bool Connection(out string error);
    .
    .
    }

    Use in View like this:
    ISqlServerService sqlserver;
    sqlserver = DependencyService.Get();
    string error = string.Empty;
    var connected = sqlserver.Connection(out error));

    In your Android and/or iOS Project you need a Service:

    using YourProject.iOS.Services;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using Xamarin.Forms;
    [assembly: Dependency(typeof(SqlServerService))]
    namespace YourProject.iOS.Services
    public class SqlServerService : ISqlServerService
    {
    public bool Connection(out string error)
    {
    error = string.Empty;

            try
            {
                using (SqlConnection con = new SqlConnection(yoursqlserverconnectionstring + ";connect timeout=5"))
                {
                    con.Open();
    
                    var query = "select 1";
                    var command = new SqlCommand(query, con);
                    command.CommandTimeout = 5;
                    command.ExecuteScalar();
    
                    return true;
                }
            }
            catch (Exception ex)
            {
                error = ex.Message + Environment.NewLine;
    
                if (ex.InnerException != null)
                    error += ex.InnerException.Message;
    
                return false;
            }
        }
    

    Your smartphone must use a WLAN able to connect to your sql server...
    This is only a very short example... ;)

  • TBMSamTBMSam Member ✭✭

    Hello @ChristianKroes

    Thank you for your answer, your code and your showing.

    I am sorry, as I am really new in android development, I havn't gotten everything you wrote:

    What do you mean with this sentence: "in your Portable you need an Interface like this"?
    What portable? In which folder does this interface have to be? Outside the Android project?

    Sames goes with: "Use in View like this" Where?^^

    Best regards

  • ChristianKroesChristianKroes USMember ✭✭

    Hi @TBMSam,

    oh sorry, that's for Xamarin Forms, but here we are in Android... I will give it a try on Android only and let you know, ok ?

    Best regards
    Chris

Sign In or Register to comment.