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; } |
标签:
sqlserver存储过程
