Tag: HowTo

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.

Logparser quick and dirty

Sometimes you must find information in text files. Many,many text files, like IIS logs or other custom non-regular formats.

I have a bot from http://www.imified.com/ – and I log the messages with log4net in text files, with another messages.

An entry looks like that :

System.ArgumentException: ;channel=private;botkey=<guid>;userkey=<guid>;user=name@yhaoo.com;network=Yahoo;msg=hello;step=1;value0=hello;to=asdasd

And there are multiple log files that I want to parse and find the email adresses to collect feedback from those persons that use my bot.

LogParser to the rescue! Download from http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en and use this command line

LOGPARSER “Select Text into a.csv from current* where Text like ‘%@%'” -i:TEXTLINE

Explanation of command :

Select Text into a.csv from current* where Text like ‘%@%’ –means find in files that begin with current(current*) all text that contains emails ( ‘%@%’) and put in file a.csv  the results.

-i:TEXTLINE – means the format is text

What can be more simple ?

(Ok, for finding the user name I had to resort to excel, to remove duplicates … )

More I think it is fast enough : for parsing 114 files with 58.8 MB (PC with a 2GB RAM + 7200 RPM ) the results are :

Statistics:
———–
Elements processed: 487176
Elements output:    1044
Execution time:     7.69 seconds

Also logparser can be used for more than text files :

http://support.microsoft.com/kb/910447

http://www.stevebunting.org/udpd4n6/forensics/logparser.htm

More, it can be as a COM DLL in every .NET project, making it a usefull tool . See

http://www.codeproject.com/KB/recipes/SimpleLogParse.aspx

Next time I will show the using Powershell in combination with LogParser.

My standard way to work with dropdown box in ASP.NET MVC – 2 steps (or 3 if you want to add long description)

I have written a small post about dropdownlist template in ASP.NET MVC here : http://msprogrammer.serviciipeweb.ro/2010/05/30/mvc-helper-templates/

I think that the dropdownlist should be explained more – aand the example will be :

First, let’s say we have Employee and Department. And we have Employee that has a field, named IDDepartment.

When edit/create a user we want to display a dropdownlist for Department in order for the user to choose the department.

Step 1 : obtain from database a list of departments and transform into List<KeyValuePair<string,string>>  – where the first string is DepartmentID and the second is Department Name.

Let’s say there is a method to do that : deptList.Values

Step 2 : display into the aspx/ascx file with List_KVP template

<%: Html.EditorFor(model => model.deptList.Values, “List_KVP”, “IDDepartment”, new { SelectedValue = Model.emp.IDDepartment })%>

Here is the weak part: the “IDDepartment” is not strongly typed. You can transform that …but it requires writing another extension. However, when you modify the code for

SelectedValue = Model.emp.IDDepartment

it is right nearby…

For reference, here is the List_KVP.ascx

<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl<List<System.Collections.Generic.KeyValuePair<string,string>>>" %>
<%
    string Val = "",style="";
    if(ViewData.Values != null && ViewData.Values.Count > 0)
    {
        Val = (ViewData["SelectedValue"]??"").ToString();
        style = (ViewData["Style"] ?? "").ToString();
    }
    if (style.Length > 0)
    {

    }
    var id=ViewData.TemplateInfo.GetFullHtmlFieldId("") ;
    id = id + "";
    %>

<select id="<%:id %>" name="<%:ViewData.TemplateInfo.GetFullHtmlFieldName("") %>" style="<%: style %>">
    <% foreach (var val in Model)
       { %>
            <option  value='<%: val.Key %>' <%:(val.Key == Val)?"selected=selected":"" %>><%: val.Value %></option>
    <%} %>
</select>
<% if(Model.Exists(x=>x.Key ==Val) )
{
       %>
       <script type="text/javascript">
   $(document).ready(function () {

       $('#<%:id  %>').change();
   }     );
      </script>
      <%
}
    %>

Oh, and if you ask how to add a description , nothing more simple :
Step1 : add to dropdown an onchange event : onchange=’javascript:funcDepartmentRetrieve(this)”
Step 2: create a java script function that retrieves the long description from the id

 <script type="text/javascript">
         <%: Html.JavaScriptFind( Model.deptList.LongDescriptionValues, "funcDepartmentLong","notfoundDepartment") %>
        </script>
  

Step 3 : Mix the 2 javascript functions

For your reference, the code for JavaScriptFind is

 public static MvcHtmlString JavaScriptFind(this HtmlHelper hh, ICollection<KeyValuePair<string,string>> values, string Name, string NotFound)
        {
            string s = "function " + Name + "(value){  switch(value){";
            string ret = "case '{0}' : return '{1}';" + Environment.NewLine;
            foreach (var item in values)
            {
                //TODO : compensate for '
                s += string.Format(ret, item.Key, item.Value);
            };
            s += string.Format("default : return '{0}' ;//+ value;"  + Environment.NewLine, NotFound);
            s += "};";//switch
            s += "}";//function
            return MvcHtmlString.Create(s);
        }

