using System;

using System.Data;

using System.Data.SqlClient;

using System.Xml;

​

public class RetrieveResult<T>

{

    public bool IsSuccessful { get; set; }

    public T Value { get; set; }    

}

​

public class stockItem

{

    public string name { get; set; }

    public double price { get; set; }

    public string size { get; set; }

}

public class Program

{

    public static void Main()

    {

        var retrieve = GetStockRecord( 1 );

        if( retrieve.IsSuccessful )

        {

            var stockItem = retrieve.Value;

            // blah blah blah blah

        }

    }

    public static RetrieveResult<stockItem> GetStockRecord( int stockItemNumber )

    {

        var query = "SELECT name, size, price FROM dbo.products WHERE itemnumber = @itemNumber";

        var result = new RetrieveResult<stockItem> { IsSuccessful = false };

        try

        {

            // previously "using ( stockConnection )" - that's fine, it was just causing compilation errors here because I wasn't sure what its source looked like

            using( var cn = new SqlConnection( "connectionString" ) )

            using( var cmd = new SqlCommand( query, cn ) )

            {

                cmd.Parameters.Add( new SqlParameter("@itemNumber", SqlDbType.Int) { Value = stockItemNumber } );

                using( var read = cmd.ExecuteReader() )

                using( var da = new SqlDataAdapter() )

                {

                    if( !read.HasRows )

                        return result; // we've initiated IsSuccessful to false, so this will return it as such

                    var dt = new DataTable();

                    da.Fill( dt );

                    // build out the stock item

                    // really consider using column names when getting row data, it is MUCH more readable

                    var row = dt.Rows[0];

                    var stockItem = new stockItem

                    {

                        name = row[ "name" ] as string,

                        price = double.Parse( row["price"] as string ),

                        size = row[ "size" ] as string

                    };

                    // set the result value and flag that it is successful

                    result.Value = stockItem;

                    result.IsSuccessful = true;

                    // return the finalized result

                    return result;

                }

            }

        }

        catch

        {

            return result; // IsSuccessful is still false at this point

        }

    }

}