Problem Error Handling in Stored Statement?

Question

Here is my stored procedure on updating records :

ALTER
PROCEDURE [dbo].[sp_UpdatetoShipped]
(
@Date datetime,
@SerialNumber
varchar(50),
@User
varchar(50),
@WorkWeek
varchar(50) 
)
AS
BEGIN

UPDATE dbo.FG_FILLIN SET Status='SHIPPED',DateModified=@Date,ModifiedBy=@User,WorkWeek=@WorkWeek where (Status='KITTED')and SerialNumber=@SerialNumber

END

Then this is my DAL:

public int UpdatetoShipped(FillinEntity fin) 
{
SqlConnection conn = new SqlConnection(connStr); 
conn.Open();
SqlCommand cmd = new SqlCommand("sp_UpdatetoShipped", conn); 
cmd.CommandType =CommandType.StoredProcedure; 
try
{
cmd.Parameters.Add("@SerialNumber", SqlDbType.VarChar,50).Value = fin.SerialNumber; 
cmd.Parameters.Add("@WorkWeek", SqlDbType.VarChar, 50).Value = fin.WorkWeek; 
cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Now.ToString(); 
cmd.Parameters.AddWithValue("@User", fin.ModifiedBy); 
return cmd.ExecuteNonQuery(); 
}
catch
{
throw; 
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}

My BLL:

public int UpdatetoShipped(FillinEntity fin) 
{
DAL pDAL = new DAL(); 
try
{
return pDAL.UpdatetoShipped(fin); 
}
catch
{
throw; 
}
finally
{
pDAL = null; 
}
}

And MY UI:

string filepath2 = txtPath2.Text; 
Stream stream2 = new FileStream(filepath2, FileMode.Open, FileAccess.Read, FileShare.Read); 
ExcelMapper<FillinEntity> exceltoshipped = new ExcelMapper<FillinEntity>(); 
IExcelParser excelParser2 = new ExcelReaderExcelParser(stream2); 
IExcelRowMapper<FillinEntity> mapper2 = new ShippedRowMapper(); 
IEnumerable<FillinEntity> fillin2 = exceltoshipped.ListAll(excelParser2, mapper2);
int intResult = 0; 
BAL pBAL = new BAL();
try
{
foreach (FillinEntity fin in fillin2) 
{
fin.ModifiedBy = loggedUser;
intResult = pBAL.UpdatetoShipped(fin);
}
if (intResult > 0) 
MessageBox.Show("Record Updated Successfully."); 
else
MessageBox.Show("Record couldn't Updated Check Serial"); 
}
catch (Exception ee) 
{
MessageBox.Show(ee.Message.ToString()); 
}
finally
{
pBAL =null; 
}

My problem is it always says updated succussfully. But if i updated it again as duplicate update i want to show serial is already updated.

There's a lot wrong with your approach in the SQL alone, including naming convention of the stored proc, and how you run the update. There is no checking in the stored proc for whether the row is already updated for that serial number either, so I'd rethink how you want to do this. — user114600
– user114600, Commented Aug 12, 2010 at 8:17

Rob · Accepted Answer · 2011-03-09 10:38:01Z

1

The key change you need to make is to the following line of SQL from your stored procedure:

UPDATE dbo.FG_FILLIN 
SET   Status='SHIPPED',
      DateModified=@Date,
      ModifiedBy=@User,
      WorkWeek=@WorkWeek 
WHERE (Status='KITTED')
AND   SerialNumber=@SerialNumber

You need to return a value that allows you to determine if this UPDATE has already happened or not, for example:

DECLARE @iUpdateAlreadyComplete INT
SET @iUpdateAlreadyComplete = 0;
IF EXISTS 
 (
    SELECT 1 
    FROM   dbo.FG_FILLIN 
    WHERE  Status='SHIPPED' 
    AND    SerialNumber=@SerialNumber
  )
BEGIN
  SET @iUpdateAlreadyComplete = 1
END
ELSE
BEGIN
    UPDATE dbo.FG_FILLIN 
    SET   Status='SHIPPED',
          DateModified=@Date,
          ModifiedBy=@User,
          WorkWeek=@WorkWeek 
    WHERE (Status='KITTED')
    AND   SerialNumber=@SerialNumber 
END

SELECT @iUpdateAlreadyComplete AS Result

You can then change your DAL from return cmd.ExecuteNonQuery(); to:

var result = Convert.ToInt32(cmd.ExecuteScalar());

return result;

The return value will now be 0 for a record that has been updated, and 1 for one that didn't need updating as it was already processed.

Other Notes

There are a couple of other things that you should consider changing:

sp_UpdatetoShipped is a bad name for a stored procedure. Do not use the sp_ prefix.
Your DAL deliberately catches and re-throws an exception (admittedly in the "best" way), do you really need to?
Rather than explicitly calling Dipose(), use the using() {} syntax instead, as this ensures that Dispose() is called, even in the event of an exception.

using syntax:

using(SqlConnection conn = new SqlConnection(connStr))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("sp_UpdatetoShipped", conn))
    {
    }
}

edited Mar 9, 2011 at 10:38

answered Aug 12, 2010 at 8:26

Rob

45.9k24 gold badges126 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Crimsonland Over a year ago

where will i place the return result... on my dal and ui. Thanks in regards

Rob Over a year ago

@Crimsonland, I've already stated how to change your DAL in my answer, as for the UI, it's still returning an int, so it's up to you what you do with it =)

Andrew · Accepted Answer · 2010-08-12 08:23:44Z

0

This seems more like a business rule issue than anything to do with an error. What you might want to do is to create a dictionary to hold serial numbers that have already been updated.

e.g.

Dictoinary<string,string> updatedSerialNumbers = new Dictionary<string, string>();

foreach (FillinEntity fin in fillin2) 
{
fin.ModifiedBy = loggedUser;
if (updatedSerialNumbers.Contains(fin.SerialNumber) == false)
{ 
  intResult = pBAL.UpdatetoShipped(fin);
  updatedSerialNumbers.Add(fin.SerialNumber,fin.SerialNumber);
}

Something like this should sort out your problem.

answered Aug 12, 2010 at 8:23

Andrew

5,2752 gold badges27 silver badges45 bronze badges

Collectives™ on Stack Overflow

Problem Error Handling in Stored Statement?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related