/// <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();
}
}
