ASHIS KUMAR DAS

75 Important queries in SQL Server every developer should know

2018-04-15T13:50:00.001+05:30

In this article we will learn about some of the mostly used SQL Server queries every developer should know. These queries can be asked you as an Interview Question or they are handy for you in your day to day tasks.

1. Create Table:

In this SQL Server query we will learn How to create table in SQL Server.

CREATE TABLE TableName

(

Id INT,

Name Nvarchar(500),

Age INT

)

2. Create table with primary key:

In this query we will Create SQL Server table with primary key.

CREATE TABLE TableName

(

Id INT PRIMARY KEY,

Name Nvarchar(500),

Age INT

)

3. Create table with primary key and Identity Column:

CREATE TABLE TableName

(

Id INT IDENTITY(1,1) PRIMARY KEY,

Name Nvarchar(500),

Age INT

)

4. Insert values into SQL Server table:

In this SQL Server query we will learn about How to insert values into Sql Server table.

1	INSERT INTO TableName (Name,Age) VALUES ('Max',30);

5. Insert multiple rows in a single query:

In this Sql query we will learn How to Insert Multiple Rows in a Single SQL Query. This query is very important and generally asked in .Net Interview questions.

1 2	INSERT INTO TableName (Name,Age) VALUES ('Max',30),('John',28),('Jack',31);

6. Update query:

Update single record:

In this Sql query we will update single record from a table.

1	UPDATE TableName SET NAME='Max Payne' WHERE Id=1

Update all records:

In this Sql query we will update all records within a table.

1	UPDATE TableName SET AGE=31

7. Delete query:

Delete single record:

In this Sql query we will delete single record from a table.

1	DELETE FROM TableName WHERE Id=1

Delete multiple records:

In this Sql query we will delete all records from a table.

1	DELETE FROM TableName

8. Select:

Select all columns from a tables:

In this Sql query we will select all columns from a table.

1	SELECT * FROM TableName

Select specific columns:

In this Sql query we will select specific columns from a table.

1	SELECT columnName1,ColumnName2 from TableName

9. Create View:

A view is a virtual table created based on the result generated by SQL statement. Fields in a view are directly related to one of more tables from database.

1 2	CREATE VIEW view_name AS SELECT Id,Name,Age FROM TableName

Usage:

1	select * From view_name

10. Create Stored procedure:

In this query we will learn about How to create stored procedure in Sql Server.

CREATE PROCEDURE getInfoFromTable

SELECT * FROM TableName

11. Get all column names from table:

In this query we will learn about How to get all column names from Sql Server table.

1 2	select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='TableName';

Get column names with data type:

1 2	select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='TableName';

12. Search column name from database:

In this query we will learn about How to search any column name from Sql Server database. This query will look out for matching column names in database.

SELECT c.name AS ColName, t.name AS TableName

FROM sys.columns c

JOIN sys.tables t ON c.object_id = t.object_id

WHERE c.name LIKE '%columnNameToSearch%'

13. Get all user created tables:

In this query we will learn about How to get names of all user created tables in Sql Server.

1	SELECT NAME FROM sys.objects WHERE TYPE='U'

14. Get all view names:

In this query we will learn about How to get names of all views in Sql Server.

1	SELECT * FROM sys.views

15. Get all stored procedure names:

In this query we will learn about How to get names of all stored procedure in Sql Server.

1	SELECT * FROM sys.procedures

16. Counting rows for all tables at once:

In this query we will learn about How to fetch the row count for all tables in a SQL SERVER database. This query is very usefull when we want to know how many records are present in tables.

SELECT

[TableName] = so.name,

[RowCount] = MAX(si.rows)

FROM

sysobjects so,

sysindexes si

WHERE

so.xtype = 'U'

AND

si.id = OBJECT_ID(so.name)

GROUP BY

so.name

ORDER BY

2 DESC

17. Get Comma Separated List of all columns in table:

In this query we will learn about How to get Comma Separated List of all columns in table.

Select TABLE_SCHEMA, TABLE_NAME

, Stuff(

(

Select ',' + C.COLUMN_NAME

From INFORMATION_SCHEMA.COLUMNS As C

Where C.TABLE_SCHEMA = T.TABLE_SCHEMA

And C.TABLE_NAME = T.TABLE_NAME

Order By C.ORDINAL_POSITION

For Xml Path('')

), 1, 1, '') As Columns

From INFORMATION_SCHEMA.TABLES As T

WHERE T.TABLE_NAME='TableName'

18. Generate ADO .net parameters:

In this query we will learn about How to Generate ADO .net SqlCommand parameters in Sql Server. Fellows who have worked on asp .net webforms and ADO .net must know the pain of generating parameters for SqlCommand object for large tables. I have written this query to save my time and generate parameters on the fly. This is one of my favourate SQL Query.

1 2	SELECT 'cmd.Parameters.AddWithValue("@'+COLUMN_NAME+'", '+COLUMN_NAME+');' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName';

19. Retrieve List of All Database names:

In this query we will learn about How to get list of all database names in Sql Server.

1	SELECT Name FROM dbo.sysdatabases

1	EXEC sp_databases

20. Check default Language of SQL Server:

In this query we will learn about How to Check default Language of SQL Server.

1	select @@language AS DefaultLanguage

21. Check server name:

