[Note] Stored Procedure Dynamic Parameters

Just take notes. Nothing !!

-- Create test table
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 ( '' )
)

-- For example, a query Stored Procedure
Create   Proc Getstudentbytype
@ Type   Int   = 0 , -- 1 query by ID, 2 query by gender
@ ARGs XML -- Write all the parameters here.
As
Begin
Declare   @ ID   Int , @ Sex   Bit
Set   @ ID = @ ARGs . Value ( ' (ARGs/ID) [1] ' , ' Int ' ) -- All parameters can be written here. If it is not passed in, it cannot be a null value. It doesn't matter.
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

 

It is much better to write parameters in XML than to use strings. In this way, the parameters are difficult to organize during the call, so here we need a help class 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;
}
}

PublicXmlargs ()
{
_ ARGs= NewDictionary<String,String>();
}

Public void Add ( string key, Object value)
{< br> _ args. add (Key, value. tostring ();
_ iscreate = false ;
}

Public void remove ( string key)
{< br> _ args. remove (key);
_iscreate = false ;
}

Public VoidClear ()
{
_ 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 ();
}
}

 

Call:

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;
}