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 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 SqlServerSourceConstructorTest_Subquery()
{
SqlServerSource source = new SqlServerSource("Table");
ISqlSelectStatement statement = new SqlServerSelectStatement();
statement.SelectClause.AddExpressions(new SqlAllField(source));
statement.FromClause.Source = source;
SqlServerSource target = new SqlServerSource(statement, "Source");
Assert.AreEqual <string>(string.Format("[{0}]", target.Name), target.Fullname);
Assert.AreEqual <string>(string.Format("({0}) AS [{1}]", statement.SqlString, target.Name), target.SqlString);
}
public void ExecuteSchemaTest()
{
ISqlObject source = new SqlServerSource("tableTypeList");
ISqlSelectStatement statement = new SqlServerSelectStatement();
statement.SelectClause.AddExpressions(new SqlAllField(source));
statement.FromClause.Source = source;
statement.WhereClause.Condition = new SqlStringExpression("1 = 2");
DbConnection connection = new SqlConnection(Properties.Settings.Default.SqlConnectionString);
DataTable schema = SqlHelper.ExecuteSchema(connection, statement);
}
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 ExecuteSelectTest()
{
ISqlObject source = new SqlServerSource(TABLE_NAME);
ISqlSelectStatement statement = new SqlServerSelectStatement();
statement.SelectClause.AddExpressions(new SqlAllField(source));
statement.FromClause.Source = source;
using (IDataReader reader = SqlHelper.ExecuteSelect(new SqlConnection(Properties.Settings.Default.SqlConnectionString), statement)) {
object value = null;
do
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
value = reader[i];
}
}
} while(reader.NextResult());
}
}
