public void ExecuteDeleteTest()
{
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);
} finally {
if (id.HasValue)
{
ISqlDeleteStatement deleteStatement = new SqlDeleteStatement();
deleteStatement.FromClause.Source = source;
deleteStatement.WhereClause.Condition = new SqlServerField(source, "ID", null).Equal(SqlStringExpression.FromParameter(new SqlParameter("@ID", id.Value)));
SqlHelper.ExecuteDelete(connection, deleteStatement);
}
}
}
public void GetSqlStringTest_OneKeyword()
{
ISqlExpression expression = new SqlStringExpression("1 > 2");
SqlOperator op = new SqlOperator("NOT", new SqlKeywordsOperatorResultSqlStringProvider("NOT"), new SqlKeywordsOperatorResultParametersProvider(1));
Assert.AreEqual <string>(string.Format("NOT ({0})", expression.SqlString), op.Compute(expression).SqlString);
}
public void SqlStringTest()
{
string sqlString = "1 = 1";
SqlStringExpression target = new SqlStringExpression(sqlString);
Assert.AreEqual <string>(sqlString, target.SqlString);
}
public void AddArgumentTest_One()
{
SqlFunction target = new SqlFunction("ADD");
ISqlExpression argument = new SqlStringExpression("13");
target.AddArgument(argument);
Assert.AreEqual <string>(string.Format("{0}({1})", target.Name, argument.SqlString), target.SqlString);
}
public void GetSqlStringTest_OneOperand()
{
ISqlExpression expression0 = new SqlStringExpression("ID");
ISqlExpression expression1 = new SqlStringExpression("1");
SqlOperator op = new SqlOperator("IN", new SqlInOperatorResultSqlStringProvider(), new SqlOperatorResultAllParametersProvider());
Assert.AreEqual <string>(string.Format("({0}) IN ({1})", expression0.SqlString, expression1.SqlString), op.Compute(expression0, expression1).SqlString);
}
public void GetSqlStringTest()
{
ISqlExpression expression0 = new SqlStringExpression("ID");
ISqlExpression expression1 = new SqlStringExpression("1");
SqlOperator op = new SqlOperator("Assign", new SqlAssignOperatorResultSqlStringProvider(), new SqlKeywordsOperatorResultParametersProvider(1));
Assert.AreEqual <string>(string.Format("{0} = ({1})", expression0.SqlString, expression1.SqlString), op.Compute(expression0, expression1).SqlString);
}
public void GetSqlStringTest_MoreKeywords()
{
ISqlExpression expression0 = new SqlStringExpression("1");
ISqlExpression expression1 = new SqlStringExpression("2");
SqlOperator op = new SqlOperator("ADD", new SqlKeywordsOperatorResultSqlStringProvider("+"), new SqlKeywordsOperatorResultParametersProvider(1));
Assert.AreEqual <string>(string.Format("({0}) + ({1})", expression0.SqlString, expression1.SqlString), op.Compute(expression0, expression1).SqlString);
}
public void GetParametersTest_OneKeyword()
{
ISqlExpression expression = SqlStringExpression.FromParameter(new SqlParameter("@p", true));
SqlOperator op = new SqlOperator("NOT", new SqlKeywordsOperatorResultSqlStringProvider("NOT"), new SqlKeywordsOperatorResultParametersProvider(1));
ReadOnlyCollection <IDataParameter> parameters = op.Compute(expression).Parameters;
Assert.AreEqual <int>(1, parameters.Count);
Assert.IsTrue(parameters.Contains(expression.Parameters[0]));
}
public void ParametersTest()
{
string sqlString = "Name = @name";
IDataParameter parameter = new SqlParameter("@name", "test");
SqlStringExpression target = new SqlStringExpression(sqlString, parameter);
Assert.IsTrue(target.Parameters.Count == 1);
Assert.IsTrue(target.Parameters.Contains(parameter));
}
public void AddArgumentTest_More()
{
SqlFunction target = new SqlFunction("SUM");
ISqlExpression argument0 = new SqlStringExpression("13");
ISqlExpression argument1 = new SqlStringExpression("14");
target.AddArgument(argument0, argument1);
Assert.AreEqual <string>(string.Format("{0}({1}, {2})", target.Name, argument0.SqlString, argument1.SqlString), target.SqlString);
}
public void GetParametersTest_MoreKeywords()
{
ISqlExpression expression0 = SqlStringExpression.FromParameter(new SqlParameter("@p0", true));
ISqlExpression expression1 = SqlStringExpression.FromParameter(new SqlParameter("@p1", true));
SqlOperator op = new SqlOperator("ADD", new SqlKeywordsOperatorResultSqlStringProvider("+"), new SqlKeywordsOperatorResultParametersProvider(1));
ReadOnlyCollection <IDataParameter> parameters = op.Compute(expression0, expression1).Parameters;
Assert.AreEqual <int>(2, parameters.Count);
Assert.IsTrue(parameters.Contains(expression0.Parameters[0]));
Assert.IsTrue(parameters.Contains(expression1.Parameters[0]));
}
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 AddArgumentTest_Parametric()
{
SqlFunction target = new SqlFunction("SUM");
ISqlExpression argument0 = SqlStringExpression.FromParameter(new SqlParameter("@p0", 13));
ISqlExpression argument1 = SqlStringExpression.FromParameter(new SqlParameter("@p1", 14));
target.AddArgument(argument0, argument1);
Assert.AreEqual <string>(string.Format("{0}({1}, {2})", target.Name, argument0.SqlString, argument1.SqlString), target.SqlString);
Assert.IsTrue(target.Parameters.Count == 2);
Assert.IsTrue(target.Parameters.Contains(argument0.Parameters[0]));
Assert.IsTrue(target.Parameters.Contains(argument1.Parameters[0]));
}
public void GetParametersTest()
{
ISqlExpression expression0 = new SqlStringExpression("ID");
ISqlExpression expression1 = SqlStringExpression.FromParameter(new SqlParameter("@p1", 1));
ISqlExpression expression2 = SqlStringExpression.FromParameter(new SqlParameter("@p2", 2));
SqlOperator op = new SqlOperator("IN", new SqlInOperatorResultSqlStringProvider(), new SqlOperatorResultAllParametersProvider());
ReadOnlyCollection <IDataParameter> parameters = op.Compute(expression0, expression1, expression2).Parameters;
Assert.AreEqual <int>(2, parameters.Count);
Assert.IsTrue(parameters.Contains(expression1.Parameters[0]));
Assert.IsTrue(parameters.Contains(expression2.Parameters[0]));
}
public void RemoveArgumentTest()
{
SqlFunction target = new SqlFunction("ADD");
ISqlExpression argument0 = new SqlStringExpression("13");
ISqlExpression argument1 = new SqlStringExpression("14");
ISqlExpression argument2 = new SqlStringExpression("15");
target.AddArgument(argument0, argument1, argument2);
Assert.AreEqual <string>(string.Format("{0}({1}, {2}, {3})", target.Name, argument0.SqlString, argument1.SqlString, argument2.SqlString), target.SqlString);
target.RemoveArgument(argument2);
Assert.AreEqual <string>(string.Format("{0}({1}, {2})", target.Name, argument0.SqlString, argument1.SqlString), target.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 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 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 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]));
}
/// <summary>
/// Creates the SQL expression of a the OracleParameter.
/// </summary>
/// <param name="name"></param>
/// <param name="type"></param>
/// <param name="direction"></param>
/// <param name="value"></param>
/// <returns></returns>
public static ISqlExpression CreateParameterExpression(string name, OracleDbType type, ParameterDirection direction, object value)
{
return(SqlStringExpression.FromParameter(CreateParameterInternal(name, type, direction, value)));
}
/// <summary>
/// Creates the SQL expression of a the OracleParameter.
/// </summary>
/// <param name="name"></param>
/// <param name="value"></param>
/// <returns></returns>
public static ISqlExpression CreateParameterExpression(string name, object value)
{
return(SqlStringExpression.FromParameter(CreateParameterInternal(name, null, null, value)));
}
public void SqlString_Null()
{
SqlStringExpression target = new SqlStringExpression(null);
}
public void SqlString_Empty()
{
SqlStringExpression target = new SqlStringExpression(" \r \n \t ");
}