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 @[email protected]('(args/id)[1]','int') --参数都可以写在这里，如果没有传过来，大不了是null值了，反正也用不到，没关系的 
SET @sex [email protected]('(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; 
}