In this query we will learn about How to Check SQL Server instance name.

1	select @@SERVERNAME AS ServerName

22. Add columns to existing table:

In this query we will learn about How to add new column to existing SQL Server table.

Syntax

1 2	ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE}

Usage

1 2	ALTER TABLE TableName ADD Email NVARCHAR(50)

23. Remove column from a table:

In this query we will learn about How to remove column from SQL Server Table.

1 2	ALTER TABLE TableName DROP COLUMN Email

24. Check column having NULL values only from a table:

In this query we will learn about How to check if any column contains only NULL values in SQL Server Table.

DECLARE @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for

SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID

WHERE t.Name = 'TableName'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM TableName WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'

EXEC(@cmd)

FETCH NEXT FROM getinfo into @col

END

CLOSE getinfo

DEALLOCATE getinfo

25. Get list of tables without primary key:

In this query we will learn about How to get list of tables without primary key in SQL Server.

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName

FROM sys.tables

WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0

ORDER BY SchemaName, TableName;

26. Get list of tables without having identity column:

In this query we will learn about How to get list of tables without having identity column in SQL Server.

SELECT

TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE

Table_NAME NOT IN

(

SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c

INNER

JOIN sys.identity_columns ic

(c.COLUMN_NAME=ic.NAME))

AND

TABLE_TYPE ='BASE TABLE'

27. Get month names with month numbers:

In this query we will learn about How to get all month names with month number in SQL Server. I have used this query to bind my dropdownlist with month name and month number.

;WITH months(MonthNumber) AS

(

SELECT 0

UNION ALL

SELECT MonthNumber+1

FROM months

WHERE MonthNumber < 12

)

SELECT DATENAME(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())) AS [MonthName],Datepart(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())) AS MonthNumber

FROM months

ORDER BY Datepart(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE()))

;

28. Get name of current month:

In this query we will learn about How to get name of current month in SQL Server.

1	select DATENAME(MONTH, GETDATE()) AS CurrentMonth

29. Get name of day:

In this query we will learn about How to get name of day in SQL Server.

1	select DATENAME(WEEKDAY, GETDATE()) AS TodayIs

30. Get first date of current month:

In this query we will learn about How to first date of current month in SQL Server.

1	SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),105) FirstDate;

31. Get last date of current month:

In this query we will learn about How to last date of current month in SQL Server.

1	SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())),105) LastDate;

32. Get first date of previous month:

In this query we will learn about How to first date of previous month in SQL Server.

1	select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) FirstDayOfPreviousMonth

33. Get last date of previous month:

In this query we will learn about How to last date of previous month in SQL Server.

1	select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) LastDayOfPreviousMonth

34. Get first date of current year:

In this query we will learn about How to first date of current year in SQL Server.

1	SELECT dateadd(year,datediff(year,0,getdate()),0) FirstDateOfYear

35. Get last date of current year:

In this query we will learn about How to last date of current year in SQL Server.

1	SELECT dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))) LasteDateOfYear

36. Select date without time:

In this query we will learn about How to select date without time in SQL Server.

1	SELECT CONVERT(date, getdate()) AS DateWithoutTime

37. Select records within two dates:

In this query we will learn about How to select records within two dates in SQL Server.

1	SELECT * FROM EMPLOYEE WHERE CONVERT(date, CreatedOn) BETWEEN '1/1/2015' AND '12/31/2016'

Note: Here Employee is my table name and CreatedOn is a name of date column used for filtering.

38. Calculate age from birth date:

In this query we will learn about How to calculate age from birth date in SQL Server.

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int

SELECT @date = '05/05/1989'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)

SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(m, @months, @tmpdate)

SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years AS Years, @months AS Months, @days AS Days

39. Get days between two dates:

In this query we will learn about How to get days between two dates in SQL Server.

1	SELECT DATEDIFF(DAY, '1/1/2016', '3/1/2016') DaysDifference

40. Truncate all tables using single query:

In this query we will learn about How to truncate all database tables using single query in SQL Server.

1	EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

Note: Please take proper backup before running this query. This query will remove all data from all tables in selected database.

41. Delete all records from all tables having foreign keys:

In this query we will learn about How to delete all rows from all tables in SQL Server which have foreign keys.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'DELETE FROM ?'

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

42. Drop all tables:

In this query we will learn about How to drop all tables with single query in SQL Server.

1	EXEC sys.sp_MSforeachtable @command1 = 'Drop Table ?'

43. Reset identity seed after deleting records in SQL Server:

In this query we will learn about How to Reset identity seed after deleting records in SQL Server.

1	DBCC CHECKIDENT ('[TableName]', RESEED, 0);

Note:Make sure to delete all records before using this query.

44. Select random rows:

In this query we will learn about How to Select random rows from SQL Server table.

Select N random Rows:

1	SELECT * FROM TableName ORDER BY NEWID()

Select N percent of random Rows:

1	SELECT TOP 5 PERCENT * FROM TableName ORDER BY NEWID()

45. Swap values of 2 columns:

In this query we will learn about How to swap values of two columns in SQL Server.

Format:

1	UPDATE TableName SET Column1=Column2, Column2=Column1

Usage:

1	UPDATE TableName SET Name=Email,Email=Name

46. Rename Database:

In this query we will learn about How to rename database in SQL Server.

