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

Question

I am trying to connect my app to Microsoft SQL server 2008 R2 and retrieve data from it. I can connect my app to MySql through out php but can't complete the same task in terms of MSSQL server. I have googled this and this links. But these links don't tell me exactly the procedure that I should follow. Here in this link I found something that tells me to work with asp. But I can't cope the code. As a new one to do so, can anyone please tell me step by step procedure about connecting android app to MSSQL server? Need the solution pretty badly. On the other hand, some of the comments and answer tells me that I should use web service. How should I implement web service in my code. I can retrieve data from MySql source through json parsing via PHP code. Should I use, ASP instead of PHP. Then how should I do it? What I have done as a novice is as below.

Tried out connecting the server directly.

public void query()
{
        Log.e("Android"," SQL Connect Example.");
        Connection conn = null;
        try {
            String driver = "net.sourceforge.jtds.jdbc.Driver";
            Class.forName(driver).newInstance();
            //test = com.microsoft.sqlserver.jdbc.SQLServerDriver.class;
            String connString = "jdbc:jtds:sqlserver://192.168.115.16:1433/WSMS_H;user=sa;password=123456;integratedSecurity=true;";
            String username = "";
            String password = "";
            conn = DriverManager.getConnection(connString,username,password);
            Log.e("Connection","open");
            Statement stmt = conn.createStatement();
            ResultSet reset = stmt.executeQuery("select * from dbo.Users");
             
            //Print the data to the console
            while(reset.next()){
            Log.e("Data:",reset.getString(3));
        //                Log.w("Data",reset.getString(2));
            }
            conn.close();
             
        }
        catch (Exception e)
        {
            Log.w("Error connection","" + e.getMessage());
        }
    }

Which is not a proper way I confess. What I have is a MS SQL server 2008 R2 installed in my local machine. 1433 port working for SQL server. But the connection string returns null.

Can somebody show me what should I do to complete the connection?Thanks for any kind of help.

Answer 1

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.