1  
   2  /* usage:  */
   3  /* call a stored procedure 'GetUserDetailsByID' that takes an '@UserID int' argument and returns a row of data about that user */
   4  string userFullName = new DataHandler( _connStr ).ExecuteSprocHandleReader(
   5      "GetUserDetailsByID",
   6      new DataReaderHandler( GetUserFullName ),
   7      new SprocParam( "@UserID", 123 )
   8  ).ToString();
   9  
  10  /* extract the data from the reader and prepare for output */
  11  private object GetUserFullName( IDataReader reader, ReaderHandlerEventArgs args )
  12  {
  13      reader.Read();
  14      return string.Format( "{0}, {1}", reader["LastName"], reader["FirstName"] );
  15  }
  16  
  17  /* code:  */
  18  public delegate object DataReaderHandler( IDataReader reader, ReaderHandlerEventArgs args );
  19  	
  20  public struct SprocParam
  21  {
  22  	private string _name;
  23  	private object _value;
  24  
  25  	public SprocParam( string name, object value )
  26  	{
  27  		_name = name;
  28  		_value = value;
  29  	}
  30  
  31  	public string Name
  32  	{
  33  		get { return _name; }
  34  	}
  35  
  36  	public object Value
  37  	{
  38  		get { return _value; }
  39  	}
  40  }
  41  	
  42  public class ReaderHandlerEventArgs : EventArgs
  43  {
  44  	private object _data;
  45  
  46  	public ReaderHandlerEventArgs()
  47  	{
  48  	}
  49  
  50  	public ReaderHandlerEventArgs( object data )
  51  	{
  52  		_data = data;
  53  	}
  54  
  55  	public object Data
  56  	{
  57  		get { return _data; }
  58  		set { _data = value; }
  59  	}
  60  }
  61  
  62  public class DataHelper
  63  {
  64  	private string _connStr;
  65  
  66  	public DataHelper( string connectionString )
  67  	{
  68  		_connStr = connectionString;
  69  	}
  70  
  71  	public object ExecuteSprocHandleReader( string sproc, DataReaderHandler handler, params SprocParam[] sprocParameters )
  72  	{
  73  		return ExecuteSprocHandleReader( sproc, handler, null, sprocParameters );
  74  	}
  75  		
  76  	public object ExecuteSprocHandleReader( string sproc, DataReaderHandler handler, object handlerArgs, params SprocParam[] sprocParameters )
  77  	{
  78  		IDbConnection conn = null;
  79  		try
  80  		{
  81  			// can we instantiate the connection type from Windsor/Spring ???
  82  			using( conn = new SqlConnection( _connStr ) )
  83  			{
  84  				conn.Open();
  85  				using( IDbCommand cmd = conn.CreateCommand() )
  86  				{
  87  					cmd.CommandType = CommandType.StoredProcedure;
  88  					cmd.CommandText = sproc;
  89  					foreach( SprocParam param in sprocParameters )
  90  					{
  91  						IDbDataParameter p = cmd.CreateParameter();
  92  						p.ParameterName = param.Name;
  93  						p.Value = param.Value;
  94  						cmd.Parameters.Add( p );
  95  					}
  96  						
  97  					using( IDataReader reader = cmd.ExecuteReader() )
  98  					{
  99  						return handler( reader, new ReaderHandlerEventArgs( handlerArgs ) );
 100  					}
 101  				}
 102  			}
 103  		}
 104  		finally
 105  		{
 106  			if( conn != null && conn.State != ConnectionState.Closed )
 107  			{
 108  				conn.Close();
 109  			}
 110  		}
 111  	}
 112  }