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

Anbu Mani
8y
58.5k
0
5

Article

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.

[PrimaryKey, AutoIncrement, Column("_Id")]
public int id { get; set; } // AutoIncrement and set primarykey
[MaxLength(25)]
public string StudentName { get; set; }
[MaxLength(25)]
public string NickName { get; set; }
[MaxLength(25)]
public string Dept { get; set; }
[MaxLength(25)]
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().

using System.IO;
namespace SQLite
{
[Activity(Label = "SQLite", MainLauncher = true, Icon = "@drawable/icon")]
public class MainActivity : Activity
{
Button btninsert;
Button btnselect;
Button btnupdate;
Button btndelete;
protected override void OnCreate(Bundle bundle)
{
base.OnCreate(bundle);
SetContentView(Resource.Layout.Main);
btninsert = FindViewById<Button>(Resource.Id.btninsert);
btnselect = FindViewById<Button>(Resource.Id.btnselect);
btnupdate = FindViewById<Button>(Resource.Id.btnupdate);
btndelete = FindViewById<Button>(Resource.Id.btndelete);
CreateDB(); //Calling DB Creation method
btninsert.Click += delegate { StartActivity(typeof(InsertActivity)); };
btnselect.Click += delegate { StartActivity(typeof(SelectActivity)); };
btnupdate.Click += delegate { StartActivity(typeof(UpdateActivity)); };
btndelete.Click += delegate { StartActivity(typeof(DeleteActivity)); };
}
public string CreateDB()
{
var output = "";
output += "Creating Databse if it doesnt exists";
string dpPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "student.db3"); //Create New Database
var db = new SQLiteConnection(dpPath);
output += "\n Database Created....";
return output;
}
}
}

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;

public class InsertActivity : Activity
{
Button btncreate;
EditText txtname;
EditText txtnickname;
EditText txtdept;
EditText txtplace;
protected override void OnCreate(Bundle savedInstanceState)
{
base.OnCreate(savedInstanceState);
// Create your application here
SetContentView(Resource.Layout.Insert);
txtname = FindViewById<EditText>(Resource.Id.txtname);
txtnickname = FindViewById<EditText>(Resource.Id.txtnickname);
txtdept = FindViewById<EditText>(Resource.Id.txtdept);
txtplace = FindViewById<EditText>(Resource.Id.txtplace);
btncreate = FindViewById<Button>(Resource.Id.btnsave);
btncreate.Click += Btncreate_Click;
}
private void Btncreate_Click(object sender, EventArgs e)
{
try
{
string dpPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "student.db3");
var db = new SQLiteConnection(dpPath);
db.CreateTable<StudentTable>();
StudentTable tbl = new StudentTable();
tbl.StudentName = txtname.Text;
tbl.NickName = txtnickname.Text;
tbl.Dept = txtdept.Text;
tbl.Place = txtplace.Text;
db.Insert(tbl);
clear();
Toast.MakeText(this, "Record Added Successfully...,", ToastLength.Short).Show();
}
catch (Exception ex)
{
Toast.MakeText(this, ex.ToString(), ToastLength.Short).Show();
}
}
void clear()
{
txtname.Text = "";
txtnickname.Text = "";
txtdept.Text = "";
txtplace.Text = "";
}
}

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;

public class SelectActivity : Activity
{
Button btn_select;
EditText txt_id;
TextView txt_name;
TextView txt_nickname;
TextView txt_dept;
TextView txt_place;
protected override void OnCreate(Bundle savedInstanceState)
{
base.OnCreate(savedInstanceState);
// Create your application here
SetContentView(Resource.Layout.Select);
txt_name = FindViewById<TextView>(Resource.Id.txt_studentname);
txt_nickname = FindViewById<TextView>(Resource.Id.txt_nickname);
txt_dept = FindViewById<TextView>(Resource.Id.txt_dept);
txt_place = FindViewById<TextView>(Resource.Id.txt_place);
txt_id = FindViewById<EditText>(Resource.Id.txtid);
btn_select = FindViewById<Button>(Resource.Id.btn_select);
btn_select.Click += Btn_select_Click;
}
private void Btn_select_Click(object sender, EventArgs e)
{
clear();
string dpPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "student.db3"); //Call Database
var db = new SQLiteConnection(dpPath);
var data = db.Table<StudentTable>(); //Call Table
int idvalue = Convert.ToInt32(txt_id.Text);
var data1=(from values in data
where values.id== idvalue
select new StudentTable
{
StudentName = values.StudentName,
NickName = values.NickName,
Dept = values.Dept,
Place = values.Place
}).ToList<StudentTable>();
if(data1.Count>0)
{
foreach (var val in data1)
{
txt_name.Text = val.StudentName;
txt_nickname.Text = val.NickName;
txt_dept.Text = val.Dept;
txt_place.Text = val.Place;
}
}
else
{
Toast.MakeText(this, "Student Data Not Available", ToastLength.Short).Show();
}
}
void clear()
{
txt_name.Text = "";
txt_nickname.Text = "";
txt_dept.Text = "";
txt_place.Text = "";
}
}

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;

