public Framework() { _InstalledVersion = string.Empty; _UpdatedOn = new DateTime(1900, 1, 1); using (new Impersonator()) { using (SqlConnection conn = DataSource.Conn()) { string sql = "SELECT TOP 1 * FROM dbo.TableVersions WHERE Name = 'FrameworkVersion'"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = CommandType.Text; try { conn.Open(); using (SqlDataReader sdr = cmd.ExecuteReader()) { if (sdr.Read()) { _InstalledVersion = sdr["Version"].ToString(); _UpdatedOn = DateTime.Parse(sdr["UpdatedOn"].ToString()); } } } catch (Exception ex) { Error.WriteError(ex); } } } } }
public static bool Exists(string ServerName) { bool found = false; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); const string sql = @"SELECT Count(ID) AS cnt FROM dbo.Servers WHERE Name = @ServerName"; SqlCommand cmd = new SqlCommand(sql.ToString(), conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@ServerName", ServerName); try { conn.Open(); SqlDataReader sdr = cmd.ExecuteReader(); if (sdr.Read()) { if (int.Parse(sdr["cnt"].ToString()) > 0) { found = true; } } } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(found); }
public static int Count() { int count = 0; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); const string sql = @"SELECT Count(ID) AS cnt FROM dbo.Servers"; SqlCommand cmd = new SqlCommand(sql.ToString(), conn); cmd.CommandType = CommandType.Text; try { conn.Open(); SqlDataReader sdr = cmd.ExecuteReader(); if (sdr.Read()) { count = int.Parse(sdr["cnt"].ToString()); } } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(count); }
public static int QuestionCount(int CourseID) { int count = 0; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); const string sql = @"SELECT Count(ID) AS cnt FROM dbo.TrngSlides WHERE CourseID = @CourseID AND SlideTypeID = 2"; SqlCommand cmd = new SqlCommand(sql.ToString(), conn); cmd.Parameters.AddWithValue("@CourseID", CourseID); cmd.CommandType = CommandType.Text; try { conn.Open(); SqlDataReader sdr = cmd.ExecuteReader(); if (sdr.Read()) { count = int.Parse(sdr["cnt"].ToString()); } } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(count); }
public static DataSet Items(int MenuID) { DataSet ds = new DataSet(); try { using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = "SELECT * FROM nav.MenuAdmins Where MenuID = @MenuId ORDER BY UserName"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@MenuID", MenuID); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } } catch (Exception ex) { Error.WriteError(ex); } return(ds); }
public static DataSet Items(string Keyword) { DataSet ds = new DataSet(); using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = @"SELECT ID, Name, Description, Protocol, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy, Name + ' - ' + Protocol + ' - ' + Description AS LongName FROM Ports ORDER BY Name"; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); } catch (SqlException sqlex) { Error.WriteError(sqlex); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(ds); }
public static DataSet Items(int ServerID) { DataSet ds = new DataSet(); using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = "SELECT * FROM dbo.ServerEnvironments WHERE ServerID = @ServerID"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@ServerID", ServerID); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); } catch (SqlException sqlex) { Error.WriteError(sqlex); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(ds); }
public static DataSet ActiveItems(bool IsAdmin) { DataSet ds = new DataSet(); using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = "SELECT * FROM dbo.Tabs WHERE AdminOnly = 0 and IsActive = 1 ORDER BY DisplayIndex"; if (IsAdmin) { sql = "SELECT * FROM dbo.Tabs WHERE IsActive = 1 ORDER BY DisplayIndex"; } SqlCommand cmd = new SqlCommand(sql, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); } catch (SqlException sqlex) { Error.WriteError(sqlex); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(ds); }
public static int UserCount(int OrgGroupID) { int count = 0; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); const string sql = @"SELECT COUNT(Users.ID) AS cnt FROM OrgGroups INNER JOIN OrgGroupMemberships ON OrgGroups.ID = OrgGroupMemberships.OrgGroupID INNER JOIN Users ON OrgGroupMemberships.OrgUnitID = Users.OrgUnitID WHERE (OrgGroups.ID = @OrgGroupID)"; SqlCommand cmd = new SqlCommand(sql.ToString(), conn); cmd.Parameters.Add(new SqlParameter("@OrgGroupID", OrgGroupID)); cmd.CommandType = CommandType.Text; try { conn.Open(); SqlDataReader sdr = cmd.ExecuteReader(); if (sdr.Read()) { count = int.Parse(sdr["cnt"].ToString()); } } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(count); }
/// <summary> /// Creates a populated Transaction object based on the supplied ID /// </summary> /// <param name="ID"></param> public Transaction(int ID) : this() { using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); string sql = "SELECT * FROM dbo.Transactions WHERE ID = @ID"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@ID", ID); SqlDataReader sdr; try { conn.Open(); sdr = cmd.ExecuteReader(); if (sdr.Read()) { _ID = int.Parse(sdr["ID"].ToString()); _Category = sdr["Category"].ToString(); _Type = sdr["Type"].ToString(); _Action = sdr["Action"].ToString(); _ResourceID = sdr["ResourceID"].ToString(); _CreatedBy = sdr["CreatedBy"].ToString(); _CreatedOn = DateTime.Parse(sdr["CreatedOn"].ToString()); _ErrorMessage = string.Empty; } } catch (Exception ex) { Error.WriteError(ex); _ErrorMessage = ex.Message; } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } }
/// <summary> /// Updates a Transaction record /// </summary> /// <returns></returns> public bool Update() { bool Successful = false; using (new Impersonator()) { using (SqlConnection conn = DataSource.Conn()) { try { DateTime timeStamp = DateTime.UtcNow; string sql = @"Update dbo.Transactions SET Category = @Category ,Type = @Type ,Action = @Action ,ResourceID = @ResourceID WHERE ID = @ID" ; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@Category", _Category); cmd.Parameters.AddWithValue("@Type", _Type); cmd.Parameters.AddWithValue("@Action", _Action); cmd.Parameters.AddWithValue("@ResourceID", _ResourceID); cmd.Parameters.AddWithValue("@ID", _ID); if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.ExecuteNonQuery(); Successful = true; } catch (Exception ex) { Error.WriteError(ex); _ErrorMessage = ex.Message; } } } return(Successful); }
public static DataSet Items() { DataSet ds = new DataSet(); using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = "SELECT * FROM nav.MenuNodeAdmins"; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); } catch (SqlException sqlex) { Error.WriteError(sqlex); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(ds); }
public static int GetOrgGroupId(int OrgUnitID) { int id = 0; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); const string sql = @"SELECT OrgGroupID FROM dbo.OrgGroupMemberships WHERE OrgUnitID = @OrgUnitID"; SqlCommand cmd = new SqlCommand(sql.ToString(), conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@OrgUnitID", OrgUnitID); try { conn.Open(); SqlDataReader sdr = cmd.ExecuteReader(); if (sdr.Read()) { id = int.Parse(sdr["OrgGroupID"].ToString()); } } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(id); }
public static int GetIdFromDn(string DistinguishedName) { int id = 0; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); const string sql = @"SELECT ID FROM dbo.OrgUnits WHERE DistinguishedName = @DistinguishedName"; SqlCommand cmd = new SqlCommand(sql.ToString(), conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@DistinguishedName", DistinguishedName); try { conn.Open(); SqlDataReader sdr = cmd.ExecuteReader(); if (sdr.Read()) { id = int.Parse(sdr["ID"].ToString()); } } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(id); }
public static string AppSetting(string Key) { string setting = string.Empty; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = @"SELECT value, salt FROM dbo.Settings WHERE [key] = @Key"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@Key", Key); if (conn.State != ConnectionState.Open) { conn.Open(); } SqlDataReader sdr; sdr = cmd.ExecuteReader(); if (sdr.Read()) { setting = Common.Decrypt(sdr["Value"].ToString(), Common.RestoreKey(Common.Decrypt(sdr["Salt"].ToString()))); } } catch (SqlException sqlex) { Error.WriteError(sqlex); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(setting); }
public static DataTable DataTable() { DataTable dt = new DataTable(); using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = "SELECT * FROM dbo.Settings "; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); foreach (DataRow dr in dt.Rows) { if (!bool.Parse(dr["IsPassword"].ToString())) { dr["Value"] = Common.Decrypt(dr["Value"].ToString(), Common.RestoreKey(Common.Decrypt(dr["Salt"].ToString()))); } } } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(dt); }
public static bool PurgeOrphaned() { bool success = false; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); const string sql = @"DELETE FROM dbo.Webs WHERE Purge = 1"; SqlCommand cmd = new SqlCommand(sql.ToString(), conn); cmd.CommandType = CommandType.Text; try { cmd = new SqlCommand(sql, conn); if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.ExecuteNonQuery(); success = true; } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(success); }
public static DataSet List(int UserID) { DataSet ds = new DataSet(); using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = @"SELECT TrngCourses.Name, TrngCourses.Description, TrngCourseAdmins.UserID, TrngCourseAdmins.CourseID FROM TrngCourseAdmins INNER JOIN TrngCourses ON TrngCourseAdmins.CourseID = TrngCourses.ID WHERE (TrngCourseAdmins.UserID = @UserID) ORDER BY TrngCourses.Name"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@UserID", UserID); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); } catch (SqlException sqlex) { Error.WriteError(sqlex); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(ds); }
public static DataSet Items(int ServerID) { DataSet ds = new DataSet(); using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = @"SELECT ServerPorts.ID, ServerPorts.ServerID, ServerPorts.PortID, Ports.Name, Ports.Protocol, Ports.Description, Ports.Name + ' - ' + Ports.Protocol + ' - ' + Description AS LongName FROM Ports INNER JOIN ServerPorts ON Ports.ID = ServerPorts.PortID WHERE ServerPorts.ServerID = @ServerID ORDER BY Ports.Name"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@ServerID", ServerID); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); } catch (SqlException sqlex) { Error.WriteError(sqlex); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(ds); }
public static bool ResetItems(Guid WebID) { bool Successful = false; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = "DELETE FROM dbo.TimeZones WHERE WebID = @WebID"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@WebID", WebID); if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.ExecuteNonQuery(); Successful = true; } catch (SqlException sqlex) { Error.WriteError(sqlex); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(Successful); }
public static bool InUse(int CourseID) { bool inuse = false; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); const string sql = @"SELECT Count(ID) AS cnt FROM dbo.TrngRecords WHERE CourseID = @CourseID"; SqlCommand cmd = new SqlCommand(sql.ToString(), conn); cmd.Parameters.AddWithValue("@CourseID", CourseID); cmd.CommandType = CommandType.Text; try { conn.Open(); SqlDataReader sdr = cmd.ExecuteReader(); if (sdr.Read()) { inuse = int.Parse(sdr["cnt"].ToString()) > 0 ? true : false; } } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(inuse); }
public static DataSet Items(Guid WebID) { DataSet ds = new DataSet(); using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = "SELECT * FROM dbo.TimeZones WHERE WebID = @WebID ORDER BY DisplayIndex"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@WebID", WebID); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); AddColumns(ds.Tables[0]); } catch (SqlException sqlex) { Error.WriteError(sqlex); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(ds); }
public static bool DeleteActions() { bool bDeletedError = false; using (new Impersonator()) { string sql = string.Empty; SqlConnection conn = DataSource.Conn(); try { sql = @"DELETE FROM dbo.Activity"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.Text; if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.ExecuteNonQuery(); bDeletedError = true; } catch (SqlException sqlex) { Error.WriteError(sqlex); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(bDeletedError); }
public bool Update() { bool Successful = false; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { DateTime TimeStamp = DateTime.UtcNow; string sql = @"UPDATE dbo.UserTypes SET Name = @Name WHERE ID = @ID" ; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@ID", _ID); cmd.Parameters.AddWithValue("@Name", _Name); if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.ExecuteNonQuery(); Successful = true; } catch (SqlException sqlex) { Error.WriteError(sqlex); _ErrorMessage = sqlex.Message; } catch (Exception ex) { Error.WriteError(ex); _ErrorMessage = ex.Message; } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(Successful); }
public static bool UpdateDisplayIndex(int SlideID, string Direction, int CourseID) { bool success = false; DataSet ds = new DataSet(); using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = @"usp_UpdateSlideNumber"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@ItemID", SlideID)); cmd.Parameters.Add(new SqlParameter("@Direction", Direction)); cmd.Parameters.Add(new SqlParameter("@CourseID", CourseID)); if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.ExecuteNonQuery(); success = true; } catch (SqlException sqlex) { success = false; Error.WriteError(sqlex); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(success); }
public UserType(int ID) : this() { using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); string sql = "SELECT * FROM dbo.UserTypes WHERE ID = @ID"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@ID", ID); SqlDataReader sdr; try { conn.Open(); sdr = cmd.ExecuteReader(); if (sdr.Read()) { _ID = int.Parse(sdr["ID"].ToString()); _Name = sdr["Name"].ToString(); _ErrorMessage = string.Empty; } } catch (SqlException sqlex) { Error.WriteError(sqlex); _ErrorMessage = sqlex.Message; } catch (Exception ex) { Error.WriteError(ex); _ErrorMessage = ex.Message; } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } }
public static bool ClearFound() { bool success = false; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); const string sql = @"UPDATE dbo.Servers SET IsFound = 0"; SqlCommand cmd = new SqlCommand(sql.ToString(), conn); cmd.CommandType = CommandType.Text; try { cmd = new SqlCommand(sql, conn); if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.ExecuteNonQuery(); success = true; } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(success); }
public static string AppMessage(string Code) { string setting = string.Empty; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = @"SELECT Message FROM dbo.Messages WHERE Number = @Number"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@Number", Code); if (conn.State != ConnectionState.Open) { conn.Open(); } SqlDataReader sdr; sdr = cmd.ExecuteReader(); if (sdr.Read()) { setting = sdr["Message"].ToString(); } } catch (SqlException sqlex) { Error.WriteError(sqlex); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(setting); }
public bool Delete() { bool Successful = false; using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); try { string sql = "DELETE FROM dbo.Modules WHERE ID = @ID"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@ID", _ID); if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.ExecuteNonQuery(); Successful = true; } catch (SqlException sqlex) { Error.WriteError(sqlex); _ErrorMessage = sqlex.Message; } catch (Exception ex) { Error.WriteError(ex); _ErrorMessage = ex.Message; } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } return(Successful); }
public static DataTable Events() { using (new Impersonator()) { SqlConnection conn = DataSource.Conn(); DataTable dt = new DataTable(); try { string sql = string.Empty; sql = @"SELECT * FROM Webs WHERE (PreMigrateOn > '1/1/1900') AND (MigrationCompleted = '1/1/1900') OR (MigrationCompleted = '1/1/1900') AND (MigrateOn > '1/1/1900')"; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); } catch (Exception ex) { Error.WriteError(ex); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } return(dt); } }