Android Sqlite and ListView Example

Android Sqlite and ListView Example
This is simpl application which insert data into Sqlite database --and shows the data  from the database in a ListView




This  is the first Activity of application which shows data from database in listview. Register here buton will start a Registration Activity.






Submit button will add data to database and show it in the ListView of MainActivity. Update can be performed by clicking ListView items.



 
To create this Simpl application do the following:-


    1. Create a class which extends  SQLiteOpenHelper the class is given below


package com.arun.registrationform;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class RegistrationOpenHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "REGISTRATION_DB";
    public static final String TABLE_NAME = "REGISTRATION_TABLE";
    public static final int VERSION = 1;
    public static final String KEY_ID = "_id";
    public static final String FNAME = "F_NAME";
    public static final String LNAME = "L_NAME";
    public static final String SCRIPT = "create table " + TABLE_NAME + " ("
            + KEY_ID + " integer primary key autoincrement, " + FNAME
            + " text not null, " + LNAME + " text not null );";

    public RegistrationOpenHelper(Context context, String name,
            CursorFactory factory, int version) {
        super(context, name, factory, version);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL(SCRIPT);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("drop table " + TABLE_NAME);
        onCreate(db);
    }


2.create another class for writing all the Sqlite functions the class is given below

package com.arun.registrationform;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class RegistrationAdapter {
    SQLiteDatabase database_ob;
    RegistrationOpenHelper openHelper_ob;
    Context context;

    public RegistrationAdapter(Context c) {
        context = c;
    }

    public RegistrationAdapter opnToRead() {
        openHelper_ob = new RegistrationOpenHelper(context,
                openHelper_ob.DATABASE_NAME, null, openHelper_ob.VERSION);
        database_ob = openHelper_ob.getReadableDatabase();
        return this;

    }

    public RegistrationAdapter opnToWrite() {
        openHelper_ob = new RegistrationOpenHelper(context,
                openHelper_ob.DATABASE_NAME, null, openHelper_ob.VERSION);
        database_ob = openHelper_ob.getWritableDatabase();
        return this;

    }

    public void Close() {
        database_ob.close();
    }

    public long insertDetails(String fname, String lname) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(openHelper_ob.FNAME, fname);
        contentValues.put(openHelper_ob.LNAME, lname);
        opnToWrite();
        long val = database_ob.insert(openHelper_ob.TABLE_NAME, null,
                contentValues);
        Close();
        return val;

    }

    public Cursor queryName() {
        String[] cols = { openHelper_ob.KEY_ID, openHelper_ob.FNAME,
                openHelper_ob.LNAME };
        opnToWrite();
        Cursor c = database_ob.query(openHelper_ob.TABLE_NAME, cols, null,
                null, null, null, null);

        return c;

    }

    public Cursor queryAll(int nameId) {
        String[] cols = { openHelper_ob.KEY_ID, openHelper_ob.FNAME,
                openHelper_ob.LNAME };
        opnToWrite();
        Cursor c = database_ob.query(openHelper_ob.TABLE_NAME, cols,
                openHelper_ob.KEY_ID + "=" + nameId, null, null, null, null);

        return c;

    }

    public long updateldetail(int rowId, String fname, String lname) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(openHelper_ob.FNAME, fname);
        contentValues.put(openHelper_ob.LNAME, lname);
        opnToWrite();
        long val = database_ob.update(openHelper_ob.TABLE_NAME, contentValues,
                openHelper_ob.KEY_ID + "=" + rowId, null);
        Close();
        return val;
    }

    public int deletOneRecord(int rowId) {
        // TODO Auto-generated method stub
        opnToWrite();
        int val = database_ob.delete(openHelper_ob.TABLE_NAME,
                openHelper_ob.KEY_ID + "=" + rowId, null);
        Close();
        return val;
    }

}
?
3.Then creae MainActivty and main.xml which shows the values from database in a listView.
 This is MainActivity.java



package com.arun.registrationform;

import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v4.widget.SimpleCursorAdapter;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Button;
import android.widget.ListView;

