public void SetUpMocks()
{
provider = MockRepository.GenerateMock<IDbProvider>();
IDbConnection connection = MockRepository.GenerateMock<IDbConnection>();
provider.Stub(x => x.CreateConnection()).Return(connection).Repeat.Once();
// Creating a query (setting DbProvider property)
// will call new DbParameters(IDbProvider), which is a real pain to mock.
// to store the declared parameters.
command = MockRepository.GenerateMock<IDbCommand>();
//This IDbCommand is used as a container for the underlying parameter collection.
provider.Stub(x => x.CreateCommand()).Return(command).Repeat.Once();
//Create a real instance of IDbParameters to stored the declared parameters
IDbProvider realDbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");
IDbParameters dbParameters = new DbParameters(realDbProvider);
//Pass real instance into mock instance.
command.Stub(x => x.Parameters).Return(dbParameters.DataParameterCollection).Repeat.Once();
provider.Stub(x => x.CreateCommand()).Return(command).Repeat.Once();
// done with init of DbParameters mock/stubbing
}
public void DeclareParameterAfterCompile()
{
TestAdoOperation operation = new TestAdoOperation();
operation.DbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");
operation.Sql = "select * from table";
operation.Compile();
IDbParameters parameters = new DbParameters(operation.DbProvider);
Assert.Throws<InvalidDataAccessApiUsageException>(() => operation.DeclaredParameters = parameters);
}
public void DeclareParameterAfterCompile()
{
TestAdoOperation operation = new TestAdoOperation();
operation.DbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");
operation.Sql = "select * from table";
operation.Compile();
IDbParameters parameters = new DbParameters(operation.DbProvider);
operation.DeclaredParameters = parameters;
}
public void TooFewParameters()
{
TestAdoOperation operation = new TestAdoOperation();
operation.DbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");
operation.Sql = "select * from table";
IDbParameters parameters = new DbParameters(operation.DbProvider);
parameters.Add("name");
operation.DeclaredParameters = parameters;
operation.Compile();
Assert.Throws<InvalidDataAccessApiUsageException>(() => operation.ValidateParams(null));
}
public void SqlClient()
{
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");
DbParameters dbParameters = new DbParameters(dbProvider);
dbParameters.Add("p1", DbType.String);
IDataParameter parameter = dbParameters[0];
Assert.AreEqual("@p1", parameter.ParameterName);
dbParameters.SetValue("p1", "foo");
object springParameter = dbParameters.GetValue("p1");
Assert.IsNotNull(springParameter);
Assert.AreEqual("foo", springParameter);
}
/// <summary>
/// Creates the a db parameters collection, adding to the collection a parameter created from
/// the method parameters.
/// </summary>
/// <param name="name">The name of the parameter</param>
/// <param name="dbType">The type of the parameter.</param>
/// <param name="size">The size of the parameter, for use in defining lengths of string values. Use
/// 0 if not applicable.</param>
/// <param name="parameterValue">The parameter value.</param>
/// <returns>A collection of db parameters with a single parameter in the collection based
/// on the method parameters</returns>
protected IDbParameters CreateDbParameters(string name, Enum dbType, int size, object parameterValue)
{
IDbParameters parameters = new DbParameters(DbProvider);
parameters.Add(name, dbType, size).Value = parameterValue;
return parameters;
}
private IDbParameters CreateDbParameters(object[] parameterValues)
{
IDbParameters parameters = null;
if (parameterValues != null)
{
parameters = new DbParameters(DbProvider);
foreach (object parameterValue in parameterValues)
{
parameters.Add(parameterValue);
}
}
return parameters;
}
private IDbParameters CreateDbParameters(IList parameterList)
{
IDbParameters parameters = null;
if (parameterList != null)
{
parameters = new DbParameters(DbProvider);
foreach (IDbDataParameter parameter in parameterList)
{
parameters.AddParameter(parameter);
}
}
return parameters;
}
private IDbParameters CreateDbParameters(IDbDataParameter parameter)
{
IDbParameters parameters = new DbParameters(DbProvider);
parameters.AddParameter(parameter);
return parameters;
}
public void TooFewParameters()
{
TestAdoOperation operation = new TestAdoOperation();
operation.DbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");
operation.Sql = "select * from table";
IDbParameters parameters = new DbParameters(operation.DbProvider);
parameters.Add("name");
operation.DeclaredParameters = parameters;
operation.Compile();
operation.ValidateParams(null);
}
public void ExecuteNonQueryText()
{
int age = 18;
int counter = 0;
String sql = String.Format("insert into TestObjects(Age, Name) VALUES ({0}, '{1}')",
age++, "George" + counter++);
adoOperations.ExecuteNonQuery(CommandType.Text, sql);
sql = "insert into TestObjects(Age,Name) values (@Age,@Name)";
//One liners are hard due to no standard 'fallback' to use of '?' for property
//placeholders. Providers that use named parameters must always use named
//parameters in SQL string.
//NamedParameterAdoOperations ...
//More portable IDbDataParameterCollection implemenation.
// -> IDbParameters
//Functionality of .NET 2.0 DbParameterCollection + common helper methods that
//are commonly found (still) in subclasses.
//How to create parameter collections?
//1. Get as much milage/portabiliyt out of basic provider interface and
// IDbDataParameter/IDataParameterCollection
// DbParameter/DbParameterCollection
// a. Must use only base DbType (can't cast as no shared base enumeration)
// b. Must use parameter prefix
// c. CLR null and DBNull.Value mapping.
// c. IsNullable is not writable in IDbDataParameter interface (1.1 only)
// c2. SourceColumnNullMapping?
// d. No convenient Add( parameter data ) methods in
// base Parameter Collection classes
// despite prevalence in provider implementations.
// d1. re-use of parameters - parameters are aware if they have been added to
// a collection of another command object.
// e. verbose
IDbParameters parametersCreated = new DbParameters(dbProvider);
IDbDataParameter p = dbProvider.CreateParameter();
p.ParameterName = "@Name";
p.DbType = DbType.String;
p.Size = 12;
p.Value = "George" + counter++;
parametersCreated.AddParameter(p);
IDbDataParameter p2 = dbProvider.CreateParameter();
p2.ParameterName = "@Age";
p2.DbType = DbType.Int32;
p2.Value = age++;
parametersCreated.AddParameter(p2);
adoOperations.ExecuteNonQuery(CommandType.Text, sql, parametersCreated);
//2. Use IDbParameters abstraction.
// e. less verbose...
IDbParameters parameters = adoOperations.CreateDbParameters();
parameters.Add("Name", DbType.String, 12).Value = "George" + counter++;
parameters.Add("Age", SqlDbType.Int).Value = age++;
//Better to use date example...people like to pick provider specific subtype..
//parameters.AddWithValue("Age", age++);
//parameters get 'cloned' before association with command, output values
//are re-associated, and so the parameter collection is re-usable.
adoOperations.ExecuteNonQuery(CommandType.Text, sql, parameters);
}