About Using Parameters
The construct method in the following sample code is for CompositeParameter. You can create a new parameter with the methods:
public CompositeParameter(string parameterName, object value)
public CompositeParameter(string parameterName, object value, CompositeDbType dbType)
public CompositeParameter(string parameterName, CompositeDbType dbType)
• The current argument name is parameterName. This must not be empty.
• The value of the current argumentvalue.
• The type to assign to the current parameter is dbType. The value of dbType must be one of the CompositeDbType objects.
Methods for Adding Parameters
There are several ways to add parameters to the CompositeParameterCollection object. In the following sample, Method A uses a prepared SQL statement as the cmd.CommandText. Method B illustrates the binding of a placeholder with a parameter. This method can be used except when working with the following data types: Clob, Date, Time, and Timestamp.
((CompositeParameterCollection)cmd.Parameters).Add("@ProductID", 1111);
Convert the cmd.Parameters object to the CompositeParameterCollection type. After converting the Add method to declare a new parameter name:
'public CompositeParameter Add(string parameterName, object value)'
Method C uses the following JDBC style SQL statement:
update products set ProductName=? where ProductID=?
The question mark (?) is a placeholder without an appended name string. The placeholder is bound with the corresponding parameter as follows:
cmd.Parameters.Add(new CompositeParameter("?ProductName", "TDV"));
cmd.Parameters.Add(new CompositeParameter("?ProductID", 1111));
The public CompositeParameter Add(CompositeParameter value) method is used to add a parameter. The first parameter is bound to the first placeholder, and the last parameter is bound to the last placeholder.
The following sample code contains four possible methods (A through D) for implementing parameters. The sample uses the ADO.NET placeholder characters of ? and @.
public void TestParameter()
{
CompositeConnection conn = GetConnection();
try
{
//Method A
CompositeCommand cmd = conn.CreateCommand();
cmd.CommandText = "delete from products where ProductID=@ProductID";
cmd.Parameters.Add("@ProductID", CompositeDbType.INTEGER);
cmd.Parameters[0].Value = 1111;
int cnt = cmd.ExecuteNonQuery();
//Method B
cmd.Parameters.Clear();
cmd = new CompositeCommand("insert into products(ProductID,ProductName) values(@ProductID,@ProductName)", conn);
((CompositeParameterCollection)cmd.Parameters).Add("@ProductID", 1111);
((CompositeParameterCollection)cmd.Parameters).Add("@ProductName", "Discovery");
cnt = cmd.ExecuteNonQuery();
//Method C
cmd.Parameters.Clear();
cmd.CommandText = "update products set ProductName=? where ProductID=?";
cmd.Parameters.Add(new CompositeParameter("?ProductName", "TDV"));
cmd.Parameters.Add(new CompositeParameter("?ProductID", 1111));
cnt = cmd.ExecuteNonQuery();
//Method D
cmd.Parameters.Clear();
cmd.CommandText = "select ProductName from products where ProductID=?ProductID";
cmd.Parameters.Add(new CompositeParameter("?ProductID", 1111));
String ProductName = (String)cmd.ExecuteScalar();
if (!ProductName.Equals("TDV"))
Console.WriteLine("error happen.");
}
catch (Exception ex)
{
throw ex;
}
}