using dynamic sql in c# code in visual studio

Question

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.

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...

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)

I have not been able to figure a way that how to use this code in C# code.

Please help.

Thanks in advance ...

@PraveenNambiar sir, how to use stored procedure in C# code? — humorousdragon, Mar 21 '13 at 08:01

score 1 · Accepted Answer · edited May 23 '17 at 11:49

1

Your best bet is to create a Stored Procedure in your database that can handle the incoming request, and execute the query. Here's some more information on Stored Procedures:

http://msdn.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

Then you will need to create a connection to your database in your C# code:

http://msdn.microsoft.com/en-us/library/s4yys16a(v=vs.71).aspx

Finally you will need to called your stored procedure using the connection you've just created and passing the parameters from your C# code, which has been answered many times on StackOverflow:

How to execute a stored procedure within C# program

edited May 23 '17 at 11:49

Community

1
1

answered Mar 21 '13 at 08:00

Tom Bowen

8,214
4
22
42

actually the values in the database are inserted by some other application and i am not allowed to make any changes to the database. – humorousdragon Mar 21 '13 at 08:04
What do you mean? You want to build up the SQL in your C# code, and then execute it on the database? – Tom Bowen Mar 21 '13 at 08:05
yes.i want the sql to be coded into the application and not on server side. – humorousdragon Mar 21 '13 at 08:08
i will be really great-full if u can guide me into the right direction. – humorousdragon Mar 21 '13 at 08:12
While this is possible, it is not great practice. Why would you want to do so? I shall update my answer shortly. – Tom Bowen Mar 21 '13 at 08:13
In fact you make use of data from your database within this code. This will become complicated and awkward to implement in C#, with multiple calls to your database. Why do you want to do it this way? – Tom Bowen Mar 21 '13 at 08:15
there are two different applications. one is the actual punching machines software.and other is the one i m trying to develop.. Now i don't have enough permissions on every machine on which the first application is installed to make changes to sql-server. So, i am left with the only option of doing everything in the code.. – humorousdragon Mar 21 '13 at 08:17
Initially i used simple select statments in my code with the predefined col_names on mothly basis. but they want to select any range of dates in the application in which i need to pass the paramaeters on run-time. So, i had to shift to dynamic sql queries. – humorousdragon Mar 21 '13 at 08:23

using dynamic sql in c# code in visual studio

1 Answers1