Using Microsoft .NET and C# with Oracle 9i - Creating a Command
(Page 3 of 4 )
In this section, we look at creating the main workhorse class and its attributes and capabilities. The
OracleCommand class has two main functions. First, you can give it a simple SQL query string and execute that command on the database. Secondly, you can use the
OracleCommand object to execute a stored procedure on the database.
The query string is the simplest use of this command and we will look at it first.
First, create the command by using the factory method CreateCommand() method on the OracleConnection object.
OracleCommand cmd = conn.CreateCommand();
This creates a command object attached to the conn connection. Alternatively, the command object can be created using its own constructor and then set the Connection field later:
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
After creating the command, we must set the CommandText attribute to give the object the actual string containing the SQL query it will run on the database. The code below also sets the CommandType attribute, which selects whether CommandText is a text command or the name of a stored procedure (this defaults to Text):
String query = “select * from users”;
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
The last step to complete is to run the command on the database and catch the results in an OracleDataReader object.
OracleDataReader reader = cmd.ExecuteReader();
This executes the command on the database and puts the results in reader. There is an important distinction between queries that read data and those that only update data. When your command reads data, you use the ExecuteReader() method to get this data back. However, when all your command does is insert or update data with nothing meaningful to return, you would use the ExecuteNonQuery() method which we will cover later.
The other mode that OracleCommand can operate in is for executing a stored procedure. This process works somewhat differently than when executing a straight text command.
Everything is the same as above until we set the command text string. In this case, the CommandText attribute is set to the name of the stored procedure. In the following case, this is a stored procedure to get back users in a certain range of user_id’s, called GetUsers. Notice also how the following code sets the CommandType attribute to StoredProcedure:
cmd.CommandText = “GetUsers”;
cmd.CommandType = CommandType.StoredProcedure;
After setting the stored procedure name and command type, we must add parameters to the OracleCommand object. This involves creating multiple OracleParameter objects and adding them to the OracleCommand’s Parameter member. This is done in the following manner:
cmd.Parameters.add(new OracleParameter(“start_user”,OracleDbType.Int, 202));
This code creates a parameter to the stored procedure GetUsers called start_user which is of type Int and has the value 202. This object is then added to the OracleCommand’s Parameters collection that stores all of the different parameters to the stored procedure. There are several things to note here. First of all, when creating multiple parameters, the number of parameters must match the number of parameters expected by the stored procedure. It is also a good idea to match up order so that parameter objects are added to the Parameters collection in the same order that they appear in the stored procedure definition to make debugging easier.
After adding the second parameter, ExecuteReader is called to get the results from the database.
cmd.Parameters.add(new OracleParameter(“end_user”, OracleDbType.Int, 210));
OracleDataReader reader = cmd.ExecuteReader();
This has shown the basics of reading data out of the database. The next step is to take that data and turn it into a .NET DataSet that can be bound to a data driven control or iterated over and changed to later update the database.
Next: Creating a DataSet from and Oracle Database >>
More .NET Articles
More By Michael Swannson
/ 41 
