如何使用 C# Ado.Net 在 SQL 查询中查找参数
本文关键字：查询 查找 参数 SQL 何使用 Ado Net | 更新日期: 2023-09-27 18:36:34
我得到了这个查询，该查询由用户在运行时输入。
SELECT * FROM Reports
WHERE ReportDate > DATEADD(d, @Days, getdate())
AND ReportCode = cast(@Reportcode as int)
在 C# .Net 或 SQL 中是否有任何方法可以从此 sql 查询中检索@Days和@ReportCode的参数名称？
使用 @ 字符的正则表达式或字符串匹配不是完全证明，因为参数名称可能以空格结尾，也可能不以空格结尾。它们可以紧跟逗号或括号等，名称本身可以包含特殊字符。
如果我在不提供参数值的情况下执行此查询，sql 引擎将引发异常 必须声明标量变量"@Days"。我可以捕获异常并获取第一个参数名称，但是在查询中获取下一个参数将非常复杂。
exec sp_describe_undeclared_parameters N'SELECT * FROM Reports
WHERE ReportDate > DATEADD(d, @Days, getdate())
AND ReportCode = cast(@Reportcode as int)'
输出：
parameter_ordinal name suggested_system_type_id suggested_system_type_name
----------------- ------------- ------------------------ ----------------------------
1 @Days 56 int
2 @Reportcode 56 int
（跳过更多列）
但是，这是一项 SQL Server 功能，而不是 ADO.NET 功能。
如果我在不提供参数值的情况下执行此查询，
这里的关键技巧非常简单：提供正确的参数
注意：您还可以使用@params告诉它您已经知道的那些。有关更多详细信息，请参阅文档。
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