生成类和 CRUD 过程

• 下载插件 - 22.4 KB
• 从CodePlex下载最新版本

引言

此生成器允许用户无需花费大量时间编写数据访问层。 它生成 C# 类和数据库存储过程。 类生成器需要使用“StandardDAC”代码。（您可以仅使用生成器的存储过程部分，而不使用 StandardDAC / 类生成器。）

该生成器可以作为 Visual Studio 插件或 Windows Forms 应用程序使用。 它被添加到“收藏引用”区域（如果使用插件版本）。

一些代码的预览

CRUD 生成器生成如下的存储过程。 它自动检测您可能想要读取的可选内容（外键、主键、索引）。

-- =============================================
-- 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

类生成器生成如下代码

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
}

DAC 会是什么样子？

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];
    }
}

项目状态/重要提示 -- ALPHA

初始版本完全支持“SQL Server”和“iSeries（通过 OLE）”的 CRUD 生成和集成。

OleDB、ODBC 和 OracleClient 具有有限的支持。 它可以管理连接，让您调用存储过程/内联 SQL，并手动设置您自己的类绑定到这些连接。 但是，它不支持生成 CRUD / 类。

尚未对此进行彻底测试。 该生成器也尚未得到彻底的文档记录。 Standard DAC 的 OleDB、ODBC 和 OracleClient 组件尚未得到彻底的文档记录。 标准 DAC 的 SqlClient 组件已经得到了很好的文档记录。

警告或性能问题？

这实际上是一个用于中小型解决方案的解决方案。 性能主要与为每个命令创建新连接有关。 我不认为更新标准 DAC 以允许更大规模的应用程序会太难，**但是**由于我实际上没有大规模的应用程序来测试这种类型的交互，所以我暂时不打算为此进行编程。 如果你想加入这个项目......？

项目成员？

我现在是唯一从事此项目的人，所以只能完成这么多。 不过，我不介意得到一些帮助。 呵呵。

项目方向

我的下一步

1. 完成 StandardDAC 的文档记录并清除所有更改。
2. 生成一个 MySql 生成器 -- (正在进行中)
3. 生成一个 OracleClient 生成器（我没有 Oracle，所以我将其推迟到我得到一些帮助，或者我认为其余解决方案已经相当完善为止）。
4. 使扩展生成器更容易......（即：某种插件框架；我需要在这方面提供一些主要帮助）。

屏幕截图

插件出现在这里

这是存储过程生成屏幕的样子

这是类生成屏幕的样子（这已经改变了一点点，但不多）

这是查看/添加引用屏幕的样子