Post
Ask Question

Android Manage MSSQL Data Table CRUD With MVC API For Beginners

Introduction

 
My previous article, Angular Manage MSSQL Table CRUD With MVC Web API for Beginners, is related to managing data table records with a JSON object on a server using SQL database tables and MVC APIs. This tutorial is for beginners/freshers or students. Now again, we are accessing CRUD functionality in Android by using MVC Web API. I explain all data operations, and how to access APIs in Android applications. I hope it will be helpful for you to get/update or save data on the server using Android applications.
 

Table CRUD with API in Android

 
First, read the previous article carefully then download code from the previous article and install as per instruction, or if you know them already, then just download code for the Android application and change it as per your system setting URL of localhost.
 
Note
No validation testing, please ignore.
  • Add a new record by inputting data in the field name, age, and city, then click on the Save button.
  • Edit select record then update data in the field name, age, and city then click on the Save button.
  • Delete select record then click on delete button.
  • Refresh button to reload data in list.
Example Screenshot
 
Android Manage MSSQL Data Table CRUD With MVC API For Beginner
 

Section First

 
Create a new empty project in Android. Then open build.gradle(app) add compile files ('libs/httpmime-4.2.1.jar') and implementation 'com.google.code.gson:gson:2.2.4' in dependencies section. Then add useLibrary 'org.apache.http.legacy' in android section. Both are showing in a red circle in the below android app structure. You can also download httpmime-4.2.1.jar and copy the jar file in the android libs folder.
 
Android Manage MSSQL Data Table CRUD With MVC API For Beginner
 
Now next add LinearLayout on the root in orientation vertical, then add three child element two LinearLayout and one is ListView. First, in the two child LinearLayout add three EditText, and in the next child, LinearLayout add three Buttons in a horizontal orientation. The design below shows an example.
 
Android Manage MSSQL Data Table CRUD With MVC API For Beginner
 
Now I added one interface method processFinish(String) to get AsyncResponse when the AsyncTask call finished. Next derived class MyAsyncTask from AsyncTask. Constructor received three-parameter context, action, and param. Context is used to run progress call spinner. Our example has not used ProgressDialog option to show a loader view. Method onPreExecute() is used to pre-setup of variables before calling method doInBackground() if requires. Method doInBackground() calls static method POST which is available in MyUtility class, and PSOT calls again inside APICALLJSON(String action,String body) method. Then finally calls method onPostExecute(String) if the background request call is finished.
  1. public interface AsyncResponse {  
  2.     void processFinish(String output);  
  3. }  
  4.   
  5. public class MyAsyncTask  extends AsyncTask<String, Void, String> {  
  6.   
  7.     public Context context;  
  8.     public AsyncResponse delegate = null;  
  9.     private String soapAction,callParam;  
  10.   
  11.     //private OnTaskCompleted listener;  
  12.     public MyAsyncTask(Context _context,String _soapAction,String _callParam){  
  13.         soapAction=_soapAction;  
  14.         callParam=_callParam;  
  15.         this.context=_context;  
  16.     }  
  17.     @Override  
  18.     protected  void onPreExecute() {  
  19.     }  
  20.     @Override  
  21.     protected String doInBackground(String... urls) {  
  22.         return MyUtility.POST(soapAction,callParam);  
  23.     }  
  24.     @Override  
  25.     protected void onPostExecute(String result) {  
  26.         delegate.processFinish(result);  
  27.     }  
  28.   
  29. }  
