Post
Ask Question

Create Android INSERT, SELECT, UPDATE and DELETE, Using SQLite Database

What is SQLite?
 
SQLite Database has methods to insert, select, update and delete, execute SQL commands, and perform other common database management tasks. More details>>
 
Let’s start.
 
Step 1: Open Visual Studio->New Project->Templates->Visual C#->Android->Blank App.
 
 
 
Select Blank App. Then, give Project Name and Project Location.
 
Step 2: Next, go to Solution Explorer-> Project Name-> References. Right click to Manage NuGet Packages and open a new Dialog box. In this dialog box, search for SQLite. Then, install SQLite-net Packages.
 
 
Step 3: Now, we need to create a database table so that we can create one data layer class. Go to Solution Explorer-> Project Name and right click to Add. Open a new Dialog box. Then, select Class and give it a name, such as, StudentTable.cs. Click Add.
 
 
Step 4: The next step is to Open Solution Explorer-> Project Name->Resources->layout->Main.axml. Click on Open Design View. Here, create four buttons.
 
 
 
Step 5: Next, Open Solution Explorer-> Project Name->StudentTable.cs. Click on Open CS code Page View and create table columns.
  1. [PrimaryKey, AutoIncrement, Column("_Id")]  
  2.   
  3. public int id { get; set; } // AutoIncrement and set primarykey  
  4.   
  5. [MaxLength(25)]  
  6.   
  7. public string StudentName { get; set; }  
  8.   
  9. [MaxLength(25)]  
  10.   
  11. public string NickName { get; set; }  
  12.   
  13. [MaxLength(25)]  
  14.   
  15. public string Dept { get; set; }  
  16.   
  17. [MaxLength(25)]  
  18.   
  19. public string Place { get; set; }  
Step 6: Now, open Solution Explorer-> Project Name->MainActivity.cs. Click on Open CS code Page view. Then, add the following namespaces. First, we create Database. So, after OnCreate(),  create a new method named as CreateDB().
  1. using System.IO;  
  2.   
  3. namespace SQLite  
  4. {  
  5. [Activity(Label = "SQLite", MainLauncher = true, Icon = "@drawable/icon")]  
  6. public class MainActivity : Activity  
  7. {  
  8. Button btninsert;  
  9. Button btnselect;  
  10. Button btnupdate;  
  11. Button btndelete;  
  12. protected override void OnCreate(Bundle bundle)  
  13. {  
  14. base.OnCreate(bundle);  
  15. SetContentView(Resource.Layout.Main);  
  16. btninsert = FindViewById<Button>(Resource.Id.btninsert);  
  17. btnselect = FindViewById<Button>(Resource.Id.btnselect);  
  18. btnupdate = FindViewById<Button>(Resource.Id.btnupdate);  
  19. btndelete = FindViewById<Button>(Resource.Id.btndelete);  
  20. CreateDB(); //Calling DB Creation method  
  21. btninsert.Click += delegate { StartActivity(typeof(InsertActivity)); };  
  22. btnselect.Click += delegate { StartActivity(typeof(SelectActivity)); };  
  23. btnupdate.Click += delegate { StartActivity(typeof(UpdateActivity)); };  
  24. btndelete.Click += delegate { StartActivity(typeof(DeleteActivity)); };  
  25. }  
  26. public string CreateDB()  
  27. {  
  28. var output = "";  
  29. output += "Creating Databse if it doesnt exists";  
  30. string dpPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "student.db3"); //Create New Database  
  31. var db = new SQLiteConnection(dpPath);  
  32. output += "\n Database Created....";  
  33. return output;  
  34. }  
  35. }  
  36. }  
Here, Database Name is “student.db3”.
 
INSERT
 
Step 7: After this, create Main Page. Insert new layout for insert operations. Next, Open the Solution Explorer-> Project Name->Layout. Right click on Add, and open new Dialog box. Then, select Android Layout and give it a name as Insert.axml. 
 
 
 
Step 8: Next, go to Solution Explorer-> Project Name, right click on Add, and open a new Dialog box. Then, select Activity. Give it a name as InsertActivity.cs and click on Add.
 
 
 
