public Image GetBookImage(string isbn)
{
Image image = null;
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(image);
}
string command = "SELECT buch_image FROM [dbo].[t_s_buecher] WHERE buch_isbn = @0";
SqlDataReader dr = con.ExcecuteCommand(command, isbn);
while (dr.Read())
{
if (dr["buch_image"] != null && dr["buch_image"].ToString() != "")
{
using (var ms = new MemoryStream((byte[])(dr["buch_image"])))
{
image = Image.FromStream(ms);
}
}
}
dr.Close();
con.Close();
return(image);
}
/// <summary>
/// saves the assignment data of a school class and a grade to database
/// </summary>
/// <param name="assignmentTable"></param>
/// <param name="grade"></param>
public void SaveAssignment(DataTable assignmentTable, string grade)
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string RawCommand1 = "DELETE FROM [dbo].[t_s_klasse_stufe] WHERE ks_klassenstufe = @stufe";
con.ConnectError();
SqlCommand cmd1 = new SqlCommand(RawCommand1, con.Con);
cmd1.Parameters.AddWithValue("@stufe", grade);
cmd1.ExecuteNonQuery();
string RawCommand2 = "INSERT INTO [dbo].[t_s_klasse_stufe] (ks_klasse, ks_klassenstufe) VALUES (@klasse, @klassenstufe)";
foreach (DataRow row in assignmentTable.Rows)
{
SqlCommand cmd2 = new SqlCommand(RawCommand2, con.Con);
cmd2.Parameters.AddWithValue("@klasse", row[0].ToString());
cmd2.Parameters.AddWithValue("@klassenstufe", grade);
cmd2.ExecuteNonQuery();
}
con.Close();
}
/// <summary>
/// returns a subject by its short name or adds it and then gets it
/// </summary>
/// <param name="subjectNameShort"></param>
/// <returns></returns>
public Subject AddOrGetSubject(string subjectNameShort)
{
Subject subject = new Subject();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(subject);
}
string command = $"begin if not exists (select f_kurzform from t_s_faecher where f_kurzform=@0) begin insert into t_s_faecher (f_kurzform, f_langform) values (@1, @2) end end";
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@0", subjectNameShort);
cmd.Parameters.AddWithValue("@1", subjectNameShort);
cmd.Parameters.AddWithValue("@2", "");
cmd.ExecuteNonQuery();
command = "SELECT * FROM [dbo].[t_s_faecher] WHERE f_kurzform = @0";
SqlDataReader dr = con.ExcecuteCommand(command, subjectNameShort);
while (dr.Read())
{
subject.SubjectId = int.Parse(dr["f_id"].ToString());
subject.SubjectNameShort = dr["f_kurzform"].ToString();
subject.SubjectNameLong = dr["f_langform"].ToString();
}
dr.Close();
con.Close();
return(subject);
}
/// <summary>
/// fills a datatable with short book data
/// </summary>
/// <returns>a datatable</returns>
/// <param name="shortLoad">true: only title will be loaded; false: everything will be loaded</param>
private DataTable FillObject(bool shortLoad)
{
DataTable table = new DataTable();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(table);
}
string command = shortLoad ? "SELECT buch_isbn as 'ISBN', buch_titel as 'Titel' " +
"from t_s_buecher WHERE buch_activated = 1" :
"SELECT buch_isbn as 'ISBN',"
+ "buch_titel as 'Titel',"
+ "ger_name as 'Genre',"
+ "ver_name as 'Verlag',"
+ "ISNULL(stuff((SELECT distinct ', '+ cast(au_autor as varchar(512)) FROM t_s_buch_autor left join t_s_autor on au_id = ba_autorid where ba_isbn = buch_isbn FOR XML PATH('')),1,2,''),'') as 'Autor',"
+ "buch_erscheinungsdatum as 'Erscheinungsdatum',"
+ "sprach_name as 'Sprache',"
+ "buch_auflage as 'Auflage',"
+ "buch_neupreis as 'Neupreis' from t_s_buecher "
+ "left join t_s_genre on buch_genre_id = ger_id "
+ "left join t_s_verlag on buch_verlag_id = ver_id "
+ "left join t_s_sprache on buch_sprache_id = sprach_id WHERE buch_activated = 1";
SqlDataAdapter adapter = new SqlDataAdapter(command, con.Con);
adapter.Fill(table);
con.Close();
return(table);
}
/// <summary>
/// returns a datatable with all the user data
/// </summary>
/// <returns></returns>
private DataTable FillObject()
{
DataTable table = new DataTable();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(table);
}
string RawCommand = "SELECT b_name as 'Name', b_rechte as 'RechteID' FROM [dbo].[t_s_benutzer]";
SqlDataAdapter adapter = new SqlDataAdapter(RawCommand, con.Con);
adapter.Fill(table);
con.Close();
if (!table.Columns.Contains("Rechte"))
{
table.Columns.Add("Rechte");
}
foreach (DataRow dr in table.Rows)
{
int rechteID = int.Parse(dr["RechteID"].ToString());
dr["Rechte"] = (User.Permission)rechteID;
}
return(table);
}
/// <summary>
/// removes the subjects of all deactivated costumers
/// </summary>
private void DeleteSubjectsOfDeactivatedCostumers()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "DELETE FROM t_s_fach_kunde WHERE fs_kundenid in (SELECT kunde_id FROM t_s_kunden WHERE kunde_activated = 0)";
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.ExecuteNonQuery();
con.Close();
}
/// <summary>
/// deactivates all existing students
/// </summary>
public void DeactivateAllStudents()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "UPDATE t_s_kunden set kunde_activated = 0, kunde_klasse = null from t_s_kunden " +
"left join t_bd_ausgeliehen on kunde_ID=aus_kundenid WHERE kunde_klasse !='' AND kunde_klasse IS NOT NULL and aus_leihnummer is NULL";
SqlCommand cmd = new SqlCommand(command, con.Con);
DeleteSubjectsOfDeactivatedCostumers();
cmd.ExecuteNonQuery();
con.Close();
}
/// <summary>
/// fills a datatable with information
/// </summary>
private DataTable FillObject()
{
table = new DataTable();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(table);
}
string command = "SELECT f_id as 'ID', f_kurzform as 'Kürzel', f_langform as 'Langbezeichnung' FROM t_s_faecher";
SqlDataAdapter adapter = new SqlDataAdapter(command, con.Con);
adapter.Fill(table);
con.Close();
return(table);
}
/// <summary>
/// returns a datatable with grades and school classes
/// </summary>
/// <returns></returns>
private DataTable FillObject()
{
CustomSqlConnection con = new CustomSqlConnection();
DataTable table = new DataTable();
if (con.ConnectError())
{
return(table);
}
string RawCommand = "SELECT ks_klassenstufe as 'Klassenstufe', ks_klasse, k_bezeichnung as 'Klasse' FROM [dbo].[t_s_klasse_stufe] " +
"left join [dbo].[t_s_klassen] on k_id = ks_klasse order by ks_klasse";
SqlDataAdapter adapter = new SqlDataAdapter(RawCommand, con.Con);
adapter.Fill(table);
con.Close();
return(table);
}
/// <summary>
/// returns the id of a subject by its short name
/// </summary>
/// <param name="subjectNameShort"></param>
/// <returns>returns -1 if no subject was found; returns >=0 if a subject was found</returns>
public int GetIdBySubjectShortName(string subjectNameShort)
{
int subjectId = -1;
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(subjectId);
}
string command = "SELECT f_id FROM [dbo].[t_s_faecher] WHERE f_kurzform = @0";
SqlDataReader dr = con.ExcecuteCommand(command, subjectNameShort);
while (dr.Read())
{
subjectId = int.Parse(dr["f_id"].ToString());
}
dr.Close();
con.Close();
return(subjectId);
}
/// <summary>
/// checks whether a subject with the given short name already exists or not
/// </summary>
/// <returns></returns>
/// <param name="subjectNameShort">the short name of a subject</param>
public bool SubjectExists(string subjectNameShort)
{
CustomSqlConnection con = new CustomSqlConnection();
int subjectId = -1;
if (con.ConnectError())
{
return(true);
}
string RawCommand = "SELECT f_id FROM t_s_faecher WHERE f_kurzform = @0";
SqlDataReader dr = con.ExcecuteCommand(RawCommand, subjectNameShort);
while (dr.Read())
{
subjectId = int.Parse(dr["f_id"].ToString());
}
dr.Close();
con.Close();
return(subjectId > -1 ? true : false);
}
/// <summary>
/// returns the grade of a school class
/// </summary>
/// <param name="schoolClassId"></param>
/// <returns>returns >=0 if grade exists, returns -1 if no grade exists</returns>
public int GetGradeOfSchoolClass(int schoolClassId)
{
int grade = -1;
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(grade);
}
string RawCommand = "SELECT ks_klassenstufe FROM [dbo].[t_s_klasse_stufe] WHERE ks_klasse = @0";
SqlDataReader dr = con.ExcecuteCommand(RawCommand, schoolClassId);
while (dr.Read())
{
grade = Convert.ToInt32(dr["ks_klassenstufe"].ToString());
}
dr.Close();
con.Close();
return(grade);
}
/// <summary>
/// fills a datatable with necessary data for book search formular
/// </summary>
/// <param name="grade"></param>
/// <returns></returns>
private DataTable FillObjectSearch(int grade)
{
DataTable table = new DataTable();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(table);
}
string command = "SELECT buch_isbn as 'ISBN', buch_titel as 'Titel' FROM t_s_buchid " +
"left join t_s_buecher on buch_isbn = bu_isbn left join t_s_buch_stufe on bs_isbn = bu_isbn " +
"WHERE bs_klassenstufe = @grade AND bu_activated = 1 AND bu_id NOT IN (SELECT aus_buchid FROM t_bd_ausgeliehen) " +
"group by buch_isbn, buch_titel";
SqlDataAdapter adapter = new SqlDataAdapter(command, con.Con);
adapter.SelectCommand.Parameters.AddWithValue("@grade", grade);
adapter.Fill(table);
con.Close();
return(table);
}
/// <summary>
/// returns a datatable with costumer data
/// </summary>
/// <returns></returns>
private DataTable FillObject()
{
DataTable table = new DataTable();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(table);
}
string command = "use greenLib SELECT kunde_ID as 'Kunden-ID', kunde_vorname as 'Vorname', kunde_nachname as 'Nachname', kunde_geburtsdatum as 'Geburtsdatum', " +
"k_bezeichnung as 'Klasse',CONCAT(stuff(( SELECT distinct ', '+ cast(f_kurzform as varchar(10)) FROM t_s_fach_kunde fk LEFT JOIN t_s_faecher f on fk.fs_fachid=f.f_id " +
"WHERE fk.fs_kundenid=k.kunde_ID AND fk.fs_lk=1 FOR XML PATH('')),1,1,'')+',',stuff(( SELECT distinct ', '+ cast(f_kurzform as varchar(10)) FROM t_s_fach_kunde fk " +
"LEFT JOIN t_s_faecher f on fk.fs_fachid=f.f_id WHERE fk.fs_kundenid=k.kunde_ID AND fk.fs_lk=0 FOR XML PATH('')),1,1,'')) as 'Fächer', kunde_strasse as 'Straße', " +
"kunde_hausnummer as 'Hausnummer', kunde_postleitzahl as 'Postleitzahl', kunde_ort as 'Wohnort', kunde_mail as 'Mail', kunde_telefonnummer as 'Telefonnummer' " +
"FROM t_s_kunden k LEFT JOIN t_s_klassen kl on k.kunde_klasse=kl.k_id WHERE kunde_activated = 1";
SqlDataAdapter adapter = new SqlDataAdapter(command, con.Con);
adapter.Fill(table);
con.Close();
return(table);
}
/// <summary>
/// saves the changes of a grid to database
/// </summary>
/// <param name="grid"></param>
public void SaveGridChangesToDatabase(ref MetroFramework.Controls.MetroGrid grid)
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
DataTable changes = table.GetChanges();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand($"SELECT * FROM t_s_faecher", con.Con);
SqlCommand insertCmd = new SqlCommand($"INSERT INTO t_s_faecher (f_kurzform, f_langform) VALUES (@subjectNameShort, @subjectNameLong)", con.Con);
insertCmd.Parameters.Add("@subjectNameShort", SqlDbType.NVarChar, 64, "Kürzel");
insertCmd.Parameters.Add("@subjectNameLong", SqlDbType.NVarChar, 256, "Langbezeichnung");
adapter.InsertCommand = insertCmd;
SqlCommand deleteCmd = new SqlCommand($"DELETE FROM t_s_faecher WHERE f_kurzform = @subjectNameShort", con.Con);
deleteCmd.Parameters.Add("@subjectNameShort", SqlDbType.NVarChar, 64, "Kürzel");
adapter.DeleteCommand = deleteCmd;
SqlCommand updateCmd = new SqlCommand($"UPDATE t_s_faecher SET f_kurzform = @subjectNameShort, f_langform = @subjectNameLong WHERE f_id = @subjectId", con.Con);
updateCmd.Parameters.Add("@subjectNameShort", SqlDbType.NVarChar, 64, "Kürzel");
updateCmd.Parameters.Add("@subjectNameLong", SqlDbType.NVarChar, 256, "Langbezeichnung");
updateCmd.Parameters.Add("@subjectId", SqlDbType.Int, 32, "ID");
adapter.UpdateCommand = updateCmd;
if (changes != null)
{
changes = noDuplicates(changes);
adapter.Update(changes);
}
}
/// <summary>
/// returns a subject by its short name
/// </summary>
/// <param name="subjectNameShort"></param>
/// <returns></returns>
public Subject GetSubjectByShortName(string subjectNameShort)
{
Subject subject = new Subject();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(subject);
}
string command = "SELECT * FROM [dbo].[t_s_faecher] WHERE f_kurzform = @0";
SqlDataReader dr = con.ExcecuteCommand(command, subjectNameShort);
while (dr.Read())
{
subject.SubjectId = int.Parse(dr["f_id"].ToString());
subject.SubjectNameShort = dr["f_kurzform"].ToString();
subject.SubjectNameLong = dr["f_langform"].ToString();
}
dr.Close();
con.Close();
return(subject);
}
/// <summary>
/// fills a datatable with costumer data
/// </summary>
/// <param name="showCostumerClass"></param>
/// <param name="classId"></param>
private DataTable FillCostumerDataTable(bool showCostumerClass, int classId)
{
DataTable table = new DataTable();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(table);
}
string command = showCostumerClass ? "SELECT kunde_ID, kunde_vorname as 'Vorname', kunde_nachname as 'Nachname', " +
"kunde_klasse, k_bezeichnung as 'Klasse', ks_klassenstufe as 'Klassenstufe' FROM [dbo].[t_s_kunden] " +
"left join [dbo].[t_s_klassen] on k_id = kunde_klasse left join [dbo].[t_s_klasse_stufe] on ks_klasse = kunde_klasse " +
"WHERE kunde_activated = 1 AND ks_klassenstufe = @classId" :
"SELECT kunde_ID, kunde_vorname as 'Vorname', kunde_nachname as 'Nachname', kunde_klasse, k_bezeichnung as 'Klasse', " +
"ks_klassenstufe as 'Klassenstufe' FROM [dbo].[t_s_kunden] left join [dbo].[t_s_klassen] on k_id = kunde_klasse " +
"left join [dbo].[t_s_klasse_stufe] on ks_klasse = kunde_klasse WHERE kunde_activated = 1 AND kunde_klasse = @classId " +
"order by Klasse";
SqlDataAdapter adapter = new SqlDataAdapter(command, con.Con);
adapter.SelectCommand.Parameters.AddWithValue("@classId", classId);
adapter.Fill(table);
con.Close();
return(table);
}
public HolidayRepository(CustomSqlConnection connection, HolidayTableQuery query)
{
_connection = connection;
_query = query;
}
//inject IOption here
public YourNewClass(IOptions <MyConfig> _config)
{
_connection = new CustomSqlConnection(_config); //use this instance for all the repository methods
}
