/projects/PigeonCms.Core/DAL/StaticPagesManager.cs

  1using System;
  2using System.Data;
  3using System.Configuration;
  4using System.Web;
  5using System.Web.Security;
  6using System.Web.UI;
  7using System.Web.UI.WebControls;
  8using System.Web.UI.WebControls.WebParts;
  9using System.Web.UI.HtmlControls;
 10using System.ComponentModel;
 11using System.Collections.Generic;
 12using System.Diagnostics;
 13using System.Threading;
 14using System.Data.Common;
 15using System.Text.RegularExpressions;
 16
 17
 18namespace PigeonCms
 19{
 20    /// <summary>
 21    /// DAL for StaticPage (in table staticPages)
 22    /// </summary>
 23    public class StaticPagesManager: TableManager<StaticPage, StaticPageFilter,string>, ITableManager
 24    {
 25        public const string DEFAULT_PAGE_NAME = "default";
 26
 27        public StaticPagesManager()
 28        {
 29            this.TableName = "#__staticPages ";
 30            this.KeyFieldName = "PageName";
 31        }
 32
 33        public override List<StaticPage> GetByFilter(StaticPageFilter filter, string sort)
 34        {
 35            DbProviderFactory myProv = Database.ProviderFactory;
 36            DbConnection myConn = myProv.CreateConnection();
 37            DbDataReader myRd = null;
 38            DbCommand myCmd = myConn.CreateCommand();
 39            string sSql;
 40            List<StaticPage> result = new List<StaticPage>();
 41
 42            try
 43            {
 44                myConn.ConnectionString = Database.ConnString;
 45                myConn.Open();
 46                myCmd.Connection = myConn;
 47
 48                sSql = "SELECT pageName FROM #__staticPages WHERE 1=1 ";
 49                if (!string.IsNullOrEmpty(filter.PageName))
 50                {
 51                    sSql += " AND pageName = @pageName ";
 52                    myCmd.Parameters.Add(Database.Parameter(myProv, "pageName", filter.PageName));
 53                }
 54                if (filter.Visible != Utility.TristateBool.NotSet)
 55                {
 56                    sSql += " AND Visible = @Visible ";
 57                    myCmd.Parameters.Add(Database.Parameter(myProv, "Visible", filter.Visible));
 58                }
 59                if (!string.IsNullOrEmpty(sort))
 60                {
 61                    sSql += " ORDER BY " + sort;
 62                }
 63                else
 64                {
 65                    sSql += " ORDER BY pageName ";
 66                }
 67                myCmd.CommandText = Database.ParseSql(sSql);
 68                myRd = myCmd.ExecuteReader();
 69                while (myRd.Read())
 70                {
 71                    StaticPage item = GetStaticPageByName((string)myRd["pageName"]);
 72                    result.Add(item);
 73                }
 74                myRd.Close();
 75            }
 76            finally
 77            {
 78                myConn.Dispose();
 79            }
 80            return result;
 81        }
 82
 83        /// <summary>
 84        /// dictionary list to use in module admin area (combo)
 85        /// </summary>
 86        /// <returns></returns>
 87        public override Dictionary<string,string> GetList()
 88        {
 89            Dictionary<string, string> res = new Dictionary<string, string>();
 90            StaticPageFilter filter = new StaticPageFilter();
 91            List<StaticPage>list = GetByFilter(filter, "");
 92            foreach (StaticPage item in list)
 93            {
 94                res.Add(item.PageName, item.PageName);
 95            }
 96            return res;
 97        }
 98
 99        /// <summary>
100        /// Indica se la pageName esiste o no
101        /// </summary>
102        /// <param name="pageName"></param>
103        /// <returns>true or false</returns>
104        public static bool ExistPage(string pageName)
105        {
106            DbProviderFactory myProv = Database.ProviderFactory;
107            DbConnection myConn = myProv.CreateConnection();
108            DbDataReader myRd = null;
109            DbCommand myCmd = myConn.CreateCommand();
110            string sSql;
111            bool result = false;
112
113            if (pageName == null) pageName = "";
114            try
115            {
116                myConn.ConnectionString = Database.ConnString;
117                myConn.Open();
118                myCmd.Connection = myConn;
119
120                sSql = "SELECT pageName "
121                + " FROM #__staticPages m "
122                + " WHERE pageName = @pageName ";
123                myCmd.CommandText = Database.ParseSql(sSql);
124                myCmd.Parameters.Add(Database.Parameter(myProv, "pageName", pageName));
125                myRd = myCmd.ExecuteReader();
126                if (myRd.Read())
127                {
128                    result = true;
129                }
130                myRd.Close();
131            }
132            finally
133            {
134                myConn.Dispose();
135            }
136            return result;
137        }
138
139        public StaticPage GetStaticPageByName(string pageName)
140        {
141            DbProviderFactory myProv = Database.ProviderFactory;
142            DbConnection myConn = myProv.CreateConnection();
143            DbDataReader myRd = null;
144            DbCommand myCmd = myConn.CreateCommand();
145            string sSql;
146            StaticPage result = new StaticPage();
147
148            try
149            {
150                myConn.ConnectionString = Database.ConnString;
151                myConn.Open();
152                myCmd.Connection = myConn;
153
154                sSql = "SELECT pageName, visible, showPageTitle "
155                + " FROM #__staticPages m "
156                + " WHERE pageName = @pageName ";
157                myCmd.CommandText = Database.ParseSql(sSql);
158                myCmd.Parameters.Add(Database.Parameter(myProv, "pageName", pageName));
159                myRd = myCmd.ExecuteReader(CommandBehavior.SingleRow);
160                if (myRd.Read())
161                {
162                    if (!Convert.IsDBNull(myRd["pageName"]))
163                        result.PageName = (string)myRd["pageName"];
164                    if (!Convert.IsDBNull(myRd["visible"]))
165                        result.Visible = (bool)myRd["visible"];
166                    if (!Convert.IsDBNull(myRd["showPageTitle"]))
167                        result.ShowPageTitle = (bool)myRd["showPageTitle"];
168                }
169                myRd.Close();
170                
171                //culture specific texts
172                sSql = "SELECT cultureName, pageName, pageTitle, pageContent "
173                + " FROM #__staticPages_Culture m "
174                + " WHERE pageName=@pageName ";
175                myCmd.CommandText = Database.ParseSql(sSql);
176                myCmd.Parameters.Clear();
177                myCmd.Parameters.Add(Database.Parameter(myProv, "pageName", pageName));
178                myRd = myCmd.ExecuteReader();
179                while (myRd.Read())
180                {
181                    if (!Convert.IsDBNull(myRd["pageTitle"]))
182                        result.PageTitleTranslations.Add((string)myRd["cultureName"], (string)myRd["pageTitle"]);
183                    if (!Convert.IsDBNull(myRd["pageContent"]))
184                        result.PageContentTranslations.Add((string)myRd["cultureName"], (string)myRd["pageContent"]);
185                }
186                myRd.Close();
187            }
188            finally
189            {
190                myConn.Dispose();
191            }
192            return result;
193        }
194
195        public override int Update(StaticPage theObj)
196        {
197            DbProviderFactory myProv = Database.ProviderFactory;
198            DbTransaction myTrans = null;
199            DbConnection myConn = myProv.CreateConnection();
200            DbCommand myCmd = myConn.CreateCommand();
201            string sSql;
202            int result = 0;
203
204            try
205            {
206                myConn.ConnectionString = Database.ConnString;
207                myConn.Open();
208                myCmd.Connection = myConn;
209
210                myTrans = myConn.BeginTransaction();
211                myCmd.Transaction = myTrans;
212
213                sSql = "UPDATE #__staticPages SET visible=@visible, "
214                + " ShowPageTitle=@ShowPageTitle "
215                + " WHERE pageName = @pageName";
216                myCmd.CommandText = Database.ParseSql(sSql);
217                myCmd.Parameters.Add(Database.Parameter(myProv, "pageName", theObj.PageName));
218                myCmd.Parameters.Add(Database.Parameter(myProv, "visible", theObj.Visible));
219                myCmd.Parameters.Add(Database.Parameter(myProv, "ShowPageTitle", theObj.ShowPageTitle));
220                result = myCmd.ExecuteNonQuery();
221
222                updateCultureText(theObj, myCmd, myProv);
223
224                myTrans.Commit();
225            }
226            catch (Exception e)
227            {
228                myTrans.Rollback();
229                throw e;
230            }
231            finally
232            {
233                myTrans.Dispose();
234                myConn.Dispose();
235            }
236            return result;
237        }
238
239        public override StaticPage Insert(StaticPage newObj)
240        {
241            DbProviderFactory myProv = Database.ProviderFactory;
242            DbTransaction myTrans = null;
243            DbConnection myConn = myProv.CreateConnection();
244            DbCommand myCmd = myConn.CreateCommand();
245            string sSql;
246            StaticPage result = new StaticPage();
247
248            try
249            {
250                myConn.ConnectionString = Database.ConnString;
251                myConn.Open();
252                myCmd.Connection = myConn;
253
254                myTrans = myConn.BeginTransaction();
255                myCmd.Transaction = myTrans;
256
257                result.PageName = newObj.PageName;
258                result.PageTitleTranslations = newObj.PageTitleTranslations;
259                result.PageContentTranslations = newObj.PageContentTranslations;
260                result.Visible = newObj.Visible;
261                result.ShowPageTitle = newObj.ShowPageTitle;
262
263                sSql = "INSERT INTO #__staticPages(PageName, Visible, ShowPageTitle) "
264                + "VALUES(@PageName, @Visible, @ShowPageTitle) ";
265                myCmd.CommandText = Database.ParseSql(sSql);
266                myCmd.Parameters.Add(Database.Parameter(myProv, "PageName", result.PageName));
267                myCmd.Parameters.Add(Database.Parameter(myProv, "Visible", result.Visible));
268                myCmd.Parameters.Add(Database.Parameter(myProv, "ShowPageTitle", result.ShowPageTitle));
269                myCmd.ExecuteNonQuery();
270
271                updateCultureText(result, myCmd, myProv);
272
273                myTrans.Commit();
274            }
275            catch (Exception e)
276            {
277                myTrans.Rollback();
278                throw e;
279            }
280            finally
281            {
282                myTrans.Dispose();
283                myConn.Dispose();
284            }
285            return result;
286        }
287
288        public int Delete(string pageName)
289        {
290            DbProviderFactory myProv = Database.ProviderFactory;
291            DbConnection myConn = myProv.CreateConnection();
292            DbCommand myCmd = myProv.CreateCommand();
293
294            string sSql;
295            int res = 0;
296
297            try
298            {
299                myConn.ConnectionString = Database.ConnString;
300                myConn.Open();
301                myCmd.Connection = myConn;
302
303                sSql = "DELETE FROM #__staticPages WHERE pageName = @pageName ";
304                myCmd.CommandText = Database.ParseSql(sSql);
305                myCmd.Parameters.Add(Database.Parameter(myProv, "pageName", pageName));
306                res = myCmd.ExecuteNonQuery();
307
308                sSql = "DELETE FROM #__staticPages_Culture WHERE pageName = @pageName ";
309                myCmd.CommandText = Database.ParseSql(sSql);
310                myCmd.Parameters.Clear();
311                myCmd.Parameters.Add(Database.Parameter(myProv, "pageName", pageName));
312                myCmd.ExecuteNonQuery();
313            }
314            finally
315            {
316                myConn.Dispose();
317            }
318            return res;
319        }
320
321        private static void updateCultureText(StaticPage theObj, DbCommand myCmd, DbProviderFactory myProv)
322        {
323            string sSql = "";
324            foreach (KeyValuePair<string, string> item in theObj.PageTitleTranslations)
325            {
326                string contentValue = "";
327                theObj.PageContentTranslations.TryGetValue(item.Key, out contentValue);
328
329                sSql = "DELETE FROM #__staticPages_Culture WHERE CultureName=@CultureName AND PageName=@PageName ";
330                myCmd.CommandText = Database.ParseSql(sSql);
331                myCmd.Parameters.Clear();
332                myCmd.Parameters.Add(Database.Parameter(myProv, "CultureName", item.Key));
333                myCmd.Parameters.Add(Database.Parameter(myProv, "PageName", theObj.PageName));
334                myCmd.ExecuteNonQuery();
335
336                sSql = "INSERT INTO #__staticPages_Culture(CultureName, PageName, PageTitle, PageContent) "
337                + "VALUES(@CultureName, @PageName, @PageTitle, @PageContent) ";
338                myCmd.CommandText = Database.ParseSql(sSql);
339                myCmd.Parameters.Clear();
340                myCmd.Parameters.Add(Database.Parameter(myProv, "CultureName", item.Key));
341                myCmd.Parameters.Add(Database.Parameter(myProv, "PageName", theObj.PageName));
342                myCmd.Parameters.Add(Database.Parameter(myProv, "PageTitle", item.Value));
343                myCmd.Parameters.Add(Database.Parameter(myProv, "PageContent", contentValue));
344                myCmd.ExecuteNonQuery();
345            }
346        }
347    }
348}