Step 9: Next, open the Solution Explorer-> Project Name->Resources->layout->Insert.axml. Click Open Design View.
 
 
 
Step 10: Open the Solution Explorer-> Project Name->InsertActivity.cs. Click Open CS code and then give it the following code.
 
Namespace: using System.IO;
  1. public class InsertActivity : Activity  
  2. {  
  3. Button btncreate;  
  4. EditText txtname;  
  5. EditText txtnickname;  
  6. EditText txtdept;  
  7. EditText txtplace;  
  8. protected override void OnCreate(Bundle savedInstanceState)  
  9. {  
  10. base.OnCreate(savedInstanceState);  
  11. // Create your application here  
  12. SetContentView(Resource.Layout.Insert);  
  13. txtname = FindViewById<EditText>(Resource.Id.txtname);  
  14. txtnickname = FindViewById<EditText>(Resource.Id.txtnickname);  
  15. txtdept = FindViewById<EditText>(Resource.Id.txtdept);  
  16. txtplace = FindViewById<EditText>(Resource.Id.txtplace);  
  17. btncreate = FindViewById<Button>(Resource.Id.btnsave);  
  18. btncreate.Click += Btncreate_Click;  
  19. }  
  20. private void Btncreate_Click(object sender, EventArgs e)  
  21. {  
  22. try  
  23. {  
  24. string dpPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "student.db3");  
  25. var db = new SQLiteConnection(dpPath);  
  26. db.CreateTable<StudentTable>();  
  27. StudentTable tbl = new StudentTable();  
  28. tbl.StudentName = txtname.Text;  
  29. tbl.NickName = txtnickname.Text;  
  30. tbl.Dept = txtdept.Text;  
  31. tbl.Place = txtplace.Text;  
  32. db.Insert(tbl);  
  33. clear();  
  34. Toast.MakeText(this, "Record Added Successfully...,", ToastLength.Short).Show();  
  35. }  
  36. catch (Exception ex)  
  37. {  
  38. Toast.MakeText(this, ex.ToString(), ToastLength.Short).Show();  
  39. }  
  40. }  
  41. void clear()  
  42. {  
  43. txtname.Text = "";  
  44. txtnickname.Text = "";  
  45. txtdept.Text = "";  
  46. txtplace.Text = "";  
  47. }  
  48. }  
Step 11: Press F5 or "Build and Run" the Application.
 
 
 
SELECT
 
Step 12: Again, insert a new layout for select operations. Open Solution Explorer-> Project Name->Layout. Right click on Add and open new Dialog box. Then, select Android Layout, give it a name as Select.axml, and click on Add. 
 
 
 
Step 13: Now, go to Solution Explorer-> Project Name. Right click on Add and open new Dialog box. Then, select Activity and name it as SelectActivity.cs. After that, click on Add to add the new activity class.
 
 
 
Step 14: Next, open Solution Explorer-> Project Name->Resources->layout->Select.axml. Click Open Design View.
 
 
 
Step 15: Open Solution Explorer-> Project Name->SelectActivity.cs. Click Open CS code. Write the following code.
 
