Duplicate Records in SQL Server

see the record without duplicate value

select name,emp_id,City from MyTable

Count Duplicate Records

Syntax:

SELECT YourColumn, COUNT(*) TotalCount

FROM YourTable

GROUP BY YourColumn

HAVING COUNT(*) > 1

ORDER BY COUNT(*) DESC

Example:

SELECT name, 

 COUNT(email) 

FROM users

GROUP BY email

HAVING ( COUNT(email) > 1 )

Delete Duplicate Records – Rows multipul column

DELETE

FROM MyTable

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM MyTable

GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3 HAVING COUNT(*)>1))

Delete Duplicate Records – Rows Single column

DELETE

FROM MyTable

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM MyTable

GROUP BY DuplicateColumn HAVING COUNT(*)>1))

How to fetch data from Excel file to Data Set in ASP .Net

 protected void Page_Load(object sender, EventArgs e)
    {


        if (!IsPostBack)
        {
            GenerateExcelData();
        }
    }


private void GenerateExcelData()
    {
        try
        {
            OleDbConnection oledbConn;
            string path = System.IO.Path.GetFullPath(@"C:\Users\user\Desktop\PriceAnalysis.xls");
            oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
           path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
         
            oledbConn.Open();
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet2$]", oledbConn);
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            oleda.SelectCommand = cmd;
            DataSet ds = new DataSet();
            oleda.Fill(ds, "Table");
            //return ds.Tables[0];
        }
        catch
        {
            //return null;
        }
       

    }
}

Note : where Sheet2 is your sheet name.

How can use Split function in C#

I want split 2009-2010 in year format like: 2009

INPUT :   2009-2010  (financialYear)

OUTPUT: 2009


  char[] splitter1 = { '-' };
            string[] year1 = financialYear.Split(splitter1);
            int str1 = Convert.ToInt32(year1[0].ToString());
          
str1 give result: 2009

How to find list of recently execute store procedure

Select DB_Name(dest.[dbid]) As 'databaseName'
   , Object_Name(dest.objectid) As 'procName'
   , Max(deqs.last_execution_time) As 'last_execution'
From sys.dm_exec_query_stats As deqs
Cross Apply sys.dm_exec_sql_text(deqs.sql_handle) As dest

Group By db_name(dest.[dbid])
   , Object_Name(dest.objectid)
Order By databaseName
   , procName
Option (MaxDop 1);

how to show data in GridView - nice and simple code

 using dataset

add connection string in web config :

<connectionStrings>
<add name="ConnectionString" providerName="System.Data.SqlClient" connectionString="Data Source=USER-PC\SQLEXPRESS;Initial Catalog=master;User id= sa;Password=sa"/>
</connectionStrings>

in .cs page :
string str = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString(); SqlConnection con = new SqlConnection(str); con.Open(); string s = "select * from employee"; SqlDataAdapter adp = new SqlDataAdapter(s, con); DataSet ds = new DataSet(); adp.Fill(ds, "dataset"); GridView1.DataSource = ds; GridView1.DataBind(); con.Close();

or
SqlConnection con=new SqlConnection(<mention here your connectionstring>);
con.Open();
SqlCommand cmd=new SqlCommand("select * from sam",con);
\\where 'rohit' is table name
SqlDataReader dr=cmd.ExecuteReader();
DataTable dt=new DataTable();
dt.Load(dr);
GridView1.DataSourece=dt;
GridView1.DataBind();
con.Close();