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).
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
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
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
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
public Model() { }
public Model(int inId) {
this.id = inId;
populate();
}
#endregion Constructors
#region DAC Methods
public static List<Model> ReadAll() {
return MESDataSqlDAC.ReadAll<Model>("Model");
}
private void populate() {
MESDataSqlDAC.ReadById<Model>(this, "Model");
}
public void Save() {
MESDataSqlDAC.Save(this, "Model");
}
public void Delete() {
MESDataSqlDAC.Delete(this, "Model");
}
public static List<Model> ReadByAbbreviatedModelId(int inAbbreviatedModelId) {
return MESDataSqlDAC.ReadByParams<Model>("Model",
MESDataSqlDAC.newInParam("@AbbreviatedModelId",
inAbbreviatedModelId));
}
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;
public class SqlDAC : StandardDAC.SqlClient.SQLDac {
#region connection string
private static string connStr =
ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
protected override string GetConnectionString() {
return connStr;
}
#endregion connection string
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:
- Finish documenting and flush out any changes to the StandardDAC.
- Generate a MySql generator -- (in-process)
- 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).
- 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:
