DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Kevin has posted 1 posts at DZone. View Full User Profile

Data Reader Handler

09.05.2006
| 2736 views |
  • submit to reddit
        Encapsulate a stored procedure call but allow external data processing.

/* usage:  */
/* call a stored procedure 'GetUserDetailsByID' that takes an '@UserID int' argument and returns a row of data about that user */
string userFullName = new DataHandler( _connStr ).ExecuteSprocHandleReader(
    "GetUserDetailsByID",
    new DataReaderHandler( GetUserFullName ),
    new SprocParam( "@UserID", 123 )
).ToString();

/* extract the data from the reader and prepare for output */
private object GetUserFullName( IDataReader reader, ReaderHandlerEventArgs args )
{
    reader.Read();
    return string.Format( "{0}, {1}", reader["LastName"], reader["FirstName"] );
}

/* code:  */
public delegate object DataReaderHandler( IDataReader reader, ReaderHandlerEventArgs args );
	
public struct SprocParam
{
	private string _name;
	private object _value;

	public SprocParam( string name, object value )
	{
		_name = name;
		_value = value;
	}

	public string Name
	{
		get { return _name; }
	}

	public object Value
	{
		get { return _value; }
	}
}
	
public class ReaderHandlerEventArgs : EventArgs
{
	private object _data;

	public ReaderHandlerEventArgs()
	{
	}

	public ReaderHandlerEventArgs( object data )
	{
		_data = data;
	}

	public object Data
	{
		get { return _data; }
		set { _data = value; }
	}
}

public class DataHelper
{
	private string _connStr;

	public DataHelper( string connectionString )
	{
		_connStr = connectionString;
	}

	public object ExecuteSprocHandleReader( string sproc, DataReaderHandler handler, params SprocParam[] sprocParameters )
	{
		return ExecuteSprocHandleReader( sproc, handler, null, sprocParameters );
	}
		
	public object ExecuteSprocHandleReader( string sproc, DataReaderHandler handler, object handlerArgs, params SprocParam[] sprocParameters )
	{
		IDbConnection conn = null;
		try
		{
			// can we instantiate the connection type from Windsor/Spring ???
			using( conn = new SqlConnection( _connStr ) )
			{
				conn.Open();
				using( IDbCommand cmd = conn.CreateCommand() )
				{
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.CommandText = sproc;
					foreach( SprocParam param in sprocParameters )
					{
						IDbDataParameter p = cmd.CreateParameter();
						p.ParameterName = param.Name;
						p.Value = param.Value;
						cmd.Parameters.Add( p );
					}
						
					using( IDataReader reader = cmd.ExecuteReader() )
					{
						return handler( reader, new ReaderHandlerEventArgs( handlerArgs ) );
					}
				}
			}
		}
		finally
		{
			if( conn != null && conn.State != ConnectionState.Closed )
			{
				conn.Close();
			}
		}
	}
}