コード例 #1
0
        public string UPDATEQuery(Hashtable pHT)
        {
            StringBuilder vUpdateQuery = new StringBuilder();

            vUpdateQuery = CRUDHelper.BuildUPDATEQuery(pHT, null);
            return(vUpdateQuery.ToString());
        }
コード例 #2
0
        public string DELETEQuery(Hashtable pHT, bool pMarkDEL, Hashtable pMarkDELColNameValues)
        {
            StringBuilder vDeleteQuery = new StringBuilder();

            vDeleteQuery = CRUDHelper.BuildDELETEQuery(pHT, pMarkDEL, pMarkDELColNameValues);
            return(vDeleteQuery.ToString());
        }
コード例 #3
0
        public string DELETEQuery(object pModel, bool pMarkDEL, Hashtable pMarkDELColNameValues)
        {
            Hashtable     vHT          = CRUDHelper.GetColumnValueCol(pModel);
            StringBuilder vDeleteQuery = new StringBuilder();

            vDeleteQuery = CRUDHelper.BuildDELETEQuery(vHT, pMarkDEL, pMarkDELColNameValues);
            return(vDeleteQuery.ToString());
        }
コード例 #4
0
        public string UPDATEQuery(object pModel, ArrayList pExcludeList)
        {
            //Taking the model object in hashtable
            Hashtable     vHT          = CRUDHelper.GetColumnValueCol(pModel);
            StringBuilder vUpdateQuery = new StringBuilder();

            vUpdateQuery = CRUDHelper.BuildUPDATEQuery(vHT, pExcludeList);
            return(vUpdateQuery.ToString());
        }
コード例 #5
0
        public string READQuery(Hashtable pHT, bool pAll)
        {
            //Start to create the query
            StringBuilder vReadQuery = new StringBuilder("SELECT ");

            vReadQuery = CRUDHelper.BuildREADQuery(pHT, vReadQuery, pAll);

            return(vReadQuery.ToString());
        }
コード例 #6
0
        public string READQuery(object pModel, bool pAll)
        {
            //Start to create the query
            StringBuilder vReadQuery = new StringBuilder("SELECT ");
            Hashtable     vHT        = CRUDHelper.GetColumnValueCol(pModel);

            vReadQuery = CRUDHelper.BuildREADQuery(vHT, vReadQuery, pAll);

            return(vReadQuery.ToString());
        }
コード例 #7
0
 public string CREATEQuery(Hashtable pHT)
 {
     if (pHT == null)
     {
         throw new ArgumentNullException("HashTable pHT contains no value.");
     }
     else if (pHT.Count < 0)
     {
         throw new ArgumentNullException("HashTable pHT contains 0 elements.");
     }
     return(CRUDHelper.BuildCREATEQuery(pHT, null).ToString());
 }
コード例 #8
0
        public string UPDATEQuery(Hashtable pHT, Hashtable pFilteredColumns)
        {
            StringBuilder vUpdateQuery = new StringBuilder();

            vUpdateQuery = CRUDHelper.BuildUPDATEQuery(pHT, null);

            //Adding additional filter
            foreach (DictionaryEntry de in pFilteredColumns)
            {
                vUpdateQuery.Append(" AND ");
                vUpdateQuery.Append(de.Key.ToString());
                vUpdateQuery.Append(" = ");
                vUpdateQuery.Append(CRUDHelper.GetFormatedColumnValue(de.Value));
            }
            return(vUpdateQuery.ToString());
        }
コード例 #9
0
        public string READQuery(Hashtable pHT, Hashtable pFilteredColumns)
        {
            //Start to create the query
            StringBuilder vReadQuery = new StringBuilder("SELECT ");

            //Build the READ query with PK
            vReadQuery = CRUDHelper.BuildREADQuery(pHT, vReadQuery, false);
            //Adding additional filter
            foreach (DictionaryEntry de in pFilteredColumns)
            {
                vReadQuery.Append(" AND ");
                vReadQuery.Append(de.Key.ToString());
                vReadQuery.Append(" = ");
                vReadQuery.Append(CRUDHelper.GetFormatedColumnValue(de.Value));
            }
            return(vReadQuery.ToString());
        }
