Comma Separated List of all columns for tablename in the Database

 select t.name, 

        STUFF ((

            select ',' + c.name

            from sys.columns c

                join sys.tables tt on tt.object_id = t.object_id and t.object_id = c.object_id

                join sys.schemas s on tt.schema_id = s.schema_id

            order by t.name, c.column_id

            for xml path('')), 1, 1, '') as columns

from sys.tables t

            where t.name = 'Test'--'PutTableNameHere if you want to filter'

How to make Bootable Pendrive (USB Drive)

Boot a Pendrive
_______________________________

 Boot cmd :

step1: diskpart
step2: list disk
step3: select disk 1
step4: clean
step5: create partition primary
step6: format fs=fat32 quick or format fs=ntfs quick
step7: active
step8: assign
step9: exit
step10: exit

Check if a stored procedure has a specified parameter with a SQL script

INFORMATION_SCHEMA view that you could use is PARAMETERS. This query will list all stored procedures that have such a parameter:


SELECT DISTINCT p.SPECIFIC_NAME
FROM INFORMATION_SCHEMA.PARAMETERS p
WHERE p.PARAMETER_NAME = '@x'

Syntax


SELECT DISTINCT p.SPECIFIC_NAME
FROM INFORMATION_SCHEMA.PARAMETERS p
WHERE p.PARAMETER_NAME = '@Pageno'

How to use Querystring Parameters in Asp.Net MVC to Retrieve or Send Data

Query string in asp.net mvc with accessing from URL now let’s call Action Method from Action Link from passing Query string. Calling Action method from Action Link with Passing Query string.



With Optional Parameters 

@Html.ActionLink("Call Index Action", "Index", new { ID = 10, Name = 3, Department = "test" })

Without Optional Parameters 

@Html.ActionLink("Call Details Action", "Details", new { Name = 3, Department = "test" })

How to generate List of previous 12 months with year from DateTime.Now

var lastSixMonths = Enumerable.Range(0, 12)
                                   .Select(i => DateTime.Now.AddMonths(i -12))
                                    .Select(date => date.ToString("MMM"));


Output :
  "Mar"
"Apr"
"May"
"Jun"
"Jul"
"Aug"
"Sep"
"Oct"
"Nov"
"Dec"
"Jan"
"Feb"


var lastSixMonths = Enumerable.Range(0, 12) .Select(i => DateTime.Now.AddMonths(i -12)) .Select(date => date.ToString("MM/yyyy"));

Output :
  "03/2019"
"04/2019"
"05/2019"
"06/2019"
"07/2019"
"08/2019"
"09/2019"
"10/2019"
"11/2019"
"12/2019"
"01/2020"

"02/2020"

Visual Studio Expand/Collapse keyboard shortcuts

Ctrl+M,O would collapse all constructors, methods, properties in a file. It would also collapse all regions.

Ctrl+M,L would expand all constructors, methods, properties in a file. It would also expand all regions.



Visual studio 2017 except it is turned off by default. It can be enabled under Tools -> Options -> Text Editor -> C# -> Advanced -> Outlining -> "Collapse #regions when collapsing to definitions"

Visual Studio 2015:

Tools > Options > Settings > Environment > Keyboard
Defaults:

Edit.CollapsetoDefinitions: CTRL + M + O

Edit.CollapseCurrentRegion: CTRL + M +CTRL + S

Edit.ExpandAllOutlining: CTRL + M + CTRL + X

Edit.ExpandCurrentRegion: CTRL + M + CTRL + E

To set and use IntelliJ's shortcuts:

Edit.CollapsetoDefinitions: CTRL + SHIFT + NUM-

Edit.CollapseCurrentRegion: CTRL + NUM-

Edit.ExpandAllOutlining: CTRL + SHIFT + NUM+

Edit.ExpandCurrentRegion: CTRL + NUM+

Collapse to definitions

CTRL + M, O

Expand all outlining

CTRL + M, X

Expand or collapse everything

CTRL + M, L

Remove extra spaces from a string

Here is a simple one:

trimmedstr = str.replace(/\s+$/, '');

When you need to remove all spaces at the end:

str.replace(/\s*$/,'');
When you need to remove one space at the end:

str.replace(/\s?$/,'');
\s means not only space but space-like characters; for example tab.

If you use jQuery, you can use the trim function also:

str = $.trim(str);

But trim removes spaces not only at the end of the string, at the beginning also.

 var str  = "Hello World  ";
  var ans = str.replace(/(^[\s]+|[\s]+$)/g, '');

  alert(str.length+" "+ ans.length);

Difference Between NEWSEQUENTIALID() and NEWID() in SQL Server

NEWSEQUENTIALID() and NEWID() both generates the GUID of datatype of uniqueidentifier.

NEWSEQUENTIALID:

1. NEWSEQUENTIALID() generates GUID in hexadecimal incremental interval. The hexadecimal number can be any placed in GUID
2. Function NEWSEQUENTIALID() can not be used in SQL queries and it can be only used in DEFAULT clause of table. NEWSEQUENTIALID() are predictable
3. NEWSEQUENTIALID() generates the GUID in sequential order.

NEWID:

• NEWID() generates the GUID in random order whereas 
• in case of privacy or security use NEWID() instead of NEWSEQUENTIALID()
• NEWID() will work perfectly fine when used in queries.

Table Syntax :

CREATE TABLE tablename
 (
 NewIDColumnName uniqueidentifier DEFAULT NEWID(),
 NewSeqColumnName uniqueidentifier DEFAULT NewSequentialID()
)

