This topic describes how to create and use Oracle stored procedures and functions with dotConnect for Oracle in the define typed DataSets.

If a procedure returns a result set, this recordset may be used to generate a data table in the typed data set. To perform this, select either the get_all_depts_proc procedure or the get_all_depts_func function in the DataSet Wizard:

Then proceed to the DataSet generation and place the generated data set instance, say, dataSet1, onto the form. After that, the procedure result set can be used just like usual data tables. For example, you can fill a data grid with this table:

dataSet1.Fill();
DataTable dt = dataSet1.Tables[0];
dataGridView1.DataSource = dt;

DataSet1.Fill()
Dim dt As DataTable = DataSet1.Tables.Item(0)
DataGridView1.DataSource = dt

Besides generation of data tables, stored procedures may be used to set the update/insert/delete commands in the DataSet Wizard. This can be useful, e.g., if some data validation is made in the procedure.

Perform the following to set the dept_insert stored procedure as the insert command for the typed data set:

1. Start the DataSet Wizard, select the Dept table and proceed to the next step of wizard
2. Click the Configure commands button:

3. On the Update Commands tab, click the Edit... button:

4. Change the Type list box to StoredProcedure and select the dept_insert procedure in the Object Name list box:
5. Proceed to the Parameters tab. Click Yes on confirmation window. The parameters that are used for insert command should be regenerated.
6. For each parameter, select the corresponding column in the Source Column list box
7. Click OK and proceed to the data set generation

After that the dept_insert procedure will be used as the insert command of the typed Dept data table. For example, the following code, which adds a new row to the Dept table, implicitly calls dept_insert:

dataSet1.DEPT.AddDEPTRow(20, "Sales", "NEW YORK");
dataSet1.Update();

DataSet1.DEPT.AddDEPTRow(20, "Sales", "NEW YORK")
DataSet1.Update()

See Also

Working with Oracle Stored Procedures  | Stored Procedures - General Information  | Using Stored Procedures via the OracleCommand class  | Using Package Procedures