// method returns all rooms
public Rooms GetRooms()
{
Rooms rooms = new Rooms();
SqlConnection sqlConnection = SqlFactory.Instance.GetSqlConnection();
SqlCommands sqlCommands = SqlFactory.Instance.GetSqlCommands();
// gets rows of data from database
DataRowCollection data = sqlCommands.GetAllRooms(sqlConnection);
foreach (DataRow row in data)
{
// rewrite to handle total price, and utilities
// 0 = number
// 1 = price pr day
// 2 = status
// creates room
Room room = new Room((int)row[0], (int)row[1], (int)row[2]);
// gets utilities for the room
DataRowCollection foundUtilities = sqlCommands.GetUtilities(sqlConnection, (int)row[0]);
List <string> utils = new List <string>();
foreach (DataRow util in foundUtilities)
{
utils.Add((string)util[0]);
room.AddPrice((int)util[1]);
}
room.SetUtilities(utils);
rooms.rooms.Add(room);
}
return(rooms);
}
public FacultyService(QueryExecutor queryExecutor, ModelMapper modelMapper, SqlCommands sqlCommands, IMapper autoMapper)
{
this._queryExecutor = queryExecutor;
this._modelMapper = modelMapper;
this._sqlCommands = sqlCommands;
this._autoMapper = autoMapper;
}
public LabelPollingService()
{
InitializeComponent();
Config.ReadSettingsFromRegistry();
Config.Log("Label Polling Started", false);
try
{
SqlCmd = new SqlCommands();
}
catch (System.Exception ex)
{
Config.Log(ex.Message);
}
// ja - read in the Last Key used for each "Trigger"
foreach (ITriggerTables obj in SqlCmd.Trig)
{
// ja - enumerators are read only so copy the object
ITriggerTables writeObj = obj;
Config.ReadLastKey(ref writeObj);
}
}
public AuthService(JwtGenerator jwtGenerator, QueryExecutor queryExecutor, ModelMapper modelMapper, SqlCommands sqlCommands)
{
this._jwtGenerator = jwtGenerator;
this._queryExecutor = queryExecutor;
this._modelMapper = modelMapper;
this._sqlCommands = sqlCommands;
}
public DataTable Table(string tableName, string sql, bool AcceptChangesDuringFill)
{
string strAccessSelect = sql;
MySqlConnection c = new MySqlConnection(ConnectionString);
var cmd = new MySqlCommand(strAccessSelect, c);
var myDataAdapter = new MySqlDataAdapter(cmd);
myDataAdapter.AcceptChangesDuringFill = AcceptChangesDuringFill;
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
DataSet myDataSet = new DataSet();
try
{
c.Open();
myDataAdapter.Fill(myDataSet, tableName);
}
catch (Exception e)
{
string msg = "Error reading from database " + sql + " Exception " + e.ToString();
Console.WriteLine(msg);
throw e;
}
finally
{
c.Close();
}
DataTable tbl = myDataSet.Tables[tableName];
myDataSet.Tables.Remove(tbl);
return(tbl);
}
// method books rooms in the database
public void BookRooms(string data, string rooms)
{
SqlConnection sqlConnection = SqlFactory.Instance.GetSqlConnection();
SqlCommands sqlCommands = SqlFactory.Instance.GetSqlCommands();
// data:
// 0 : name - string
// 1 : address - string
// 2 : zipcode - int
// 3 : city - string
// 4 : phone - int
// 5 : email - string
// 6 : from date - datetime
// 7 : to date - datetime
// last string in data is empty
string[] x = data.Split(',');
sqlCommands.InsertBooking(sqlConnection, x[0], x[1], int.Parse(x[2]), x[3], int.Parse(x[4]), x[5], DateTime.Parse(x[6]), DateTime.Parse(x[7]));
// rooms : number - string
// last string in rooms is empty
DataRowCollection dataRows = sqlCommands.GetReservationId(sqlConnection, x[0], int.Parse(x[4]));
DataRow dataRow = dataRows[0];
int y = (int)dataRow[0];
string[] roomnumbers = rooms.Split(',');
for (int i = 0; i < roomnumbers.Length - 1; i++)
{
sqlCommands.InsertReservatedRooms(sqlConnection, y, int.Parse(roomnumbers[i]));
}
SendEmail();
}
// method for employee login
public bool ConfirmUser(string initials, string password)
{
SqlConnection sqlConnection = SqlFactory.Instance.GetSqlConnection();
SqlCommands sqlCommands = SqlFactory.Instance.GetSqlCommands();
Rinjdael rinjdael = SecurityFactory.Instance.GetRinjdael();
IHash sha = SecurityFactory.Instance.GetHashing();
try
{
// converts salt to string
DataRow row = sqlCommands.GetSalt(sqlConnection, initials)[0];
string salt = row[0].ToString();
// gets initials if password is correct
row = sqlCommands.GetInitials(sqlConnection, Convert.ToBase64String(sha.GetHash(Convert.FromBase64String(rinjdael.Encrypt(password, "Landlyst", Convert.FromBase64String(salt))))))[0];
string ini = row[0].ToString();
// checks if both sets of initials matches
if (initials == ini)
{
row = sqlCommands.GetPosition(sqlConnection, ini)[0];
int pos = int.Parse(row[0].ToString());
_userHandler = new UserHandler(new User(ini, pos));
return(true);
}
}
catch (Exception)
{
return(false);
}
return(false);
}
/// <summary>
/// add field list to combobox from sql request
/// </summary>
/// <param name="cmb">Combobox to populate</param>
/// <param name="table">Table name</param>
/// <param name="connectionString">connexction string</param>
/// <param name="DbKeyName">if set field with DbKeyName is not shown</param>
/// <param name="allowNull">Add selectable null item</param>
/// <param name="hideTabloidField">if true Tabloid fields are hidden</param>
public static void displayField(ComboBox cmb, string table, string connectionString, string schema = null, string DbKeyName = "", bool allowNull = false, bool hideTabloidField = true)
{
cmb.Items.Clear();
string lastError;
if (string.IsNullOrEmpty(schema))
{
schema = Program.AppSet.Schema;
}
var dc = DataTools.Data(SqlCommands.SqlGetColums(table, schema), connectionString, out lastError);//0
if (allowNull)
{
cmb.Items.Add("");
}
foreach (DataRow dcr in dc.Rows)
{
if (hideTabloidField || TabloidFields.IstabloidField(dcr[0].ToString()) == -1)
{
cmb.Items.Add(dcr[0].ToString());
if (dcr[0].ToString() == DbKeyName)
{
cmb.SelectedIndex = cmb.Items.Count - 1;
}
}
}
if (cmb.Items.Count > 0)
{
cmb.SelectedIndex = 0;
}
}
private List <List <string> > GetDistinctRepo(string userName)
{
var query = string.Format("Select DISTINCT Repository,Branch,Tool FROM UserCodeAnalysisData WHERE UserName='******'", userName);
List <List <string> > repos = new List <List <string> >();
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommands.OpenConnection(con);
SqlDataReader reader = SqlCommands.ExecuteRead(con, query);
while (reader.Read())
{
List <string> repo = new List <string>();
GatingResult result = new GatingResult();
repo.Add(reader["Repository"].ToString());
repo.Add(reader["Branch"].ToString());
repo.Add(reader["Tool"].ToString());
repos.Add(repo);
}
reader.Close();
}
return(repos);
}
public bool Discharge(string patientId)
{
var admissionQuery = string.Format("Select * FROM PatientAdmission WHERE PatientId='{0}'", patientId);
var admissions = patientAdmissionReader.FetchPatientAdmissions(conString, admissionQuery);
if (admissions.Any())
{
m_patientRepo.AddAdmissionHistory(patientId, new AdmissionHistory
{
AdmissionDate = admissions.First().AdmissionTime.ToString(),
Diagnosis = admissions.First().Diagnosis,
Illness = admissions.First().Illness
});
var bed = m_bedRepo.ReadPatientsBed(patientId);
bed.Occupancy = "";
m_bedRepo.Update(bed);
string query = $"DELETE FROM PatientAdmission WHERE PatientId='{patientId}'";
if (SqlCommands.ExecuteCommand(query, conString))
{
return(DeletePatientDevices(patientId));
}
}
return(false);
}
public override void FillTable(DataTable dataTable, string sql)
{
if (dataTable.TableName == "")
{
dataTable.TableName = "table1";
}
base.SqlCommands.Add("Fill(" + dataTable.TableName + ")");
string strAccessSelect = sql;
var myAccessConn = new NpgsqlConnection(ConnectionString);
var myAccessCommand = new NpgsqlCommand(strAccessSelect, myAccessConn);
var myDataAdapter = new NpgsqlDataAdapter(myAccessCommand);
//Console.WriteLine(sql);
this.lastSqlCeCommand = sql;
SqlCommands.Add(sql);
try
{
myAccessConn.Open();
myDataAdapter.Fill(dataTable);
}
catch (Exception e)
{
string msg = "Error reading from database " + sql + " Exception " + e.ToString();
Console.WriteLine(msg);
throw e;
}
finally
{
myAccessConn.Close(); //
}
}
public DataTable Fill(string tableName, string sql, DataSet myDataSet)
{
string strAccessSelect = sql;
SqlConnection myAccessConn = new SqlConnection(ConnectionString);
SqlCommand myAccessCommand = new SqlCommand(strAccessSelect, myAccessConn);
SqlDataAdapter myDataAdapter = new SqlDataAdapter(myAccessCommand);
//Console.WriteLine(sql);
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
try
{
myAccessConn.Open();
myDataAdapter.Fill(myDataSet, tableName);
}
catch (Exception e)
{
string msg = "Error reading from database " + sql + " Exception " + e.ToString();
Console.WriteLine(msg);
throw e;
}
finally
{
myAccessConn.Close();
}
DataTable tbl = myDataSet.Tables[tableName];
return(tbl);
}
public override void FillTable(DataTable dataTable, string sql)
{
base.SqlCommands.Add("Fill(" + dataTable.TableName + ")");
var myAccessConn = new SqlConnection(ConnectionString);
var myAccessCommand = new SqlCommand(sql, myAccessConn);
var myDataAdapter = new SqlDataAdapter(myAccessCommand);
//Console.WriteLine(sql);
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
try
{
myAccessConn.Open();
myDataAdapter.Fill(dataTable);
}
catch (Exception e)
{
string msg = "Error reading from database " + sql + " Exception " + e.ToString();
Console.WriteLine(msg);
throw e;
}
finally
{
myAccessConn.Close();
myAccessConn.Dispose();
}
// DataTable tbl = myDataSet.Tables[tableName];
// return tbl;
}
private bool RunSqlCommandNonTransaction(string sql)
{
SqlConnection myConnection = new SqlConnection(ConnectionString);
myConnection.Open();
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
bool rval = false;
try
{
myCommand.CommandText = sql;
if (myCommand.ExecuteNonQuery() > 0)
{
rval = true;
}
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("Error running " + sql);
this.lastMessage = e.ToString();
rval = false;
}
finally
{
myConnection.Close();
}
return(rval);
}
private void populateFieldList()
{
string lastError;
lstChamp.Items.Clear();
if (cmbTable.SelectedValue == null)
{
return;
}
var dc = DataTools.Data(SqlCommands.SqlGetColums(
cmbTable.SelectedValue.ToString(),
cmdSchema.SelectedValue.ToString()), ConnectionString, out lastError);//0
foreach (DataRow dcr in dc.Rows)
{
if (TabloidFields.IstabloidField(dcr[0].ToString()) == -1)
{
lstChamp.Items.Add(dcr[0].ToString());
}
}
if (lstChamp.Items.Count > 0)
{
lstChamp.SelectedIndex = 0;
}
}
public WizardCalendrier(TabloidConfigView view, string connectionString, Provider provider)
{
InitializeComponent();
_connectionString = connectionString;
_provider = provider;
_view = view;
string lastError;
var dt = DataTools.Data(SqlCommands.SqlGetColums(view.NomTable), connectionString, out lastError);//01
//is there 2 timestamp
cmbDeb.DataSource = new DataView(dt, dt.Columns[1].ColumnName + " like 'timestamp%'", null, DataViewRowState.CurrentRows);
cmbFin.DataSource = new DataView(dt, dt.Columns[1].ColumnName + " like 'timestamp%'", null, DataViewRowState.CurrentRows);
cmbDeb.DisplayMember = cmbFin.DisplayMember = dt.Columns[0].ColumnName;
chkUtilDeb.Enabled = chkUtilFin.Enabled = cmbDeb.Items.Count != 0;//no timestamp field couldn't use existing fields
//is there 1 or more character varying
cmbTitre.DataSource = dt;
cmbTitre.ValueMember = dt.Columns[0].ColumnName;
var nameField = cmbTitre.FindStringExact("nom_" + _view.NomTable);
if (nameField > -1)
{
cmbTitre.SelectedIndex = nameField;
chkExistTitre.Checked = true;
}
///create new field
txtTitre.Text = "eve_" + view.NomTable;
txtDebut.Text = "deb_" + view.NomTable;
txtFin.Text = "fin_" + view.NomTable;
}
public int ReadRelativeThreshold(string userName, string path, string branch, Tools tool)
{
if (!Exists(userName, path, branch, tool))
{
return(-1);
}
var query = string.Format("Select TOP 1 Result FROM UserCodeAnalysisData WHERE UserName='******' AND Repository='{1}'AND Tool='{2}' AND Branch='{3}' order by Result ASC", userName, path, (int)tool, branch);
int threshold = -1;
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommands.OpenConnection(con);
SqlDataReader reader = SqlCommands.ExecuteRead(con, query);
while (reader.Read())
{
threshold = Convert.ToInt32(reader["Result"]);
}
reader.Close();
}
return(threshold);
}
public void BuildScriptCollection(string testName)
{
using (var sqlConnection = SqlConnectionManager.GetOpenConnection())
{
_scriptNames = SqlCommands.GetScriptCollection(testName, sqlConnection);
}
}
private async Task SaveSubjectAsync()
{
var chosenSemester = Semesters[semesterPicker.SelectedIndex];
var code = subjectCodeTextBox.Text;
var facultyCode = facultyPicker.Text.Split('-')[0];
await SqlCommands.AddSubjectAsync(code, subjectNameTextBox.Text, facultyCode, chosenSemester.SemesterId);
}
public RoleService(QueryExecutor queryExecutor, ModelMapper modelMapper, IMapper autoMapper, SqlCommands sqlCommands)
{
this._queryExecutor = queryExecutor;
this._modelMapper = modelMapper;
this._autoMapper = autoMapper;
this._sqlCommands = sqlCommands;
}
private async void RemoveButton_Click(object sender, EventArgs e)
{
try
{
await SqlCommands.RemoveSemesterAsync(Semesters[SemestersBox.SelectedIndex]);
SemestersBox.Items.RemoveAt(SemestersBox.SelectedIndex);
Semesters = await SqlCommands.GetAllSemestersAsync();
if (SemestersBox.Items.Count > 0)
{
RemoveButton.Enabled = true;
SemestersBox.SelectedIndex = 0;
}
else
{
RemoveButton.Enabled = false;
}
MessageBox.Show("Semester succesfully removed", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show("Error! First you need to remove assigments and" +
" subjects associated with this semester.", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private async void SemesterPicker_SelectedIndexChanged(object sender, EventArgs e)
{
SubjectPicker.Items.Clear();
Subjects = await SqlCommands.GetAllSubjectsInSemesterAsync(Semesters[SemesterPicker.SelectedIndex].SemesterId);
foreach (var subject in Subjects)
{
if (subject.SemesterId == Semesters[SemesterPicker.SelectedIndex].SemesterId)
{
SubjectPicker.Items.Add($"{subject.SubjectFaculty.Trim()}: {subject.SubjectName.Trim()}");
}
}
if (SubjectPicker.Items.Count > 0)
{
SubjectPicker.Enabled = true;
AssigmentGroupBox.Visible = true;
SubjectPicker.SelectedIndex = 0;
AssigmentNameTextBox.Text = "";
WarningLabel.Visible = false;
}
else
{
SubjectPicker.Enabled = false;
AssigmentGroupBox.Visible = false;
SaveButton.Enabled = false;
WarningLabel.Visible = true;
}
}
public void Update()
{
using (var sqlConnection = SqlConnectionManager.GetOpenConnection())
{
_testNames = SqlCommands.GetTestCollections(ScriptAttribute.TestNames, sqlConnection);
_testBuilds = SqlCommands.GetTestCollections(ScriptAttribute.BuildNames, sqlConnection);
}
}
public AdminService(IUserService userService, QueryExecutor queryExecutor, ModelMapper modelMapper, IMapper autoMapper, SqlCommands sqlCommands)
{
this._userService = userService;
this._queryExecutor = queryExecutor;
this._modelMapper = modelMapper;
this._autoMapper = autoMapper;
this._sqlCommands = sqlCommands;
}
public DepartmentService(QueryExecutor queryExecutor, ModelMapper modelMapper, SqlCommands sqlCommands, IMapper autoMapper, IFacultyService facultyService)
{
this._queryExecutor = queryExecutor;
this._modelMapper = modelMapper;
this._sqlCommands = sqlCommands;
this._autoMapper = autoMapper;
this._facultyService = facultyService;
}
private async void EditButton_Click(object sender, EventArgs e)
{
DateTime time = AssigmentEndDate.Value.Date + AssigmentEndTime.Value.TimeOfDay;
await SqlCommands.UpdateAssigmentAsync(Assigments[AssigmentBox.SelectedIndex].AssigmentId, AssigmentName.Text,
AssigmentState.Text, time);
Close();
}
public UserService(IRoleService roleService, QueryExecutor queryExecutor, ModelMapper modelMapper, IMapper autoMapper, SqlCommands sqlCommands, IHttpContextAccessor httpContextAccessor)
{
this._roleService = roleService;
this._queryExecutor = queryExecutor;
this._modelMapper = modelMapper;
this._autoMapper = autoMapper;
this._sqlCommands = sqlCommands;
this._httpContextAccessor = httpContextAccessor;
}
public CourseArchivesService(QueryExecutor queryExecutor, IMapper autoMapper, ModelMapper modelMapper, SqlCommands sqlCommands, HttpClientService httpClientService, IHttpContextAccessor httpContextAccessor)
{
this._autoMapper = autoMapper;
this._queryExecutor = queryExecutor;
this._sqlCommands = sqlCommands;
this._modelMapper = modelMapper;
this._httpClientService = httpClientService;
this._httpContextAccessor = httpContextAccessor;
}
private void populateTableList()
{
string lastError;
var dt = DataTools.Data(SqlCommands.SqlGetTable(cmdSchema.SelectedItem.ToString()), ConnectionString, out lastError);
cmbTable.ValueMember = dt.Columns[0].ColumnName;
cmbTable.DataSource = dt;
}
void cmbTable_SelectedIndexChanged(object sender, EventArgs e)
{
string lastError;
var dc = DataTools.Data(SqlCommands.SqlGetColums(cmbTable.Text), Program.AppSet.ConnectionString, out lastError);//c0
cmbChamp.DisplayMember = cmbChamp.ValueMember = "column_name";
cmbChamp.DataSource = dc;
}
