Boolean Search to SQL CONTAINS

Today I rolled out Boolean Search feature for PostJobFree.
PostJobFree uses SQL Server backend, so it seems obvious to use Full-Text search that SQL Server have to process boolean search queries from users.
Unfortunately it's not as easy:
Full-Text search CONTAINS query is pretty strict, and crashes the whole SELECT statement if CONTAINS clause syntax is not correct. For example:

asp sql

query would crash SQL Server's SELECT command if the query would be passed into CONTAINS clause as-is. Correct syntax would be:

asp AND sql

So I need to write a parser that parses user's search input into boolean search query object hierarchy and then renders CONTAINS query for SQL Server from that hierarchy.
Here's how I designed it.
Search object hierarchy consist of the following parts:
1) Word. It consists only of letters, digits, dots, and dashes. Dashes can be only be inside the word, between letters/digits. Dash outside the word is considered as negation.
2) Phrase. Phrase can have any chars wrapped in quotes.
3) OrList. OrList consists from two or more elements, separated by "or". For example:

("sql server" or oracle or DB2)

4) AndList. AndList consists from two or more elements, separated by "and" or "and not" operators. For example:

sql and asp and not oracle

This query would be rendered into AndList that consists of 3 elements.
Both OrList and AndList could consist not only from atomic pieces (such as Word and Phrase), but also from OrList and AndList subelements.

Parsing logic:
1) Split input query by quote char ('"'). Basically even elements in this split list are phrases. Everything else does not contain phrases.
2) After phrases are extracted, partially processed list consists of strings and Phrase objects.
3) Next step is to extract parenthesis that are not wrapped by other parentheses. Consider example:

(one and (two or three)) or four

"one and (two or three)" would be extracted from original query, and then recursively processed again.
The recursion ends when there are no more parentheses.
4) When recursion is so deep, that there are no parentheses left in a subquery, then that subquery is split by "or" word (or "|" char).
5) Last major step is to split remaining subquery into AndList. The trick here would be to appropriately assign negation if subquery was preceded by negation orerator "not", "!", or leading dash ("-").

After query is fully parsed, it's time to get final CONTAINS query for SQL Server.
It can be accomplished by overriding ToString() method for Word, Phrase, OrList, and AndList:
1) Word just converting containing text to UpperCase.
2) Phrase's ToString() overload converts InnerText to UpperCase and wraps it by quotes.
3) AndList joins all elements into single string using "&" separator or "&!" separator if one of AndList's subqueries was negated.
4) OrList joins all elements into single string using "|" separator. Then it wraps the result by parenthesis.

ASP-NET-SQL-XML-Dennis-Gorelik-Resume

Dennis Gorelik E-mail: blogresume @ dennisgorelik.com

You describe your business problem – I deliver technical solution
Objective

Team Lead / Architect / Senior Software Developer for the position that requires deep technical knowledge as well as good understanding of underlying business processes.

Please ask me:
- How to deploy program changes without interruptions in web site work (which is 24/7 service).
- How to improve web site usability.
- How to use A/B testing in Google WebSite Optimizer.
- How to track web site performance and usage (Google Analytics, IIS Logs, custom logs, …).
- How to use Google AdSense and Google AdWords.
- How to integrate your solution with Google Checkout (payment system).
- How to add maps to your web site using Google Maps API.
- How ASP.NET Membership provider helps to keep users passwords reliably encrypted.
- Practical way to setup data exchange with other web sites using XML web services.
- How to quickly implement Full-Text search using SQL Server 2008.
- How to make Web Application and background Windows Service to communicate with each other using WCF.
- What physical tiers and logical layers I recommend to use for your application and why.
Background

Team Lead / Architect / Senior ASP.NET Developer. Over 10 years of experience in analysis, design and software development.
Full system life cycle experience: design, coding, testing, bug fixing, deployment, documentation and maintenance.
Preferences in Agile/RAD methodologies.
Technical skills

Languages: C# 4.0, VB.NET, T-SQL (DDL, DML), VB script, JavaScript, JQuery, HTML/DHTML, XML, ...

Technologies: ASP.NET 4.0, ADO.NET, WCF (Windows Communication Foundation), XML Web Services, Unit test, WPF …

Databases: MS SQL Server, Oracle, DB2 …
Development Tools: Google Search, Visual Studio.NET 2005 Team Edition, Microsoft SQL Server Management Studio, Nant, Toad VI, MS Visio, Google Docs, MS Office, Google Maps API.
Web Development: ASP.NET, XML Web Services, IIS, JavaScript, HTML, DHTML, CSS, ...
Reporting Tools: Microsoft Reporting Services, Crystal Reports.
Version and Source Control: TFS, Visual Source Safe, Subversion, VisualSVN, ...
Operating Systems: Windows 7, Windows Server 2008, MS Windows XP/2000/NT, Windows 95/98/Me, Windows 3.xx, MS DOS.
Projects history

May 2008 - Present. Team Lead, www.postjobfree.com, Jacksonville, FL

Tools and technologies: VS.NET 2010 (ASP.NET, WCF, AJAX, JQuery, Unit Tests, Web Tests, MembershipProvider …), C# 4.0, SQL Server 2008, VisualSVN, NDepend, Reflector, Google Analytics, AdWords, AdSense.

- Architected and implemented www.postjobfree.com.
- Integrated PostJobFree with external systems: job search aggregators, Google and search engines, Twitter, … (XML Services, API calls).
- Designed and lead implementation of search alerts, including immediate match delivery.
- Analyzed users’ feedback and prioritized development features.
- SEO (Search Engine Optimization):
- Implemented RESTful architecture: Made all content available through direct HTTP GET requests (direct URLs to every bit of information without need to clicking buttons (HTTP POST)).
- Exposed PostJobFree web content through Site Maps to Google and Yahoo search engines.
- Designed & implemented strategy to fight spam and duplicates.
- Designed database structure to accommodate business requirements.
September 2010 - December 2010. Senior ASP.NET developer, Lender Processing Services, Jacksonville, FL

http://www.lpsvcs.com - Tax Desktop application -- tax oriented component of Loan Processing System.
Tools & Technologies: ASP.NET 3.5, C#, VS.NET 2008, MS SQL Server 2008, T-SQL Stored Procedures, WCF web services, JavaScript, JQuery, TFS.
Accomplishments:
Extended business functionality (Loan details, Tax Lines).
Refactored spaghetti code into object oriented muti-tier/multi-layer solution with:
- DAO (Data Access Object -- client consuming WCF service)
- WCF service (Windows Communication Foundation)
- BLL (Business Logic Layer)
- DAL (Data Access Layer) that faces LINQ-to-SQL designer.
Eliminated the need of updating web references and minimized WCF client footprint in web.config, which improved code maintainability.
Identified SQL Connection leak and implemented solution that fixes it.

- September 2005 – May 2008. Senior ASP.NET & database developer, National Flood Services, Kalispell, MT
http://www.nfsmt.com/ - the company works in flood insurance business.
Conversion from Legacy system (AS-400/DB2/VB6/ASP/ASP.NET/MS SQL2000) to new ASP.NET 2005 / MS SQL 2005 application.

Tools & Technologies: ASP.NET 2.0, VS.NET 2005, C# 2.0, SSRS (SQL Server Reporting Services), Generics, Provider Model, User Controls, Master pages, GridView, Web parts, ADO.NET, Web Forms, XML Web Services, MS SQL Server 2000, Validation controls, JavaScript, Telerik controls, HTML/DHTML, CSS, VSS, Web Forms.

Achievements:

- Data Warehouse: Architected and developed system that imports data from legacy database (DB2) in form of flat files. Incoming data is parsed, scrubbed for errors, and loaded into final database in denormalized form for fast reports performance.

