Monday, March 31, 2014

How to create and deploy a CLR in SQL Server 2012 using Visual Studio 2012

The following steps will guide you to create CLR, also this cover the errors that appearing while this process and the solutions.
Create a Visual Studio project using SQL Server templates

Add item – under SQL SLR C#. By default the relevant codes will be there as shown below.


In this example, I am going to fire email when a customer does a transaction above 100000. First I created a table with the columns as shown below, the table name is “TransSample”.


To run a CLR in SQL Server you must enable “clr enabled” in server level.


Change the project Connection properties for the target database can be changes via project properties as shown below.


First rename default name in the code for Name, Target and Event.
Name=CLR Name
Target= Target table name
Event = INSERT/UPDATE/...
Function name as CLR name



When you try to deploy it, you might get error an error as:
SQL71501: Trigger: [dbo].[MyCLRTrigger] has an unresolved reference to object [dbo].[TransSample]

This problem is due to project does not have proper metadata about the Target Database object for that import the target database under project to do it, follow the following steps:
Close the project and locate to the project folder and delete the *.dbmdl file, then open the project aging and import the database by right click on project and under import as shown below screen dump. Once you have done this you would be able to deploy the CLR successfully.



Now you can deploy the project as shown below.


Once you deploy, you would be able to see the CLR under the database as show below.


Now will see how to write the logic under this CLR to send an email for more than 100000 transaction.


Code
*********************************************************************************
using System;using System.Data;using System.Data.SqlClient;using Microsoft.SqlServer.Server;using System.Net;using System.Net.Mail;public partial class Triggers
{        
   
// Enter existing table or view for the target and uncomment the attribute line
   [Microsoft.SqlServer.Server.SqlTrigger(Name =
"MyCLRTrigger", Target = "TransSample", Event = "FOR INSERT")]
   
public static void MyCLRTrigger()
   {
       SqlTriggerContext context = SqlContext.TriggerContext;
      
       
string customer = "";
       
double amount = 0;

       
if(context.TriggerAction ==TriggerAction.Insert)
       {
           
using (SqlConnection con = new SqlConnection("context connection=true"))
           {
               con.Open();
               SqlCommand com =
new SqlCommand();
               com.Connection = con;
               
//getting customer name
               com.CommandText =
"select CustomerName from inserted";
               customer = com.ExecuteScalar().ToString();
               
//getting Amount
               com.CommandText =
"select Amount from inserted";
               amount = Convert.ToDouble(com.ExecuteScalar());

               
//apply condition
               
if (amount >= 100000)
               {
                   SendMail(customer, amount);
               }

           }
       }

   }

   
private static void SendMail(string CustomerName, double TrnAmt)
   {
       MailMessage msg =
new MailMessage("umashanthan@gmail.com", "umashanthan@gmail.com");
       SmtpClient cl =
new SmtpClient("smtp.gmail.com", 587);
       cl.DeliveryMethod = SmtpDeliveryMethod.Network;
       cl.EnableSsl =
true;
       cl.UseDefaultCredentials =
false;
       cl.Credentials =
new NetworkCredential("umashanthan@gmail.com", "xxxxxxx");
       msg.Subject =
"Credit Card Transaction Alert";
       msg.Body =
"Alert!" + Environment.NewLine + Environment.NewLine +
           
"The customer " + CustomerName + " made a transaction of " + TrnAmt.ToString();
       cl.Send(msg);
   }

}



*********************************************************************************


After deploy this CLR, You might face another issue as shown below while try to insert a records with transaction amount more than 100000. This is due to the permission issue.


Detail error message:
Msg 6522, Level 16, State 1, Procedure MyCLRTrigger, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "MyCLRTrigger":
System.Security.SecurityException: Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
 at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
 at System.Security.CodeAccessPermission.Demand()
 at System.Net.Mail.SmtpClient.Initialize()
 at System.Net.Mail.SmtpClient..ctor(String host, Int32 port)
 at Triggers.SendMail(String CustomerName, Double TrnAmt)
 at Triggers.MyCLRTrigger()


First change the Permission Level as “UNSAFE”.


Even after you change the permission level you might get the issue as shown below. This problem is due to the permission related issue to solve that do the following. Make Trustworthy CLR in database. The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. By default, this setting is OFF, but can be set to ON by using the ALTER DATABASE statement.
Error   1           SQL72014: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 ALTER ASSEMBLY for assembly 'CLRDemo' failed because assembly 'CLRDemo' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.


The following image show that how to make it CLR Trustworthy in Database level via SQL Server Management Studio.


The below image shows that how to make it through the project.


Once deploy the CLR again, just run it and see. You will receive the email as you expects.
Cheers!



How to remove double quotes when import data from CSV file in SSIS

To remove double quotes from CSV file via SSIS Flat File Connection Manager, just type the Text Qualifier under general tab. By default it will display as <none>, you need to change as double quote (“).
The below screen dump is shows the default setting.


After change Text Qualifier as Double Quotes.


Cheers!

HTML5 new Form input types with sample

HTML5 introduces many new form input types, to make the design more convenient and easy, specifically for form input validation and mobile support. In here, the screen dumps are desktop based Google Chrome (Version 33.0.1750.154 m). Here you might not see all the benefits of these inputs unless used via mobile devices. Some of the main important new input types are following:
search
range
week
email
color
time
url
date
datetime-local
tel
datetime
list
number
month
The following screen dup show the code for the above form input type

The output via Google Chrome:
Search : Desktop browser will render this in a similar way to a standard text field, once you start typing there will be X button will display to clear the text, however in mobile browser once you click on it, keyboard will be according to the search utility.


Email: This will automatically verify the email address, such as whether @ or (.) symbol is available in the text or not.

Here 2 more additional key words added they are required and Placeholder. Required has made the filed input is mandatory and Placeholder display the sample format of required fields.


Placeholder
*
Required
*


Url: This is for specifically for web url.



Tel: This is for telephone number, there is no any specialty in desktop browser, but in mobile browser, the telephone number keyboard will display as shown below.


Number: This is specifically for number; this will validate the number and can get the number instead of typing using the button.



Range:  it represents a numeric value within a given range.



Color: it allows the user to select a color and returns the hex value for that color.

Date and Time: There are many different inputs for data and time, they are date, time, datetime (with time zone), datatime-local (without time zone), month and week. All for different purposes, such as month input type used for a credit card expiry date.


Month
*
Week
*
Time
*
Datetime
*
Data List: Data list is different from drop down box of previous version, when you start typing this will filer the values, the below screen dup shown that when I type “U”, it will filter the countries which start with U.



Cheers!