存储过程动态配置

using System;
using System.Text.RegularExpressions;
using System.Web;
using System.Reflection;
using System.Configuration;
using System.Xml;
using System.Xml.Serialization;
using System.Xml.XPath;

namespace WebApplication2
{
 /// <summary>
 /// ProcedureConfig 的摘要说明。
 /// </summary>
 public class ProcedureConfig:System.Configuration.IConfigurationSectionHandler
 {
  
  public ProcedureConfig()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }
  private Command[] _Commands;
  [XmlArray("Commands")]
  public  Command[] Commands
  {
   get
   {
    return this._Commands;}
   set
   {
    this._Commands=value;
    
   }
  }
  public static ProcedureConfig Instance()
  {
   return ((ProcedureConfig)ConfigurationSettings.GetConfig("ProcedureConfig"));
  }
  public static  System.Data.SqlClient.SqlCommand GetCommandByName(string cmdname)
  {
            ProcedureConfig cfg=ProcedureConfig.Instance ();
   foreach(Command cmd in cfg.Commands)
   {
    if(cmd.CommandText.ToUpper ()==cmdname.ToUpper ())
     return cmd.toSqlCommand ();
   }
   throw new Exception("Command Not Found");
  }
  public static  System.Data.SqlClient.SqlConnection GetSqlConn()

  {
    return new System.Data.SqlClient.SqlConnection("server=.;uid=sa;pwd=;database=ORM");
  }
  #region IConfigurationSectionHandler 成员
  public object Create(object parent, object configContext, XmlNode section)
  {
   // TODO:  添加 ProcedureConfig.Create 实现
   XPathNavigator nav = section.CreateNavigator();
   string typename = (string) nav.Evaluate("string(@type)");
   Type t = Type.GetType(typename);
   XmlSerializer ser = new XmlSerializer(t);
   return ser.Deserialize(new XmlNodeReader(section));
  }

  #endregion
 }
 public class Pm
 {
  /*<Pm ParameterName="@ID" Direction="Output"  SqlDbType="Int" Size="4"></Pm>*/

  public  Pm()
  {
  }
  private string _ParameterName;
  [XmlAttribute("ParameterName")]
  public  string  ParameterName
  {
   get
   {
    return this._ParameterName;}
   set
   {
    this._ParameterName=value;
    
   }
  }
  private System.Data.ParameterDirection  _Direction;
  [XmlAttribute("Direction")]
  public  System.Data.ParameterDirection  Direction
  {
   get
   {
    return this._Direction;}
   set
   {
    this._Direction=value;
    
   }
  }
  private System.Data.SqlDbType _SqlType;
  [XmlAttribute("SqlDbType")]
  public  System.Data.SqlDbType  SqlDbType
  {
   get
   {
    return this._SqlType;}
   set
   {
    this._SqlType=value;
    
   }
  }
  private int _Size;
  [XmlAttribute("Size")]
  public  int  Size
  {
   get
   {
    return this._Size;}
   set
   {
    this._Size=value;
    
   }
  }
  private readonly object HandlerLock = new object();
  private ConstructorInfo constructor = null;
  public object Instance()
  {
   if(constructor == null)
   {
    lock(HandlerLock)
    {
     if(constructor == null)
     {
      System.Type t = System.Type.GetType("WebApplication2.Pm,WebApplication2");
      constructor = t.GetConstructor(new Type[0]);
     }
    }
   }
   return constructor.Invoke(null);
  }
  public   System.Data.SqlClient.SqlParameter toSqlParameter()
  {
   System.Data.SqlClient.SqlParameter pa=new System.Data.SqlClient.SqlParameter();
   pa.ParameterName=this.ParameterName;
   pa.Direction=this.Direction ;
   pa.Size=this.Size;
   pa.SqlDbType=this.SqlDbType;
   return pa;
    }
 }
 public class Command
 {
  private string _CommandText;
  [XmlAttribute("CommandText")]
  public  string  CommandText
  {
   get
   {
    return this._CommandText;}
   set
   {
    this._CommandText=value;
    
   }
  }
  private Pm[] _Parameters;
  [XmlArray("Parameters")]
  public  Pm[] Parameters
  {
   get
   {
    return this._Parameters;}
   set
   {
    this._Parameters=value;
    
      }
  }
  public Command()
  {
  }
  private readonly object HandlerLock = new object();
  private ConstructorInfo constructor = null;
  public object Instance()
  {
   if(constructor == null)
   {
    lock(HandlerLock)
    {
     if(constructor == null)
     {
      System.Type t = System.Type.GetType("WebApplication2.Command,WebApplication2");
      constructor = t.GetConstructor(new Type[0]);
     }
    }
   }
   return constructor.Invoke(null);
  }
  public System.Data.SqlClient.SqlCommand toSqlCommand()
  {
   System.Data.SqlClient.SqlCommand cmd=new System.Data.SqlClient.SqlCommand();
   cmd.CommandText=this.CommandText;
   cmd.CommandType=System.Data.CommandType.StoredProcedure;
   foreach(Pm pm in this.Parameters)
   {
   
    cmd.Parameters.Add(pm.toSqlParameter ());
   }
   return cmd;
  }
 
   //<Command CommandText="tAdd" CommandType="StoredProcedure">
 }
}
///////////////////自定义配置节

<configSections>
  <!--存储过程配置-->
  <section name="ProcedureConfig" type="WebApplication2.ProcedureConfig,WebApplication2"></section>
 </configSections>
 
 <ProcedureConfig type="WebApplication2.ProcedureConfig,WebApplication2">
     <Commands>
        <Command CommandText="tAdd">
     <Parameters>
      <Pm ParameterName="@ID" Direction="Output"  SqlDbType="Int" Size="4"></Pm>
      <Pm ParameterName="@PC" Direction="Input"   SqlDbType="VarChar" Size="50"></Pm>
     </Parameters>
        </Command>
     </Commands>
 </ProcedureConfig>

////////////////调用方式

using(System.Data.SqlClient.SqlConnection cnn=ProcedureConfig.GetSqlConn())
   {
    cnn.Open ();
    System.Data.SqlClient.SqlCommand cmd=ProcedureConfig.GetCommandByName("tAdd");
    cmd.Connection=cnn;
    cmd.Parameters[1].Value="dhz";
       cmd.ExecuteReader ();
    cnn.Close ();
    Response.Write(cmd.CommandText);
    Response.Write(cmd.Parameters[0].Value .ToString ());
   }

/////////////////////////////存储过程如下:

ALTER  procedure  tAdd
(
  @id int output,
  @pc varchar(50)
)
as
Begin
    Select  @id=4
    select * from proTab where  proName like '%'+@pc or proName like @pc+'%'
    
End
/////////////////////执行结果是正确的