public DbCommand CreateDeleteCommand(string tableName, WhereClip where)
{
Check.Require(!string.IsNullOrEmpty(tableName), "tableName could not be null or empty!");
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("DELETE FROM ");
sb.Append(leftToken);
sb.Append(tableName.TrimStart(leftToken).TrimEnd(rightToken));
sb.Append(rightToken);
if ((!WhereClip.IsNullOrEmpty(where)) && where.Sql.Length > 0)
{
sb.Append(" WHERE ");
sb.Append(SqlQueryUtils.RemoveTableAliasNamePrefixes(where.Sql));
AddExpressionParameters(where, cmd);
}
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}
public WhereClip Or(WhereClip where)
{
if (WhereClip.IsNullOrEmpty(where) || where.Sql.Length == 0)
{
return(this);
}
if (sql.Length > 0)
{
sql.Append(" OR ");
}
if (where.isNot && where.sql.Length > 0)
{
sql.Append("NOT ");
}
if (where.sql.Length > 0)
{
sql.Append('(');
sql.Append(where.sql);
sql.Append(')');
}
SqlQueryUtils.AddParameters(parameters, where);
return(this);
}
/// <summary>
/// Wheres the specified where.
/// </summary>
/// <param name="where">The where.</param>
/// <returns>SelectSqlSection<T, TSchema>.</returns>
/// <remarks>
/// <para>创建：Teddy</para>
/// <para>日期：2018/3/22</para>
/// </remarks>
public SelectSqlSection <T, TSchema> Where(WhereClip where)
{
if (!WhereClip.IsNullOrEmpty(where))
{
whereClip.And(where);
}
return(this);
}
public SelectSqlSection <T> Join(TableSchema joinTable, WhereClip joinOnWhere)
{
Check.Require(joinTable != null, "joinTable could not be null.");
Check.Require(!WhereClip.IsNullOrEmpty(joinOnWhere), "joinOnWhere could not be null or empty.");
this.whereClip.From.Join(joinTable, joinOnWhere);
return(this);
}
/// <summary>
/// 连接设置
/// </summary>
/// <param name="joinTable">主表跟当前表进行连接</param>
/// <param name="joinOnWhere">连接条件</param>
/// <param name="joinType">连接类型，支持内连接、左外连接、右外连接</param>
/// <returns>SelectSqlSection<T, TSchema>.</returns>
/// <remarks><para>创建：Teddy</para>
/// <para>日期：2016-10-18</para></remarks>
public SelectSqlSection <T, TSchema> Join(TableSchema joinTable, WhereClip joinOnWhere, JoinType joinType = JoinType.INNER)
{
Check.Require(joinTable != null, "joinTable could not be null.");
Check.Require(!WhereClip.IsNullOrEmpty(joinOnWhere), "joinOnWhere could not be null or empty.");
whereClip.AddOtherPararms(joinOnWhere.Parameters);
this.whereClip.From.Join(joinTable, joinOnWhere, joinType);
return(this);
}
public DbCommand CreateUpdateCommand(string tableName, WhereClip where, string[] columns, DbType[] types, object[] values)
{
Check.Require(!string.IsNullOrEmpty(tableName), "tableName could not be null or empty!");
Check.Require(columns != null && types != null && values != null && columns.Length == types.Length && columns.Length == values.Length,
"length of columns, types and values must equal!");
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("UPDATE ");
sb.Append(leftToken);
sb.Append(tableName.TrimStart(leftToken).TrimEnd(rightToken));
sb.Append(rightToken);
sb.Append(' ');
sb.Append("SET ");
for (int i = 0; i < columns.Length; ++i)
{
if (columns[i].Trim()[0] == '[')
{
sb.Append(columns[i].Replace("[", leftToken.ToString()).Replace("]", rightToken.ToString()));
}
else
{
sb.Append(leftToken);
sb.Append(columns[i].TrimStart(leftToken).TrimEnd(rightToken));
sb.Append(rightToken);
}
sb.Append('=');
if (values[i] != null && values[i] is ExpressionClip)
{
ExpressionClip expr = (ExpressionClip)values[i];
sb.Append(expr.ToString());
AddExpressionParameters(expr, cmd);
}
else
{
string paramName = MakeUniqueParamNameWithPrefixToken();
sb.Append(paramName);
DbParameter p = cmd.CreateParameter();
p.ParameterName = paramName;
p.DbType = types[i];
p.Value = values[i] == null ? DBNull.Value : values[i];
cmd.Parameters.Add(p);
}
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
if ((!WhereClip.IsNullOrEmpty(where)) && where.Sql.Length > 0)
{
sb.Append(" WHERE ");
sb.Append(SqlQueryUtils.RemoveTableAliasNamePrefixes(where.Sql));
AddExpressionParameters(where, cmd);
}
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}