A student/parent database system developed for Kent Youth and Family Services.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

365 lines
13 KiB

using Dapper;
using DBWizard.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
using System.Linq;
namespace DBWizard
{
class SqliteDataAccess
{
#region student
// ***************** Create *****************
/* Inserts a new student to the SQL database.
* INPUT: student
* OUTPUT: new row to SQL database, null to program.
*/
internal void InsertNewStudent(Student stu)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "INSERT INTO STUDENTS (student_id, parent_id, FirstName, LastName, DOB, gender, address, program_id, school_id, GradeLevel, photo)" +
"VALUES (@student_id, @parent_id, @FirstName, @LastName, @DOB, @gender, @address, @program_id, @school_id, @GradeLevel, @photo)";
cnn.Execute(strQuery, stu);
}
}
// ***************** Read *****************
/* Gets a list of all students with the provided last name as equality.
* INPUT: String last name
* OUTPUT List<Student>
*/
public List<Student> FindStudentByLastname(string last_name)
{
//A using statement protects us as a failsafe: it guarantees the connection to the
//database will be closed, even in event of an application or computer crash.
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "SELECT * FROM students WHERE LastName=@LastName";
var output = cnn.Query<Student>(strQuery, new { LastName = last_name });
return output.ToList();
}
}
/* Gets a single student from the db given the db id field.
* This function is also usedl used in delete functionality of Form1.cs
* INPUT: integer database id
* OUTPUT: student
*/
public Student GetStudentByDbID(int dbid_in)
{
/* protect query from 0 or negative indexes.
* this is needed when creating a new student
* as dbid_in will be zero.
*/
if (dbid_in < 1)
return null;
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "SELECT * FROM students WHERE id=@id";
Student stu_out = cnn.Query<Student>(strQuery, new { id = dbid_in }).SingleOrDefault();
return stu_out;
}
}
//Can also be used for search function, but currently unused.
//TODO: cleanup if we decide not to use this.
public List<Student> FindStudentByStudentId(string studentid_in)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "SELECT * FROM students WHERE student_id=@studentid";
var output = cnn.Query<Student>(strQuery, new { studentid = studentid_in });
return output.ToList();
}
}
// ***************** Update *****************
/* Updates an individual student in the DB.
* Needs to do field mapping in the Execute call because
* the photo field can be null or not null, and
* the conditional operator can be used to change the query on the fly.
*
* INPUT: Student
* OUTPUT: Data to SQL Database, void to program
*/
internal void UpdateStudent(Student stu)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "UPDATE Students SET student_id = @student_id, FirstName = @First_Name, " +
"LastName = @Last_Name, DOB = @_dob, Gender = @_gender, Address = @_address, program_id=@_program_id, " +
"school_id=@_school_id, GradeLevel = @Grade_Level, Photo = @_photo " +
"WHERE id = @_id;";
cnn.Execute(strQuery, new
{
_id = stu.id,
student_id = stu.student_id,
First_Name = stu.FirstName,
Last_Name = stu.LastName,
_dob = stu.DOB,
_gender = stu.gender,
_address = stu.address,
_program_id = stu.program_id,
_school_id = stu.school_id,
Grade_Level = stu.GradeLevel,
_photo = stu.photo ?? null
});
}
}
// ***************** Delete *****************
/* Deletes a student record from the database.
* INPUT: student
* OUTPUT: void
*/
internal void DeleteStudent(Student stu_in)
{
//protect query from 0 or negative indexes.
if (stu_in.id <= 0)
return;
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "DELETE FROM STUDENTS WHERE id=@id;";
cnn.Execute(strQuery, stu_in);
}
}
#endregion
#region parent
// ***************** Create *****************
/* Inserts a new parent.
* returns parent id as the new row # it just inserted.
* INPUT: parent
* OUTPUT: int <new parent db id>
*/
internal int InsertNewParent(Parent par)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "INSERT INTO PARENTS (FirstName,LastName,PhoneNumber,EmailAddress)" +
"VALUES (@FirstName,@LastName,@PhoneNumber,@EmailAddress);" +
"SELECT MAX(parent_id) as parent_id from parents;";
int parent_id_out = cnn.Query<int>(strQuery, par).SingleOrDefault();
return parent_id_out;
}
}
// ***************** Read *****************
/* Gets a single Parent from the db given the parent_id db field.
* INPUT: int parent_id
* OUTPUT: Parent
*/
public Parent GetParentByID(int parent_id)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "SELECT * FROM parents WHERE parent_id=@id;";
var output = cnn.Query<Parent>(strQuery, new { id = parent_id });
var parList = output.ToList();
Parent parent = parList[0];
return parent;
}
}
// ***************** Update *****************
/* Update a parent to the SQL database.
* Needs to do the mapping as parent_id is not stored on the parent object of the form.
* but it is stored in the student object if the student is found.
*
* INPUT: Parent
* OUTPUT: Data to SQL database, void to program.
*/
internal void UpdateParent(Parent par)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "UPDATE Parents SET FirstName = @FirstName, LastName = @LastName," +
"PhoneNumber = @PhoneNumber, EmailAddress = @EmailAddress " +
"WHERE parent_id = @parent_id;";
cnn.Execute(strQuery, par);
}
}
// ***************** Delete *****************
/* Deletes a parent record from the database.
* INPUT: Parent
* OUTPUT: void
*/
internal void DeleteParent(Parent par_in)
{
//protect query from 0 or negative indexes.
if (par_in.parent_id <= 0)
return;
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "DELETE FROM Parents WHERE parent_id=@parent_id;";
cnn.Execute(strQuery, par_in);
}
}
#endregion
#region school
// ***************** Create *****************
/* Inserts a new school to the SQL database.
* INPUT: School
* OUTPUT: new row to SQL database, null to program.
*/
internal void InsertNewSchool(School sch)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "INSERT INTO SCHOOLS (name) VALUES (@name)";
cnn.Execute(strQuery, sch);
}
}
// ***************** Read *****************
/* Returns the list of available schools from the DB to fill the Schools drop-down.
* INPUT: no args
* OUTPUT: List<School>
*/
internal List<School> GetSchools()
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "SELECT name FROM schools;";
var output = cnn.Query<School>(strQuery, new DynamicParameters());
return output.ToList();
}
}
/* Returns the a single School from the DB by name.
* Needed for the school editor.
* INPUT: string school_name_in
* OUTPUT: School
*/
internal School GetSchoolByName(string school_name_in)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "SELECT name FROM Schools WHERE name = @school_name;";
School output = cnn.Query<School>(strQuery, new { school_name = school_name_in }).SingleOrDefault();
return output;
}
}
#endregion
#region Misc
/* Returns the list of available programs from the DB to fill the Programs drop-down.
* INPUT: no args
* OUTPUT: List<Program>
*/
internal List<Program> GetPrograms()
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "SELECT name FROM programs;";
var output = cnn.Query<Program>(strQuery, new DynamicParameters());
return output.ToList();
}
}
#endregion
#region reports
/* Gets a list of all students with the provided program id.
* INPUT: String last name
* OUTPUT List<Student>
*/
public List<Student> GetStudentsByProgram(int program_id_in)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "SELECT * FROM students WHERE program_id=@ProgramID";
var output = cnn.Query<Student>(strQuery, new { ProgramID = program_id_in });
return output.ToList();
}
}
/* Gets a list of all students with the provided school id.
* INPUT: String last name
* OUTPUT List<Student>
*/
public List<Student> GetStudentsBySchool(int school_id_in)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = "SELECT * FROM students WHERE school_id=@SchoolID";
var output = cnn.Query<Student>(strQuery, new { SchoolID = school_id_in });
return output.ToList();
}
}
/* Gets a list of all students with the provided school id.
* INPUT: String last name
* OUTPUT List<Student>
*/
public List<ReportFull> GetFullDBDump()
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
string strQuery = @"
SELECT
id,
student_id,
Students.FirstName,
Students.LastName,
DOB,
gender,
address,
GradeLevel,
Parents.FirstName as ParentFirstName,
Parents.LastName as ParentLastName,
PhoneNumber as ParentPhoneNumber,
EmailAddress as ParentEmailAddress,
Programs.name as ProgramName,
Schools.name as SchoolName
from students
INNER JOIN Parents on Students.parent_id = Parents.parent_id
INNER JOIN Programs on Students.program_id = Programs.program_id
INNER JOIN Schools on Students.school_id = Schools.school_id
";
var output = cnn.Query<ReportFull>(strQuery);
return output.ToList();
}
}
#endregion
private static string LoadConnectionString(string id = "Default")
{
return ConfigurationManager.ConnectionStrings[id].ConnectionString;
}
} //class SqliteDataAccess
}