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