Send Email using smtp C#

public void SendEmail(string smtpClient_add, int port_no, string username, string password, string fromEmail, string fromname, string Email_subject, string Email_body, string toEmail, string ccEmail, string pathToAttachment)
    {
        MailMessage mail = new MailMessage();
        SmtpClient SmtpServer = new SmtpClient(smtpClient_add);

        mail.From = new MailAddress(fromEmail, fromname);
        mail.To.Add(toEmail);
		if (ccEmail != "")
        mail.CC.Add(ccEmail);
        mail.Subject = Email_subject;
        mail.Body = Email_body;
        mail.IsBodyHtml = true;
        if (pathToAttachment != "")
            mail.Attachments.Add(new Attachment(pathToAttachment));
        SmtpServer.Port = port_no;
        SmtpServer.Credentials = new System.Net.NetworkCredential(username, password);
        SmtpServer.EnableSsl = true;
        SmtpServer.Send(mail);
    }

Convert DataTable to HTML in C#

public static string ConvertDataTableToHTML(DataTable dt)
    {
        string html = "<table border='1' cellpadding='0' cellspacing='0'>";
        //add header row
        html += "<tr>";
        for (int i = 0; i < dt.Columns.Count; i++)
            html += "<th bgcolor='#507CD1' style='padding: 2px; color: #FFFFFF'>" + 
            dt.Columns[i].ColumnName + "</th>";
        html += "</tr>";

        //add rows
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            html += "<tr>";
            for (int j = 0; j < dt.Columns.Count; j++)           
            html += "<td style='padding: 2px;'>" + dt.Rows[i][j].ToString() + "</td>";                
        html += "</tr>";
        }
        html += "</table>";
        return html;
    }

Add Linked Servers using query

  • First, get all server name check if the server already linked or not

exec sp_linkedservers

  • Link server who has windows authentication

exec sp_addlinkedserver    @server=’SERVERNAME’

  • Link server who has login credentials

exec sp_addlinkedsrvlogin  ‘SERVERNAME’, ‘false’, null, ‘USERNAME’, ‘PASSWORD

Get All Database and Tables in SQL

DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR

SET @getDBName = CURSOR FOR

SELECT name
FROM sys.databases where name NOT IN (‘master’, ‘tempdb’, ‘model’, ‘msdb’)

CREATE TABLE #TmpTable (DBName VARCHAR(256),
SchemaName VARCHAR(256),
TableName VARCHAR(256))

OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = ‘USE ‘ + @DBName + ‘;
INSERT INTO #TmpTable
SELECT ”’+ @DBName + ”’ AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS TableName
FROM sys.tables’

EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END

CLOSE @getDBName
DEALLOCATE @getDBName
SELECT * FROM #TmpTable

Recover SQL statements

Recover MS SQL statements which get deleted/closed accidentally

SELECT
deqs.last_execution_time AS [Time],
dest.TEXT AS [Query]
FROM
sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID(‘YOURDATABASENAME’)
ORDER BY deqs.last_execution_time DESC

OR

SELECT
deqs.last_execution_time AS [Time],
dest.TEXT AS [Query]
FROM
sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY
deqs.last_execution_time DESC

Returns query with time.