/// <summary> /// Gets all citrix plans in the database /// </summary> /// <returns></returns> public static List<BaseCitrixApp> GetCitrixPlans() { SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString); SqlCommand cmd = new SqlCommand(@"SELECT *,(SELECT COUNT(UserID) FROM UserPlansCitrix WHERE CitrixPlanID=p.CitrixPlanID) AS UserCount FROM Plans_Citrix p ORDER BY Name", sql); try { // Open connection sql.Open(); // Create collection List<BaseCitrixApp> citrixApps = new List<BaseCitrixApp>(); // Read data SqlDataReader r = cmd.ExecuteReader(); if (r.HasRows) { while (r.Read()) { BaseCitrixApp tmp = new BaseCitrixApp(); tmp.ID = int.Parse(r["CitrixPlanID"].ToString()); tmp.DisplayName = r["Name"].ToString(); tmp.GroupName = r["GroupName"].ToString(); tmp.IsServer = bool.Parse(r["IsServer"].ToString()); if (r["CompanyCode"] != DBNull.Value) tmp.CompanyCode = r["CompanyCode"].ToString(); if (r["Description"] != DBNull.Value) tmp.Description = r["Description"].ToString(); if (r["Cost"] != DBNull.Value) tmp.Cost = r["Cost"].ToString(); else tmp.Cost = "0.00"; if (r["Price"] != DBNull.Value) tmp.Price = r["Price"].ToString(); else tmp.Price = "0.00"; if (r["PictureURL"] != DBNull.Value) tmp.PictureURL = r["PictureURL"].ToString(); if (r["UserCount"] != DBNull.Value) tmp.CurrentUsers = int.Parse(r["UserCount"].ToString()); citrixApps.Add(tmp); } } // Close r.Close(); sql.Close(); // Dispose r.Dispose(); // Return return citrixApps; } catch (Exception ex) { throw; } finally { if (cmd != null) cmd.Dispose(); if (sql != null) sql.Dispose(); } }
/// <summary> /// Adds a citrix plan to the database /// </summary> /// <param name="citrixApp"></param> public static void AddCitrixPlan(BaseCitrixApp citrixApp) { SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString); SqlCommand cmd = new SqlCommand(@"INSERT INTO Plans_Citrix (Name, GroupName, Description, IsServer, CompanyCode, Price, Cost, PictureURL) VALUES (@Name, @GroupName, @Description, @IsServer, @CompanyCode, @Price, @Cost, @PictureURL)", sql); try { // Add parameters cmd.Parameters.AddWithValue("Name", citrixApp.DisplayName); cmd.Parameters.AddWithValue("GroupName", citrixApp.GroupName); cmd.Parameters.AddWithValue("Description", citrixApp.Description); cmd.Parameters.AddWithValue("IsServer", citrixApp.IsServer); cmd.Parameters.AddWithValue("CompanyCode", citrixApp.CompanyCode); cmd.Parameters.AddWithValue("Price", citrixApp.Price); cmd.Parameters.AddWithValue("Cost", citrixApp.Cost); if (!string.IsNullOrEmpty(citrixApp.PictureURL)) cmd.Parameters.AddWithValue("PictureURL", citrixApp.PictureURL); else cmd.Parameters.AddWithValue("PictureURL", string.Empty); // Open connection sql.Open(); // Insert cmd.ExecuteNonQuery(); // Close sql.Close(); } catch (Exception ex) { throw; } finally { if (cmd != null) cmd.Dispose(); if (sql != null) sql.Dispose(); } }
/// <summary> /// Updates a citrix plan in the database /// </summary> /// <param name="citrixApp"></param> public static void UpdateCitrixPlan(BaseCitrixApp citrixApp) { SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString); SqlCommand cmd = new SqlCommand(@"", sql); try { if (!string.IsNullOrEmpty(citrixApp.PictureURL)) { cmd.CommandText = @"UPDATE Plans_Citrix SET Name=@Name, Description=@Description, IsServer=@IsServer, CompanyCode=@CompanyCode, Price=@Price, Cost=@Cost, PictureURL=@PictureURL WHERE CitrixPlanID=@CitrixPlanID"; cmd.Parameters.AddWithValue("PictureURL", citrixApp.PictureURL); } else { cmd.CommandText = @"UPDATE Plans_Citrix SET Name=@Name, Description=@Description, IsServer=@IsServer, CompanyCode=@CompanyCode, Price=@Price, Cost=@Cost WHERE CitrixPlanID=@CitrixPlanID"; } // Add parameters cmd.Parameters.AddWithValue("CitrixPlanID", citrixApp.ID); cmd.Parameters.AddWithValue("Name", citrixApp.DisplayName); cmd.Parameters.AddWithValue("Description", citrixApp.Description); cmd.Parameters.AddWithValue("IsServer", citrixApp.IsServer); cmd.Parameters.AddWithValue("CompanyCode", citrixApp.CompanyCode); cmd.Parameters.AddWithValue("Price", citrixApp.Price); cmd.Parameters.AddWithValue("Cost", citrixApp.Cost); // Open connection sql.Open(); // Update cmd.ExecuteNonQuery(); // Close sql.Close(); } catch (Exception ex) { throw; } finally { if (cmd != null) cmd.Dispose(); if (sql != null) sql.Dispose(); } }
/// <summary> /// Gets a particular Citrix plan /// </summary> /// <param name="citrixAppID"></param> /// <returns></returns> public static BaseCitrixApp GetCitrixPlan(string citrixAppID) { SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString); SqlCommand cmd = new SqlCommand("SELECT * FROM Plans_Citrix WHERE CitrixPlanID=@CitrixPlanID", sql); try { // Add parameters cmd.Parameters.AddWithValue("CitrixPlanID", citrixAppID); // Open connection sql.Open(); // Create our return object BaseCitrixApp tmp = new BaseCitrixApp(); // Read data SqlDataReader r = cmd.ExecuteReader(); if (r.HasRows) { while (r.Read()) { tmp.ID = int.Parse(r["CitrixPlanID"].ToString()); tmp.DisplayName = r["Name"].ToString(); tmp.GroupName = r["GroupName"].ToString(); tmp.IsServer = bool.Parse(r["IsServer"].ToString()); if (r["CompanyCode"] != DBNull.Value) tmp.CompanyCode = r["CompanyCode"].ToString(); if (r["Description"] != DBNull.Value) tmp.Description = r["Description"].ToString(); if (r["Cost"] != DBNull.Value) tmp.Cost = r["Cost"].ToString(); else tmp.Cost = "0.00"; if (r["Price"] != DBNull.Value) tmp.Price = r["Price"].ToString(); else tmp.Price = "0.00"; if (r["PictureURL"] != DBNull.Value) tmp.PictureURL = r["PictureURL"].ToString(); } } // Close r.Close(); sql.Close(); // Dispose r.Dispose(); // Return return tmp; } catch (Exception ex) { throw; } finally { if (cmd != null) cmd.Dispose(); if (sql != null) sql.Dispose(); } }