Example #1
0
        public static string UniqueName(string TableName, string ParentFieldName, string NameFieldName, string sParentNodeID, string SeedName, bool bIsInteger, string Condition)
        {
            int    i         = 0;
            string SeedNames = "";
            string LastName  = "";
            int    iNum      = 10;

            while (true)
            {
                SeedNames = "";
                for (int j = i; j < i + iNum; j++, i++)
                {
                    SeedNames += " OR {0}='" + ValidCharValue(SeedName + (i == 0 ? "" : ("(" + i + ")"))) + "'";
                }
                string            strSql = string.Format("SELECT {0} AS GName FROM {1} WHERE {2}={3} AND ({4}) {5} ORDER BY {0} ASC", new object[] { NameFieldName, TableName, ParentFieldName, bIsInteger ? sParentNodeID : ("'" + sParentNodeID + "'"), SeedNames.Substring(4), Condition });
                DataRowCollection rows   = MrDBAccess.ExecuteDataSet(strSql).Tables[0].Rows;
                if (rows.Count < iNum)
                {
                    string tempName = LastName;
                    if (rows.Count > 0)
                    {
                        tempName = DataBase.ObjectToString(rows[rows.Count - 1]["GName"]);
                    }
                    if (string.IsNullOrEmpty(tempName))
                    {
                        return(SeedName);
                    }
                    int index = tempName.LastIndexOf("(");
                    tempName = tempName.Substring(index + 1);
                    i        = DataBase.ObjectToInt(tempName.Substring(0, tempName.Length - 1));
                    return(SeedName + "(" + (i + 1) + ")");
                }
                LastName = DataBase.ObjectToString(rows[iNum - 1]["GName"]);
            }
        }
Example #2
0
        public static void UpdateIndex(string connectionString, string strID, string strNextID, string strTable, string where = "")
        {
            int               ID         = DataBase.ObjectToInt(strID);
            int               NextID     = DataBase.ObjectToInt(strNextID);
            string            strSql     = "SELECT F_ID,F_Index FROM " + strTable + " WHERE F_ID = " + ID + " OR F_ID = " + NextID;
            DataRowCollection rows       = MrDBAccess.ExecuteDataSet(strSql).Tables[0].Rows;
            int               iOldIndex1 = -1;
            int               iOldIndex2 = -1;

            for (int i = 0; i < rows.Count; i++)
            {
                if (DataBase.ObjectToString(rows[i]["F_ID"]) == ID + "")
                {
                    iOldIndex1 = DataBase.ObjectToInt(rows[i]["F_Index"]);
                }
                else if (DataBase.ObjectToString(rows[i]["F_ID"]) == NextID + "")
                {
                    iOldIndex2 = DataBase.ObjectToInt(rows[i]["F_Index"]);
                }
            }
            try
            {
                MrDBAccess.BeginTransaction(connectionString);
                strSql = "UPDATE " + strTable + " SET F_Index=" + iOldIndex2 + " WHERE F_ID=" + ID + where;
                MrDBAccess.ExecuteNonQuery(connectionString, strSql);
                strSql = "UPDATE " + strTable + " SET F_Index=" + iOldIndex1 + " WHERE F_ID=" + NextID + where;
                MrDBAccess.ExecuteNonQuery(connectionString, strSql);
                MrDBAccess.CommitTransaction();
            }
            catch
            {
                MrDBAccess.RollbackTransaction();
            }
        }
Example #3
0
 public static void RemoveTableKey(string connectionString, string key, int iNum, int iGroupNum = 50)
 {
     lock (objLock)
     {
         string            strSql = "SELECT * FROM T_SEQUENCE WHERE F_Name='" + key.Replace("'", "''") + "'";
         DataRowCollection rows   = MrDBAccess.ExecuteDataSet(connectionString, strSql).Tables[0].Rows;
         if (rows.Count > 0 && DataBase.ObjectToInt(rows[0]["F_Value"]) == iNum)
         {
             strSql = "UPDATE T_SEQUENCE SET F_Value=" + (iNum - iGroupNum) + " WHERE F_Name='" + key.Replace("'", "''") + "'";
             MrDBAccess.ExecuteNonQuery(connectionString, strSql);
         }
         if (dicTableKey.ContainsKey(key) && dicTableKey[key] == iNum)
         {
             dicTableKey[key] = iNum - iGroupNum;
         }
     }
 }
