Saturday, November 10, 2012

MVC Elmah Remote Viewer

ElmahViewer Project

If you have never used Elmah, then I urge you to check it out via NuGet.  It is the quintessential .NET logging mechanism. Most people will probably use Elmah out of the box with very little configuring. This post doesn't address those implementations. If you use Elmah to log to a sql server database then this post is tailored for you.

By using SQL server as your centralized logging mechanism, you don't need direct access to a web server.  Also, some users may have their website on a farm of servers, and trying to traverse each server can be a bit of a pain.  If you are like me, then you will start using Elmah for a plethora of web projects.  I have intranet projects as well as internet projects wired up to use Elmah.  

Elmah does a good job of grabbing all of the details that comprise the error and packaging it up into a nifty little XML object.  What I needed was the ability to take all those various different applications and have one internal web page / site, where I could go through the various different errors and quickly filter through them to concentrate on a specific application.  In order to do this I had to modify the Elmah SQL script a bit and add a new column.

The only thing you should really have to do with this project is probably edit the EF object or delete it and add a new one specifying your connection string.  I would advise to keep the table name as Elmah_Error to limit the amount of work necessary to get the project running.

USE [Internal]

GO

/****** Object:  Table [dbo].[ELMAH_Error]    Script Date: 11/10/2012 14:03:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ELMAH_Error](
[ErrorId] [uniqueidentifier] NOT NULL,
[Application] [nvarchar](60) NOT NULL,
[Host] [nvarchar](50) NOT NULL,
[Type] [nvarchar](100) NOT NULL,
[Source] [nvarchar](60) NOT NULL,
[Message] [nvarchar](500) NOT NULL,
[User] [nvarchar](50) NOT NULL,
[StatusCode] [int] NOT NULL,
[TimeUtc] [datetime] NOT NULL,
[Sequence] [int] IDENTITY(1,1) NOT NULL,
[AllXml] [ntext] NOT NULL,
[AppName] [varchar](255) NULL,
 CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY NONCLUSTERED 
(
[ErrorId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [INDEXDATA] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ELMAH_Error] ADD  CONSTRAINT [DF_ELMAH_Error_ErrorId]  DEFAULT (newid()) FOR [ErrorId]
GO


Create a new trigger:


CREATE trigger [dbo].[tgElmahInsert]
on [dbo].[ELMAH_Error]
for insert
as
begin
   update e
      set e.AppName = substring(i.Application,len(i.Application) - charindex('/',reverse(i.Application))+2, charindex('/',reverse(i.Application)))
     from Inserted i 
    inner join dbo.Elmah_Error e on i.ErrorId = e.ErrorId
end


Once you have the database stuff done and you are getting errors logged to the Elmah_Error table in the database of your choice, you are just about ready to start viewing them.  This is what some of the views will look like:

The Main Report Section
This comes complete with paging and jQuery UI buttons, using MVC and a WebGrid control.



This is the Cookie Section
Since Elmah took the time to package up the cookies, I took the time to display them almost the same way Elmah does out of the box, however I decided to break apart Cookie Collections if used.














Yellow Screen of Death
Error information wouldn't be complete without all the detailed error information and keeping with the original style, I show it to you right here in yellow background.



Yellow Screen of Death

Server, Form and QueryString variables
In order to properly troubleshoot your application errors having access to some of the pertinent data is invaluable.




Monday, October 22, 2012

LINQPad - Codesmith Database Deploy Utility

If you use CodeSmith Tools and are heavily dependent on the use of Stored Procedures as I am, then perhaps this little program will be of use to you.  It's kind of a hack as I threw it together in less than an hour, but it serves to solve a fairly unique problem.  Rather than going out and buying an expensive tool that compares your databases together and produces some sort of update script, I decided that it didn't make sense to make wholesale changes as there maybe some procs that aren't updated that have been deprecated or just aren't ready yet.

This little program will traverse your solution file and find all of your .csproj files.  It will then read through that and look for any .csp files, which are codesmith project files.  This is where I keep all of the procedures that are used for that specific Domain project.  I edited my CodeSmith csp files and included two new variables at the very top that are very similar to the Connection String item.  I name them the same in all my projects. Test and Live.  This way when I run through the project file I will always know which are the Test Servers and which are the Production Servers.

After it has gone through all of the project files and built up a few lists of all the items that it needs to compare it utilizes the SQLDMO object to pull the actual Source Code for each procedure.  I run it through a couple of string normalization steps so as not to confuse whitespace issues as well as mixed capitalization.  Depending on who the DBA is they might decide to make some textual edits and I just don't want to have false-positives come up.

You will need a couple of references added.  

System.Web
SQLDMO

I might update this and make it a bit more user friendly but this is just the first rough draft.

void Main()
{
    string solutionFile = @"path to your solution file here";
    string solutionfldr = @"path to your solution folder here";
    
    List<string> projectFiles = new List<string>();
    List<string> cspFiles = new List<string>();
    List<DatabaseProcs> dbs = new List<DatabaseProcs>();
    
    using (FileStream fs = new FileStream(solutionFile, FileMode.Open, FileAccess.Read))
    {
        using (StreamReader sr = new StreamReader(fs))
        {
            while (!sr.EndOfStream)
            {
                string line = sr.ReadLine();
                if (line.StartsWith("Project") && line.Contains(".csproj"))
                {
                    var arr = line.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                    projectFiles.Add(string.Format("{0}{1}", solutionfldr, arr[1].Replace("\"", "").Trim()));
                }
            }
        }
    }
    //projectFiles.Dump();
    
    foreach (string projfile in projectFiles)
    {
        string projFldr = projfile.Substring(0, projfile.LastIndexOf("\\"));
        using (FileStream fs = new FileStream(projfile, FileMode.Open, FileAccess.Read))
        {
            using (StreamReader sr = new StreamReader(fs))
            {
                var xml = XElement.Parse(string.Format("<root>{0}</root>", sr.ReadToEnd()
                    .Replace("<?xml version=\"1.0\" encoding=\"utf-8\"?>","")
                    .Replace("xmlns=\"http://schemas.microsoft.com/developer/msbuild/2003\"", "")));
                
                xml.Elements("Project")
                    .Elements("ItemGroup")
                    .Elements("Generate")
                    .Select(x => x.Attribute("Include"))
                    .Where (x => x.Value.Contains(".csp"))
                    .ToList()
                    .ForEach(a => cspFiles.Add(string.Format("{0}\\{1}", projFldr, a.Value)));
                
                xml.Elements("Project")
                    .Elements("ItemGroup")
                    .Elements("None")
                    .Select (x => x.Attribute("Include"))
                    .Where (x => x.Value.Contains(".csp"))
                    .ToList()
                    .ForEach(a => cspFiles.Add(string.Format("{0}\\{1}", projFldr, a.Value)));
            }
        }   
    }
    //cspFiles.Dump();
    
    foreach (string csp in cspFiles)
    {
        DatabaseProcs db = new DatabaseProcs();
        db.CSPName = csp.Substring(csp.LastIndexOf("\\")+1).Replace(".csp", "");
        using (FileStream fs = new FileStream(csp, FileMode.Open, FileAccess.Read))
        {
            using (StreamReader sr = new StreamReader(fs))
            {
                var xml = XElement.Parse(sr.ReadToEnd().Replace("xmlns=\"http://www.codesmithtools.com/schema/csp.xsd\"", ""));
                xml.Elements("propertySets")
                    .Elements("propertySet")
                    .Elements("property")
                    .Where (x => x.Attribute("name").Value == "IncludeList")
                    .Elements("stringList")
                    .Elements("string").Select (x => x.Value).ToList()
                    .ForEach(a => db.Procedures.Add(a.ToString().Replace("\\b", "")));
                
                var srvrs = xml.Elements("variables").Elements("add").Where (x => x.Attribute("key").Value == "Live" || x.Attribute("key").Value == "Test");
                //srvrs.Dump();
                
                var test = srvrs.Where(s => s.Attribute("key").Value == "Test").Select (s => s.Attribute("value").Value).FirstOrDefault().Replace(';','&');
                NameValueCollection testObj = HttpUtility.ParseQueryString(test);
                var live = srvrs.Where(s => s.Attribute("key").Value == "Live").Select (s => s.Attribute("value").Value).FirstOrDefault().Replace(';','&');
                NameValueCollection liveObj = HttpUtility.ParseQueryString(live);
                db.LiveServer.Server = liveObj["Server"];
                db.LiveServer.User = liveObj["User"];
                db.LiveServer.Password = liveObj["Password"];
                db.LiveServer.Database = liveObj["Database"];
                db.TestServer.Server = testObj["Server"];
                db.TestServer.User = testObj["User"];
                db.TestServer.Password = testObj["Password"];
                db.TestServer.Database = testObj["Database"];
                
            }
        }
        dbs.Add(db);
    }
    //dbs.Dump();
    
    string sep = Environment.NewLine + "GO;" + Environment.NewLine;
    
    foreach (var srvr in dbs)
    {
        string grant = string.Empty;
        grant = "grant exec on {0} to your_db_user_goes_here";
            
        grant += Environment.NewLine + "go;" + Environment.NewLine;
        
        List<ProcedureData> test = GetProcedureData(srvr.Procedures, srvr.TestServer);
        List<ProcedureData> live = GetProcedureData(srvr.Procedures, srvr.LiveServer);
        
        string filePath = string.Format("c:\\LinqPad\\SQLDeployments\\{0}.sql", srvr.CSPName);
        
        Console.WriteLine(filePath);
        
        using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite))
        {
            using (StreamWriter sw = new StreamWriter(fs))
            {
                test.GroupJoin(live, t => t.ProcedureName, l => l.ProcedureName, ( t, l ) => new { t, l})
                    .SelectMany (t => t.l.DefaultIfEmpty(new ProcedureData { ProcedureName = string.Empty, Script = string.Empty}), (t, l) => new { test = t.t, live = l})
                    .Select (t => new { ProcName = t.test.ProcedureName, TestProcScript = t.test.Script, LiveProcScript = t.live.Script})
                    .Where (x => Flatten(x.LiveProcScript) != Flatten(x.TestProcScript))
                    .ToList()
                    .ForEach(x => sw.Write(
                        (x.LiveProcScript.Length > 0 
                            ? x.TestProcScript.Replace("CREATE","ALTER").Replace("create","alter") + sep
                            : x.TestProcScript + sep + string.Format(grant, x.ProcName))));
                    
                test.GroupJoin(live, t => t.ProcedureName, l => l.ProcedureName, ( t, l ) => new { t, l})
                    .SelectMany (t => t.l.DefaultIfEmpty(new ProcedureData { ProcedureName = string.Empty, Script = string.Empty}), (t, l) => new { test = t.t, live = l})
                    .Select (t => new { ProcName = t.test.ProcedureName, TestProcScript = t.test.Script, LiveProcScript = t.live.Script})
                    .Select (n => new 
                        { 
                            ProcName = n.ProcName, 
                            TestEqualsLive = Flatten(n.LiveProcScript) == Flatten(n.TestProcScript),
                            TestProcSize = Flatten(n.TestProcScript).Length,
                            LiveProcSize = Flatten(n.LiveProcScript).Length//,
                            //testScript = Flatten(n.TestProcScript),
                            //liveScript = Flatten(n.LiveProcScript)
                        })
                    .ToList()
                    .Dump();
            }
        }
    }
}

public List<ProcedureData> GetProcedureData(List<string> procs, ServerData srvr)
{
    List<ProcedureData> procedures = new List<ProcedureData>();
    SQLDMO.SQLServer server = new SQLServer();
    server.Connect(srvr.Server, srvr.User, srvr.Password);
    foreach (SQLDMO.Database db in server.Databases)
    {
        if (db.Name.ToLower() == srvr.Database.ToLower())
        {
            foreach (SQLDMO.StoredProcedure procedure in db.StoredProcedures)
            {
                foreach (var proc in procs)
                {
                    if (procedure.Name.ToLower() == proc.ToLower())
                    {
                        procedures.Add(new ProcedureData() { ProcedureName = proc, Script = procedure.Script() });
                    }
                }
            }
        }
    }
    return procedures;
}

private static readonly Regex normalizeSpace = new Regex(@"\s+", RegexOptions.Compiled);

public string Flatten(string proc)
{
    proc = proc.ToLower().Replace("[", "").Replace("]", "").Replace("\t", "").Trim();
    var procarr = proc.Split(new char[] {'\n'}, StringSplitOptions.RemoveEmptyEntries);
    return string.Join("\n", procarr.Select (p => p.Trim()).Where(p => p.Length > 1).Select(d => normalizeSpace.Replace(d, " ")).ToArray());
}

public string GetProcedure(SQLDMO.StoredProcedures procs, string procName)
{
    foreach (SQLDMO.StoredProcedure proc in procs)
    {
        if (proc.Name.ToLower() == procName.ToLower())
        {
            return proc.Script();
        }
    }
    return string.Empty;
}

public string GetFunction(SQLDMO.UserDefinedFunctions funcs, string funcName)
{
    foreach (SQLDMO.UserDefinedFunction func in funcs)
    {
        if (func.Name.ToLower() == funcName.ToLower())
        {
            return func.Script();
        }
    }
    return string.Empty;
}

public class DatabaseProcs
{
    public List<string> Procedures;
    public ServerData TestServer { get; set; }
    public ServerData LiveServer { get; set; }
    public string CSPName { get; set; }
    
    public DatabaseProcs()
    {
        Procedures = new List<string>();
        TestServer = new ServerData();
        LiveServer = new ServerData();
    }
}
public class ProcedureData
{
    public string ProcedureName { get; set; }
    public string Script { get; set; }
}

public class ServerData
{
    public string Server { get; set; }
    public string User { get; set; }
    public string Password { get; set; }
    public string Database { get; set; }
}

LINQPad Source Code Searcher


As I had been working in a Classic ASP environment in the past, there were many database procedures that we still used on a day to day basis and there was need to be able to make changes to our new system, written in C# MVC 3 Razor. If anyone has had to work in classic ASP, you can understand just how disjointed it can be having database calls mixed in with your presentation layer (bleh!).

So my big challenge was that anytime a modification needed to be done and we weren't sure whether or not the procedure that we were about to deploy was being used elsewhere, I decided using LINQPad (I love this tool), that I would write a very simple script that would go out and search all of our Source and find all of the procedures that were being called and list out the filename that was actually involved.

void Main()
{
    DirectoryInfo d = new DirectoryInfo("c:\\{dir}\\{dir}");
    // Directories that we don't want to scan
    List<string> OmitDirectories = new List<string>();
    OmitDirectories.Add("c:\\{dir}\\{dir}\\{dir}");
    
    var dirList = d.GetFiles("*.asp", SearchOption.AllDirectories)
        .ToList()
        .Select(x => x.FullName);

    foreach (var omit in OmitDirectories)
    {
        dirList = dirList.Where(x => !x.Contains(omit)).ToList();
    }
    
    List<ProcData> Procs = new List<ProcData>();
    int counter = 0;
    int found = 0;
    foreach (string filename in dirList)
    {
        string text = File.ReadAllText(filename);
        Regex regexObj = new Regex(@"\b([-A-Z.]+)(\.dbo\.)([-A-Z.]+)|(dbo\.)([-A-Z.]+)?", RegexOptions.IgnoreCase);
        Match matchResults = regexObj.Match(text);
        string proc = string.Empty;
        if (matchResults.Success)
        {
            found++;
            for (int i = 1; i < matchResults.Groups.Count; i++) {
                Group groupObj = matchResults.Groups[i];
                proc += groupObj.Value;
            }
            Procs.Add(new ProcData() { Proc = proc, FileName = filename });
            if (found % 100 == 0)
                Console.WriteLine("Found:{0}...", found);
        }       
        string message = string.Format("Reading :{0}", filename);
        counter++;
        if (counter % 1000 == 0)
            Console.WriteLine("Records Read:{0}", counter);
    }
    Console.WriteLine("Read through {0} files", counter);
    Procs.Dump();
}

public class ProcData
{
    public string Proc { get; set; }
    public string FileName { get; set; }
}