Skip to main content

Connect Android to MS SQL Database.


This is a continuation of the post on explaining login process using MS SQL Database. This post is about Insert, Update and delete process in the MS SQL database using Android application.

For configuring SQL to connect using IP address in a same local network (or Wifi network), please see this post.

Note : If you haven’t yet seen my first post on login application please do so, as it has the ConnectionClass and Database script and also AndroidManifest file details.


Make a new layout file in the application with name addproducts.xml and edit is as following :

addproducts.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
 xmlns:tools="http://schemas.android.com/tools"
 android:layout_width="match_parent"
 android:layout_height="match_parent"
 android:orientation="vertical"
 android:background="#282828">

 <LinearLayout
 android:layout_width="match_parent"
 android:layout_height="wrap_content"
 android:orientation="vertical"
 android:layout_alignParentTop="true"
 android:padding="2dp">

 <TextView
 android:layout_width="match_parent"
 android:layout_height="wrap_content"
 android:text="ADD PRODUCTS"
 android:layout_marginTop="7dp"
 android:typeface="sans"
 android:textSize="35sp"
 android:textColor="#ffffff"
 android:gravity="center" />

 <EditText
 android:layout_width="match_parent"
 android:layout_height="wrap_content"
 android:layout_marginTop="10dp"
 android:textColor="#ffffff"
 android:textColorHint="#ffffff"
 android:textStyle="bold"
 android:background="#5d5d5d"
 android:padding="10dp"
 android:hint="PRODUCT NAME"
 android:textSize="20sp"
 android:id="@+id/edtproname" />

 <EditText
 android:layout_width="match_parent"
 android:layout_height="wrap_content"
 android:textSize="16sp"
 android:hint="DESCRIPTION"
 android:textColor="#ffffff"
 android:textColorHint="#ffffff"
 android:textStyle="bold"
 android:background="#5d5d5d"
 android:padding="10dp"
 android:inputType="textMultiLine"
 android:maxLines="3"
 android:minLines="2"
 android:layout_gravity="top|left"
 android:layout_marginTop="5dp"
 android:id="@+id/edtprodesc" />

 <LinearLayout
 android:layout_width="match_parent"
 android:layout_height="wrap_content"
 android:weightSum="3"
 android:layout_marginTop="5dp"
 android:orientation="horizontal">

 <Button
 android:layout_width="0dp"
 android:layout_height="wrap_content"
 android:layout_weight="1"
 android:background="@drawable/btnbg"
 android:textColor="#ffffff"
 android:textSize="20sp"
 android:layout_margin="2dp"
 android:padding="7dp"
 android:layout_marginTop="10dp"
 android:id="@+id/btnadd"
 android:text="Add" />

 <Button
 android:layout_width="0dp"
 android:layout_height="wrap_content"
 android:layout_weight="1"
 android:layout_margin="2dp"
 android:background="@drawable/btnbg"
 android:textColor="#ffffff"
 android:textSize="20sp"
 android:padding="7dp"
 android:layout_marginTop="10dp"
 android:id="@+id/btnupdate"
 android:text="Update" />

 <Button
 android:layout_width="0dp"
 android:layout_height="wrap_content"
 android:layout_weight="1"
 android:background="@drawable/btnbg"
 android:textColor="#ffffff"
 android:textSize="20sp"
 android:layout_margin="2dp"
 android:padding="7dp"
 android:layout_marginTop="10dp"
 android:id="@+id/btndelete"
 android:text="Delete" />
 </LinearLayout>

 <ProgressBar
 android:layout_width="wrap_content"
 android:layout_height="wrap_content"
 android:layout_gravity="center"
 android:id="@+id/pbbar" />

 <ListView
 android:id="@+id/lstproducts"
 android:layout_width="match_parent"
 android:layout_height="wrap_content"
 android:divider="#808080"
 android:layout_marginTop="5dp"
 android:dividerHeight="1dp"
 android:padding="5dp">

 </ListView>
 </LinearLayout>
</RelativeLayout>

Another xml layout file with name lsttemplate.xml and edit it as following :

lsttemplate.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
 android:orientation="horizontal"
 android:layout_width="match_parent"
 android:layout_height="match_parent"
 android:weightSum="5"
 android:padding="5dp"
android:layout_marginTop="2dp"
 >

 <TextView
 android:layout_width="0dp"
 android:layout_height="wrap_content"
 android:textColor="#ffffff"
 android:layout_weight="1"
 android:textSize="15sp"
 android:text="1"
 android:id="@+id/lblproid"/>

 <TextView
 android:layout_width="0dp"
 android:layout_height="wrap_content"
 android:layout_weight="4"
 android:textColor="#ffffff"
 android:textSize="15sp"
 android:text="MOTOROLA"
 android:id="@+id/lblproname"/>


 <TextView
 android:layout_width="0dp"
 android:layout_height="wrap_content"
 android:layout_weight="0"
 android:textColor="#ffffff"
 android:textSize="0sp"
 android:text="MOTOROLA"
 android:id="@+id/lblprodesc"/>