Example #4
0
        public static PageDataResult ExecuteDataSetPage(string connectionString, PageDataArg args)
        {
            try
            {
                args.SQL   = args.SQL.ToUpper();
                args.Order = args.Order.ToUpper();

                string            strSql = "";
                string            strCol = "";
                DataRowCollection rows;
                #region
                if (args.SQL.StartsWith("SELECT", StringComparison.InvariantCultureIgnoreCase))
                {
                    int index = args.SQL.IndexOf("FROM", StringComparison.InvariantCultureIgnoreCase);
                    strCol   = args.SQL.Substring(6, index - 6);
                    args.SQL = " " + args.SQL.Substring(index);
                }
                else
                {
                    strCol   = "*";
                    args.SQL = " FROM " + args.SQL;
                }
                if (args.Count < 1)
                {
                    strSql = "SELECT COUNT(1) AS Cnt" + args.SQL;
                    rows   = MrDBAccess.ExecuteDataSet(connectionString, strSql).Tables[0].Rows;
                    if (rows.Count == 0)
                    {
                        args.Count = 0;
                    }
                    else
                    {
                        args.Count = DataBase.ObjectToInt(rows[0]["Cnt"]);
                    }
                }
                if (!string.IsNullOrEmpty(args.TurnPageKey) && !string.IsNullOrEmpty(args.TurnPageValue))
                {
                    int iNextID = 0;
                    args.Page = GetPage(connectionString, args.TurnPageKey, args.SQL, strCol, args.Order, args.TurnPageValue, args.PerPage, ref iNextID);
                }
                #endregion
                args.AllPage = DataBase.ObjectToInt((args.Count / args.PerPage) + (args.Count % args.PerPage != 0 ? 1 : 0));
                if (args.Page > args.AllPage)
                {
                    args.Page = args.AllPage;
                }
                if (args.Page < 1)
                {
                    args.Page = 1;
                }
                strSql = "BEGIN SELECT IDENTITY(INT,1,1) ROWNUM," + strCol + " INTO #TEMP" + args.SQL + " ORDER BY " + args.Order + ";SELECT * FROM #TEMP WHERE ROWNUM>" + (args.PerPage * (args.Page - 1)) + " AND ROWNUM<=" + (args.PerPage * args.Page) + ";DROP TABLE #TEMP;END;";
                rows   = MrDBAccess.ExecuteDataSet(connectionString, strSql).Tables[0].Rows;
                return(new PageDataResult()
                {
                    Count = args.Count, AllPage = args.AllPage, Page = args.Page, Data = rows
                });
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
Example #5
0
 public static int GetTableKey(string connectionString, string key, int iGetCnt = 1, int iGroupNum = 50, int iStart = 10000)
 {
     lock (objLock)
     {
         bool   inDB   = true;
         string strSql = "";
         #region 查找可使用的ID
         int iValue = iStart;
         if (dicTableKey.ContainsKey(key))
         {
             iValue = dicTableKey[key];
         }
         else
         {
             strSql = "SELECT F_Value FROM T_SEQUENCE WHERE F_Name='" + key.Replace("'", "''") + "'";
             DataRowCollection rows = MrDBAccess.ExecuteDataSet(connectionString, strSql).Tables[0].Rows;
             if (rows.Count > 0)
             {
                 iValue = DataBase.ObjectToInt(rows[0]["F_Value"]);
             }
             else
             {
                 inDB = false;
             }
         }
         if (iValue < iStart)
         {
             iValue = iStart;
         }
         #endregion
         #region 保存到新内存和数据库
         if (dicTableKey.ContainsKey(key))
         {
             dicTableKey[key] = iValue + iGetCnt;
         }
         else
         {
             dicTableKey.Add(key, iValue + iGetCnt);
         }
         strSql = "";
         if (iGetCnt == 1)
         {
             if (!inDB)
             {
                 strSql = "INSERT INTO T_SEQUENCE(F_Value,F_Name) VALUES(" + (iValue + iGroupNum) + ",'" + key.Replace("'", "''") + "')";
             }
             else if ((iValue - 1) % iGroupNum == 0)
             {
                 strSql = "UPDATE T_SEQUENCE SET F_Value=" + (iValue + iGroupNum) + " WHERE F_Name='" + key.Replace("'", "''") + "'";
             }
         }
         else
         {
             if (!inDB)
             {
                 strSql = "INSERT INTO T_SEQUENCE(F_Value,F_Name) VALUES(" + (iValue + iGetCnt - 1 + iGroupNum) + ",'" + key.Replace("'", "''") + "')";
             }
             else
             {
                 strSql = "UPDATE T_SEQUENCE SET F_Value=" + (iValue + iGetCnt - 1 + iGroupNum) + " WHERE F_Name='" + key.Replace("'", "''") + "'";
             }
         }
         if (strSql != "")
         {
             MrDBAccess.ExecuteNonQuery(connectionString, strSql);
         }
         #endregion
         return(iValue);
     }
 }