1	EXEC sp_renamedb 'oldName', 'newName'

1	ALTER DATABASE oldName MODIFY NAME = newName

47. Rename Table:

In this query we will learn about How to rename Table in SQL Server.

1	EXEC sp_rename 'OldTableName', 'NewTableName'

48. Rename Table Column:

In this query we will learn about How to rename Table Column in SQL Server.

1	EXEC sp_rename 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

49. Check SQL Server version:

In this query we will learn about How to check version of SQL Server.

1	SELECT @@version

50. Get list of hard drives with free space:

In this query we will learn about How to get list of system hard drives with available free space using SQL Server.

1	EXEC master..xp_fixeddrives

51. Get Id of latest inserted record:

In this query we will learn about How to Id of latest inserted record in SQL Server.

INSERT INTO TableName (NAME,Email,Age) VALUES ('Test User','test@gmail.com',30)

-- Get Id of latest inserted record

SELECT SCOPE_IDENTITY()

52. Delete duplicate records:

In this query we will learn about How to delete duplicate records in SQL Server.

DELETE

FROM TableName

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM TableName

GROUP BY DuplicateColumn)

53. Display Text of Stored Procedure, Trigger, View :

In this query we will learn about How to Display Text of Stored Procedure, Trigger, View in SQL Server.

1 2	exec sp_helptext @objname = 'getInfoFromTable' -- Here getInfoFromTable is my storedprocedure name

54. Get List of Primary Key and Foreign Key for a particular table :

In this query we will learn about How to get list of Primary Key and Foreign Key for a particular table in SQL Server.

SELECT DISTINCT

Constraint_Name AS [ConstraintName],

Table_Schema AS [Schema],

Table_Name AS [Table Name] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='TableName'

55. Get List of Primary Key and Foreign Key of entire database:

In this query we will learn about How to get list of Primary Key and Foreign Key for a entire database in SQL Server.

SELECT DISTINCT

Constraint_Name AS [ConstraintName],

Table_Schema AS [Schema],

Table_Name AS [Table Name] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

56. Disable Trigger:

In this query we will learn about How to disable trigger in SQL Server.

Disable particular trigger

1	ALTER TABLE TableName DISABLE TRIGGER TriggerName

Disable all triggers

1	ALTER TABLE TableName DISABLE TRIGGER ALL

57. Enable Trigger:

In this query we will learn about How to enable trigger in SQL Server.

Enable particular trigger

1	ALTER TABLE TableName ENABBLE TRIGGER TriggerName

Enable all triggers

1	ALTER TABLE TableName ENABLE TRIGGER ALL

58. Make database offline or online :

In this query we will learn about How to make database offline or online in SQL Server.

Make database offline

1	ALTER DATABASE [ForTesting] SET OFFLINE

Make Database online

1	ALTER DATABASE [ForTesting] SET ONLINE

Note: Make sure to select any other database while using this query.

59. Backup Database :

In this query we will learn about How to take backup of database using script in SQL Server.

1	BACKUP DATABASE DataBaseName TO DISK='d:\NameOfBackupFile.bak'

60. Restore Database :

In this query we will learn about How to restore database using script in SQL Server.

1	RESTORE DATABASE DataBaseName TO DISK='d:\NameOfBackupFile.bak'

61. Search string in a single table :

In this query we will learn about How to search string within a table in SQL Server.

1	SELECT * FROM TableName WHERE Name LIKE '%TextToSearch%'

62. Search string in all tables :

In this query we will learn about How to search string in all tables in SQL Server.

DECLARE @SearchStr nvarchar(100)='textToSearch'

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ''

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

), 'IsMSShipped'

) = 0

)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

AND TABLE_NAME = PARSENAME(@TableName, 1)

AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')

AND QUOTENAME(COLUMN_NAME) > @ColumnName

)

IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results

EXEC

(

'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

FROM ' + @TableName + 'WITH (NOLOCK) ' +

' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

)

END

SELECT ColumnName, ColumnValue FROM #Results

63. Replace a string in a SQL Server Table :

In this query we will learn about How to replace a string in a SQL Server Table Column.

1	UPDATE TableName SET ColumnName = REPLACE(ColumnName, 'OldValue', 'NewValue')

64. Left Outer Join :

In this query we will learn about How to use Left outer join in SQL Server.

SELECT * FROM Table1 as t1

LEFT OUTER JOIN Table2 as t2

on t1.PrimaryColumn=t2.ForeignKeyColumn

65. Right Outer Join :

In this query we will learn about How to use Right outer join in SQL Server.

SELECT * FROM Table1 as t1

RIGHT OUTER JOIN Table2 as t2

on t1.PrimaryColumn=t2.ForeignKeyColumn

66. Full Outer Join :

In this query we will learn about How to use Full outer join in SQL Server.

SELECT * FROM Table1 as t1

FULL OUTER JOIN Table2 as t2

on t1.PrimaryColumn=t2.ForeignKeyColumn

67. Inner Join :

In this query we will learn about How to use Inner join in SQL Server.

SELECT * FROM Table1 as t1

INNER JOIN Table2 as t2

on t1.PrimaryColumn=t2.ForeignKeyColumn

68. Cross Join :

In this query we will learn about How to use Cross join in SQL Server.

1 2	SELECT * FROM Table1 CROSS JOIN Table2

69. IF ELSE :

