Frequently asked sql query

-- Find Duplicate Record ----

select empname, count(empname) as Duplicate from employee

group by empname having (count (empname)>1) order by empname  desc

---- Find Second Highest Salary---

select min(salary) from employee where

salary in(select top 2 salary from employee order by salary desc )

----- Find Duplicate Name ----

SELECT empname

FROM employee

GROUP BY empname

HAVING ( COUNT(empname) = 1 )

--- Find Duplicate Name ---

SELECT empname, COUNT(*) TotalCount

FROM employee

GROUP BY empname

HAVING COUNT(*) > 1

ORDER BY COUNT(*) DESC

-- Find Duplicate -- 

SELECT empname, COUNT(*) TotalCount

FROM employee

GROUP BY empname

--- Select Duplicate ---

SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT min(AUTOID)

FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

--- Delete Duplicate --- 

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)

FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

Using Dispose method in C#

This peice of code will show you some memory management in classes. 

When we give application to client, we dont know how will he use that whether he will call dispose or not?

It may be missing at client side so our application should be smart enough to free used resources, here is code
which will give Idea to implement finalizer and dispose in same class to take care of resource cleaning.

using System;

namespace disposeConsole

{

          class ResourceManagement : IDisposable

          {

                   public ResourceManagement()

                   {}

                   private bool IsDisposed = false;

                   public void Free()

                   {

                             if (IsDisposed)

                                      throw new System.ObjectDisposedException("Object Name");

                   }

                   //Call Dispose to free resources explicitly

                   public void Dispose()

                   {

                             //Pass true in dispose method to clean managed resources too and say GC to skip finalize 
                             in next line.

                             Dispose(true);

                             //If dispose is called already then say GC to skip finalize on this instance.

                             GC.SuppressFinalize(this);

                   }

                   ~ResourceManagement()

                   {

                             //Pass false as param because no need to free managed resources when you call finalize it
                             will be done

                             //by GC itself as its work of finalize to manage managed resources.

                             Dispose(false);

                   }

                   //Implement dispose to free resources

                   protected virtual void Dispose(bool disposedStatus)

                   {

                             if (!IsDisposed)

                             {

                                      IsDisposed = true;

                                      // Released unmanaged Resources

                                      if (disposedStatus)

                                      {

                                                // Released managed Resources

                                      }

                             }

                   }

          }

} 

What is Managed code and unmanaged code in .NET

In this article I will try to explain you managed code and unmanaged code in .NET with help of diagram and its execution process.

.NET supports two kind of coding

Managed Code

Unmanaged Code

Managed Code

The resource, which is with in your application domain is, managed code. The resources that are within domain are faster.

The code, which is developed in .NET framework, is known as managed code. This code is directly executed by CLR with help of managed code execution. Any language that is written in .NET Framework is managed code.

Managed code uses CLR which in turns looks after your applications by managing memory, handling security, allowing cross - language debugging, and so on.

Unmanaged Code

The code, which is developed outside .NET, Framework is known as unmanaged code.

Applications that do not run under the control of the CLR are said to be unmanaged, and certain languages such as C++ can be used to write such applications, which, for example, access low - level functions of the operating system. Background compatibility with code of VB, ASP and COM are examples of unmanaged code.

Unmanaged code can be unmanaged source code and unmanaged compile code.

Unmanaged code is executed with help of wrapper classes.

Wrapper classes are of two types: CCW (COM Callable Wrapper) and RCW (Runtime Callable Wrapper).

Wrapper is used to cover difference with the help of CCW and RCW.

COM callable wrapper unmanaged code execution



Runtime Callable Wrapper unmanaged code execution

I hope that this article would have helped you in understanding managed and unmanaged .NET.

Data inserted in database and send it in mail to user after user has registered

//Inserting registration data of user!!!

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["RegConnectionString"].ConnectionString);

string inscmd = "Insert into Registration(Username, Password,EmailAddress,FullName,CNIC,city) Values(@UserName, @Password, @EmailAddress, @FullName, @CNIC, @City)";

SqlCommand InsertUser = new SqlCommand(inscmd, con);

InsertUser.Parameters.AddWithValue("@UserName", TextBoxUN.Text);

InsertUser.Parameters.AddWithValue("@Password", TextBoxPass.Text);

InsertUser.Parameters.AddWithValue("@EmailAddress", TextBoxEA.Text);

InsertUser.Parameters.AddWithValue("@FullName", TextBoxFN.Text);

InsertUser.Parameters.AddWithValue("@CNIC", TextBoxCNIC.Text);

InsertUser.Parameters.AddWithValue("@City", DropDownListCity.SelectedItem.ToString());

try

{

    con.Open();

    //Response.Write("Trying ...");

    InsertUser.ExecuteNonQuery();

    con.Close();

}

catch(SqlException ex)

{

    Response.Write(ex.Message);

    Response.Write("Failed!!! ...");

    Response.Write("<b>Something really bad happened .. try again later</b>");

}

//send mail message after user fills registration form

try               

{

    MailMessage msg = new MailMessage();

    msg.From = new MailAddress("pankaj.ics@gmail.com");

    msg.To.Add(TextBoxEA.Text);

    msg.Subject = "Your Registration is confirmed! ";

    msg.Body = "Dear " + TextBoxFN.Text + " Your Registration to motion voter system has been confirmed. .. Kindly note down your Voter's Identity Number(VIN) required for your login";

    SmtpClient Sc = new SmtpClient("smtp.gmail.com");

    Sc.Port = 587;

    Sc.Credentials = new NetworkCredential("pankaj.ics@gmail.com","password");

    Sc.EnableSsl = true;

    Sc.Send(msg);

    Response.Write("Sent!!! ... ");

    Response.Redirect("Login.aspx");

}

catch (Exception ex)

{

    Response.Write(ex.Message);

}