I really like storing Session State out-of-process in a SQL Server database. However, I use custom code that switches the connection string based on the current environment my code is running in (test, stage, production, etc.). It was a little tricky to get this setup properly. But I am pretty happy with the following solution.
namespace MyApplication.Utility
{
public class SessionConnStringResolver : System.Web.IPartitionResolver
{
public void Initialize() { }
// Return the correct ASP.NET Session DB for the current running environment
public String ResolvePartition(Object key)
{
// Custom code to get connection string based on current environment
return MyConfigClass.GetConfigSettings.SessionDBConnString;
}
}
}
If you are using LINQ to SQL you should also set Serialization Mode to Unidirectional on the Object Relational Designer as discussed in this link. This allows you to serialize/deserialize your LINQ classes when saving to the database.
For a better explanation of the Partition Resolver feature of SQL Server Session State see the following links. Above is just the steps I followed to set everything up.
The FreeTextTable function in SQL Server is very useful when you need to search a column for words, phrases, etc. FreeTextTable will return a table that you can join on. It also returns the Rank of the matches so you can order the most relevant matches at the top.
In the example below I am searching the Keywords column of the MyTable table using the @Keywords passed into the stored procedure. I am then ordering them by Rank so the highest matches show up first.
CREATE PROCEDURE [dbo].[FreeTextSample]
@Keywords [varchar](1000) = ''
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;
SELECT myTable.MyPrimaryKeyID,
myTable.Title,
myTable.Description
FROM MyTable myTable(nolock)
INNER JOIN FreeTextTable(dbo.MyTable, Keywords, @Keywords) AS searchTable
ON myTable.MyPrimaryKeyID = searchTable.[KEY]
ORDER By searchTable.[RANK] DESC
END
GO
One thing to note is that LINQ does not contain an equivalent keyword for FreeTextTable. So what I did was to create a new method on my DataContext that returns the results of the stored procedure. See my post here for further details on how to do this.
We saw the above message in SourceSafe after moving to SQL Server 2008 and then saving the scripts for our tables back to SourceSafe. It appears that SQL 2008 Management Studio uses a different text encoding than prior versions of Management Studio/Enterprise Manager.
To save your script using a specific text encoding. You can "Save as.." and then there is an arrow to the right of the Save button. Click this and select "Save with Encoding...". Then you can select the specific encoding you need. In my case I needed to change the encoding from "Western Eurpean (Windows) - Codepage 1252" to "Unicode - Codepage 1200".
This trigger will write to an archive table when a row is updated or deleted. Very useful when you want to know who did what and when.
USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trigTableName_Archive]
ON [dbo].[TableName]
FOR UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO TableName_ARCHIVE
(Column1,
Column2,
Column3)
SELECT
Column1,
Column2,
Column3
FROM deleted
END
I always seem to be looking this up. So here it is:
USE DatabaseName
GO
ALTER TABLE TableName
ADD CONSTRAINT ConstraintName
UNIQUE NONCLUSTERED (ColumnName)
GO