/// <summary> /// 生成一条Update SQL语句 /// </summary> /// <param name="condition">不带Where关键字的条件,如果条件字符串中含有参数标记,则必须设置parameterValues数组的值</param> /// <param name="mapTable">DataFieldMap:键[key]为字段名;值[value]为字段对应的值</param> /// <param name="targetTable">需要操作的目标表名称</param> /// <param name="parameterValues">SQL参数对应值的集合,如果条件字符串中含有参数标记,则必须设置该数组的值</param> /// <returns>更新记录的条数</returns> protected virtual string GenerateUpdateSql(string condition, DataFieldMapTable mapTable, string targetTable, params object[] parameterValues) { if (mapTable == null || mapTable.Count == 0) { throw new ArgumentException("数据映射对象表为空或没有值", "mapTable"); } StringBuilder setValues = new StringBuilder(); List <OracleParameter> sqlParameters = null; foreach (string key in mapTable.Keys) { setValues.AppendFormat(string.Format("{0} = '{1}',", key, mapTable[key])); } if (parameterValues != null && parameterValues.Length > 0) { sqlParameters = new List <OracleParameter>(parameterValues.Length); foreach (OracleParameter parameter in this.FillParameters(parameterValues)) { sqlParameters.Add(parameter); } } string sqlCmd = string.Format("UPDATE {0} SET {1} WHERE {2} ", targetTable, setValues.ToString().TrimEnd(','), condition); return(sqlCmd); }
/// <summary> /// 生成一条INSERT SQL语句 /// </summary> /// <param name="mapTable">mapTable:键[key]为字段名;值[value]为字段对应的值</param> /// <param name="targetTable">需要操作的目标表名称</param> /// <returns>返回生成的INSERT SQL语句</returns> protected string GenerateInsertSql(DataFieldMapTable mapTable, string targetTable) { if (mapTable == null || mapTable.Count == 0) { throw new ArgumentException("数据映射对象表为空或没有值", "mapTable"); } StringBuilder fields = new StringBuilder(); StringBuilder values = new StringBuilder(); foreach (string key in mapTable.Keys) { fields.AppendFormat("{0},", key); values.AppendFormat("'{0}',", mapTable[key]); } string commandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", targetTable, fields.ToString().TrimEnd(','), values.ToString().TrimEnd(',')); return(commandText); }
/// <summary> /// 生成一条INSERT SQLExpression /// </summary> /// <param name="mapTable">mapTable:键[key]为字段名;值[value]为字段对应的值</param> /// <param name="targetTable">需要操作的目标表名称</param> /// <returns>返回生成的INSERT SQL语句</returns> protected SqlExpression GenerateInsertSqlExpression(DataFieldMapTable mapTable, string targetTable) { if (mapTable == null || mapTable.Count == 0) { throw new ArgumentException("数据映射对象表为空或没有值", "mapTable"); } StringBuilder fields = new StringBuilder(); StringBuilder values = new StringBuilder(); List <OracleParameter> sqlParameters = new List <OracleParameter>(mapTable.Count); foreach (string key in mapTable.Keys) { fields.AppendFormat("{0},", key); values.AppendFormat(":{0},", key); sqlParameters.Add(new OracleParameter(":" + key, mapTable[key])); } string commandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", targetTable, fields.ToString().TrimEnd(','), values.ToString().TrimEnd(',')); return(new SqlExpression(commandText, sqlParameters.ToArray())); }
/// <summary> /// 生成一条Update SQLExpression /// </summary> /// <param name="condition">不带Where关键字的条件,如果条件字符串中含有参数标记,则必须设置parameterValues数组的值</param> /// <param name="mapTable">DataFieldMap:键[key]为字段名;值[value]为字段对应的值</param> /// <param name="targetTable">需要操作的目标表名称</param> /// <param name="parameterValues">SQL参数对应值的集合,如果条件字符串中含有参数标记,则必须设置该数组的值</param> /// <returns>更新记录的条数</returns> protected virtual SqlExpression GenerateUpdateSqlExpression(string condition, DataFieldMapTable mapTable, string targetTable, params object[] parameterValues) { if (mapTable == null || mapTable.Count == 0) { throw new ArgumentException("数据映射对象表为空或没有值", "mapTable"); } StringBuilder setValues = new StringBuilder(); List <OleDbParameter> sqlParameters = new List <OleDbParameter>(mapTable.Count); foreach (string key in mapTable.Keys) { setValues.AppendFormat(string.Format("[{0}] = @{0},", key)); sqlParameters.Add(new OleDbParameter("@" + key, mapTable[key])); } if (parameterValues != null && parameterValues.Length > 0) { foreach (OleDbParameter parameter in this.FillParameters(parameterValues)) { sqlParameters.Add(parameter); } } string commandText = string.Format("UPDATE [{0}] SET {1} WHERE {2} ", targetTable, setValues.ToString().TrimEnd(','), condition); return(new SqlExpression(commandText, sqlParameters.ToArray())); }