public class MainActivity extends Activity {
    RegistrationAdapter adapter_ob;
    RegistrationOpenHelper helper_ob;
    SQLiteDatabase db_ob;
    ListView nameList;
    Button registerBtn;
    Cursor cursor;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        nameList = (ListView) findViewById(R.id.lv_name);
        registerBtn = (Button) findViewById(R.id.btn_register);
        adapter_ob = new RegistrationAdapter(this);

        String[] from = { helper_ob.FNAME, helper_ob.LNAME };
        int[] to = { R.id.tv_fname, R.id.tv_lname };
        cursor = adapter_ob.queryName();
        SimpleCursorAdapter cursorAdapter = new SimpleCursorAdapter(this,
                R.layout.row, cursor, from, to);
        nameList.setAdapter(cursorAdapter);
        nameList.setOnItemClickListener(new OnItemClickListener() {

            @Override
            public void onItemClick(AdapterView arg0, View arg1, int arg2,
                    long arg3) {
                // TODO Auto-generated method stub
                Bundle passdata = new Bundle();
                Cursor listCursor = (Cursor) arg0.getItemAtPosition(arg2);
                int nameId = listCursor.getInt(listCursor
                        .getColumnIndex(helper_ob.KEY_ID));
                // Toast.makeText(getApplicationContext(),
                // Integer.toString(nameId), 500).show();
                passdata.putInt("keyid", nameId);
                Intent passIntent = new Intent(MainActivity.this,
                        EditActivity.class);
                passIntent.putExtras(passdata);
                startActivity(passIntent);
            }
        });
        registerBtn.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View arg0) {
                // TODO Auto-generated method stub
                Intent registerIntent = new Intent(MainActivity.this,
                        RegistrationActivity.class);
                startActivity(registerIntent);
            }
        });

    }

    @Override
    public void onResume() {
        super.onResume();
        cursor.requery();

    }

}


Main.xml is given below



    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

            android:id="@+id/lv_name"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" >
   

   
-->> for listView we need another layout t ospecify each Row of the LIstView
which is row.xml given below





    android:layout_width="match_parent"

    android:layout_height="wrap_content"

    android:orientation="horizontal" >



   
        android:id="@+id/tv_fname"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content" />



   
        android:id="@+id/tv_lname"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_marginLeft="20dp" />





4.To inserting data  into database RegistrationActivity.java  and register.xml is used which is given below

RegistrationActivity.java


package com.arun.registrationform;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

public class RegistrationActivity extends Activity {
    RegistrationAdapter adapter;
    RegistrationOpenHelper helper;
    EditText fnameEdit, lnameEdit;
    Button submitBtn, resetBtn;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.register);
        fnameEdit = (EditText) findViewById(R.id.et_fname);
        lnameEdit = (EditText) findViewById(R.id.et_lname);
        submitBtn = (Button) findViewById(R.id.btn_submit);
        resetBtn = (Button) findViewById(R.id.btn_reset);
        adapter = new RegistrationAdapter(this);

        submitBtn.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View arg0) {
                // TODO Auto-generated method stub
                String fnameValue = fnameEdit.getText().toString();
                String lnameValue = lnameEdit.getText().toString();
                long val = adapter.insertDetails(fnameValue, lnameValue);
                // Toast.makeText(getApplicationContext(), Long.toString(val),
                // 300).show();
                finish();
            }
        });
        resetBtn.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                fnameEdit.setText("");
                lnameEdit.setText("");
            }
        });

    }
}
?
register.xml


    xmlns:tools="http://schemas.android.com/tools"

    android:layout_width="fill_parent"

    android:layout_height="fill_parent"

    android:stretchColumns="1" >



   
        android:layout_width="fill_parent"

        android:layout_height="wrap_content" >



       
            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:text="@string/fname" />



       
            android:id="@+id/et_fname"

            android:layout_width="fill_parent"

            android:layout_height="wrap_content" />

   



   
        android:layout_width="wrap_content"

        android:layout_height="wrap_content" >



       
            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:text="@string/lname" />



       
            android:id="@+id/et_lname"

            android:layout_width="fill_parent"

            android:layout_height="wrap_content" />

   



   
        android:layout_width="wrap_content"

        android:layout_height="wrap_content" >



       

\

5.For updating Database values EditActivity.java is used

