Implementing a Feedback form

If you want to implement a feedback form for your application or web application without relying on the user configuring mail accounts etc., the simplest solution may be a web service which the application will access to deliver the message.

We would want the user to enter name, email address, and the message. Minimal validation would require the user to enter non-blank values for each field using a RequiredFieldValidator.

Additionally, we can check the email address for formal constraints using regular expressions, but this is quite tricky. Checking the name field for validity seems impossible.

Additionally, the application should also provide its name and version.

Thus, our web service looks something like this:

[WebMethod]
 public string PostFeedback(string Application, string Version,
    string Name, string Email, string Message)

It’s up to you what logic you implement in the web service handler. I chose to check application and version, and send an email to the contact stored for the application.

The web service returns a message to the user notifying successful receipt of the feedback (or an error message) which the invoking application finally displays.

SMOscript: Scripting database objects using SMO

I created SMOscript a couple of months ago with the intention to make it a replacement for the SQL Server 2000 tool called scptxfr.

After installing SQL Server 2008 and AdventureWorks, I found that, besides some other shortcomings, SMOscript would not connect to an SQL Server 2008. Clearly time for an update!

The new version of SMOscript provides these improvements and new features:

The database dictionary is iterated faster by calling SetDefaultInitFields.

CREATE scripts now include child objects such as constraints and triggers as a result of SetDefaultInitFields.

SMOscript scripts all scriptable database objects as defined by SMO API. (The previous version only scripted tables, views, synonyms, stored procedures, and functions)

SMOscript now supports SQL Server 2008.

The latest version of SMOscript is available for download here.

Note: This is a command-line tool. You won’t see an icon under Start/Programs!

Upgrading SMO 2005 to SP3

The original SMO 2005 library does not support connections to MS SQL Server 2008. Trying to connect results in a exception with the message

This SQL Server version (10.0) not supported.

I had downloaded and installed the MS SQL Server 2005 Management Objects Collection only on my PC and wanted to upgrade to SP3, only to find that there is no separate download of SMO 2005 SP3.

So I downloaded SQL Server 2005 Express SP3 and started the installer. However there was no simple option to upgrade SMO (or XMO) only, and I did not want to install the database engine on this machine.

The solution I found was to leave the installer open, go to the installer’s temporary directory (in c:\ root, name consisting of 20 hex digits), and from the Setup subdirectory started SqlRun_Tools.msi.

This upgraded the SMO objects to SP3 level, and resolved the cause of the exception.

Writing to Event Log from ASP.Net

If you create an Event Log entry from an ASP.Net application using

System.Diagnostics. EventLog.WriteEntry()

with an unregistered value for the Source parameter, you may raise the exception

System.Security. SecurityException: Requested registry access is not allowed.

This can be solved by creating a registry Key with the Source name under

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application

manually or via an installer application, as described in MS KnowledgeBase article 329291. Another possibility is to create a .reg file and import it into the registry.

7zip has no Move command?

I just found that 7-zip, which is a great zipping tool with command line support, does not have a Move command. Never had, still hasn’t.

The semantics of Move should be: Add to archive, and Delete from filesystem if successfully added.

From this description it’s obvious that it’s close to impossible to achieve this functionality in a batch file.

Is there an online petition page for stuff like this?

Installing MS AdventureWorks 2008

If you download the SQL2008 AdventureWorks sample and run the .msi installer package, the installer checks whether Full-Text Search and FileStream support are enabled on the SQL Server instance you specified, but the installer will not install the database(s). The only thing it does is extract the msi to the specified locations. The comments on the Readme page indicate that this did not only happen to me.

Solution

Open a command prompt window in the 100 directory of the SQL Server program files:

cd C:\Program Files\Microsoft SQL Server\100

and run the CreateAdventureWorks.cmd with the parameters: instance name, script path, and data path

CreateAdventureWorks.cmd
    ".\instance2008"
    "%PROGRAMFILES%\Microsoft SQL Server\100\tools\samples\"
    "C:\path-to\databases\MSSQL10.SQL08\MSSQL\DATA\"

This batch file will execute the SQL files for the 6 databases being created:

"%_sourcepath%AdventureWorks OLTP\instawdb.sql" (940k)
"%_sourcepath%AdventureWorks Data Warehouse\instawdwdb.sql" (50k)
"%_sourcepath%AdventureWorks LT\instawltdb.sql"  (200k)
"%_sourcepath%AdventureWorks 2008 OLTP\instawdb.sql" (964k)
"%_sourcepath%AdventureWorks 2008 Data Warehouse\instawdwdb.sql" (56k)
"%_sourcepath%AdventureWorks 2008 LT\instawltdb.sql" (203k)

where %_sourcepath% is set to the value of the script path parameter you provided. Numbers in parens show approximate file size in kB.

The .sql files create all database objects and fill the tables with sample data using BULK INSERT.

After the batch file has completed, you will see 6 new databases in Management Studio:

• AdventureWorks
• AdventureWorksLT
• AdventureWorksDW
• AdventureWorks2008
• AdventureWorksLT2008
• AdventureWorksDW2008

Tools and Libraries for .Net Developers

I just came across this article on recommended tools for .Net developers based on this blog and this blog.

What does StackOverflow have to say? A lot!

An ASP.Net Developers Toolset

Most useful free .Net libraries

Open Source .Net Tools

Open Source .Net Reporting Tools

Enjoy the inspiration

Parsing SQL into XML

dbscript has a built-in SQL script parser so that users can upload SQL scripts and analyze and document their database schemas.

While the parser is good enough to both extract all the relevant information from a SQL script and also skip over unsupported commands or simply plain text, I felt that the current approach would not scale well if I wanted to support more commands or different SQL dialects.

I started to play with a couple of C# classes, re-using the original dbscript T-SQL tokenizer, and soon had a small class library to define terminals, rules, and a parser class that kept everything together.

Additionally, the parser result can be output as XML.

Some parts of the parsing process turned out to be rather tough, such as the Repeater (or Loop) construct (as I said, it was playing rather than planning ahead), but I guess I now have a good framework to start implementing a T-SQL parser.

A table definition taken out of MS AdventureWorks

CREATE TABLE [Production].[TransactionHistoryArchive](
 [TransactionID] [int] NOT NULL,
 [ProductID] [int] NOT NULL,
 [ReferenceOrderID] [int] NOT NULL,
 [ReferenceOrderLineID] [int] NOT NULL ,
 [TransactionDate] [datetime] NOT NULL ,
 [TransactionType] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
 [Quantity] [int] NOT NULL,
 [ActualCost] [money] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL ,
 CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY CLUSTERED
(
 [TransactionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

is parsed and translated into XML like this:

<TSQL><cmdCREATETABLE><CREATE>CREATE</CREATE><TABLE>TABLE</TABLE>
<id13a><id2s><id2>[Production]</id2><x2E>.</x2E>
<id1>[TransactionHistoryArchive]</id1></id2s></id13a>
<columnsr><column><colid><id1>[TransactionID]</id1></colid>
<datatype><id1s><id1>[int]</id1></id1s></datatype>
<attr><notnull><NOT>NOT</NOT><NULL>NULL</NULL></notnull><rep-end>,</rep-end></attr>
</column>
...
</columnsr><x29>)</x29>
<onfg><ON>ON</ON><filegroup><id1>[PRIMARY]</id1></filegroup></onfg>
</cmdCREATETABLE></TSQL>

See the whole generated XML file here.

A “GO” command is transformed into this XML document:

<TSQL><cmdGO><GO>GO</GO></cmdGO></TSQL>

This is a work-in-progress. The naming of the symbols is not final, and the parser code needs to be cleaned up and separated into parsing and TSQL functionality.