public void CreateNullableConditionTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
SelectCommand baseCommand = new SelectCommand(fakeDb, "");
AbstractSqlCondition baseCondition = SqlCondition.Equal(baseCommand, "TestColumn2", 1);
AbstractSqlCondition expectedCondition = baseCondition & null;
AbstractSqlCondition actualCondition = baseCondition;
Assert.AreEqual(expectedCondition, actualCondition);
AbstractSqlCondition expectedCondition2 = null & baseCondition;
AbstractSqlCondition actualCondition2 = baseCondition;
Assert.AreEqual(expectedCondition2, actualCondition2);
AbstractSqlCondition expectedCondition3 = baseCondition | null;
AbstractSqlCondition actualCondition3 = baseCondition;
Assert.AreEqual(expectedCondition3, actualCondition3);
AbstractSqlCondition expectedCondition4 = null | baseCondition;
AbstractSqlCondition actualCondition4 = baseCondition;
Assert.AreEqual(expectedCondition4, actualCondition4);
}
/// <summary>
/// 初始化新的Sql分类字段类
/// </summary>
/// <param name="baseCommand">选择语句</param>
/// <param name="function">合计函数</param>
private SqlGroupByField(SelectCommand baseCommand, String function)
{
this._tableName = String.Empty;
this._columnName = String.Empty;
this._useFunction = true;
this._function = function;
}
/// <summary>
/// 初始化新的Sql分类字段类
/// </summary>
/// <param name="baseCommand">选择语句</param>
/// <param name="function">合计函数</param>
private SqlGroupByField(SelectCommand baseCommand, String function)
{
this._tableName = String.Empty;
this._columnName = String.Empty;
this._useFunction = true;
this._function = function;
}
/// <summary>
/// 初始化新的Sql分类字段类
/// </summary>
/// <param name="baseCommand">选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="columnName">字段名</param>
private SqlGroupByField(SelectCommand baseCommand, String tableName, String columnName)
{
this._tableName = tableName;
this._columnName = columnName;
this._useFunction = false;
this._function = String.Empty;
}
/// <summary>
/// 初始化新的Sql分类字段类
/// </summary>
/// <param name="baseCommand">选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="columnName">字段名</param>
private SqlGroupByField(SelectCommand baseCommand, String tableName, String columnName)
{
this._tableName = tableName;
this._columnName = columnName;
this._useFunction = false;
this._function = String.Empty;
}
/// <summary>
/// 添加新的Sql选择语句类
/// </summary>
/// <param name="tableAliasesName">数据表别名</param>
/// <returns>Sql选择语句</returns>
public SelectCommand Select(String tableAliasesName)
{
SelectCommand command = this._database.CreateSelectCommand(this.TableName, tableAliasesName);
this._list.Add(command);
return(command);
}
/// <summary>
/// 添加新的Sql选择语句类
/// </summary>
/// <returns>Sql选择语句</returns>
public SelectCommand Select()
{
SelectCommand command = this._database.CreateSelectCommand(this.TableName);
this._list.Add(command);
return(command);
}
/// <summary>
/// 初始化Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="function">合计函数</param>
/// <param name="columnName">字段名称</param>
/// <param name="aliasesName">字段别名</param>
private SqlQueryField(SelectCommand cmd, String tableName, SqlAggregateFunction function, String columnName, String aliasesName)
{
this._tableName = tableName;
this._columnName = columnName;
this._aliasesName = aliasesName;
this._useFunction = true;
this._function = function.ToString().ToUpperInvariant();
}
/// <summary>
/// 初始化Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="columnName">字段名称</param>
/// <param name="aliasesName">字段别名</param>
private SqlQueryField(SelectCommand cmd, String tableName, String columnName, String aliasesName)
{
this._tableName = tableName;
this._columnName = columnName;
this._aliasesName = aliasesName;
this._useFunction = false;
this._function = String.Empty;
}
/// <summary>
/// 添加新的Sql选择语句类
/// </summary>
/// <param name="tableAliasesName">数据表别名</param>
/// <param name="action">待执行的语句</param>
/// <returns>当前集合</returns>
public CommandCollection Select(String tableAliasesName, Action <SelectCommand> action)
{
SelectCommand command = this._database.CreateSelectCommand(this.TableName, tableAliasesName);
this._list.Add(command);
action(command);
return(this);
}
/// <summary>
/// 初始化新的Sql语句排序类
/// </summary>
/// <param name="baseCommand">选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="columnName">字段名</param>
/// <param name="orderType">排序方式</param>
private SqlOrder(SelectCommand baseCommand, String tableName, String columnName, SqlOrderType orderType)
{
this._tableName = tableName;
this._columnName = columnName;
this._orderType = orderType;
this._useFunction = false;
this._useDistinct = false;
this._function = String.Empty;
}
/// <summary>
/// 初始化Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="function">合计函数</param>
/// <param name="columnName">字段名称</param>
/// <param name="useDistinct">是否保证记录唯一</param>
/// <param name="aliasesName">字段别名</param>
private SqlQueryField(SelectCommand cmd, String tableName, SqlAggregateFunction function, String columnName, Boolean useDistinct, String aliasesName)
{
this._tableName = tableName;
this._columnName = columnName;
this._aliasesName = aliasesName;
this._useFunction = true;
this._useDistinct = useDistinct;
this._function = function.ToString().ToUpperInvariant();
}
/// <summary>
/// 初始化Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="columnName">字段名称</param>
/// <param name="aliasesName">字段别名</param>
private SqlQueryField(SelectCommand cmd, String tableName, String columnName, String aliasesName)
{
this._tableName = tableName;
this._columnName = columnName;
this._aliasesName = aliasesName;
this._useFunction = false;
this._useDistinct = false;
this._function = String.Empty;
}
/// <summary>
/// 初始化新的Sql语句排序类
/// </summary>
/// <param name="baseCommand">选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="columnName">字段名</param>
/// <param name="orderType">排序方式</param>
private SqlOrder(SelectCommand baseCommand, String tableName, String columnName, SqlOrderType orderType)
{
this._tableName = tableName;
this._columnName = columnName;
this._orderType = orderType;
this._useFunction = false;
this._useDistinct = false;
this._function = String.Empty;
}
/// <summary>
/// 初始化新的Sql语句排序类
/// </summary>
/// <param name="baseCommand">选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="function">合计函数</param>
/// <param name="columnName">字段名</param>
/// <param name="useDistinct">是否保证记录唯一</param>
/// <param name="orderType">排序方式</param>
private SqlOrder(SelectCommand baseCommand, String tableName, SqlAggregateFunction function, String columnName, Boolean useDistinct, SqlOrderType orderType)
{
this._tableName = tableName;
this._columnName = columnName;
this._orderType = orderType;
this._useFunction = true;
this._useDistinct = useDistinct;
this._function = function.ToString().ToUpperInvariant();
}
/// <summary>
/// 获取分页后的选择语句
/// </summary>
/// <param name="sourceCommand">源选择语句</param>
/// <param name="recordCount">记录数量</param>
/// <param name="orderReverse">是否反转</param>
/// <returns>分页后的选择语句</returns>
internal override String InternalGetPagerSelectCommand(SelectCommand sourceCommand, Int32 recordCount, Boolean orderReverse)
{
if (sourceCommand.RecordStart < recordCount)
{
return AccessSelectPager.InternalGetPagerCommand(sourceCommand, recordCount, orderReverse);
}
else
{
return AccessSelectPager.InternalGetSelectNoneCommand(sourceCommand, orderReverse);
}
}
public void CreateNotInConditionTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
SelectCommand expectedCommand = new SelectCommand(fakeDb, "");
SelectCommand actualCommand = new SelectCommand(fakeDb, "");
SqlInsideParametersCondition expectedCondition = SqlCondition.NotIn(expectedCommand, "TestColumn2", 1, 2, 3, 4, 5);
SqlInsideParametersCondition actualCondition = SqlCondition.NotInInt32(actualCommand, "TestColumn2", "1, 2, 3, 4, 5", ',');
Assert.AreEqual(expectedCondition, actualCondition);
}
/// <summary>
/// 当满足指定条件时添加指定Sql选择语句类
/// </summary>
/// <param name="tableAliasesName">数据表别名</param>
/// <param name="condition">指定条件</param>
/// <param name="action">待执行的语句</param>
/// <returns>当前集合</returns>
public CommandCollection Select(String tableAliasesName, Func <Boolean> condition, Action <SelectCommand> action)
{
if (condition())
{
SelectCommand command = this._database.CreateSelectCommand(this.TableName, tableAliasesName);
this._list.Add(command);
action(command);
}
return(this);
}
public void LinqCreateBasicConditionTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
SelectCommand expectedCommand = new SelectCommand(fakeDb, "");
SelectCommand actualCommand = new SelectCommand(fakeDb, "");
SqlBasicParameterCondition expectedCondition = SqlCondition.Equal(expectedCommand, "TestColumn2", 123);
SqlBasicParameterCondition actualCondition = SqlLinqCondition.Create<TestEntity>(actualCommand, c => c.Test2 == 123) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition, actualCondition);
SqlBasicParameterCondition expectedCondition2 = SqlCondition.NotEqual(expectedCommand, "TestColumn2", 123);
SqlBasicParameterCondition actualCondition2 = SqlLinqCondition.Create<TestEntity>(actualCommand, c => c.Test2 != 123) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition2, actualCondition2);
SqlBasicParameterCondition expectedCondition3 = SqlCondition.GreaterThan(expectedCommand, "TestColumn2", 123);
SqlBasicParameterCondition actualCondition3 = SqlLinqCondition.Create<TestEntity>(actualCommand, c => c.Test2 > 123) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition3, actualCondition3);
SqlBasicParameterCondition expectedCondition4 = SqlCondition.LessThan(expectedCommand, "TestColumn2", 123);
SqlBasicParameterCondition actualCondition4 = SqlLinqCondition.Create<TestEntity>(actualCommand, c => c.Test2 < 123) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition4, actualCondition4);
SqlBasicParameterCondition expectedCondition5 = SqlCondition.GreaterThanOrEqual(expectedCommand, "TestColumn2", 123);
SqlBasicParameterCondition actualCondition5 = SqlLinqCondition.Create<TestEntity>(actualCommand, c => c.Test2 >= 123) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition5, actualCondition5);
SqlBasicParameterCondition expectedCondition6 = SqlCondition.LessThanOrEqual(expectedCommand, "TestColumn2", 123);
SqlBasicParameterCondition actualCondition6 = SqlLinqCondition.Create<TestEntity>(actualCommand, c => c.Test2 <= 123) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition6, actualCondition6);
SqlBasicParameterCondition expectedCondition7 = SqlCondition.IsNotNull(expectedCommand, "TestColumn1");
SqlBasicParameterCondition actualCondition7 = SqlLinqCondition.Create<TestEntity>(actualCommand, c => c.Test1 != null) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition7, actualCondition7);
SqlBasicParameterCondition expectedCondition8 = SqlCondition.IsNull(expectedCommand, "TestColumn3");
SqlBasicParameterCondition actualCondition8 = SqlLinqCondition.Create<TestEntity>(actualCommand, c => c.Test3 == null) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition8, actualCondition8);
SqlBasicParameterCondition expectedCondition10 = SqlCondition.EqualColumn(expectedCommand, "TestColumn2", "TestColumn3");
SqlBasicParameterCondition actualCondition10 = SqlLinqCondition.Create<TestEntity>(actualCommand, c => c.Test2 == c.Test3) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition10, actualCondition10);
}
/// <summary>
/// 获取分页后的选择语句
/// </summary>
/// <param name="sourceCommand">源选择语句</param>
/// <param name="orderReverse">是否反转</param>
/// <returns>分页后的选择语句</returns>
internal override String InternalGetPagerSelectCommand(SelectCommand sourceCommand, Boolean orderReverse)
{
if (sourceCommand.PageSize <= 0 || sourceCommand.RecordStart <= 0)//正常模式或分页模式中的子语句
{
return AccessSelectPager.InternalGetPagerCommand(sourceCommand, 0, orderReverse);
}
String cntCommand = AccessSelectPager.InternalGetCountCommand(sourceCommand);
SqlParameter[] parameters = sourceCommand.GetAllParameters();
DbCommand dbCommand = this.CreateDbCommand(cntCommand, parameters);
Int32 recordCount = this.ExecuteScalar<Int32>(dbCommand);
return this.InternalGetPagerSelectCommand(sourceCommand, recordCount, orderReverse);
}
/// <summary>
/// 查询指定选择语句并返回当前语句
/// </summary>
/// <param name="command">选择语句</param>
/// <param name="aliasesName">别名</param>
/// <exception cref="ArgumentNullException">选择语句不能为空</exception>
/// <returns>当前语句</returns>
public SelectCommand Query(SelectCommand command, String aliasesName)
{
if (command == null)
{
throw new ArgumentNullException("function");
}
this._queryFields.Add(SqlQueryField.InternalCreateFromFunction(command.ToString(true), aliasesName));
List <SqlParameter> parameters = command.GetAllParameters();
if (parameters != null)
{
this._parameters.AddRange(parameters);
}
return(this);
}
/// <summary>
/// 插入指定参数并返回当前语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="command">选择语句</param>
/// <exception cref="ArgumentNullException">选择语句不能为空</exception>
/// <returns>当前语句</returns>
public InsertCommand Add(String columnName, SelectCommand command)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
this._parameters.Add(SqlParameter.CreateCustomAction(columnName, command.ToString()));
List <SqlParameter> parameters = command.GetAllParameters();
if (parameters != null)
{
this._parameters.AddRange(command.GetAllParameters());
}
return(this);
}
public void CreateNotConditionTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
SelectCommand baseCommand = new SelectCommand(fakeDb, "");
SqlBasicParameterCondition baseCondition = SqlCondition.Equal(baseCommand, "TestColumn2", 1);
SqlNotCondition actualCondition = !baseCondition;
String expectedConditionClause = "(NOT((TestColumn2 = @PN_IDX_0)))";
String actualConditionClause = actualCondition.GetClauseText();
SqlParameter[] expectedParameters = baseCondition.GetAllParameters();
SqlParameter[] actualParameters = baseCondition.GetAllParameters();
Assert.AreEqual(expectedConditionClause, actualConditionClause);
for (Int32 i = 0; i < expectedParameters.Length; i++)
{
Assert.AreEqual(expectedParameters[i], actualParameters[i]);
}
}
public void LinqCreateIsNullConditionTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
SelectCommand expectedCommand = new SelectCommand(fakeDb, "");
SelectCommand actualCommand = new SelectCommand(fakeDb, "");
SqlBasicParameterCondition expectedCondition = SqlCondition.IsNull(expectedCommand, "TestColumn1");
SqlBasicParameterCondition actualCondition = SqlLinqCondition.Create<TestEntity>(actualCommand, c => c.Test1.IsNull()) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition, actualCondition);
SqlBasicParameterCondition expectedCondition2 = SqlCondition.IsNull(expectedCommand, "TestColumn2");
SqlBasicParameterCondition actualCondition2 = SqlLinqCondition.Create<TestEntity>(actualCommand, c => c.Test2.IsNull()) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition2, actualCondition2);
SqlBasicParameterCondition expectedCondition3 = SqlCondition.IsNull(expectedCommand, "TestColumn5");
SqlBasicParameterCondition actualCondition3 = SqlLinqCondition.Create<TestEntity>(actualCommand, c => c.Test5.IsNull()) as SqlBasicParameterCondition;
Assert.AreEqual(expectedCondition3, actualCondition3);
}
/// <summary>
/// 更新指定参数并返回当前语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="tableName">查询的表名</param>
/// <param name="action">设置选择语句的方法</param>
/// <exception cref="ArgumentNullException">设置语句的方法不能为空</exception>
/// <returns>当前语句</returns>
/// <example>
/// <code lang="C#">
/// <![CDATA[
/// IDatabase db = DatabaseFactory.CreateDatabase();
///
/// UpdateCommand cmd = db.CreateUpdateCommand("tbl_Users")
/// .Set("UserName", "admin")
/// .Set("UploadCount",
/// "tbl_Uploads", s =>
/// {
/// s.Query(SqlAggregateFunction.Count)
/// .Where(c => c.Equal("UserID", 1));
/// }
/// )
/// .Where(c => c.Equal("UserID", 1));
///
/// //UPDATE tbl_Users SET UserName = @UserName, UploadCount = (SELECT COUNT(*) FROM tbl_Uploads WHERE UserID = @UserID_Select) WHERE UserID = @UserID_Update
/// //@UserName = "******"
/// //@UserID_Select = 1
/// //@UserID_Update = 1
///
/// Boolean success = cmd.Result() > 0;
/// ]]>
/// </code>
/// </example>
public UpdateCommand Set(String columnName, String tableName, Action <SelectCommand> action)
{
if (action == null)
{
throw new ArgumentNullException("action");
}
SelectCommand command = this._database.InternalCreateSelectCommand((this.RootSource == null ? this : this.RootSource), tableName);
action(command);
this._updateFields.Add(this.CreateDataParameterCustomAction(columnName, command.GetCommandText()));
DataParameter[] parameters = command.GetAllParameters();
if (parameters != null)
{
this._parameters.AddRange(command.GetAllParameters());
}
return(this);
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">函数内容</param>
/// <param name="aliasesName">别名</param>
internal static SqlQueryField InternalCreateFromFunction(SelectCommand cmd, String function, String aliasesName)
{
return new SqlQueryField(cmd, function, aliasesName);
}
/// <summary>
/// 创建新的Sql分类字段类
/// </summary>
/// <param name="cmd">选择语句</param>
/// <param name="columnName">字段名</param>
internal static SqlGroupByField InternalCreate(SelectCommand cmd, String columnName)
{
return(new SqlGroupByField(cmd, String.Empty, columnName));
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="columnName">字段名称</param>
/// <param name="aliasesName">字段别名</param>
internal static SqlQueryField InternalCreateFromColumn(SelectCommand cmd, String tableName, String columnName, String aliasesName)
{
return new SqlQueryField(cmd, tableName, columnName, aliasesName);
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">函数内容</param>
internal static SqlQueryField InternalCreateFromFunction(SelectCommand cmd, String function)
{
return(new SqlQueryField(cmd, function, String.Empty));
}
/// <summary>
/// 创建新的Sql语句排序类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">函数内容</param>
/// <param name="orderType">排序方式</param>
internal static SqlOrder InternalCreateFromFunction(SelectCommand cmd, String function, SqlOrderType orderType)
{
return(new SqlOrder(cmd, function, orderType));
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="function">合计函数</param>
/// <param name="columnName">字段名称</param>
/// <param name="useDistinct">是否保证记录唯一</param>
/// <param name="aliasesName">字段别名</param>
internal static SqlQueryField InternalCreateFromAggregateFunction(SelectCommand cmd, String tableName, SqlAggregateFunction function, String columnName, Boolean useDistinct, String aliasesName)
{
return(new SqlQueryField(cmd, tableName, function, columnName, useDistinct, aliasesName));
}
/// <summary>
/// 初始化新的Sql语句排序类
/// </summary>
/// <param name="cmd">选择语句</param>
/// <param name="columnName">字段名</param>
/// <param name="orderType">排序方式</param>
internal static SqlOrder Create(SelectCommand cmd, String columnName, SqlOrderType orderType)
{
return new SqlOrder(cmd, columnName, orderType);
}
/// <summary>
/// 创建新的Sql语句排序类
/// </summary>
/// <param name="cmd">选择语句</param>
/// <param name="function">合计函数</param>
/// <param name="columnName">字段名称</param>
/// <param name="useDistinct">是否保证记录唯一</param>
/// <param name="orderType">排序方式</param>
internal static SqlOrder InternalCreateFromAggregateFunction(SelectCommand cmd, SqlAggregateFunction function, String columnName, Boolean useDistinct, SqlOrderType orderType)
{
return(new SqlOrder(cmd, String.Empty, function, columnName, useDistinct, orderType));
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">合计函数</param>
/// <param name="columnName">字段名称</param>
/// <param name="useDistinct">是否保证记录唯一</param>
internal static SqlQueryField InternalCreateFromAggregateFunction(SelectCommand cmd, SqlAggregateFunction function, String columnName, Boolean useDistinct)
{
return(new SqlQueryField(cmd, String.Empty, function, columnName, useDistinct, String.Empty));
}
/// <summary>
/// 初始化新的Sql语句排序类
/// </summary>
/// <param name="baseCommand">选择语句</param>
/// <param name="columnName">字段名</param>
/// <param name="orderType">排序方式</param>
private SqlOrder(SelectCommand baseCommand, String columnName, SqlOrderType orderType)
{
this._columnName = columnName;
this._orderType = orderType;
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">合计函数</param>
/// <param name="useDistinct">是否保证记录唯一</param>
/// <param name="aliasesName">字段别名</param>
internal static SqlQueryField InternalCreateFromAggregateFunction(SelectCommand cmd, SqlAggregateFunction function, Boolean useDistinct, String aliasesName)
{
return(new SqlQueryField(cmd, String.Empty, function, "*", useDistinct, aliasesName));
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">合计函数</param>
internal static SqlQueryField InternalCreateFromAggregateFunction(SelectCommand cmd, SqlAggregateFunction function)
{
return(new SqlQueryField(cmd, String.Empty, function, "*", false, String.Empty));
}
/// <summary>
/// 创建新的Sql分类字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">函数内容</param>
internal static SqlGroupByField InternalCreateFromFunction(SelectCommand cmd, String function)
{
return new SqlGroupByField(cmd, function);
}
/// <summary>
/// 创建新的Sql语句排序类
/// </summary>
/// <param name="cmd">选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="function">合计函数</param>
/// <param name="columnName">字段名称</param>
/// <param name="orderType">排序方式</param>
internal static SqlOrder InternalCreateFromAggregateFunction(SelectCommand cmd, String tableName, SqlAggregateFunction function, String columnName, SqlOrderType orderType)
{
return(new SqlOrder(cmd, tableName, function, columnName, false, orderType));
}
/// <summary>
/// 初始化新的Sql语句排序类
/// </summary>
/// <param name="cmd">选择语句</param>
/// <param name="columnName">字段名</param>
/// <param name="isAscending">是否升序</param>
internal static SqlOrder Create(SelectCommand cmd, String columnName, Boolean isAscending)
{
return new SqlOrder(cmd, columnName, (isAscending ? SqlOrderType.Asc : SqlOrderType.Desc));
}
/// <summary>
/// 创建新的Sql语句排序类
/// </summary>
/// <param name="cmd">选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="function">合计函数</param>
/// <param name="useDistinct">是否保证记录唯一</param>
/// <param name="orderType">排序方式</param>
internal static SqlOrder InternalCreateFromAggregateFunction(SelectCommand cmd, String tableName, SqlAggregateFunction function, Boolean useDistinct, SqlOrderType orderType)
{
return(new SqlOrder(cmd, tableName, function, "*", useDistinct, orderType));
}
/// <summary>
/// 获取分页后的选择语句
/// </summary>
/// <param name="sourceCommand">源选择语句</param>
/// <param name="orderReverse">是否反转</param>
/// <returns>分页后的选择语句</returns>
internal override String InternalGetPagerSelectCommand(SelectCommand sourceCommand, Boolean orderReverse)
{
return SqlServerSelectPager.InternalGetPagerCommand(sourceCommand, orderReverse);
}
/// <summary>
/// 创建新的Sql语句排序类
/// </summary>
/// <param name="cmd">选择语句</param>
/// <param name="function">合计函数</param>
/// <param name="orderType">排序方式</param>
internal static SqlOrder InternalCreateFromAggregateFunction(SelectCommand cmd, SqlAggregateFunction function, SqlOrderType orderType)
{
return(new SqlOrder(cmd, String.Empty, function, "*", false, orderType));
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="tableName">表格名称</param>
/// <param name="function">合计函数</param>
/// <param name="columnName">字段名称</param>
/// <param name="useDistinct">是否保证记录唯一</param>
/// <param name="aliasesName">字段别名</param>
internal static SqlQueryField InternalCreateFromAggregateFunction(SelectCommand cmd, String tableName, SqlAggregateFunction function, String columnName, Boolean useDistinct, String aliasesName)
{
return new SqlQueryField(cmd, tableName, function, columnName, useDistinct, aliasesName);
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">合计函数</param>
/// <param name="columnName">字段名称</param>
/// <param name="aliasesName">字段别名</param>
internal static SqlQueryField InternalCreateFromAggregateFunction(SelectCommand cmd, SqlAggregateFunction function, String columnName, String aliasesName)
{
return(new SqlQueryField(cmd, String.Empty, function, columnName, false, aliasesName));
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">合计函数</param>
/// <param name="columnName">字段名称</param>
/// <param name="useDistinct">是否保证记录唯一</param>
internal static SqlQueryField InternalCreateFromAggregateFunction(SelectCommand cmd, SqlAggregateFunction function, String columnName, Boolean useDistinct)
{
return new SqlQueryField(cmd, String.Empty, function, columnName, useDistinct, String.Empty);
}
/// <summary>
/// 初始化Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">函数名称</param>
/// <param name="aliasesName">字段别名</param>
private SqlQueryField(SelectCommand cmd, String function, String aliasesName)
{
this._tableName = String.Empty;
this._columnName = String.Empty;
this._aliasesName = aliasesName;
this._useFunction = true;
this._useDistinct = false;
this._function = function;
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">合计函数</param>
internal static SqlQueryField InternalCreateFromAggregateFunction(SelectCommand cmd, SqlAggregateFunction function)
{
return new SqlQueryField(cmd, String.Empty, function, "*", false, String.Empty);
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="columnName">字段名称</param>
internal static SqlQueryField InternalCreateFromColumn(SelectCommand cmd, String columnName)
{
return new SqlQueryField(cmd, String.Empty, columnName, String.Empty);
}
/// <summary>
/// 创建新的Sql分类字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">函数内容</param>
internal static SqlGroupByField InternalCreateFromFunction(SelectCommand cmd, String function)
{
return(new SqlGroupByField(cmd, function));
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">合计函数</param>
/// <param name="columnName">字段名称</param>
/// <param name="aliasesName">字段别名</param>
internal static SqlQueryField InternalCreateFromAggregateFunction(SelectCommand cmd, SqlAggregateFunction function, String columnName, String aliasesName)
{
return new SqlQueryField(cmd, String.Empty, function, columnName, false, aliasesName);
}
/// <summary>
/// 初始化Sql选择语句类
/// </summary>
/// <param name="database">数据库</param>
/// <param name="from">选择的从Sql语句</param>
/// <param name="fromAliasesName">从Sql语句的别名</param>
/// <param name="pageSize">页面大小</param>
public SelectCommand(Database database, SelectCommand from, String fromAliasesName, Int32 pageSize)
: this(database, true, from.ToString(fromAliasesName), pageSize, 0, 0)
{
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">合计函数</param>
/// <param name="useDistinct">是否保证记录唯一</param>
/// <param name="aliasesName">字段别名</param>
internal static SqlQueryField InternalCreateFromAggregateFunction(SelectCommand cmd, SqlAggregateFunction function, Boolean useDistinct, String aliasesName)
{
return new SqlQueryField(cmd, String.Empty, function, "*", useDistinct, aliasesName);
}
public void CreateNotConditionTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
SelectCommand expectedCommand = new SelectCommand(fakeDb, "");
SelectCommand actualCommand = new SelectCommand(fakeDb, "");
SqlBasicParameterCondition baseCondition = SqlCondition.Equal(expectedCommand, "TestColumn2", 1);
SqlNotCondition expectedCondition = !baseCondition;
SqlNotCondition actualCondition = SqlLinqCondition.Create<TestEntity>(actualCommand, c => !(c.Test2 == 1)) as SqlNotCondition;
Assert.AreEqual(expectedCondition, actualCondition);
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">函数内容</param>
internal static SqlQueryField InternalCreateFromFunction(SelectCommand cmd, String function)
{
return new SqlQueryField(cmd, function, String.Empty);
}
/// <summary>
/// 创建新的Sql语句排序类
/// </summary>
/// <param name="cmd">选择语句</param>
/// <param name="columnName">字段名</param>
/// <param name="orderType">排序方式</param>
internal static SqlOrder InternalCreate(SelectCommand cmd, String columnName, SqlOrderType orderType)
{
return(new SqlOrder(cmd, String.Empty, columnName, orderType));
}
/// <summary>
/// 更新指定参数并返回当前语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="command">选择语句</param>
/// <exception cref="ArgumentNullException">选择语句不能为空</exception>
/// <returns>当前语句</returns>
/// <example>
/// <code lang="C#">
/// <![CDATA[
/// IDatabase db = DatabaseFactory.CreateDatabase();
/// SelectCommand countCmd = db.CreateSelectCommand("tbl_Uploads")
/// .Query(SqlAggregateFunction.Count)
/// .Where(c => c.Equal("UserID", 1));
///
/// UpdateCommand cmd = db.CreateUpdateCommand("tbl_Users")
/// .Set("UserName", "admin")
/// .Set("UploadCount", countCmd)
/// .Where(c => c.Equal("UserID", 1));
///
/// //UPDATE tbl_Users SET UserName = @UserName, UploadCount = (SELECT COUNT(*) FROM tbl_Uploads WHERE UserID = @UserID_Select) WHERE UserID = @UserID_Update
/// //@UserName = "******"
/// //@UserID_Select = 1
/// //@UserID_Update = 1
///
/// Boolean success = cmd.Result() > 0;
/// ]]>
/// </code>
/// </example>
public UpdateCommand Set(String columnName, SelectCommand command)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
this._parameters.Add(this.CreateSqlParameterCustomAction(columnName, command.GetCommandText()));
SqlParameter[] parameters = command.GetAllParameters();
if (parameters != null)
{
this._parameters.AddRange(command.GetAllParameters());
}
return this;
}
/// <summary>
/// 创建新的Sql查询字段类
/// </summary>
/// <param name="cmd">源选择语句</param>
/// <param name="function">函数内容</param>
/// <param name="aliasesName">别名</param>
internal static SqlQueryField InternalCreateFromFunction(SelectCommand cmd, String function, String aliasesName)
{
return(new SqlQueryField(cmd, function, aliasesName));
}
/// <summary>
/// 创建新的Sql分类字段类
/// </summary>
/// <param name="cmd">选择语句</param>
/// <param name="columnName">字段名</param>
internal static SqlGroupByField InternalCreate(SelectCommand cmd, String columnName)
{
return new SqlGroupByField(cmd, String.Empty, columnName);
}
/// <summary>
/// 全连接语句并返回当前语句
/// </summary>
/// <param name="currentTableField">当前表格主键</param>
/// <param name="anotherTableCommand">另个表格命令</param>
/// <param name="anotherTableField">另个表格主键</param>
/// <returns>当前语句</returns>
public SelectCommand FullJoin(String currentTableField, SelectCommand anotherTableCommand, String anotherTableField)
{
this._joins.Add(new SqlJoinTableCommand(SqlJoinType.FullJoin, this._tableName, currentTableField, anotherTableCommand, this.SqlJoins.Count, anotherTableField));
return(this);
}