Connecting ASP.NET 2.0 GridView to Oracle

Connecting ASP.NET 2.0 GridView to Oracle

Lots of people ask about connecting to Oracle with .NET. My answer has always been, there are lots of ways to do it, it's easy. A managed provider for Oracle has shipped with .NET since v1.0, and of course is included in v1.1 and the recently released v2.0 of .NET.

But, some of you are kinda pesky and say, "Show me." So here it is. I'll show you how to wire up one of the cool new controls in ASP.NET 2.0 to an Oracle database back-end. I'll talk about what works and what doesn't. and I'll provide some guidance on doing this in production environments.

Required Materials:

1. an Oracle db.
   If you don't already have access to a test server, you can get yourself a limited, low-end version, kinda like SQL Express. Oracle's is called Oracle Express, and like SQL Express, it's a free download, though I think you need to register with Oracle to get it. Get it here. For the version I got, it is a download of about 216mb.
2. The Oracle client library.
   You need a client-side library to connect to the Oracle server. The server can be remote, but of course the client library has to be installed on the development workstation, during the dev phase, and of course it needs to be on the deployment PC, after you deploy your app. This is also available from Oracle, no charge. Oracle calls it ODAC. Get it here. There are multiple versions available from Oracle, and I am not clear when I want one or the other. So I just grabbed the latest one (10.2.0.1.0), which is also the biggest, at 472mb. The setup program (Oracle Universal installer) looked really nasty. There were a bunch of things in the "Default install" that I absolutely did not want, all kinds of Java stuff I didn't think was necessary. And I could not find a way to NOT install it. Somehow all this java junk was required for OLEDB and .NET clients? I didn't like the looks of it, So I punted that setup and went back to the same page on oracle.com to get the Oracle client v10.1.0.4. This is smaller than the 10.2.0.1 client download, and is also just what I wanted. I ran the setup, accepted the defaults. It installed:
   • The Oracle Data Provider for .NET 10.1.0.4.0 - We're going to use the .NET provider for Oracle that is built-in to .NET 2.0, but I may want to test this later
   • The Oracle Provider for OLE DB 10.1.0.4.0 - You don't need this for this project, but I want to do some comparisons with the fully managed provider later.
   • Oracle Objects for OLE 10.1.0.5.2 - You don't really need this. But I heard this is the only way to get to Oracle AQ from .NET and want to investigate later.
   • Oracle ODBC Driver - Won't need this for this project, but I may test it later.
   • Oracle Services for MTS 10.1.0.4 - we won't need transactions in this project, but I will definitely need this later.
   • Oracle Universal Installer
   • Oracle NET
3. Visual Studio 2005, and the .NET Framework and SDK!
   This example depends on features added in VS2005 and .NET v2.0 (runtime, SDK). You don't absolutely need VS2005 to develop ASP.NET apps against Oracle. You can just use the .NET SDK. But it helps to have Visual Studio.

What to do:

1. Install the Oracle Server if you need to.

2. Install the Oracle client.

3. Configure Oracle NET. Right after installing the oracle client, the installer should ask you (it did for me anyway) to configure Oracle NET. This is pretty simple. It asks you the Oracle Service name - for me this was XEXDB (for a local Oracle Express). Don't make this name up. If you don't know it, go find out what it should be. Then specify the hostname and port (mine was local and 1521 (the default)). Then the userid and password that you set up when installing Oracle XE. At this point, you should be able to test the connection. If it works, good. If not, figure out what you've got wrong. Obviously if you are targetting a remote Oracle then your service name will be different. Your port and hostname will be different. So figure it out and make sure the connection test passes.

4. Open Visual Studio 2005

5. Create a new Web Site
   The designer will open the default webpage, mine opened it in "source view".

6. Insert a SqlDataSource onto the page
   Make sure the designer is in "design view". Hover over the toolbox (left hand side), scroll down to the Data category, and drag and drop a SqlDataSource onto the web page design surface. There will be a pop up that suggests for you to "Configure the Data Source...". Click it.

7. Create a new Data connection
   The first screen in the wizard asks you to Choose your Data Connection. Click the "New Connection..." button.

   • In the "Add connection" dialog, click "Change..." under the Data Source. Select "Oracle Database", and in the dropdown box on the lower part of this dialog, be sure to select ".NET Data Provider for Oracle". The other option is ".NET Data Provider for OLEDB". The obnoxious thing is, the dropdown is only large enough to show the first part of the selection, which is exactly the same for either choice: ".NET Data Provider for O" . Lame!
   • Specify the Server name for Oracle (mine is XEXDB) and the userid and password. You will need to check the box for "Save my password".
   • Click "Test Connection". It should succeed. If not, check your inputs.

8. Click Next... Then, save the connection as "OracleClientXexdb" or something similarly relevant.

9. When you click Next... The system goes out and queries Oracle. It semi-freezes Visual Studio - really lame. I figure it is retrieving all the Oracle table data and metadata ? But I am checking on this. Anyway, in a few seconds, it returns and asks how you would like to retrieve data from the DB. You can specify your own SQL Query, or just select a DB Table and check boxes next to all the columns you want. Select the Employees table, and select a few columns. Or select em all if you want.

10. Click "Advanced..."
    Specify that you want the wizard to Generate INSERT, UPDATE and DELETE statements. And check the box for Optimistic Concurrency, if you want it.

11. Click "OK".. Then Next... You are now asked if you'd like to Test the Query. When I try this, the test fails. There is apparently a problem in the wizard - it does not prepend the schema to the table names in the generated SQL. Also it surrounds the table names in quotes which seems to confuse Oracle. We'll have to fix this up later. I ignore the test failure and click Finish.

12. In the VS designer, click "Source" view. You will see the generated SQL statements for the various operations. Doctor them up to include HR. prefix on each table name. "EMPLOYEES" becomes HR.EMPLOYEES.

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"

    ConflictDetection="CompareAllValues"

    ConnectionString="<%$ ConnectionStrings:OracleXexdb %>"

    ProviderName="<%$ ConnectionStrings:OracleXexdb.ProviderName %>"

    OldValuesParameterFormatString="original_{0}"

    DeleteCommand='DELETE FROM HR.EMPLOYEES WHERE "EMPLOYEE_ID" = :original_EMPLOYEE_ID AND "FIRST_NAME" = :original_FIRST_NAME AND "LAST_NAME" = :original_LAST_NAME AND "EMAIL" = :original_EMAIL AND "PHONE_NUMBER" = :original_PHONE_NUMBER AND "HIRE_DATE" = :original_HIRE_DATE AND "JOB_ID" = :original_JOB_ID AND "SALARY" = :original_SALARY'

    InsertCommand='INSERT INTO HR.EMPLOYEES ("EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "EMAIL", "PHONE_NUMBER", "HIRE_DATE", "JOB_ID", "SALARY") VALUES (:EMPLOYEE_ID, :FIRST_NAME, :LAST_NAME, :EMAIL, :PHONE_NUMBER, :HIRE_DATE, :JOB_ID, :SALARY)'

    SelectCommand='SELECT "EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "EMAIL", "PHONE_NUMBER", "HIRE_DATE", "JOB_ID", "SALARY" FROM HR.EMPLOYEES'

    UpdateCommand='UPDATE HR.EMPLOYEES SET "FIRST_NAME" = :FIRST_NAME, "LAST_NAME" = :LAST_NAME, "EMAIL" = :EMAIL, "PHONE_NUMBER" = :PHONE_NUMBER, "HIRE_DATE" = :HIRE_DATE, "JOB_ID" = :JOB_ID, "SALARY" = :SALARY WHERE "EMPLOYEE_ID" = :original_EMPLOYEE_ID AND "FIRST_NAME" = :original_FIRST_NAME AND "LAST_NAME" = :original_LAST_NAME AND "EMAIL" = :original_EMAIL AND "PHONE_NUMBER" = :original_PHONE_NUMBER AND "HIRE_DATE" = :original_HIRE_DATE AND "JOB_ID" = :original_JOB_ID AND "SALARY" = :original_SALARY'>

    <DeleteParameters>

    <asp:Parameter Name="original_EMPLOYEE_ID" Type="Decimal" />

    <asp:Parameter Name="original_FIRST_NAME" Type="String" />

    <asp:Parameter Name="original_LAST_NAME" Type="String" />

    . . .

    . . .

13. Swap back to design view. Hover to the toolbox and drag over a GridView control. It asks which datasource you want. Select SqlDataSource1 (or whatever you named it). Check the boxes to enable Paging, Deleting, Sorting, and Editing.

14. Ctrl-S to save, F5 to run the web app.

BAM! You're done. You just built an ASP.NET GridView app bound to an Oracle table. It supports paging, and sorting by columns. You can edit the data in the grid and update the database. You can delete rows. And it took all of 90 seconds, not counting the install of Oracle. (we didn't do row inserts, but that's another 20 seconds of work). Wow! Nice work! You deserve a break. Hey, take some time off.

But then reality sets in...

When you come back from holiday, you think you're just going to fixup the web app to add CSS Styles and so on. But then you test it out and find out that:

• editing any one row more than once, results in a constraint violation, which results in an exception in the ADO.NET code that you did not write.
• Your app doesn't catch the exception! Which means you get the yellow-screen of death from ASP.NET. Eewww...

Fie! What's really happening here? The Update() method on the SqlDataSource gets called when the user clicks the Update button in the web page. But you didn't provide any code for that method, right? This is the ASP.NET 2.0 GridView, bound to that SqlDataSource, and so there is no code to write, except for the SQL statement. The SQL Statement is what is causing the exception (due to the trigger and the constraint violation), but if it's not your code throwing, how can you catch that exception? What can you do?

Ah, but there is a way of course. Pay attention now, here's how it works: When the user clicks the Update button, ASP.NET invokes the Update() method on the SqlDataSource. Within that method, the SQL we provided gets sent to the database. That is surrounded in a try..catch to grab and store any exceptions that might occur. After that db call completes, ASP.NET then calls the RowUpdated() method on the GridView. ASP.NET passes the GridViewUpdatedEventArgs argument, which includes any exception, in freeze-dried form. What you can do, is within that event method, check if the exception is non-null. If it is, then you can handle the exception there, and set the ExceptionHandled flag to true. If you don't set this flag, then later the GridView logic will throw, and you'll get the yellow screen of Unhandled Exceptions.