Namespace: using System.IO;
  1. public class SelectActivity : Activity  
  2. {  
  3. Button btn_select;  
  4. EditText txt_id;  
  5. TextView txt_name;  
  6. TextView txt_nickname;  
  7. TextView txt_dept;  
  8. TextView txt_place;  
  9. protected override void OnCreate(Bundle savedInstanceState)  
  10. {  
  11. base.OnCreate(savedInstanceState);  
  12. // Create your application here  
  13. SetContentView(Resource.Layout.Select);  
  14. txt_name = FindViewById<TextView>(Resource.Id.txt_studentname);  
  15. txt_nickname = FindViewById<TextView>(Resource.Id.txt_nickname);  
  16. txt_dept = FindViewById<TextView>(Resource.Id.txt_dept);  
  17. txt_place = FindViewById<TextView>(Resource.Id.txt_place);  
  18. txt_id = FindViewById<EditText>(Resource.Id.txtid);  
  19. btn_select = FindViewById<Button>(Resource.Id.btn_select);  
  20. btn_select.Click += Btn_select_Click;  
  21. }  
  22. private void Btn_select_Click(object sender, EventArgs e)  
  23. {  
  24. clear();  
  25. string dpPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "student.db3"); //Call Database  
  26. var db = new SQLiteConnection(dpPath);  
  27. var data = db.Table<StudentTable>(); //Call Table  
  28. int idvalue = Convert.ToInt32(txt_id.Text);  
  29. var data1=(from values in data  
  30. where values.id== idvalue  
  31. select new StudentTable  
  32. {  
  33. StudentName = values.StudentName,  
  34. NickName = values.NickName,  
  35. Dept = values.Dept,  
  36. Place = values.Place  
  37. }).ToList<StudentTable>();  
  38. if(data1.Count>0)  
  39. {  
  40. foreach (var val in data1)  
  41. {  
  42. txt_name.Text = val.StudentName;  
  43. txt_nickname.Text = val.NickName;  
  44. txt_dept.Text = val.Dept;  
  45. txt_place.Text = val.Place;  
  46. }  
  47. }  
  48. else  
  49. {  
  50. Toast.MakeText(this, "Student Data Not Available", ToastLength.Short).Show();  
  51. }  
  52. }  
  53. void clear()  
  54. {  
  55. txt_name.Text = "";  
  56. txt_nickname.Text = "";  
  57. txt_dept.Text = "";  
  58. txt_place.Text = "";  
  59. }  
  60. }  
Step 16: Press F5 or "Build and Run" the Application.
 
 
 
UPDATE
 
Step 17: Insert new layout for Update operations. For this, open Solution Explorer-> Project Name->Layout. Right click on Add and open a new Dialog box. Then, select Android Layout, name as Update.axml, and click Add.
 
 
 
Step 18: Now, go to Solution Explorer-> Project Name, right click on Add, and open new Dialog box. Select Activity and name it as UpdateActivity.cs. Now, click on Add. 
 
 
 
Step 19: Next, open Solution Explorer-> Project Name->Resources->layout-> Update.axml. Click Open Design View.
 
 
 
Step 20: Open Solution Explorer-> Project Name-> UpdateActivity.cs. Click Open CS code and give the following code.
 
Namespace: using System.IO;
  1. public class UpdateActivity : Activity  
  2. {  
  3. Button btn_get;  
  4. Button btn_update;  
  5. EditText txt_id;  
  6. EditText txt_updatename;  
  7. EditText txt_updatenickname;  
  8. EditText txt_updatedept;  
  9. EditText txt_updateplace;  
  10. protected override void OnCreate(Bundle savedInstanceState)  
  11. {  
  12. base.OnCreate(savedInstanceState);  
  13. // Create your application here  
  14. SetContentView(Resource.Layout.Update);  
  15. txt_updatename = FindViewById<EditText>(Resource.Id.txt_updatename);  
  16. txt_updatenickname = FindViewById<EditText>(Resource.Id.txt_updatenickname);  
  17. txt_updatedept = FindViewById<EditText>(Resource.Id.txt_updatedept);  
  18. txt_updateplace = FindViewById<EditText>(Resource.Id.txt_updateplace);  
  19. txt_id = FindViewById<EditText>(Resource.Id.txt_update_id);  
  20. btn_get = FindViewById<Button>(Resource.Id.btnget);  
  21. btn_update = FindViewById<Button>(Resource.Id.btn_update);  
  22. btn_get.Click += Btn_get_Click;  
  23. btn_update.Click += Btn_update_Click;  
  24. }  
  25. private void Btn_get_Click(object sender, EventArgs e)  
  26. {  
  27. //clear();  
  28. string dpPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "student.db3"); //Call Database  
  29. var db = new SQLiteConnection(dpPath);  
  30. var data = db.Table<StudentTable>(); //Call Table  
  31. int idvalue = Convert.ToInt32(txt_id.Text);  
  32. var data1 = (from values in data  
  33. where values.id == idvalue  
  34. select new StudentTable  
  35. {  
  36. StudentName = values.StudentName,  
  37. NickName = values.NickName,  
  38. Dept = values.Dept,  
  39. Place = values.Place  
  40. }).ToList<StudentTable>();  
  41. if (data1.Count > 0)  
  42. {  
  43. foreach (var val in data1)  
  44. {  
  45. txt_updatename.Text = val.StudentName;  
  46. txt_updatenickname.Text = val.NickName;  
  47. txt_updatedept.Text = val.Dept;  
  48. txt_updateplace.Text = val.Place;  
  49. }  
  50. }  
  51. else  
  52. {  
  53. Toast.MakeText(this, "Student Data Not Available", ToastLength.Short).Show();  
  54. }  
  55. }  
  56. private void Btn_update_Click(object sender, EventArgs e)  
  57. {  
  58. try  
  59. {  
  60. string dpPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "student.db3"); //Call Database  
  61. var db = new SQLiteConnection(dpPath);  
  62. var data = db.Table<StudentTable>();  
  63. int idvalue = Convert.ToInt32(txt_id.Text);  
  64. var data1 = (from values in data  
  65. where values.id == idvalue  
  66. select values).Single();  
  67. data1.StudentName = txt_updatename.Text;  
  68. data1.NickName = txt_updatenickname.Text;  
  69. data1.Dept = txt_updatedept.Text;  
  70. data1.Place = txt_updateplace.Text;  
  71. db.Update(data1);  
  72. Toast.MakeText(this, "Updated Successfully", ToastLength.Short).Show();  
  73. }  
  74. catch(Exception ex)  
  75. {  
  76. Toast.MakeText(this, ex.ToString(), ToastLength.Short).Show();  
  77. }  
  78. }  
  79. }  
