Connecting android with MS SQL SERVER 2008

Is there a way that we can connect an Android application to a central database server (e.g. MSSQLServer 2008)?

I have a MySQL database that is accessed by both website and Android. Connecting to the database from the website is fine, but how can it be done from the Android app?

Solution 1:

here are some similar questions asked (an answered):

• android MySQL connection
• How to connect to a MySQL Database from an Android App?

Even though those are for MySQL, it should work for MSSQL by changing the engine or the driver's use to connect. Usually, the approach is to expose some limited level of modification through a web service. Still, nothing is stopping you from directly accessing the database, albeit depending on the case, could pose a security risk.

Main Reasons the web service approach is taking:

• Performance
• Security
• Best Practice
• Separation of concerns

An exception is if you want to enable direct access because you're building a sort of database client through mobile.

Solution 2:

All you have to do is use the appropriate driver, i'd recommend using JTDS, and version 1.2.5 seems to have worked well with android.Detailed instruction on how to use with eclipse can be found here A working code is available in github

/**
 * This is a demo code to demonstrate db connection and operations and not 
 * meant for a live run. 
 * 
 */

public class DBTestActivity extends Activity {

    private Connection conn;

    @Override
    protected void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_dbtest);

    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {

        getMenuInflater().inflate(R.menu.dbtest, menu);
        return true;

    }
    @Override
    protected void onResume() {

        super.onResume();
        (new DBConnection()).execute(null, null, null);

    }

    @Override 
    protected void onPause() {

        super.onPause();
        try {

            conn.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }
    class DBConnection extends AsyncTask<String, String, String> {

        @Override
        protected String doInBackground(String... arg0) {

            try {

                Log.e("MSSQL", "Attempting to connect");

                Class.forName("net.sourceforge.jtds.jdbc.Driver");
                conn = DriverManager.getConnection(
                        "jdbc:jtds:sqlserver://yourserver.com/DBName",
                        "username", "password");

                Log.e("MSSQL", "Connected");

            } catch (Exception e) {

                e.printStackTrace();
                Log.e("MSSQL", e.toString());

            }

            return null;
        }

    }

    class UserInfo {

        String userID;
        String userName;
        String PhoneNo;
        String age;

        public UserInfo(String userID, String userName, String PhoneNo,
                String age) {

            this.userID = userID;
            this.userName = userName;
            this.PhoneNo = PhoneNo;
            this.age = age;

        }

        public String getUserID() {
            return userID;
        }

        public void setUserID(String userID) {
            this.userID = userID;
        }

        public String getUserName() {
            return userName;
        }

        public void setUserName(String userName) {
            this.userName = userName;
        }

        public String getPhoneNo() {
            return PhoneNo;
        }

        public void setPhoneNo(String phoneNo) {
            PhoneNo = phoneNo;
        }

        public String getAge() {
            return age;
        }

        public void setAge(String age) {
            this.age = age;
        }

    }

    class DBOperation {

        public List<UserInfo> getAllUsers() throws SQLException {

            Statement statement = getStatement(conn);

            List<UserInfo> userlist = new ArrayList<UserInfo>();

            ResultSet rs = statement.executeQuery("SELECT * FROM UserInfoTable");
            rs.next();
            int count = 0;

            while (rs.next()) {

                userlist.add(new UserInfo(rs.getString(1), rs.getString(2),
                        rs.getString(3), rs.getString(4)));
                count++;

            }

            rs.close();
            statement.close();
            return userlist;

        }

        public void addUser(UserInfo info) {

            Log.e("MSSQL", "in adduser");

            Statement statement = getStatement(conn);

            try {

                ResultSet rs = statement.executeQuery("INSERT INTO UserInfoTable "
                        + " VALUES ('1001', 'Bob', '333333', '33')");
                rs.close();
                statement.close();

            } catch (SQLException e) {

                e.printStackTrace();

            } 

        }

        private Statement getStatement(Connection connection) {

            try {

                return connection.createStatement();

            } catch (Exception e) {

                throw new RuntimeException(e);

            }
        }

    }

}

Solution 3:

An other approach, musch simpler than Web Service, is to use a Virtual JDBC Driver that uses a three-tier architecture: your JDBC code is sent through HTTP to a remote Servlet that filters the JDBC code (configuration & security) before passing it to the SQL Server JDBC Driver. The result is sent you back through HTTP. There are some free software that use this technique. Just Google "Android JDBC Driver over HTTP".

Solution 4:

Connecting your android application directly to an external database server is a bad idea, instead create a web application and access the database through that.