コード例 #10
0
        public string UPDATEQuery(object pModel, Hashtable pFilteredColumns, ArrayList pExcludeList)
        {
            //Taking the model object in hashtable
            Hashtable     vHT          = CRUDHelper.GetColumnValueCol(pModel);
            StringBuilder vUpdateQuery = new StringBuilder();

            vUpdateQuery = CRUDHelper.BuildUPDATEQuery(vHT, pExcludeList);

            //Adding additional filter
            foreach (DictionaryEntry de in pFilteredColumns)
            {
                vUpdateQuery.Append(" AND ");
                vUpdateQuery.Append(de.Key.ToString());
                vUpdateQuery.Append(" = ");
                vUpdateQuery.Append(CRUDHelper.GetFormatedColumnValue(de.Value));
            }
            return(vUpdateQuery.ToString());
        }
コード例 #11
0
        public string READQuery(Hashtable pHT, bool pAll, bool pOrderBy, string[] pOrderColumns, bool pASC)
        {
            //Start to create the query
            StringBuilder vReadQuery = new StringBuilder("SELECT ");

            vReadQuery = CRUDHelper.BuildREADQuery(pHT, vReadQuery, pAll);
            if (pOrderBy)//Adding Order By
            {
                vReadQuery.Append(CRUDHelper.CreateOrderBy(pOrderColumns));
                if (pASC)
                {
                    vReadQuery.Append(" ASC ");
                }
                else
                {
                    vReadQuery.Append(" DESC ");
                }
            }
            return(vReadQuery.ToString());
        }
コード例 #12
0
ファイル: CRUDHelper.cs プロジェクト: ParamaBiswas/Hnm
        internal static StringBuilder BuildDELETEQuery(Hashtable pHT, bool pMarkDEL, Hashtable pMarkDELColNameValues)
        {
            StringBuilder vDeleteQuery = new StringBuilder();
            //This will add the Column values
            StringBuilder vWhere      = new StringBuilder(" WHERE ");
            string        vTableName  = "";
            string        vColumnName = "";

            bool vPK  = false;
            bool vTBL = false;

            foreach (DictionaryEntry de in pHT)
            {
                vColumnName = de.Key.ToString();
                if (vColumnName.Contains("_TBL"))//Property with _TBL contains table name
                {
                    vTBL = true;
                    //Getting table name
                    vTableName = de.Value.ToString();
                }
                else if (vColumnName.Contains("_PK")) //Property with _PK contains primary column name
                {
                    vPK = true;                       //Setting true to ensure that where clause will be applied
                    //Removing _PK from the property name.
                    vColumnName = CRUDHelper.RemoveSuffix(vColumnName, "_PK");
                    //Generating where clause
                    vWhere.Append(vColumnName);
                    vWhere.Append(" = ");

                    vWhere.Append(CRUDHelper.GetFormatedColumnValue(de.Value));
                    vWhere.Append(" AND ");
                }
            }//End of foreach (PropertyInfo pi in vPI)

            if (vPK)
            {
                vWhere.Remove(vWhere.Length - 5, 5); //Removing last ' AND ' from where clause
            }
            if (!pMarkDEL)                           //pMarkDEL is false ie. DELETE FROM DB
            {
                vDeleteQuery.Append("DELETE FROM "); //Start to create the query
                //Adding table name
                vDeleteQuery.Append(vTableName);
            }
            else// Dont Delete from DB, rather mark delete
            {
                vDeleteQuery.Append("UPDATE  ");
                vDeleteQuery.Append(vTableName);//Adding table name
                vDeleteQuery.Append(" SET ");
                foreach (DictionaryEntry de in pMarkDELColNameValues)
                {
                    if (de.Key.ToString().ToUpper() == "ACTIONDATE")
                    {
                        vDeleteQuery.Append(de.Key.ToString());
                        vDeleteQuery.Append(" = ");
                        vDeleteQuery.Append("getdate()");
                        vDeleteQuery.Append(" ,");
                    }
                    else
                    {
                        vDeleteQuery.Append(de.Key.ToString());
                        vDeleteQuery.Append(" = '");
                        vDeleteQuery.Append(de.Value.ToString());
                        vDeleteQuery.Append("' ,");
                    }
                }
                vDeleteQuery.Remove(vDeleteQuery.Length - 1, 1);//Removing last comma
            }

            if (vPK)                                    //If there is PK then need to add where clause
            {
                vDeleteQuery.Append(vWhere.ToString()); //Adding where clause
            }
            if (!vPK)
            {
                throw new ApplicationException("Error in LS_CRUD.CRUDBuilder.DELETEQuery(...)! Primary Key property for the parameter model is not found.");
            }
            if (!vTBL)
            {
                throw new ApplicationException("Error in LS_CRUD.CRUDBuilder.DELETEQuery(...)! Table Name property for the parameter model is not found.");
            }
            return(vDeleteQuery);
        }
