public DbCommand CreateSelectCommand(WhereClip where, string[] columns)
{
Check.Require(((object)where) != null && where.From != null, "expr and expr.From could not be null!");
Check.Require(columns != null && columns.Length > 0, "columns could not be null or empty!");
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("SELECT ");
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(" FROM ");
sb.Append(where.ToString());
AddExpressionParameters(where, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}
public DbCommand CreateDeleteCommand(string tableName, WhereClip where)
{
Check.Require(!string.IsNullOrEmpty(tableName), "tableName could not be null or empty!");
//if (where != null)
//{
// Check.Require(where.From == null, "expr.From must be null in Delete");
//}
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 DbCommand CreateInsertCommand(string tableName, string[] columns, DbType[] types, object[] values)
{
Check.Require(!string.IsNullOrEmpty(tableName), "tableName could not be null or empty!");
Check.Require(columns == null ||
(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("INSERT INTO ");
sb.Append(leftToken);
sb.Append(tableName.TrimStart(leftToken).TrimEnd(rightToken));
sb.Append(rightToken);
sb.Append(' ');
if (columns == null || columns.Length == 0)
{
sb.Append("DEFAULT VALUES");
}
else
{
sb.Append('(');
for (int i = 0; i < columns.Length; ++i)
{
sb.Append(leftToken);
sb.Append(columns[i].TrimStart(leftToken).TrimEnd(rightToken));
sb.Append(rightToken);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(") VALUES (");
for (int i = 0; i < columns.Length; ++i)
{
string paramName = MakeUniqueParamNameWithPrefixToken();
sb.Append(paramName);
if (i < columns.Length - 1)
{
sb.Append(',');
}
DbParameter p = cmd.CreateParameter();
p.ParameterName = paramName;
p.DbType = types[i];
p.Value = values[i] == null ? DBNull.Value : values[i];
cmd.Parameters.Add(p);
}
sb.Append(')');
}
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}
public DbCommand CreateCustomSqlCommand(string sql, string[] paramNames, DbType[] paramTypes, object[] paramValues)
{
Check.Require(!string.IsNullOrEmpty(sql), "sql could not be null or empty!");
Check.Require(paramNames == null ||
(paramNames != null && paramTypes != null && paramValues != null &&
paramNames.Length == paramTypes.Length && paramNames.Length == paramValues.Length),
"length of paramNames, paramTypes and paramValues should equal!");
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
AddInputParametersToCommand(paramNames, paramTypes, paramValues, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sql, leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}
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!");
//if (where != null)
//{
// Check.Require(where.From == null, "expr.From must be null in Update");
//}
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)
{
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);
}