In this query we will learn about How to use IF ELSE Statements in SQL Server.

DECLARE @ValueToCheck INT;

SET @ValueToCheck = 14;

IF @ValueToCheck=15

SELECT 'Value is 15' As Result

ELSE IF @ValueToCheck<15

SELECT 'Value is Less than 15' As Result

ELSE IF @ValueToCheck>15

SELECT 'Value is greater than 15' As Result

70. CASE Expression :

In this query we will learn about How to CASE Expression in SQL Server.

SELECT CASE Name

WHEN 'max' THEN 'Name is max'

WHEN 'jack' THEN 'Name is max'

ELSE ' You have selected other user'

END AS Result

FROM TableName

71. Select Second highest salary :

In this query we will learn about How to select second highest salary in SQL Server.

1	SELECT MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );

72. Paging Example :

In this query we will learn about How to Implement paging in SQL Server.

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) AS RowNum,* FROM Country)as Result

WHERE RowNum>=20 AND RowNum<=40

ORDER BY RowNum

73. Temporary Tables Example :

In this query we will learn about How to use temporary tables in SQL Server.

DECLARE @TempTable TABLE

(

Id int primary key,

Name varchar(50),

Email varchar(50)

)

INSERT INTO @TempTable VALUES (1,'Max','max@gmail.com'),

(2,'Tony','Tony@gmail.com'),

(3,'Jack','Jack@Indivar.com')

SELECT * FROM @TempTable

74. Delete duplicate rows :

In this query we will learn about How to delete duplicate rows in SQL Server.

DELETE FROM TableName

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM TableName

GROUP BY DuplicateColumn1, DuplicateColumn2)

75. Reset Identity Seed :

In this query we will learn about How to Reset identity seed after deleting records in SQL Server.

1	DBCC CHECKIDENT ('[YourTableName]', RESEED, 0);

Note: Here 0 is the number-1 from where you want to start your auto increment. Suppose you wants to start auto increment your primary key value from 50 then you need to pass 49 in the above method.

Difference between var and dynamic in C#

2015-10-05T15:13:00.000+05:30

var	dynamic
Introduced in C# 3.0	Introduced in C# 4.0
Statically typed – This means the type of variable declared is decided by the compiler at compile time.	Dynamically typed - This means the type of variable declared is decided by the compiler at runtime time.
Need to initialize at the time of declaration. e.g., `var str=”I am a string”;` Looking at the value assigned to the variable `str`, the compiler will treat the variable `str` as string.	No need to initialize at the time of declaration. e.g., `dynamic str;` `str=”I am a string”;` //Works fine and compiles `str=2;` //Works fine and compiles
Errors are caught at compile time. Since the compiler knows about the type and the methods and properties of the type at the compile time itself	Errors are caught at runtime Since the compiler comes to about the type and the methods and properties of the type at the run time.
Visual Studio shows intellisense since the type of variable assigned is known to compiler.	Intellisense is not available since the type and its related methods and properties can be known at run time only
e.g., `var obj1;` will throw a compile error since the variable is not initialized. The compiler needs that this variable should be initialized so that it can infer a type from the value.	e.g., `dynamic obj1;` will compile;
e.g. `var obj1=1;` will compile `var obj1=” I am a string”;` will throw error since the compiler has already decided that the type of obj1 is System.Int32 when the value 1 was assigned to it. Now assigning a string value to it violates the type safety.	e.g. `dynamic obj1=1;` will compile and run `dynamic obj1=” I am a string”;` will compile and run since the compiler creates the type for obj1 as System.Int32 and then recreates the type as string when the value “I am a string” was assigned to it. This code will work fine.

Last modified time of a file reside in FTP server

2015-04-08T15:59:00.001+05:30

Add reference in your class:
using System.Net;
Then copy below mentioned method
/// <summary>
        /// Get Server File TimeStamp
        /// </summary>
        /// <param name="serverPath"></param>
        /// <param name="userId"></param>
        /// <param name="password"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        ///
        public static DateTime GetServerFileTimestamp(string serverPath, string userId, string password, string fileName)
        {
            // Get the object used to communicate with the server.
            String serverUrl = String.Format("{0}{1}/{2}", @"ftp:", serverPath, fileName);
            Uri serverUri = new Uri(serverUrl);
            FtpWebRequest reqFTP = (FtpWebRequest)WebRequest.Create(serverUrl);
            reqFTP.Method = WebRequestMethods.Ftp.GetDateTimestamp;
            reqFTP.Credentials = new NetworkCredential(userId, password);
            try
            {
                using (FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse())
                {
                    // Return the last modified time.
                    return response.LastModified;
                }
            }
            catch (Exception ex)
            {
                // If the file doesn't exist, return min date Otherwise rethrow the error.
                if (ex.Message.Contains("File unavailable"))
                    return DateTime.MinValue;
                throw;
            }
            finally
            {
                reqFTP = null;
            }
        }

Above mentioned code will return you last modified time.

Happy learning.............

Check ftp server is available or not

2015-04-08T15:56:00.001+05:30

