/// <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>
/// 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);
}
}
/// <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>
/// 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>
/// 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();
}
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>
/// 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>
/// updates the user data
/// </summary>
public void UpdateUser()
{
CustomSqlConnection con = new CustomSqlConnection();
string RawCommand = "UPDATE t_s_benutzer set b_password = @userPassword, b_rechte = @permissionId WHERE b_name = @userName";
if (con.ConnectError())
{
return;
}
SqlCommand cmd = new SqlCommand(RawCommand, con.Con);
cmd.Parameters.AddWithValue("@userPassword", Encoding.UTF8.GetBytes(UserPassword));
cmd.Parameters.AddWithValue("@permissionId", PermissionId);
cmd.Parameters.AddWithValue("@userName", UserName);
cmd.ExecuteNonQuery();
con.Close();
}
/// <summary>
/// adds a new user to database
/// </summary>
public void AddUser()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "INSERT INTO [dbo].[t_s_benutzer] (b_name, b_password, b_rechte) VALUES (@0, @1, @2)";
SqlCommand cmd = new SqlCommand(command, con.Con);
cmd.Parameters.AddWithValue("@0", UserName);
cmd.Parameters.AddWithValue("@1", Encoding.UTF8.GetBytes(UserPassword));
cmd.Parameters.AddWithValue("@2", PermissionId);
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);
}