It's a mix and mash thing, when you crate a web application you may be using ASP.NET but exactly how many technologies are you actually using? What patterns, technique, ... Well it's mix and mash...

So here is the next step of the evolution towards a web application, the data layer. This is no way a "standard", it's more to make life easier for first myself, second my co-workers.

These are my 13 guidelines:

1. Structure your Namespaces, make it logical, readable. Take your queues for the Microsoft Framework. Keep it logical, data related and simple.
   
2. Make use of the tools you have to make you code logical and flowing. Make use "regions", name your classes as close as possible to your data table names. It makes it simpler to read and debug.
   
3. Keep the methods sweet and short, do not be ashamed to have 1 liners.
   
4. Never have conditions within conditions, it meses code and makes it unreadable. I always say a developer should be able to read code like he reads a good novel.
   
5. Name your methods logically with descriptive names.
   
6. If you are going to use collections, generics are a good idea. In fact, take it further, have a look at Wintellect PowerCollections, I can't get enough of it.
   
7. In your properties, do some data validation, catch the error before it happens. Maybe start using Microsoft's Validation Block from the forthcoming Enterprise Library 3.0
   
8. Why not use Microsoft's Enterprise Library for data access, caching, encryption, and more...
   
9. Have standard methods such as Save, Delete, Populate.
   
10. Always have an empty  constructor. And always have a constructor with a default population scheme.
    
11. Have an initialize method that gets called for all constructors to validate the class setting and access.
    
12. Comment and document your code !!! 
    
13. Make use of abstract classes (The examples are not abstract classes)
    

Here I was looking at Microsoft SQL Server 2005 and it occurred to me that maybe there might be a blog to write in all of this.

First and foremost, naming of stored procedures and other objects in SQL should be taken seriously, too often do I see sp_garbagenameofsorts.

1. The name does not need to be prefixed and should you feel the urge to do so do not use "sp_", I use "su_" Stored procedure User.
   From Microsoft (http://support.microsoft.com/kb/263889)
   
   Note If your stored procedure name begins with the "sp_" prefix and is not in the master database, you see SP:CacheMiss before the cache hit for each execution even if you owner-qualify the stored procedure. This is because the sp_ prefix tells SQL Server that the stored procedure is a system stored procedure, and system stored procedures have different name resolution rules (the "preferred" location is in the master database). The names of user-created stored procedures should not begin with "sp_."
   
2. Have a descriptive name, I generally use the Object Name followed by a narrowing type, if there is one, and finally the action.
   eg: UsersByRegionSelect, UserInsert, UserDelete, UserSave, UserByIDSelect, UserByIDUpdate... You get the idea.
   This allows me to "group" the store procedures by object and thus finding them easier, notice the use of camel case.
   
3. Make use of the TRY / CATCH close, mind you this should be used everywhere. If you do not already, make it a New Year's resolution.
   Example:

   CREATE PROCEDURE [user].[TableSave] 
   
       @propertyName nvarchar(255),
       @propertyName varchar(128), 
       ... ,
       @ID int OUT 
   AS
   BEGIN
       SET NOCOUNT ON
       
       BEGIN TRY
           IF EXISTS(SELECT ID FROM [user].[Table] WHERE ID = @ID)
               BEGIN
                   UPDATE [user].[Table] 
                   SET [fieldName] = @propertyName, [fieldName] = @propertyName, ...
                   WHERE [ID]=@iD
   
                   IF @@ROWCOUNT = 0
                   BEGIN
                       RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
                   END
               END
           ELSE
               BEGIN
                   INSERT INTO [user].[Table] ([fieldName], [fieldName], ...)
                   VALUES (@propertyName, @propertyName, ...)
                   SET @ID = SCOPE_IDENTITY()
               END
       END TRY
   
       BEGIN CATCH
           EXEC RethrowError;
       END CATCH
       
       SET NOCOUNT OFF
   
   END

   
   
   For your reading pleasure, this is the code for "RethrowError" as generated by the Microsoft Service Factory
   
   

   ALTER PROCEDURE RethrowError AS
       /* Return if there is no error information to retrieve. */
       IF ERROR_NUMBER() IS NULL
           RETURN;
   
       DECLARE
           @ErrorMessage    NVARCHAR(4000),
           @ErrorNumber     INT,
           @ErrorSeverity   INT,
           @ErrorState      INT,
           @ErrorLine       INT,
           @ErrorProcedure  NVARCHAR(200); 
   
       /* Assign variables to error-handling functions that
          capture information for RAISERROR. */
   
       SELECT
           @ErrorNumber = ERROR_NUMBER(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE(),
           @ErrorLine = ERROR_LINE(),
           @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); 
   
       /* Building the message string that will contain original
          error information. */
   
       SELECT @ErrorMessage = 
           N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE(); 
   
       /* Raise an error: msg_str parameter of RAISERROR will contain
          the original error information. */
   
       RAISERROR(@ErrorMessage, @ErrorSeverity, 1,
           @ErrorNumber,    /* parameter: original error number. */
           @ErrorSeverity,  /* parameter: original error severity. */
           @ErrorState,     /* parameter: original error state. */
           @ErrorProcedure, /* parameter: original error procedure name. */
           @ErrorLine       /* parameter: original error line number. */
           );

4. Do not include any business logics in your stored procedures, build a Business Logic Layer for that. Keep to what SQL was made for, inserting, deleting and selecting data.
   
5. Keep the name of your table fields across the application, so if you use "Name" as a table field, in your stored procedure your field input / output will be called @Name. It's easier to read and find errors later.
   
6. Avoid any reserved words, and if you can't make use of square brackets as in the [user].[ObjectSave] example above.
   

As you may have noticed the [user].[ObjectSave]  has both INSERT and UPDATE commands in it, I find it more practical this way as my DataLayer / Business Object only has a save. It's my preference and is certainly not a standard, though it is a recommendation.