</LinearLayout>

The first file addproducts.xml is the layout design file and lsttemplate.xml is the layout file for our listview to make up. I hope you find design good.

More on Android and MS SQL :

  1. Filling data in Android Spinner using MS SQL Database.
  2. Using Stored Procedure in Android from MS SQL Server
  3. Filling data in Android GridView using MS SQL Database.
  4. Filling data in Android Listview using MS SQL Database.
  5. Storing Images in MS SQL database in Base64 string values using Android
  6. Retrieving Images from MS SQL Database in Android.
  7. Storing images using ASP.NET webservice in MS SQL database from Android

 

 

Now add a class file in your application with name AddProducts.java

Declare following variables in this class :


    ConnectionClass connectionClass;
    EditText edtproname, edtprodesc;
    Button btnadd,btnupdate,btndelete;
    ProgressBar pbbar;
    ListView lstpro;
    String proid;

In the onCreate initialize this variables :


 super.onCreate(savedInstanceState);
        setContentView(R.layout.addproducts);

        connectionClass = new ConnectionClass();
        edtproname = (EditText) findViewById(R.id.edtproname);
        edtprodesc = (EditText) findViewById(R.id.edtprodesc);
        btnadd = (Button) findViewById(R.id.btnadd);
        btnupdate = (Button) findViewById(R.id.btnupdate);
        btndelete = (Button) findViewById(R.id.btndelete);
        pbbar = (ProgressBar) findViewById(R.id.pbbar);
        pbbar.setVisibility(View.GONE);
        lstpro = (ListView) findViewById(R.id.lstproducts);
        proid = "";

Here I am making four AsyncTask for doing background processes.
First make a AsyncTask task named FillList as following :

AsyncTask task 1 – FillList (to fill the data in listview and click listener of the list):


public class FillList extends AsyncTask<String, String, String> {
        String z = "";

        List<Map<String, String>> prolist  = new ArrayList<Map<String, String>>();

        @Override
        protected void onPreExecute() {

            pbbar.setVisibility(View.VISIBLE);
        }

        @Override
        protected void onPostExecute(String r) {

            pbbar.setVisibility(View.GONE);
            Toast.makeText(AddProducts.this, r, Toast.LENGTH_SHORT).show();

            String[] from = { "A", "B", "C" };
            int[] views = { R.id.lblproid, R.id.lblproname,R.id.lblprodesc };
            final SimpleAdapter ADA = new SimpleAdapter(AddProducts.this,
                    prolist, R.layout.lsttemplate, from,
                    views);
            lstpro.setAdapter(ADA);


            lstpro.setOnItemClickListener(new AdapterView.OnItemClickListener() {

                @Override
                public void onItemClick(AdapterView<?> arg0, View arg1,
                                        int arg2, long arg3) {
                    HashMap<String, Object> obj = (HashMap<String, Object>) ADA
                            .getItem(arg2);
                    proid = (String) obj.get("A");
                    String proname = (String) obj.get("B");
                    String prodesc = (String) obj.get("C");
                    edtprodesc.setText(prodesc);
                    edtproname.setText(proname);
               //     qty.setText(qtys);
                }
            });



        }

        @Override
        protected String doInBackground(String... params) {

            try {
                Connection con = connectionClass.CONN();
                if (con == null) {
                    z = "Error in connection with SQL server";
                } else {
                    String query = "select * from Producttbl";
                    PreparedStatement ps = con.prepareStatement(query);
                    ResultSet rs = ps.executeQuery();

                    ArrayList data1 = new ArrayList();
                    while (rs.next()) {
                        Map<String, String> datanum = new HashMap<String, String>();
                        datanum.put("A", rs.getString("Id"));
                        datanum.put("B", rs.getString("ProName"));
                        datanum.put("C", rs.getString("ProDesc"));
                        prolist.add(datanum);
                    }


                    z = "Success";
                }
            } catch (Exception ex) {
                z = "Error retrieving data from table";

            }
            return z;
        }
    }

Now add onClick Listeners to three buttons :


btnadd.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                AddPro addPro = new AddPro();
                addPro.execute("");

            }
        });
        btnupdate.setOnClickListener(new View.OnClickListener(){

            @Override
            public void onClick(View v) {
                UpdatePro updatePro = new UpdatePro();
                updatePro.execute("");

            }
        });

        btndelete.setOnClickListener(new View.OnClickListener(){

            @Override
            public void onClick(View v) {
                DeletePro deletePro = new DeletePro();
                deletePro.execute("");

            }
        });

AsyncTask task 2 – AddPro(to add product in our database):


public class AddPro extends AsyncTask<String, String, String> {



        String z = "";
        Boolean isSuccess = false;


        String proname = edtproname.getText().toString();
        String prodesc = edtprodesc.getText().toString();


        @Override
        protected void onPreExecute() {
            pbbar.setVisibility(View.VISIBLE);
        }

