title: using dynamic sql in c# code in visual studio
body: <p>I am developing a application in which i need to generate reports based on in and out entries of employees.
  I dont have experience with database related applications using dynamic queries.
  In this particular application i need to pass dynamic sql queries.</p>
  
  <p>In this i need to select column names based on user selection and then pass them to another sql query as parameters.
   here are the queries...</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX),
  
      @query  AS NVARCHAR(MAX)
  
  
  select @cols = STUFF((SELECT ',' + QUOTENAME(date +'_'+Logname) 
                      from
                      (
                        select doordate,
                          convert(char(10),doordate, 101) date, 
                          LogName
                        from DoorLog
                        cross apply
                        (
                          select 'In' LogName
                          union all
                          select 'Out' 
                        ) l
                      ) s   
                      group by convert(char(10), doordate, 112), date, Logname
                      order by convert(char(10), doordate, 112)
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'')
  
  set @query 
      = 'select employeeid, name, '+@cols+'
         from
         (
           select employeeid, name, 
            convert(char(8), doortime, 108) DoorTime,
            date + ''_''+ col col_names
           from
           (
              select p.employeeid,
                p.name,
                convert(char(10),d.doordate, 101) date,
                min(d.doordate) [In],
                max(d.doordate) [Out]
              from person p
              left join doorlog d
                on p.employeeid = d.employeeid
              group by p.employeeid, p.name, 
                convert(char(10),d.doordate, 101)
           )src
           unpivot
           (
             doortime
             for col in ([In], [Out])
           ) unpiv
         ) p
         pivot
         (
            max(doortime)
            for col_names in('+@cols+')
         ) piv'
  
  execute(@query)
  </code></pre>
  
  <p>I  have not been able to figure a way that how to use this code in C# code.</p>
  
  <p>Please help.</p>
  
  <p>Thanks in advance ...</p>