protected virtual string CreateUpdateSql(ObjectData od, DynamicParameters sqlList)//增加参数sqlList，参数化查询
{
//if(od.MODEFY_COLUMNS.)
string cols = string.Join(",", od.MODEFY_COLUMNS);
string sql = "UPDATE {0} SET {1} WHERE {2} = '{3}' ";
List <string> vals = new List <string>();
//List<string> colNames = new List<string>();
bool isNull = true;
string key = "";
foreach (string col in od.MODEFY_COLUMNS)
{
if (FullColumns.FirstOrDefault(a => a.Name == col && a.Kind == ColumnKind.Data) != null)
{
string val = od.Row[col].ToString();
if (col != PrimaryKey)
{
string temp = val;
val = string.Format(CultureInfo.CurrentCulture, "{0} = @{0} ", col);
sqlList.Add(string.Format("@{0}", col), temp);
vals.Add(val);
//colNames.Add(col);
isNull = false;
}
else
{
key = val;
}
}
}
foreach (ColumnConfig column in FullColumns.Where(a => a.Kind == ColumnKind.Data))
{
string mod = column.Name;
if (!od.MODEFY_COLUMNS.Contains(mod))
{
if (mod == "UPDATE_TIME")
{
string val2 = string.Format(CultureInfo.CurrentCulture, " UPDATE_TIME = @{0} ", mod);
sqlList.Add(string.Format("@{0}", mod), DbContext.Now.ToString());
vals.Add(val2);
isNull = false;
}
if (mod == "UPDATE_ID")
{
string val2 = string.Format(CultureInfo.CurrentCulture, " UPDATE_ID = @{0} ", mod);
sqlList.Add(string.Format("@{0}", mod), ""); //当前用户id
vals.Add(val2);
isNull = false;
}
}
}
if (isNull)
{
return("");
}
sql = string.Format(CultureInfo.CurrentCulture, sql, RegName, string.Join(",", vals), PrimaryKey, key);
return(sql);
}
protected virtual string CreateInsertSql(ObjectData od, DynamicParameters sqlList, string key)//增加参数sqlList，参数化查询
{
string paraName = "";
HashSet <string> _modefyColumns = od.MODEFY_COLUMNS;
if (!_modefyColumns.Contains(PrimaryKey))
{
_modefyColumns.Add(PrimaryKey);
}
//added by sj
if (!ForeignKey.IsEmpty())
{
_modefyColumns.Add(ForeignKey);
}
string sql = "INSERT INTO {0} ({1}) VALUES ({2})";
List <string> vals = new List <string>();
List <string> colNames = new List <string>();
bool hasPriKey = false;
bool isNull = true;
foreach (string col in _modefyColumns)
{
if (FullColumns.FirstOrDefault(a => a.Name == col && a.Kind == ColumnKind.Data) != null)
{
string val = "";
if (col == PrimaryKey)
{
//Debug.AssertArgument(!hasPriKey, col, string.Format(CultureInfo.CurrentCulture, "表{0}主键只能有一个", RegName), this);
hasPriKey = true;
val = key;
// FileID = "";
//added by sj 保存主表主键，当有它子表时作为外键
if (this.PostDataSet.Tables[PAGE_SYS] != null && ForeignKey.IsEmpty())
{
this.PostDataSet.Tables[PAGE_SYS].Rows[0][KEYVALUE] = val;
}
}
else if (col == ForeignKey)
{
val = ForeignKeyValue;
}
else
{
val = od.Row[col].ToString();
}
paraName = "@" + col;
sqlList.Add(paraName, val);
vals.Add(paraName);
colNames.Add(col);
isNull = false;
}
}
string cols = string.Join(",", colNames);
foreach (ColumnConfig column in FullColumns.Where(a => a.Kind == ColumnKind.Data))
{
string mod = column.Name;
if (!_modefyColumns.Contains(mod))
{
if (mod == "CREATE_TIME" || mod == "UPDATE_TIME")
{
cols += "," + mod;
paraName = "@" + mod;
sqlList.Add(paraName, DbContext.Now);
vals.Add(paraName);
isNull = false;
}
if (mod == "CREATE_ID" || mod == "UPDATE_ID")
{
cols += "," + mod;
paraName = "@" + mod;
sqlList.Add(paraName, "");//当前用户id
vals.Add(paraName);
isNull = false;
}
}
}
if (isNull)
{
return("");
}
sql = string.Format(CultureInfo.CurrentCulture, sql, RegName, cols, string.Join(",", vals));
return(sql);
}