Add reference in your class:
using System.Net;
Then copy below mentioned method
/// <summary>
        /// GetServerAvailability to check ftp server is available or not
        /// </summary>
        /// <param name="userId">userId</param>
        /// <param name="password">password</param>
        /// <param name="ftpServerPath">ftpServerPath</param>
        /// <returns>status</returns>
        public static bool GetServerAvailability(string userId, string password, string ftpServerPath)
        {
            FtpWebRequest reqFTP;
            bool isServerConnected = false;
            try
            {
                String serverUrl = String.Format("{0}{1}", @"ftp:", ftpServerPath);
                reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(serverUrl));
                reqFTP.UseBinary = true;
                reqFTP.Credentials = new NetworkCredential(userId, password);
                reqFTP.Method = WebRequestMethods.Ftp.ListDirectory;
                reqFTP.Proxy = null;
                reqFTP.KeepAlive = false;
                using (WebResponse response = reqFTP.GetResponse())
                {
                    isServerConnected = true;
                }

            }
            catch (Exception ex)
            {
                if (string.Compare(ex.GetType().ToString(), "System.Net.Sockets.SocketException", true) == 0)
                {
                    System.Net.Sockets.SocketException socketError = (System.Net.Sockets.SocketException)ex;
                    if (socketError.ErrorCode == 11004)
                        isServerConnected = false;
                }
                else
                {
                    isServerConnected = false;
                }
            }
            finally
            {
                reqFTP = null;
            }
            return isServerConnected;
        }

Above mentioned code will return you true if server is available else false.

Upload file into FTP server

2015-04-08T15:52:00.001+05:30

Add 2 reference in your class:
using System.Net;
using System.IO;

Then copy below mentioned method

