Thursday, January 26, 2017

Use DLL/Assembly without install in GAC, C# /SSIS script

This post will describe the use of Assembly type DLL without installing it on GAC

Always we have to install an assembly first then we can use the DLL file into C# code. But when we need to develop something portable and need to run from any machine. So, for this case, we have to use an assembly like a normal DLL, means which DLL has no any strong name. But we everybody knows that assembly type DLL has a strong name so that we can’t use it without install.
Furthermore, there are many procedures to use an Assembly or Strong Named DLL as a reference in the C# coding …
·         Remove Strong Name from the DLL
·         Use DLL merger to change the strong name by the new one, every time we need to run the C# code.
But above process is not so easy to implement. But there is another procedure available which is as simple as it says.
·         Load the Assembly DLL file at the runtime of the beginning of the program which needs it to execute.
Yes, it’s so easy to implement and every time c#.NET will load the assembly from the defined location. Follow below points to implement the procedure.
·         Because of we have to load the assembly/DLL at the beginning of the program, we will create a static constructor of the class of first entry point, that means Main Class should have a static constructor.
·         Put the assembly/DLL file on a fix location and mention this location here to load at runtime.
·         And then implement an interface/event handler named ResolveEventHandler to include the missing assemblies from the pre-defined location
Let’s see the coding for that ...

NOTE: you need to change the GREEN parts only

public class assemblyLoadAtRuntime 
{
…..
…..
…..

static string[] Path = { @"D:\temp\" , "AssemblyDLL" } ; // { "Path of the DLL file" , "Name of the DLL file without extension" }

     static assemblyLoadAtRuntime () // static Constractor of your class
     {
         AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentResolve);
     }

     static System.Reflection.Assembly CurrentResolve (object sender, ResolveEventArgs args)
     {

         if (args.Name.Contains(Path[1]))  // change this code if you have many dll file to load
         {
return System.Reflection.Assembly.LoadFile(Path[0] + Path[1]+".dll");
         }
         return null;
     }

…..
…..
…..

}

Here assemblyLoadAtRuntime is the main class
AssemblyResolve represent the list of assemblies
And finally, assemblyLoadAtRuntime is the interface to implement to load the SN DLL.

This procedure is very much simple but it can solve the problem of assembly use.

Friday, January 20, 2017

Send mail as html format from SQL Server DB with attached file and/or embedded picture/video

Sometimes we need to send task/operation status report as a well-formatted mail, known as HTML formatted mail. So that, we need to setup a DB mail environment to provide mail from DB end.

Also, below description can be used to send mail as plain text format from DB end... 

To send a mail from DB end, we have to follow below 3 simple steps...

NOTEmake sure login user have granted on the role “DatabaseMailUser” and have permission to execute MSDB procedure “sp_send_dbmail”  ... always "Admin" role is great :)

1.       Create a Database Mail Profile
2.       Send a test mail to test Mail Profile is working
3.   Execute Send Mail Command to Send a Mail (HTML and/or plain text) Using above DB Mail Profile.

See below detail (step-by-step) description of above points...

Connect to your SQL Server database. You can use any user, ie SQL user and/or Windows authentication.

Step - 1. Create a Database Mail Profile:
After login to DB look at the option "Database Mail" under "Management" of Object Explorer window [normally Object Explorer located on the left side of MSSM] section... See Figure1:"DB Mail-1"

All we have to create a DB Mail profile using "Database Mail" option. We can do this by using Wizard Guide and SQL command.

1.1: Wizard Guide: steps are as follows. You have to follow the on screen instruction.

A.    right click on the "Database Mail" option then click "Configure Database Mail". A new window will open and Click next if you are in the welcome page.

B.    Check the option “set up database mail by performing the following tasks:”, Then click next. See “Figure 3: Select Conf Task”

C.    Fill-up the “New Profile” page by providing required information.
Give a name for this new profile: Test_Profile_Name
Write a short description for this profile (its optional) : Test_Profile_Desc
 

D.    Now click on “Add…” under the SMTP section. See Figure 3: New Profile
E.     New window will come named “Add Account to Profile”, see figure4- add account to profile. In this section you have to create a new account, if you don’t have one, to send the mail.
F.     Now you have to fill-up “New Database Mail Account” form.
Give a name: Test_Mail_Account
Give a desc : Test_Mail_Desc
Give an Email address to send a mail from this address: test@test.com
Provide display name to show in the recipient inbox: Test_Display_Name
Provide a reply address to use when recipient like to reply the mail: I leave it blank because of this is a system generated mail.
Provide Mail Server Name from which server will send the mail: testMailServer.com
Provide server port number: 25 (this is default to 25, if not provide the actual port for the provided mail server)
If encryption required please check the option SSL required.
G.    Now provide the SMTP Authentication information:
There are three options: you have to choose one.
·            Windows autho: by this DBMailAccount will use the user, who run the DB Engine service.
·            Basic Autho: it’s a SQL Server user and password.
·            Anonymous Autho: if DB Engine allow guest user to login.

H.    Click “Finish”. Ok, you are done to setup a Mail Profile named : Test_Profile_Name

Step - 2. Send a test mail to test the Mail Profile, Test_Profile_Name,  is working : go to “Send Test E-Mail…

Now check test result: go to “Database Mail Log
 

Step - 3. Execute Send Mail Command to Send a Mail (HTML and/or plain text) Using above DB Mail Profile.

As “HTML” format:
-----------------------------------------
EXEC msdb..sp_send_dbmail
 @profile_name = 'GILEAD_Mail'
 ,@recipients = 'Saleh.Faize@bd.imshealth.com;saiful.azam@bd.imshealth.com'
 ,@copy_recipients = 'RSaha@bd.imshealth.com'
 ,@subject = 'A Message From GILEAD DB ADMIN!'
 ,@body = '<b>TEST</b> <i>Mail</i> <font color="FF0000">Body</font> from <font color="00FF00"><b>DataBase End</b></font> J'
 @body_format = 'HTML';
-----------------------------------------
Received mail would be like below image---

As “Plain Text” format:
-----------------------------------------
EXEC msdb..sp_send_dbmail
 @profile_name = 'GILEAD_Mail'
 ,@recipients = 'Saleh.Faize@bd.imshealth.com;saiful.azam@bd.imshealth.com'
 ,@copy_recipients = 'RSaha@bd.imshealth.com'
 ,@subject = 'A Message From GILEAD DB ADMIN!'
 ,@body = 'Mail body as plain text J'
 @body_format = 'Text';
-----------------------------------------