public void TestCreateCommandPopulatesCommandParameters()
{
var conn = new MockConnection();
var query = new DbQueryBuilder("dbo.TableName", conn);
query.Parameters.Add(new DbParameterValue()
{
ParameterName = "@Param1",
DbType = System.Data.DbType.Int32,
Size = 4,
Value = 123
});
var stmt = query.Render();
Assert.AreEqual(1, stmt.Parameters.Count);
Assert.AreEqual(System.Data.DbType.Int32, stmt.Parameters.First().DbType);
Assert.AreEqual(4, stmt.Parameters.First().Size);
Assert.AreEqual(123, stmt.Parameters.First().Value);
var cmd = stmt.CreateCommand(conn);
Assert.AreEqual(conn, cmd.Connection);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(System.Data.DbType.Int32, ((IDbDataParameter)cmd.Parameters[0]).DbType);
Assert.AreEqual(4, ((IDbDataParameter)cmd.Parameters[0]).Size);
Assert.AreEqual(123, ((IDbDataParameter)cmd.Parameters[0]).Value);
}
/// <summary>
/// Loads Count of Records in the specifyed Table
/// </summary>
/// <param name="tableName"></param>
/// <returns>Count</returns>
public ulong LoadCount(string tableName)
{
string query = new DbQueryBuilder(this).BuildSelectCount(tableName);
object scalar = ExecuteScalar(query);
return(Convert.ToUInt64(scalar));
}
/// <summary>
/// Sets the database query builder in the specified script variable set.
/// </summary>
/// <param name="scriptVariableSet">The script variable set to consider.</param>
/// <param name="queryBuilder">The query builder to consider.</param>
/// <returns></returns>
public static IScriptVariableSet SetDbBuilder(
this IScriptVariableSet scriptVariableSet,
DbQueryBuilder queryBuilder)
{
scriptVariableSet?.SetValue(__DbBuilder, queryBuilder);
return(scriptVariableSet);
}
private static void GetSalesOrderDetails(Db db, DbQueryBuilder queryBuilder, SalesOrderDetail model)
{
SalesOrderDetail d;
queryBuilder.From(db.SalesOrderDetail, out d)
.AutoSelect()
.OrderBy(d.SalesOrderDetailID);
}
/// <summary>
/// Creates database query for specified model.
/// </summary>
/// <typeparam name="T">Type of the model.</typeparam>
/// <param name="_">The specified model.</param>
/// <param name="initializer">The model initializer.</param>
/// <param name="buildQuery">The query builder.</param>
/// <returns>The created database query.</returns>
public DbQuery <T> CreateQuery <T>(T _, Action <T> initializer, Action <DbQueryBuilder, T> buildQuery)
where T : Model, new()
{
buildQuery.VerifyNotNull(nameof(buildQuery));
var model = _ == null ? new T() : (_.DataSource == null ? _ : _.MakeCopy(false));
model.Initialize(initializer);
var builder = new DbQueryBuilder(model);
buildQuery(builder, model);
return(PerformCreateQuery(model, builder.BuildQueryStatement(null)));
}
public void TestForkCreatesShallowClone()
{
var conn = new MockConnection();
var query = new DbQueryBuilder("dbo.TableName", conn);
query.OrderBy.Add(new SqlOrderColumn()
{
ColumnName = "Foo"
});
query.AddJoin(new SqlJoin(query.Root, SqlJoinType.Cross, SqlTable.Create("Blah")));
query.AddFilter(new SqlNullFilter(query.Root, "Foo", false));
query.SelectColumns.Add(new SqlColumn(query.Root, "Foo"));
query.PagingOptions = new SqlPagingOptions()
{
Offset = 10,
Limit = 100
};
query.Parameters.Add(new DbParameterValue()
{
ParameterName = "@Param1",
DbType = System.Data.DbType.Int32,
Size = 4,
Value = 123
});
query.Distinct = true;
query.CommandTimeout = 123;
var fork = query.Fork();
query.OrderBy.Clear();
query.AddFilter(new SqlNullFilter(query.Root, "Bar", false));
query.SelectColumns.Clear();
query.PagingOptions = null;
query.Parameters.Clear();
query.CommandTimeout = 999;
query.Distinct = false;
query.AddJoin(new SqlJoin(query.Root, SqlJoinType.Cross, SqlTable.Create("blah2")));
Assert.AreEqual(1, fork.OrderBy.Count);
Assert.AreEqual(1, fork.SelectColumns.Count);
Assert.AreEqual(1, fork.Parameters.Count);
Assert.AreEqual(10, fork.PagingOptions.Value.Offset);
Assert.AreEqual(100, fork.PagingOptions.Value.Limit);
Assert.AreEqual(true, fork.Distinct);
Assert.AreEqual(123, fork.CommandTimeout);
}
public void TestQueryBuilderRenderBatch()
{
var conn = new MockConnection();
var query = new DbQueryBuilder("SELECT * FROM dbo.TableName WHERE Foo=@Foo", conn);
query.Parameters.Add(new DbParameterValue()
{
ParameterName = "@Foo",
DbType = System.Data.DbType.Int32,
Size = 4,
Value = 123
});
var stmt = new ISqlStatementElement[] { query }.RenderBatch();
Assert.AreEqual(1, stmt.Parameters.Count);
Assert.AreEqual("SELECT * FROM (SELECT * FROM dbo.TableName WHERE Foo=@Foo) AS [t0];\r\n", stmt.CommandText);
}
public void TestQueryBuilderAsSqlStatementRendersParametersToArgs()
{
var conn = new MockConnection();
var query = new DbQueryBuilder("SELECT * FROM dbo.TableName WHERE Foo=@Foo", conn);
query.Parameters.Add(new DbParameterValue()
{
ParameterName = "@Foo",
DbType = System.Data.DbType.Int32,
Size = 4,
Value = 123
});
var args = new SqlBuildArguments();
var writer = new SqlTextWriter();
((ISqlStatementElement)query).Render(writer, args);
Assert.AreEqual(1, args.Parameters.Count);
Assert.AreEqual("SELECT * FROM (SELECT * FROM dbo.TableName WHERE Foo=@Foo) AS [t0]", writer.ToString());
}
/// <summary>
/// Load records using association table
/// </summary>
/// <typeparam name="TTargetType">Type we want to Load</typeparam>
/// <typeparam name="TAssociationType">Type which contains associations</typeparam>
/// <param name="data">Data object</param>
/// <returns>Array of Requested Objects</returns>
/// <example>
/// <code>
/// Task []tasks = DbGateway.Instance.LoadAssociated{Task, TasksAssignment}(TestData.User);
///
/// Types Definitions:
///
/// [DbRecord]
/// public class TasksAssignment
/// {
/// [DbForeignKeyField(typeof(Task))]
/// public ulong TaskId;
///
/// [DbForeignKeyField(typeof(User))]
/// public ulong UserId;
///
/// ...
/// }
///
/// [DbRecord]
/// public class Task
/// {
/// [DbPrimaryKeyField]
/// public ulong Id;
///
/// ...
/// }
///
/// [DbRecord]
/// public class User
/// {
/// [DbPrimaryKeyField]
/// public ulong Id;
///
/// ...
/// }
/// </code>
/// </example>
public TTargetType[] LoadAssociated <TTargetType, TAssociationType>(object data) where TTargetType : new()
{
Type primaryType = data.GetType();
DbIdentityRecordInfo targetRecordInfo = DbAttributesManager.GetRecordInfo(typeof(TTargetType)) as DbIdentityRecordInfo;
if (targetRecordInfo == null)
{
throw new NdbNotIdentityException("Only DbIdentityRecord objects can have related records");
}
DbRecordInfo associationRecordInfo = DbAttributesManager.GetRecordInfo(typeof(TAssociationType));
DbIdentityRecordInfo sourceRecordInfo = DbAttributesManager.GetRecordInfo(primaryType) as DbIdentityRecordInfo;
if (sourceRecordInfo == null)
{
throw new NdbNotIdentityException("Only DbIdentityRecord objects can have related records");
}
object primaryKey = sourceRecordInfo.PrimaryKey.GetValue(data);
DbQueryBuilder queryBuilder = new DbQueryBuilder(Accessor);
string select = queryBuilder.BuildSelect(targetRecordInfo);
// below is a self documented query? :)
string sql = string.Format(
@"{5} INNER JOIN {1} ON {0}.{3}={1}.{2} AND {1}.{4}=@PrimaryKey"
// @"SELECT * FROM {0} INNER JOIN {1} ON {0}.{3}={1}.{2} AND {1}.{4}=@PrimaryKey"
, targetRecordInfo.TableName
, associationRecordInfo.TableName
, associationRecordInfo.ForeignKeys[typeof(TTargetType)].Name
, targetRecordInfo.PrimaryKey.Name
, associationRecordInfo.ForeignKeys[primaryType].Name
, select
);
return(loadRecords <TTargetType>(targetRecordInfo, sql, "PrimaryKey", primaryKey));
}