Hi Bob,

One suggestion is to only return the row count from your duplicate test instead of returning all rows in the record. This reduces bandwidth becuase you never know when someone will decide to move the SQL server 3000 miles away. Not a big deal on a record with a limited number of columns of limited size, but can make an incremental difference when the overall record size is larger.

Here is some code for just checking to see if a duplicate record exists without returning all of the rows:

  /// <summary>
        /// Checks against database for duplicate resource names and allows 
        /// cleaner error handling 
        /// </summary>
        /// <param name="resourceEntity">The resource entity to check again database</param>
        /// <returns>Error message as string or null if no errors</returns>
        private string NoDuplicateResourceName(SQLTracResourceEntity resourceEntity)
        {
            string sMsg = null;

            // check to see if there is a resource by this name already defined


            if (!mmType.IsEmpty(resourceEntity.ResourceName))
            {
                // if empty the required rule will catch the error
                if (this.SQLTracResourceHost.DuplicateResourceName(resourceEntity.ResourceID, resourceEntity.ResourceName))
                {
                    this.EntityPropertyDisplayName = "Resource Names";

                    sMsg = "Duplicate " + this.EntityPropertyDisplayName;
                    sMsg += " are not allowed.  Please check your Resource Name entry.";


                    AddErrorProviderBrokenRule("ResourceName", sMsg);
                }
            }
            return sMsg;
        }


/// <summary>
        /// Returns the host object as a strongly typed SQLTracResource object
        /// (saves doing multiple casts...)
        /// </summary>
        protected SQLTracResource SQLTracResourceHost
        {
            get
            {
                return (SQLTracResource)this.HostObject;
            }
        }

Inside the business object I have the method DuplicateResourceName as follows:

        /// <summary>
        /// Used to checks for duplicate resource names on the SQL server prior to inserting or 
        /// updating a record. Allows improved warning messaging compared to the SQL constraint message 
        /// </summary>
        /// <param name="p_iID">Resource ID</param>
        /// <param name="p_sName">New or modified resource name</param>
        /// <returns>True if a duplicate name is found</returns>
        public bool DuplicateResourceName(int p_iID, string p_sName)
        {
            bool bReturn = false;

            IDbDataParameter[] parameters = new IDbDataParameter[]
            {
                this.CreateParameter("@ResourceID",(object)p_iID,DbType.Int16),
                this.CreateParameter("@NewName",(object)p_sName,DbType.String)

            };


            if ((int)this.ExecSprocScalar("[dbo].[sp_SQLTrac_CheckDupResourceName]", parameters) > 0)
            {

                bReturn = true;
            }


            return bReturn;
        }

And here is the code for the sp_SQLTrac_CheckDupResourceName :

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tim Holgerson
-- Create date: 06/26/2008
-- Description:	Check for duplicate resource names to avoid SQL except on unique constraint
-- =============================================
ALTER PROCEDURE [dbo].[sp_SQLTrac_CheckDupResourceName] 
	-- Add the parameters for the stored procedure here
	@ResourceID int, 
	@NewName varchar(50)
AS

	DECLARE @RowCount as int
	DECLARE @Return as int

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


	SET @Return = 1 -- default false can't add return value

    -- Insert statements for procedure here
	SET @RowCount = (SELECT Count(*) FROM SQLTrac_Resources WHERE (ResourceID != @ResourceID AND RTRIM(UPPER(ResourceName)) = @NewName))
	
	

	if @RowCount = 0  -- OK to add or change
	begin	
		Set @Return = 0
	end

	SELECT @Return

END