public void AddCuratedPage(SocialSite data)
{
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString)) {
using (SqlCommand cmd = new SqlCommand()) {
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
//Make room at the top
cmd.CommandText = "UPDATE SocialPage SET SortOrder = SortOrder + 1 WHERE Town = @Town";
cmd.Parameters.Add("Town", SqlDbType.VarChar, 50).Value = data.Town;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
cmd.CommandText = "INSERT SocialPage (Town, PageType, URL, ImageURL, TitleText, SortOrder) " +
"VALUES (@Town, @PageType, @URL, @ImageURL, @TitleText, 0)";
cmd.Parameters.Add("PageType", SqlDbType.VarChar, 50).Value = data.PageType;
cmd.Parameters.Add("URL", SqlDbType.VarChar).Value = (data.URL + "").Trim();
cmd.Parameters.Add("ImageURL", SqlDbType.VarChar).Value = (data.ImageURL + "").Trim();
cmd.Parameters.Add("TitleText", SqlDbType.VarChar, 250).Value = (data.TitleText + "").Trim();
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
}
public List <SocialSite> GetPages()
{
List <SocialSite> l = new List <SocialSite>();
try {
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString)) {
string SQL = "SELECT Town, PageType, URL, TitleText, SocialPageSerial, ImageURL FROM [SocialPage] WHERE Town = @Town ORDER BY SortOrder, PageType, TitleText";
using (SqlCommand cmd = new SqlCommand(SQL, cn)) {
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("Town", SqlDbType.VarChar, 50).Value = _Town;
cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SocialSite s = new SocialSite();
s.Town = dr[0].ToString();
s.PageType = dr[1].ToString();
if (s.PageType == "InstagramEmbed")
{
string temp = dr[2].ToString();
if (temp.IndexOf("<script") > 0)
{
s.URL = temp.Substring(0, temp.IndexOf("<script"));
}
else
{
s.URL = temp;
}
}
else
{
s.URL = dr[2].ToString();
}
s.TitleText = dr[3].ToString();
s.SocialPageSerial = dr.GetInt32(4);
s.ImageURL = dr[5].ToString();
l.Add(s);
}
cmd.Connection.Close();
}
}
} catch (Exception) {
}
return(l);
}
public SocialSite GetPage(int SocialPageSerial)
{
SocialSite s = new SocialSite();
try {
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString)) {
string SQL = "SELECT Town, PageType, URL, TitleText, ImageURL FROM [SocialPage] WHERE SocialPageSerial = @SocialPageSerial";
using (SqlCommand cmd = new SqlCommand(SQL, cn)) {
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("SocialPageSerial", SqlDbType.Int).Value = SocialPageSerial;
cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
s.Town = dr[0].ToString();
s.PageType = dr[1].ToString();
if (s.PageType == "InstagramEmbed")
{
string temp = dr[2].ToString();
if (temp.IndexOf("<script") > 0)
{
s.URL = temp.Substring(0, temp.IndexOf("<script"));
}
else
{
s.URL = temp;
}
}
else
{
s.URL = dr[2].ToString();
}
s.TitleText = dr[3].ToString();
s.SocialPageSerial = SocialPageSerial;
s.ImageURL = dr[4].ToString();
}
cmd.Connection.Close();
}
}
} catch (Exception) {
}
return(s);
}
public void EditCuratedPage(SocialSite data)
{
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString)) {
using (SqlCommand cmd = new SqlCommand()) {
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
//Make room at the top
cmd.CommandText = "UPDATE SocialPage SET PageType = @PageType, URL = @URL, ImageURL = @ImageURL, TitleText = @TitleText WHERE SocialPageSerial = @SocialPageSerial";
cmd.Parameters.Add("SocialPageSerial", SqlDbType.Int).Value = data.SocialPageSerial;
cmd.Parameters.Add("PageType", SqlDbType.VarChar, 50).Value = data.PageType;
cmd.Parameters.Add("URL", SqlDbType.VarChar).Value = data.URL;
cmd.Parameters.Add("ImageURL", SqlDbType.VarChar).Value = data.ImageURL;
cmd.Parameters.Add("TitleText", SqlDbType.VarChar, 250).Value = data.TitleText;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
}