Tag: .tt

Generating history trigger with EF , edmx and TT files

I have wrote in an older post ( http://msprogrammer.serviciipeweb.ro/2010/06/28/ef-automatic-history-of-table-and-t4-files-tt-files/ ) how to generate history code for tables . The easy solution was to create a tt file that track for the ObjectContext the SaveChanges for each table that has a “history” in name. the limitation is that , when you raise an sql command such as “update table ” you must load from database a lot of rows for this….

Now I want to show the same thing, but generating triggers in database for that ! I start also from edmx file and with a template stealed from http://forums.asp.net/p/1599616/4083198.aspx ( to have type of fields in the database ) and another stealed and modified from StackOverflow(to generate trigger for after insert , update, delete) I manage to have a solution to generate sql tables and trigger code.

When is that good ?At the beginning stages of a project when the table structure changes by adding a new parameter.

Sample code generated for table Notes(ID, TextNote, PersonID)

print 'Create table Notes_History ';
Create Table Notes_History(
		ID_Notes_History BIGINT IDENTITY NOT NULL
		,History_Action varchar(50)
		,History_From varchar(100) default HOST_NAME()
		,History_User varchar(50) default SYSTEM_USER
		,History_Date varchar(50) default  getdate()
	   ,Id  int   NOT NULL
	   ,Textnote  nvarchar (255)  NULL
	   ,PersonId  int   NULL
	)
print 'end Create table Notes_History ';
GO
print 'create trigger for Notes'
GO
CREATE TRIGGER dbo.TR_IUP_Notes
   ON  Notes
   AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
	DECLARE @Ins int

DECLARE @Del int

SELECT @Ins = Count(*) FROM inserted

SELECT @Del = Count(*) FROM deleted
if(@Ins  + @Del = 0)
	return;

declare @operation varchar(50)
	set @operation ='update';
	if(@ins < @del)
		set @operation ='delete';
	if(@ins > @del)
		set @operation ='insert';

	if(@ins <= @del)
	begin
		INSERT INTO Notes_History(History_Action ,Id,Textnote,PersonId)
			select @operation ,Id,Textnote,PersonId from deleted
	end
    else
	begin
    INSERT INTO Notes_History(History_Action ,Id,Textnote,PersonId)
			select @operation ,Id,Textnote,PersonId from inserted
	end
END

The drawback of the code : the user that executes is not always the logged sql server user…
Anyway, what you have to do to use this automatically generated history/audit for tables ?

download historysql and modify

string inputFile = @”Model1.edmx”;

from historysql.tt to your edmx name. Then take the generated code and execute in sql server.

TT files – generate enum from database

Many times you will program against a table that contains something like an enum , like Status( open=1 , close=2, sent=3, approved=4 )  .

It is peculiar to wrote those status as text in the other tables – and you do not like also to have update their codes in the C# (VB.NET) code source each time you will add another one.

Rather , it is convenient to auto-generate from database at once.

But how to do it in Visual Studio ? The answer is .tt files – the files that generates also POCO

So here it is my own template for such enum from database .

To use ,unzip, add to your project that contains the edmx and do what is says below- and you will see as many  .cs file as tables want to put.

<#
//*********************************************************
//
//    NO Copyright .Use at your own risk.
//    Please modify :
//    1) the names of tables to generate enums : string nameforenum
//    2) the connection to the database : string connectionstring
//    3) the name of the model : string inputFile
//    Then save the file and you will have an enum …
//*********************************************************
#>

GenerateEnum

Andrei Ignat weekly software news(mostly .NET)

* indicates required

Please select all the ways you would like to hear from me:

You can unsubscribe at any time by clicking the link in the footer of our emails. For information about our privacy practices, please visit our website.

We use Mailchimp as our marketing platform. By clicking below to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.