13.2. Design

Before getting into the code-behind, this section will step through the process of using the code generator. It's a trivial process but it is easier to understand the code if you see it in action. The goal is to enable you to create your stored procedures and standard class files without leaving Visual Studio. Let's assume you need to generate the code for the ENTRole table. The first step after the table has been added to the database is to create the stored procedures. The code generator will enable you to use the Add New Item menu to generate the DDL statements to create the standard procedures and then you can execute these against the database. The Add New Item dialog displays the wizard shown in Figure 13-1.

Figure 13.1. Figure 13-1

Notice you can pick from three new templates: V2BusinessClass, V2DataClass, and V2StoredProcedures. If you click on the V2StoredProcedures template and click the Add button, the window shown in Figure 13-2 will appear.

Figure 13.2. Figure 13-2

The Select a Connection list displays the names of the connection strings in any project in the current solution. When you select the connection, the screen will automatically populate the Select a Table drop-down list with all the tables in that database. Click OK to create a new file called TABLENameSP.sql and add it to the project. If you selected the ENTRole table, the file would be called ENTRoleSP.sql and the content would be as follows:

CREATE PROCEDURE ENTRoleSelectAll
AS
    SET NOCOUNT ON

    SELECT ENTRoleId, RoleName, InsertDate, InsertENTUserAccountId, UpdateDate,
           UpdateENTUserAccountId, Version
      FROM ENTRole

    RETURN
GO

CREATE PROCEDURE ENTRoleSelectById
(
    @ENTRoleId int
)
AS
    SET NOCOUNT ON

    SELECT ENTRoleId, RoleName, InsertDate, InsertENTUserAccountId, UpdateDate,
           UpdateENTUserAccountId, Version
      FROM ENTRole
     WHERE ENTRoleId = @ENTRoleId

    RETURN
GO

CREATE PROCEDURE ENTRoleDelete
(
    @ENTRoleId int
)
AS
    SET NOCOUNT ON

    DELETE
      FROM ENTRole
     WHERE ENTRoleId = @ENTRoleId

    RETURN
GO

CREATE PROCEDURE ENTRoleInsert
(
    @ENTRoleId  int OUTPUT,
    @RoleName  varchar(50),
    @InsertENTUserAccountId  int
)

AS
    SET NOCOUNT ON

    INSERT INTO ENTRole (RoleName, InsertDate, InsertENTUserAccountId, UpdateDate,
                         UpdateENTUserAccountId)
         VALUES (
                @RoleName,
                GetDate(),
                @InsertENTUserAccountId,
                GetDate(),
                @InsertENTUserAccountId
                )
    SET @ENTRoleId = Scope_Identity()

    RETURN

GO

CREATE PROCEDURE ENTRoleUpdate
(
    @ENTRoleId  int,
    @RoleName  varchar(50),
    @UpdateENTUserAccountId  int,
    @Version  timestamp
)
AS
    SET NOCOUNT ON

    UPDATE ENTRole
       SET
           RoleName = @RoleName,
           UpdateDate = GetDate(),
           UpdateENTUserAccountId = @UpdateENTUserAccountId
     WHERE ENTRoleId = @ENTRoleId
       AND Version = @Version

    RETURN @@ROWCOUNT

GO

13.2.1. Data and Business Classes

The same concept applies to the data and business classes. If you were to click the Add New Item menu and select V2DataClass, you would be presented with the same dialog. When you click the OK button, the system adds a new class to the project and names it TABLENAMEData.cs. Using the ENTRole table as the example again would create a file called ENTRoleData.cs, and the code would look as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Text;
using V2.PaidTimeOffDAL.Framework;

namespace V2.PaidTimeOffDAL
{
    public class ENTRoleData : ENTBaseData<ENTRole>
    {
        #region Overrides

        public override List<ENTRole> Select()
        {
            using (HRPaidTimeOffDataContext db = new
             HRPaidTimeOffDataContext(DBHelper.GetHRPaidTimeOffConnectionString()))
            {
                return db.ENTRoleSelectAll().ToList();
            }
        }

        public override ENTRole Select(int id)
        {
            using (HRPaidTimeOffDataContext db = new
             HRPaidTimeOffDataContext(DBHelper.GetHRPaidTimeOffConnectionString()))
            {
                return db.ENTRoleSelectById(id).SingleOrDefault();
            }
        }

        public override void Delete(HRPaidTimeOffDataContext db, int id)
        {
            db.ENTRoleDelete(id);
        }

        #endregion Overrides

        #region Insert

        public int Insert(string connectionString, string roleName, int
                          insertENTUserAccountId)
        {
            using (HRPaidTimeOffDataContext db = new
                   HRPaidTimeOffDataContext(connectionString))
            {
                return Insert(db, roleName, insertENTUserAccountId);
            }
        }

        public int Insert(HRPaidTimeOffDataContext db, string roleName, int
                          insertENTUserAccountId)
        {
            Nullable<int> eNTRoleId = 0;

            db.ENTRoleInsert(ref eNTRoleId, roleName, insertENTUserAccountId);

            return Convert.ToInt32(eNTRoleId);
        }

