public long Count(params Object[] selectionArgs)
{
var builder = Builder;
var countBuilder = new SqlSelectBuilder
{
Source = builder.Source,
Dialect = builder.Dialect,
WhereClause = builder.WhereClause,
GroupByClause = builder.GroupByClause,
OrderByClause = builder.OrderByClause,
LimitClause = builder.LimitClause,
Clauses = builder.Clauses,
};
foreach (var jc in builder.JoinClauses)
{
countBuilder.JoinClauses.Add(jc);
}
countBuilder.ResultColumns.Add("count(1)");
var connection = Connection;
if (connection != null)
{
return(connection.ExecuteScalar <long>(countBuilder.ToString() + ";", parameters: selectionArgs));
}
else
{
return(Datastore.ExecuteScalar <long>(countBuilder.ToString() + ";", parameters: selectionArgs));
}
}
public void Select_from_subquery(string expected, string alias)
{
var subQuery = new SqlSelectBuilder()
{
Source = new TableOrSubQuery("tbl")
};
var builder = new SqlSelectBuilder()
{
Source = new TableOrSubQuery(subQuery, alias),
};
var sql = builder.ToString();
Output.WriteLine(sql);
sql.ShouldBeEquivalentTo(expected);
VerifyCommandSyntex(sql + ";");
}
public void Select_with_joins(string expected, string alias, params string[] joinTables)
{
var constr = "USING (ID)";
var builder = new SqlSelectBuilder()
{
Source = new TableOrSubQuery("tbl")
};
foreach (var joinTable in joinTables)
{
builder.JoinClauses.Add(new JoinClause(joinTable, constr, alias));
}
var sql = builder.ToString();
Output.WriteLine(sql);
sql.ShouldBeEquivalentTo(expected);
VerifyCommandSyntex(sql + ";");
}
public void Select_from_table(string expected, params string[] cols)
{
var builder = new SqlSelectBuilder()
{
Source = new TableOrSubQuery("tbl"),
};
if (cols != null)
{
foreach (var col in cols)
{
builder.ResultColumns.Add(col);
}
}
var sql = builder.ToString();
Output.WriteLine(sql);
sql.ShouldBeEquivalentTo(expected);
VerifyCommandSyntex(sql + ";");
}
public void FluentInterfaceTest()
{
SqlSelectBuilder builder = new SqlSelectBuilder();
builder.Source = new TableOrSubQuery("something", "t1");
builder.ResultColumns.Add("col1");
builder.Join("something2", "Using (FKey)")
.Join("something3", "Using (FKey2)")
.Where("x > 1")
.GroupBy("col1", "col2")
.Limit(1, 0);
var sql = builder.ToString();
sql.Should().ContainEquivalentOf("JOIN");
sql.Should().ContainEquivalentOf("WHERE");
sql.Should().ContainEquivalentOf("GROUP BY");
sql.Should().ContainEquivalentOf("LIMIT");
VerifyCommandSyntex(sql + ";");
}
public void All()
{
var returnColumnNames = new List <string> {
"f1", "f2"
};
var builder = new SqlSelectBuilder(new SqlFlavorizer(SqlFlavorizer.VendorKind.PostgreSQL), "Emp", "LastName", returnColumnNames);
builder.WhereClause.AddWhere("IsActive<>0");
builder.WhereClause.AddWhere("State=" + builder.WhereClause.NextParameterName(), "Ohio");
Assert.AreEqual("@p1", builder.WhereClause.NextParameterName());
var cmd = new NpgsqlCommand();
builder.WhereClause.ExportParameters(cmd);
Assert.AreEqual("Ohio", cmd.Parameters[0].Value);
Assert.AreEqual("select f1,f2 from Emp where IsActive<>0 and State=@p0 order by Emp.LastName", builder.ToString());
//with paging
builder.PageSize = 100;
builder.PageNo = 1;
Assert.AreEqual("select f1,f2 from Emp where IsActive<>0 and State=@p0 order by Emp.LastName limit 101 offset 100", builder.ToString());
}