Monitor SqlServer Stored Proc execution time

I have had the task to made something to monitor what stored procedure, in Sql Server,in most used. The problem that I have had is that the host_name is easily changed by putting, in the connection string WSID (see http://msdn.microsoft.com/en-us/library/ms178598.aspx )
The code is for sql server 2005 and I have not realized without the help of http://sqlserver.ro/forums/thread/8332.aspx
So there are the three easy steps to have the monitor proceudre in place:
1. Define an audit table :

CREATE TABLE [Audit](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProcName] [varchar](128)
	[IdentifierAccess] [varchar](max)
	[DateAccess] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY]

2. Stored proc to insert data :

alter proc dbo.proc_Log(@procname varchar(100))

as
begin
declare @userid varchar(100)
BEGIN TRY
select @userid=client_net_address from sys.[dm_exec_connections] with(nolock)
where session_id = @@SPID
End TRY
BEGIN CATCH
--GRANT SELECT ON sys.dm_exec_connections TO the user or
--grant view server state to the user ! http://msdn.microsoft.com/en-us/library/ms186717.aspx
set @userid = SUSER_NAME()
End CATCH

INSERT INTO [Audit]
(
[ProcName]
,[IdentifierAccess]
)
VALUES
(@procname,
@userid
)
end

3. In each stored procedure put this at beginning

DECLARE @ProcName nvarchar(128)

    SET @ProcName = OBJECT_NAME(@@PROCID)

    exec proc_Log  @ProcName

And this at the end of stored proc :

    exec proc_Log  @ProcName

4. See what the procedure calling are and the time (basic)

select a1.*,'--',a2.* , datediff(ss,a2.DateAccess,a1.DateAccess) 
from [Audit] a1  inner join [Audit] a2 on
a2.ID =
( select max(ID) from [Audit] a2 where a1.ProcName= a2.ProcName and a1.IdentifierAcces=a2.IdentifierAcces and a1.ID > a2.ID )
order by 1

From there is a simple way to make a group by to have the sum * count or other relevant data ( which proceudres are most used in what times of the day, and so on)

Work from home :
1. From the previous select(put it into a Common table Expression ), see what procedure use most
a) number of uses
b) number of time
2. What is missing from Audit table definition ? (Hint :easy clustered index definition)

Caching in .NET

In every application you have some data that is more read-more, write-once or twice. For example you can have the list of Cities of a country, the list of Countries of the world or list of exchange currency. This data is modified rarely. Also, you can have data that is not very sensitive to be real-time , such as the list of invoices for the day.
In .NET 3.5 you have several options
1. ASP.NET caching – and implementing in other applications with HttpRuntime  ( even if MS says “The Cache class is not intended for use outside of ASP.NET applications”)

2. Enterprise caching block : hard to configure

3. memcached , velocity, sharedcache and other third party providers – that comes with their options and configuration

In .NET 4.0 you have a new kid : objectcache and , more important , an implementation : MemoryCache

http://msdn.microsoft.com/en-us/library/system.runtime.caching.memorycache%28v=VS.100%29.aspx

What is very good is that now you can cache in Memory what do you want – and apply easily to your Business Layer. More, the object is a singleton for the application – that is even better (see the test on the final of the post)

What it is missing is an easy implementation for List and an implementation to remove data after a defined time.

So I decided to do my implementation for that (ok, it is wrong to have both implementations in a single class – but you can separate easily )

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Caching;

namespace CachingData{

    /// <summary>
    /// List<int> i = new List<int>() { 1, 10, 100 };
            //CacheData_List<List<int>, int> data = new CacheData_List<List<int>, int>(2);
            //data.Add(i);
            //Assert.AreEqual(3, data.Items().Count, "must be 3");
            //data = new CacheData_List<List<int>, int>();
            //Assert.AreEqual(3, data.Items().Count, "must be 3");
            //Assert.IsTrue(data.ContainsData, "must have data");
            //Thread.Sleep(1000 * 3);
            //Assert.IsFalse(data.ContainsData, "must not have data");
    /// </summary>
    /// <typeparam name="T">and generic ILIST </typeparam>
    /// <typeparam name="U"></typeparam>
    public class CacheData_List<T, U>
        where T:class,IList<U>, new()

