/// <summary>
/// saves the assignment
/// </summary>
/// <param name="assignmentTable"></param>
/// <param name="grade"></param>
public void SaveAssignment(DataTable assignmentTable, int grade)
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "DELETE FROM [dbo].[t_s_fach_stufe] WHERE bf_klassenstufe = @grade";
con.ConnectError();
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@grade", grade);
cmd.ExecuteNonQuery();
command = "INSERT INTO [dbo].[t_s_fach_stufe] (bf_fachid, bf_klassenstufe) VALUES (@subjectId, @grade)";
foreach (DataRow row in assignmentTable.Rows)
{
cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@subjectId", row[0].ToString());
cmd.Parameters.AddWithValue("@grade", grade);
cmd.ExecuteNonQuery();
}
con.Close();
}
private void Login()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
MetroMessageBox.Show(this, "Die Verbindung zum SQL-Server konnte nicht hergestellt werden. " +
"Bitte überprüfen Sie die Verbindungseinstellungen.", "Keine Verbindung.", MessageBoxButtons.OK, MessageBoxIcon.Error);
FormSettings formSettings = new FormSettings();
formSettings.ShowDialog(this);
formSettings.Dispose();
return;
}
User user = new User(tb_User.Text);
string userPassword = tb_Password.Text;
if (user.CanLogin(userPassword))
{
AuthInfo.CurrentUser = user;
this.Hide();
FormMain formMain = new FormMain();
formMain.Closed += (s, args) => this.Close();
formMain.Show();
}
else
{
MetroMessageBox.Show(this, "Die Anmeldedaten sind falsch. Bitte versuchen Sie es erneut!", "Fehler!",
MessageBoxButtons.OK, MessageBoxIcon.Error);
tb_Password.Clear();
}
}
/// <summary>
/// adds the subjects to the specific costumer
/// </summary>
private void AddSubjectsToCostumer()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "INSERT INTO [dbo].[t_s_fach_kunde] (fs_kundenid, fs_fachid, fs_lk) VALUES (@costumerId, @subjectId, @isAdvanced)";
for (int i = 0; i < CostumerSubjects.Count; i++)
{
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@costumerId", CostumerId);
cmd.Parameters.AddWithValue("@subjectId", subjectHelper.AddOrGetSubject(CostumerSubjects[i].SubjectNameShort).SubjectId);
if (CostumerAdvancedSubjects.Contains(CostumerSubjects[i]))
{
cmd.Parameters.AddWithValue("@isAdvanced", true);
}
else
{
cmd.Parameters.AddWithValue("@isAdvanced", false);
}
cmd.ExecuteNonQuery();
}
con.Close();
}
/// <summary>
/// Überträgt eine Datatable in eine SQL-Datenbank.
/// </summary>
private void transferDataTable()
{
try
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
using (var bulkCopy = new SqlBulkCopy(con.Con))
{
foreach (DataColumn col in dt.Columns)
{
bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = target;
bulkCopy.WriteToServer(dt);
}
}
catch
{
MessageBox.Show("Diese Datei enthält eine inkorrekte Daten-Struktur! Die Datei kann nicht eingelesen werden.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// loads all the data for the copy object
/// </summary>
private void LoadCopy()
{
CustomSqlConnection connection = new CustomSqlConnection();
if (connection.ConnectError())
{
return;
}
string command = "SELECT bu_id, bu_isbn, buch_titel, " +
"bu_zustandsid, isnull(bu_aufnahmedatum, '01.01.1990') as 'verified_aufnahmedatum', " +
"bu_activated, bu_printed from t_s_buchid left join t_s_zustand on bu_zustandsid = zu_id " +
"left join t_s_buecher on bu_isbn = buch_isbn where bu_id = @0";
SqlDataReader dr = connection.ExcecuteCommand(command, CopyId);
while (dr.Read())
{
CopyId = int.Parse(dr["bu_id"].ToString());
CopyIsbn = dr["bu_isbn"].ToString();
CopyTitle = dr["buch_titel"].ToString();
Condition = new Condition(int.Parse(dr["bu_zustandsid"].ToString()));
DateRegistration = (DateTime)dr["verified_aufnahmedatum"];
CopyActivated = Convert.ToBoolean(dr["bu_activated"].ToString());
CopyPrinted = dr["bu_printed"].ToString().Equals(0) ? false : true;
}
dr.Close();
connection.Close();
}
/// <summary>
/// saves the assignment data
/// </summary>
/// <param name="assignmentTable"></param>
/// <param name="subjectId"></param>
/// <param name="isAdvancedSubject"></param>
public void SaveAssignment(DataTable assignmentTable, int subjectId, bool isAdvancedSubject)
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
int advancedSubjectInt = isAdvancedSubject ? 1 : 0;
string command = "DELETE FROM [dbo].[t_s_buch_fach] WHERE bf_fachid = @subjectId AND bf_lk = @advancedSubjectInt";
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@subjectId", subjectId);
cmd.Parameters.AddWithValue("@advancedSubjectInt", advancedSubjectInt);
cmd.ExecuteNonQuery();
command = "INSERT INTO [dbo].[t_s_buch_fach] (bf_isbn, bf_fachid, bf_lk) VALUES (@isbn, @subjectId, @advancedSubjectInt)";
foreach (DataRow row in assignmentTable.Rows)
{
cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@isbn", row[0].ToString());
cmd.Parameters.AddWithValue("@subjectId", subjectId);
cmd.Parameters.AddWithValue("@advancedSubjectInt", advancedSubjectInt);
cmd.ExecuteNonQuery();
}
con.Close();
}
/// <summary>
/// Füllt ein DataSet-Objekt mit Daten der zu exportierenden Tabellen der Datenbank
/// </summary>
private void FillDataSet(string[] source)
{
try
{
ds.Clear();
ds.Tables.Clear();
int i = 0;
foreach (string s in source)
{
ds.Tables.Add(i.ToString());
if (con.ConnectError())
{
return;
}
string RawCommand = "SELECT * FROM @source";
RawCommand = RawCommand.Replace("@source", source[i].ToString());
adapter = new SqlDataAdapter(RawCommand, con.Con);
Console.WriteLine(RawCommand);
adapter.Fill(ds.Tables[i]);
i++;
}
con.Close();
}
catch (Exception exceptionObject)
{
log.CreateReport(exceptionObject);
}
}
public List <Copy> GetUnprintedCopies(string isbn)
{
List <Copy> copies = new List <Copy>();
CustomSqlConnection connection = new CustomSqlConnection();
if (connection.ConnectError())
{
return(copies);
}
string command = "SELECT bu_id, bu_isbn, " +
"bu_zustandsid, isnull(bu_aufnahmedatum, '01.01.1990') as 'verified_aufnahmedatum', " +
"bu_activated, bu_printed from t_s_buchid left join t_s_zustand on bu_zustandsid = zu_id where bu_isbn = @0 AND bu_printed='0'";
SqlDataReader dr = connection.ExcecuteCommand(command, isbn);
while (dr.Read())
{
Copy copy = new Copy();
copy.CopyId = int.Parse(dr["bu_id"].ToString());
copy.CopyIsbn = dr["bu_isbn"].ToString();
//copy.Condition = new Condition(int.Parse(dr["bu_zustandsid"].ToString()));
//copy.DateRegistration = (DateTime)dr["verified_aufnahmedatum"];
copy.CopyActivated = dr["bu_activated"].ToString().Equals(0) ? false : true;
copy.CopyPrinted = dr["bu_printed"].ToString().Equals(0) ? false : true;
copies.Add(copy);
}
dr.Close();
connection.Close();
return(copies);
}
/// <summary>
/// Laden der KundenID, und des Ausleih- und Rückgabedatums eines Buches
/// </summary>
public void LoadInfo(int copyId, bool loadFullCostumerInfo = false)
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string RawCommand = "SELECT aus_leihdatum, aus_rückgabedatum, aus_kundenid, aus_buchid FROM t_bd_ausgeliehen WHERE aus_buchid = @0";
SqlDataReader dr = con.ExcecuteCommand(RawCommand, copyId);
while (dr.Read())
{
Costumer = new Costumer();
if (loadFullCostumerInfo)
{
Costumer = new Costumer(int.Parse(dr["aus_kundenid"].ToString()));
}
else
{
Costumer.CostumerId = int.Parse(dr["aus_kundenid"].ToString());
}
Copy = new Copy(int.Parse(dr["aus_buchid"].ToString()));
BorrowDate = (DateTime)dr["aus_leihdatum"];
ReturnDate = (DateTime)dr["aus_rückgabedatum"];
}
dr.Close();
con.Close();
}
/// <summary>
/// returning of a book copy
/// </summary>
/// <param name="copyId"></param>
/// <param name="costumerId"></param>
/// <param name="shouldConditionName"></param>
/// <param name="isConditionId"></param>
/// <param name="isConditionName"></param>
/// <param name="borrowDateStart"></param>
/// <param name="borrowDateEnd"></param>
public void Execute_Rueckgabe(int copyId, int costumerId, string shouldConditionName, int isConditionId, string isConditionName, string borrowDateStart, string borrowDateEnd)
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "DELETE FROM t_bd_ausgeliehen WHERE aus_buchid = @0";
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@0", copyId);
cmd.ExecuteNonQuery();
command = "UPDATE t_s_buchid set bu_zustandsid = @zustandsid WHERE bu_id = @id";
cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@zustandsid", isConditionId);
cmd.Parameters.AddWithValue("@id", copyId);
cmd.ExecuteNonQuery();
command = "INSERT INTO t_s_verlauf (id_buch, k_id, zu_vor, zu_nach, aus_geliehen, aus_ruckgabe) VALUES (@buchid, @kid, @zvor, @znach, @ausgeliehen, @ruckgabe)";
cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@buchid", copyId);
cmd.Parameters.AddWithValue("@kid", costumerId);
cmd.Parameters.AddWithValue("@zvor", shouldConditionName);
cmd.Parameters.AddWithValue("@znach", isConditionName);
cmd.Parameters.AddWithValue("@ausgeliehen", borrowDateStart);
cmd.Parameters.AddWithValue("@ruckgabe", borrowDateEnd);
cmd.ExecuteNonQuery();
con.Close();
}
/// <summary>
/// Ermittelt das Tabellen-Schema der Zieltabelle in der SQL-Datenbank.
/// </summary>
private DataTable GetSchemaOfSQLTable()
{
schemaOfSQLTable.Clear();
schemaOfSQLTable.Columns.Clear();
if (con.ConnectError())
{
return(null);
}
//string RawCommand = "SELECT SUBSTRING(COLUMN_NAME, CHARINDEX('_', COLUMN_NAME)+1, LEN(COLUMN_NAME)) as 'Feld', DATA_TYPE as 'Datentyp' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @0";
string RawCommand = "SELECT COLUMN_NAME as 'Feld', DATA_TYPE as 'Datentyp' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @0";
adapter = new SqlDataAdapter(RawCommand, con.Con);
adapter.SelectCommand.Parameters.AddWithValue("@0", Zieltabellen);
adapter.Fill(schemaOfSQLTable);
con.Close();
return(schemaOfSQLTable);
}
/// <summary>
/// deletes the authors of an book
/// </summary>
private void DeleteBookAuthors()
{
CustomSqlConnection con = new CustomSqlConnection();
con.ConnectError();
string command = "DELETE FROM [dbo].[t_s_buch_autor] WHERE ba_isbn = @bookIsbn";
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@bookIsbn", BookIsbn);
cmd.ExecuteNonQuery();
con.Close();
}
/// <summary>
/// adds the book object to database
/// </summary>
public void Add()
{
if (BookAlreadyExistsDisabled())
{
this.Activate();
this.Update();
return;
}
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "INSERT INTO [dbo].[t_s_buecher] (buch_isbn, buch_titel, buch_genre_id, " +
"buch_verlag_id, buch_erscheinungsdatum, buch_sprache_id, buch_auflage, buch_neupreis, " +
"buch_image, buch_activated, buch_anzahl) VALUES " +
"(@bookIsbn, @bookTitle, @bookGenreId, @bookPublisherId, @bookDatePublication, @bookLanguageId, @bookEdition, " +
"@bookPrice, @bookImage, 1, 0)";
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@bookIsbn", BookIsbn);
cmd.Parameters.AddWithValue("@bookTitle", BookTitle);
cmd.Parameters.AddWithValue("@bookGenreId", BookGenre.GenreId);
cmd.Parameters.AddWithValue("@bookPublisherId", BookPublisher.PublisherId);
cmd.Parameters.AddWithValue("@bookLanguageId", BookLanguage.LanguageId);
cmd.Parameters.AddWithValue("@bookDatePublication", BookDatePublication);
if (BookEdition == null)
{
cmd.Parameters.AddWithValue("@bookEdition", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@bookEdition", BookEdition);
}
cmd.Parameters.AddWithValue("@bookPrice", BookPrice);
cmd.Parameters.Add("@bookImage", SqlDbType.VarBinary, -1);
if (BookImage == null)
{
cmd.Parameters["@bookImage"].Value = DBNull.Value;
}
else
{
using (var ms = new MemoryStream())
{
BookImage.Save(ms, ImageFormat.Png);
cmd.Parameters["@bookImage"].Value = ms.ToArray();
}
}
cmd.ExecuteNonQuery();
AddBookAuthors();
con.Close();
}
/// <summary>
/// removes the user from database
/// </summary>
public void DeleteUser()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string RawCommand = "DELETE FROM [dbo].[t_s_benutzer] WHERE b_name = @userName";
SqlCommand cmd = new SqlCommand(RawCommand, con.Con);
cmd.Parameters.AddWithValue("@userName", UserName);
cmd.ExecuteNonQuery();
con.Close();
}
/// <summary>
/// deletes the subjects of the costumer
/// </summary>
private void DeleteSubjectsOfCostumer()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "DELETE FROM [dbo].[t_s_fach_kunde] WHERE fs_kundenid = @costumerId";
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@costumerId", CostumerId);
cmd.ExecuteNonQuery();
con.Close();
}
/// <summary>
/// activates the book
/// </summary>
private void Activate()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "UPDATE [dbo].[t_s_buecher] set buch_activated = 1 WHERE buch_isbn = @bookIsbn";
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@bookIsbn", BookIsbn);
cmd.ExecuteNonQuery();
con.Close();
}
/// <summary>
/// deletes the copy with the given id from the database
/// </summary>
public void Delete()
{
CustomSqlConnection connection = new CustomSqlConnection();
if (connection.ConnectError())
{
return;
}
string command = "DELETE FROM [dbo].[t_s_buchid] WHERE bu_id = @copyId";
SqlCommand cmd = new SqlCommand(command, connection.Con);
cmd.Parameters.AddWithValue("@copyId", CopyId);
cmd.ExecuteNonQuery();
connection.Close();
}
/// <summary>
/// deletes all copy entries from database where copyIsbn = bookIsbn
/// </summary>
/// <param name="bookIsbn">the isbn of a book</param>
public void DeleteByBookIsbn(string bookIsbn)
{
CustomSqlConnection connection = new CustomSqlConnection();
if (connection.ConnectError())
{
return;
}
string command = "DELETE FROM [dbo].[t_s_buchid] WHERE bu_isbn = @bookIsbn";
SqlCommand cmd = new SqlCommand(command, connection.Con);
cmd.Parameters.AddWithValue("@bookIsbn", bookIsbn);
cmd.ExecuteNonQuery();
connection.Close();
}
/// <summary>
/// adds all the authors from the author list to the book
/// </summary>
private void AddBookAuthors()
{
CustomSqlConnection con = new CustomSqlConnection();
con.ConnectError();
string command = "INSERT INTO [dbo].[t_s_buch_autor] (ba_isbn, ba_autorid) VALUES (@bookIsbn, @bookAuthor)";
foreach (Author author in BookAuthors)
{
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@bookIsbn", BookIsbn);
cmd.Parameters.AddWithValue("@bookAuthor", author.AuthorId);
cmd.ExecuteNonQuery();
}
}
private bool IsConnected()
{
CustomSqlConnection connection = new CustomSqlConnection();
if (connection.ConnectError())
{
MetroMessageBox.Show(this, "Sie müssen eine Verbindung zu einem SQL-Server herstellen, bevor Sie auf weitere Funktionen der Software " +
"zugreifen können!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// activates the current costumer
/// </summary>
public void ActivateCostumer()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "UPDATE [dbo].[t_s_kunden] set kunde_activated = 1 WHERE kunde_ID = @costumerId";
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@costumerId", CostumerId);
cmd.ExecuteNonQuery();
con.Close();
}
/// <summary>
/// updates the book
/// </summary>
public void Update()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
DeleteBookAuthors();
string command = "UPDATE [dbo].[t_s_buecher] set buch_titel = @bookTitle , buch_genre_id = @bookGenreId, " +
"buch_sprache_id = @bookLanguageId, buch_verlag_id = @bookPublisherId, buch_auflage = @bookEdition, " +
"buch_erscheinungsdatum = @bookDatePublication, buch_neupreis = @bookPrice, " +
"buch_image = @bookImage WHERE buch_isbn = @bookIsbn";
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@bookTitle", BookTitle);
cmd.Parameters.AddWithValue("@bookGenreId", BookGenre.GenreId);
cmd.Parameters.AddWithValue("@bookLanguageId", BookLanguage.LanguageId);
cmd.Parameters.AddWithValue("@bookPublisherId", BookPublisher.PublisherId);
if (BookEdition == null || BookEdition.Equals(""))
{
cmd.Parameters.AddWithValue("@bookEdition", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@bookEdition", BookEdition);
}
cmd.Parameters.AddWithValue("@bookDatePublication", BookDatePublication);
cmd.Parameters.AddWithValue("@bookPrice", BookPrice);
cmd.Parameters.Add("@bookImage", SqlDbType.VarBinary, -1);
if (BookImage == null)
{
cmd.Parameters["@bookImage"].Value = DBNull.Value;
}
else
{
using (var ms = new MemoryStream())
{
BookImage.Save(ms, ImageFormat.Png);
cmd.Parameters["@bookImage"].Value = ms.ToArray();
}
}
cmd.Parameters.AddWithValue("@bookIsbn", BookIsbn);
cmd.ExecuteNonQuery();
AddBookAuthors();
con.Close();
}
private DataTable FillObject()
{
DataTable table = new DataTable();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(table);
}
string command = "SELECT bs_isbn as 'ISBN', bs_klassenstufe, buch_titel as 'Titel' FROM [dbo].[t_s_buch_stufe] left join [dbo].[t_s_buecher] on buch_isbn = bs_isbn order by buch_titel";
SqlDataAdapter adapter = new SqlDataAdapter(command, con.Con);
adapter.Fill(table);
con.Close();
return(table);
}
/// <summary>
/// deactivates all copy entries where copyIsbn = bookIsbn
/// </summary>
/// <param name="bookIsbn">the isbn of a book</param>
public void DeactivateByBookIsbn(string bookIsbn)
{
CustomSqlConnection connection = new CustomSqlConnection();
if (connection.ConnectError())
{
return;
}
string RawCommand = "UPDATE t_s_buchid set bu_activated = @copyActivated WHERE bu_isbn = @copyIsbn";
SqlCommand cmd = new SqlCommand(RawCommand, connection.Con);
cmd.Parameters.AddWithValue("@copyActivated", 0);
cmd.Parameters.AddWithValue("@copyIsbn", bookIsbn);
cmd.ExecuteNonQuery();
connection.Close();
}
/// <summary>
/// sets the 'printed' attribute of the copy to true
/// </summary>
public void Print()
{
CustomSqlConnection connection = new CustomSqlConnection();
string command = "UPDATE t_s_buchid set bu_printed = @copyPrinted WHERE bu_id = @copyId";
if (connection.ConnectError())
{
return;
}
SqlCommand cmd = new SqlCommand(command, connection.Con);
cmd.Parameters.AddWithValue("@copyPrinted", 1);
cmd.Parameters.AddWithValue("@copyId", CopyId);
cmd.ExecuteNonQuery();
connection.Close();
CopyPrinted = true;
}
/// <summary>
/// adds a subject to database
/// </summary>
public void AddSubjectIfNotExists()
{
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";
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@0", SubjectNameShort);
cmd.Parameters.AddWithValue("@1", SubjectNameShort);
cmd.Parameters.AddWithValue("@2", SubjectNameLong);
cmd.ExecuteNonQuery();
con.Close();
}
/// <summary>
/// Ermittelt das Tabellen-Schema der Zieltabelle in der SQL-Datenbank.
/// </summary>
private void getSchemaOfSQLTable(string table)
{
schema.Clear();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string RawCommand = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @0";
// Verbindung öffnen
adapter = new SqlDataAdapter(RawCommand, con.Con);
adapter.SelectCommand.Parameters.AddWithValue("@0", table);
adapter.Fill(schema);
con.Close();
}
/// <summary>
/// returns a datatable with subject grade information
/// </summary>
/// <returns></returns>
private DataTable FillObject()
{
DataTable table = new DataTable();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(table);
}
string command = "SELECT bf_fachid, bf_klassenstufe, f_kurzform as 'Kürzel', f_langform as 'Langbezeichnung' " +
"FROM [dbo].[t_s_fach_stufe] left join [dbo].[t_s_faecher] on f_id = bf_fachid order by f_kurzform";
SqlDataAdapter adapter = new SqlDataAdapter(command, con.Con);
adapter.Fill(table);
con.Close();
return(table);
}
/// <summary>
/// loads the book information by a given id of one of its copies
/// </summary>
/// <param name="copyId"></param>
public void LoadBookByCopyId(int copyId)
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "SELECT bu_isbn FROM [dbo].[t_s_buchid] WHERE bu_id = @0";
SqlDataReader dr = con.ExcecuteCommand(command, copyId);
while (dr.Read())
{
BookIsbn = dr["bu_isbn"].ToString();
}
LoadBook();
}
/// <summary>
/// deactivates the copy object
/// </summary>
public void Deactivate()
{
CustomSqlConnection connection = new CustomSqlConnection();
if (connection.ConnectError())
{
return;
}
string command = "UPDATE t_s_buchid set bu_activated = @activated WHERE bu_id = @copyId";
SqlCommand cmd = new SqlCommand(command, connection.Con);
cmd.Parameters.AddWithValue("@activated", 0);
cmd.Parameters.AddWithValue("@copyId", CopyId);
cmd.ExecuteNonQuery();
connection.Close();
CopyActivated = false;
}