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 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 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 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 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 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_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)));
}