Now the below method belongs to class MyUtility.java. This is static method which name is APICALLJSON(String action,String body). Action is the name of the API function name, and the body is a JSON body off parameter. API URL is localhost of your computer IP address. Create a HttpClient object to call request. Also create object of HttpPost and set header in request. Finally, execute the HTTP client and check the response status and get a response, convert to string and return to call location.
  1. public class MyUtility {  
  2.   
  3.     public static String POST(String action, String callparam) {  
  4.         String sResult = "";  
  5.         Log.e("Post:==>", action+"=="+callparam);  
  6.         try {  
  7.             sResult=APICALLJSON(action,callparam);  
  8.   
  9.         } catch (Exception e) {  
  10.             Log.d("Post", e.getMessage());  
  11.         }  
  12.         return sResult;  
  13.     }  
  14.   
  15.     public static String APICALLJSON(String action, String body) {  
  16.         // you can use your api url  
  17.         final String urlPost="http://192.168.42.82/mywapi/api/tcrud/"+action;  
  18.         String sResult = "";  
  19.         try {  
  20.             HttpClient httpclient = new DefaultHttpClient();  
  21.             HttpPost httppost = new HttpPost(urlPost);  
  22.             httppost.setHeader("Accept-Encoding", "gzip,deflate");  
  23.             httppost.setHeader("Content-Type", "application/json");  
  24.             //httppost.setHeader("Host", "www.example.com");  
  25.             httppost.setHeader("Host", "192.168.42.82");  
  26.             httppost.setHeader("Connection", "Keep-Alive");  
  27.             httppost.setHeader("User-Agent", "Apache-HttpClient/4.1.1");  
  28.             StringEntity se = new StringEntity(body);  
  29.             httppost.setEntity(se);  
  30.             HttpResponse resp = httpclient.execute(httppost);  
  31.             if(resp.getStatusLine().getStatusCode()== HttpStatus.SC_OK){  
  32.                 HttpEntity ent = resp.getEntity();  
  33.                 sResult = EntityUtils.toString(ent);  
  34.                 return sResult;  
  35.             }  
  36.         } catch (Exception e) {  
  37.             Log.d("Post", e.getMessage());  
  38.         }  
  39.         finally {  
  40.             sResult="";  
  41.         }  
  42.         return sResult;  
  43.     }  
  44.   
  45. }  
Next is a created model class which is named MyPerson. This class property is related to the SQL table TblPerson, and the script is available in my previous article Angular Manage MSSQL Table CRUD With MVC Web API for Beginners. The other method selectRecord(String) is created to get List of MyPerson class objects. Because we received json response and by using Gson() to convert string json to object list of MyPerson object. Added get, set property of Id, Name, Age and City variable. Also, I created another two method getPersonRow() and setPersonRow(). Method getPersonRow is used to get rows of single record MyPerson class. Another is to convert a string to object of MyPerson class.
  1. public class MyPerson {  
  2.     // data table field  
  3.     private int Id;  
  4.     private String Name;  
  5.     private int Age;  
  6.     private String City;  
  7.     // data table field get set property  
  8.     public int getId() {  
  9.         return Id;  
  10.     }  
  11.     public void setId(int id) {  
  12.         Id = id;  
  13.     }  
  14.     public String getName() {  
  15.         return Name;  
  16.     }  
  17.     public void setName(String name) {  
  18.         Name = name;  
  19.     }  
  20.     public int getAge() {  
  21.         return Age;  
  22.     }  
  23.     public void setAge(int age) {  
  24.         Age = age;  
  25.     }  
  26.     public String getCity() {  
  27.         return City;  
  28.     }  
  29.     public void setCity(String city) {  
  30.         City = city;  
  31.     }  
  32.     // return person data row as string  
  33.     public String getPersonRow(){  
  34.         return String.valueOf(this.getId())+", "+this.getName()+", "+String.valueOf(this.getAge())+", "+this.getCity();  
  35.     }  
  36.     // return MyPerson object, split row and set to object.  
  37.     public MyPerson setPersonRow(String row){  
  38.         String []sAry=row.split(",");  
  39.         MyPerson objP=new MyPerson();  
  40.         objP.setId(Integer.parseInt(sAry[0].trim()));  
  41.         objP.setName(sAry[1].trim());  
  42.         objP.setAge(Integer.parseInt(sAry[2].trim()));  
  43.         objP.setCity(sAry[3].trim());  
  44.         return objP;  
  45.     }  
  46.     // convert json data to object list of MyPerson  
  47.     public List<MyPerson> selectRecord(String sResponse){  
  48.         List<MyPerson> objList =null;  
  49.         sResponse=sResponse.replace("\\","");  
  50.         sResponse=sResponse.substring(1,sResponse.length()-1);  
  51.         try{  
  52.             Gson gson = new Gson();  
  53.             Type listType = new TypeToken<List<MyPerson>>() {}.getType();  
  54.             objList  = gson.fromJson(sResponse,listType);  
  55.             return objList;  
  56.         }  
  57.         catch(Exception ex){  
  58.             return null;  
  59.         }  
  60.     }  
  61. }  
