Comma separated values to table as a column

To display a comma separated varchar into table with columns,

Ex: input:
@csvstring varchar(100) =  'prod1,prod2,prod3,prod4'

output:
products
prod1
prod2
prod3
prod4

Script to acheive this in sql:

DECLARE @param NVARCHAR(MAX)
SET @param = 'prod1,prod2,prod3,prod4'
DECLARE @paramToCSV XML
SET @paramToCSV = CAST ('<M>' + REPLACE(@param, ',', '</M><M>') + '</M>' AS XML)
SELECT prod.query('./text()') AS products
FROM  @paramToCSV.nodes('/M')  AS Bike(prod);

Using CASE THEN in WHERE clause in SQL query

Recently I have come up with a problem where I have to write a query against a CTE result. The problem statement looks simple, came to know the complexity only after when I tried it out.

Here is the problem Statement: 

There are result set which will come from CTE having two columns named "Col1" and "Col2" which are of type "BIT". At any point of time both "Col1" and "Col2" will not have "1" in any case. Rest all possible combinations can exist.

Problem: The User should have option to see  

• Option 1(@None) : all the records with Col1=0 AND Col2=0 
• Option 2(@Col1). records with Col1=1. Ex : Col1=1 and Col2 != 1
• Option 3(@Col2). Records with Col2=1. Ex : Col1 != 1 and Col2 = 1

Here User even can see records with multiple combinations.  

ex: Option 1 and 2

Can See all records having 

•  Col1=0 AND Col2=0
•  Col1=1 AND Col2=0

Approach:

To achieve this I have tried with various conditions which ended up with the large set of AND, OR opertaions, which looks very difficult to understand and maintain later on time for anyone.

After trying and doing  lot of possible ways ended up using "CASE WHEN THEN" in WHERE. 

Solution:

Here is how I have achieved :

select * from CndTrk

WHERE

(CASE 

when (@Col1=1 AND @Col2=1 AND @None = 1)

THEN 1

WHEN @None =1

THEN  

CASE 

WHEN @Col2=1 AND CndTrk.Col1 <>1

THEN 1

WHEN @Col1=1 AND CndTrk.Col2 <>1

THEN 1

WHEN CndTrk.Col2 <>1 AND CndTrk.Col1 <>1

THEN 1

END

ELSE

CASE 

WHEN @Col2=1 AND CndTrk.Col2 = 1 

THEN 1

WHEN @Col1=1 AND CndTrk.Col1 = 1 

THEN 1

END

END

) = 1

Quick Reference to ADO.Net

	Command Objects	Returns	O/p Type	Used
1	Execute Non Query	No Of Rows Effected	int	Insert, Update
2	ExecuteReader	SqlDataReader	SqlDataReader	Fetch Data
3	ExecuteScalar	First Col of  First Row	int, string	Count(*)
4	SqlDataReader
	Features
	Forward Read-only
	Initiate only using ExecuteReader()
5	Dataset
	The DataSet represents a complete set of data independent of actual DataSource
	Interaction with existing data sources is controlled through the DataAdapter

SQL Server Data Types and Their .NET Framework Equivalents

SQLServer datatype       CLRdatatype(SQLServer)    CLRdatatype(.NET Framework)  
varbinary                     SqlBytes, SqlBinary           Byte[]  
binary                        SqlBytes, SqlBinary           Byte[]  
varbinary(1), binary(1)       SqlBytes, SqlBinary           byte, Byte[] 
image                         None                          None

varchar                       None                          None
char                          None                          None
nvarchar(1), nchar(1)         SqlChars, SqlString           Char, String, Char[]     
nvarchar                      SqlChars, SqlString           String, Char[] 
nchar                         SqlChars, SqlString           String, Char[] 
text                          None                          None
ntext                         None                          None

uniqueidentifier              SqlGuid                       Guid 
rowversion                    None                          Byte[]  
bit                           SqlBoolean                    Boolean 
tinyint                       SqlByte                       Byte 
smallint                      SqlInt16                      Int16  
int                           SqlInt32                      Int32  
bigint                        SqlInt64                      Int64 

smallmoney                    SqlMoney                      Decimal  
money                         SqlMoney                      Decimal  
numeric                       SqlDecimal                    Decimal  
decimal                       SqlDecimal                    Decimal  
real                          SqlSingle                     Single  
float                         SqlDouble                     Double  

smalldatetime                 SqlDateTime                   DateTime  
datetime                      SqlDateTime                   DateTime 

sql_variant                   None                          Object  
User-defined type(UDT)        None                          user-defined type     
table                         None                          None 
cursor                        None                          None
timestamp                     None                          None 
xml                           SqlXml                        None

Get Assembly location of the DLL added to SQL Server

SELECT 
    assembly = a.name, 
    path     = f.name
FROM sys.assemblies AS a
INNER JOIN sys.assembly_files AS f
ON a.assembly_id = f.assembly_id
WHERE a.is_user_defined = 1;

Get GridView Column Headers and Appropriate Row Values in Asp.Net

Get Gridview column header(s) text and Specific Row's Values on Row Command in asp.net. Below Method takes GridView control, Command Source Within the Row(LinkButton, Button, ImageButton, etc..), no of Columns to skip in from Start Column and from End Column as Parameters and returns List<KeyValuePair<headerText,Values>> as output.

private void SetDeleteDetails(GridView pGV, object pCmdSource, int pStartSkipColCount=0, int pEndSkipColCount=0)

        {

            List<KeyValuePair<string, string>> lkvp = new List<KeyValuePair<string, string>>();

            GridViewRow rw = (GridViewRow)((Control)pCmdSource).NamingContainer;

            for (int i = pStartSkipColCount; i < pGV.Columns.Count - pEndSkipColCount; i++)

                if (!string.IsNullOrEmpty(rw.Cells[i].Text))

                    lkvp.Add(new KeyValuePair<string, string>(pGV.Columns[i].HeaderText, rw.Cells[i].Text));

            //Session["DeleteDetails"] = lkvp;

        }

Get Specific Property Name Using Expression

Hope the program explains all:

class Program
    {
        static void Main(string[] args)
        {
            SomeClass sc = new SomeClass();
            Console.WriteLine(sc.p1.GetType());

            //RemoteMgr.ExposeProperty(() => SomeClass.SomeProperty);
            MemberInfo member = RemoteMgr.GetMemberInfo((SomeClass p) => p.SomeProperty);
            Console.WriteLine(member.Name);
            Console.ReadKey();
        }
    }

    public class SomeClass
    {
        public string SomeProperty
        {
            get { return "Foo"; }
        }
    }

    public class RemoteMgr
    {
        public static MemberInfo GetMemberInfo<T, U>(Expression<Func<T,U>> expression)
        {
            var member = expression.Body as MemberExpression;
            if (member != null)
                return member.Member;
            throw new ArgumentException("Expression is not a member access", "expression");
        }
    }