Finding and deleting duplicate records in sql server

FINDING DUPLICATE RECORDS
SELECT COLUMNNAME, COUNT(*) TOTALCOUNT
FROM TABLENAME
GROUP BY COLUMNNAME
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

DELETING THE DUPLICATE RECORDS
DELETE
FROM TABLE
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TABLENAME
GROUP BY COLUMNNAME
)

How to add charts in asp.net mvc

Using System.Web.Helpers;

public ActionResult ShowChart()
{
var key = new Chart(width: 600, height: 400)
.AddSeries(
chartType: “bar”,
legend: “Rainfall”,
xValue: new[] { “Jan”, “Feb”, “Mar”, “Apr”, “May” },
yValues: new[] { “20”, “20”, “40”, “10”, “10” })
.Write();

return null;
}

Note : Above you can use line,bar,pie,stock,column types

How to install sql server 2008

Visit this link,

http://www.sqlserverclub.com/essentialguides/how-to-install-sql-server-2008-step-by-step-guide.aspx

Split comma separated string in sql server

 

DECLARE @Num INT,@Pos INT, @NextPos INT, @ToProfileIds NVARCHAR(MAX)
SET @ToProfileIds = ‘sainath,sagar,gopi,ad’
SET @Pos = 1
WHILE(@Pos <= LEN(@ToProfileIds))
BEGIN
SELECT @NextPos = CHARINDEX(N’,’, @ToProfileIds, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@ToProfileIds) + 1
insert into #tmptable
SELECT RTRIM(LTRIM(SUBSTRING(@ToProfileIds, @Pos, @NextPos – @Pos)))
SELECT @Pos = @NextPos+1
END

Prevent a member to be visible in debug window

Just set,

[DebuggerBrowsable(DebuggerBrowsableState.Never)]
private string _myPrivateMember = “value”;

Enumerate event log entries

Import the following namespace:

using System.Diagnostics;
Here, is the code to check

bool exists = EventLog.Exists(“Application”);

if(exists == true)
{
EventLog log = new EventLog(“Application”);

foreach (EventLogEntry entry in log.Entries)
{
Console.WriteLine(“Type: {0}”, entry.EntryType);
Console.WriteLine(“Source: {0}”, entry.Source);
Console.WriteLine(“Written: {0}”, entry.TimeWritten);
Console.WriteLine(“Message: {0}”, entry.Message);
}
}

How to get command line arguments as array

string[] arguments = Environment.GetCommandLineArgs();
or
static void Main(string[] args)
{
foreach (string arguments in args)
{
Console.WriteLine(arguments);
}
}

How to get image encoder/decoder by image format

public static ImageCodecInfo GetImageEncoder(ImageFormat format)
{
return ImageCodecInfo.GetImageEncoders().ToList().Find(delegate(ImageCodecInfo codec)
{
return codec.FormatID == format.Guid;
});
}

public static ImageCodecInfo GetImageDecoder(ImageFormat format)
{
return ImageCodecInfo.GetImageDecoders().ToList().Find(delegate(ImageCodecInfo codec)
{
return codec.FormatID == format.Guid;
});
}
Use
ImageCodecInfo gifEncoder = GetImageEncoder(ImageFormat.Gif);
ImageCodecInfo bmpDecoder = GetImageDecoder(ImageFormat.Bmp);

Loop through/split a delimited string in SQL Server 2008

CREATE TABLE #temp (names NARCHAR(40))

DECLARE @names NVARCHAR(MAX)
SET @names = ‘,sainath,sagar,saikumar,saisaar,’
DECLARE @IND INT
SET @IND = CHARINDEX(‘,’,@names)
DECLARE @EIND INT set @EIND = 0
WHILE(@IND != LEN(@names))
BEGIN
SET @EIND = ISNULL(((CHARINDEX(‘,’, @names, @IND + 1)) – @IND – 1), 0)
INSERT INTO #temp
SELECT (SUBSTRING(@names, (@IND + 1), @EIND)) as names
SELECT @IND = ISNULL(CHARINDEX(‘,’, @names, @IND + 1), 0)
END

SELECT *FROM #temp

convert text data to numeric with the sign

CREATE Function TextDataWithSigns(@Column text)
returns numeric(4,2)
as
Begin
Declare @Count int
Declare @returncolumn varchar(100)
Declare @leng int
Declare @search int
set @Count =Count(*)
while(@Count>0)
Begin
set @returncolumn = cast(@Column as varchar(100))
set @search = patindex(‘%-%’,@Column)
if @search != 0
Begin
set @leng =len(@returncolumn)
set @returncolumn = substring(@returncolumn,1,@search-1)
set @returncolumn = ‘-’+ @returncolumn
set @returncolumn = cast(@returncolumn as numeric(4,2))
set @count =@count-1
End
Else
set @returncolumn = cast(@returncolumn as numeric(4,2))
set @count =@count-1
End
return @returncolumn
End