        @Override
        protected void onPostExecute(String r) {
            pbbar.setVisibility(View.GONE);
            Toast.makeText(AddProducts.this, r, Toast.LENGTH_SHORT).show();
            if(isSuccess==true) {
                FillList fillList = new FillList();
                fillList.execute("");
            }

        }

        @Override
        protected String doInBackground(String... params) {
            if (proname.trim().equals("") || prodesc.trim().equals(""))
                z = "Please enter User Id and Password";
            else {
                try {
                    Connection con = connectionClass.CONN();
                    if (con == null) {
                        z = "Error in connection with SQL server";
                    } else {

                        String dates = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH)
                                .format(Calendar.getInstance().getTime());
                        String query = "insert into Producttbl (ProName,ProDesc,OnDate) values ('" + proname + "','" + prodesc + "','" + dates + "')";
                        PreparedStatement preparedStatement = con.prepareStatement(query);
                        preparedStatement.executeUpdate();
                        z = "Added Successfully";
                        isSuccess = true;
                    }
                } catch (Exception ex) {
                    isSuccess = false;
                    z = "Exceptions";
                }
            }
            return z;
        }
    }

 

AsyncTask task 3 – UpdatePro(to Update a product in our database):


public class UpdatePro extends AsyncTask<String, String, String> {



        String z = "";
        Boolean isSuccess = false;


        String proname = edtproname.getText().toString();
        String prodesc = edtprodesc.getText().toString();


        @Override
        protected void onPreExecute() {
            pbbar.setVisibility(View.VISIBLE);
        }

        @Override
        protected void onPostExecute(String r) {
            pbbar.setVisibility(View.GONE);
            Toast.makeText(AddProducts.this, r, Toast.LENGTH_SHORT).show();
            if(isSuccess==true) {
                FillList fillList = new FillList();
                fillList.execute("");
            }

        }

        @Override
        protected String doInBackground(String... params) {
            if (proname.trim().equals("") || prodesc.trim().equals(""))
                z = "Please enter User Id and Password";
            else {
                try {
                    Connection con = connectionClass.CONN();
                    if (con == null) {
                        z = "Error in connection with SQL server";
                    } else {

                        String dates = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH)
                                .format(Calendar.getInstance().getTime());

                        String query = "Update Producttbl set ProName='"+proname+"',ProDesc='"+prodesc+"' , OnDate='"+dates+"' where Id="+proid;
                        PreparedStatement preparedStatement = con.prepareStatement(query);
                        preparedStatement.executeUpdate();
                        z = "Updated Successfully";

                        isSuccess = true;
                    }
                } catch (Exception ex) {
                    isSuccess = false;
                    z = "Exceptions";
                }
            }
            return z;
        }
    }

AsyncTask task 4 – DeletePro(to delete product in our database):


public class DeletePro extends AsyncTask<String, String, String> {



        String z = "";
        Boolean isSuccess = false;


        String proname = edtproname.getText().toString();
        String prodesc = edtprodesc.getText().toString();


        @Override
        protected void onPreExecute() {
            pbbar.setVisibility(View.VISIBLE);
        }

        @Override
        protected void onPostExecute(String r) {
            pbbar.setVisibility(View.GONE);
            Toast.makeText(AddProducts.this, r, Toast.LENGTH_SHORT).show();
            if(isSuccess==true) {
                FillList fillList = new FillList();
                fillList.execute("");
            }

        }

        @Override
        protected String doInBackground(String... params) {
            if (proname.trim().equals("") || prodesc.trim().equals(""))
                z = "Please enter User Id and Password";
            else {
                try {
                    Connection con = connectionClass.CONN();
                    if (con == null) {
                        z = "Error in connection with SQL server";
                    } else {

                        String dates = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH)
                                .format(Calendar.getInstance().getTime());

                        String query = "delete from Producttbl where Id="+proid;
                        PreparedStatement preparedStatement = con.prepareStatement(query);
                        preparedStatement.executeUpdate();
                        z = "Deleted Successfully";
                        isSuccess = true;
                    }
                } catch (Exception ex) {
                    isSuccess = false;
                    z = "Exceptions";
                }
            }
            return z;
        }
    }

Do you have any questions or queries on this post…please comment below and let me know what you think about this code.

Drop a mail to me on any one of this email addresses if you are interested in buying the source code. I will be replying back in 2-4 hours. The Cost of the source code is INR 500 or 9 USD. I will provide you with database copy. But I will not assist you in setting up if you are not able to connect the database yourself. Basic help can be provided. Email-Ids :
hitesh@parallelcodes.com
hitesh1120@gmail.com
awesomeblogcreation@gmail.com

 

More on Android and MS SQL :

Filling data in Android Spinner using MS SQL Database.

Using Stored Procedure in Android from MS SQL Server


Filling data in Android GridView using MS SQL Database.


Filling data in Android Listview using MS SQL Database.

Storing Images in MS SQL database in Base64 string values using Android

Retrieving Images from MS SQL Database in Android.

Storing images using ASP.NET webservice in MS SQL database from Android

 

 

Close