/// <summary>
        /// Upload Design File into FTP server
        /// </summary>
        /// <param name="serverPath"></param>
        /// <param name="localPath"></param>
        /// <param name="fileName"></param>
        /// <param name="settings"></param>
        /// <returns></returns>
        public static bool UploadDesignFile(string serverPath, string localPath, string fileName,Dictionary<string,string> credentials)
        {
            bool status = false;

            FtpWebRequest reqFTP;
            int bufferSize = 2048;
            try
            {
                String uploadUrl = String.Format("{0}{1}/{2}", @"ftp:", serverPath, fileName);
                reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(uploadUrl));
                 string username = string.Empty;
                credentials.TryGetValue("UserId",out username);
                string passwd = string.Empty;
                credentials.TryGetValue("Password",out passwd);
                reqFTP.Credentials = new NetworkCredential(Decrypt(username), Decrypt(passwd));
                reqFTP.UseBinary = true;
                reqFTP.UsePassive = true;
                reqFTP.KeepAlive = true;
                reqFTP.Method = WebRequestMethods.Ftp.UploadFile;

                using (Stream uploadStream = reqFTP.GetRequestStream())
                {
                    string localFilePath = String.Format("{0}/{1}", localPath, fileName);
                    using (FileStream localFileStream = File.OpenRead(localFilePath))
                    {
                        /* Buffer for the Downloaded Data */
                        byte[] byteBuffer = new byte[localFileStream.Length];
                        int bytesSent = localFileStream.Read(byteBuffer, 0, bufferSize);
                        /* Upload the File by Sending the Buffered Data Until the Transfer is Complete */
                        try
                        {
                            while (bytesSent != 0)
                            {
                                uploadStream.Write(byteBuffer, 0, bytesSent);
                                bytesSent = localFileStream.Read(byteBuffer, 0, bufferSize);
                            }
                            status = true;
                        }
                        catch (Exception ex)
                        {
                            status = false;
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                status = false;
            }
            finally
            {
                reqFTP = null;
            }

            return status;
        }

Above mentioned code will upload your file into FTP server. If file exists it will override that file.

Happy learning.....

Download file from FTP

2015-04-08T15:48:00.000+05:30

Add 2 reference in your class:
using System.Net;
using System.IO;

Then copy below mentioned method
public static bool DownloadFile(string userId, string password, string ftpServerPath, string fileName, string localPath)
        {
            FtpWebRequest reqFTP;
            bool status = false;
            try
            {
                String downloadUrl = String.Format("{0}{1}/{2}", @"ftp:", ftpServerPath,fileName);
                reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(downloadUrl));
                reqFTP.UseBinary = true;
                reqFTP.Credentials = new NetworkCredential(userId, password);
                reqFTP.Method = WebRequestMethods.Ftp.DownloadFile;
                reqFTP.Proxy = null;
                reqFTP.KeepAlive = false;
                using (WebResponse response = reqFTP.GetResponse())
                {
                    using (Stream downloadStream = response.GetResponseStream())//;//read the download file from his stream
                    {
                        using (FileStream fs = File.Create(localPath + @"\" + fileName))
                        {
                            byte[] buffer = new byte[2048];
                            int read = 0;
                            do
                            {
                                read = downloadStream.Read(buffer, 0, buffer.Length);
                                fs.Write(buffer, 0, read);
                            }
                            while (read != 0);
                            status = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                status = false;
            }
            finally
            {
                reqFTP = null;
            }
            return status;
        }

Above mentioned method will download the desired file from FTP server to your local file system.

Happy learning......

Difference between Lamda expression and LINQ query

2014-05-15T11:24:00.000+05:30

Each style has their pros and cons. Query syntax is nicer when it comes to joins and it has the useful letkeyword that makes creating temporary variables inside a query easy.

Fluent syntax on the other hand has a lot more methods and operations that aren't exposed through the query syntax. Also since they are just extension methods you can write your own.

I have found that every time I start writing a LINQ statement using the query syntax I end up having to put it in parenthesis and fall back to using fluent LINQ extension methods. Query syntax just doesn't have enough features to use by itself.

There are some LINQ extension methods which do not have counterparts in LINQ query expressions, and will require the use of Lambda Expressions. A good example is Enumerable.ToLookup - if you want to create an ILookup, you need to use lambdas to generate this

Programatically setting up Physical Path Credential on IIS server for a virtual directoty

2014-05-13T15:00:00.001+05:30

Below mentioned function will do the same for you. But only thing you need to do add the reference of Microsoft.Web.Administration dll on your project(7.0.0.0)

private void SetPhysicalPathCredential(string virDirName)
{
try
{
if (!String.IsNullOrEmpty(physicalPathCredentialUserDomain) && !String.IsNullOrEmpty(physicalPathCredentialUserUserName) && !String.IsNullOrEmpty(physicalPathCredentialUserPassword))
{
using (ServerManager manager = new ServerManager())
{
Site defaultSite = manager.Sites["Default Web Site"];

// Read the data back from the updated configuration system,
// then modify the Physical Path credential.
Application apps = manager.Sites["Default Web Site"].Applications["/" + virDirName];
VirtualDirectory appDir = apps.VirtualDirectories[0];
LogMessage("Setting up Physical Path Credential: " + virDirName);
appDir["userName"] = physicalPathCredentialUserDomain + "\\" + physicalPathCredentialUserUserName;
appDir["password"] = physicalPathCredentialUserPassword;
LogMessage("Successfully set up Physical Path Credential");
manager.CommitChanges();
}
}
}
catch (Exception e)
{
LogMessage("ERROR: Error Setting up Physical Path Credential " + virDirName);
throw e;
}

}

Windows Installer error like while running light.exe from WiX

2014-05-13T14:54:00.000+05:30

Unregister vbscript.dll and jscript.dll from HKEY_CURRENT_USER to resolve this error:

1. Click on the Start menu, choose Run, type cmd and click OK
2. To unregister the VBScript engine, run this command: reg delete "HKCU\SOFTWARE\Classes\CLSID\{B54F3741-5B07-11CF-A4B0-00AA004A55E8}" /f
3. To unregister the VBScript engine on a 64-bit OS, run this command: reg delete "HKCU\SOFTWARE\Classes\Wow6432Node\CLSID\{B54F3741-5B07-11CF-A4B0-00AA004A55E8}" /f
4. To unregister the JScript engine, run this command: reg delete "HKCU\SOFTWARE\Classes\CLSID\{F414C260-6AC0-11CF-B6D1-00AA00BBBB58}" /f

5. To unregister the JScript engine on a 64-bit OS, run this command: reg delete "HKCU\SOFTWARE\Classes\Wow6432Node\CLSID\{F414C260-6AC0-11CF-B6D1-00AA00BBBB58}" /f

So here you just need to do depending your OS version(32bit/64bit) you need to run the appropriate command.

Once I did that, my build started working as expected. I still do not know how this information ended up in HKEY_CURRENT_USER on my system, particularly because the same build worked correctly on the same machine a couple of days ago. However, I am now back into a state where I can build successfully, and I know of a possible workaround to try if I end up with similar build issues in the future.

Hope this information will everyone who face this issue...... :)

Happy learning.......................................

Remove cache in asp.net

2013-09-03T14:17:00.000+05:30

The following code will remove all keys from the cache:

public void ClearApplicationCache(){

List<string> keys = new List<string>();
// retrieve application Cache enumerator
IDictionaryEnumerator enumerator = Cache.GetEnumerator();
// copy all keys that currently exist in Cache
while (enumerator.MoveNext()){
keys.Add(enumerator.Key.ToString());
}
// delete every key from cache
for (int i = 0; i < keys.Count; i++) {
Cache.Remove(keys[i]);
}
}

Modifying the second loop to check the value of the key before removing it should be trivial.

Hope this helps.

Asp.net Show Message box after saving the data into db server(sql)

2013-07-31T23:07:00.000+05:30

I have a common class and one of my method is this one

using System.Web;
using System.Text;
using System.Web.UI;

/// <summary>
/// A JavaScript alert
/// </summary>
public static class ShowAlertMessage
{

/// <summary>
/// Shows a client-side JavaScript alert in the browser.
/// </summary>
/// <param name="message">The message to appear in the alert.</param>
public static void Show(string message,string pageurl)
{
// Cleans the message to allow single quotation marks
string cleanMessage = message.Replace("'", "\\'");
string script = "<script type=\"text/javascript\">alert('" + cleanMessage + "');window.location='" + pageurl + "'</script>";

// Gets the executing web page
Page page = HttpContext.Current.CurrentHandler as Page;

// Checks if the handler is a Page and that the script isn't allready on the Page
if (page != null && !page.ClientScript.IsClientScriptBlockRegistered("alert"))
{
page.ClientScript.RegisterClientScriptBlock(typeof(ShowAlertMessage), "alert", script);
}
}

public static void Show(string message)
{
// Cleans the message to allow single quotation marks
string cleanMessage = message.Replace("'", "\\'");
string script = "<script type=\"text/javascript\">alert('" + cleanMessage + "');</script>";

// Gets the executing web page
Page page = HttpContext.Current.CurrentHandler as Page;

// Checks if the handler is a Page and that the script isn't allready on the Page
if (page != null && !page.ClientScript.IsClientScriptBlockRegistered("alert"))
{
page.ClientScript.RegisterClientScriptBlock(typeof(ShowAlertMessage), "alert", script);
}
}
}

then any time i want to display a message i just call that method from page like this.

ShowAlertMessage.Show("Data has been saved!");

hope that help.

Happy learning :)

Printing pyramid like stars using C#

2013-06-17T22:56:00.001+05:30

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(Enumerable.Range(1, 7).Aggregate(new StringBuilder(), (sb, i) => sb.AppendLine(new string('*', 7 - 2 * Math.Abs(i - 4)).PadLeft(7 - Math.Abs(i - 4)))));
            Console.ReadLine();
        }
    }
}

Output:

Use getElementById in Javascript to iterate through table

2013-04-24T15:51:00.001+05:30

Getting Access Denied error when using XMLHttpRequest in javascript on IE

2013-02-28T14:40:00.002+05:30

This error could happen due to cross domain issue, use relative path in Server Url if using absolute, also you may try changing Internet Explorer Trusted Site Zones settings (Internet Explorer Options->Security-> Trusted Sites (if your Url added into Trusted Sites, if added into Local intranet then choose Local Intranet) -> Custom Level) , scroll down and change Access data sources across domains value to Enable

If the above does not solve problem then you may deploy clientaccesspolicy and crossdomainpolict xml to resolve the issue:http://msdn.microsoft.com/en-us/library/cc197955(v=vs.95).aspx

Happy Learning.....

window.showModalDialog Page Load not being executed everytime

2012-11-27T15:37:00.001+05:30

Problem : Loading a page as a modal dialog box as window.showModalDialog("myurl.aspx"). The first time the modal dialog is poped up the page load event gets called. When i close it and call the same again, the Control does not come to the Page_Load(). Instead the page pops up with the previous values in all its controls. What we want is the Page_Load() to be triggered every time the modal dialog pops up.

Solution: First of all showModalDialog is an proprietary IE only feature its not supported by all browsers.

Solution 1 : You need to add a unique query string value to the URL that you are using to avoid IE showing the cached version. So generate a random number and append it to the URL e.g myurl.aspx?rnd=12237827348273. This should bust the cache and make a fresh request

Solution 2 : Turn caching of on the page by using @OutputCache directive

<%@ OutputCache Duration="1" Location="None" VaryByParam="none" %>

Solution 3 : Call----- Response.Cache.SetCacheability(HttpCacheability.NoCache);

Hope that will help you. Happy Learning...... :)

