Generate Classes and CRUD Procedures

• Download add-in - 22.4 KB
• Download latest versions from CodePlex

Introduction

This generator allows for users to eliminate the need to spend much time writing a data access layer. It generates C# classes and database Stored Procedures. The class generator requires use of the "StandardDAC" code. (You could just use the Stored Procedure part of the generator and not use the StandardDAC /class generator.)

The generator is available as a Visual Studio add-in or as a Windows Forms App. It is added in the "Favorite References" area (if using the add-in version).

Preview of some of the code

CRUD Generator makes Stored Procedures like this. It auto-detects what optional things you might want to read by (Foreign Keys, Primary Keys, Indexes).

-- =============================================
-- Author:         colinbashbash
-- Create date:    5/5/2009
-- Description:    Inserts a single record into Model
-- Revisions:    
-- =============================================
Create Procedure Model_Create
    @ModelId int OUTPUT,
    @AbbreviatedModelId int = NULL,
    @MapicsModelSalesGroup varchar(50) = NULL
AS
Begin
    SET NOCOUNT ON
    insert into Model
        (AbbreviatedModelId, MapicsModelSalesGroup)
    values
        (@AbbreviatedModelId,@MapicsModelSalesGroup)

    select @ModelId = SCOPE_IDENTITY()
End
GO
GRANT EXECUTE ON Model_Create TO DataEntry
GO
-- =============================================
-- Author:        colinbashbash
-- Create date:    5/5/2009
-- Description:    Deletes a single record from Model
-- Revisions:    
-- =============================================
Create Procedure Model_Delete
    @ModelId int
AS
Begin
    SET NOCOUNT ON
    delete from Model
    where
        ModelId = @ModelId
End
GO
GRANT EXECUTE ON Model_Delete TO DataEntry
GO
-- =============================================
-- Author:        colinbashbash
-- Create date:    5/5/2009
-- Description:    Updates a single record from Model
-- Revisions:    
-- =============================================
Create Procedure Model_Update
    @ModelId int = NULL,
    @AbbreviatedModelId int = NULL,
    @MapicsModelSalesGroup varchar(50) = NULL
AS
Begin
    SET NOCOUNT ON
    update Model
    set
        AbbreviatedModelId = @AbbreviatedModelId,
        MapicsModelSalesGroup = @MapicsModelSalesGroup
    where
        ModelId = @ModelId
End
GO
GRANT EXECUTE ON Model_Update TO DataEntry
GO
-- =============================================
-- Author:        colinbashbash
-- Create date:    5/5/2009
-- Description:    Reads record(s) from Model. These 'may' be
--   limited by primary/forign keys or an IsActive column.
-- Revisions:    
-- =============================================
Create Procedure Model_Read
    @ModelId int = NULL,
    @AbbreviatedModelId int = NULL,
    @MapicsModelSalesGroup varchar(50) = NULL
AS
Begin
    SET NOCOUNT ON
    select
        ModelId, AbbreviatedModelId, MapicsModelSalesGroup
    from Model
    where
        ModelId = coalesce(@ModelId, ModelId)
        and AbbreviatedModelId = 
            coalesce(@AbbreviatedModelId, AbbreviatedModelId)
        and MapicsModelSalesGroup = 
            coalesce(@MapicsModelSalesGroup, MapicsModelSalesGroup)
End
GO
GRANT EXECUTE ON Model_Read TO DataEntry
GO

The class generator makes code like this:

using System;
using System.Collections.Generic;
using System.Data;
using StandardDAC;
using System.Data.SqlClient;
using StandardDAC.SqlClient;


public class Model : IDataModel {
    #region members
    int id;
    int abbreviatedModelId;
    string mapicsModelSalesGroup;

    #endregion members
    #region Properties
    [PKSqlColumn("ModelId", 0)]
    public int Id {
        get { return id; }
        set{ id = value; }
    }
    [SqlColumn("AbbreviatedModelId", SqlDbType.Int)]
    public int AbbreviatedModelId {
        get { return abbreviatedModelId; }
        set{ abbreviatedModelId = value; }
    }
    [StringSqlColumn("MapicsModelSalesGroup")]
    public string MapicsModelSalesGroup {
        get { return mapicsModelSalesGroup; }
        set{ mapicsModelSalesGroup = value; }
    }

    #endregion Properties
    #region IsNew()
    public bool IsNew() {
        return id == 0; 
    }

    #endregion IsNew()
    #region Constructors
    /// <summary>
    ///Default Constructor. Required by DAL Helper
    /// </summary>
    public Model() { }
    /// <summary>
    ///Gets item by Id.
    /// </summary>
    /// <param name="inId"></param>
    public Model(int inId) {
        this.id = inId;
        populate();
    }

