Sending email through MS Sql Server

This sample is intended for MVC, C# code. The front end code consists of Kendo UI & jquery.

I will be using asynchronous mode to upload the file to a temporary destination path “FileUploads” in the server. Users need to be given access to this folder first.

[HttpPost]

public ActionResult SaveAttachment(HttpPostedFileBase ImageFile)

{

       imagepath = ImageFile.FileName;

       var destinationPath = Path.Combine(Server.MapPath("~/FileUploads"), imagepath);

       FileInfo files = new FileInfo(destinationPath);

       ImageFile.SaveAs(destinationPath);

       imagepath = destinationPath;

}

Once the file has been uploaded to the folder in server, our program needs a method to get the server IP. (We may hardcode the IP for server, but that will not be ideal.)

        private string GetIP()

        {

            IPHostEntry host;

            string localIP = "?";

            host = Dns.GetHostEntry(Dns.GetHostName());

            foreach (IPAddress ip in host.AddressList)

            {

                if (ip.AddressFamily == AddressFamily.InterNetwork)

                {

                    localIP = ip.ToString();

                }

            }

            return localIP;

        }

public ActionResult EMail([DataSourceRequest] DataSourceRequest request, Somemodel model)

{         

string ip = GetIP();

       string fileUploadPath = @"\\" + ip + "\\FileUploads\\";

String[] filecount = FileNames.Split(','); //Here get FileNames from FileUploads folder

//Handling multiple files in the attachment here, attachments must be separated by ;

       foreach (string filename in filecount)

       {

                     if (attachmentPath == "")

              {

                           attachmentPath = fileUploadPath + filename;

              }

              else

              {

                     attachmentPath = attachmentPath + ";" + fileUploadPath + filename;

                     }

              }

//Use some business entity to send the mail information to Data access layer

       SomeBusinessEntity mailMaster = new MailMaster

       {

                     Recipients = model.Recipients,

Subject = model.Subject,

                     Body = System.Web.HttpUtility.HtmlDecode(model.Body),

                     BodyFormat = model.BodyFormat,                       

                     FileAttachments = attachmentPath

                     CreatedDate = DateTime.Now

       };

       MailDAL.Email(mailMaster);

}

You may directly call the msdb.dbo.sp_send_dbmail stored procedure from DAL, or the other option is to write a separate stored procedure which will then call the system defined one.

If all code goes fine, your mail will be reaching the intended recipient.

This example also solves the issue "attachment file is invalid" – error 22051 as we use GetIP() to get IP of the server.

SplitString SQL function returning comma seperated values in a table

Missed on the function that will receive comma seperated IDs and return as a table after splitting them.

CREATE
FUNCTION [dbo].[SplitString] (
@myString
VARCHAR(500),
@deliminator
VARCHAR(10)
)
RETURNS
@ReturnTable
TABLE (
[id] [int]
IDENTITY(1,1) NOT NULL,
[part] [varchar]
(50) NULL
)
AS
BEGIN
DECLARE @iSpaces INT
DECLARE @part VARCHAR(50)
SELECT @iSpaces = CHARINDEX(@deliminator,@myString,0)
WHILE @iSpaces > 0
BEGIN
SELECT @part = SUBSTRING(@myString,0,CHARINDEX(@deliminator,@myString,0))
INSERT INTO @ReturnTable(part)
SELECT @part
SELECT @myString = SUBSTRING(@mystring,CHARINDEX(@deliminator,@myString,0)+ LEN(@deliminator),LEN(@myString) - CHARINDEX(' ',@myString,0))
SELECT @iSpaces = CHARINDEX(@deliminator,@myString,0)
END
IF LEN(@myString) > 0
INSERT INTO @ReturnTable
SELECT @myString
RETURN END

Fetch Cartesian product count from 2 column, both columns containing ID, one containing comma separated IDs

I am working on a chart which uses xml data to build the graph. Well, here I am not going to do anything on xml. Rather I will look into what data the chart is expecting, what were the challenges that I faced and how did I overcome it.

I have a table named ‘OfficeDetails’, of which I am interested in 3 columns viz., ‘AppID’, ‘ManagedBy’, ‘BU’. My graph will contain the information of the last 2 columns, but as I need some unique column for filtering the data, I had chosen AppID for that purpose.  ManagedBy and BU contains the ID and the actual values are available in OFFICELookupValue table.

The graph need the count of ManagedBy for each BU. However the graph need the count even if a ManagedBy –BU count is 0; i.e, if there are 10 unique BU and 5 ManagedBy then we need to return 10*5=50 records. The logic becomes more complicated because the BU column consists of comma separated Ids. I looked for some sort of logic to loop through the rows in OfficeDetails table. Most of them suggested cursors. Well, I am aware that cursor in SqlServer is not a very good idea. Looked around and I found a much better solution.

