Posted on February 11th, 2009%
I’ve been doing a lot of work with NHibernate lately, particularly with named queries. It took a while to get it just right, so I figured it would be helpful to others (and to myself in the future) to note some of the gotchas and how-to steps to get it just right.
What is a named query?
An NHibernate named query is essentially a native SQL statement that can be invoked by NHibernate and can return strongly-typed object data. It allows you to leverage native SQL code — parametrized statements or stored procedures — to perform complex data manipulation and retrieval.
In other words, let’s say you’re writing a baseball game, and you have three objects in your domain model: a Player, a Team, and a DrugTestResult model. You write a stored procedure, spSelectPlayersByLastDrugTestDate, which returns all players who haven’t had a drug test since a given date.
Now that you have a domain model and a SQL statement, how do you execute . . .
→ Read More: Executing native SQL using NHibernate named queries
Posted on September 11th, 2008%
A few weeks ago, I wrote a post about deleting all tables and constraints in a database. It’s time to take that one step further!
The following SQL code will delete all stored procedures, user-defined functions (UDFs), views, table constraints, and tables (in that order) from a given SQL database. As with the original, you can specify an object name previx by changing the set @tblname = '' as needed.
This is helpful in two situations:
- When you want to delete all objects in a database without dropping/recreating it, in which case you’d specify
'%' as the @tblname.
- When you want to delete all objects in a database whose name starts with a certain sequence of characters. For example, if you want to delete all the ASP.Net membership objects in a database, you can specify
'%aspnet_%' as the @tblname, since its SQL objects start with aspnet_ or vw_aspnet_.
Note that you should be very careful any time you use scripts like this, as data loss can happen where you least expect it. I use this script for clearing out development databases, but would never use it for production databases.
Anyway, on to the script.
. . . → Read More: Scripting the deletion of objects in a SQL database (second version)
Posted on August 7th, 2008%
If you use the ASP.Net membership tools, have <anonymousIdentification enabled=”true” /> specified in your Web.config, and get lots of anonymous visitors, it’s only a matter of time before your database grows. What’s filling it up is the countless user records for your anonymous users.
If you don’t need to track user and profile information for an anonymous user once they leave the site, you can delete the unneeded data by running a SQL script. The following script will delete from your membership tables all anonymous users whose last activity was more than 7 days ago.
delete from aspnet_profile
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) > 7
)
delete from aspnet_usersinroles
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) > 7
)
delete from aspnet_membership
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) > 7
)
delete from aspnet_personalizationperuser
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) . . .
→ Read More: Remove anonymous users from ASP.Net Membership tables
Posted on August 6th, 2008%
We all need to do it from time to time — erase a bunch of tables in a SQL database. Sure, you can do this through Enterprise Manager, one table at a time, or write a bunch of DROP TABLE statements, but when doing it this way, you also need to ensure all foreign key constraints (i.e. table dependencies) are dropped first.
There’s an easier way — the following script, which was derived from a few posts scattered around the Internet. (Sorry, it’s been a while, and I don’t remember what they were!)
. . . → Read More: Deleting all tables and constraints in a database
Posted on June 12th, 2008%
I just stumbled across the following SQL 2005 code:
BEGIN TRY
UPDATE [tabProgramSite]
SET
[Name] = @ProgramSiteName
WHERE [ProgramSiteID] = @ProgramSiteId
END TRY
BEGIN CATCH
END CATCH
Nothing like catching an error… and doing nothing about it. I hope the user experience isn’t . . .
→ Read More: Bad Programming: How not to use try/catch blocks
|
|
Recent Comments