SQL
Geometry data type support
LINQ to SQL and Entity Framework don’t support the geometry data type…
boo…
Great OR/M resources
Today I had a conversation with a co-worker that started with my declaration of not wanting to ever have to code a stored procedure. Adamantly I know that there are places where sprocs are the solution to a problem but in today’s landscape I feel those are edge cases.
Our argument..err..I mean conversation had all the usual discussion points.
co-worker points:
- sprocs are faster
- sprocs are more secure
- sprocs provide a layer in n-tier
My points:
- the dollar cost of milliseconds of speed
truesee comments; but all queries should be parameterized hence not a valid argument.- an unnecessary abstraction with no benefit (except as a pro sproc discussion point)
- sprocs often end up not separating query from command
I also made some claims, which I completely stand by, that might have seemed outlandish and bordering on reckless. Specify I said, based on the metric that 50% of a projects code is related to data access, that I would be able to complete a project in almost 40% less hours. I also suggested that sprocs, in 99.9% of their use, are a waste of time and since time = money that his project was wasting thousands of dollars writing sprocs.
With that wonderful setup I would now like to present my compliation of required OR/M, LINQ and SQL information. AKA: For the love of god at least digest this information before telling me that all data access should be done through stored procedures!
ALT.NET 7: OR/M
http://altnetpodcast.com/episodes/7-object-relational-mapping/
In this episode Ward Bell (from IdeaBlade) and Jeremy Miller discuss Object-Relational Mapping and when to use ORM tools. Ward and Jeremy discuss the following:
- Object-Relational Mapping
- Object-first vs. Data-first Approaches
- Evolutionary Database Design
- Evolutionary Design
- Code Generation
- Persistence Ignorance
- POCO
- Domain-Driven Design
- The Vietnam of Computer Science
Hanselminutes
Rob Conery limps and learns about Domain Driven Design 36:28 12/1/2008 Hanselminutes is a weekly audio talk show with noted web developer and technologist Scott Hanselman and hosted by Carl Franklin. Scott discusses utilities and tools, gives practical how-to advice, and discusses ASP.NET or Windows issues and workarounds.
Herding Code 38
NHibernate performance with Ayende, David Penton, and Ben Scheirman 44:22 3/13/2009 While K Scott and Jon were at the Microsoft MVP Global Summit, we listened in on a late night debate on NHibernate performance between Oren Eini (a.k.a. Ayende Rahein), David Penton, and Ben Scheirman. Show Links: NHibernate – http://nhforge.org Ayende&#
Herding Code 10
Episode 10: LINQ 44:01 7/23/2008 K Scott leads us in a discussion of LINQ, including: What is it How introducing LINQ to .NET changed the framework LINQ Providers LINQ to XML LINQ to SQL – how it’s different from EF, tips and tricks, when to use it Links: LINQpad 3rd Party LINQ
NET Rocks!
Peter DaBetta and Adam Machanic on creating good SQL databases
Peter DaBetta and Adam Machanic on creating good SQL databases 1:00:07 5/18/2009 .NET Rocks! is an Internet Audio Talk Show for Microsoft .NET Developers.
.NET Rocks!
Ward Bell on ORMs
Ward Bell on ORMs. 59:24 1/12/2009 .NET Rocks! is an Internet Audio Talk Show for Microsoft .NET Developers.
Articles:
Why I do not use Stored Procedures : Jeremy D. Miller
http://codebetter.com/blogs/jeremy.miller/archive/2006/05/25/145450.aspx
http://blogs.msdn.com/adonet/archive/2008/03/27/ado-net-entity-framework-performance-comparison.aspx
- Yep. I can read. EF is slower…but we are talking milliseconds here!
I wonder if I could somehow come up with the total milliseconds a project “saved” by using nothing but sproc and extrapolate out the cost per millisecond….off the top of my head I would suspect it at least 500$ per millisecond….
NHibernate: Testing The Performance Urban Legend
http://www.iamnotmyself.com/2008/07/02/NHibernateTestingThePerformanceUrbanLegend.aspx
ASP.NET MVC SiteMap
Note: This code does not support caching or security trimming. It is simply a way to build something that compares to the .NET TreeView control using the MVC pattern.
Recently one of my clients had the "perfect storm" of requirements, restrictions, deadlines and policies that lead to the need to develop a MVC TreeView(ish) control.
Requirements:
- Database storage – Site administrations needed a simple interface into editing and I was not about to write something to update XML files.
- NOT use the SQL Service Broker for easy SQL caching support – Security concerns….I assume…I was never told why NOT but just NO.
- Support the MVC pattern – new projects where being developed using ASP.NET MVC
- Easy to implement and maintain
Design:
- With MS shipping jQuery it would be nice to use a jQuery plugin to render the tree
- Leverage existing technology – Exising project are using the SQL SiteMap Provider, hence, accompanying tables and stored procedures.
- Extend the ASP.NET MVC HtmlHelpers class
Note: I assume your application is currently setup to use the SQL SiteMap Provider…or at least the Table for the provider exists.
Step 1: Create a Stored Procedure that uses a CTE
Most of the heavy lifting happens here. As you can see, when viewing the full-size image, the results are ordered by "thePath". That makes parsing the results into a ordered list very easy.
1: WITH siteMapCTE(SYSID, ID, Parent,title,description,url,roles, level, thePath) AS (
2: SELECT SYSID, ID, Parent,title,description,url,roles, 1 as level, CONVERT(VARBINARY(MAX), ID) AS thePath
3: FROM aspnet_SiteMap WHERE Parent is NULL
4: UNION ALL
5: SELECT e.SYSID, e.ID, e.Parent,e.title,e.description,e.url,e.roles, c.level + 1, c.thePath + CONVERT(VARBINARY(MAX), e.ID) AS thePath
6: FROM aspnet_SiteMap e
7: INNER JOIN siteMapCTE c
8: ON e.Parent = c.ID
9: )
10: SELECT * FROM siteMapCTE ORDER BY thePath, title
Step 2: Create new HtmlHelper method
First off..Stephen Walther’s first ASP.NET MVC article was on writing html helpers using Extension Methods. I have his sample in a current project so it was a great place to drop in the new method.
<Extension()> _
Public Function ListBasedSiteMap(ByVal HtmlHelper As HtmlHelper, ByVal items As Object) As String
If items Is Nothing Then
Throw New ArgumentNullException("items")
End If
Dim builder As New StringBuilder()
Dim mPreviousLevel As Integer
Dim mUrl As String = String.Empty
builder.Append("<div class=""demo"" id=""testMap""><ul>")
For Each row As DataRow In items.Rows
If row.Item("url").ToString.Contains("~/") = True Then
mUrl = "/" & System.Configuration.ConfigurationManager.AppSettings("application.name") & "" & row.Item("url").ToString.Split("~/")(1)
Else
mUrl = row.Item("url").ToString
End If
If row.Item("level") = 1 Then
builder.AppendLine("<li id=""" & row.Item("id") & """ class=""open""><a href=""" & mUrl & """>" & row.Item("title") & "</a>")
ElseIf mPreviousLevel < row.Item("level") Then
'child
builder.AppendLine("<ul>")
builder.AppendLine("<li id=""" & row.Item("id") & """><a href=""" & mUrl & """>" & row.Item("title") & "</a>")
ElseIf mPreviousLevel = row.Item("level") Then
builder.AppendLine("<li id=""" & row.Item("id") & """><a href=""" & mUrl & """>" & row.Item("title") & "</a>")
ElseIf mPreviousLevel > row.Item("level") Then
'close level
builder.AppendLine("</li>")
builder.AppendLine("</ul>")
builder.AppendLine("<li id=""" & row.Item("id") & """><a href=""" & mUrl & """>" & row.Item("title") & "</a>")
Else
'parent ?
End If
added.Add(row.Item("id"))
mPreviousLevel = row.Item("level")
Next
builder.AppendLine("</ul></div>")
Return builder.ToString()
End Function
Oh…I pass the results in as a data table….you can change that to be whatever your passing….
Step 3: Get results to your view
How ever you like, get the data to the view. Again, I am using my own DAL and pass the results in as a Data Table
Step 4: Put the unordered list on the view
1: <%=Html.ListBasedSiteMap(ViewData("siteMap"))%>
Don’t forget to import the HtmlHelpers namespace…in your paper (I presume its a master page or the web.config)
1: <%@ Import Namespace="yourNameSpace.HtmlHelpers" %>
Break
Ok, at this point you should have an non-styled unordered list of SiteMap nodes that looks something like. All we have to do now is style, wire and go. As a matter of fact, the sitemap should work too!…it just looks like crap.
Step E: jsTree!
I’m not going to walk you through the process of wiring up your site to use jsTree but that is the next step. download, and put the files into your project, add the script and link tags for the javascript and css…modify the CSS as needed…
So maybe I did walk you through it…anyway.
Step VI: onload event….um…
I’m using dojo for this project with the exception of the TreeView so I leveraged dojo’s OOP and onload….this should work but its not tested.
1: var first = null;
2: $(function() {
3: tree1 = new tree_component();
4: tree1.init(jQuery("#testMap"), {
5: cookies: {
6: prefix: "sitemap",
7: opts: { path: '/' }
8: },
9: callback: {
10: onchange: function(NODE) {
11: if (!first) {
12: first = true;
13: } else { document.location.href = jQuery(NODE).children("a:eq(0)").attr("href"); }
14: }
15: }
16: });
17: });
Finish
Here is what I ended up with…
Again, I know…no caching…no security trimming but it works and you can allow management of the nodes from a web interface (of course you have to code that) … If anyone adds trimming please let me know…I don’t have a need now but I’d like to keep this all together in one place.
Content Segregation is a bad idea
In the past, when building small web applications with up to a couple concurrent users, I’ve placed the database on the same system as the web server. Blasphemy!!!…not really. You see, knowing that database systems will entirely consume their allocated memory allows for clear capacity planning.
There are many factors that need to be taken into consideration when designing an architecture and for the sake of this post lets just say that:
- This system does not require a ridiculous amount of memory
- This system does not require HA
And really, while every project sponsor will tell you that their project requires the most powerful server ever built and for the sake of humanity can never go offline again, most web applications don’t require all that much memory…and the fate of the company is not dependant on 100% uptime…just check the SLA.
So what does this all have to do with “Content Segregation”? Oh, and WTF is “Content Segregation”?
Ok, ok, just one more paragraph to provide context.
Large web applications with high concurrent user counts will commonly separate not only content types across systems but also application modules. For example, there might be a small web server cluster for images or a separate group of servers for the account management modules (login, register, profile). These practices are commonly accepted and I couldn’t agree with them more. Its just that most of the applications I am involved with don’t require this type of advanced architecture even though the project sponsor would have you believe otherwise. These, not the large web applications, are those we need to discuss..
So what does this all have to do with “Content Segregation”? Oh, and WTF is “Content Segregation”?
Until recently performance and availably have been the only reasons I’ve recognized to separate content across physical servers. That is until just very recently, in a meeting of the minds it was stated, as fact, that “Items of content type X are data and should be stored on the database server”
WTF? Let me clarify, not IN the database, but on the database server file system..
Ok, I have my head around what you are saying, I understand that your an advocate of Content Segregation, I just don’t understand why, but I can tell you why not.
1. Performance
Database servers are commonly separate physical systems for performance reasons. The DB server should not be a dumping ground of user generated content. It might seem silly but there is a chance that you might really use the SQL server for something that requires its full power. Maybe some form of analytics and when that happens you need to have the available power.
2. Slippery Slope
I truly hate this argument but it fits so perfect into this context. Its not so far of a leap to go from storing images on the database file system to storing images in the database.