Tips for MyGeneration

I am a big fan of code generation based on templates. It gives all the flexibility you are expecting when coding by hand, but still

• Increase your productivity
• Decrease maintenance efforts
• Code consistency across your project(s)
• etc...

I have been using MyGeneration for a while now and I recently found a good post from Michael Freidgeim summarizing most of the good links you need if you use it.

Replacing the configuration in Web Deployment Projects

I am using Microsoft Web Deployment Projects in order to deploy automatically my nightly build on the development and test servers.

It worked great but I struggled quite some time trying to replace the configuration section.

Well, it was as stupid as that you cannot replace the whole system.serviceModel because it's not a configuration section, but is a configuration section group...and guess what?

WDP can only replace configuration sections and not configuration section groups.

What you can do though is replacing a section within the section group.

Within system.serviceModel, you can replace the client configuration section for instance using the replacement string "system.serviceModel/client=system.serviceModel.client.dev.config;"

SQL Code Prettifier from Simple-Talk

Use the Simple-Talk Prettifer to format your code (SQL, C#, VB and Python)http://extras.sqlservercentral.com/prettifier/prettifier.aspx in your web pages.

Update (23-March-10): I have been recently using http://www.manoli.net/csharpformat/, which does a better job and allow xml formatting.

Calling a WCF Service

Based on WCF best practices, calling a WCF service should not be wrapped in a using statement as exceptions can be raised (http://msdn2.microsoft.com/en-us/library/aa355056.aspx). Below is a code snippet highlighting the best practice:

ServiceClient client = new ServiceClient();

try
{
client.MyOperation();
client.Close();
}
catch (CommunicationException e)
{
//Handle exception if necessary
client.Abort();
}
catch (TimeoutException e)
{
//Handle exception if necessary
client.Abort();
}
catch (Exception e)
{
//Handle exception if necessary
client.Abort();
throw;
}

However the code to write to do so is quite painful, hence I have written a VS code snippet (wcfproxycall.snippet) to minimize the pain.

To install the code snippet, simply copy the attached snippet to "My Documents\Visual Studio 2008\Code Snippets\Visual C#\My Code Snippets"

When you need to call a proxy, type wcfproxycall in your code and hit the Tab key to expand the code snippet.

• Fill the highlighted fields
• Use the tab key to navigate to the next field
• Hit Enter to complete when all fields are filled

Grant permissions to all stored procedures in a database

SQL Server 2005 improves on the current situation by making the EXECUTE permission grantable at the database scope.

What it means is that we can issue a statement like the example below and this will GRANT execute permissions on ALL existing stored procedures and scalar functions AND all subsequently created ones.

GRANT EXECUTE TO [myUser] AS [dbo]

If you only make use of stored procedures to access your database and have not implemented a complex security model, this is probably the easiest and cleanest way to manage access rights.

If users' Windows accounts are used to connect to the database, you will prefer creating a role like db_storedprocedures_executor and assigning it the EXECUTE permission. Then it is very much like the existing fixed database roles such as db_datareader.

Implementing a self-signed certificate

It is a must to be able to generate certificates in order to test various security scenarios like running HTTP over SSL, message signing and/or encryption.

The .Net Framework SDK is shipped with a command line tool makecrt.exe that can create a self-signed certificate. This certificate can then be used on a developer's workstation or testing server.

Note: This should not be used for Production purposes. You can buy certificates from various entities.

The following command can be used to create a self-signed SSL test certificate:

makecert -r -pe -n "CN=www.yourserver.com" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

To install this certificate in IIS (5.x, 6.0), open the IIS Management console:

• Right-click on your site (e.g. Default site) and select Properties
• Open the tab "Directory Security"
• Click the "Server Certificate..." button, pass the first screen of the wizard
• Choose "Assign an existing certificate"
• Select the newly generated certificate from the list and click Next until the end of the wizard

SSL is now enabled in IIS!

Note: Older versions of makecert.exe do not support the "-pe" option, which makes the private key exportable. If you have an old version of makecert.exe, you can omit the "-pe" option, but then the certificate cannot be exported including the private key.

The testing certificate above is also known as self certificate. Self certificates are not "trusted" by your computer or browser, which maintains a list of trusted authorities.

A certificate issued by a trusted authority is a trusted certificate.

When using a testing certificate, you will get security warning in your browser warning you that the certificate is not a trusted one. Moreover, if you force SSL on your VS.Net project you may not be able to open it if it uses a testing certificate.

The only way to get around these issues is to add your testing certificate to the list of trusted authorities.

• Open a command prompt and run the MS Management Console by typing mmc and enter
• Click File and Add/Remove Snap-in
• Click the Add button and choose Certificates
• Choose "Computer Account"
• Select the local computer
• Open the Personal \ Certificates
• Copy your testing certificate to Trusted Root Certification Authorities \ Certificates (Drag and Drop + Ctrl key)

UPDATE: IIS7 offers a new feature to create self-signed certificate easily. The only issue is that you cannot set all the certificate properties.

Self Signed Certificate in IIS6

You can use the IIS 6 Resources Kit to generate and install a self-signed certificate with the SelfSSL.exe command line tool.

The IIS 6 Resouces Kit is available on the Microsoft.com website: http://go.microsoft.com/fwlink/?LinkId=34407

Note that you should use a self-signed certificate when you need to troubleshoot third-party certificate problems or when you need to create a secure private channel between your server and a limited, known group of users, such as exists in a software test environment.

Follow this step in order to generate and install the self-signed certificate.

1. Create a virtual site (or use the one on which you want to install the certificate) and set up SSL (default port is 443)


2. Launch the SelfSSL tool (Start Menu All Programs IIS Resources SelfSSL SelfSSL Prompt)


3. Run the following from the prompt replacing the /N:CN with your DNS name and the /S parameter with the IIS site Id

SelfSSL /N:CN=dnsname.mydomain.org /V:365 /S:siteId /P:433

Note: If you create a SSL certificate for the main IIS site, you can omit the /S, else the site ID can be found from the IIS Manager console)

SelfSSL command help

Installs self-signed SSL certificate into IIS.
SELFSSL [/T] [/N:cn] [/K:key size] [/S:site id] [/P:port]

/T Adds the self-signed certificate to "Trusted Certificates" list.
The local browser will trust the self-signed certificate if this flag is specified.
/N:cn Specifies the common name of the certificate. The computer
name is used if not specified.
/K:key size Specifies the key length. Default is 1024.
/V:validity days Specifies the validity of the certificate. Default is 7 days.
/S:site id Specifies the id of the site. Default is 1 (Default Site).
/P:port Specifies the SSL port. Default is 443.
/Q Quiet mode. You will not be prompted when SSL settings are overwritten.

The default behaviour is equivalent with:
selfssl.exe /N:CN=MYSERVER /K:1024 /V:7 /S:1 /P:443

Resetting security after restoring a database backup

When you restore a database from one server to another (i.e. restoring from Production to QA or Development), the user permissions of SQL logins are not automatically reset even if a user with the same name exists (note that this does not apply if you use Windows authentication)

In order to reset the user permissions, you can use the system command sp_change_users_login.

When passing REPORT as a parameter, it will list all the orphan users.

sp_change_users_login REPORT
GO

You can then re-assign the orphan users to existing users by using the following command:

sp_change_users_login UPDATE_ONE, 'ORPHAN_USER', 'LOGIN'

Example:

sp_change_users_login UPDATE_ONE, 'production_account', 'qa_account'
GO

Resetting SQL identity columns

If you're somehow perfectionnist as I am, you probably want to reset the your identity columns when deleting all records from a table.

With SQL Server, you can do so by using the DBCC CHECKIDENT command.

DBCC CHECKIDENT ('MyTable', RESEED, 0)

Now if you want to reset the identity of a table that still contains some rows, you must reseed it to the last number used.

DBCC CHECKIDENT ('MyTable', RESEED, 123)

Edit Lotus Notes document without an agent

If you manage Lotus Notes application, there is always a time when you will need to edit a document to change a value. That say, you are not always able to do so even with full administrator rights (the application may not allow you to do it via the user interface)

One solution is to create a simple agent that will do the work for you but it ends being painful!

Another solution is to use the very useful tool "Edit Document Fields"

http://www.chadsmiley.com/chadsmiley/home.nsf/htdocs/Edit_Document_Fields_History

The tool is setup by adding a new button to your Notes toolbar and sets its formula to the code given. You're now all set and can edit any field in any document!

Note that there are version for R4.x, R5, R6.x and R7