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