Data Correction Application: Designed and developed application that allows to review and fix incorrect data that was imported into Data Warehouse (Winforms, C#, Web Services, SQL Server 2005).

Insight Reports: Architected and developed web application for report requests – users can customize reports according to their requirements (ASP.NET 2.0, MS SQL 2005, SSRS (SQL Server Reporting Services), Web Services, JavaScript, HTML/DHTML, Telerik controls,
DB2, SQuirreL SQL 2.1).
- Developed custom Membership and Role providers to access legacy database (ASP.NET 2.0, Provider Model, MS SQL 2000, DB2).
- Developed custom localization provider (C# 2.0, Reflector. Namespaces: System.Reflection, System.Resources, System.Web.Compilation, System.IO).
Developed User Management system + Web site administration (MS SQL 2005, ASP.NET 2.0 Login controls).
- Developed several Flood Service providers (C# 2.0, HttpWebRequest).
- Developed UI for testing various Flood Service providers (ASP.NET, C# 2.0, GridView, ObjectDataSource, Session, ViewState).
- Helped others to solve technical problems.
June 2005 – August 2005. Senior ASP.NET developer, IBM Global Services, Indianapolis, IN

http://www.ibm.com/services/
This is fast paced project for Wells Fargo bank (http://www.wellsfargo.com/). The project was about making web portal for managing vendor evaluation scorecards.
Snapshots: http://www.dennisgorelik.com/resume/IbmGlobalServices
Tools & Technologies: ASP.NET, C#.NET, ASP.NET User Controls, ADO.NET, Web Forms, MS SQL Server 2000, Validation controls, JavaScript, HTML/DHTML, CSS, VSS, RapTier codegenerator.

Achievements:
- Analyzed business requirement.
- Defined technical details of the solution.
- Developed scorecard template functionality edit/view/report (ASP.NET, C#, MS SQL, Stored Procedures, RapTier, CSS, HTML).
- Implemented making and handling web-page snapshorts in “Web archive, single file (*.mht)” format:
- Developed functionality for saving web pages snapshots into database (used CDO functionality).
- Developed functionality for viewing saved snapshots.
Saved images into database and showed images from database on web pages.
Implemented Scorecard View with dynamical load of heterogeneous user controls into the web page.
Developed several appendices for Scorecard functionality:
- Designed database tables.
- Created stored Procedures / Views / User defined functions
- Created middle-tier DB classes (C#).
- Created AppendixControl_Base.cs as a base for “Appendix” user controls. This base class was used by me and other developers in the team.
- Created user controls (*.ascx / C#).
Tested, fixed, and modified application according to changes in user requirements.
December 2004 – June 2005. Senior ASP.NET developer, Cendant Mobility, Danbury, CT

http://www.cendantmobility.com/

Tools & Technologies: ASP.NET, C#.NET, ASP.NET User Controls, ADO.NET, Web Forms, Win Forms, MS SQL Server, Remoting, Validation controls, JavaScript, HTML/DHTML, CSS, Nant, VSS.
Analyzed business requirements.
- Developed Template Request module (ASP.NET, C#, JavaScript, ADO.NET) and integrated it with existing application https://atlas.cendantmobility.com/.
- Template finder.
- Multiple container pages.
- Various template list screens with "multiple rows add" and "multiple rows remove" functionality (ASP.NET, JavaScript). (“Template associations”, “Template list”).
- “Update” screens (document template update, package template update).
- Developed ASP.NET User controls, including:
- Template list ASP user control - with Middle tier call, page navigation (paging), and sorting all columns.
- Navigation ASP user control.
- Created unit tests (WinForms).
- Used the following techniques:
- Nested pages (iframe), including nesting iFrames.
- Popup pages (Template finder, Product selection)
- Style sheets (*.css)
- Wrote stored procedures.
- Set up Nant --- tool for automatic build generation (for daily build).
- Helped other developers to solve technical problems.

August 2004 – December 2004. Senior ASP.NET developer, MPS Group, Jacksonville, FL
http://www.mpsgroup.com/
“Knowledge Spring” web portal project development.
Developed portlets(ASP.NET user controls) for multi-tier web system.
Tools & Technologies: VS.NET, ASP.NET, User Controls, Portlets, Repeater, DataList, Validator Controls, JavaScript, C#.NET, VB.NET, WebForms, WinForms, Oracle 9.2, LLBLGen Pr- Engine, Toad VI, SQL Plus, VB Script, PL/SQL script, ADO.NET, XML Web Services, MS VSS 6.0d, ComponentArt TreeView.

Projects:
- Directory Browser (recursive browsing through database items + Properties PopUp)
- MessageSet Editor & Message Editor (used repeater and Cute Editor control)
- Role Editor, UserSelectionControl, Actions Editor PopUp
- Attribute Editor, Portlet type editor, User Control editor, Item Types editor, DomainMappings editor, Audiences editor, Actions editor (repeater, JavaScript including passing data between main and popup windows, Web Validation Controls)
- SubstitutionVariables editor (used RegEx expressions)
- FileReportViewer (including NTFS permissions check through Advapi32.dll and Kernel32.dll API)
- Developed Windows services (C#).
- Developed win forms test app.
- Site Directory View 3-layers tree (ComponentArt TreeView).
- Configurations loading (VB Script, PL/SQL script, *.lst files).
May 2004 – July 2004. .NET architect, Yellow Pepper, Boston, MA

http://www.yellowpepper.com/
Tools & Technologies: VS.NET, ASP.NET, C#.NET, VB.NET, WebForms, WinForms, MS SQL 2000, ADO.NET, XML Web Services, MS Visual Source Safe, Groove.

Projects:
- Shazam web service wrapper.
- SMS Tracker – tracks information about SMS messages delivery.
- SMS Games – games based on sending/receiving SMS text messages to/from cell phone.
- Phone number look up – look up information about specified phone number (Carrier, Gate, IsWireless, City, State, Country, Switch).
August 2003 – April 2004. Senior .Net Software developer, Wurzburg, Memphis, TN

Tools & Technologies: ASP.NET, C#.NET, WebForms, WinForms, MS SQL 2000, ADO.NET, Microsoft Application Block, Remoting, Crystal Reports, XML Web Services, User controls, Server Controls, MS Visual Source Safe, MS Project, MS Visio.

Projects:
Development of various functionalities for warehouse project.

Web-site security
Designed and developed security framework for the web application: User authentication and authorization, Cross-applications calls.

UPS address validation project
Designed and developed mail address validation program. The program interface is exposed through web-page interface (and .csv file) and through XML Web Service (http://www.wurzburg.com/UPS/UpsValidation.asmx).
Technologies: ASP.NET, IIS, C#, XML, XML Web Services.
The program prepares XML-request and send it to UPS.

The program is based on UPS EDI functionality exposed here:
https://www.ups.com/ups.app/xml/AV

Reports project
Developed various business reports. Reports are provided in PDF-format, HTML-format, and can be printed directly to the specified printer. Reports functionality is exposed as to end users (through web-pages) as to other developers (in form of XML web services).
Technologies: IIS, ASP.NET, C#, Crystal Reports, ADO.NET, MS SQL Server 2000 (stored procedures, views, functions), XML Web Services, IE 6.

Catalogue project
Designed catalogue module, including ASP.NET and MS SQL database design. Developed shopping card functionality.

Label printing project
Designed and developed program to print bar-code labels. Program is tightly integrated with the company’s database. For instance, user which prints a label is associated with it’s own label printer.
Technologies: IIS, ASP.NET, C#, Crystal Reports, ADO.NET, MS SQL Server 2000 (stored procedures, views, functions), IE 6.

Deployment
Worked out fast and reliable deployment method. This method allows to deploy development changes to Production every day. Automated deployment SQL-script preparation.
In case of emergency deployment can be made even more often (in extreme cases up to 4 times per day).
Technologies: VS.NET, Database project, VSS, ASP.NET.
Performance
After initial prototyping and user evaluation I improved performance of "bottle-neck" applications. Modified SQL-queries, used MS SQL Query analyzer and SQL Profiler.

Other responsibilities
Was responsible for managing project documentation and for Visual Source Safe administration.

April 2003 – August 2003. Team leader. Global Consulting Group, Cincinnati, OH

Tools & Technologies: Visual Studio .NET, VB.NET, C#.NET, WebForms, WinForms, ADO.NET, MS SQL 2000, MS Visual Source Safe, MS Project, MS Visio.

Responsibilities: Lead team of 3 developers.
Achievements:

- Analyzed business requirements.
- Designed application architecture.
- Wrote technical specification.
Coached team members.
- Implemented logical and physical database design.
- Created SQL-queries (T-SQL).
- Developed web UI forms (ASP.NET, WebForms).
- Implemented data exchange (ADO.NET).
- Created business components (C#.NET).
- Created reports (Crystal Reports 9.0).
- Created XML Web services.
- Created XSL transformations.
May 2002 - April 2003. Application Developer, Scala

http://www.scala.net/
Tools & Technologies:

Visual Studio.NET, VB.NET, ASP.NET, WinForm, WebForms, Visual Basic 6.0, MS XML Parser, SOAP, Crystal Reports, MS SQL Server, Oracle, VC++, VB Script, Syncr- VSS, Source of Site and Visual Source Safe for version control.
MS Project for time tracking.
Benjamin (http://benjamin.scala.se/) - for tracking bugs and features.
MS Office, Rational Rose diagrams – for documentation purposes.

Responsibilities: Migration of legacy VB6 application into new .NET application. Reports development, XML services development for ERP (Enterprise Resource Planning) system. Retail sales, Warehousing, and Accounting modules development.

Achievements:
- Converted existing VB6 application into VB.NET application.
- Developed WinForms (VB.NET).
- Developed WebForms (ASP.NET).
- Implemented business logic (VB.NET).
- Created and modified SQL-queries.
- Developed several XML services for iScala (see: http://www.scala.net/scalasap/scalasap.asp):
- Created XSD, XDR schemas and XML samples. (Used XML Spy http://www.xmlspy.com/).
- Created XSLT transformations. Used Xselerator http://www.vbxml.com/xselerator/.
- Created business reports (Crystal Reports).
- Created and maintained tools for development process:
- Universal AutoTest program for testing iScala XML-services.
- Wrote documentation for AutoTest program.
- XML validator – allows quick checking is XML document valid or not.
- XML service registrator – copies all necessary components of specified XML service on tester’s computer and registers them under COM+ server.
- Created and modified type libraries (*.idl, *.tlb).
- Designed Work Flow diagrams for iScala.
October 2001 - April 2002: System analyst/Software developer, Egar Technology Corporation

http://www.egartech.com/
Tools & Technologies: MS SQL 2000, VB 6.0, Data Environment, ADO, Far Point Spread 2.5 (data grid), Crystal Reports 8.0, VSS 6.0, Visual InterDev 6.0, ASP, Rational ClearQuest and MS Outlook.

Responsibilities:
Development of the Collateral module for financial system. Data Modeling. ER-diagrams (ERwin).
Full Application consists of more than 300 database tables, 200 views, 500 stored procedures, 1000 VB-forms and modules

Achievements:
- Developed Back End of the Collateral module (tables, constraints, views and stored procedures).
- Developed VB user interface (Designed client forms and wrote VB-code).
- Created various financial reports (Crystal Reports 8.0).
- Developed Web interface (ASP).
- Wrote SQL-script for database deployment on customer computer.
- Used Rational ClearQuest to track defects and change requests.
- Used Visual InterDev for DB-scripts (SP and view) handle and version tracking and control via VSS.
November 2000 – September 2001: Software developer, Granat International Inc., Cleveland, OH

http://www.granatinternational.com/
Tools & Technologies:
VB 6.0, MTS, Visual Source Safe 6.0, InterDev, IIS, ADO, MS SQL 7.0 (MS SQL Query Analyzer, MS SQL Enterprise manager), Microsoft Transaction Server 2.0, Crystal Reports 8.0, VB-script (MS-Word 2000), Windows 2000.

Project: Loan Inventory Control System. Migration from Clipper to VB6.
Application consists of more than 200 forms and reports. It is 3-tier application.

Responsibilities:
The task was complete transferring the business logic from the existing file-server application (AS400, Clipper) to the Windows 3-tier architecture. Customer is the “Leader Mortgage” co., USA, OH (http://www.mortgagemag.com/guide/c008/c008630.htm).

Achievements:
- Investigated behavior of DOS-application and its source code (Clipper).
- Created DTS-package for conversion *.dbf into MS SQL database.
- Developed SQL-queries and business logic (COM+ components).
- Created reports (Crystal Reports 8) and forms for view and update database information.
- Developed VB-script (MS Word 2000), which helps to convert large fragments of clipper source code to VB code.
- Developed Web interface (ASP).
Certification

Microsoft

http://www.microsoft.com/learning/mcp/transcripts
Transcript ID: 663106
Access Code: dennisgorelik
- 070-320 exam (Developing XML Web Services and Server Components with Microsoft Visual C#.NET and the Microsoft .NET Framework).
Score: 981.
- 080-316 exam (Developing and Implementing Windows-based Applications with Microsoft Visual C#.NET and Microsoft Visual Studio.NET).
Score: 917.
- 080-315 exam (Developing and Implementing Web Applications with Microsoft Visual C#.NET and Microsoft Visual Studio.NET).
Score: 916.
Brainbench

- 2004 Brainbench certification ASP.NET.
- 2004 Brainbench certification C#.NET.
- 2004 Brainbench certification VB.NET.
http://www.dennisgorelik.com/resume/BrainbenchNetCertification.htm
- 2000 Brainbench certification in MS SQL 7.0.
- 2000 Brainbench certification in VB 6.0.
- 2000 Brainbench certification in Active Server Pages (ASP).
- 2000 Brainbench certification in MS Visual Interdev 6.0.
Education

1997 - 2000 MS degree in Economic and Management. South-Ural State University, Chelyabinsk, Russia.
1991 - 1997 MS degree in Automatic Control Systems. Chelyabinsk State Technical University, Chelyabinsk, Russia.
Personal

Excellent analytical, problem solving, technical, interpersonal and communication skills with a strong entrepreneurial drive.
References

Wurzburg

Keywords
ASP.NET, C#, SQL, MS SQL Server, Microsoft SQL Server, Visual Basic, VB, JavaScript, JQuery, Microsoft developer, Microsoft technologies, Visual Studio 6.0, Visual Studio.NET, VC++, XML, MS XML Parser, XML services, XML Web Services, SOAP, EDI (Electronic Data Interchange), BizTalk, XSLT, XPath, XSD schemas, XDR schemas, OAGIS, Active Server Pages, ASPX, Visual InterDev, Visual Source Safe, VSS, VSS 6.0d, IIS, Internet Information Server, MS Office, Microsoft Office, Perl, Microsoft Access, Microsoft Word VBA, Microsoft Excel VBA, Outlook VBA, Windows 2000, Windows XP, Windows NT, Windows 98, Windows 95, Object Orienting Programming, OOP, Component Object Model, COM, COM+ Server, MTS Server, RDBMS, UML, Rational Rose, Rational ClearQuest, Crystal Reports 9.0, MSDN Library, AS400, XML Spy, ERP, Microsoft Office VBA, Microsoft Query Analyzer, Microsoft Enterprise Manager, Profiler, XSelerator, OAGIS, *.idl, *.tlb, mIDL, DLL, VB6, VB.NET, ASP.NET, User controls, Server Controls, Portlets, Repeater, DataList, DataGrid, Components, ActiveX controls, ADO, OLE DB, MS Project, MS Visio, Work Flow diagrams, The Far manager, The Bat, Grid, True DB grid, FarPoint Spread, MSN, ICQ, Email, VB script, Java script, HTML, DHTML, programmer, data modeling, ER-diagrams, Erwin, Database technologies, Database developer, System Analyst, DBA, stored procedures, SP, Views, Triggers, MS IE, eCommerce, Microsoft Internet Explorer, MCAD, NTFS permissions check, Oracle 9.2, Toad VI, SQL Plus, ComponentArt TreeView, LLBLGet, RapTier codegenerator, mht, CDO, WCF, WPF.

Willing to relocate to: Tampa, FL; Saint Petersburg, FL; Orlando, FL; Jacksonville, FL; Miami, FL; Atlanta, GA; Dallas, TX; Houston, TX; Austin, TX; San Antonio, TX; Raleigh, NC; Memphis, TN; Chattanooga, TN; Nashville, TN, Phoenix, AZ; Kansas, KS; San Diego, CA; San Francisco, CA; Oakland, CA, Los Angeles, CA; Orange County, CA; SC; AL; LA; CO; AR; MS; NM; OK and some other warm places.

SQL_Latin1_General_Cp850_BIN

While working on Auto-Moderator for PostJobFree.com I encountered the following problem: my C# code considered ‘3’ and ‘３’ as different words, but SQL Server considered them the same.
That expressed itself in the following error:

Cannot insert duplicate key row in object 'dbo.Word' with unique index 'IX_Word'.

That was a little bit surprising, considering that I defined Word as Unicode column (nvarchar).

While searching for the solution, my first thought was to make 'IX_Word' index not unique. That worked, but would have introduced other problems with spam filtering business logic down the road.

The solution should have been about making SQL Server to compare strings exactly the same way C# code does.

I started to look into SQL Server Collations, and finally found the solution: use SQL_Latin1_General_Cp850_BIN collation.

Basically the solution is about to declaring 'Word' column with SQL_Latin1_General_Cp850_BIN collation:

Create Table Word(
 WordId bigint identity(1,1) not null,
 Word nvarchar(50) COLLATE SQL_Latin1_General_Cp850_BIN not null,
 JobPostCount int not null DEFAULT 0,
 JobLogSpamCount int not null DEFAULT 0,
 CreateDate datetime not null,
 UpdateDate datetime not null,
 CONSTRAINT PK_Word Primary Key Clustered
 (
  WordId ASC
 )
)
GO

Create Unique Index IX_Word ON Word
(
 Word
)
GO

Possible drawbacks of the solution: using SQL_Latin1_General_Cp850_BIN collation may cause weird sorting in SQL queries, but sorting collation can be easily redefined like this:

select * from Word
order by Word COLLATE SQL_Latin1_General_CP1_CI_AS

Moreover, the sorting it provided by binary collation (SQL_Latin1_General_Cp850_BIN) looks quite reasonable.

You may also use SQL_Latin1_General_Cp850_BIN2 collation for better sorting.


Here’s SQL sample to you to play with:

--drop table t;
select N'３' as Word
into t;

insert into t
select '3' as Word;

select * from t
where Word = N'3';

select * from t
where Word = N'３' 

select * from t
where Word = N'3' collate SQL_Latin1_General_Cp850_BIN;

select * from t
where Word = N'３' collate SQL_Latin1_General_Cp850_BIN

Enjoy!

Troubleshooting SqlCacheDependency in SQL Server 2008 and SQL Server 2005

"Getting immediate notification from SQL Server when data changes" is a very attractive feature, but unfortunately it's not easy to implement.
(It took me full day to identify and fix all issues...).

SQL Server Query Notification framework is quite fragile and may not work for multiple reasons.
If you get error messages -- consider youself lucky. Sometimes there will be no error messages, but notifications simply would refuse to work.

There are two major steps in troubleshooting SqlCacheDependency notifications:
Step 1: Make SqlCacheDependency clean up ASP.NET Cache item.
Step 2: Prevent SqlCacheDependency from cleaning up ASP.NET Cache item when it's inapropriate.

Both steps are hard, but Step 1 is the hardest.

I strongly recommend iterative approach: implement the easiest possible solution first, and then make it more advanced one small step at a time. Test every little step.

Business context

In this example I use SqlCacheDependency in order to get list of blocked IP addresses on my web site PostJobFree.com.
From time to time I delete bad users and write their IP addresses into BlackListIpAddress table.

I can retrieve the list of recently blocked IP addresses like that:

CREATE Procedure spGetBlockedIpList
@cutDate datetime
AS

set nocount on
select IpAddress
from BlackListIpAddress with (nolock)
where (DecisionDate > @cutDate)
group by IpAddress
having count(1) > 1
GO

When anybody opens web page -- I check if current web page request came from that list of blocked IP addresses.
I created C# function that does that check:

public static bool IsBlackListed(string ipAddress)
{
bool cached;
if (GetBlockedIpAddresses(out cached).Contains(ipAddress)) return true;
return false;
}

Because I run IsBlackListed() on every page, I don't want to run spGetBlockedIpList without need.
So, I keep database results in ASP.NET Cache object and use SqlCacheDependency to clean up Cache object as soon as new IP address is blacklisted in BlackListIpAddress table.

Implementation

public static List GetBlockedIpAddresses(out bool cached)
{
HttpContext context = HttpContext.Current;
List blockedIPs = (List)context.Cache["BlockedIPAddresses"];
if (blockedIPs == null)
{
SqlCommand cmdDependency = new SqlCommand(@"select IpAddress from dbo.BlackListIpAddress where DecisionDate > @cutDate",
SqlUtilities.GetSqlConnection("PostJobFreeConnectionString"));
SqlUtilities.AddInputParameter(cmdDependency, "@cutDate", DateTime.UtcNow.AddMinutes(-1), SqlDbType.DateTime);
SqlCacheDependency dependency = new SqlCacheDependency(cmdDependency);
SqlUtilities.ExecuteNonQuery(cmdDependency, "PostJobFreeConnectionString");
blockedIPs = LoadBlockedIPsFromDatabase();
// Cache retrieved blockedIPs in ASP.NET Cache object:
context.Cache.Insert("BlockedIPAddresses", blockedIPs, dependency);
cached = false;
}
else
{
cached = true;
}
return blockedIPs;
}

Note, that almost always blockedIPs will be retrieved from ASP.NET Cache.
But if Cache["BlockedIPAddresses"] is empty -- I execute two SQL queries instead of one query.

I run simple query "select IpAddress from dbo.BlackListIpAddress where DecisionDate > @cutDate" in order to make SqlCacheDependency work.
I then run more complex query LoadBlockedIPsFromDatabase() (it runs spGetBlockedIpList) in order to get data I need.
spGetBlockedIpList is too complex to work with SqlCacheDependency.

Simple query is not smart enough to give me the data I need.

When you debug your own code -- dump more complex query and use only simple one.
Remember -- first step is to make SqlCacheDependency clean up ASP.NET Cache item.
If SqlCacheDependency cleans up your ASP.NET Cache -- you are about 70% done.

You may even start with even simpler SQL query. For example: "select IpAddress from dbo.BlackListIpAddress". You would polish it later.

Preparations

1) Make sure that when your Web Application start, you run SqlDependency.Start().
I do it this way:

public sealed class DenyIpAddressModule : IHttpModule
{
void IHttpModule.Init(HttpApplication application)
{
string connectionString = WebConfigurationManager.
ConnectionStrings["PostJobFreeConnectionString"].ConnectionString;
SqlDependency.Start(connectionString);
}
}

If you forget to do that, you would get "When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance." error message.

2) Enable Service Brocker on your database.
I do it like this in SQL Server Management Studio (SSMS):

use PostJobFree;
alter database PostJobFree set ENABLE_BROKER;

The trick here is to kill all existing connections prior to altering your database.
Use these SQL commands in SSMS:

sp_Who2
kill 52 -- or whatever SPID is

You may check your Service Broker setting like this:

use PostJobFree;
select is_broker_enabled from sys.databases where database_id=db_id()

1 - Enabled; 0 - Disabled (default).

3) Make sure that permissions in your database are not out of whack. SQL Server 2005 and SQL Server 2008 have unpleasant bug that [almost] silently kills Queue Notifications:
"You cannot run a statement or a module that includes the EXECUTE AS clause after you restore a database in SQL Server 2005" http://support.microsoft.com/kb/913423

I fixed it by running this command:

use PostJobFree
GO
sp_changedbowner [MyServerName\dennis]

You may check current database settings by this command:

sp_helpdb PostJobFree

Troubleshooting

1) If you are still unable to make SqlCacheDependency to invalidate your ASP.NET Cache, I recommend you great article "Using and Monitoring SQL 2005 Query Notification"
http://www.simple-talk.com/sql/sql-server-2005/using-and-monitoring-sql-2005-query-notification/
Sanchan explains how to use SQL Profiler to see what's going on with query notifications.
2) Using Profiler helped me to find the following errors in SQL Profiler:
- An exception occurred while enqueueing a message in the target queue. Error: 33009, State: 2. The database owner SID recorded in the master database differs from the database owner SID recorded in database 'PostJobFree'. You should correct this situation by resetting the owner of database 'PostJobFree' using the ALTER AUTHORIZATION statement.
- Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
That gave me an idea to run:

sp_changedbowner [MyServerName\dennis]

3) These couple of queries would let you take a look at what active Query Notification Subscriptions you currently have:

select * from sys.dm_qn_subscriptions
select * from sys.transmission_queue

4) I didn't need that step, but while troubleshooting I did it anyway.
Grant these permissions to the user account that runs your web application (it's usually either "aspnet" or "NT AUTHORITY\NETWORK SERVICE").

use PostJobFree
GRANT CREATE PROCEDURE TO [MyServerName\aspnet]
GRANT CREATE QUEUE TO [MyServerName\aspnet]
GRANT CREATE SERVICE TO [MyServerName\aspnet]
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [MyServerName\aspnet]
GRANT SELECT ON OBJECT::dbo.BlackListIpAddress TO [MyServerName\aspnet]
GRANT SELECT ON OBJECT::dbo.T TO [MyServerName\aspnet]
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [MyServerName\aspnet]
ALTER DATABASE PostJobFree SET TRUSTWORTHY ON

Clean up Cache only when needed

I assume that at this point you are done with the hardest part ("Make SqlCacheDependency object clean up Cache").
There is still some work ahead.
For example I noticed that my SqlCacheDependency code when I was playing with it -- always invalidated ASP.NET Cache. It didn't matter if I updated underlying BlackListIpAddress table or not.
By using trial & error approach I found that the problem was caused by using inapropriate version of SQL query.
I found that:
- "group by" doesn't work no matter what.
- "top 10" doesn't work.
- "with (nolock)" hint doesn't work.
- passing @cutDate parameter to the query _does_ work.

See documentation on SELECT statements that are supported by Query Notification: http://msdn.microsoft.com/en-us/library/ms181122(SQL.90).aspx

Other useful resources

1) More tips about SQL Server Query Notification:
http://rusanu.com/2006/06/17/the-mysterious-notification/

2) Troubleshooting Query Notifications
http://msdn.microsoft.com/en-us/library/ms177469.aspx

3) If you are lucky and expect everything to go smooth - use this articles:
SqlCacheDependency using ASP.NET 2.0 and SQL Server 2005
SQL 2005 and SQL2008 Enabling Notifications. SQL Chache Dependancy Part-I

Keywords:
ASP.NET 2.0, ASP.NET 3.5, C#

SqlBulkCopy in CLR SQL Stored Procedure

When I was trying to run SqlBulkCopy in CLR (C#) SQL Stored Procedure, I've got at exception:

System.InvalidOperationException: The requested operation is not available on the context connection.
System.InvalidOperationException:
at System.Data.SqlClient.SqlBulkCopy.CreateOrValidateConnection(String method)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)

It seems that it's impossible to run SqlBulkCopy within "context connection" ...

Here's the only tip I was able to find:

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-clr/9/SqlBulkInsert

> Does SqlBulkInsert not run in CLR? Why does not?

Nope. It (BulkInsert) uses a different provider library if I'm understanding
things correctly.

Not much ... I guess CLR Stored Procedures work fast enough already...

Batch Update doesn't work in CLR Stored Procedures [on context connection] either.

When I tried to execute this code:

SqlDataAdapter da = new SqlDataAdapter();
cmd.UpdatedRowSource = UpdateRowSource.None;
da.UpdateCommand = cmd;
da.UpdateBatchSize = 1000;
da.Update(table);

I got this exception:

"Batching updates is not supported on the context connection."

Note the grammar: "... updates IS not supported ..."

It seems that nobody really cares about batch functionality in CLR SP.


What do you think? Please, let me know.

Web site in production

Omar Al Zabir wrote great article about his experience dealing with web site production issues:
13 disasters for production web sites and their solutions

Web site in production

Omar Al Zabir has great article about his experience dealing with web site production issues:
13 disasters for production web sites and their solutions