CRM remove email warning "The email below might contain script or content that is potentially harmful and has been blocked."

1. Download and install the solution
• You can get the latest version under this link: https://github.com/seanmcne/OrgDbOrgSettings/releases
2. Open the solution and locate the “SecuritySettingforEmail” entry
• Open the Solution and activate the tracking through pressing of the “Add” button.
3. Change the “SecuritySettingforEmail” value from 1 to 3

https://iiu.dk/2019/06/03/farewell-email-warning/

Loop through sys columns and change data in tables to proper case

DECLARE @SQL varchar(max)
DECLARE @MyCursor CURSOR
DECLARE @TableName char(50)
DECLARE @FieldName char(50)

--Get a list of all tables in DB
if object_id('tempdb..#temp') is not null
drop table #temp
--45
select o.name TableName,c.name FieldName, t.name datatype , c.max_length , 2 keep
into #temp
from sys.objects o inner join sys.columns c on o.object_id =c.object_id inner join sys.types t on c.user_type_id =t.user_type_id
where type = 'U' and   t.precision =0 and t.max_length >0
and o.name not like 'meta%' and o.name not like 'sysd%' 
and c.name in  ('Address1', 'Address2', 'City', 'Country', 'County', 'Description','FirstName', 'LastName', 'Name',  'JobTitle', 'MiddleName')

--Display the top 3 records of each table         
SET @MyCursor = CURSOR FOR
 select rtrim(ltrim(TableName)),rtrim(ltrim(FieldName))  from #temp order by fieldname

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @TableName, @FieldName
WHILE @@FETCH_STATUS = 0
BEGIN