Step 21: Press F5 or "Build and Run" the Application.
 
 
 
DELETE
 
Step 22: Insert new layout for Delete Operations. Open Solution Explorer-> Project Name->Layout. Right click on Add and open new Dialog box. Then, select Android Layout and give it a name, Delete.axml. Just click Add.
 
 
 
Step 23: Now, go to Solution Explorer-> Project Name. Right click on Add to open new Dialog box. Then, select Activity and name it DeleteActivity.cs. Simply, click Add.
 
 
 
Step 24: Open Solution Explorer-> Project Name->Resources->layout-> Delete.axml. Click Open Design View.
 
 
 
Step 25: Open Solution Explorer-> Project Name-> DeleteActivity.cs. Click Open CS code. Then, give the following code.
 
Name Space: using System.IO;
  1. public class DeleteActivity : Activity  
  2. {  
  3. Button btn_delete;  
  4. EditText txt_delete_id;  
  5. protected override void OnCreate(Bundle savedInstanceState)  
  6. {  
  7. base.OnCreate(savedInstanceState);  
  8. // Create your application here  
  9. SetContentView(Resource.Layout.Delete);  
  10. txt_delete_id = FindViewById<EditText>(Resource.Id.txt_delete_id);  
  11. btn_delete = FindViewById<Button>(Resource.Id.btn_delete);  
  12. btn_delete.Click += Btn_delete_Click;  
  13. }  
  14. private void Btn_delete_Click(object sender, EventArgs e)  
  15. {  
  16. string dpPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "student.db3"); //Call Database  
  17. var db = new SQLiteConnection(dpPath);  
  18. var data = db.Table<StudentTable>();  
  19. int idvalue = Convert.ToInt32(txt_delete_id.Text);  
  20. var data1 = data.Where(x => x.id == idvalue).FirstOrDefault();  
  21. if (data1.id!=null)  
  22. {  
  23. db.Delete(data1);  
  24. Toast.MakeText(this, "Delete Successfully", ToastLength.Short).Show();  
  25. txt_delete_id.Text = "";  
  26. }  
  27. else  
  28. {  
  29. Toast.MakeText(this, "Not Found", ToastLength.Short).Show();  
  30. }  
  31. }  
  32. }  
Step 26: Press F5 or "Build and Run" the Application.
 
 
 
Finally, we have successfully created Xamarin Android INSERT, SELECT, UPDATE and DELETE operations, using SQLite Database.


Similar Articles