コード例 #13
0
ファイル: CRUDHelper.cs プロジェクト: ParamaBiswas/Hnm
        internal static StringBuilder BuildUPDATEQuery(Hashtable pHT, ArrayList pExcludeList)
        {
            //test code starts here
            //Start to create the query
            StringBuilder vUpdateQuery = new StringBuilder("UPDATE ");
            //This will add the Column names
            StringBuilder vUpdate = new StringBuilder(" SET ");
            //This will add the Column values
            StringBuilder vWhere      = new StringBuilder(" WHERE ");
            string        vTableName  = "";
            string        vColumnName = "";
            bool          vPK         = false;
            bool          vTBL        = false;

            foreach (DictionaryEntry de in pHT)
            {
                vColumnName = de.Key.ToString();
                //Items in the exclude list need required in the SQL
                if (pExcludeList != null)
                {
                    if (pExcludeList.Contains(vColumnName))
                    {
                        continue;
                    }
                }
                if (CRUDHelper.CheckFilteredColumn(vColumnName) && !vColumnName.Contains("_PK"))
                {
                    if (vColumnName.Contains("_FK"))
                    {
                        vColumnName = CRUDHelper.RemoveSuffix(vColumnName, "_FK");
                    }

                    //Adding column name followed by '= column values'
                    vUpdate.Append(vColumnName);
                    vUpdate.Append(" = ");
                    if (vColumnName.ToUpper() == "ACTIONDATE")
                    {
                        vUpdate.Append("Getdate()");
                    }
                    else
                    {
                        vUpdate.Append(CRUDHelper.GetFormatedColumnValue(de.Value));
                    }

                    vUpdate.Append(",");

                    if (vColumnName.Contains("CompanyCode"))
                    {
                        //Generating where clause with companycode consulting with Anam vi
                        vWhere.Append("CompanyCode");
                        vWhere.Append(" = ");

                        vWhere.Append(CRUDHelper.GetFormatedColumnValue(de.Value));
                        vWhere.Append(" AND ");
                    }
                }
                else if (vColumnName.Contains("_TBL"))//Property with _TBL contains table name
                {
                    vTBL = true;
                    //Getting table name
                    vTableName = de.Value.ToString();
                }
                else if (vColumnName.Contains("_PK")) //Property with _PK contains primary column name
                {
                    vPK = true;                       //Setting true to ensure that where clause will be applied
                    //Removing _PK from the property name.
                    vColumnName = CRUDHelper.RemoveSuffix(vColumnName, "_PK");
                    //Generating where clause
                    vWhere.Append(vColumnName);
                    vWhere.Append(" = ");

                    vWhere.Append(CRUDHelper.GetFormatedColumnValue(de.Value));
                    vWhere.Append(" AND ");
                }
            }//End of foreach (PropertyInfo pi in vPI)

            vUpdate.Remove(vUpdate.Length - 1, 1);//Removing last comma
            if (vPK)
            {
                vWhere.Remove(vWhere.Length - 5, 5);//Removing last ' AND ' from where clause
            }
            //Adding table name
            vUpdateQuery.Append(vTableName);
            //Adding update values
            vUpdateQuery.Append(vUpdate.ToString());
            if (vPK)
            {
                //Adding where clause
                vUpdateQuery.Append(vWhere.ToString());
            }

            if (!vPK)
            {
                throw new ApplicationException("Error in LS_CRUD.CRUDBuilder.UPDATEQuery(...)! Primary Key property for the parameter model is not found.");
            }
            else if (!vTBL)
            {
                throw new ApplicationException("Error in LS_CRUD.CRUDBuilder.UPDATEQuery(...)! Table Name property for the parameter model is not found.");
            }
            else
            {
                return(vUpdateQuery);
            }
        }
