Introduction
I wrote this tool for myself creating data layer classes using the entity Framework based on template files. It connects to the SQL server and reads tables, views and their columns. On your click, it will generate basic data layer classes you can implement into your projects.
You can modifiy the code template files (included) to translate it to VB or add methods you miss.
Connect to SQL server
First, a connection to your SQL server need to estiblished.
Code Generator
Now you enter some project related variables, choose a table, select the primary key column and sorting and click "Generate Class". Voila.
Methods Generated
Inside the template files, there are some default methods created by generation. In case your table contains columns like "DELETED", "CREATED", UPDATED" (all of them are datetime) this generator will decide, which line needs to be generated.
Sample: You have a column "DELETED" (datetime) used to flag a record on deletion, this generator detect this.
The following methods will be generated for a table:
- Create (record)
- Update (record)
- DeleteById (record)
- DropById (record)
- GetById (record)
- GetList (list of records)
- CleanMarkedAsDeleted (delete marked records for deletion)
The following methods will be generated for a view:
- GetById (record)
- GetList (list of records)
Add-On
Two additional options are available:
- Generate the DatabaseException class, which is needed only once per project.
- Generate a class for getting a SelectList for MVC projects. You can use this very easy in your ViewModel to fill combo boxes and lists with data.
Using the generated code in your destination project
As soon you implemented the generated class into your EF project, you can use this like this.
A sample based on the model object VEREIN:
Get a list of all VEREIN objects
List<VEREIN> lst = EF.Verein.GetList();
Get one specific VEREIN object
Guid id = new Guid("7125A5EA-25EA-4C3F-A123-415506246359");
VEREIN verein = EF.Verein.GetByID(id);
Full context menu when using this class:
Exception
The generated classes will need the DatabaseException class once, in which you fill your events on your need. Gererated exception class:
using System;
using System.Runtime.Serialization;
namespace TestProject
{
[Serializable]
public class DatabaseException : Exception
{
public DatabaseException(string message)
: base(message)
{
}
public DatabaseException(string message, Exception innerException)
: base(message)
{
}
public DatabaseException(string format, params object[] args)
: base(string.Format(format, args))
{
}
protected DatabaseException(SerializationInfo info, StreamingContext ctxt)
: base(info, ctxt)
{
}
}
}
SelectList
Only for MVC (ASP) projects but very helpfull filling combo boxes in views.
This generated code will return a MVC List<SelectListItem>filled with all VEREIN records (ID and Name) for a combo box.
using System;
using System.Collections.Generic;
using System.Linq;
using TestProject.Models;
using System.Web.Mvc;
namespace TestProject.EF
{
class SelList_Verein
{
private const string exceptionMessage = "A database exception occurred";
public static List<SelectListItem> GetList()
{
try
{
List<SelectListItem> sellist = new List<SelectListItem>();
using (EFDataClassGeneratorEntities model = new EFDataClassGeneratorEntities())
{
var list = model.VEREIN.OrderBy(w => w.VEREIN_NAME).ToList();
foreach (VEREIN item in list)
{
sellist.Add(new SelectListItem { Value = item.VEREIN_ID.ToString(), Text = item.VEREIN_NAME });
}
return sellist;
}
}
catch (Exception ex)
{
throw new DatabaseException(exceptionMessage, ex);
}
}
}
}
Source
I am not an enterprise developer and there is always something wrong with code from others than yourself. Take this sample as inspiration. Feel free to change the source like you want, edit the template files, do what you want.
But please don't criticize the way of coding.
Any other feedback is very welcome.
History
I will enhance this project by myself only. I guess further versions will not be posted here ny me.
2017/07/20
Updated the source and fixed some bugs