Here's how to do it. Go back to Visual Studio. (You may have to click the Stop button to stop debugging the page). Put the page in design view. Select the GridView. Down in the properties panel (lower right hand side of the VS screen) there is a lightning bolt, click it. This shows the events that are generated by the GridView control. Select the RowUpdated event. Double-click it. In there, add this code:

1 protected void GridView1_RowUpdated(object sender, GridViewUpdatedEventArgs e)

2 {

3 if (e.Exception != null)

4 {

5 string[] s= e.Exception.StackTrace.ToString().Split(new char[]{'\n'});

6 ErrorMessage.Text= String.Format("Exception while Updating: {0}<br/> {1}",

7 e.Exception.Message, s[1]);

8 e.ExceptionHandled = true;

9 }

10 }

That's clever, eh? Rather than catching the exception directly, the SqlDataSource model allows you to catch and handle it out-of-band. In this handler, I just stuff a notice in an <asp:label> control on the page, and move on. Everything's cool. No yellow-screen-of-death.

Over-Constrained

Now, what about the constraint violation? Whoever designed this database inserted a constraint that fires when the job for a person changes more than one time per day. I think. I am no Oracle expert. I am a simple developer. Just doing my job. Is it my problem that the constraint is firing? Well, Yes, it is. See, when the user clicks the Update button, the generated SQL Statement (declared in the UpdateCommand) updates all the fields in the Oracle row, whether the values are changed or not. This includes updating the JOB_ID. Updating the JOB_ID causes the trigger to fire, which logs the job history for each employee in a separate table. (Oracle's sample database is richer than Northwind). And, on the second invocation of that job history logging logic, it causes the table constraint to be violated - the constraint that says, an employee can only get one new job per day.

So what we have here is naive wizard-generated code wreaking havoc on a nice, tight database design. What to do?

To be honest, the wizard-generated SQL statements are not something you should be putting into production on any large and complex app. It works for demos of course, and for relatively simple applications. But it isn't going to work for really complex applications with existing databases with a set of triggers and constraints all set up. The SqlDataSource is essentially a 2-tier design artifact. That is neither good nor bad, but as we know, 2-tier has its place.

When you need to employ greater layering for larger scale, ideally you want to invoke some data-access-layer logic in your application code - something that, for example in this case - could check the old values against the new values and generate a SQL Statement that updates only the changed values. or better, that DAL should invoke Stored Procedures, which are provided by your database people, audited and vetted by experts. This way you could avoid tripping the db constraint when there is no need to do so.

Invoking stored procedures directly from the SqlDataSource is easy to do with the ASP.NET 2.0 GridView control linked to a SqlDataSource. After all, the stored proc is just different SQL, right? But moving to a data-access logic layer but it requires some changes: rather than using a SqlDataSource, you will use an ObjectDataSource. Similar idea, but instead of specifying SQL statements, you will provide a class and methods for Select / Insert / Update / Delete.

Within those methods, you can provide all the required intelligence you need -- validation, optimization, and caching logic if you like. On the optimization side, you could dynamically generate SQL (or better, invoke the stored procedure or Data Access Layer.

But I'm not going to go through all that here. That is left as an exercise for the reader.

What to Watch Out For

While the steps listed here are pretty simple, there are some things to watch out for.

1. If you tweak the generated SQL, and you leave out a parameter, you will get the dreaded ORA-01036: illegal variable name/number or ORA-01008: not all variables bound errors. To really diagnose these things you need to trace the SQL sent through the OCI layer to Oracle. I looked and looked (like I said, I am not an Oracle expert), and found an interesting tool called OCISPY. I was able to get OCISPY to work with sqlplus against Oracle 10g XE, but I could not get it to work for my ASP.NET app. If you get these kinds of errors, the best advice I can offer is to closely examine your Command strings and insure you have the right variable and column names.
2. In order for the System.Data.OracleClient to work, it loads the native OCI libraries. They have to be on your path! So be sure the ORACLE_HOME\bin is on your path, when you start Visual Studio. The WebDev.WebServer.exe process that starts when you press F5, will inherit the environment settings from VS2005.

What Have We Done?

Ok, I think we covered some good ground here. We showed you how to connect an ASP.NET 2.0 GridView to an Oracle XE database. We talked a little about the glitches with the generated SQL - the quotes and lack of explicit schema specification. We fixed up that stuff and everything else just worked. We also talked about naive code from wizards, how to handle exceptions, and a better design for updating enterprise operational databases. We also talked about common gotchas and errors. I'd call that a good day for interop.

Cheers!
-Dino

References

MSDN Mag article on {Sql,Object}DataSource
ASP.NET Quickstart on ObjectDataSource
Commentary from Fredrik on ODS vs SDS
David Hayden on ObjectDataSource and optimistic concurrency
DEVX tutorial covering the ASP.NET GridView (with SQL Server)

OGV2.zip