Calling an Oracle Stored Proc from .NET

Well, this has worked far better than I could have expected. First I went into the TableAdapter for the Work_Stats table and added a query.

Of course, not all is perfect. As you can see, the stored procedure radio buttons are grayed out for some reason in the Table Adapter Query Wizard dialog, but we can work around that by kludging in a simple select query and then fixing the properties later.
Here, I've changed the CommandType from 'Text' to 'StoredProcedure' and chosen the correct SP from the dropdown that appears when you click on the CommandText line. In addition, since I expect a return value, I changed the Execute Mode to scalar. The parameters were all auto-sensed for me, so I didn't have to change anything.

Sometimes my update queries don't work...

I added a query to the Work_StatsTableAdapter(TA) that did an update to Work Stats. The problem was that sometimes the update didn't work because the WHERE clause specified rows that didn't exist in the database. There was no signal back to the calling procedure that update didn't. I decided that we needed a number-of-rows counter passed back. The problem is I don't see a way to do this using Oracle. There's no rowcount that can be returned back immediately after a sql statement. You can use SQL%Rowcount, but that has to be used in a begin-end block and the TA query parser doesn't understand this construct. So I guess I'm stuck trying to implement a stored proc and calling it.

My stored proc on Oracle looks like this:

CREATE OR REPLACE PROCEDURE UPDATE_WORK_STATS(
P_COUNT IN WORK_STATS.COUNT%TYPE,
P_USER_ID IN WORK_STATS.USER_ID%TYPE.
P_WORK_DATE IN WORK_STATS.WORK_DATE%TYPE,
P_WORKTYPE IN WORK_STATS.WORKTYPE%TYPE,
P_COMM_MTHD IN WORK_STATS.COMM_MTHD%TYPE,
P_ROWCOUNT OUT NUMBER) AS
BEGIN
UPDATE WORK_STATS SET COUNT=P_COUNT
WHERE USER_ID = P_USER_ID
AND WORK_DATE = P_WORK_DATE
AND WORKTYPE = P_WORK_TYPE
AND COMM_MTHD = P_COMM_MTHD;
P_ROWCOUNT := SQL%ROWCOUNT;
COMMIT;
END UPDATE_WORK_STATS;

Now let's see if I can get VS to invoke this SP and give me back a rowcount.

I wish I'd started this blog earlier in the project. Now I'm going to have to reconstruct where I've gotten to so far.

I first started out creating a database for this project. I sketched out what we'd need for tables and data elements. I had our Oracle DBAs create me a database and I created the tables.

Next, I used Visual Studio .Net 2005 (VS) to create a strongly typed data access layer(DAL). As this was a simple system, I decided not to go with a separate business logic layer. I looked at the tutorial in MSDN for this and was overwhelmed by the arcane syntax and also by the problem with the parameterless constructor and problems with object datasource and strongly-typed datasets. I especially did not like the prospect of having to go and edit generated code.

The other reason for my decision is that I'm going to jump into this without a fully developed data model and expect to have to change things as I go along. I'm figuring this will be easier if there are fewer layers.

In creating the DAL for this project I am beset by some difficulties working with the Oracle database. I was unable to get some of my queries working as stored procedures.

The main query for the dataview grid takes data from my WORK_STATS table which looks like this:
USER_ID
WORK_DATE
WORKTYPE
COUNT
ENTRY_DATE

So there's one row per worktype per day. How are we going to display that in a grid that looks like this?

WORKTYPE	MON	TUE	WED	THU	FRI
Dollars	32	87	32	34	19
Donuts	322	827	342	314	139

The answer is to develop a pivot table query in Oracle. Thanks to help from Ask Tom,
I've come up with this little jewel:

SELECT WORKTYPE_DESC,
MAX(decode(WDAY,'MON',CNT,NULL)) MON,
MAX(decode(WDAY,'TUE',CNT,NULL)) TUE,
MAX(decode(WDAY,'WED',CNT,NULL)) WED,
MAX(decode(WDAY,'THU',CNT,NULL)) THU,
MAX(decode(WDAY,'FRI',CNT,NULL)) FRI,
MAX(WORKTYPE) WORKTYPE
FROM (SELECT WORKTYPE_DESC, TO_CHAR(WORK_DATE,'DY') WDAY,
SUM(COUNT) CNT, MAX(WORKTYPE) WORKTYPE
FROM WORK_STATS_VW
WHERE WORK_DATE BETWEEN 'FirstDate' AND 'LastDate'
GROUP BY WORKTYPE_DESC, TO_CHAR(WORK_DATE,'DY'))
GROUP BY WORKTYPE_DESC

This would definitely not work in VS as the add query parser
would not allow it. I created a separate class under AppCode and added a function which invoked this query against the Oracle db.

The website overview

The objective of this web site is to deliver a report of how many work tasks were completed each day in various work categories (worktypes). Data entry will be in a dataview grid set up such that users can enter a week's worth of data (5 entries, Monday thru Friday) at a time for each worktype that they are authorized to enter data for. The back-end database is Oracle 9i, but soon to be upgraded to 10g.

The travails of a web developer in the .Net 2005 environment

This blog details my journeys (the rat in the maze) in trying to set up my first .NET 2005 website. I wanted to write down all the techniques I considered so I'd have a record of what didn't work as well as what I finally decided to go with.