Thursday, July 18, 2013

Delete Duplicate Records – SQL

If there is an identity and unique column is available

Way 1:-

 WITH cte(ID, RankField)
AS (SELECT Id
, RankField = DENSE_RANK()
OVER (
PARTITION BY ID
ORDER BY newID())
FROM
Temp_ACTIVE)
DELETE FROM cte
WHERE RankField > 1

Way 2:-

/* Create Table with 7 entries - 3 are duplicate entries */
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO

/* It should give you 7 rows */
SELECT *
FROM DuplicateRcordTable
GO

/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
--DELETE
Select *
FROM CTE
WHERE DuplicateCount > 1
GO

/* It should give you Distinct 4 records */
SELECT *
FROM DuplicateRcordTable
GO

-----------------------------------

If thers is an unique column exists

-- Deleting Duplicate
DELETE
FROM
TestTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TestTable
GROUP BY NameCol)
GO

Wednesday, July 10, 2013

Send Email using password credantial

    private static void SendSMTPMail(string FromEmail, string maskemail, string ToEmail, string SubjectText, string MessageText)
    {
        string Domain = System.Configuration.ConfigurationManager.AppSettings["Domain"].ToString();
        string DomainUserName = System.Configuration.ConfigurationManager.AppSettings["DomainUserName"].ToString();
        string DomainPassword = System.Configuration.ConfigurationManager.AppSettings["DomainPassword"].ToString();
        string BCC = System.Configuration.ConfigurationManager.AppSettings["BCC"].ToString();

        System.Net.Mail.MailMessage MailObject = new System.Net.Mail.MailMessage();

        System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient(Domain);
        MailObject.From = new System.Net.Mail.MailAddress(FromEmail, maskemail);
        MailObject.To.Add(ToEmail);
        MailObject.Bcc.Add(BCC);

        NetworkCredential basicAuthenticationInfo = new NetworkCredential(DomainUserName, DomainPassword);

        MailObject.Subject = SubjectText;
        MailObject.IsBodyHtml = true;
        MailObject.Body = "" + MessageText + "";
        MailObject.DeliveryNotificationOptions = System.Net.Mail.DeliveryNotificationOptions.OnFailure;
        //smtp.EnableSsl = true;
        smtp.UseDefaultCredentials = false;
        smtp.Credentials = basicAuthenticationInfo;
        smtp.Send(MailObject);
    }

Monday, July 8, 2013

Get the value of checked checkbox?

var boxes = document.getElementsByName('<%= rdlIsMobile.ClientID %>');
            var val = '';
            var len = boxes.length;
            for (var i = 0; i < len; i++) {
                if (boxes[i].checked) {
                    flag = true;
                    val = boxes[i].value;
                    break;
                }
                //alert(i + (boxes[i].checked ? ' checked ' : ' unchecked ') + boxes[i].value);
            }