public void CanExecuteNonQueryWithDbCommand()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
string insertionString = @"insert into TestTable values (5, 'Cinco')";
DbCommand insertionCommand = new SqlCeCommand();
insertionCommand.CommandText = insertionString;
database.ExecuteNonQuery(insertionCommand);
string countCommand = @"select count(*) from TestTable";
int count = Convert.ToInt32(database.ExecuteScalar(countCommand));
string cleanupString = "delete from TestTable where TestColumn = 5";
DbCommand cleanupCommand = new SqlCeCommand();
cleanupCommand.CommandText = cleanupString;
int rowsAffected = database.ExecuteNonQuery(cleanupCommand);
Assert.AreEqual(5, count);
Assert.AreEqual(1, rowsAffected);
}
}
}
public void ExecuteScalarWithInvalidSqlStringThrows()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
database.ExecuteScalar("junk");
}
}
}
public void ExecuteScalarWithNullStringDoesNotReturnNull()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
database.ExecuteScalar((String)null);
}
}
}
public void ExecuteScalarWithNullDbCommandThrows()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
database.ExecuteScalar((DbCommand)null);
}
}
}
public void ExecuteScalarWithStringSql()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
string command = @"select count(*) from TestTable";
int count = Convert.ToInt32(database.ExecuteScalar(command));
Assert.AreEqual(4, count);
}
}
}
public void ThrowsIfNullParameterNameIsPassed()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
string sql = @"SELECT COUNT(*) FROM Customers WHERE ContactName = @Name";
DbParameter param = database.CreateParameter(null, "Maria Anders");
Assert.AreEqual(1, database.ExecuteScalar(sql, param));
database.CloseSharedConnection();
}
}
}
public void ExecuteSqlStringCommandWithNotEnoughParameterValues()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
string sql = "select * from TestTable where (TestColumn=@Param1) and TestColumn2=@Param2";
DbParameter parameter = database.CreateParameter("@Param1", DbType.Int32, 0, 1);
DbParameter paramete2 = database.CreateParameter("@Param2", DbType.Int32, 0, 1);
database.ExecuteScalar(sql, parameter, paramete2);
}
}
}
public void ExecuteScalarWithParametersReturnExpectedValue()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
string command = @"select * from TestTable where TestColumn > @TestColumn";
DbParameter parameter = database.CreateParameter("TestColumn", 2);
int value = Convert.ToInt32(database.ExecuteScalar(command, parameter));
Assert.AreEqual(3, value);
}
}
}
public void ExecuteScalarWithDbCommand()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
DbCommand command = new SqlCeCommand();
command.CommandText = @"SELECT COUNT(*) FROM TestTable";
int count = Convert.ToInt32(database.ExecuteScalar(command));
Assert.AreEqual(4, count);
}
}
}
public void ExecuteSqlStringCommandWithTooManyParameterValues()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
string sql = "select count(*) from TestTable where (TestColumn=@Param1) and TestColumn2=@Param2";
DbParameter[] parameters = new DbParameter[]
{
database.CreateParameter("@Param1", DbType.Int32, 0, 1),
database.CreateParameter("@Param2", DbType.String, 50, "Uno"),
database.CreateParameter("@Param3", DbType.Int32, 0, 123)
};
object o = database.ExecuteScalar(sql, parameters);
Assert.AreEqual(1, (int)o);
}
}
}
public void CanInsertNullStringParameter()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
string sqlString = "insert into TestTable Values (@Param1, @Param2)";
DbParameter[] parameters = new DbParameter[]
{
database.CreateParameter("@Param1", DbType.Int32, 0, 5),
database.CreateParameter("@Param2", DbType.String, 50, null)
};
database.ExecuteNonQuery(sqlString, parameters);
string sqlCount = "SELECT COUNT(*) FROM TestTable ";
Assert.AreEqual(5, database.ExecuteScalar(sqlCount, null));
}
}
}