User Defined Functions of SQL with .NET CLR

Well, sometimes you might have thought if you had the power of .NET framework in your SQL query's, well here it is. You write your
1. Stored Procedure
2. User-Defined Function
3. Trigger
4. User-Defined Type
5. User-Defined Aggregate

all using C# or VB .net.

I'm going to blog here what i have done recently for my project. I required a webservice which will actually retreive some data using pattern matching.

For this i required to create a UDF that gives me easy regular exp checking using Regex in .NET,

It took me hardly 2 minutes, i opened Visual Studio, choose Visual C# and then Database and choose user defined function for SQL.


public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean RegExMatch(String failure_reason,String pattern)
{
Regex argstr = new Regex(pattern);
return argstr.Match(failure_reason).Success;
}
};

and thats it i compiled and deployed in SQL, went ahead and used the UDF as below

Select dbo.RegExMatch (..,..)

I am still completing this task but found this very useful thought this might com handy for you too sometime

CreateUser Wizard, adding roles to users created

With .NET 2.0 its all easy :)

Drag and drop a CreateUserWizard in your page and set the OnCreatedUser=CreateUserWizard1_CreatedUser

Add a Wizard Step as below

asp:WizardStep ID="AddRolesStep" Runat="server" AllowReturn="False" Title="Step 2: Assign User To Roles"
OnActivate="AssignUserToRoles_Activate" OnDeactivate="AssignUserToRoles_Deactivate"

with a Listbox or any control to display the roles

After that in code behind add

public void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
{
// Create an empty Profile for the newly created user
ProfileCommon p = (ProfileCommon)ProfileCommon.Create(CreateUserWizard1.UserName, true);
// Save the profile - must be done since we explicitly created this profile instance
p.Save();
}
// Activate event fires when the user hits "next" in the CreateUserWizard
public void AssignUserToRoles_Activate(object sender, EventArgs e)
{

// Databind list of roles in the role manager system to a listbox in the wizard
AvailableRoles.DataSource = Roles.GetAllRoles(); ;
AvailableRoles.DataBind();
}

// Deactivate event fires when user hits "next" in the CreateUserWizard
public void AssignUserToRoles_Deactivate(object sender, EventArgs e)
{

// Add user to all selected roles from the roles listbox
for (int i = 0; i < AvailableRoles.Items.Count; i++)
{
if (AvailableRoles.Items[i].Selected == true)
Roles.AddUserToRole(CreateUserWizard1.UserName, AvailableRoles.Items[i].Value);
}
}

Also dont forget to add in web.config file

< profile enabled="true" >
<properties>

<add name="Email" />
</properties>
</profile>

else you will wonder which namespace to add for ProfileCommon, compilation error will show, you are missing reference.

That's it, a Create User with associating to roles are ready!!!