Dropdown list binding with financial year

2012-11-26T10:59:00.001+05:30

Problem: Sometimes we need to get a result of financial years(...2011-2012, 2012-2013, 2013-2014) starting from the date given by me to till 5 year in a dropdown list.

Solution: Lets say we received the given date from text box:-

DateTime GivenDate = Convert.ToDateTime(textBox1.Text);
int GivenYear = GivenDate.Year;
 
for(int i=0; i<5; i++)
{
comboBox1.Items.Add(GivenYear + i + "-" + (GivenYear + 1 + i));
}

Hope this will solve your problem.....

'Operation is not valid due to the current state of the object' error during postback

2012-10-18T10:18:00.003+05:30

Whenever a postback is done, this error occurs when form fields are very large in number. The stack trace is:

at System.Web.HttpValueCollection.ThrowIfMaxHttpCollectionKeysExceeded() at System.Web.HttpValueCollection.FillFromEncodedBytes(Byte[] bytes, Encoding encoding) at System.Web.HttpRequest.FillInFormCollection()

By default, the maximum value of MaxHttpCollection is 1000.
To solve this error, increase the MaxHttpCollection value. Try adding the following setting in your web.config's <appsettings> block.

<appSettings>
    <add key="aspnet:MaxHttpCollectionKeys" value="2001" />
</appSettings>

It can solve your problem. If you have very large records on your page, say 600 records in a grid, and you click on Submit, then increase the value of MaxHttpCollection.
Then change value 2000 to 5000 and test again. It will be resolved.

Question : Specifying MaxHttpCollectionKeys for a specific page - is this possible?

No, that's not possible. It always checks the appSetting aspnet:MaxHttpCollectionKeys and if that is missing it will be set to 1000. You cannot override this because the reading of the setting and the default value is done inside an internal class.

Perhaps a better approach would be to build a form with less than 1000 items. Maybe in the form of a wizard. Or maybe by combining multiple values into a single data structure and deserializing it on the server.

for more info please visit the link

Happy learning....... :)

Remove Duplicate Rows from a Table in SQL Server

2012-10-16T10:24:00.000+05:30

--Create Table Script
CREATE TABLE [dbo].[ATTENDANCE](
    [AUTOID] INT IDENTITY(1,1),
    [EMPLOYEE_ID] [varchar](50) NOT NULL,
    [ATTENDANCE_DATE] [date] NOT NULL
) ON [PRIMARY]

