Getting Table Data from Sql Server to SQLite with Android

Question

I'm a newly born Android Developer. I have a table in Sql Server with 1000 data and I want to get that data into my Android Application, My app will have this data as Offline Database, Can any one suggest me how can I get all the data into my SQLite database using Android. I know how to create database/Tables and Simple CRUd operations on SQLite... Please help me, Thanks in Advance

Answer 1

If you have existing data, you need to export to SQLite. You can create manually SQLite Database file. SQLite Manager is firefox add on. You can create database and table and then you can insert your existing data with SQLite Manager. After create the sqlite database file, you need to embed the database in your application. This is Android SQLite Database Tutorial for you. In this tutorial SQLiteHelper class is not database embed style. My sample class is database embed style as you wish. Hope for help. This is sample SQLiteDatabaseHelper Class for you.
1) adding your db file file.db or file.sqlite into your project/assets folder;
2) writing SQLiteHelper Class

public class SQLiteHelper extends SQLiteOpenHelper{

private static String DATABASE_NAME = "file.db";
public final static String DATABASE_PATH = "/data/data/yourpackagename/databases/";
private static final int DATABASE_VERSION = 1;
private SQLiteDatabase dataBase;
private final Context dbContext;

public SQLiteHelper(Context context) {
    super(context, DBActivity.DatabaseName, null, DATABASE_VERSION);
    this.dbContext = context;
    DATABASE_NAME = DBActivity.DatabaseName;
    // checking database and open it if exists
    if (checkDataBase()) {
        openDataBase();
    } else
    {
        try {
            this.getReadableDatabase();
            copyDataBase();
            this.close();
            openDataBase();

        } catch (IOException e) {
            throw new Error("Error copying database");
        }
        Toast.makeText(context, "Initial database is created", Toast.LENGTH_LONG).show();
    }
}

// Adding new contact
    public void addContact(Contact contact) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, contact.getName()); // Contact Name
        values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone

        // Inserting Row
        db.insert(TABLE_CONTACTS, null, values);
        db.close(); // Closing database connection
    }

    // Getting single contact
    public Contact getContact(int id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
                KEY_NAME, KEY_PH_NO }, KEY_ID + "=?",
                new String[] { String.valueOf(id) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
                cursor.getString(1), cursor.getString(2));
        // return contact
        return contact;
    }

    // Getting All Contacts
    public List<Contact> getAllContacts() {
        List<Contact> contactList = new ArrayList<Contact>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Contact contact = new Contact();
                contact.setID(Integer.parseInt(cursor.getString(0)));
                contact.setName(cursor.getString(1));
                contact.setPhoneNumber(cursor.getString(2));
                // Adding contact to list
                contactList.add(contact);
            } while (cursor.moveToNext());
        }

        // return contact list
        return contactList;
    }

    // Updating single contact
    public int updateContact(Contact contact) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, contact.getName());
        values.put(KEY_PH_NO, contact.getPhoneNumber());

        // updating row
        return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",
                new String[] { String.valueOf(contact.getID()) });
    }

    // Deleting single contact
    public void deleteContact(Contact contact) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
                new String[] { String.valueOf(contact.getID()) });
        db.close();
    }


    // Getting contacts Count
    public int getContactsCount() {
        String countQuery = "SELECT  * FROM " + TABLE_CONTACTS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }




private void copyDataBase() throws IOException{
    InputStream myInput = dbContext.getAssets().open(DATABASE_NAME);
    String outFileName = DATABASE_PATH + DATABASE_NAME;
    OutputStream myOutput = new FileOutputStream(outFileName);

    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
        myOutput.write(buffer, 0, length);
    }

    myOutput.flush();
    myOutput.close();
    myInput.close();
}

public void openDataBase() throws SQLException {
    String dbPath = DATABASE_PATH + DATABASE_NAME;
    dataBase = SQLiteDatabase.openDatabase(dbPath, null, SQLiteDatabase.OPEN_READWRITE);
}

private boolean checkDataBase() {
    SQLiteDatabase checkDB = null;
    boolean exist = false;
    try {
        String dbPath = DATABASE_PATH + DATABASE_NAME;
        checkDB = SQLiteDatabase.openDatabase(dbPath, null,
                SQLiteDatabase.OPEN_READONLY);
    } catch (SQLiteException e) {
        Log.v("db log", "database does't exist");
    }

    if (checkDB != null) {
        exist = true;
        checkDB.close();
    }
    return exist;
}

Answer 2

Depends on whether you have a MySql Database or an Sql Server Database.

If you have have a MySql Database you can make your server side code in PHP and return the data in JSON format to store it in your android system.

Link as given by @Kunal .C

If you have an Sql Server Database on ASP.net, you can write a Restful WCF Service that returns JSON Data. you can check out: How to Consume WCF Service with Android

In either case I would suggest transferring data as JSON for which you can use GSON for serializing and de-serializing https://code.google.com/p/google-gson/

Hope This helps