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 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);
}
}