Step 1

Create a table variable:

DECLARE @tempOfficeDetails TABLE(AppID INT , ManagedBy INT, BU VARCHAR(200)) 

Step 2

Insert the required data to the table variable, we may also filter data in this step:

INSERT INTO @tempOfficeDetails  

      SELECT AppID, ManagedBy, BU FROM OfficeDetails ORDER BY BU DESC

Step 3

Handling the comma separated values of BU need one more table variable:

DECLARE @tempOfficeDetailsSplitBU TABLE(AppID INT , ManagedBy INT, BU VARCHAR(200))

Step 4

As we move through the While loop the top values are inserted to tempOfficeDetailsSplitBU  table:

SELECT TOP 1 @AppID = AppID, @ManagedBy = ManagedBy, @BU = BU FROM @tempOfficeDetails 

      WHILE @AppID IS NOT NULL 

      BEGIN  

            INSERT INTO @tempOfficeDetailsSplitBU SELECT AppID, ManagedBy, S.part FROM @tempOfficeDetails CROSS APPLY [dbo].[SplitString](@BU, ',') AS S WHERE AppID =@AppID 

            DELETE  @tempOfficeDetails WHERE @AppID = AppID 

            SET @AppID = NULL          

            SELECT TOP 1 @AppID = AppID, @ManagedBy = ManagedBy, @BU = BU FROM @tempOfficeDetails   

      END

Step 5

We Created one more table variable @BusinessFunctManagedBy which will store the all the records where ManagedBy –BU count is >0.

DECLARE @BusinessFunctManagedBy TABLE           

      (           

            BusinessFunct VARCHAR(254),           

            ManagedBy VARCHAR(254),           

            OfficeCount INT            

      )

Step 6

The step/logic to insert the records from tempOfficeDetailsSplitBU is below:

INSERT INTO @BusinessFunctManagedBy           

      SELECT officelkp2.[Value] AS BusinessFunct, officelkp.[Value] AS ManagedBy,            

            OfficeCount = CASE WHEN officelkp2.[Value] IS NULL THEN 0 ELSE COUNT(1) END           

      FROM  @tempOfficeDetailsSplitBU  AS OfficeDetails         

      LEFT JOIN OFFICELookupValue officelkp      

            ON officelkp.pkID = OfficeDetails.ManagedBy 

      LEFT JOIN OFFICELookupValue officelkp2      

            ON officelkp2.pkID = OfficeDetails.BU           

      GROUP BY officelkp2.[Value], officelkp.[Value] ORDER BY officelkp2.[Value] DESC

Step 7

Since we need the records where BU-ManagedBy count is 0, we created 2 table variables and inserted records as below:

DECLARE @BusinessFunct TABLE           

      (           

            BusinessFunct VARCHAR(254)           

      )           

             

      DECLARE @ManagedByTab TABLE           

      (           

            ManagedBy VARCHAR(254)           

      )           

             

      INSERT INTO @BusinessFunct           

      SELECT DISTINCT officelkp.[Value]  AS BusinessFunct         

      FROM  @tempOfficeDetailsSplitBU  AS OfficeDetails    

      LEFT JOIN OFFICELookupValue officelkp      

            ON officelkp.pkID = OfficeDetails.BU       

          

      --SELECT * from @BusinessFunct

             

      INSERT INTO @ManagedByTab           

      SELECT DISTINCT officelkp.[Value] AS ManagedBy         

      FROM  @tempOfficeDetailsSplitBU  AS OfficeDetails      

      LEFT JOIN OFFICELookupValue officelkp      

            ON officelkp.pkID = OfficeDetails.ManagedBy 

Step 8

We created one more table variable TotalMatching which inserts the Cartesian product of ManagedBy-BU columns with count 0. Next it update the OfficeCount with values from BusinessFunctManagedBy table that we created earlier. Next delete all the records where record count is 0. Hence we are left with only the records with 0 count. Insert them to BusinessFunctManagedBy.

DECLARE @TotalMatching TABLE           

      (           

            BusinessFunct VARCHAR(254),           

            ManagedBy VARCHAR(254),            

            OfficeCount INT           

      )           

             

      INSERT INTO @TotalMatching           

      SELECT H.BusinessFunct,M.ManagedBy,0           

      FROM @BusinessFunct H           

      CROSS JOIN @ManagedByTab M           

             

      UPDATE T           

      SET T.OfficeCount = A.OfficeCount           

      FROM @TotalMatching T           

      INNER JOIN @BusinessFunctManagedBy A           

            ON T.BusinessFunct = A.BusinessFunct           

            AND T.ManagedBy=A.ManagedBy           

             

      DELETE FROM @TotalMatching           

      WHERE OfficeCount > 0           

             

      INSERT INTO @BusinessFunctManagedBy           

      SELECT * FROM @TotalMatching           

 The complete stored procedure is below:

CREATE PROC dbo.OFFICE_BusinessFunctManagedBy     

AS       

BEGIN  

      DECLARE @tempOfficeDetails TABLE(AppID INT , ManagedBy INT, BU VARCHAR(200)) 

      DECLARE @tempOfficeDetailsSplitBU TABLE(AppID INT , ManagedBy INT, BU VARCHAR(200))

      DECLARE  @AppID INT, @ManagedBy INT, @BU VARCHAR(200)

      INSERT INTO @tempOfficeDetails  

      SELECT AppID, ManagedBy, BU FROM OfficeDetails ORDER BY BU DESC 

      SELECT TOP 1 @AppID = AppID, @ManagedBy = ManagedBy, @BU = BU FROM @tempOfficeDetails 

      WHILE @AppID IS NOT NULL 

      BEGIN  

            INSERT INTO @tempOfficeDetailsSplitBU SELECT AppID, ManagedBy, S.part FROM @tempOfficeDetails CROSS APPLY [dbo].[SplitString](@BU, ',') AS S WHERE AppID =@AppID 

            DELETE  @tempOfficeDetails WHERE @AppID = AppID 

            SET @AppID = NULL          

            SELECT TOP 1 @AppID = AppID, @ManagedBy = ManagedBy, @BU = BU FROM @tempOfficeDetails   

      END  

      DECLARE @BusinessFunctManagedBy TABLE           

      (           

            BusinessFunct VARCHAR(254),           

            ManagedBy VARCHAR(254),           

            OfficeCount INT            

      )           

      INSERT INTO @BusinessFunctManagedBy           

      SELECT officelkp2.[Value] AS BusinessFunct, officelkp.[Value] AS ManagedBy,            

            OfficeCount = CASE WHEN officelkp2.[Value] IS NULL THEN 0 ELSE COUNT(1) END           

      FROM  @tempOfficeDetailsSplitBU  AS OfficeDetails         

      LEFT JOIN OFFICELookupValue officelkp      

            ON officelkp.pkID = OfficeDetails.ManagedBy 

      LEFT JOIN OFFICELookupValue officelkp2      

            ON officelkp2.pkID = OfficeDetails.BU           

      GROUP BY officelkp2.[Value], officelkp.[Value] ORDER BY officelkp2.[Value] DESC

      --SELECT * from @BusinessFunctManagedBy order by BusinessFunct asc

      DECLARE @BusinessFunct TABLE           

      (           

            BusinessFunct VARCHAR(254)           

      )           

             

      DECLARE @ManagedByTab TABLE           

      (           

            ManagedBy VARCHAR(254)           

      )           

             

      INSERT INTO @BusinessFunct           

      SELECT DISTINCT officelkp.[Value]  AS BusinessFunct         

      FROM  @tempOfficeDetailsSplitBU  AS OfficeDetails    

      LEFT JOIN OFFICELookupValue officelkp      

            ON officelkp.pkID = OfficeDetails.BU       

          

      --SELECT * from @BusinessFunct

             

      INSERT INTO @ManagedByTab           

      SELECT DISTINCT officelkp.[Value] AS ManagedBy         

      FROM  @tempOfficeDetailsSplitBU  AS OfficeDetails      

      LEFT JOIN OFFICELookupValue officelkp      

            ON officelkp.pkID = OfficeDetails.ManagedBy              

             

      DECLARE @TotalMatching TABLE           

      (           

            BusinessFunct VARCHAR(254),           

            ManagedBy VARCHAR(254),            

            OfficeCount INT           

      )           

             

      INSERT INTO @TotalMatching           

      SELECT H.BusinessFunct,M.ManagedBy,0           

      FROM @BusinessFunct H           

      CROSS JOIN @ManagedByTab M           

             

      UPDATE T           

      SET T.OfficeCount = A.OfficeCount           

      FROM @TotalMatching T           

      INNER JOIN @BusinessFunctManagedBy A           

            ON T.BusinessFunct = A.BusinessFunct           

            AND T.ManagedBy=A.ManagedBy           

             

      DELETE FROM @TotalMatching           

      WHERE OfficeCount > 0           

             

      INSERT INTO @BusinessFunctManagedBy           

      SELECT * FROM @TotalMatching           

             

      SELECT * FROM @BusinessFunctManagedBy ORDER BY LEN(BusinessFunct) DESC, 2, 1           

END