How to retrieve data from SQL Database in web service using Android?

Hello Friends,
Myself Prashant.

Me giving u WebService Example for Android.

Part - 1:
In this part I giving small demo for webservice running in Android device.
From this link you can find this example. Where I give Comment for webservice demo.
Please all of you check it.
http://lukencode.com/2010/04/27/calling-web-services-in-android-using-httpclient/#comment-179

Part - 2: Webservice running in Android with database connectivity
Now I am running database connectivity and return value from database and showing in Android device.
Here i m using Microsoft SQL connectivity. You can use your way.
For that u have to download this file
SQL connectivity jar file for webservice:
http://www.4shared.com/file/IxoPooiE/sqljdbc.html
Ksoap Library File for android project.
http://www.4shared.com/file/uOhx5aoj/ksoap2-android-assembly-24-jar.html

This is good example for you all.

First We create Dynamic Project usign Eclipse
Add this package and class.
Package Name: org.webservice.pa
Class Name : GetDatabaseRecords.java
and also create once class for bean.(Setter-Getter Property).
Class Name : BookData.java


BookData.java

package org.webservice.pa;

public class BookData {
public String bookCode;
public String bookName;
public String authorName;

public String getBookCode() {
return bookCode;
}
public void setBookCode(String bookCode) {
this.bookCode = bookCode;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public String getAuthorName() {
return authorName;
}
public void setAuthorName(String authorName) {
this.authorName = authorName;
}

}


GetDatabaseRecords.java

package org.webservice.pa;
public class GetDatabaseRecords{

public BookData[] getBookDetails{
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
ArrayList bookDataList = new ArrayList();
BookData bookData = null;
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");//Driver Name
con = DriverManager.getConnection("connectionUrl");//connection URL
stmt = con.createStatement();
rs = stmt.executeQuery("select * from BookDetails");//Table Name
while (rs.next())
{
bookData= new BookData();
bookData.setBookCode(rs.getString("bookCode"));
bookData.setBookName(rs.getString("bookName"));
bookData.setAuthorName(rs.getString("authorName"));
bookDataList.add(bookData);
}
}catch (Exception e)
{
e.printStackTrace();
}
finally {
try {
rs.close();
} catch(Exception e) {}
try {
stmn.close();
} catch(Exception e) {}
try {
con.close();
} catch(Exception e) {}
}
return (BookData[])bookDataList.toArray(new BookData[bookDataList.size()]);
}
}

Now Right click on GetDatabaseRecords.java file and select "WebService" option.
Now further step is same as posted my Part 1:
Click finish.
and test your service it will return BookData object.
Here our Dynamic WebProject task is completed.


Now you want to create Android Project for calling above WebService.
Now u add "ksoap" library File in android project:
Download from here:
http://www.4shared.com/file/uOhx5aoj/ksoap2-android-assembly-24-jar.html

public class MainActivity extends Activity {
private String METHOD_NAME = "getBookDetails"; // our webservice method name
private String NAMESPACE = "http://pa.webservice.org"; // Here package name in webservice with reverse order.
private String SOAP_ACTION = NAMESPACE + METHOD_NAME; // NAMESPACE + method name
private static final String URL = "http://192.168.1.1:8080/WebServiceDemo/services/GetDatabaseRecords?wsdl"; // you must use ipaddress here, don't use Hostname or localhost

public void onCreate(Bundle icicle)
{
super.onCreate(icicle);
setContentView(R.layout.main);
TextView tv = (TextView) findViewById(R.id.txtsearch);
try
{
SoapObject request = new SoapObject(NAMESPACE, METHOD_NAME);
SoapSerializationEnvelope envelope = new SoapSerializationEnvelope(SoapEnvelope.VER11);
envelope.setOutputSoapObject(request);
HttpTransportSE androidHttpTransport = new HttpTransportSE(URL);
androidHttpTransport.call(SOAP_ACTION,envelope);
SoapObect so = (SoapObject)envelope.bodyIn;

System.out.println("Counter: "+so.getPropertyCount());

for(int i =0; i < so.getPropertyCount(); i++){
SoapObject soap = (SoapObject) so.getProperty(i);
System.out.println("BookCode :: "+ soap.getProperty("bookCode"));
System.out.println("BookName :: "+ soap.getProperty("bookName"));
System.out.println("AuthorName :: "+ soap.getProperty("authorName"));
}

tv.setText(so.toString());
} catch (Exception E) {
E.printStackTrace();
tv.setText("ERROR:" + E.getClass().getName() + ": " + E.getMessage());
}
}
}


Now you can run your project and it's work fine
:)

And if you have any doubt just tell me.


Thanks & Regards,
Prashant Adesara.