/projects/PigeonCms.Core/Helpers/Database.cs

  1using System;
  2using System.Configuration;
  3using System.Data;
  4using System.Text;
  5using System.Web;
  6using System.Web.Security;
  7using System.Web.UI;
  8using System.Web.UI.HtmlControls;
  9using System.Web.UI.WebControls;
 10using System.Web.UI.WebControls.WebParts;
 11using System.Collections.Generic;
 12using System.ComponentModel;
 13using System.Data.Common;
 14using System.Reflection;
 15using System.Diagnostics;
 16using System.IO;
 17
 18namespace PigeonCms
 19{
 20    /// <summary>
 21    /// Database useful functions
 22    /// </summary>
 23    public static class Database
 24    {
 25        /// <summary>
 26        /// retrieve the connectionstring using ConnectionStringName setting in AppSettings section
 27        /// </summary>
 28        public static string ConnString
 29        {
 30            get 
 31            {
 32                try
 33                {
 34                    string res = "";
 35                    string connectionStringName = ConfigurationManager.AppSettings["ConnectionStringName"].ToString();
 36                    ConnectionStringsSection section = (ConnectionStringsSection)ConfigurationManager.GetSection("connectionStrings");
 37                    res = section.ConnectionStrings[connectionStringName].ToString();
 38                    return res;
 39                }
 40                catch (Exception ex1)
 41                {
 42                    throw new Exception("Invalid connectionStringName", ex1);
 43                }
 44            }
 45        }
 46
 47        public static string ProviderName
 48        {
 49            get { return ConfigurationManager.AppSettings["ProviderName"].ToString(); }
 50        }
 51
 52        public static DbProviderFactory ProviderFactory 
 53        {
 54            get
 55            {
 56                return DbProviderFactories.GetFactory(Database.ProviderName);
 57            }
 58        }
 59
 60        public enum MoveRecordDirection
 61        {
 62            Up = 1,
 63            Down
 64        }
 65
 66        /// <summary>
 67        /// parse sql string before the command execution
 68        /// </summary>
 69        /// <param name="sqlQuery"></param>
 70        /// <param name="tabPrefix">current installation tab prefix (usually 'pgn_')</param>
 71        /// <returns></returns>
 72        public static string ParseSql(string sqlQuery, string tabPrefix)
 73        {
 74            const string PlaceHolder = "#__";
 75            if (!string.IsNullOrEmpty(tabPrefix))
 76                return sqlQuery.Replace(PlaceHolder, tabPrefix);
 77            else
 78                return sqlQuery.Replace(PlaceHolder, Config.TabPrefix);
 79        }
 80
 81        /// <summary>
 82        /// parse sql string before the command execution
 83        /// </summary>
 84        /// <param name="sqlQuery"></param>
 85        /// <returns></returns>
 86        public static string ParseSql(string sqlQuery)
 87        {
 88            return ParseSql(sqlQuery, "");
 89        }
 90
 91        public static string AddDatesRangeParameters(DbParameterCollection parameters, 
 92            DbProviderFactory myProv, string sqlFieldName, DatesRange rangeFilter)
 93        {
 94            string res = "";
 95
 96            if (rangeFilter.DateRangeType == DatesRange.RangeType.Always)
 97                res = "1=1";
 98            else if (rangeFilter.DateRangeType == DatesRange.RangeType.None)
 99                res = "1=0";
100            else
101            {
102                res = "1=1";
103                if (rangeFilter.InitDate != DateTime.MinValue)
104                {
105                    res += " AND " + sqlFieldName + " >= @InitDate";
106                    parameters.Add(Database.Parameter(myProv, "InitDate", rangeFilter.InitDate));
107                }
108                if (rangeFilter.EndDate != DateTime.MaxValue)
109                {
110                    res += " AND " + sqlFieldName + " < @EndDate";
111                    parameters.Add(Database.Parameter(myProv, "EndDate", rangeFilter.EndDate.AddDays(1)));
112                }
113            }
114            return res;
115        }
116
117        /// <summary>
118        /// execute 1 sql statement
119        /// </summary>
120        /// <param name="myRd"></param>
121        /// <param name="myCmd"></param>
122        /// <param name="sqlCommand">sql string (already parsed) with Database.ParseSql method</param>
123        /// <returns>formatted results</returns>
124        public static string ExecuteCommand(DbDataReader myRd, DbCommand myCmd, string sqlCommand)
125        {
126            string res = "";
127
128            myCmd.CommandText = sqlCommand;
129            myRd = myCmd.ExecuteReader();
130            if (myRd.HasRows)
131            {
132                int row = 0;
133                res += "<table>";
134                while (myRd.Read())
135                {
136                    //col headers
137                    if (row == 0)
138                    {
139                        res += "<tr>";
140                        for (int i = 0; i < myRd.FieldCount; i++)
141                        {
142                            res += "<th>" + myRd.GetName(i) + "</th>";
143                        }
144                        res += "</tr>";
145                    }
146
147                    res += "<tr>";
148                    for (int i = 0; i < myRd.FieldCount; i++)
149                    {
150                        res += "<td>" +
151                            HttpContext.Current.Server.HtmlEncode(myRd[i].ToString()) + 
152                            "</td>";
153                    }
154                    res += "</tr>";
155
156                    row++;
157                }
158                res += "</table>";
159            }
160            myRd.Close();
161            return res;
162        }
163
164        /// <summary>
165        /// execute multiple sql statement
166        /// </summary>
167        /// <param name="myRd"></param>
168        /// <param name="myCmd"></param>
169        /// <param name="sqlQuery">sql string (already parsed) with Database.ParseSql method</param>
170        /// <returns></returns>
171        public static string ExecuteQuery(DbDataReader myRd, DbCommand myCmd, string sqlQuery)
172        {
173            string line = "";        //current sqlQuery line
174            string sqlCommand = "";  //single command statement
175            int lineCounter = 0;
176            string res = "";
177
178            try
179            {
180                StringReader reader = new StringReader(sqlQuery);
181                while ((line = reader.ReadLine()) != null)
182                {
183                    lineCounter++;
184                    line = line.Trim();
185                    if (line.ToUpper() == "GO")
186                    {
187                        res += Database.ExecuteCommand(myRd, myCmd, sqlCommand);
188                        sqlCommand = "";
189                    }
190                    else
191                    {
192                        sqlCommand += line + "\n";
193                    }
194                }
195                if (!string.IsNullOrEmpty(sqlCommand))
196                    res += Database.ExecuteCommand(myRd, myCmd, sqlCommand);
197            }
198            catch (DbException ex)
199            {
200                throw new Exception(@"Sql query line " + lineCounter, ex);
201            }
202            return res;
203        }
204
205        public static DbParameter Parameter(DbProviderFactory provider, string paramName, object paramValue)
206        {
207            return Parameter(provider, paramName, paramValue, DbType.Object);
208        }
209
210        public static DbParameter Parameter(DbProviderFactory provider, string paramName, object paramValue, DbType paramType)
211        {
212            DbParameter p1 = provider.CreateParameter();
213            p1.ParameterName = paramName;
214            p1.Value = paramValue;
215            if (paramType != DbType.Object)
216            {
217                p1.DbType = paramType;    
218            }
219            //p1.Direction = ParameterDirection.Input
220            return p1;
221        }
222    }
223
224
225    public class TableManagerWithOrdering<T,F,Kkey>: 
226        PigeonCms.TableManager<T,F,Kkey> where T: PigeonCms.ITableWithOrdering
227    {
228        /// <summary>
229        /// change record order in list
230        /// </summary>
231        /// <param name="recordId">current record id</param>
232        /// <param name="direction">direction up, down</param>
233        [DataObjectMethod(DataObjectMethodType.Update, false)]
234        public virtual void MoveRecord(Kkey recordId, Database.MoveRecordDirection direction)
235        {
236            T o1;// = new T();
237            T o2;// = new T();
238            int tmpOrdering = 0;
239
240            try
241            {
242                o1 = GetByKey(recordId);
243                tmpOrdering = o1.Ordering;
244                if (direction == Database.MoveRecordDirection.Up)
245                {
246                    o2 = GetByKey(GetPreviousRecordInOrder(tmpOrdering, recordId));
247                }
248                else
249                {
250                    o2 = GetByKey(GetNextRecordInOrder(tmpOrdering, recordId));
251                    if (o1.Ordering == o2.Ordering) o2.Ordering++;
252                }
253                if (o2.Ordering != tmpOrdering)
254                {
255                    o1.Ordering = o2.Ordering;
256                    o2.Ordering = tmpOrdering;
257                    Update(o1);
258                    Update(o2);
259                }
260            }
261            finally
262            {
263            }
264        }
265
266        /// <summary>
267        /// return previous record id in order, if first return currentRecordId
268        /// </summary>
269        /// <param name="orderId"></param>
270        /// <param name="currentRecordId"></param>
271        /// <returns>a record id</returns>
272        protected virtual Kkey GetPreviousRecordInOrder(int ordering, Kkey currentRecordId)
273        {
274            DbProviderFactory myProv = Database.ProviderFactory;
275            DbConnection myConn = myProv.CreateConnection();
276            DbDataReader myRd = null;
277            DbCommand myCmd = myConn.CreateCommand();
278            string sSql;
279            Kkey result = currentRecordId;
280
281            try
282            {
283                myConn.ConnectionString = Database.ConnString;
284                myConn.Open();
285                myCmd.Connection = myConn;
286
287                sSql = "SELECT TOP 1 [" + this.KeyFieldName + "] FROM [" + this.TableName + "] WHERE ordering < @ordering ORDER BY ordering DESC ";
288                myCmd.CommandText = Database.ParseSql(sSql);
289                myCmd.Parameters.Add(Database.Parameter(myProv, "ordering", ordering));
290                myRd = myCmd.ExecuteReader();
291                if (myRd.Read())
292                {
293                    if (myRd[0] != DBNull.Value)
294                    {
295                        result = (Kkey)myRd[0];
296                    }
297                }
298                myRd.Close();
299                //se nn trovo un record prendo quello precedente per chiave (per init tabella)
300                if (result.ToString() == "0")
301                {
302                    sSql = "SELECT TOP 1 [" + KeyFieldName + "] FROM " + TableName
303                        + " WHERE [" + KeyFieldName + "] < @currentRecordId ORDER BY ordering ASC ";
304                    myCmd.CommandText = Database.ParseSql(sSql);
305                    myCmd.Parameters.Add(Database.Parameter(myProv, "currentRecordId", currentRecordId));
306                    myRd = myCmd.ExecuteReader();
307                    if (myRd.Read())
308                    {
309                        if (myRd[0] != DBNull.Value)
310                        {
311                            result = (Kkey)myRd[0];
312                        }
313                    }
314                    myRd.Close();
315                }
316            }
317            finally
318            {
319                myConn.Dispose();
320            }
321            return result;
322        }
323
324        /// <summary>
325        /// return next record in order, if last return currentRecordId
326        /// </summary>
327        /// <param name="orderId"></param>
328        /// <param name="currentRecordId"></param>
329        /// <returns>a recordId</returns>
330        protected virtual Kkey GetNextRecordInOrder(int ordering, Kkey currentRecordId)
331        {
332            DbProviderFactory myProv = Database.ProviderFactory;
333            DbConnection myConn = myProv.CreateConnection();
334            DbDataReader myRd = null;
335            DbCommand myCmd = myConn.CreateCommand();
336            string sSql;
337            Kkey result = currentRecordId;
338
339            try
340            {
341                myConn.ConnectionString = Database.ConnString;
342                myConn.Open();
343                myCmd.Connection = myConn;
344
345                sSql = "SELECT TOP 1 [" + this.KeyFieldName + "] FROM [" + this.TableName + "] WHERE ordering > @ordering ORDER BY ordering ASC ";
346                myCmd.CommandText = Database.ParseSql(sSql);
347                myCmd.Parameters.Add(Database.Parameter(myProv, "ordering", ordering));
348                myRd = myCmd.ExecuteReader();
349                if (myRd.Read())
350                {
351                    if (myRd[0] != DBNull.Value)
352                    {
353                        result = (Kkey)myRd[0];
354                    }
355                }
356                myRd.Close();
357                //se nn trovo un record prendo quello successivo per chiave (per init tabella)
358                if (result.ToString() == currentRecordId.ToString())
359                {
360                    sSql = "SELECT TOP 1 [" + KeyFieldName + "] FROM " + TableName
361                        + " WHERE [" + KeyFieldName + "] > @currentRecordId ORDER BY ordering, [" + KeyFieldName + "] ";
362                    myCmd.CommandText = Database.ParseSql(sSql);
363                    myCmd.Parameters.Add(Database.Parameter(myProv, "currentRecordId", currentRecordId));
364                    myRd = myCmd.ExecuteReader();
365                    if (myRd.Read())
366                    {
367                        if (myRd[0] != DBNull.Value)
368                        {
369                            result = (Kkey)myRd[0];
370                        }
371                    }
372                    myRd.Close();
373                }
374            }
375            finally
376            {
377                myConn.Dispose();
378            }
379            return result;
380        }
381    }
382
383
384    /// <summary>
385    /// common methods in DAL class
386    /// </summary>
387    /// <typeparam name="T">BLL class</typeparam>
388    /// <typeparam name="F">BLL filter class</typeparam>
389    /// <typeparam name="K">type of key class</typeparam>
390    [DataObject()]
391    public class TableManager<T,F,Kkey> where T: PigeonCms.ITable
392    {
393        #region fields
394        private string tableName = "";
395        private string keyFieldName = "";
396
397        /// <summary>
398        /// name of the key field in the table
399        /// </summary>
400        public string KeyFieldName
401        {
402            [DebuggerStepThrough()]
403            get { return this.keyFieldName; }
404            [DebuggerStepThrough()]
405            set { this.keyFieldName = value; } 
406        }
407
408        /// <summary>
409        /// name of the table managed by the class
410        /// </summary>
411        public string TableName
412        {
413            [DebuggerStepThrough()]
414            get { return this.tableName; }
415            [DebuggerStepThrough()]
416            set { this.tableName = value; }
417        }
418        #endregion
419     
420        [DebuggerStepThrough()]
421        public TableManager(){}
422
423        [DebuggerStepThrough()]
424        public TableManager(string mainTableName, string keyFieldName)
425        {
426            TableName = mainTableName;
427            KeyFieldName = keyFieldName;
428        }
429
430        /// <summary>
431        /// dictionary list to use in module admin area (combo)
432        /// </summary>
433        /// <returns></returns>
434        [DataObjectMethod(DataObjectMethodType.Select, false)]
435        public virtual Dictionary<string, string> GetList()
436        {
437            int counter = 0;
438            Dictionary<string, string> res = new Dictionary<string, string>();
439            F filter = default(F); //= new F();
440            List<T> list = GetByFilter(filter, "");
441            foreach (T item in list)
442            {
443                res.Add(counter.ToString(), item.ToString());
444            }
445            return res;
446        }
447
448        [DataObjectMethod(DataObjectMethodType.Select, true)]
449        public virtual List<T> GetByFilter(F filter, string sort)
450        {
451            throw new NotImplementedException();    //to complete
452
453            //Type t = typeof(F);
454            //PropertyInfo[] props = t.GetProperties();
455
456            //DbProviderFactory myProv = Database.ProviderFactory;
457            //DbConnection myConn = myProv.CreateConnection();
458            //DbDataReader myRd = null;
459            //DbCommand myCmd = myConn.CreateCommand();
460            //string sSql;
461            //List<T> result = new List<T>();
462
463            //try
464            //{
465            //    myConn.ConnectionString = Database.ConnString;
466            //    myConn.Open();
467            //    myCmd.Connection = myConn;
468
469            //    sSql = "SELECT [" + this.KeyFieldName + "] FROM [" + this.TableName + "] t "
470            //    + " WHERE [" + this.KeyFieldName + "] > 0 ";
471
472            //    foreach (PropertyInfo prop in props)
473            //    {
474            //        DataObjectFieldAttribute[] attrs = (DataObjectFieldAttribute[])prop.GetCustomAttributes(typeof(DataObjectFieldAttribute), true);
475            //        if (attrs.Length > 0)
476            //        {
477            //            //switch (attrs[0].GetType().)
478            //            //{
479            //            //    case Type.
480            //            //    default:
481            //            //}
482            //            if (attrs[0].PrimaryKey)
483            //            {
484            //                if ((int)prop.GetValue(filter, null) > 0 || (int)prop.GetValue(filter, null) == -1)
485            //                {
486            //                    sSql += " AND ["+ prop.Name +"] = @RecordId ";
487            //                    myCmd.Parameters.Add(Database.Parameter(myProv, "RecordId", (int)prop.GetValue(filter, null)));
488            //                }
489            //            }
490            //        }
491
492            //        //if (!string.IsNullOrEmpty(filter.Nome))
493            //        //{
494            //        //    sSql += " AND t.Nome = @Nome ";
495            //        //    myCmd.Parameters.Add(Database.Parameter(myProv, "Nome", filter.Nome));
496            //        //}
497            //        //if (filter.Visible != Utility.TristateBool.NotSet)
498            //        //{
499            //        //    sSql += " AND t.Visible = @Visible ";
500            //        //    myCmd.Parameters.Add(Database.Parameter(myProv, "Visible", filter.Visible));
501            //        //} 
502            //    }
503
504            //    if (!string.IsNullOrEmpty(sort))
505            //    {
506            //        sSql += " ORDER BY " + sort;
507            //    }
508            //    else
509            //    {
510            //        sSql += " ORDER BY OrderId ";
511            //    }
512
513            //    myCmd.CommandText = Database.ParseSql(sSql);
514            //    myRd = myCmd.ExecuteReader();
515            //    while (myRd.Read())
516            //    {
517            //        T item = GetById((Kkey)myRd[this.KeyFieldName]);
518            //        result.Add(item);
519            //    }
520            //    myRd.Close();
521            //}
522            //finally
523            //{
524            //    myConn.Dispose();
525            //}
526            //return result;
527        }
528
529        [DataObjectMethod(DataObjectMethodType.Select, false)]
530        public virtual T GetByKey(Kkey id)
531        {
532            throw new NotImplementedException();
533        }
534
535        /// <summary>
536        /// update the existing record
537        /// </summary>
538        /// <param name="theObj">The obj to update</param>
539        /// <returns>number of records affected</returns>
540        [DataObjectMethod(DataObjectMethodType.Update, false)]
541        public virtual int Update(T theObj)
542        {
543            throw new NotImplementedException();
544        }
545
546        /// <summary>
547        /// Insert a new record; newObj.recordId=last+1, newObj.OrderId=last+1
548        /// </summary>
549        /// <param name="newObj">Data about the new object</param>
550        /// <returns>The new obj</returns>
551        [DataObjectMethod(DataObjectMethodType.Insert, false)]
552        public virtual T Insert(T newObj)
553        {
554            throw new NotImplementedException();
555        }
556
557        /// <summary>
558        /// Delete a record from its own table
559        /// </summary>
560        /// <param name="recordId">P Key of the record to delete</param>
561        /// <returns>Num of records deleted</returns>
562        [DataObjectMethod(DataObjectMethodType.Delete, true)]
563        public virtual int DeleteById(Kkey recordId)
564        {
565            if (string.IsNullOrEmpty(TableName) || string.IsNullOrEmpty(KeyFieldName))
566                throw new NotImplementedException("Not implemented or TableName/KeyFieldName not filled");
567
568            DbProviderFactory myProv = Database.ProviderFactory;
569            DbConnection myConn = myProv.CreateConnection();
570            DbCommand myCmd = myConn.CreateCommand();
571            string sSql;
572            int res = 0;
573
574            try
575            {
576                myConn.ConnectionString = Database.ConnString;
577                myConn.Open();
578                myCmd.Connection = myConn;
579
580                sSql = "DELETE FROM [" + this.TableName + "] WHERE [" + this.KeyFieldName + "] = @recordId ";
581                myCmd.CommandText = Database.ParseSql(sSql);
582                myCmd.Parameters.Add(Database.Parameter(myProv, "recordId", recordId));
583                res = myCmd.ExecuteNonQuery();
584            }
585            finally
586            {
587                myConn.Dispose();
588            }
589            return res;
590        }
591
592        /// <summary>
593        /// fill the obj2Fill with data in myRd
594        /// </summary>
595        /// <param name="obj2Fill"></param>
596        /// <param name="myRd"></param>
597        protected virtual void FillObject(T obj2Fill, DbDataReader myRd)
598        {
599            throw new NotImplementedException();
600        }
601
602        /// <summary>
603        /// Used during Insert method. Put the new record in the last order position
604        /// </summary>
605        /// <returns>The order position</returns>
606        protected virtual int GetNextOrderId()
607        {
608            return GetNextProgressive(this.TableName, "orderId");
609        }
610
611        /// <summary>
612        /// Used during Insert method. Put the new record in the last order position
613        /// </summary>
614        /// <returns>The order position</returns>
615        protected virtual int GetNextOrdering()
616        {
617            return GetNextProgressive(this.TableName, "ordering");
618        }
619
620        /// <summary>
621        /// Give the new record id for current class,key T,F
622        /// </summary>
623        /// <returns>The new record Id</returns>
624        protected virtual int GetNextId()
625        {
626            return GetNextProgressive(this.TableName, this.KeyFieldName);
627        }
628
629        /// <summary>
630        /// Give the new record id for current class,key T,F
631        /// </summary>
632        /// <returns>The next progressive value</returns>
633        protected int GetNextProgressive(string tableName, string fieldName)
634        {
635            DbProviderFactory myProv = Database.ProviderFactory;
636            DbConnection myConn = myProv.CreateConnection();
637            DbDataReader myRd = null;
638            DbCommand myCmd = myConn.CreateCommand();
639            string sSql;
640            int result = 0;
641
642            try
643            {
644                myConn.ConnectionString = Database.ConnString;
645                myConn.Open();
646                myCmd.Connection = myConn;
647
648                sSql = "SELECT max([" + fieldName + "]) FROM [" + tableName + "]";
649                myCmd.CommandText = Database.ParseSql(sSql);
650                myRd = myCmd.ExecuteReader();
651                if (myRd.Read())
652                {
653                    if (myRd[0] != DBNull.Value)
654                    {
655                        result = (int)myRd[0];
656                    }
657                }
658                myRd.Close();
659                result++;
660            }
661            finally
662            {
663                myConn.Dispose();
664            }
665            return result;
666        }
667
668    }
669
670
671    /// <summary>
672    /// Generic Table used to implement a BLL object
673    /// </summary>
674    public interface ITable
675    {
676    }
677
678    public interface ITableWithOrdering: ITable
679    {
680        int Ordering { get; set; }
681    }
682
683    public interface ITableWithComments : ITable
684    {
685        int CommentsGroupId { get; set; }
686    }
687
688    public interface ITableWithPermissions : ITable
689    {
690        //read
691        MenuAccesstype ReadAccessType { get; set; }
692        int ReadPermissionId { get; set; }
693        List<string> ReadRolenames { get; set; }
694        string ReadAccessCode { get; set; }
695        int ReadAccessLevel { get; set; }
696        //write
697        MenuAccesstype WriteAccessType { get; set; }
698        int WritePermissionId { get; set; }
699        List<string> WriteRolenames { get; set; }
700        string WriteAccessCode { get; set; }
701        int WriteAccessLevel { get; set; }
702    }
703
704    public interface ITableManager
705    {
706        //*** not implemented
707
708        //int DeleteById(int recordId);
709        //int Update(ITableObject theObj);
710        //ITableObject Insert(ITableObject newObj);
711        //List<ITableObject> GetByFilter();
712        //ITableObject GetById();
713    }
714
715    public interface ITableManagerWithPermission: ITable
716    {
717        bool CheckUserContext { get; }
718        bool WriteMode { get; }
719    }
720}