sqlserver 存储过程动态参数调用实现代码

只是做笔记，没什么！！

--创建测试表
create table [dbo].[student](
[id] [int] identity(1,1) not null primary key,
[name] [nvarchar](20) not null default (''),
[age] [int] not null default (0),
[sex] [bit] not null default (0),
[address] [nvarchar](200) not null default ('')
)
--比如是一个查询存储过程
create proc getstudentbytype
@type int =0, -- 1根据id查询, 2根据性别查询
@args xml -- 参数都写到这里吧
as
begin
declare @id int,@sex bit
set @id=@args.value('(args/id)[1]','int') --参数都可以写在这里，如果没有传过来，大不了是null值了，反正也用不到，没关系的
set @sex =@args.value('(args/sex)[1]','bit')
if(@type=1)
begin
select * from dbo.student where id=@id
end
if(@type=2)
begin
select * from dbo.student where sex=@sex
end
end

参数写xml里感觉比用字符串要好很多，这样调用时参数就不好组织了，所以这里要有个帮助类xmlargs

public class xmlargs
{
private string _strargs = string.empty;
private bool _iscreate = false;
private dictionary<string, string> _args;
public string args
{
get
{
if (!_iscreate)
{
_strargs = _createargs();
_iscreate = true;
}
return _strargs;
}
}
public xmlargs()
{
_args = new dictionary<string, string>();
}
public void add(string key, object value)
{
_args.add(key, value.tostring());
_iscreate = false;
}
public void remove(string key)
{
_args.remove(key);
_iscreate = false;
}
public void clear()
{
_args.clear();
_iscreate = false;
}
private string _createargs()
{
if (_args.count == 0)
{
return string.empty;
}
stringbuilder sb = new stringbuilder();
foreach (string key in _args.keys)
{
sb.appendformat("<{0}>{1}</{0}>", key, _args[key]);
}
return sb.tostring();
}
}

调用：

private void binddata()
{
xmlargs args = new xmlargs();
args.add("id", 1);
system.data.datatable dt = getstudentbytype(1, args);
gridview1.datashow(dt);
}
private system.data.datatable getstudentbytype(int type, xmlargs args)
{
sqlhelper helper = new sqlhelper();
helper.params.add("type", type);
helper.params.add("args", args.args);
system.data.datatable dt = helper.rundatatable("getstudentbytype");
return dt;
}