IF @fieldname = 'Country'
BEGIN
--SET @SQL = ' SELECT TOP 3 '''+ @fieldname +''', dbo.topropercase(' + rtrim(ltrim(@fieldname)) + ') FROM  ' + rtrim(ltrim(@TableName) + ' where ' + @fieldname + ' not in (''USA'', ''US'') and len(rtrim(ltrim(' + @fieldname + ')))>2') 
SET @SQL = 'UPDATE ' + rtrim(ltrim(@TableName) + 'SET '+ @fieldname +'= dbo.topropercase(' + rtrim(ltrim(@fieldname)) + ')  where ' + @fieldname + ' not in (''USA'', ''US'') and len(rtrim(ltrim(' + @fieldname + ')))>2') 
END
ELSE
BEGIN
--SET @SQL = ' SELECT TOP 3 '''+ @fieldname +''', dbo.topropercase(' + rtrim(ltrim(@fieldname)) + ') FROM  ' + rtrim(ltrim(@TableName) + ' where ' + @fieldname + ' is not null') 
SET @SQL = 'UPDATE ' + rtrim(ltrim(@TableName) + 'SET '+ @fieldname +'= dbo.topropercase(' + rtrim(ltrim(@fieldname)) + ')  where ' + @fieldname + ' is not null') 
END

 --set @sql = 'select '+ @fieldname +' from ' + @TableName
--select @SQL
EXEC(@SQL)

FETCH NEXT FROM @MyCursor
INTO @TableName, @FieldName
END
CLOSE @MyCursor
DEALLOCATE @MyCursor

SQL funtion to change text to proper case

CREATE FUNCTION ToProperCase(@string VARCHAR(255)) RETURNS VARCHAR(255)
AS
BEGIN
  DECLARE @i INT           -- index
  DECLARE @l INT           -- input length
  DECLARE @c NCHAR(1)      -- current char
  DECLARE @f INT           -- first letter flag (1/0)
  DECLARE @o VARCHAR(255)  -- output string
  DECLARE @w VARCHAR(10)   -- characters considered as white space

  SET @w = '[' + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(160) + ' ' + ']'
  SET @i = 1
  SET @l = LEN(@string)
  SET @f = 1
  SET @o = ''

  WHILE @i <= @l
  BEGIN
    SET @c = SUBSTRING(@string, @i, 1)
    IF @f = 1 
    BEGIN
     SET @o = @o + @c
     SET @f = 0
    END
    ELSE
    BEGIN
     SET @o = @o + LOWER(@c)
    END

    IF @c LIKE @w SET @f = 1

    SET @i = @i + 1
  END

  RETURN @o
END

Load image to SQL

INSERT INTO [Images]
           ([ImageId]
           ,[FileName]
           ,[FileSize]
           ,[ImageExt]
           ,[CreatedDateTime]
           ,[ModifiedDateTime]
           ,[CreatedBy]
           ,[ModifiedBy]
           ,[CompanyId]
           ,[ImageData])
     select
           'Test3'
           ,'Test3'
           ,'Test3'
           ,'jpg'
           ,'1/1/2019'
           ,'1/1/2019'
           ,'Test'
           ,'Test'
           ,'Test'
   ,bulkColumn FROM OPENROWSET(BULK N'e:\temp\monica\flowers.jpg', SINGLE_BLOB) image

SQL Cursor

DECLARE @SQL varchar(max)
DECLARE @MyCursor CURSOR
DECLARE @TableName char(50)

--Get a list of all tables in DB
if object_id('tempdb..#tableList') is not null
drop table #tableList
select name, object_id into #tableList from sys.objects where type='u' and name not like 'sys%'

--Display the top 3 records of each table
SET @MyCursor = CURSOR FOR
 select name  from #tableList

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = ' SELECT TOP 3 * FROM  ' + rtrim(ltrim(@TableName))

EXEC(@SQL)

FETCH NEXT FROM @MyCursor
INTO @TableName
END
CLOSE @MyCursor
DEALLOCATE @MyCursor

c# Convert a comma separate string into astring array and loop

string fruit = "Apple,Banana,Orange,Strawberry";
string[] split = fruit.Split(',');

foreach (string item in split)
{
    Console.WriteLine(item);
}

c# Create Datatable

 public static DataTable CreateDataTable()
        {

            DataTable dt = new DataTable();

            DataColumn col1 = new DataColumn("FirstName");
            DataColumn col2 = new DataColumn("LastName");
            DataColumn col3 = new DataColumn("Age");

            col1.DataType = System.Type.GetType("System.String");
            col2.DataType = System.Type.GetType("System.String");
            col3.DataType = System.Type.GetType("System.String");

            dt.Columns.Add(col1);
            dt.Columns.Add(col2);
            dt.Columns.Add(col3);

            DataRow row1 = dt.NewRow();
            row1[col1] = "Bob";
            row1[col2] = "Smith";
            row1[col3] = "47";
            dt.Rows.Add(row1);

            DataRow row2 = dt.NewRow();
            row2[col1] = "Tina";
            row2[col2] = "Smith";
            row2[col3] = "34";
            dt.Rows.Add(row2);

            DataRow row3 = dt.NewRow();
            row3[col1] = "Maria";
            row3[col2] = "Smith";
            row3[col3] = "27";
            dt.Rows.Add(row3);

            return dt;

        }

c# add Colunm to Datatable and populate with same value

        public static void AddColunmToDataTable(DataTable dt, string colunmName, string colunmValue)
        {
            dt.Columns.Add(colunmName, typeof(System.String));
            foreach (DataRow row in dt.Rows)
            {
                row[colunmName] = colunmValue;
            }
        }

c# remove last row on a datatable

c# remove last row on a datatable

dt.Rows.RemoveAt(dt.Rows.Count - 1)

SQL - script to generate c# class

SELECT 
Name,
Case system_type_id
WHEN 127 THEN 'long'
WHEN 56 THEN 'int'
WHEN 60 THEN 'decimal'
WHEN 106 THEN 'decimal'
WHEN 61 THEN 'DateTime'
WHEN 104 THEN 'bool'
WHEN 165 THEN 'byte[]'
WHEN 108 THEN 'double'
WHEN 231 THEN 'string'
WHEN 99 THEN 'string'
WHEN 239 THEN 'string'
WHEN 167 THEN 'string'
when 62 then 'decimal'
ELSE 'ukn:'+CAST(system_type_id AS NVARCHAR(10))
END As [Type],
system_type_id,
'[DataField("' + name +  '")] ' +
+ 'public '+ Case system_type_id
WHEN 127 THEN 'long'
WHEN 56 THEN 'int'
WHEN 60  THEN 'decimal'
WHEN 106 THEN 'decimal'
WHEN 61 THEN 'DateTime'
WHEN 104 THEN 'bool'
WHEN 165 THEN 'byte[]'
WHEN 108 THEN 'double'
WHEN 231 THEN 'string'
WHEN 99 THEN 'string'
WHEN 239 THEN 'string'
WHEN 167 THEN 'string'
when 62 then 'decimal'
ELSE 'ukn:'+CAST(system_type_id AS NVARCHAR(10))
END + ' '+ replace(replace(replace(name, ' ','_'), '-','_'), '''','_') + ' { get; set; }'
AS Code,
'public '+ Case system_type_id
WHEN 127 THEN 'long'
WHEN 56 THEN 'int'
WHEN 60  THEN 'decimal'
WHEN 106 THEN 'decimal'
WHEN 61 THEN 'DateTime'
WHEN 104 THEN 'bool'
WHEN 165 THEN 'byte[]'
WHEN 108 THEN 'double'
WHEN 231 THEN 'string'
WHEN 99 THEN 'string'
WHEN 239 THEN 'string'
WHEN 167 THEN 'string'
when 62 then 'decimal'
ELSE 'ukn:'+CAST(system_type_id AS NVARCHAR(10))
END + ' '+ replace(replace(replace(name, ' ','_'), '-','_'), '''','_') + ' { get; set; }'
AS CodeClass
from Sys.columns where [OBJECT_ID]=OBJECT_ID('[DRIVERSMASTER]')

c# SFTP download all files

        public static void DownloadAllFile()
        {
            try
            {
                string host = Properties.Settings.Default.SFTP;
                string username = Properties.Settings.Default.SFTP_UserName;
                string password = Properties.Settings.Default.SFTP_Password;

                string remoteDirectory = Properties.Settings.Default.SFTP_Path;
               
                string downloadDirectory = Properties.Settings.Default.DownloadDirectory;


                using (SftpClient sftp = new SftpClient(host, username, password))
                {
                    try
                    {
                        sftp.Connect();
                        var files = sftp.ListDirectory(remoteDirectory);

                        foreach (var file in files)
                        {
                            string fileName = file.Name;
                            if (fileName.Length > 2)
                            {
                                //Only download file if the file name begins with XX
                                if (fileName.Substring(0, 2) == "XX")
                                {
                                    Console.WriteLine(file.Name);
                                    using (Stream fileStream = File.OpenWrite(downloadDirectory + file.Name))
                                    {
                                        sftp.DownloadFile(file.FullName, fileStream);
                                    }
                                }
                            }
                        }

                        sftp.Disconnect();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine("An exception has been caught " + e.ToString());
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("General Exception: {0}", e);
            }
        }

c# SFTP List all Files

This method will list all files in SFTP.

using Renci.SshNet;

    public static void ListFiles()
        {
            try
            {
                string host = Properties.Settings.Default.SFTP;
                string username = Properties.Settings.Default.SFTP_UserName;
                string password = Properties.Settings.Default.SFTP_Password;

                string remoteDirectory = Properties.Settings.Default.SFTP_Path;

                using (SftpClient sftp = new SftpClient(host, username, password))
                {
                    try
                    {
                        sftp.Connect();
                        var files = sftp.ListDirectory(remoteDirectory);

                        foreach (var file in files)
                        {
                            Console.WriteLine(file.Name);
                        }

                        sftp.Disconnect();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine("An exception has been caught " + e.ToString());

                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("General Exception: {0}", e);
            }
        }

c# Method to send email with attachment

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Net.Mail;
using System.Net.Mime;

namespace SendEmail
{
    public class SMTPHelper
    {

        //SMTP for email - set these up in project --> Property --> Settings (app.config)
        public static string _SmtpServer;
        public static int _SmtpPort;
        public static Boolean _SmtpIsAuthenticated;
        public static string _SmtpUserName;
        public static string _SmtpPassword;
        public static string _SmtpDomain;

        public static string _SmtpFrom;
        public static string _SmtpDefaultTo;
        public static string _DebugTo;
        public static bool _isDebug;

        public SMTPHelper()
        {

            _SmtpServer = Properties.Settings.Default.smtpServer;
            _SmtpPort = Properties.Settings.Default.smtpPort;
            _SmtpIsAuthenticated = Properties.Settings.Default.smtpIsAuthenticated;
            _SmtpUserName = Properties.Settings.Default.smtpUserName;
            _SmtpPassword = Properties.Settings.Default.smtpPassword;
            _SmtpDomain = Properties.Settings.Default.smtpDomain;


            _SmtpFrom = Properties.Settings.Default.smtpFrom;
            _SmtpDefaultTo = Properties.Settings.Default.smtpDefaultTo;

            _DebugTo = Properties.Settings.Default.smtpDebugTo;
            _isDebug = Properties.Settings.Default.DebugMode;



        }

        public string SendEmail(string emailAddresses, string subject, string message, string submessage, bool isHighPriority, string fileToAttach)
        {
            string retObj = null;
            try
            {

                //emailAddresses = (emailAddresses == "" || emailAddresses == null) ? _SmtpDefaultTo : emailAddresses;
                System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();
                msg.Subject = subject;
                string toAddress = (_isDebug) ? _DebugTo : emailAddresses;
                string[] toArray = toAddress.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                foreach (var item in toArray)
                {
                    msg.To.Add(item);
                }


                msg.From = new System.Net.Mail.MailAddress(_SmtpFrom);
                msg.IsBodyHtml = true;
                msg.Body = message + "<p> " + submessage + "</p>";
                if (_isDebug)
                {
                    msg.Body += "<h3>Debug Mode is On. <br/> Intended Email Addresses: " + emailAddresses + "</h3>";
                }
                //msg.Body += (ex == null) ? "" : ex.ToString();
                //msg.AlternateViews.Add(view);

                msg.Priority = (isHighPriority) ? System.Net.Mail.MailPriority.High : System.Net.Mail.MailPriority.Normal;
                msg.IsBodyHtml = true;
                Attachment data = new Attachment(fileToAttach, MediaTypeNames.Application.Octet);
                msg.Attachments.Add(data);
                System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient(_SmtpServer);
                smtp.Port = _SmtpPort;

                if (_SmtpIsAuthenticated)
                {
                    smtp.Credentials = new System.Net.NetworkCredential(_SmtpUserName, _SmtpPassword, _SmtpDomain);
                }

                smtp.Send(msg);

            }
            catch (Exception e)
            {
                // log the exception
                retObj = e.ToString();
                string errorStr = "General Exception Email: " + e.ToString();
                Utility.LogWrite(errorStr);
                Console.WriteLine("General Exception Email: {0}", e);
            }

            return retObj;
        }
    }
}

c# utility to log in a text file

using System.IO;

//Loggin
public static string LogFilePath;
public static bool DebugMode;

LogFilePath = Properties.Settings.Default.LogFilePath;
DebugMode = Properties.Settings.Default.DebugMode;

        public static void LogWrite(string logText)
        {

            if (GlobalVars.DebugMode)
            {
                // check to see if the directory exists
                FileInfo logFile = new FileInfo(GlobalVars.LogFilePath);
                if (!logFile.Directory.Exists)
                {
                    logFile.Directory.Create();
                }

                logText = Environment.NewLine + DateTime.Now + " - " + logText;
                File.AppendAllText(GlobalVars.LogFilePath, logText);
            }
        }

c# Convert a non delimited txt file to a DataTable

This a a way to convert a non delimited txt file into a DataTable. First you would need another table with the definition of the file.  The table below would be the DataTable dt_Definition mentioned on the method below.

FieldType	ReportColunmName	Begin	Length	Order
Detail	Last Name	1	30	1
Detail	First Name	31	20	2
Detail	Middle Initial	51	1	3
Detail	Address	52	60	4
Detail	City	112	30	5
Detail	State	142	2	6
Detail	Zip	144	10	7
Detail	Phone AreaCode	154	3	8
Detail	Phone	157	8	9
Detail	Phone Ext	164	4	10
Detail	Hope Phone	169	15	11
Detail	Date of Birth	184	10	12
Detail	Sex	194	1	13
Detail	SSN	195	9	14

using System.IO;
using System.Data;

        static DataTable ConvertTxtFileToDataTable(string dataFile, DataTable dt_Definition)
        {
            DataTable retObj = new DataTable();
            try
            {
                //Add colunms
                foreach (DataRow row in dt_Definition.Rows)
                {
                    string reportColunmName = row["ReportColunmName"].ToString().Trim();
                    string recordType = row["FieldType"].ToString();
                    Int32 order = Int32.Parse(row["Order"].ToString());

                    if (order > 0 && recordType == "Detail")
                    {
                        retObj.Columns.Add(new DataColumn(reportColunmName, typeof(string)));
                    }
                }

                //Add rows
                bool isFirstLine = true;
                var lines = File.ReadAllLines(dataFile);
                foreach (string line in lines)
                {

                    string currentLine = line;

                    if (isFirstLine == false)
                    {
                        DataRow newRow = retObj.NewRow();
                        foreach (DataRow defRow in dt_Definition.Rows)
                        {

                            Int32 begin = Int32.Parse(defRow["Begin"].ToString()) - 1;
                            Int32 lenght = Int32.Parse(defRow["Length"].ToString());
                            string ReportColunmName = defRow["ReportColunmName"].ToString().Trim();

                            string value = currentLine.Substring(begin, lenght).Trim();
                            newRow[ReportColunmName] = value;
                        }
                        retObj.Rows.Add(newRow);
                    }
                    else
                    {
                        //Skip header row
                        isFirstLine = false;
                    }
                }
            }

            catch (Exception e)
            {
                Console.WriteLine("General Exception: {0}", e);

            }
            return retObj;

        }