    {
        /// <summary>
        /// use this for adding in cache
        /// </summary>
        public event EventHandler RemovedCacheItem;

        private System.Timers.Timer timerEvent;
        private MemoryCache buffer;
        private int TimeToLiveSeconds;

        private DateTimeOffset dto;
        private string Key;
        /// <summary>
        /// default constructor - cache 600 seconds = 10 minutes
        /// </summary>
        public CacheData_List()
            : this(600)
        {
        }
        /// <summary>
        /// constructor cache the mentioned TimeSeconds time
        /// </summary>
        /// <param name="TimeSeconds">value of time for cache</param>
        public CacheData_List(int TimeSeconds)
        {
            TimeToLiveSeconds = TimeSeconds;
            timerEvent=new System.Timers.Timer(TimeToLiveSeconds * 1000);
            timerEvent.AutoReset = true;
            timerEvent.Elapsed += new System.Timers.ElapsedEventHandler(timerEvent_Elapsed);
            dto = new DateTimeOffset(DateTime.UtcNow.AddSeconds(TimeToLiveSeconds));
            Key = typeof(T).ToString();
            buffer = MemoryCache.Default;
        }

        void timerEvent_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            if (RemovedCacheItem != null)
            {
                RemovedCacheItem(this, EventArgs.Empty);
            }
        }
        /// <summary>
        /// remove item from cache
        /// </summary>
        public void Remove()
        {
            if (buffer.Contains(Key))
            {
                buffer.Remove(Key);

            }
            dto=new DateTimeOffset(DateTime.UtcNow.AddSeconds(TimeToLiveSeconds));
        }
        /// <summary>
        /// add multiple items to cache
        /// </summary>
        /// <param name="items">items to add to the cache</param>
        public void Add(T items)
        {

            if (buffer.Contains(Key))
            {
                T data = Items();
                foreach (var t in data)
                {
                    items.Add(t);
                }
                buffer.Remove(Key);
            }
            buffer.Add(Key, items, dto);
        }
        /// <summary>
        /// add a item to the IList of the cache
        /// </summary>
        /// <param name="item">an item to add</param>
        public void AddItem(U item)
        {

            T data=new T();
            if (buffer.Contains(Key))
            {
                data = buffer.Get(Key) as T;
                buffer.Remove(Key);
            }

            data.Add(item);
            buffer.Add(Key, data,dto);
        }
        /// <summary>
        /// usefull if you do not intercept the removed event
        /// </summary>
        public bool ContainsData
        {
            get
            {
                return buffer.Contains(Key);
            }

        }
        /// <summary>
        /// retrieve items
        /// </summary>
        /// <returns></returns>
        public T Items()
        {
            if (!buffer.Contains(Key))
                return null;

            return buffer.Get(Key) as T;
        }
    }
}

Please note that the test for usage is in the summary :

</pre>
List i = new List() { 1, 10, 100 };
CacheData_List <List<int>, int> data = new CacheData_List<List<int>, int>(2);
data.Add(i);
Assert.AreEqual(3, data.Items().Count, "must be 3");
data = new CacheData_List<List<int>, int>();
Assert.AreEqual(3, data.Items().Count, "must be 3");
Assert.IsTrue(data.ContainsData, "must have data");
Thread.Sleep(1000 * 3);
Assert.IsFalse(data.ContainsData, "must not have data");

You can download the file from

http://msprogrammer.serviciipeweb.ro/wp-content/uploads/2010/05/CachingData.zip

(Last Note : for synchonization maybe it was better to lock on readerwriterlockslim

How To … Create a new site similar with regular internet site in Windows 7

 

Step 1 : Make a Web site with Visual Studio

Step 2 : In IIS Manager create the new site

Step 3: edit the hosts file (administrative rights)

Step 4: Create the Web application, modify his Project=>properties=>Web =>Use local IIS Web Server

Step 5: Edit bindings for new site to point to the folder of application

Step 6: Run Visual Studio

How to … Make a bootable clone hard disk in Windows 7

Disclaimer  : it is a short story of the long story from http://onegeekwithalife.blogspot.com/2009/11/booting-from-cloned-vhd-in-win7.html 

Step 1 : Read http://onegeekwithalife.blogspot.com/2009/11/booting-from-cloned-vhd-in-win7.html  and confirm you have administrative rights to run programs.

Step 2 : Download tools

                          a) Disk2VHD from SysInternals, http://technet.microsoft.com/en-us/sysinternals/ee656415.aspx

                          b) VHD Resizer, http://vmtoolkit.com/files/folders/converters/entry87.aspx

                          c) BcdVHD, http://disk2vhd.codeplex.com/ .

                         d) Original Windows 7 disc with bootsect.exe

Step 3 : Run Disk2VHD and create a vhd file

image

Step 4 : Attach the VHD  file in the DiskManagement, make online, delete raw volumes and shrink hard disk. Detach VHD.

Step 5: Run VHD Resizer with selected VHD.

image

Step 6: Run BcdVHD in order to add this VHD to the boot configuration.

image

Run bootsect on VHD as mentioned in http://onegeekwithalife.blogspot.com/2009/11/booting-from-cloned-vhd-in-win7.html 

Step 7: Restart and confirm you successfully boot. Run %SystemDrive% on vhd. Note the drive

Step 8: Attach the registry of the file as mentioned in http://onegeekwithalife.blogspot.com/2009/11/booting-from-cloned-vhd-in-win7.html 

Step 9 : Restart

Step 10: Congratulations, you have a full VHD system to boot that is running now!