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; }
}

Saturday, November 27, 2010

Dynamic LINQ to XML : Solving the OrderBy

With every new inovation lies new problems.  If you haven't checked out the awesome Dynamic library for LINQ to whatever,  you should.  The GU has a great post about it here.  I started using this and it worked great for LINQ to SQL, which is what I was using it for at the time.  Later I decided that I wanted to do some complex paging and sorting with a result set that was returning an XML document.  Based upon what I read in Scott's post it seemed like it supported LINQ to XML.  Well it sorta didn't.  You have to modify the Dynamic.cs file in order to get this to work.

The code that I was trying to execute looked something like this:


Click for larger image

This is the XML




The problem is that without any changes to the DynamicLibrary you get some error that either the Element is not a method of your object or your orderby selection is not a valid property. Ultimately it means that LINQ to XML doesn't support this right out of the box. Now I read another post that stated that the problem was that a few Objects were not in the predefinedTypes and so that got me thinking and I modified the Dynamic.vb file in my project...thats right V freakin B. I am sure the changes are quite similar in the Dynamic.cs file if you are programming in C#, but I was programming in VB.

Change your predefinedTypes to look like this and recompile and you should be able to do dynamic OrderBy with LINQ to XML:



 

Tuesday, November 23, 2010

SQL CLR Function

One of the most commonly used functions in SQL is the ability to take a delimited string and use it in a query.  Before the advent of .NET the only real way to solve this problem was to break the string apart by brute force using SQL and looping through the string looking for the delimiter, tokenizing out the strings into a return table.

Those days are over.  Not only was that very cludgey but wasn't all that effecient as well.  Now with .NET we can create a CLR function to do all the heavy lifting.

First you will need to create a Database Project.  For this post I will be documenting how I did it using Visual Studio 2010.  First thing you will do is open up your IDE and navigate to "New Project --> Database --> SQL Server".  Take special note on how you name this project.  I would name it something like {dbName}CLR as it will be the project where you will put all of your CLR Functions for a specific database. 



Next you will then "Add a New Item" and choose the type "Class".  This class file can be named anything, but I would keep it sort of generic as it will most likely be the same project that you add all of your functions to.
Next cut and paste the following code below into your class file:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "fnToList", FillRowMethodName = "FillRow", TableDefinition = "ID NVARCHAR(255)")]
    public static IEnumerable SqlArray(SqlString str, SqlChars delimiter)
    {
        if (delimiter.Length == 0)
            return new string[1] { str.Value };
        return str.Value.Split(delimiter[0]);
    }

    public static void FillRow(object row, out SqlString str)
    {
        str = new SqlString((string)row);
    }
};

Before you can deploy the class, you will need to enable clr on your SQL Server.  To do this you can execute the following commands:

sp_configure 'clr enabled', 1;
reconfigure
with override;

After you compile the Class, which will hopefully be error-free, you can then Deploy the project. When you deploy the project it will push the Assembly to the database that you specified during the Project creation. Visual Studio will also create the user defined function for you.
If you did everything correctly you should see in SSMS in your Table-valued Functions a new function.


To test this, you can simply run the following:
nd it should produce the following output: