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.

Answer 1

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.

Answer 2

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 thinsg 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))
    {
    }
}