Geeks With Blogs
Robin Hames Hints, tricks and tips relating to MS SQL Server and .NET
This post adds SQL Cache Dependency support to the loosely coupled .NET Cache Provider that I described in the previous post (http://geekswithblogs.net/... The sample code is available on github at https://github.com/RobinHam... Each time we ......

I have recently been reading the excellent book “Dependency Injection in .NET”, written by Mark Seemann, which I strongly recommend. Reading the ideas around Dependency Injection made me realise that the Cache Provider code I wrote about earlier (see http://geekswithblogs.net/R... ......

ASP.NET MVC3 provides out of the box support for binding a Json data object into a Model on postback. This maybe used with a JQuery Ajax function call to post selected data back to an action. It is also possible to return a partial view from the same action, and refresh this from the ajax success function. For example: In my MVC3 Razor project, I create ......

Adding Cache Dependency Support This second part of my article on adding cache support to applications will extend the sample application developed in part one to add support for cache dependencies such as the SqlCacheDependency. Part One of this article is at: http://geekswithblogs.net/R... ......

ASP.NET applications can make use of the System.Web.Caching.Cache object to cache data and prevent repeated expensive calls to a database or other store. However, ideally an application should make use of caching at the point where data is retrieved from the database, which typically is inside a Business Objects or Model layer. One of the key features ......

This user defined function is based on a function posted at this very useful site: http://www.sql-server-helpe... I have modified it slightly to handle spaces within the input string; by first replacing any existing spaces with ‘¬’ (this seems to be a suitably obscure character that is never likely to occur in the ......

I pretty new to LINQ, and I’m keen to get more experience using it, so whenever an opportunity arises I like to try writing LINQ queries. I needed to write a method to extract a comma separated list of numbers from a config file, and return this as List<int>. I was looking at ways to do this using LINQ, but hit a problem. I wanted my LINQ query ......

Just some code to get the time part only from a DateTime field (is SQL Server 2000 or 2005), with the date set to a reference date declare @timetest datetime declare @refdate datetime set @timetest = getdate() set @refdate = '30 Dec 1899 00:00:00' select @timetest , @refdate , dateadd(day, datediff(day, @refdate , @timetest), @refdate ) -- date part ......

This script generates the “CREATE INDEX” scripts for a SQL Server database: set nocount on declare @index table ( object_id int, objectName sysname, index_id int, indexName sysname, fill_factor tinyint, allow_row_locks bit, allow_page_locks bit, is_padded bit, indexText varchar(max), indexTextEnd varchar(max) ) declare @indexColumn table ( object_id ......

I am always forgetting the format for using DATEADD and DATEPART functions to get just part of a SQL Server datetime (e.g. just the date), so I am posting it here so I’ll always know where to find it. declare @date datetime set @date = getdate() select @date, dateadd(day, datediff(day, 0, @date), 0) ......

SQL Server CHECKSUM() with Single Quotes (‘) gives duplicate values. A database I have been working on uses a checksum across address data to try to establish whether an entered address already exists in the database. A problem occurred because two addresses had the same checksum value, but the actual address was subtlety different. The difference was ......

How not specifying the ADO.NET SqlParameter DbType can lead to a horrible query execution plan! I was looking into a query that was performing much worse than expected. The query was a simple select of the form: select e.EmployeeID, count(r.EmployeeRoleID) from Employee e left join EmployeeRole r on e.EmployeeID = r.EmployeeID where e.EmployeeRef = ......

Creating Test Wildcards with a Common Table Expression in SQL Server 2005 I needed to create a SQL Server table of test wildcard strings ranging from ‘AA%’ through to ‘ZZ%’. This seemed to be a prime candidate for using recursion with a Common Table Expression (CTE). After a little playing around I came up with the following: with wildcards as (select ......

Using “Update to a local variable” to calculate running totals in SQL. Recently I was looking at an existing view on a client's SQL server 2005 database. This view calculated the running total for a transaction amount from a table, but was performing very poorly. I had always believed there were three different methods for calculating a running total ......

Copyright © Rhames | Powered by: GeeksWithBlogs.net | Join free