package com.arun.registrationform;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

public class EditActivity extends Activity {
    RegistrationAdapter regadapter;
    RegistrationOpenHelper openHelper;
    int rowId;
    Cursor c;
    String fNameValue, lNameValue;
    EditText fname, lname;
    Button editSubmit, btnDelete;

    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.editregister);
        fname = (EditText) findViewById(R.id.et_editfname);
        lname = (EditText) findViewById(R.id.et_editlname);
        editSubmit = (Button) findViewById(R.id.btn_update);
        btnDelete = (Button) findViewById(R.id.btn_delete);

        Bundle showData = getIntent().getExtras();
        rowId = showData.getInt("keyid");
        // Toast.makeText(getApplicationContext(), Integer.toString(rowId),
        // 500).show();
        regadapter = new RegistrationAdapter(this);

        c = regadapter.queryAll(rowId);

        if (c.moveToFirst()) {
            do {
                fname.setText(c.getString(1));
                lname.setText(c.getString(2));

            } while (c.moveToNext());
        }

        editSubmit.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View arg0) {
                // TODO Auto-generated method stub
                regadapter.updateldetail(rowId, fname.getText().toString(),
                        lname.getText().toString());
                finish();
            }
        });
        btnDelete.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                regadapter.deletOneRecord(rowId);
                finish();
            }
        });
    }
}


editregister.xml is given below


    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

            android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:stretchColumns="1" >

                    android:layout_width="fill_parent"
            android:layout_height="wrap_content" >

                            android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="@string/fname" />

                            android:id="@+id/et_editfname"
                android:layout_width="fill_parent"
                android:layout_height="wrap_content" >

               
           
       

                    android:layout_width="wrap_content"
            android:layout_height="wrap_content" >

                            android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="@string/fname" />

                            android:id="@+id/et_editlname"
                android:layout_width="fill_parent"
                android:layout_height="wrap_content" />
       
   

            android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal" >

       

JQuery & MVC2 Example

Here i give an example for  binding the state to dropdown list  based on the country   by mvc & jquery

First we need to create the Controller called "Country"  & add a action called state .  and then i have given here  json  data to return in that action.



      public JsonResult State(string Country)
        {
            //items is your list what ever how you get it
            List State = new List();
            if (Country == "India")
            {
             
                State.Add("TN");
                State.Add("KA");
            }
            else if (Country == "US")
            {
                State.Add("CA");
                State.Add("NY");
            }

            var stateList = State;
            return Json(stateList, JsonRequestBehavior.AllowGet);
         
        }




now we talk about jquery part .   by using $.ajax() call ,  we need to post the request to Country controller for action  "State".  So url should be '/Country/State'.  we need to define the datatype  to json.  the parameter part should be done with data . if the ajax called request successfully , then sucess block gets executed .  we add anonymous function to bind the data as following one.  meanwhile if any exception are thrown,  ajax's error handle function added


 $(document).ready(function () {

          $.ajax({
              type: "POST",
              url: "/Country/State",
              datatype: 'json',
              data: {Country:'US'},
              success: function (data) {

                  $('#mes').html(data);
                  var items = '';
                  for (var i = 0; i < data.length; i++) {
                      items = data[i];
                      $("#Select1").append('' + data[i] + '');                  
                  }

              }
                ,
              error: function (request, status, error) {
                  alert(error);
              }
          });


      });


Regards
Guna seakran

Class inherits two interfaces with same method that class inherited by another class

A class  called One  which inherits two interface i1 & i2.  I1 has a method called method1().  I2 has a method called method1().  These two interface I1 & I2 have same method name called method1(). in it.

the class One implements the method method1() by  interface name with dot and method name like
 interface.method1()

again we come to access  part of those method in derived class called two.    create the object for base class and assign it to interface  declaration.  call method by using interface
The real example below



 class Program
    {
        static void Main(string[] args)
        {
            two obj = new two();
            obj.method2();
            Console.ReadLine();
        }
    }
    interface i1
    {
        void method1();
    }
    interface i2
    {
        void method1();
    }
    class one : i1, i2
    {
        void i1.method1()
        {
            Console.WriteLine("test i1");
        }
       void i2.method1()
        {
            Console.WriteLine("test i2");
        }
    }
    class two : one
    {

        internal void method2()
        {

            i1 obji1 = new one();
            obji1.method1();
            i2 obji2 = new one();
            obji2.method1();
           
          
        }
      
    }


