Connect android app to SQL Server

Question

Is there a way to connect android to SQL server using Xamarin and ASP.NET C# ? I have seen the way to connect to SQLite but not SQL server. If this not possible then is there a method to sync data in android app from SQLite to SQL server?

Any help will be greatly appreciated.

Solution

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;
}