コード例 #14
0
ファイル: CRUDHelper.cs プロジェクト: ParamaBiswas/Hnm
        internal static StringBuilder BuildCREATEQuery(Hashtable pHT, ArrayList pExcludeList)
        {
            //Start to create the query
            StringBuilder vCreateQuery = new StringBuilder("INSERT INTO ");
            //This will add the Column names
            StringBuilder vInsert = new StringBuilder("(");
            //This will add the Column values
            StringBuilder vValues     = new StringBuilder("VALUES(");
            string        vTableName  = "";
            string        vColumnName = "";
            bool          vTBL        = false;


            //PropertyInfo[] vPI = pModel.GetType().GetProperties();

            //foreach (PropertyInfo pi in vPI)
            foreach (DictionaryEntry de in pHT)
            {
                vColumnName = de.Key.ToString();
                //For INSERT query it is not necessary to build null or empty values in the INSERT SQL.
                //Items in the exclude list need required in the SQL
                if (de.Value == null)
                {
                    continue;
                }
                if (de.Value.ToString() == "")
                {
                    continue;
                }
                if (pExcludeList != null)
                {
                    if (pExcludeList.Contains(vColumnName))
                    {
                        continue;
                    }
                }

                if (CheckFilteredColumn(vColumnName))
                {
                    if (vColumnName.Contains("_PK"))
                    {
                        vColumnName = CRUDHelper.RemoveSuffix(vColumnName, "_PK");
                    }
                    else if (vColumnName.Contains("_FK"))
                    {
                        vColumnName = CRUDHelper.RemoveSuffix(vColumnName, "_FK");
                    }

                    //Adding column name with comma
                    vInsert.Append(vColumnName);
                    vInsert.Append(",");
                    //Adding column values with comma
                    string vColumnValue = CRUDHelper.GetFormatedColumnValue(de.Value);
                    if (vColumnName.ToUpper() == "ACTIONDATE")
                    {
                        vValues.Append("getdate()");
                    }
                    else
                    {
                        vValues.Append(vColumnValue);
                    }

                    //vValues.Append(CRUDHelper.GetFormatedColumnValue(pi, pModel, ""));
                    vValues.Append(",");
                }
                else if (de.Key.ToString().Contains("_TBL"))//Property with _TBL contains table name
                {
                    vTBL = true;
                    //Getting table name
                    vTableName = de.Value.ToString();
                }
            }

            vInsert.Remove(vInsert.Length - 1, 1); //Removing last comma
            vValues.Remove(vValues.Length - 1, 1); //Removing last comma

            vInsert.Append(")");                   //Adding ) after last column name
            vValues.Append(")");                   //Adding ) after last column values
            //Adding table name
            vCreateQuery.Append(vTableName);
            //Adding column names
            vCreateQuery.Append(vInsert.ToString());
            //Adding column values
            vCreateQuery.Append(vValues.ToString());

            if (!vTBL)
            {
                throw new ApplicationException("Error in LS_CRUD.CRUDBuilder.CREATEQuery(...)! Table Name property for the parameter model is not found.");
            }
            return(vCreateQuery);
        }
