Static class VS Sealed class

1. Static and sealed classes cannot be inherited.
2. Static cannot be instantiated and but sealed class can be instantiated.
3. In Static class, only static members are allowed.
4. Sealed class will behave as a normal class, we can create instance, but we cannot inherit further.

Nth max salary

declare @N int
set @N = 2

SELECT Salary FROM dbo.Employee E1
WHERE @N = (SELECT Count(Salary) FROM dbo.Employee E2 WHERE E1.Salary <= E2.Salary)

USING Joins:

SELECT A.Salary, count(1) FROM dbo.Employee A
LEFT OUTER JOIN dbo.Employee B ON A.Salary <= B.Salary
GROUP BY A.Salary
HAVING count(1) = 1

Sorting a LIST (LINQ)

<ListName>.Sort((x, y) => string.Compare(x.<ColumnName>, y.<ColumnName>));

Give permission to all stored procedures for a SQL user

use < DatabaseName>
Declare @userName varchar(50)
set @userName = < UserName>

declare cur cursor FAST_FORWARD for
select name from sysobjects where xtype = 'P' and category <> 2

declare @spName varchar(255)

open cur
fetch next from cur into @spName

WHILE @@FETCH_STATUS = 0
BEGIN
exec('GRANT EXECUTE ON [dbo].[' + @spName + '] TO ' + @userName + '')
fetch next from cur into @spName
end

close cur
deallocate cur


SELECT 'Given exec permision to all procedures for the user ' + @userName

--

Web application reviewer need to concentrate

While reviewing the code... Some of the points we need to have it in mind, which I have listed below.

1. Checking for un-used reference
2. Removing unused namespace
3. Have logged all the errors
4. Error page for web application
5. If there is any static variable, note the purpose of it
6. If you find replicated code, try to move it to a separate function
7. Coding-Standard (Camel case – Pascal case)
8. Not to have multiple class in a single file
9. Use built-in C# data type (avoid using System.String instead of string)
10. NameSpace need to be as the Project folder structure
11. Better to implement the design pattern as much as possible
12. Code alignment
13. Covered test case
14. Commented wherever there is some complexity logic
15. Avoid in-line style (use only css classes)
16. Avoid specifying the javascript in the page level, instead of that use js (javascript) file

Some tools to check coding standard and test case.
1. StyleCop (To check the coding standard in the Microsoft standard)
2. Re-sharper (Not free-ware. There is a trail version)
3. N-Unit or MSTest for checking unit test cases
4. FX-Cop for coding standard

Some tools which are all useful for web application:
1. Fiddler (HTTP debugging)
2. FireBug (Analyze a web-page)
3. Codesmith (Not free-ware) – to generate database code automatically with the table schema and also database script.

Unicode to Codepage

Unicode to Codepage in SSIS package:

In SSIS package, for converting from Unicode to Codepage, follow the following steps:



1. Source table (Contains Unicode data)
2. Script component
3. Data Conversion
2. Destination table (Contains Codepage data)

For converting Unicode to Codepage

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim codePageNumber As Integer = System.Text.Encoding.GetEncoding(Row.charset).CodePage
Dim unicodeBytes As Byte() = Row.unicodecolumn.GetBlobData(0, CInt(Row.unicodecolumn.Length))
Dim codepagebytes As Byte() = System.Text.Encoding.Convert(System.Text.Encoding.Unicode, System.Text.Encoding.GetEncoding(codePageNumber), unicodeBytes)

Row.codepagecolumn.AddBlobData(codepagebytes)
End Sub

Note:
UnicodeColumn - Unicode data which will be in DT_NTEXT
CodepageColumn - Codepage data which need to be in DT_TEXT

Declare CodePageColumn as output parameter and set the datatype as DT_TEXT.

Then map it to Destination table where codepage need to be stored.

If you want to converrt it again to DT_NTEXT, you can do this using DATA Conversion.

Hope this will help you.

Start/Stop Windows Services from ASP.Net 2.0

Need to add the reference System.ServiceProcess
Imports System.ServiceProcess

Dim sc As New ServiceController()
sc.ServiceName = "W3SVC"
Console.WriteLine("The Alerter service status is currently set to {0}", sc.Status)
If sc.Status.Equals(ServiceControllerStatus.Stopped) Then
sc.Start()
Else
sc.Stop()
End If

We can do all related to the service controller