    #endregion Constructors
    #region DAC Methods
    /// <summary>
    ///Reads all items in database.
    /// </summary>
    public static List<Model> ReadAll() {
        return MESDataSqlDAC.ReadAll<Model>("Model");
    }
    /// <summary>
    ///Populates item from database by its id.
    /// </summary>
    private void populate() {
        MESDataSqlDAC.ReadById<Model>(this, "Model");
    }
    /// <summary>
    ///Saves item to database.
    /// </summary>
    public void Save() {
        MESDataSqlDAC.Save(this, "Model");
    }
    /// <summary>
    ///Deletes item from database.
    /// </summary>
    public void Delete() {
        MESDataSqlDAC.Delete(this, "Model");
    }
    /// <summary>
    ///Read By Foreign Key
    /// </summary>
    /// <param name="inAbbreviatedModelId"></param>
    public static List<Model> ReadByAbbreviatedModelId(int inAbbreviatedModelId) {
        return MESDataSqlDAC.ReadByParams<Model>("Model", 
          MESDataSqlDAC.newInParam("@AbbreviatedModelId", 
                                   inAbbreviatedModelId));
    }
    /// <summary>
    ///Read by Unique Constraint
    /// </summary>
    /// <param name="inMapicsModelSalesGroup"></param>
    public static List<Model> ReadByMapicsModelSalesGroup(
                              string inMapicsModelSalesGroup) {
        return MESDataSqlDAC.ReadByParams<Model>("Model", 
          MESDataSqlDAC.newInParam("@MapicsModelSalesGroup", 
                                   inMapicsModelSalesGroup));
    }
    #endregion DAC Methods
}

What will the DAC look like?

using System;
using System.Configuration;
using System.Data;

/// <summary>
/// Summary description for DAC
/// </summary>
public class SqlDAC : StandardDAC.SqlClient.SQLDac {
    #region connection string
    private static string connStr = 
      ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
    /// <summary>
    /// Returns your Connection String
    /// </summary>
    protected override string GetConnectionString() {
        return connStr;
    }
    #endregion connection string

    //You can always add additional methods, 
    //if they're reports or some such. Example:
    

    public DataTable SearchLocks(DateTime BeginDate, 
           DateTime EndDate, string EcoNumber, string UserName) {
        SqlParameter[] ary = new SqlParameter[4];
        ary[0] = new SqlParameter("@BeginDate", BeginDate);
        ary[1] = new SqlParameter("@EndDate", EndDate);
        ary[2] = new SqlParameter("@EcoNumber", EcoNumber);
        ary[3] = new SqlParameter("@UserName", 
                 UserName == "" ? 
                 (object)DBNull.Value : (object)UserName);

        return Execute("LockView_Read", ary).Tables[0];
    }
    public DataTable GetLocks2(DateTime BeginDate, DateTime EndDate, 
                     string EcoNumber, string UserName) {
        SqlParameter[] ary = new SqlParameter[4];
        ary[0] = new SqlParameter("@BeginDate", BeginDate);
        ary[1] = new SqlParameter("@EndDate", EndDate);
        ary[2] = new SqlParameter("@EcoNumber", EcoNumber);
        ary[3] = new SqlParameter("@UserName", 
                 UserName == "" ? 
                 (object)DBNull.Value : (object)UserName);

        string sql = "select lngLockedECONumber,occurredat, " + 
                     "case when txtLockingUser = 'ECO ADMIN' then 'ADMIN' " + 
                     "else '' end as LockType, UserName, case when " + 
                     "isDelete = 1 then 'UNLOCKED' else 'LOCKED' end " + 
                     "as Action from history_tblLockedECOBackPages where " + 
                     "dteTimeOfLock between @BeginDate and @EndDate " + 
                     "and UserName = coalesce(@UserName,UserName) " + 
                     "and lngLockedECONumber = @EcoNumber order by occurredat desc";
        return InlineSql_Execute(sql, ary).Tables[0];
    }
}

Project status / Important notes -- ALPHA

The initial release has full support for "SQL Server" and "iSeries (through OLE)" CRUD Generation and Integration.

OleDB, ODBC, and OracleClient have limited support. It will manage connections, let you call Stored Procedures / inline SQL, and set up your own class bindings manually to these connections. It doesn't support generating CRUD / Classes, though.

This has not been thoroughly tested as of yet. The generator has not also been thoroughly documented as of yet. The OleDB, ODBC, and OracleClient components of the Standard DAC have not been thoroughly documented as of yet. The SqlClient component of the standard DAC has been fairly well documented.

Caveats or performance issues?

This is really a solution for small to mid-range solutions. The performance is mostly linked to the creation of a new connection for each command. I would not think that it would be too hard to update the standard DAC to allow for larger scale applications, but since I don't really have a large scale application to test this type of interaction, I'm not going to program for it at the moment. If you want to join this project though....?

Project members?

I'm the only person working on this right now, so only so much will get done. I wouldn't mind some help, though. Heh.

Project direction

My next steps:

1. Finish documenting and flush out any changes to the StandardDAC.
2. Generate a MySql generator -- (in-process)
3. Generate an OracleClient generator (I don't have Oracle, so I'm putting this off until I either get some help, or I feel the rest of the solution is fairly flushed out).
4. Make extending the generator easier... (i.e.: some kind of plug-in framework; I need some major help with this).

Screenshots

The add-in appears here:

Here's how the Stored Procedure generation screen looks like:

Here's how the class generation screen looks like (this has changed a little bit, but not much):

Here's how the View/Add References screen looks like: