SQL CLR Functions

We can create a function within SQL Server that depend on a SQL assembly which itself is compiled using any of the .Net framework Common Language Runtime (CLR) managed code.

Beginning with SQL Server 2005, we can write user-defined functions which are of scalar (which returns single value) and table-valued function types. However, in this blog post we are dealing with Scalar type CLR functions.

T-SQL has lot of inbuilt functions and features. However to custom our own complex logic we use any CLR managed code like C# or VB.Net etc., and incorporate it in SQL environment.

Here are the steps to create a Scalar CLR functions:

1. Create a project of type "Class Library" using Visual Studio.

2. Add your static methods in our case "Encrypt" and "Decrypt” methods to the class.

3. Specify "SqlFunction()" attribute to all the functions that can be accessed from SQL Server function/stored procedure.

4. Compile & build the application in "Release" mode to get the assembly (.dll)

5. Now go to SQL Server MS; select your database and create New Query and execute the following statements below to enable CLR:
sp_configure 'clr enabled', 1;
GO
reconfigure
GO
6. If you encounter any compatibility level errors then check to see you database compatibility level using
sp_dbcmptlevel

If it is set to 100 or above, execute the following statement to set it to 90
sp_dbcmptlevel 'SQLCLR', 90

7. Before adding the 'dll' to the SQL assemblies you need to set the database to trustworthy using the following statement
ALTER DATABASE SET TRUSTWORTHY ON

8. Now expand your database node to go to Assemblies located within Programmability and create new assembly.

9. Choose the assembly file path and set the permissions for assembly owner.
(Note: In case you get any errors check the steps #5, #6 and #7)

10. Later to access the external CLR functions from within assembly we need to create a function in SQL Server. External name should be like ...
Execute following queries to encrypt and decrypt functions:

CREATE FUNCTION [dbo].Encrypt(@Input nvarchar(max)) RETURNS nvarchar(max)
EXTERNAL NAME  EDCLR.EDCLR.Encrypt

Go

CREATE FUNCTION [dbo].Decrypt(@Input nvarchar(max)) RETURNS nvarchar(max)
EXTERNAL NAME EDCLR.EDCLR.Decrypt;
 

11. When everything is ready; use the following query to encrypt

Select dbo.Encrypt('Hello World')

and use function to decrypt the encrypted string

Select dbo.Decrypt('i9E2KOEoT7D+Doc2CBdjDA==')

This can be use to encrypt passwords, credit card details and other sensitive information within SQL. Visit MSDN to know further about CLR

Happy Coding :)

The Ten Commandments of Source Control

There are a number of great version control systems out there; the most important thing is to pick one and learn to use it effectively. No matter which source control system you decide to use, there are a number of universal principles that will help you to get the most out of source control.

1. Always use source control

There is no reason not to use source control. Even for a solo project. Even for a toy project. Even if you never make mistakes. Just use it. Source control offers so many benefits like rollbacks, code diffs, backups, and commit logs that every project will benefit from it.

2. Don't break the build

Don't commit code if it breaks the build. You don't want to force other people to sift through your code looking for the problem that broke the build before they can continue with their own work.

3. Commit early, commit often

Don't go dark. Committing early and often gives you a backup, an incremental log of progress, easier merges, and lets the team know what you are doing.

4. Remove unused code immediately

Don't commit code with large blocks of old code commented out or unused, deprecated functions. If you need to get this code back at some point, you can always get it from the repository. Keep the current working copy of the source as clean and cruft-free as possible.

5. Keep a good commit log

Leave good comments in the commit log. Searching the commit log should allow you to find which commits implemented a specific feature or fixed a specific bug. Skimming the commit log should provide a good history of the project and give some hints as to it's maturity.

6. White spaces

Don't commit white space, formatting, or code style changes at the same time as other changes. If you must make these types of changes to the code base, commit them separately. This will keep the diffs vastly more readable.

7. Minimal Changeset

When doing bug fixes, make the minimal set of changes needed to fix the bug. If a rewrite of a module is really in order, do the rewrite, but err on the side of making the absolute minimal set of changes needed to fix the bug. This way the change log will clearly reflect the exact source of the bug and the changes needed for the fix.

8. One bug fix per commit

Again, keep the change log clear by doing only one bug fix per commit (unless multiple bugs are very closely related). One bug fix per commit makes rollbacks easier and leaves a clear track of how the bug was fixed that will be valuable later if a regression test fails or if the fix created unintended side effects.

9. Notify the team of new commits

Other team members working on the code base should be immediately and automatically notified of any changes that have the potential to affect them. This could be setup in different ways depending on the project and the workflow used by the team. One of the easiest and most effective ways to set this up is an automated email including the commit message and the diff to all members of the team working on that particular module. Notifications keep all team members appraised of the current state of the code and give them a heads up if they are likely encounter problems merging their own code.

10. Integrate source control with other tools

Source control should be integrated with other tools used on the project like bug tracking, continuous integration, and IDEs. This makes it easy to cross-reference the commit that fixed a bug, created a bug, or broke the build. Integration with an IDE gives you the option to do source control operations like diff, status, and commit within the IDE without having to drop to the command line or a separate tool.
The above basic principles of version control will make you effective with any type of version control system out there. Have more source control tips? Leave them in the comments.
The terms 'version control', 'source control', and 'revision control' are used interchangeably here. Original source of the blog.

Converting a DataSet to XML node excluding Schema

In this blog post I'm going to explain you on how to return an object of type "DataSet" from a web service (SOAP) method.

 

In general when we return any type from a web method, it returns XML formatted string which then need to be parsed to get the resultant output. Whether it is a simple or any complex object type like Collections or generic list

However, when we returning a DataSet the resultant output will include table data along with the Schema.

So, if we need data with schema then it is well & good. 

What can we do to get only data and eliminate the schema from the DataSet?

The following code explains how we can achieve this:

DataSet ds = //... Use your logic to get DataSet from some DB/source

string xmlContent = ds.GetXml(); 

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();

doc.LoadXml(xmlContent);

System.Xml.XmlNode newNode = doc.DocumentElement;

return newNode;

GetXml method will return all the xml code in string variable for the corresponding DataSet (including schema).

Creating an XmlDocument object and load the Xml string using LoadXml method

Then we need to return DocumentElement of XmlDocument class to get only data excluding schema.

There is another technique to do the same with less effort, using Linq. Although, I never tried this to confirm..

//// When Using LINQ

//XElement newNode = XDocument.Parse(xmlContent).Root;

Try this and let me know if there are any other ways...

Validation Summary and AjaxControlToolkit's ModalPopupExtender

Hi Guys,
Recently I come across a situation where in I used a AjaxControlToolkit's ModalPopupExtender with in a aspx page and it was working fine. Until when I added a simple form with validation controls. Obviously, I had a validation summary control too.

The application threw a JavaScript error "Object Expected" and in a mean time I came to knew that there is a issue with "Validation Summary" control and AjaxControlToolkit's "ModalPopupExtender" control.

Check this link from asp.net forums for details.

In order to get this done we need to disable the validation summary control before opening the modal pop up. In some posts, I find a way of using "ValidationGroup" property of asp.net controls (but this don't solve my issue).

The only solution I got is from by favorite scripting language "JavaScript" by writing custom validation to it. Problem solved... :-)

If you have any other solution for this issue, please feel free to comment.

Hope this helps,
Sreekanth