public class UpdateActivity : Activity
{
Button btn_get;
Button btn_update;
EditText txt_id;
EditText txt_updatename;
EditText txt_updatenickname;
EditText txt_updatedept;
EditText txt_updateplace;
protected override void OnCreate(Bundle savedInstanceState)
{
base.OnCreate(savedInstanceState);
// Create your application here
SetContentView(Resource.Layout.Update);
txt_updatename = FindViewById<EditText>(Resource.Id.txt_updatename);
txt_updatenickname = FindViewById<EditText>(Resource.Id.txt_updatenickname);
txt_updatedept = FindViewById<EditText>(Resource.Id.txt_updatedept);
txt_updateplace = FindViewById<EditText>(Resource.Id.txt_updateplace);
txt_id = FindViewById<EditText>(Resource.Id.txt_update_id);
btn_get = FindViewById<Button>(Resource.Id.btnget);
btn_update = FindViewById<Button>(Resource.Id.btn_update);
btn_get.Click += Btn_get_Click;
btn_update.Click += Btn_update_Click;
}
private void Btn_get_Click(object sender, EventArgs e)
{
//clear();
string dpPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "student.db3"); //Call Database
var db = new SQLiteConnection(dpPath);
var data = db.Table<StudentTable>(); //Call Table
int idvalue = Convert.ToInt32(txt_id.Text);
var data1 = (from values in data
where values.id == idvalue
select new StudentTable
{
StudentName = values.StudentName,
NickName = values.NickName,
Dept = values.Dept,
Place = values.Place
}).ToList<StudentTable>();
if (data1.Count > 0)
{
foreach (var val in data1)
{
txt_updatename.Text = val.StudentName;
txt_updatenickname.Text = val.NickName;
txt_updatedept.Text = val.Dept;
txt_updateplace.Text = val.Place;
}
}
else
{
Toast.MakeText(this, "Student Data Not Available", ToastLength.Short).Show();
}
}
private void Btn_update_Click(object sender, EventArgs e)
{
try
{
string dpPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "student.db3"); //Call Database
var db = new SQLiteConnection(dpPath);
var data = db.Table<StudentTable>();
int idvalue = Convert.ToInt32(txt_id.Text);
var data1 = (from values in data
where values.id == idvalue
select values).Single();
data1.StudentName = txt_updatename.Text;
data1.NickName = txt_updatenickname.Text;
data1.Dept = txt_updatedept.Text;
data1.Place = txt_updateplace.Text;
db.Update(data1);
Toast.MakeText(this, "Updated Successfully", ToastLength.Short).Show();
}
catch(Exception ex)
{
Toast.MakeText(this, ex.ToString(), ToastLength.Short).Show();
}
}
}

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;

public class DeleteActivity : Activity
{
Button btn_delete;
EditText txt_delete_id;
protected override void OnCreate(Bundle savedInstanceState)
{
base.OnCreate(savedInstanceState);
// Create your application here
SetContentView(Resource.Layout.Delete);
txt_delete_id = FindViewById<EditText>(Resource.Id.txt_delete_id);
btn_delete = FindViewById<Button>(Resource.Id.btn_delete);
btn_delete.Click += Btn_delete_Click;
}
private void Btn_delete_Click(object sender, EventArgs e)
{
string dpPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "student.db3"); //Call Database
var db = new SQLiteConnection(dpPath);
var data = db.Table<StudentTable>();
int idvalue = Convert.ToInt32(txt_delete_id.Text);
var data1 = data.Where(x => x.id == idvalue).FirstOrDefault();
if (data1.id!=null)
{
db.Delete(data1);
Toast.MakeText(this, "Delete Successfully", ToastLength.Short).Show();
txt_delete_id.Text = "";
}
else
{
Toast.MakeText(this, "Not Found", ToastLength.Short).Show();
}
}
}