public static int AddORUpdateByHashtable(Hashtable hst, string TableName, string UpdateCon)
{
string str = "";
string sql = "";
bool IsUpdate = false;
if (!string.IsNullOrWhiteSpace(UpdateCon))
{
IsUpdate = true;
}
List <Field> FieldList = ReadDataColumnSet.GetTableFieldByTableName(TableName);
if (FieldList.Count == 0)
{
return(-1);
}
StringBuilder sbField = new StringBuilder();
StringBuilder sbValue = new StringBuilder();
foreach (DictionaryEntry de in hst)
{
string field = de.Key.ToString();
if (string.IsNullOrWhiteSpace(field))
{
continue;
}
string valueStr = "";
Field f = FieldList.FirstOrDefault(p => p.Name.Equals(field));
if (f == null)
{
continue;
}
int t = ReadDataColumnSet.getType(f.DataType);
if (de.Value == null)
{
valueStr = " null ";
}
else
{
valueStr = de.Value.ToString();
if (t == 0 || t == -1 || string.IsNullOrWhiteSpace(valueStr))
{
valueStr = "'" + valueStr + "'";
}
}
if (IsUpdate)
{
if (sbValue.Length == 0)
{
sbValue.Append(" SET ");
}
else
{
sbValue.Append(",");
}
sbValue.Append(field + "=" + valueStr);
}
else
{
if (sbField.Length == 0)
{
sbField.Append(" ( ");
}
else
{
sbField.Append(",");
}
sbField.Append(field);
if (sbValue.Length == 0)
{
sbValue.Append("VALUES (");
}
else
{
sbValue.Append(",");
}
sbValue.Append(valueStr);
}
}
if (IsUpdate)
{
str = " UPDATE " + TableName;
sql = str + sbValue.ToString() + " " + " where " + UpdateCon;
}
else
{
str = " INSERT INTO " + TableName;
sql = str + sbField.ToString() + " ) " + sbValue.ToString() + " ) ";
}
string[] changePassWord = Common.getChangePassWord();
return(Resource.ExecuteSql(sql, changePassWord[0], changePassWord[1], changePassWord[2]));
}
/// <summary>
/// 获取保存表的Hashtable列表（JSON数据）
/// </summary>
/// <param name="JArray"></param>
/// <param name="SaveTableResid"></param>
/// <param name="SaveTableName"></param>
/// <param name="UserID"></param>
/// <param name="UseFieldID"></param>
/// <param name="SaveSameTable"></param>
/// <param name="CreateNewHst"></param>
/// <param name="CreateNewID"></param>
/// <returns></returns>
public static List <Hashtable> CommonGetHstListByJArray(string JArray, string SaveTableResid, string SaveTableName, string UserID, bool UseFieldID = true, bool SaveSameTable = true, bool CreateNewHst = true, bool CreateNewID = true)
{
List <Hashtable> hstList = new List <Hashtable>();
Newtonsoft.Json.Linq.JArray jsonVal = Newtonsoft.Json.Linq.JArray.Parse(JArray) as Newtonsoft.Json.Linq.JArray;
if (jsonVal.Count == 0)
{
return(hstList);
}
List <ReadDataColumnSet> AllReadDataColumnSet = new List <ReadDataColumnSet>();
bool IsResidTable = false;
if (!string.IsNullOrWhiteSpace(SaveTableResid))
{
IsResidTable = true;
}
for (int i = 0; i < jsonVal.Count; i++)
{
string json = Newtonsoft.Json.JsonConvert.SerializeObject(jsonVal[i]);
DataTable jsonDT = JsonConvert.DeserializeObject <DataTable>(json);
Hashtable hst = new Hashtable();
if (IsResidTable && CreateNewHst)
{
hst = GetNewHst(CreateNewID, SaveTableResid, UserID);
}
for (int j = 0; j < jsonDT.Columns.Count; j++)
{
if (AllReadDataColumnSet.Count == 0 || SaveSameTable)
{
if (j == 0)
{
List <Field> _ResIDField = new List <Field>();
if (IsResidTable)
{
_ResIDField = Resource.GetFieldList(SaveTableResid).ToList();
}
else
{
_ResIDField = ReadDataColumnSet.GetTableFieldByTableName(SaveTableName);
}
AllReadDataColumnSet = new List <ReadDataColumnSet>();
if (_ResIDField.Count == 0)
{
return(null);
}
string[] noNeedColumn = { "RESID", "RELID", "CRTID", "CRTTIME", "EDTID", "EDTTIME" };
for (int c = 0; c < jsonDT.Columns.Count; c++)
{
ReadDataColumnSet ColumnSet = new ReadDataColumnSet();
ColumnSet.FieldName = jsonDT.Columns[c].ColumnName;
ColumnSet.ReadColumnName = jsonDT.Columns[c].ColumnName;
Field f = _ResIDField.FirstOrDefault(p => (p.Name.Equals(ColumnSet.FieldName) && !UseFieldID) || (UseFieldID && p.Description.Equals(ColumnSet.FieldName)));
if (f != null && !noNeedColumn.Contains(f.Name.ToUpper()))
{
ColumnSet.FieldId = f.Name;
if (AllReadDataColumnSet.Find(p => p.FieldId.Equals(f.Name)) == null)
{
AllReadDataColumnSet.Add(ColumnSet);
}
}
}
}
}
}
for (int c = 0; c < jsonDT.Columns.Count; c++)
{
ReadDataColumnSet ColumnSet = AllReadDataColumnSet.FirstOrDefault(p => p.ReadColumnName.Equals(jsonDT.Columns[c].ColumnName));
if (ColumnSet != null)
{
hst.Add(ColumnSet.FieldId, jsonDT.Rows[0][c]);
}
}
hstList.Add(hst);
}
return(hstList);
}
public static string GetALLCommonSearchField(string argResourcesId, string argReportKey, string argSearchType, string argIsDynamicallyCreatTableHead, bool argAutomaticFilteringColumn = true, bool argIsUsedFieldNameQuery = false, bool argIsSearchQuery = false)
{
List <Field> FieldList = new List <Field>();
string str = "";
if (string.IsNullOrWhiteSpace(argIsDynamicallyCreatTableHead))
{
FieldList = GetSearchFieldList(argResourcesId, argReportKey);
}
if (argIsSearchQuery)
{
dynamic ReturnFieldList = FieldList.Select(p => new { FieldId = p.Name, FieldName = p.Description, FieldType = ReadDataColumnSet.getType(p.DataType) });
str = Newtonsoft.Json.JsonConvert.SerializeObject(ReturnFieldList);
}
else
{
dynamic ReturnFieldList = FieldList.Select(p => new { FieldId = p.Name, FieldName = p.Description, FieldType = p.DataType });
str = Newtonsoft.Json.JsonConvert.SerializeObject(ReturnFieldList);
}
return(str + "[#]" + GetCombobox_Data(null, "OP", "", argIsUsedFieldNameQuery) + "[#]" + GetCombobox_Data(FieldList, "field", "", argIsUsedFieldNameQuery));
}
