public void ExecuteSqlStringWithoutParametersButWithValues()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
string sql = "select * from TestTable";
DbParameter[] parameters = new DbParameter[]
{
database.CreateParameter("@Param1", DbType.Int32, 0, 1),
database.CreateParameter("@Param2", DbType.String, 50, "Uno")
};
using (IDataReader reader = database.ExecuteReader(sql, parameters))
{
reader.Read();
Assert.AreEqual(1, reader["TestColumn"]);
Assert.AreEqual("Uno", reader["TestColumn2"]);
reader.Read();
Assert.AreEqual(2, reader["TestColumn"]);
Assert.AreEqual("Dos", reader["TestColumn2"]);
reader.Read();
Assert.AreEqual(3, reader["TestColumn"]);
Assert.AreEqual("Tres", reader["TestColumn2"]);
reader.Read();
Assert.AreEqual(4, reader["TestColumn"]);
Assert.AreEqual("Cuatro", reader["TestColumn2"]);
}
}
}
}
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 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 AtAddedToParameterNameForSqlMobile()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
DbParameter parameter = database.CreateParameter("test", 1);
Assert.AreEqual("@test", parameter.ParameterName);
}
}
}
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));
}
}
}
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 ExecuteSqlStringCommandWithParameters()
{
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[] parameters = new DbParameter[]
{
database.CreateParameter("@Param1", DbType.Int32, 0, 1),
database.CreateParameter("@Param2", DbType.String, 50, "Uno")
};
using (IDataReader reader = database.ExecuteReader(sql, parameters))
{
reader.Read();
Assert.AreEqual(1, reader["TestColumn"]);
Assert.AreEqual("Uno", reader["TestColumn2"]);
}
}
}
}
public void CanCreateSQLSpecificParameters()
{
using (TestResourceFile dbFile = new TestResourceFile(this, "Datastore.sdf"))
{
using (SmartClientDatabase database = DatabaseFactory.CreateDatabase() as SmartClientDatabase)
{
DbParameter param = database.CreateParameter("@Price", SqlDbType.Money, 19, 12.95);
Assert.IsTrue(typeof(SqlCeParameter).IsAssignableFrom(param.GetType()));
SqlCeParameter sqlParam = (SqlCeParameter)param;
Assert.AreEqual(SqlDbType.Money, sqlParam.SqlDbType);
}
}
}
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);
}
}
}
