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