Finally, we are taking the MainActivity class of this tutorial. In our example, the MainActivity class is extends AppCompatActivity implements AsyncResponse. Next, get the object from design view in android. Here, it shows a button function method of save, refresh and delete in all function setup action type and initialize asynctask object by calling taskInitSetup which received the API action name and body as a JSON string.
  1. public class MainActivity extends AppCompatActivity implements AsyncResponse{  
  2.   
  3.     private EditText etname,etage,etcity;  
  4.     private Button btnsave, btnrefresh,btndelete;  
  5.     private ListView lvperson;  
  6.     private MyAsyncTask myAsyncTask = null;  
  7.     private String sActionType,sqryprmjson;  
  8.     private int iPersonId;  
  9.     @Override  
  10.     protected void onCreate(Bundle savedInstanceState) {  
  11.         super.onCreate(savedInstanceState);  
  12.         setContentView(R.layout.activity_main);  
  13.   
  14.         //get object from design view;  
  15.         etname=(EditText)findViewById(R.id.etname);  
  16.         etage=(EditText)findViewById(R.id.etage);  
  17.         etcity=(EditText)findViewById(R.id.etcity);  
  18.         btnsave=(Button)findViewById(R.id.btnsave);  
  19.         btnrefresh=(Button)findViewById(R.id.btnrefresh);  
  20.         btndelete=(Button)findViewById(R.id.btndelete);  
  21.         lvperson=(ListView)findViewById(R.id.lvperson);  
  22.         iPersonId=0;  
  23.         sActionType="";  
  24.         btnsave.setOnClickListener(new View.OnClickListener() {  
  25.             @Override  
  26.             public void onClick(View view) {  
  27.                 sActionType="S";//save new record or edit record  
  28.                 String sname=etname.getText().toString();  
  29.                 String sage=etage.getText().toString();  
  30.                 String scity=etcity.getText().toString();  
  31.                 if (iPersonId > 0)  
  32.                     sqryprmjson = "{\"StrQry\":\"UPDATE [TblPerson] SET [Name]=\'" + sname + "\',[Age]=" + sage + ",[City]=\'" + scity + "\' WHERE [Id]=" + iPersonId + "\"}";  
  33.                 else  
  34.                     sqryprmjson = "{\"StrQry\":\"INSERT INTO [TblPerson] ([Name],[Age],[City]) VALUES(\'" + sname + "\'," + sage + ",\'" + scity + "\')\"}";  
  35.                 myAsyncTask=taskInitSetup("recsave",sqryprmjson);  
  36.                 myAsyncTask.execute();  
  37.             }  
  38.         });  
  39.         btnrefresh.setOnClickListener(new View.OnClickListener() {  
  40.             @Override  
  41.             public void onClick(View view) {  
  42.                 sActionType="R";//refresh or reload  
  43.                 etname.setText("");etage.setText(""); etcity.setText("");  
  44.                 iPersonId= 0;  
  45.                 sqryprmjson="{'StrQry':'SELECT * FROM [TblPerson]'}";  
  46.                 myAsyncTask=taskInitSetup("recselect",sqryprmjson);  
  47.                 myAsyncTask.execute();  
  48.             }  
  49.         });  
  50.         btndelete.setOnClickListener(new View.OnClickListener() {  
  51.             @Override  
  52.             public void onClick(View view) {  
  53.                 if(iPersonId==0)return;  
  54.                 sActionType="D";//delete record  
  55.                 sqryprmjson="{'StrQry':'DELETE FROM [TblPerson] WHERE Id="+iPersonId+"'}";  
  56.                 myAsyncTask=taskInitSetup("recselect",sqryprmjson);  
  57.                 myAsyncTask.execute();  
  58.             }  
  59.         });  
  60.         btnrefresh.callOnClick();  
  61.     }  
  62.     public MyAsyncTask taskInitSetup(String _soapAction, String _sBody) {  
  63.         MyAsyncTask _task = new MyAsyncTask(this, _soapAction, _sBody);  
  64.         _task.delegate = MainActivity.this;  
  65.         return _task;  
  66.     }  
The below method is called when async task is request finished and return for output. Because I have use switch case due to different action type request in our example which is Save,Refresh,Delete.Edit or Delete call when you select record in listview, then you can call method.fillListView function takes json data as string, and convert json string to List. Next, we create ArrayAdapter for listview to set and display data.
  1. @Override  
  2.     public void processFinish(String output) {  
  3.         myAsyncTask=null;  
  4.         iPersonId=0;  
  5.         if(output!=null && output.trim()!="") {  
  6.             switch (sActionType) {  
  7.                 case "D":  
  8.                     btnrefresh.callOnClick();  
  9.                     break;  
  10.                 case "S":  
  11.                     btnrefresh.callOnClick();  
  12.                     break;  
  13.                 case "R":  
  14.                     fillListView(output);  
  15.                     break;  
  16.             }  
  17.   
  18.         }else{  
  19.             Toast.makeText(getApplicationContext(), "Try again...!", Toast.LENGTH_LONG).show();  
  20.         }  
  21.     }  
  22.   
  23.     public void fillListView(String output){  
  24.         sActionType="";  
  25.         List<MyPerson> list = null;  
  26.         MyPerson obj;  
  27.         obj = new MyPerson();  
  28.         list = obj.selectRecord(output);  
  29.         try{  
  30.             if(list!=null && list.size()>0){  
  31.                 String[] personRow=new String[list.size()];  
  32.                 for(int r=0;r<list.size();r++)  
  33.                     personRow[r]=list.get(r).getPersonRow();  
  34.                 ArrayAdapter <String>adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, personRow);  
  35.                 lvperson.setAdapter(adapter);  
  36.                 lvperson.setOnItemClickListener(new AdapterView.OnItemClickListener() {  
  37.                     @Override  
  38.                     public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {  
  39.                         String sRow=adapterView.getItemAtPosition(i).toString();  
  40.                         MyPerson objP=new MyPerson();  
  41.                         objP=objP.setPersonRow(sRow);  
  42.                         etname.setText(objP.getName());  
  43.                         etage.setText(String.valueOf(objP.getAge()));  
  44.                         etcity.setText(objP.getCity());  
  45.                         iPersonId= objP.getId();  
  46.                     }  
  47.                 });  
  48.             }else{  
  49.                 lvperson.setAdapter(null);  
  50.                 Toast.makeText(getApplicationContext(), "No record found.", Toast.LENGTH_LONG).show();  
  51.             }  
  52.         }catch (Exception ex){  
  53.             Toast.makeText(getApplicationContext(), ex.getMessage(), Toast.LENGTH_LONG).show();  
  54.         }  
  55.     }  
  56. }  

Conclusion

 
This article showed and explained to beginner/fresher/student how to use an API request in Android and manage a data table by calling. Also, it showed the use of SQL, MVC API, and an Android app to build good mobile apps.


Similar Articles