Connect to SQL Server from Android

Connect android app to SQL Server

You can use SqlConnection:
https://developer.xamarin.com/api/type/System.Data.SqlClient.SqlConnection/

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        // Do work here; connection closed on following line.
    }

EDIT:
This a method that I found in one of my libraries and I modified it a little bit. First you need to create a connection string so Xamarin knows where to connect. After that we create a SQL command using SqlCommand and then we executes it.

public void Execute()
{
    SqlConnectionStringBuilder dbConString = new SqlConnectionStringBuilder();
    dbConString.UserID = "My Username";
    dbConString.Password = "My Password";
    dbConString.DataSource = "My Server Address";

    using (SqlConnection con = new SqlConnection(returnConnectionString().ConnectionString))
    {
        con.Open();
        for (int i = 0; i < commands.Count; i++)
        {
            SqlCommand cmd = new SqlCommand("UPDATE MyTable SET Name = 'New Name' WHERE ID = 1");
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
        }
    }

ExecuteNonQuery() returns how many rows was affected so it's usuall used when for UPDATE- and INSERT-statements (i.e. not for SELECT-statements). More information:
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx
}

If you are going to use a SELECT-statement this code returns the result as a DataSet:

public DataSet Execute(Statement stat)
{
        DataSet ds = new DataSet();

        SqlConnectionStringBuilder dbConString = new SqlConnectionStringBuilder();
        dbConString.UserID = "My Username";
        dbConString.Password = "My Password";
        dbConString.DataSource = "My Server Address";

        using (SqlConnection con = new SqlConnection(dbConString.ConnectionString))
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM MyTable", con);

            var adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);

        }
        return ds;
}

Connect android application to a server database

I would not recommend you to connect directly to the database from the application, if you plan to publish the app. You open a port to the database server to everyone and store the password to the database in the user device. Anyone who wants to retrieve data from your SQL Server just needs to open the application in a hex editor or decompile it in order to retrieve the password.

Always use an API through a webserver that gets you desired information.
You could use the Firebase Realtime Database to sync specific data, if you do not trust Google then build your own API.
https://firebase.google.com/docs/database/

How to connect android application to MSSQL server and retrive data from it?

At last found a quiet good guideline to solve my problem. It tells me details about implementing web services and how to connect MSSQL server with Android.

This is more than helpful and details are found on the correct answer .

What do I need to do is, first of all, have to create an web service using .NET(C#) and then connect my android application to that web service to retrieve data from the local machine [for me its my MSSQL Server 2008 R2]. Hope this helps others.
But after implementing the ideas and codes provided in those links; I came up with some changes,particularly in the approach.

This is what I have done in my MainActivity.

Hope, this helps others.

 private static final String SOAP_ACTION = "http://tempuri.org/findContact";

    private static final String OPERATION_NAME = "findContact";// your webservice web method name

    private static final String WSDL_TARGET_NAMESPACE = "http://tempuri.org/";

    private static final String SOAP_ADDRESS = "http://10.0.2.2:58497/WebService/Service.asmx";

    protected static final String TAG = null;
    private static String fahren;

    TextView tvData1;
    EditText edata;
    Button button;
    String studentNo;
    String state;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        tvData1 = (TextView)findViewById(R.id.textView1);
        edata =(EditText)findViewById(R.id.editText1);

        button=(Button)findViewById(R.id.button1);

        button.setOnClickListener(new OnClickListener() {

            public void onClick(View v) {
                studentNo=edata.getText().toString();
                new Submit().execute(studentNo);
            }
        });
    }

    private class Submit extends AsyncTask<String, Void, String> {

        @Override
        protected void onPreExecute() {
            super.onPreExecute();

        }

        @Override
        protected String doInBackground(String... arg) {
            // TODO Auto-generated method stub
            SoapObject request = new SoapObject(WSDL_TARGET_NAMESPACE,OPERATION_NAME);
            PropertyInfo propertyInfo = new PropertyInfo();
            propertyInfo.type = PropertyInfo.STRING_CLASS;
            propertyInfo.name = "eid";
            propertyInfo.setValue(studentNo);
            request.addProperty(propertyInfo);//need to be careful adding this, as it became an issue for me while I was getting continuous exception.

            SoapSerializationEnvelope envelope = new SoapSerializationEnvelope(SoapEnvelope.VER11);
            envelope.dotNet = true;
            envelope.setOutputSoapObject(request);
            HttpTransportSE httpTransport = new HttpTransportSE(SOAP_ADDRESS);
            httpTransport.debug = true;
            try{ 
                httpTransport.call(SOAP_ACTION, envelope);

                Log.e("RequestDump", httpTransport.requestDump.toString());
                Log.e("ResponseDump", httpTransport.responseDump.toString());

                SoapObject result=(SoapObject)envelope.bodyIn;
                if(result!= null){
                    state = result.getProperty(0).toString();
                    Log.e("Found", state);
                }
                else{
                    Log.e("Obj", result.toString());
                }
            }  
            catch (Exception exception)   {
                Log.e("Exception", exception.toString());
            }
            return state;
        }

        @Override
        protected void onPostExecute(String result) {
            super.onPostExecute(result);
            tvData1.setText(result);
        }
    }
}

And of course, you cannot access webservice situated in your pc via wifi connection from your real device. [correct me if you know how to do it, please, provide correct url]. Here I ended up with emulator, and anyone can access live webservice from the real ip with their real device for sure, I have tested it already. This code works perfectly for me.
That's how I solved my problem.

My Android application does not connect to my SQL Server

If your database is local, Your phone and your server computer must be connected to the same network. May be that is the problem, if your phone is connected to SIM network or to any other network which is not same as server computer network and you try to access it, it will not. When talking about your Emulator, it is obviously connected to the server network, hence it works.

Try it, may be that's the issue.

---

Related Topics