How To Insert/Update/Delete Data in an Oracle Database From a WinForms.NET Application 

This tutorial on using Oracle with Visual Studio WinForms.NET continues on from our first tutorial on Oracle database access from C#. In this tutorial we're gong to look at how to use the grid control to display data, update it in situ and then save the changes to the database.

Create your WinForms.NET Project

1. Start Visual Studio 2010 (Start -> All Programs -> Microsoft Visual Studio 2010)
2. Select File->New Project from the Visual Studio menu
3. Select the “Windows Forms Application          Visual C#”  template in the New Projects dialog box and name your application “AdvOracleWinFormsApp”. Accept the defaults click “OK”.
   
   
   
   
4. The next step is to add a reference to the ODP.NET Provider DLL. To do this select “Add Reference…” from the Project menu option and then select the ".NET" tab. The screen should look like this:
   
   
   
   Select “Oracle.DataAccess” from the list of components. ODP.NET is part of this. You can either scroll up and down the list until you find it or just press "O" and this component will jump to the top of the screen. Click the “OK” button to add it as a reference to the project.
   

Oracle Tips & Tricks
to SKYROCKET Your Career!

If you're not already a subscriber to our newsletter you're missing out on a myriad of tips and tricks you need to become a better, faster, smarter developer. Subscribe now and ignite your career.

5. To check Oracle.DataAccess has been added as a component, use the Solution Explorer tool (View->Solution Explorer) and expand the References node. 
   
   
   
   
6. Next we'll add a DataGridView and a button control from the Toolbox to Form1 - the Windows Form created automatically when you create a new project. Ignore the DataGridView Tasks window that pops up - we DO NOT want to choose a data source here! Aslo, for this tutorial we're not going to worry much about the size or position of the controls, although we would for a real project.
   
   
   
   
7. We now have a data grid and a button on the form but what do we do with them? Well, as we'll see shortly, the grid will be used to display our data and will enable us to change the data in situ. We also need to be able to write those changes back to the database and that's where our button comes in.
   
   The next step is to change the name of our DataGridView control to "departments" to make the code easier to understand. This is done by pressing <F4> to bring up the properties window and then changing the (Name) property.
   
    We also need to give the button a meaningful label - "Save". To do this, change the text and name properties to "Save" (without the quotes).
   
   
   
   
8. Now we can add the code to write changes back to the database. Double clicking on the “Save" button icon on the form brings up the code for the form and positions the cursor on the button click event handler (called save_Click in our example).
   
   
   
   
9. Before we write this code though we need to add the code to display the data in the first place. Where does this code go? At the start of the form instantiation routine - the public block called Form1. That way as soon as the form loads our grid will be populated automatically without the user having to do anything.
   
   So, how do we get data from our database? As we saw in the first tutorial, there are 3 steps: first, we connect to the database, next we send our SQL query, then we fetch and display the results. 
   
   To achieve this In C#, we also need to add the following declarations to the Form1() block after the call to InitializeComponent:
   
   string sql = "select depertmant_id, department_name from departments";
   OracleConnection conn = new OracleConnection();
   OracleCommand cmd ;
   OracleCommandBuilder cb;
   
   Don't worry about the red underlining - we'll fix that in a minute!
   
   
10. After the declarations we define the database connection string:
    
    conn.ConnectionString =
    "User Id=HR;Password=HR;Data Source=XE";
    
    In this case our data source is a 10g XE database and "XE" has been defined in our TNSNAMES.ORA file
    
    
11. Now we can add the code to open the connection to the database,  query it and retrieve and display the results.
    
    conn.Open();
    cmd = new OracleCommand(sql,conn);
    cmd.CommandType = CommandType.Text;
    da = new OracleDataAdapter(cmd);
    cb = new OracleCommandBuilder(da);
    ds = new DataSet();
    da.Fill(ds);
    departments.DataSource = ds.Tables[0];
    
    
12. Now let's fix the compilation errors! We need to add the following 2 lines to the "using" section at the beginning of the module:
    
    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;
    
    And the following 2 declarations to the Form1 class:
    
    private DataSet ds;
    private OracleDataAdapter da
    
    These need to be declared at this level because they will also be used in the event handler for our button control.
    
    
13. Speaking of event handlers, we can now write the code for this. All the event handler needs to do is write the changed dataset back to the database which we can do in 1 line:
    
    da.Update(ds.Tables[0]);
    
    However, even though this is just a tutorial and we're not building a robust enterprise-class application, we ought to add some error handling to trap constraint violations and other exceptions.
    
    If these errors were not trapped they would cause our form to stop with an unhandled exception - which isn't very professional. So let's wrap our data set update command in a try-catch-finally block and deal with any exceptions. We should end up with something like this:
    
    private void save_Click(object sender, EventArgs e)
            {
    
                try
                {
                    da.Update(ds.Tables[0]);
                }
                catch (OracleException oe)
                {
                    switch (oe.Number)
                    {
                        case 1:
                            MessageBox.Show
                           ("This department id already exists");
                            break;
                        case 1400:
                            MessageBox.Show
                           ("Department name must be speciifed");
                            break;
                        default:
                            MessageBox.Show
                    ("Database error: " + oe.Message.ToString());
                            break;
                    }
                }
                finally
                {
                }
            }
    
    
14. Now let's run our form. We should see something like the following:
    
    
    
    Notice that the user can resize the columns and the rows but not the grid itself. We could adjust the properties of the grid control (either at design time or programmatically) or we could alias the column names but for the purpose of this tutorial what we have is good enough.
    
    
15. We have one last task - to test our form, including the error handling and modifying data. Let's start by inserting a new record.
    
    
    1. To do this we need to scroll down to the bottom of the grid until we see a record with a * in the left most (empty) column.
    2. Then we can enter a new record. We'll use the following values: 400, Library
       and repeat with another new record: 500, Catering
       And save our changes by clicking on the save button.
       
       
       
    3. We can check the new departments have been added to the database by using SQL*Plus or some other tool to query the database or we can just close and restart our form.
       
       
16. Now let's delete the last record added (department 500), save this change and again check that the database has been updated.
    
    
17. To test updates of existing data, let's change the name of department 400 to catering. After saving the change we can again check that the database has been updated by using SQL*Plus or re-starting our form.
    
    
18. Having tested that our form works for normal operations all we need to do now is check the error handling. There are 2 exceptions that we specifically check for - primary key constraint violation (i.e. duplicate department id) and not null constraint violation (i.e. we must specify a department name). So let's test these - first let's add a new record with a department id that already exists (10 in this case) and click save. We should see a message box pop up like this:
    
    
    
    Finally let's add a new record with an id of 1000 and no name. This should cause the following message box to pop up:
    
    

Return to Oracle and .NET overview