Thanks
gunasekaran

HTML5 with Sql database

I have created the sql database in HTML5 page .  it supports latest browsers like ie ,chrome
It is very simple and useful

There are three core methods in the spec that I’m going to cover in this article:

openDatabase

transaction

executeSql

I’ve passed four arguments to the openDatabase method. These are:

Database name

Version number

Text description

Estimated size of database  -  size might be 5Mb to 500 Mb. it varies on browser

executeSql

This is the funnel of love for all your SQL goodness. executeSql is used for both read and write statements, includes SQL injection projection, and provides a callback method to process the results of any queries you may have written.

Once we have a transaction object, we can call executeSql:

var db = openDatabase('mydb', '1.0', 'my first database', 2 *1024 * 1024);
db.transaction(function (tx) {
  tx.executeSql('CREATE TABLE foo (id unique, text)');
});

Example is below

Status Message

prime number storedprocedure in SQL

Create procedure guna  @testprime int

as

begin

declare @i int
declare @reminder int
declare @messgae varchar(10)
set @messgae='Prime'
set @i=2

while (@i<@testprime)
begin

select  @reminder=@testprime%@i

if(@reminder=0)
begin
set @messgae='not prime'
break
end

set @i=@i+1
end

select @messgae

end



exec guna 4

Clustered & non clustered indes

1. Introduction

We all know that data entered in the tables are persisted in the physical drive in the form of database files. Think about a table, say Customer (For any leading bank India), that has around 16 million records. When we try to retrieve records for two or three customers based on their customer id, all 16 million records are taken and comparison is made to get a match on the supplied customer ids. Think about how much time that will take if it is a web application and there are 25 to 30 customers that want to access their data through internet. Does the database server do 16 million x 30 searches? The answer is no because all modern databases use the concept of index.

2. What is an Index

Index is a database object, which can be created on one or more columns (16 Max column combination). When creating the index will read the column(s) and forms a relevant data structure to minimize the number of data comparisons. The index will improve the performance of data retrieval and adds some overhead on data modification such as create, delete and modify. So it depends on how much data retrieval can be performed on table versus how much of DML (Insert, Delete and Update) operations.

In this article, we will see creating the Index. The below two sections are taken from my previous article as it is required here. If your database has changes for the next two sections, you can directly go to section 5.

3. First Create Two Tables

To explain these constraints, we need two tables. First, let us create these tables. Run the below scripts to create the tables. Copy paste the code on the new Query Editor window, then execute it.

 Collapse | Copy Code

CREATE TABLE Student(StudId smallint, StudName varchar(50), Class tinyint);
CREATE TABLE TotalMarks(StudentId smallint, TotalMarks smallint);
Go

Note that there are no constraints at present on these tables. We will add the constraints one by one.

4. Primary Key Constraint

A table column with this constraint is called as the key column for the table. This constraint helps the table to make sure that the value is not repeated and also no null entries. We will mark the StudId column of the Student table as primary key. Follow these steps:

1. Right click the student table and click on the modify button.
2. From the displayed layout, select the StudId row by clicking the Small Square like button on the left side of the row.
3. Click on the Set Primary Key toolbar button to set the StudId column as primary key column.

Now this column does not allow null values and duplicate values. You can try inserting values to violate these conditions and see what happens. A table can have only one Primary key. Multiple columns can participate on the primary key column. Then, the uniqueness is considered among all the participant columns by combining their values.

5. Clustered Index

The primary key created for the StudId column will create a clustered index for the Studid column. A table can have only one clustered index on it.

When creating the clustered index, SQL server 2005 reads the Studid column and forms a Binary tree on it. This binary tree information is then stored separately in the disc. Expand the table Student and then expand the Indexes. You will see the following index created for you when the primary key is created:

With the use of the binary tree, now the search for the student based on the studid decreases the number of comparisons to a large amount. Let us assume that you had entered the following data in the table student:

The index will form the below specified binary tree. Note that for a given parent, there are only one or two Childs. The left side will always have a lesser value and the right side will always have a greater value when compared to parent. The tree can be constructed in the reverse way also. That is, left side higher and right side lower.

Now let us assume that we had written a query like below:

 Collapse | Copy Code

Select * from student where studid = 103;
Select * from student where studid = 107;

Execution without index will return value for the first query after third comparison.
Execution without index will return value for the second query at eights comparison.

Execution of first query with index will return value at first comparison.
Execution of second query with index will return the value at the third comparison. Look below:

1. Compare 107 vs 103 : Move to right node
2. Compare 107 vs 106 : Move to right node
3. Compare 107 vs 107 : Matched, return the record

If numbers of records are less, you cannot see a different one. Now apply this technique with a Yahoo email user accounts stored in a table called say YahooLogin. Let us assume there are 33 million users around the world that have Yahoo email id and that is stored in the YahooLogin. When a user logs in by giving the user name and password, the comparison required is 1 to 25, with the binary tree that is clustered index.

Look at the above picture and guess yourself how fast you will reach into the level 25. Without Clustered index, the comparison required is 1 to 33 millions.

Got the usage of Clustered index? Let us move to Non-Clustered index.

6. Non Clustered Index

A non-clustered index is useful for columns that have some repeated values. Say for example, AccountType column of a bank database may have 10 million rows. But, the distinct values of account type may be 10-15. A clustered index is automatically created when we create the primary key for the table. We need to take care of the creation of the non-clustered index.

Follow the steps below to create a Non-clustered index on our table Student based on the column class.

1. After expanding the Student table, right click on the Indexes. And click on the New Index.
2. From the displayed dialog, type the index name as shown below and then click on the Add button to select the column(s) that participate in the index. Make sure the Index type is Non-Clustered.
3. In the select column dialog, place a check mark for the column class. This tells that we need a non-clustered index for the column Student.Class. You can also combine more than one column to create the Index. Once the column is selected, click on the OK button. You will return the dialog shown above with the selected column marked in blue. Our index has only one column. If you selected more than one column, using the MoveUp andMoveDown button, you can change order of the indexed columns. When you are using the combination of columns, always use the highly repeated column first and more unique columns down in the list. For example, let use assume the correct order for creating the Non-clustered index is: Class, DateOfBirth, PlaceOfBirth.
4. Click on the Index folder on the right side and you will see the non-clustered index based on the column class is created for you.

7. How Does a Non-Clustered Index Work?

A table can have more than one Non-Clustered index. But, it should have only one clustered index that works based on the Binary tree concept. Non-Clustered column always depends on the Clustered column on the database.

This can be easily explained with the concept of a book and its index page at the end. Let us assume that you are going to a bookshop and found a big 1500 pages of C# book that says all about C#. When you glanced at the book, it has all beautiful color pages and shiny papers. But, that is not only the eligibility for a good book right? One you are impressed, you want to see your favorite topic of Regular Expressions and how it is explained in the book. What will you do? I just peeped at you from behind and recorded what you did as below:

1. You went to the Index page (it has total 25 pages). It is already sorted and hence you easily picked up Regular Expression that comes on page Number 17.
2. Next, you noted down the number displayed next to it which is 407, 816, 1200-1220.
3. Your first target is Page 407. You opened a page in the middle, the page is greater than 500.
4. Then you moved to a somewhat lower page. But it still reads 310.
5. Then you moved to a higher page. You are very lucky you exactly got page 407. [Yes man you got it. Otherwise I need to write more. OK?]
6. That’s all, you started exploring what is written about Regular expression on that page, keeping in mind that you need to find page 816 also.

In the above scenario, the Index page is Non-Clustered index and the page numbers are clustered index arranged in a binary tree. See how you came to the page 407 very quickly. Your mind actually traversed the binary tree way left and right to reach the page 407 quickly.

Here, the class column with distinct values 1,2,3..12 will store the clustered index columns value along with it. Say for example; Let us take only class value of 1. The Index goes like this:

 Collapse | Copy Code

1: 100, 104, 105

So here, you can easily get all the records that have value for class = 1. Map this with the Book index example now. See you all in the next article.