Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Windows.Storage;
using SQLite;
namespace SQLiteDatabase.Database
{
class DatabaseHelper
{
//Database name
public static string DbName = "Records.sqlite";
//Full Database Path
public static string DbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, DbName);
public async static Task<bool> Createdatabase()
{
var result = await Checkdatabase();
if (!result)
{
//Creating a database
var connection = new SQLiteConnection(DbPath);
{
//Creating a table
connection.RunInTransaction(() =>
{
connection.CreateTable<Schema.Students>();
});
}
connection.Close();
return true;
}
else
return false;
}
public static async Task<bool> Checkdatabase()
{
var dbexist = true;
try
{
var storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync(DbName);
if (storageFile == null) dbexist = false;
}
catch (Exception ex)
{
dbexist = false;
}
return dbexist;
}
public static async Task<bool> DeleteDatabase()
{
try
{
var storageFile = await StorageFile.GetFileFromPathAsync(DbPath);
await storageFile.DeleteAsync(StorageDeleteOption.PermanentDelete);
return true;
}
catch (Exception ex)
{
}
return false;
}
public static void AddNewStudent(Schema.Students obj)
{
SQLiteConnection connection;
using (connection = new SQLiteConnection(DbPath))
{
connection.RunInTransaction(() =>
{
connection.Insert(obj);
});
}
connection.Close();
}
public static void UpdateStudent(int id,int newmarks)
{
SQLiteConnection connection;
using (connection = new SQLiteConnection(DbPath))
{
var existingstudent =
connection.Query<Schema.Students>("select * from Students where Id =" + id).FirstOrDefault();
if (existingstudent != null)
{
existingstudent.Marks = newmarks;
connection.RunInTransaction(() =>
{
connection.Update(existingstudent);
});
}
}
}
public static bool DeleteStudent(int id)
{
SQLiteConnection connection;
using (connection = new SQLiteConnection(DbPath))
{
var existingstudent =
connection.Query<Schema.Students>("select * from Students where Id =" + id).FirstOrDefault();
connection.RunInTransaction(() =>
{
connection.Delete(existingstudent);
});
}
connection.Close();
return true;
}
public static ObservableCollection<Schema.Students> GetAllStudents()
{
SQLiteConnection connection;
using (connection = new SQLiteConnection(DbPath))
{
var myCollection = connection.Table<Schema.Students>().ToList<Schema.Students>();
var studentsList = new ObservableCollection<Schema.Students>(myCollection);
return studentsList;
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.