Ranjan’s Weblog

Query to Insert new record in table with generating new id in key column
If no records exist then new id is 1 else new id is greater then 1 of maximum id.

Example :

Inserting new product in product_master : -

Note : Assuming that productid is of type int.

Insert into product_master (productid,product_name,created_date)
select (select isnull(max(productid)+1,1) from product_master),’CPU’,getdate()

Introducing Cache Dependencies:

As time passes, the data source may change in response to other actions. However, if your code uses caching, you may remain unaware of the changes and continue using out-of-date information from the cache. To help mitigate this problem, ASP.NET supports cache dependencies. Cache dependencies allow you to make a cached item dependent on another resource so that when that resource changes the cached item is removed automatically. ASP.NET includes three types of dependencies:

• Dependencies on other cache items.
• Dependencies on files or folders.
• Dependencies on a database query.

Introducing SQL Cache Notifications:

SQL cache dependencies are one of the most wonderful new features in ASP.NET 2.0, the ability to automatically invalidate a cached data object (such as a DataSet or Custom Data Type) when the related data is modified in the database. This feature is supported in both SQL Server 2005 and in SQL Server 2000, although the underlying plumbing is quite different.

Cache Notifications in SQL Server 2000:

Before you can use SQL Server cache invalidation, you need to enable notifications for the database. This task is performed with the aspnet_regsql.exe command-line utility, which is located in the c:\[WinDir]\Microsoft.NET\Framework\[Version] directory. To enable notifications, you need to use the -ed command-line switch. You also need to identify the server (use -E for a trusted connection and -S to choose a server other than the current computer) and the database (use -d). Here’s an example that enables notifications for the Northwind database on the current server:

aspnet_regsql -ed -E -d Northwind

After executing this command, a new table named SqlCacheTablesForChangeNotification is added to the database Northwind. The SqlCacheTablesForChangeNotification table has three columns: tableName, notificationCreated, and changeId. This table is used to track changes. Essentially, when a change takes place, a record is written into this table. The SQL Server polling queries this table. Also a set of stored procedures is added to the database as well. See the following table.

After this, need to enable notification support for each individual table. You can do this manually using the AspNet_SqlCacheRegisterTableStoredProcedure, to that, open your query analyzer and select your Database you’ve enabled for SQL Cache Notification for example Northwind database and write the following command:

exec AspNet_SqlCacheRegisterTableStoredProcedure ‘TableName’

Or you can use aspnet_regsql, using the -et parameter to enable a able for sql cache dependency notifications and the -t parameter to name the table. Here’s an example that enables notifications for the Employees table:

aspnet_regsql -et -E -d Northwind -t Products

Both options generates the notification trigger for the Products table as the following:

CREATE TRIGGER dbo.[Products_AspNet_SqlCacheNotification_Trigger] ON [Products]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N’Products’
ENDSo any record is inserted, deleted or updated in Products table will update ChangeId field in AspNet_SqlCacheTablesForChangeNotification table.

How Notificaions Works:

The AspNet_SqlCacheTablesForChangeNotification contains a single record for every table you’re monitoring. When you make a change in the table (such as inserting ,deleting or updating a record), the changeId column is incremented by 1 -see AspNet_SqlCacheUpdateChangeIdStoredProcedure procedure-. ASP.NET queries this table repeatedly and keeps track of the most recent changeId values for every table. When this value changes in a subsequent read, ASP.NET knows that the table has changed.

Displaying time in format hh:mm:ss by using Convert() function

Convert(varchar(8),InDateTime,108) as [In Time]

To display date in format : dd/mm/yyyy

convert(varchar(12),[field name],103)

To get records between two dates use following syntax in where condition.
Note : 1) Assuming that field type is varchar in database table.
2) From date & to date variables are in date format.

Convert(datetime,Convert(varchar(12),[field name])) between Convert(datetime,[from date variable] ) and Convert(datetime,[to date variable])

Using Cursor – Basic syntax

DECLARE [cursor name] CURSOR
FOR

[SELECT query ]

OPEN [cursor name]

[DECLARE variables]

FETCH NEXT FROM [cursor name] INTO [variable list]

WHILE(@@FETCH_STATUS -1)
BEGIN
IF(@@FETCH_STATUS -2)
BEGIN
[Insert/Update statements]
END
FETCH NEXT FROM [cursor name] TO[variable list]
END
CLOSE [cursor name]
DEALLOCATE [cursor name]