--Insert the into the newly created table
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
            ('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
            ('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
            ('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
            ('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
            ('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
            ('A003',CONVERT(DATETIME,'01-01-11',5))

--Check the data from the table
SELECT * FROM dbo.ATTENDANCE

--Check the duplicate data
SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)
    FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

--Delete the duplicate data
DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)
    FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

--Alternatively you can use the below query as well
DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MAX(AUTOID)
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

--After deleting the duplicate data check data from the table
SELECT * FROM dbo.ATTENDANCE

So in general use the below Query-

DELETE

FROM MyTable

WHERE ID NOT IN

(

SELECT    MAX(ID)

FROM MyTable

GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Table in example is has ID as Identity Column and Columns which have  duplicate data are DuplicateColumn1, DuplicateColumn2 and  DuplicateColumn3.

Happy learning..... :)

WCF - The type name ServiceReference1 does not exist in the type Namespace.ClassName

2012-10-09T18:13:00.000+05:30

This is a short blog post about a strange error when adding a WCF service reference. A WCF service was created and I wanted to add a service reference to a client project. I added the reference through Visual Studio and everything went fine until I compiled. Then I got this error:

Error 1 The type name 'ServiceReference1' does not exist in the type 'FeedbackCustomControl.FeedbackCustomControl' D:\RND\ExcelApplication\FeedbackCustomControl\Service References\ServiceReference1\Reference.cs 146 46 FeedbackCustomControl

I found that this is caused by me having the same namespace name as class name (FeedbackCustomControl.FeedbackCustomControl = Namespace.ClassName). Change your namespace to a name that is not the same name as your class and this will compile.

Happy Learning..... :)

The shortest, fastest, and easiest way to compare two tables in SQL Server

2012-09-05T18:41:00.000+05:30

When you have two tables (or resultsets from SELECT statements) that you wish to compare, and you want to see any changes in ANY columns, as well as to see which rows exist in 1 table but not the other (in either direction) I have found that the UNION operator works quite well.
UNION allows you to compare all columns very quickly, and also handles comparing NULL values to other NULLs successfully, which a join clause or a WHERE condition doesn't normally do. It also allows you to very quickly see which rows are missing in either table, which only a FULL OUTER JOIN will do, but of course we all know to avoid those at all costs (right?) -- a full outer join is about as “unrelational” as you can get. (every column returned is potentially Null and must be wrapped in a COALESCE function). Best of all, the UNION is quick and easy and short.
The basic idea is: if we GROUP the union of two tables on all columns, then if the two tables are identical all groups will result in a COUNT(*) of 2. But for any rows that are not completely matched on any column in the GROUP BY clause, the COUNT(*) will be 1 -- and those are the ones we want. We also need to add a column to each part of the UNION to indicate which table each row comes from, otherwise there is no way to distinguish between which row comes from which table.

CREATE PROCEDURE CompareTables(@table1 varchar(100),
@table2 Varchar(100), @T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS
declare @SQL varchar(8000);

IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList

set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2

set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'

exec ( @SQL)

Parameter definition:

-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all rows from either table that do NOT match
-- the other table in all columns specified, along with which table that row is from.

Happy learning......

Nested user controls and javascript getElementById

2012-08-07T14:22:00.000+05:30

This is the code to find control in WebUserControl which nested in your aspx page.
Suppose the popup.ascx's ID is popupControl and the locations.ascx's ID is locationControl
To find "drpLocationList" which in location.ascx we can use the below mentioned code:

var ddlLocation = document.getElementById('<%=popupControl.FindControl("locationControl").FindControl("drpLocationList").ClientID%>');

Disable History on Textbox

2012-08-07T13:06:00.000+05:30

For Framework version 2.0 and above:

Set the AutoCompleteType property to Disabled for the textbox.

AutoCompleteType="Disabled"

For other framework version :

TextBox1.Attributes.Add("autocomplete", "off")

Printing the content of div or panel

2012-06-27T03:24:00.000+05:30

In general, we required to print the content of a portion of page. We don't need that whole page will get printed. So here I am going to provide a small java script function that can solve this problem.
Here is the code for print a specific area.

function PrintDiv() {    
var prtContent = document.getElementById('<%=divPrintArea.ClientID%>');   
var WinPrint = window.open('', '', 'left=0,top=0,width=900,height=600,

toolbar=1,scrollbars=1,status=0');    
WinPrint.document.write(prtContent.innerHTML);    
WinPrint.document.close();   
WinPrint.focus();   
WinPrint.print();   
WinPrint.close();    
}

Javascript function to check integer value

2012-06-27T02:53:00.000+05:30

function isInteger(s) {
    var i;
    for (i = 0; i < s.length; i++) {
        // Check that current character is number.
        var c = s.charAt(i);
        if (((c < "0") || (c > "9"))) return false;
    }
    // All characters are numbers.
    return true;
}

Making a button default clickable when enter key is pressed

2012-06-27T02:45:00.001+05:30

Many times we want that on pressing enter key the submit button should be automatic click. For example on login page after typing password generally users press enter key and want that submit button will call but nothing happen.

Here is the JavaScript code for it:



function doClick(buttonName, e) {
            var key;
            if (window.event)
                        key = window.event.keyCode;     //IE
            else
                        key = e.which;     //firefox
            if (key == 13) {   //Get the button the user wants to have clicked
                        var btn = document.getElementById(buttonName);
                        if (btn != null) { //If we find the button click it
                                    btn.click();
                                    event.keyCode = 0
                        }
            }
}