tag:blogger.com,1999:blog-907748370295992572024-03-14T06:01:25.062-07:00Unhandled ExceptionDone wrong is still done, but I will show you how to do it right.Erichttp://www.blogger.com/profile/12971684455904048002noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-90774837029599257.post-43191482516286551732012-11-10T12:00:00.001-08:002012-11-10T12:00:20.006-08:00MVC Elmah Remote Viewer<a href="https://sites.google.com/site/throwexfiles/elmahviewer/ElmahViewer.zip?attredirects=0&d=1">ElmahViewer Project</a><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<span style="font-family: "Trebuchet MS", sans-serif;">If you have never used <a href="http://code.google.com/p/elmah/">Elmah</a>, then I urge you to check it out via <a href="http://nuget.org/">NuGet</a>. 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. </span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<span style="font-family: "Trebuchet MS", sans-serif;">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. </span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<span style="font-family: "Trebuchet MS", sans-serif;">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.</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<span style="font-family: "Trebuchet MS", sans-serif;">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 <b>Elmah_Error</b> to limit the amount of work necessary to get the project running.</span><br />
<div style="background-color:#dddddd">
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<span style="font-family: 'Trebuchet MS', sans-serif;">USE [Internal]</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"></span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">GO</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<span style="font-family: "Trebuchet MS", sans-serif;">/****** Object: Table [dbo].[ELMAH_Error] Script Date: 11/10/2012 14:03:23 ******/</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">SET ANSI_NULLS ON</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">GO</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<span style="font-family: "Trebuchet MS", sans-serif;">SET QUOTED_IDENTIFIER ON</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">GO</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<span style="font-family: "Trebuchet MS", sans-serif;">SET ANSI_PADDING ON</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">GO</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<span style="font-family: "Trebuchet MS", sans-serif;">CREATE TABLE [dbo].[ELMAH_Error](</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[ErrorId] [uniqueidentifier] NOT NULL,</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Application] [nvarchar](60) NOT NULL,</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Host] [nvarchar](50) NOT NULL,</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Type] [nvarchar](100) NOT NULL,</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Source] [nvarchar](60) NOT NULL,</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Message] [nvarchar](500) NOT NULL,</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[User] [nvarchar](50) NOT NULL,</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[StatusCode] [int] NOT NULL,</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[TimeUtc] [datetime] NOT NULL,</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Sequence] [int] IDENTITY(1,1) NOT NULL,</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[AllXml] [ntext] NOT NULL,</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[AppName] [varchar](255) NULL,</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"> CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY NONCLUSTERED </span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">(</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[ErrorId] ASC</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">) ON [INDEXDATA] TEXTIMAGE_ON [PRIMARY]</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<span style="font-family: "Trebuchet MS", sans-serif;">GO</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<span style="font-family: "Trebuchet MS", sans-serif;">SET ANSI_PADDING OFF</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">GO</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<span style="font-family: "Trebuchet MS", sans-serif;">ALTER TABLE [dbo].[ELMAH_Error] ADD CONSTRAINT [DF_ELMAH_Error_ErrorId] DEFAULT (newid()) FOR [ErrorId]</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">GO</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<br />
</div>
<span style="font-family: "Trebuchet MS", sans-serif;">Create a new trigger:</span><br />
<div style="background-color: #dddddd">
<span style="font-family: "Trebuchet MS", sans-serif;"><br /></span>
<span style="font-family: "Trebuchet MS", sans-serif;"></span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">CREATE trigger [dbo].[tgElmahInsert]</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">on [dbo].[ELMAH_Error]</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">for insert</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">as</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">begin</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"> update e</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"> set e.AppName = substring(i.Application,len(i.Application) - charindex('/',reverse(i.Application))+2, charindex('/',reverse(i.Application)))</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"> from Inserted i </span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"> inner join dbo.Elmah_Error e on i.ErrorId = e.ErrorId</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">end</span><br />
</div>
<br />
<br />
<span style="font-family: Trebuchet MS, sans-serif;">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:</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b>The Main Report Section</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;">This comes complete with paging and jQuery UI buttons, using MVC and a WebGrid control.</span><br />
<hr/>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-5wmAgDMNDNs/UJ6smsbMV_I/AAAAAAAAAEw/oVqur4yJn9E/s1600/elmah_report.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="172" src="http://2.bp.blogspot.com/-5wmAgDMNDNs/UJ6smsbMV_I/AAAAAAAAAEw/oVqur4yJn9E/s640/elmah_report.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<span style="font-family: Trebuchet MS, sans-serif;"><b>This is the Cookie Section</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;">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.</span><br />
<hr/>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-Ba00BgtZEKQ/UJ6ssvcwXRI/AAAAAAAAAE4/1Ar_aMTk3qQ/s1600/elmah_cookie.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em; text-align: left;"><img border="0" height="166" src="http://1.bp.blogspot.com/-Ba00BgtZEKQ/UJ6ssvcwXRI/AAAAAAAAAE4/1Ar_aMTk3qQ/s640/elmah_cookie.png" width="640" /></a></div>
<span style="font-family: Trebuchet MS, sans-serif;"><b><br /></b></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b><br /></b></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b><br /></b></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b><br /></b></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b><br /></b></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b><br /></b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><b><br /></b></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b><br /></b></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b><br /></b></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b><br /></b></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b><br /></b></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b>Yellow Screen of Death</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;">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.</span><br />
<br />
<br />
<hr/>
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: left; margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="http://4.bp.blogspot.com/-D8swcu2Xw7I/UJ6sxnLbiwI/AAAAAAAAAFA/2in72N5UWAw/s1600/elmah_yellow_death.png" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="174" src="http://4.bp.blogspot.com/-D8swcu2Xw7I/UJ6sxnLbiwI/AAAAAAAAAFA/2in72N5UWAw/s640/elmah_yellow_death.png" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><span style="font-family: Trebuchet MS, sans-serif;">Yellow Screen of Death<br /></span></td></tr>
</tbody></table>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<span style="font-family: Trebuchet MS, sans-serif;"><b>Server, Form and QueryString variables</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;">In order to properly troubleshoot your application errors having access to some of the pertinent data is invaluable.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<br />
<hr/>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-8qwMVIeavNo/UJ6s2Iy9taI/AAAAAAAAAFI/UCDauojcsk4/s1600/elmah_server_vars.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em; text-align: left;"><img border="0" height="560" src="http://4.bp.blogspot.com/-8qwMVIeavNo/UJ6s2Iy9taI/AAAAAAAAAFI/UCDauojcsk4/s640/elmah_server_vars.png" width="640" /></a></div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>Erichttp://www.blogger.com/profile/12971684455904048002noreply@blogger.com0tag:blogger.com,1999:blog-90774837029599257.post-20154103940295983322012-10-22T14:09:00.000-07:002012-10-22T14:09:21.967-07:00LINQPad - Codesmith Database Deploy Utility<div>
<span style="font-family: Trebuchet MS, sans-serif;">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.</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">This little program will traverse your solution file and find all of your .<b>csproj</b> files. It will then read through that and look for any .<b>csp</b> 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. <b>Test</b> and <b>Live</b>. This way when I run through the project file I will always know which are the Test Servers and which are the Production Servers.</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">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 <b>SQLDMO </b>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.</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">You will need a couple of references added. </span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">System.Web</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">SQLDMO</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">I might update this and make it a bit more user friendly but this is just the first rough draft.</span></div>
<div>
<br /></div>
<pre formatcs="1">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; }
}
</pre>
Erichttp://www.blogger.com/profile/12971684455904048002noreply@blogger.com0tag:blogger.com,1999:blog-90774837029599257.post-28562491427672616252012-10-22T10:26:00.001-07:002012-10-22T12:33:12.407-07:00LINQPad Source Code Searcher<br />
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!).
<br />
<br />
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.
<br />
<br />
<pre formatcs=1>
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; }
}
</pre>Erichttp://www.blogger.com/profile/12971684455904048002noreply@blogger.com0tag:blogger.com,1999:blog-90774837029599257.post-45658414481315943242010-11-27T08:58:00.000-08:002010-11-27T08:58:55.217-08:00Dynamic LINQ to XML : Solving the OrderBy<span style="font-family: "Trebuchet MS", sans-serif;">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 <a href="http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx">here.</a> 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.</span><br />
<br />
<span style="font-family: Trebuchet MS;">The code that I was trying to execute looked something like this:</span><br />
<br />
<span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> <table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="http://1.bp.blogspot.com/_FJ8YtDMLbtA/TPE2M7gAyzI/AAAAAAAAAAo/xFdvxM6_VME/s1600/sampleCode.png" imageanchor="1" style="clear: left; cssfloat: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="75" ox="true" src="http://1.bp.blogspot.com/_FJ8YtDMLbtA/TPE2M7gAyzI/AAAAAAAAAAo/xFdvxM6_VME/s400/sampleCode.png" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click for larger image<br />
</td></tr>
</tbody></table> <table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="http://2.bp.blogspot.com/_FJ8YtDMLbtA/TPE0L_SolFI/AAAAAAAAAAg/05IZpNF90YI/s1600/samplexml.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" ox="true" src="http://2.bp.blogspot.com/_FJ8YtDMLbtA/TPE0L_SolFI/AAAAAAAAAAg/05IZpNF90YI/s1600/samplexml.png" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">This is the XML</td></tr>
</tbody></table> <br />
<br />
<br />
<br />
<span style="font-family: Trebuchet MS; font-size: small;">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. <br />
<br />
Change your predefinedTypes to look like this and recompile and you should be able to do dynamic OrderBy with LINQ to XML:</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_FJ8YtDMLbtA/TPE29QXronI/AAAAAAAAAAs/PbQ_3ocQUiA/s1600/predefinedTypes.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="306" ox="true" src="http://3.bp.blogspot.com/_FJ8YtDMLbtA/TPE29QXronI/AAAAAAAAAAs/PbQ_3ocQUiA/s320/predefinedTypes.png" width="320" /></a></div></span></span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"> </span>Erichttp://www.blogger.com/profile/12971684455904048002noreply@blogger.com0tag:blogger.com,1999:blog-90774837029599257.post-72837865178493656032010-11-23T18:13:00.000-08:002014-05-22T07:34:23.831-07:00SQL CLR Function<span style="font-family: "Trebuchet MS", sans-serif;">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.</span><br />
<br />
<span style="font-family: Trebuchet MS;">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.</span><br />
<br />
<span style="font-family: Trebuchet MS;">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. </span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div align="left">
<span style="font-family: Trebuchet MS;">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.</span></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div align="left">
</div>
<div align="left">
<span style="font-family: "Trebuchet MS", sans-serif;">Next cut and paste the following code below into your class file:</span></div>
<br />
<div align="left">
<pre class="csharpcode"><span class="kwrd">using</span> System;
<span class="kwrd">using</span> System.Collections;
<span class="kwrd">using</span> System.Collections.Generic;
<span class="kwrd">using</span> System.Data;
<span class="kwrd">using</span> System.Data.SqlClient;
<span class="kwrd">using</span> System.Data.SqlTypes;
<span class="kwrd">using</span> Microsoft.SqlServer.Server;
<span class="kwrd">public</span> <span class="kwrd">partial</span> <span class="kwrd">class</span> UserDefinedFunctions
{
[SqlFunction(Name = <span class="str">"fnToList"</span>, FillRowMethodName = <span class="str">"FillRow"</span>, TableDefinition = <span class="str">"ID NVARCHAR(255)"</span>)]
<span class="kwrd">public</span> <span class="kwrd">static</span> IEnumerable SqlArray(SqlString str, SqlChars delimiter)
{
<span class="kwrd">if</span> (delimiter.Length == 0)
<span class="kwrd">return</span> <span class="kwrd">new</span> <span class="kwrd">string</span>[1] { str.Value };
<span class="kwrd">return</span> str.Value.Split(delimiter[0]);
}
<span class="kwrd">public</span> <span class="kwrd">static</span> <span class="kwrd">void</span> FillRow(<span class="kwrd">object</span> row, <span class="kwrd">out</span> SqlString str)
{
str = <span class="kwrd">new</span> SqlString((<span class="kwrd">string</span>)row);
}
};
<style>
.csharpcode, .csharpcode pre
{
font-size: 8pt;
color: black;
font-family: Consolas, "Courier New", Courier, Monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
</style>
</pre>
</div>
<div align="left">
</div>
<div align="left">
<span style="font-family: Trebuchet MS;">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:</span><br />
<br /></div>
<div align="left">
</div>
<div align="left" style="background-color: white;">
<span style="font-family: "Courier New", Courier, monospace;"><span style="color: maroon;"><span style="color: maroon;">sp_configure</span></span> <span style="color: red;"><span style="color: red;">'clr enabled'</span></span><span style="color: grey;"><span style="color: grey;">,</span></span> <span style="color: black;">1</span><span style="color: grey;"><span style="color: grey;">;</span></span></span><span style="color: blue;"><span style="color: blue;"> <br />
<span style="font-family: "Courier New", Courier, monospace;">reconfigure</span></span></span><span style="font-family: "Courier New", Courier, monospace;"> <span style="color: blue;"><span style="color: blue;">with</span></span> <span style="color: blue;"><span style="color: blue;">override</span></span><span style="color: grey;"><span style="color: grey;">;</span></span></span></div>
<div align="left">
<span style="font-family: "Trebuchet MS", sans-serif;"><br />
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.<br />
If you did everything correctly you should see in SSMS in your Table-valued Functions a new function.</span><br />
<div align="left">
</div>
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div align="left">
<br />
To test this, you can simply run the following:<br />
<span style="color: white; font-family: 'Trebuchet MS', sans-serif;">nd it should produce the following output:</span></div>
<div align="left">
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: black;"></span><br />
</span></span><br />
<div class="separator" style="clear: both; text-align: center;">
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuT0n7VZ4ysiXS41jBWvo9sKLogbg3XHYofHnKrPej2X48gPVEB8wBr2dn32yY_0utt0OFcI5ZeGpFV1hnNZlWy15_XazdyhurnPPyxHqEfS9KG5CayRldUeSTETikMDrfIsu-U4u_Mx7H/s1600/clr_call.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuT0n7VZ4ysiXS41jBWvo9sKLogbg3XHYofHnKrPej2X48gPVEB8wBr2dn32yY_0utt0OFcI5ZeGpFV1hnNZlWy15_XazdyhurnPPyxHqEfS9KG5CayRldUeSTETikMDrfIsu-U4u_Mx7H/s1600/clr_call.png" height="292" width="640" /></a></span></span></div>
<br />
<div align="left">
<br /></div>
</div>
<div align="left" class="separator" style="clear: both; text-align: center;">
</div>
<div align="left" class="separator" style="clear: both; text-align: center;">
</div>
Erichttp://www.blogger.com/profile/12971684455904048002noreply@blogger.com0