コード例 #15
0
ファイル: CRUDHelper.cs プロジェクト: ParamaBiswas/Hnm
        internal static StringBuilder BuildREADQuery(Hashtable pHT, StringBuilder pReadQuery, bool pAll)
        {
            //This will add the select column names
            StringBuilder vRead = new StringBuilder();
            //This will add the Column values
            StringBuilder vWhere      = new StringBuilder(" WHERE ");
            string        vTableName  = "";
            string        vColumnName = "";
            bool          vPK         = false;
            bool          vTBL        = false;

            //PropertyInfo[] vPI = pModel.GetType().GetProperties();
            foreach (DictionaryEntry de in pHT)
            //foreach (PropertyInfo pi in vPI)
            {
                //vColumnName = pi.Name;
                vColumnName = de.Key.ToString();
                if (CheckFilteredColumn(vColumnName))
                {
                    if (vColumnName.Contains("_FK"))
                    {
                        vColumnName = CRUDHelper.RemoveSuffix(vColumnName, "_FK");
                        vRead.Append(vColumnName);
                        vRead.Append(",");
                    }
                    //Adding column name followed by comma
                    else if (vColumnName.Contains("_PK")) //Property with _PK contains primary column name
                    {
                        vPK = true;                       //Setting true to ensure that where clause will be applied
                        //Removing _PK from the property name.
                        vColumnName = CRUDHelper.RemoveSuffix(vColumnName, "_PK");
                        vRead.Append(vColumnName);
                        vRead.Append(",");
                        //Generating where clause
                        vWhere.Append(vColumnName);
                        vWhere.Append(" = ");

                        string vColumnValue = CRUDHelper.GetFormatedColumnValue(de.Value);
                        vWhere.Append(vColumnValue);
                        vWhere.Append(" AND ");
                    }
                    else
                    {
                        vRead.Append(vColumnName);
                        vRead.Append(",");
                    }
                }
                else if (vColumnName.Contains("_TBL"))//Property with _TBL contains table name
                {
                    vTBL = true;
                    //Getting table name
                    vTableName = de.Value.ToString();
                }
            }//foreach(DictionaryEntry de in vHT)

            if (!vPK)
            {
                throw new ApplicationException("Error in LS_CRUD.CRUDBuilder! Primary Key property for the parameter model is not found.");
            }
            else if (!vTBL)
            {
                throw new ApplicationException("Error in LS_CRUD.CRUDBuilder! Table Name property for the parameter model is not found.");
            }

            vRead.Remove(vRead.Length - 1, 1);//Removing last comma
            //Adding FROM keyword
            vRead.Append(" FROM ");
            if (vPK)
            {
                vWhere.Remove(vWhere.Length - 5, 5);//Removing last ' AND ' from where clause
            }
            //Adding select column names
            pReadQuery.Append(vRead.ToString());
            //Adding table name
            pReadQuery.Append(vTableName);
            //If there is primary key and pAll is false then load by PK otherwise load all data...no where clause..
            if (vPK == true && pAll == false)
            {
                pReadQuery.Append(vWhere.ToString());//Adding where clause
            }
            return(pReadQuery);
        }
コード例 #16
0
        public string CREATEQuery(object pModel)
        {
            Hashtable vHT = CRUDHelper.GetColumnValueCol(pModel);

            return(CRUDHelper.BuildCREATEQuery(vHT, null).ToString());
        }
コード例 #17
0
        public string CREATEQuery(object pModel, ArrayList pExcludeList)
        {
            Hashtable vHT = CRUDHelper.GetColumnValueCol(pModel);

            return(CRUDHelper.BuildCREATEQuery(vHT, pExcludeList).ToString());
        }