Data Portal Exceptions with Stored Procedures - CSLAGen creating wrong code?

g18c posted on Monday, October 22, 2007

Hi, i have a problem with the DataPortal_Update(), it is throwing an exception on and the stack trace it is complaining about this line:

m_date = (SmartDate) cmd.Parameters["@Date"].Value;

The object is generated using the excellent tool CSLAGen. Full code is below:

using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = (SqlConnection)ApplicationContext.LocalContext["dpConnection"];
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "AddHoursItem";
DoInsertUpdate(cmd);
ApplicationContext.LocalContext["dpCommand"] = cmd;
onInsertPre(this, EventArgs.Empty);
cmd.ExecuteNonQuery();
ApplicationContext.LocalContext["dpCommand"] = cmd;
ApplicationContext.LocalContext["dpConnection"] = cmd.Connection;
onInsertPost(this, EventArgs.Empty);
m_lastChanged = (Byte[]) cmd.Parameters["@NewLastChanged"].Value;
m_employeeID = (Guid) cmd.Parameters["@EmployeeID"].Value;
m_activityID = (Guid) cmd.Parameters["@ActivityID"].Value;
m_date = (SmartDate) cmd.Parameters["@Date"].Value;
MarkOld();
}

By removing the lines in the generated source file it works again.

/*m_employeeID = (Guid) cmd.Parameters["@EmployeeID"].Value;
m_activityID = (Guid) cmd.Parameters["@ActivityID"].Value;
m_date = (SmartDate) cmd.Parameters["@Date"].Value;*/

My question is, should CSLAGen/Codesmith be generating these lines? They dont make sense to me, and seem surplus to requirements. Any other people using CSLAGen here?

The second problem is when i update an existing rows Date column, and errors are thrown (both errors 65001 and 65002). Editing any other than the date column works ok.

CREATE PROCEDURE [UpdateHoursItem]
@EmployeeID uniqueidentifier,
@ActivityID uniqueidentifier,
@Date smalldatetime,
@StdHours decimal(4, 2),
@OvrTmeHrs decimal(4, 2),
@LastChanged timestamp,
@NewLastChanged timestamp OUTPUT
AS
BEGIN
SET NOCOUNT ON
/* Check for object existance
* Error message 65001 must be added before RAISERROR is executed:
* sp_addmessage 65001, 16, [''%s' object doesn't exists in database. It's probably removed by another user.']*/
IF NOT EXISTS (SELECT * FROM [Hours]
WHERE [EmployeeID] = @EmployeeID AND
[ActivityID] = @ActivityID AND
[Date] = @Date)
RAISERROR (65001, 16, 1, 'HoursItem')

/* Check for timestamp match
* Error message 65002 must be added before RAISERROR is executed:
* sp_addmessage 65002, 16, [''%s' object is modified by another user.']*/
IF NOT EXISTS (SELECT * FROM [Hours]
WHERE [EmployeeID] = @EmployeeID AND
[ActivityID] = @ActivityID AND
[Date] = @Date AND
[LastChanged] = @LastChanged)
RAISERROR (65002, 16, 1, 'HoursItem')

/* Update object in Hours */
UPDATE [Hours]
SET
[StdHours] = @StdHours,
[OvrTmeHrs] = @OvrTmeHrs
WHERE [EmployeeID] = @EmployeeID
AND [ActivityID] = @ActivityID
AND [Date] = @Date
AND [LastChanged] = @LastChanged
/* Return new timestamp value */
SELECT @NewLastChanged = [LastChanged]
FROM [Hours]
WHERE [EmployeeID] = @EmployeeID AND
[ActivityID] = @ActivityID AND
[Date] = @Date
END
GO

The update procedure for the business object is below:

using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = (SqlConnection)ApplicationContext.LocalContext["dpConnection"];
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "UpdateHoursItem";
cmd.Parameters.AddWithValue("@LastChanged", m_lastChanged);
DoInsertUpdate(cmd);
ApplicationContext.LocalContext["dpCommand"] = cmd;
onUpdatePre(this, EventArgs.Empty);
cmd.ExecuteNonQuery();
ApplicationContext.LocalContext["dpCommand"] = cmd;
ApplicationContext.LocalContext["dpConnection"] = cmd.Connection;
onUpdatePost(this, EventArgs.Empty);
ApplicationContext.LocalContext.Remove("dpCommand");
m_lastChanged = (Byte[]) cmd.Parameters["@NewLastChanged"].Value;
MarkOld();
}

private void DoInsertUpdate(SqlCommand cmd)
{
cmd.Parameters.AddWithValue("@EmployeeID", m_employeeID.Equals(Guid.Empty) ? (object)DBNull.Value : m_employeeID);
cmd.Parameters.AddWithValue("@ActivityID", m_activityID.Equals(Guid.Empty) ? (object)DBNull.Value : m_activityID);
cmd.Parameters.AddWithValue("@Date", m_date.DBValue);
cmd.Parameters.AddWithValue("@StdHours", m_stdHours);
cmd.Parameters.AddWithValue("@OvrTmeHrs", m_ovrTmeHrs);
cmd.Parameters.Add("@NewLastChanged", SqlDbType.Timestamp).Direction = ParameterDirection.Output;
}

The strange thing is if i only change the stdhours or ovrtmehrs fields and hit save, all is well and the values are written to the database. Its been a long day and my brain has stopped working, could anyone be kind enough to suggest where i may look next?

If anyone has experience with CSLAGen is the code being generated properly?

Many thanks in advance,

Chris