Check for empty GUID in SQL

select cast(cast(0 as binary) as uniqueidentifier)

Declare @GuidParam uniqueidentifier

 SET  @GuidParam ='00000000-0000-0000-0000-000000000000'


IF @GuidParam = CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER)
BEGIN
   --Guid is empty
END

To write Case Statement in WHERE Clause

 DECLARE @FirstName VARCHAR(100)
SET @FirstName = 'King'

DECLARE @LastName VARCHAR(100)
SET @LastName = 'Test'

SELECT FirstName, LastName
FROM Employee
WHERE 
    FirstName = CASE
    WHEN LEN(@FirstName) > 0 THEN  @FirstName
    ELSE FirstName
    END
AND
    LastName = CASE
    WHEN LEN(@LastName) > 0 THEN  @LastName
    ELSE LastName
    END
GO

Regular Expressions: Remove Whitespace from Start and End

let hello = "   Hello, World!  ";
let wsRegex = /^\s+|\s+$/g; // Change this line
let result = hello.replace(wsRegex,""); // Change this line


Explanation :(/^\s+|\s+$/g;)

1. caret symbol ^ looks at the start of string
2. dollar symbol $ looks at the end of string
3. The | is an OR operator
4. \s+ looks for one or more white spaces 
5. The g flag is needed to match all the spaces that are at the beginning OR at the end. Otherwise it would match only the first part of the regex, and the second part only if the first part doesn’t match anything

To Get Current Database Name in SQL SERVER

SELECT statement where I get the name of the current Database


SELECT DB_NAME() AS DataBaseName

How do I replace multiple spaces with a single space in C#?

Example:

1 2 3  4    5
would be:

1 2 3 4 5


Solution:-
RegexOptions options = RegexOptions.None;
Regex regex = new Regex("[ ]{2,}", options);     
tempo = regex.Replace(tempo, " ");


myString = Regex.Replace(myString, @"\s+", " ");

TRIM() In SQL Server 2017

SELECT TRIM('     My test query     ')

/* Result */
My test query


SELECT TRIM( '.! ' FROM  '@     My Test Query   !..') AS Result;

/* Result */
@     My Test Query


SELECT len(TRIM( '.! ' FROM  '@     My Test Query   !..')) AS Result;

/* Result */
19


SELECT firstName, TRIM(char(32) FROM firstName) as Trim_name from Table

Firstname  TrimName
      Test      Test

'CONCAT' is not a recognized built-in function name

"CONCAT" was introduced in SQL Server 2012; there is no way to make it work in SQL Server 2008 R2.


use the ODBC CONCAT function like this:

SELECT {fn CONCAT('foo ', 'test') }

The problem with this is that this function only allows you two parameters at a time. So unless you want to use more than two like this:

SELECT {fn CONCAT('foo ', {fn CONCAT('test ', 'buddy')}) }

 just use the '+' operator.

Syntax:

 select

 RTRIM(LTRIM(
        CONCAT(
COALESCE(c.Prefix + ' ', '')
           , COALESCE(c.FirstName + ' ', '')
            , COALESCE(c.MiddleName + ' ', '')
, COALESCE(c.LastName + ' ', '')
            , COALESCE(c.Suffix,+ ' ', '')
        )
    )) as CustomerName  from TableName


-- To Check SQL Server  version

--SELECT @@VERSION;
-- Microsoft SQL Server 2017 

Cache VS Session VS cookies?

State management is a critical thing to master when coming to Web world from a desktop application perspective.

• Session is used to store per-user information for the current Web session on the server. It supports using a database server as the back-end store.
• Cookie should be used to store per-user information for the current Web session or persistent information on the client, therefore client has control over the contents of a cookie.
• Cache object is shared between users in a single application. Its primary purpose is to cache data from a data store and should not be used as a primary storage. It supports automatic invalidation features.
• Application object is shared between users to store application-wide state and should be used accordingly.

how to remove cached your connection to Team Foundation under your user settings

if you still get an error message that mentions your old IP address, try emptying the cache folder at the following location:

C:\Users[USER]\AppData\Local\Microsoft\Team Foundation\X.0\Cache

This should force Visual Studio to use the new IP address that you provide in the sln file

Getting the list of parameters from a stored procedure in SQL

use the INFORMATION_SCHEMA.parameters view
 
SELECT parameter_name, ordinal_position,parameter_mode,data_type
FROM INFORMATION_SCHEMA.parameters
WHERE SPECIFIC_NAME = 'USP_TableList'


use sys.parameters and join that with sys.types

SELECT s.name AS parameter_name,
   parameter_id AS ordinal_position,
   CASE is_output WHEN 0 THEN 'IN' ELSE 'INOUT' END Parameter_Mode,
   t.name AS data_type
FROM sys.parameters s
JOIN sys.types t ON s.system_type_id = t.user_type_id
WHERE object_id = object_id('USP_TableList')

Here is the output

parameter_name	ordinal_position	parameter_mode	data_type
@id	1	IN	int
@Date	2	IN	date
@char	3	INOUT	char

Update query failing with error in MySQL Workbench

Follow the following steps before executing the UPDATE command: In MySQL Workbench

    Go to Edit --> Preferences
    Click "SQL Editor" tab and uncheck "Safe Updates" check box
    Query --> Reconnect to Server // logout and then login
    Now execute your SQL query

or

SET SQL_SAFE_UPDATES = 0;