Or ORM's Are Not the Whole Answer
OR Some ORM's Are A Better Answer

One point I feel I need to make.  So many people seem to think that at some point an ORM will appear that will just magically work.  It will map all your entities to objects and "just work."

So an endless parade of ORM's continue to lurch forth from the hinterlands. 

I'm amazed that people like Bruce Eckel think that an ORM powerful enough will just solve the problem.

The thinking is usually something like "we can shield the user from that ugly, mean SQL with some nice procedural and markup tricks." 

I've come to believe that anytime you try to hide/wrap/abstract a powerful technology/language (SQL, JavaScript, HTML, etc) you end up with at best a leaky abstraction and at worst a complete mess.

SQL is concise and powerful.  It doesn't need to be hidden.  It's not that hard to understand.  It's at least easier to understand than most of the ORM frameworks.  It's quite ridiculous what kind of code acrobatics you must perform to add parameters to a query. 

One of the biggest problems with an ORM is predicting what kind of code they will generate/execute.  The more considerate frameworks include a logging function of some sort.  This is nice but when I go to look at some code and I find a bunch of ORM garbage code, I find myself wishing for the SQL.

I used to hate embedded SQL, then I realized there really is nothing wrong with it, and it fact it's preferable as I found out what a web page is doing all in one file, without analyzing a (now unsupported) ORM or trying to find a stored procedure in the database.

The stored procedure approach is not that bad, I just find myself shying away from them as code re-use is a myth.  Yes, now I've become a true OOP Heretic.  Code re-use, especially between projects, is a rarity.  And this is the major problem with ORM's, they tend to emphasize the one object for every situation paradigm.

If I have an employee table, naturally I will have an employee object, if I'm an OOP ORM Myrmidon.  So I try to use this Employee object for my employee edit screen, my employee absence report, my employee salary report, my company org chart, etc, etc.  However, I soon find my nice clean ERD and UML diagrams become corrupted by the business needs.  We have to join in extra tables.  Pretty soon we are writing all manner of custom logic in our programming language of choice.  We have to use a little somthing like join_to(tables.employee_benefits).get_by(table.columns.user_name='a%').limit(10).sort_by(table.columns.user_name). 

We are wasting resources by bringing back unneeded columns.  Because the One True Employee contains fields for every situation, we populate the whole object for every request.  We can resort to lazy loading and other performance optimizations for expensive entities. 

The Better Solution

The answer is that there is no answer.  However, I think the frameworks like CoolStorage.NET and the db library in Web.Py have a nice ability to load data from queries into objects.  This takes away about 80 percent of the pain of Database/Object interaction and let's us use queries specific to the Use Case at hand.

I think the best we can do is to use the best tool for the job, in the case of talking to databases that tool is SQL.  We should use SQL and take advantage of automatic mapping. 

Learning SQL one time is much better than learning a new ORM every year.  I feel the same for HTML, JavaScript, CSS and the rest of fundamental technologies of our trade. 

http://www.aspfaqs.com/webtech/042606-1.shtml

One of the best (not to mention easiest) paging methods I've seen.  In fact it requires no temp tables or table variables. 

Paging is something that is so common yet seems to hard to get right and keep performant that it pays to have a nice easy template to start from.  I've posted this mainly for my own benefit so I have a permanant bookmark to this code.

The example uses a stored procedure, but its important to understand that a stored procedure is not required to make this work.

Here's the code:

CREATE PROCEDURE [dbo].[usp_PageResults_NAI]

