How to find parameters in sql query using C# Ado.Net

Question

I have got this query which is entered by user at run time.

SELECT * FROM Reports 
WHERE ReportDate > DATEADD(d, @Days, getdate())
AND ReportCode = cast(@Reportcode as int)

Is there any way in C# .Net or SQL to retrieve the parameter names @Days and @ReportCode from this sql query?

Regex or string matching using @ character is not full proof as parameter names may or may not end with a space. They can be immediately followed by a comma or parenthesis etc. and the name itself can contain a special character.

If I execute this query without providing parameter values, the sql engine throws exception Must declare the scalar variable "@Days". I can catch the exception and get first parameter name, but then it would be very complex to get next parameter in query.

Answer 1

create table login_db(UserName varchar(50),Password varchar(50))

go
create proc sp_logininsertdb
(@UserName varchar(50),@Password varchar(50))
as
begin
insert into login_db values(@UserName,@Password)
end

drop proc sp_logininsertdb 

exec   sp_logininsertdb @UserName='admin',@Password='admin'


go
create proc sp_loginviewdb
as
select *from login_db

exec sp_loginview_db



create table addemployee_db(id int identity(1,1),employeename varchar(50),dob Date,location varchar(50),gender varchar(50),doj Date,experience int,ctc int,designation varchar(50),unithead varchar(50),projectid int)


go
create procedure sp_addemployeedb(@id int out,@employeename varchar(50),@dob Date,@location varchar(50),@gender varchar(50),@doj Date,@experience int,@ctc int,@designation varchar(50),@unithead varchar(50),@projectid int)
as
begin 
insert into addemployee_db values (@employeename,@dob,@location,@gender,@doj,@experience,@ctc,@designation,@unithead,@projectid)
set @id=@@IDENTITY
end

declare @result int

exec sp_addemployee @id=@result output,@employeename='lokesh',
@dob='03/16/1994',@location='tvm',@gender='male',@doj='01/18/2016',
@experience=1,@ctc=4,@designation='ASE',@unithead='head1',@projectid='001'
print @result


go
create proc sp_viewemployee_db
as
begin
select * from addemployee_db
end


exec sp_viewemployee_db

drop proc sp_updateemployee_db

drop proc sp_editemployee_db



go
create procedure sp_editemployee_db(@id int,@employeename varchar(50),@dob Date,@location varchar(50),@gender varchar(50),@doj Date,@experience int,@ctc int,@designation varchar(50),@unithead varchar(50),@projectid int)
as
begin
update addemployee_db set employeename=@employeename,dob=@dob,location=@location,gender=@gender,doj=@doj,experience=@experience,ctc=@ctc,designation=@designation,unithead=@unithead,projectid=@projectid
where id=@id
end

exec sp_editemployee_db @id=10,@employeename='avaneesh',
@dob='10/11/1992',@location='Trivandrum',@gender='male',@doj='01/18/2016',
@experience=2,@ctc=7,@designation='ASET',@unithead='head2',@projectid='002'



go
create proc sp_updateemployee_db
as
begin
select * from addemployee_db
exec sp_updateemployee_db
end


drop proc sp_deletemployee_db


go
create proc sp_deletemployee_db
(@id int out)
as
begin
delete from addemployee_db where id=@id
end

exec sp_deletemployee @id=12


go
create proc sp_deleteemployee_db
as
begin
select * from addemployee_db
exec sp_deleteemployee_db
end

exec sp1_login_db




create table databinding_db(location varchar(20))


go
create proc sp_databindinginsertdb
(
@location varchar(20)
)
as
begin
insert into databinding_db
values(@location)
end






drop table databinding_db

drop proc sp_databindinginsertdb

select * from databinding_db


exec sp_databindinginsertdb 'chennai'
exec sp_databindinginsertdb 'trivandrum'
exec sp_databindinginsertdb 'puducherry'
exec sp_databindinginsertdb 'trichy'

select * from databinding_db



go
create proc sp_databindingdb
as
begin
select * from databinding_db
end

Answer 2

exec sp_describe_undeclared_parameters N'SELECT * FROM Reports 
WHERE ReportDate > DATEADD(d, @Days, getdate())
AND ReportCode = cast(@Reportcode as int)'

outputs:

parameter_ordinal name          suggested_system_type_id suggested_system_type_name  
----------------- ------------- ------------------------ ----------------------------
1                 @Days         56                       int                         
2                 @Reportcode   56                       int                         

(lots more columns skipped)

However, this is a SQL Server feature, not an ADO.NET one.

If I execute this query without providing parameter values,

The key trick here is remarkably simple: supply the correct parameters

Note: you can also use @params to tell it about the ones you already know about. For more details, see the documentation.