        #endregion Insert

#region Update

        public bool Update(string connectionString, int eNTRoleId, string roleName,
                           int updateENTUserAccountId, Binary version)
        {
            using (HRPaidTimeOffDataContext db = new
                   HRPaidTimeOffDataContext(connectionString))
            {
                return Update(db, eNTRoleId, roleName, updateENTUserAccountId,
                              version);
            }
        }

        public bool Update(HRPaidTimeOffDataContext db, int eNTRoleId, string
                           roleName, int updateENTUserAccountId, Binary version)
        {
            int rowsAffected = db.ENTRoleUpdate(eNTRoleId, roleName,
                               updateENTUserAccountId, version);
            return rowsAffected == 1;
        }

        #endregion Update
    }
}

The data class assumes you are inheriting from the ENTBaseData class, and you would create the entity class using the LINQ to SQL Designer, just as you have done in the previous chapters. The code generator creates the methods that can be used to call the methods on the DataContext object that calls the stored procedures.

Adding a new business class is just as easy. If you click the Add New Item menu and select V2BusinessClass, the same dialog appears, from which you can select the connection and table for which you want to create the classes. Clicking the Add button adds a file called TABLENAMEEO.cs to the project and the code would be as follows (both the EO and EOList classes are in this same file):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using V2.PaidTimeOffDAL.Framework;
using V2.PaidTimeOffDAL;
using V2.PaidTimeOffBLL.Framework;

namespace V2.PaidTimeOffBLL
{
    #region ENTRoleEO

    [Serializable()]
    public class ENTRoleEO : ENTBaseEO
    {
        #region Properties

        public string RoleName { get; set; }

#endregion Properties

        #region Overrides

        public override bool Load(int id)
        {
            //Get the entity object from the DAL.
            ENTRole eNTRole = new ENTRoleData().Select(id);
            MapEntityToProperties(eNTRole);
            return eNTRole != null;
        }

        protected override void MapEntityToCustomProperties(IENTBaseEntity entity)
        {
            ENTRole eNTRole = (ENTRole)entity;

            ID = eNTRole.ENTRoleId;
            RoleName = eNTRole.RoleName;
        }

        public override bool Save(HRPaidTimeOffDataContext db, ref
           ENTValidationErrors validationErrors, int userAccountId)
        {
            if (DBAction == DBActionEnum.Save)
            {
                //Validate the object
                Validate(db, ref validationErrors);

                //Check if there were any validation errors
                if (validationErrors.Count == 0)
                {
                    if (IsNewRecord())
                    {
                        //Add
                       ID = new ENTRoleData().Insert(db, RoleName, userAccountId);

                    }
                    else
                    {
                        //Update
                        if (!new ENTRoleData().Update(db, ID, RoleName,
                                                      userAccountId, Version))
                        {
                            UpdateFailed(ref validationErrors);
                            return false;
                        }
                    }

                    return true;

                }
                else
                {
                    //Didn't pass validation.

return false;
                }
            }
            else
            {
                throw new Exception("DBAction not Save.");
            }
        }

        protected override void Validate(HRPaidTimeOffDataContext db, ref
                                         ENTValidationErrors validationErrors)
        {
            throw new NotImplementedException();
        }

        protected override void DeleteForReal(HRPaidTimeOffDataContext db)
        {
            if (DBAction == DBActionEnum.Delete)
            {
                new ENTRoleData().Delete(db, ID);
            }
            else
            {
                throw new Exception("DBAction not delete.");
            }
        }

        protected override void ValidateDelete(HRPaidTimeOffDataContext db, ref
            ENTValidationErrors validationErrors)
        {
            throw new NotImplementedException();
        }

        public override void Init()
        {
            throw new NotImplementedException();
        }

        protected override string GetDisplayText()
        {
            throw new NotImplementedException();
        }

        #endregion Overrides
    }

    #endregion ENTRoleEO

    #region ENTRoleEOList

    [Serializable()]
    public class ENTRoleEOList : ENTBaseEOList<ENTRoleEO>
    {
        #region Overrides

public override void Load()
        {
            LoadFromList(new ENTRoleData().Select());
        }

        #endregion Overrides

        #region Private Methods

        private void LoadFromList(List<ENTRole> eNTRoles)
        {
            if (eNTRoles.Count > 0)
            {
                foreach (ENTRole eNTRole in eNTRoles)
                {
                    ENTRoleEO newENTRoleEO = new ENTRoleEO();
                    newENTRoleEO.MapEntityToProperties(eNTRole);
                    this.Add(newENTRoleEO);
                }
            }
        }

        #endregion Private Methods

        #region Internal Methods

        #endregion Internal Methods
    }

    #endregion ENTRoleEOList
}

As you can see, most of the grunt work is done for you. Once the business class is created you can add the validation rules to the Validate method or make any customizations needed to represent relationships. This truly saves you a tremendous amount of time.