Understanding O.O.P. Documentation in .Net

Parameters in VisualStudio.Net

By David M. Woods
Published October 19, 2006, 3:13 pm in Software.

Command Text revised

Now we need to revise the CommandText property of the command object. Simply substitute the parameter names. 

   MyCmd.CommandText = "select * from MyTable where ID = @ID "

Here's an example of a command object with multiple parameters, where one of them is a 2-character string representing a state code: Note: do NOT use quotes around non-numeric parameters: 

   MyCmd.Parameters.Add("@ID", SqlDbType.int)
   MyCmd.Parameters.Add("@ST", SqlDbType.char, 2)
   MyCmd.CommandText = "select * from MyTable where ID = @ID and State=@ST"

Here's an example of a command text that updates data:

   "update MyTable set State=@ST where ID=@ID "

Finally, an example of a command object that executes a stored procedure called "MyProc" with 2 parameters. Note that the "CommandType" property must be thusly specified (it's default value is "text", which is why we have not had to worry about it before): 

   MyCmd.CommandText = "MyProc @ID, @ST "
   MyCmd.CommandType = CommandType.StoredProcedure

Have value, will execute

All we have done thus far is SET UP the command object with some parameters - we still haven't executed it yet. But before we execute it, we must give all parameters a value. We do that with the "Value" property of the SqlParameter class.

Fortunately, it is not necessary to create a separate object from the Parameter class in order to access this property, or any other property. Rather, we can access it directly from its collection class "parent." If we want to set the "ID" property to "12345" and the "ST" property to "TX", in C#, it's done like this: 

   MyCmd.Parameters["@ID"].Value = 12345;
   MyCmd.Parameters["@ST"].Value = "TX";

In VB:

   MyCmd.Parameters.Item("@ID").Value = 12345;
   MyCmd.Parameters.Item("@ST").Value = "TX";

Now run it. If it's a non-recordset-returning query:

   MyCmd.ExecuteNonQuery();

Updating data with a DataAdapter

When creating command objects to be used with a DataAdapter to insert, update, or delete records from a database, you will need to use the 4-parameter overloaded version of the Add method to specify the database table's field name. Here is a syntactical diagram: 

   ...Parameters.Add("@ParamName", SqlDbType.Type, Length,  "FieldName")

Output parameters

All of the parameters we have used so far have been "input" parameters. Many database systems, such as Sql Server, also support "output" and "return value" parameters, which can be used to return non-tabular data fields.

To set up your command object to use them, you will need to set the "Direction" property of the Parameter class. The Direction property is of type "ParameterDirection", which is an enumerator; its important value options are "Input", "Output", and "ReturnValue". The default is "Input".

First, create a parameter object. Here's a syntactical diagram In VB:

   dim Param as New SqlParameter("@ParamName", SqlDbType.Type, Length)
   Param.Direction = ParameterDirection.Direction
   CommandObject.Parameters.Add(Param)

After the command object is executed, use the "Value" property of the Parameter class to get the value of the output parameters. 

   Variable = CommandObject.Parameters.Item("@Param").Value

Pages: 1 2

You're viewing a selection from Technology.

You may subscribe to Entries (RSS) and Comments (RSS).