public void SqlStringTest()
{
ISqlObject source = new SqlServerSource("Table");
ISqlObject field0 = new SqlServerField(source, "Field0", null);
ISqlFunction field1 = new SqlFunction("COUNT").AddArgument(new SqlAllField(null));
ISqlSelectClause target = new SqlSelectClause().AddExpressions(field0, field1);
Assert.AreEqual <string>(string.Format("{0} {1}, {2}", target.Keyword, field0.SqlString, field1.SqlString), target.SqlString);
}
public void SqlStringTest_More()
{
ISqlObject source = new SqlServerSource("Table");
ISqlObject field0 = new SqlServerField(source, "AddTime", null);
ISqlObject field1 = SqlObject.FromVariable("ROWNUM");
ISqlOrderClause target = new SqlOrderClause().AddExpression(field0, SqlOrder.Desc).AddExpression(field1, SqlOrder.Asc);
Assert.AreEqual <string>(string.Format("{0} ({1}) DESC, ({2}) ASC", target.Keyword, field0.Fullname, field1.Fullname), target.SqlString);
}
public void ExecuteSelectScalarTest()
{
ISqlObject source = new SqlServerSource(TABLE_NAME);
ISqlObject field = new SqlServerField(source, "ID", null);
ISqlSelectStatement statement = new SqlServerSelectStatement();
statement.SelectClause.AddExpressions(field);
statement.FromClause.Source = source;
statement.OrderClause.AddExpression(field, SqlOrder.Asc);
Assert.AreEqual <int>(1, SqlHelper.ExecuteSelectScalar <int>(new SqlConnection(Properties.Settings.Default.SqlConnectionString), statement));
}
public void GetSqlStringTest()
{
ISqlObject source = new SqlServerSource("Table");
ISqlObject field0 = new SqlServerField(source, "ID", null);
ISqlExpression value0 = new SqlStringExpression("13");
ISqlObject field1 = new SqlServerField(source, "Type", null);
ISqlExpression value1 = new SqlStringExpression("14");
Assert.AreEqual <string>(string.Format("{0} ({1}, {2}) VALUES ({3}, {4})", source.Fullname, field0.Fullname, field1.Fullname, value0.SqlString, value1.SqlString),
new SqlFieldValueClause(new SqlInsertFieldValueClauseSqlStringProvider()).SetSource(source).AddField(field0, value0).AddField(field1, value1).SqlString);
}
public void SqlServerFieldConstructorTest_Function()
{
ISqlFunction function = new SqlFunction("SUM");
function.AddArgument(new SqlStringExpression("1"));
function.AddArgument(new SqlStringExpression("2"));
ISqlObject field = new SqlServerField(function, "Total");
Assert.AreEqual <string>(string.Format("[{0}]", field.Name), field.Fullname);
Assert.AreEqual <string>(string.Format("{0} AS [{1}]", function.SqlString, field.Name), field.SqlString);
}
public void CountTest()
{
ISqlObject source = new SqlServerSource("Table");
ISqlObject field = new SqlServerField(source, "ID", null);
SqlServerSelectClause target = new SqlServerSelectClause();
target.AddExpressions(field);
target.Count = 13;
Assert.AreEqual <string>(string.Format("{0} TOP {1} {2}", target.Keyword, target.Count, field.SqlString), target.SqlString);
}
public void SqlStringTest()
{
ISqlObject source = new SqlServerSource("Table");
ISqlObject field = new SqlServerField(source, "ID", null);
ISqlDeleteStatement statement = new SqlDeleteStatement();
statement.FromClause.SetSource(source);
statement.WhereClause.Condition = field.GreaterThan(new SqlStringExpression("13"));
Assert.AreEqual <string>(string.Format("{0} {1} {2}", statement.Keyword, statement.FromClause.SqlString, statement.WhereClause.SqlString), statement.SqlString);
}
public void CountTest()
{
ISqlObject source = new SqlServerSource("Table");
ISqlObject field = new SqlServerField(source, "ID", null);
SqlServerSelectStatement target = new SqlServerSelectStatement();
target.SelectClause.AddExpressions(field);
target.FromClause.SetSource(source);
target.Count = 13;
Assert.AreEqual <string>(string.Format("{0} {1}", target.SelectClause.SqlString, target.FromClause.SqlString), target.SqlString);
}
public void InnerJoinTest()
{
ISqlObject source0 = new SqlServerSource("Table0");
ISqlObject source1 = new SqlServerSource("Table1");
ISqlObject id0 = new SqlServerField(source0, "ID", null);
ISqlObject id1 = new SqlServerField(source1, "ID", null);
ISqlExpression condition = id0.Equal(id1);
SqlFromClause target = new SqlFromClause();
Assert.AreEqual <string>(string.Format("FROM {0} INNER JOIN {1} ON {2}", source0.SqlString, source1.SqlString, condition.SqlString),
target.SetSource(source0).InnerJoin(source1, condition).SqlString);
}
public void SqlStringTest_NoWhere()
{
ISqlObject source = new SqlServerSource("Table");
ISqlObject field0 = new SqlServerField(source, "ID", null);
ISqlExpression value0 = new SqlStringExpression("13");
ISqlObject field1 = new SqlServerField(source, "Type", null);
ISqlExpression value1 = new SqlStringExpression("14");
ISqlUpdateStatement statement = new SqlUpdateStatement();
statement.FieldValueClause.SetSource(source).AddField(field0, value0).AddField(field1, value1);
Assert.AreEqual <string>(string.Format("{0} {1}", statement.Keyword, statement.FieldValueClause.SqlString),
statement.SqlString);
}
public void ParametersTest()
{
ISqlObject source = new SqlServerSource("Table");
ISqlObject field0 = new SqlServerField(source, "ID", null);
ISqlExpression value0 = SqlStringExpression.FromParameter(new SqlParameter("@p0", 13));
ISqlObject field1 = new SqlServerField(source, "Type", null);
ISqlExpression value1 = SqlStringExpression.FromParameter(new SqlParameter("@p1", 14));
ReadOnlyCollection <IDataParameter> parameters = new SqlFieldValueClause(new SqlUpdateFieldValueClauseSqlStringProvider()).SetSource(source).AddField(field0, value0).AddField(field1, value1).Parameters;
Assert.AreEqual <int>(2, parameters.Count);
Assert.IsTrue(parameters.Contains(value0.Parameters[0]));
Assert.IsTrue(parameters.Contains(value1.Parameters[0]));
}
public void SqlStringTest_Parametric()
{
ISqlObject source = new SqlServerSource("Table");
ISqlObject field = new SqlServerField(source, "ID", null);
ISqlExpression value = SqlStringExpression.FromParameter(new SqlParameter("@p", 13));
ISqlDeleteStatement statement = new SqlDeleteStatement();
statement.FromClause.SetSource(source);
statement.WhereClause.Condition = field.GreaterThan(value);
Assert.AreEqual <string>(string.Format("{0} {1} {2}", statement.Keyword, statement.FromClause.SqlString, statement.WhereClause.SqlString), statement.SqlString);
ReadOnlyCollection <IDataParameter> parameters = statement.Parameters;
Assert.AreEqual <int>(1, parameters.Count);
Assert.IsTrue(parameters.Contains(value.Parameters[0]));
}
public void SqlStringTest()
{
ISqlObject target = new SqlServerSource("Table1");
ISqlObject field0 = new SqlServerField(target, "ID", null);
ISqlExpression value0 = new SqlStringExpression("13");
ISqlObject field1 = new SqlServerField(target, "Type", null);
ISqlObject source = new SqlServerSource("Table2");
ISqlExpression value1 = new SqlServerField(source, "Type", null);
ISqlUpdateStatement statement = new SqlUpdateStatement();
statement.FieldValueClause.SetSource(target).AddField(field0, value0).AddField(field1, value1);
statement.FromClause.Source = source;
statement.WhereClause.Condition = new SqlServerField(target, "Flag", null).Equal(new SqlStringExpression("1"));
Assert.AreEqual <string>(string.Format("{0} {1} {2} {3}", statement.Keyword, statement.FieldValueClause.SqlString, statement.FromClause.SqlString, statement.WhereClause.SqlString),
statement.SqlString);
}
public void InnerJoinTest_Parametric()
{
ISqlObject source0 = new SqlServerSource("Table0");
ISqlObject source1 = new SqlServerSource("Table1");
ISqlObject id0 = new SqlServerField(source0, "ID", null);
ISqlObject id1 = new SqlServerField(source1, "ID", null);
ISqlExpression value = SqlStringExpression.FromParameter(new SqlParameter("@p", 13));
ISqlExpression condition = id0.Equal(id1).And(id0.GreaterEqual(value));
SqlFromClause target = new SqlFromClause();
Assert.AreEqual <string>(string.Format("FROM {0} INNER JOIN {1} ON {2}", source0.SqlString, source1.SqlString, condition.SqlString),
target.SetSource(source0).InnerJoin(source1, condition).SqlString);
ReadOnlyCollection <IDataParameter> parameters = target.Parameters;
Assert.AreEqual <int>(1, parameters.Count);
Assert.IsTrue(parameters.Contains(value.Parameters[0]));
}
public void ExecuteUpdateTest()
{
int? id = null;
ISqlObject source = new SqlServerSource(TABLE_NAME);
DbConnection connection = new SqlConnection(Properties.Settings.Default.SqlConnectionString);
try {
ISqlSelectStatement identityStatement = new SqlServerSelectStatement();
identityStatement.SelectClause.AddExpressions(new SqlFunction("IDENT_CURRENT").AddArgument(new SqlServerExpressionFactory().ConstantFactory.Create(TABLE_NAME)));
ISqlObject nameField = new SqlServerField(source, "Name", null);
ISqlExpression name = SqlStringExpression.FromParameter(new SqlParameter("@name", "Insert Test"));
ISqlInsertStatement insertStatement = new SqlInsertStatement();
insertStatement.FieldValueClause.SetSource(source).AddField(nameField, name);
id = SqlHelper.ExecuteInsert(connection, insertStatement, identityStatement);
ISqlSelectStatement selectStatement = new SqlServerSelectStatement();
selectStatement.SelectClause.AddExpressions(nameField);
selectStatement.FromClause.Source = source;
selectStatement.WhereClause.Condition = new SqlServerField(source, "ID", null).Equal(new SqlServerExpressionFactory().ConstantFactory.Create(id.Value));
Assert.AreEqual <string>((string)name.Parameters[0].Value, SqlHelper.ExecuteSelectScalar <string>(connection, selectStatement));
name = SqlStringExpression.FromParameter(new SqlParameter("@name", "Insert Test Updated"));
ISqlUpdateStatement updateStatement = new SqlUpdateStatement();
updateStatement.FieldValueClause.SetSource(source).AddField(nameField, name);
updateStatement.WhereClause.Condition = new SqlServerField(source, "ID", null).Equal(new SqlServerExpressionFactory().ConstantFactory.Create(id.Value));
SqlHelper.ExecuteUpdate(connection, updateStatement);
Assert.AreEqual <string>((string)name.Parameters[0].Value, SqlHelper.ExecuteSelectScalar <string>(connection, selectStatement));
} finally {
if (id.HasValue)
{
ISqlDeleteStatement deleteStatement = new SqlDeleteStatement();
deleteStatement.FromClause.Source = source;
deleteStatement.WhereClause.Condition = new SqlServerField(source, "ID", null).Equal(new SqlServerExpressionFactory().ConstantFactory.Create(id.Value));
SqlHelper.ExecuteDelete(connection, deleteStatement);
}
}
}
public void SqlStringTest_Parametric()
{
ISqlObject source = new SqlServerSource("Table");
ISqlObject field0 = new SqlServerField(source, "ID", null);
ISqlExpression value0 = SqlStringExpression.FromParameter(new SqlParameter("@ID", 13));
ISqlObject field1 = new SqlServerField(source, "Type", null);
ISqlExpression value1 = SqlStringExpression.FromParameter(new SqlParameter("@Type", 14));
ISqlUpdateStatement statement = new SqlUpdateStatement();
statement.FieldValueClause.SetSource(source).AddField(field0, value0).AddField(field1, value1);
ISqlExpression value2 = SqlStringExpression.FromParameter(new SqlParameter("@Flag", 1));
statement.WhereClause.Condition = new SqlServerField(source, "Flag", null).Equal(value2);
Assert.AreEqual <string>(string.Format("{0} {1} {2}", statement.Keyword, statement.FieldValueClause.SqlString, statement.WhereClause.SqlString),
statement.SqlString);
ReadOnlyCollection <IDataParameter> parameters = statement.Parameters;
Assert.AreEqual <int>(3, parameters.Count);
Assert.IsTrue(parameters.Contains(value0.Parameters[0]));
Assert.IsTrue(parameters.Contains(value1.Parameters[0]));
Assert.IsTrue(parameters.Contains(value2.Parameters[0]));
}