(
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @startRow int
    
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO

How many times has your database servers hard drives filled up and you want to find the culprit?  Probably not many but for me it happens all the time.  Here is a simple command to get a list of all sql server databases and their file size:

use master
go

exec sp_databases
go

Need to find out where the files are on disk?

SELECT [name] [Database], [filename] [File Path]
FROM master..sysdatabases

Here's a code tidbit to enable the service broker in SQL Server 2005.  This needs to be running for caching to work in ASP.NET.

IF (SELECT is_broker_enabled
  FROM sys.databases
  WHERE database_id = DB_ID()) = 0
    ALTER DATABASE yourdatabasenamehere
    SET ENABLE_BROKER
    WITH ROLLBACK IMMEDIATE

The if statement checks to see if the broker is already running.  If it is not then it attempts to enable it.  The WITH statement will rollback any other pending transactions that may be blocking this transactions.  This is important because if you run this statement without WITH ROLLBACK IMMEDIATE then it may seem that the batch will not complete.  Again, this is because other transactions or services like SQL Server Agent may be blocking.

Here's one thing that I constantly want to do.  I have a lookup table of some sort that needs to be moved to another database, but the data may need to be massaged somewhat before moving it around.  Plus I may want to store it in plain text format for versioning etc.

To this point I've been too lazy to implement it but I found a SQL guru (he must be if he went throught the trouble to write this) that has implemented just what I need.

I've used it this morning already and I'm quite happy.

Generate SQL statements from existing data in a table

I've found yet another blog and post(sheesh) regarding Query Notifications.  It's a survey asking some general questions.  If you have some complaints or comments head over there and let MS know what you want to see.  I'm still formulating my customer solution.

Observation:  Microsoft has a habit of assuming their solution should be satisfactory and doesn't really give an example of what to do if it isn't.  The user is left to try to piece together a solution that actually works in the real world.  So many of MS's examples assume that you are a drag-and-dropping cheeseball that uses SELECT * for queries and the SQLDataAdapterConnectorInterpreterManagerGateway for all things data. 

What about us folks who actually want to write code.  If I see one more MSDN artcile trumpeting the fact that you don't need to write one line of code, I'm going to puke.  Yeah instead of using a first class IDE supported language to write code I want to put some mish mash of bastardized markup into my ASP.NET page.  That's a great solution.  Try debugging that pile of angle brackets when something doesn't work.  Code is good.  I like writing code.  The IDE supports writing code. 

MS for god's sake let me write code!

In a follow up to my original article on Query Notification in SQL Server 2005, I've posted a question on the MSDN forums about Customer Query Notifications.

I'll keep you posted on whatever solution I come up with.

 

Seriously, SqlCacheDependancy looked like one of the best new features of ASP.NET 2.0.  Unfortunately, this feature is crippled because of the limited queries you can use.  See this MSDN article for more on that.  Basically you can't use TOP, ORDER BY, COUNT(*) and many other standard SQL clauses.  This is a serious limitation.

I am across this post about one person's attempt to get this to work which led to this post on the rules and some criticisms of the Query Notification mechanism.  Here is the official MSDN article on creating query notifications. 

So I wonder if this mechanism is too complicated for real-world use.  It certainly seems nice on first review but after trying to use it I can testify that it difficult to get this working correctly.  The query restrictions really limit your options. 

I'm not ready to give up because the potential up side to this is just too great.  One alternative I'm considering is trying the overloaded SqlCacheDependancy constructor and trying to pass the database and the table.  For joins and sub-queries I may need to hook multiple SqlCacheDependancy objects together with the AggregateCacheDependency.  This seems like a big pain but I think it may be worth it because your site gets much faster once it is all cached up.

To Be Completed...

If you've ever wanted to BCP from inside a stored procedure here's how:

DECLARE @FileName varchar(50)
   ,@bcpCommand varchar(2000)
   ,@YearMonth char(7)

set @YearMonth = '2006-04'
SET @FileName = '\\server\share\folder\'+ @YearMonth +'.txt'

SET @bcpCommand = 'bcp "SELECT * FROM Northwind..tblSomeTable where SomeField like ''' + @YearMonth + '%''" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P password -c'

print @bcpCommand
Exec master..xp_cmdshell @bcpCommand

Notice that in this example the BCP is using an sql query to filter the exported records.  Also notice that I am using the same filter as the name of the exported file.

This is useful when you want to group records by date but your datetime column has a time value also.  This code converts your date to a varchar in a specified format.

--101 is "mm/dd/yyyy"

SELECT CONVERT(varchar,DateField,101)

-- 1 is "mm//dd/yy"

SELECT CONVERT(varchar,DateField,1)

-- 108 is "hh:mm:ss"

SELECT CONVERT(varchar,DateField,1)

-- An example query

Select convert(varchar,CallDateTime ,101), count(*)

FROM [DBName].[dbo].[TableName]

where

CallDateTime between '04/21/2006' and '05/21/2006'

group by convert(varchar,CallDateTime ,101)