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"]);
}
}
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();
}
}
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;
}
}
}
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);
}
}
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);
}
}