How to loop datareader when passing to wcftestclient?

Question

When i trying to view more than one row in database from wcf, i just got one last row

i create this service

public dcTransaction SelectMasterTransaction(DateTime date1, DateTime date2)
    {
        dcTransaction dcTrans = new dcTransaction();
        string conString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
        using (con = new SqlConnection(conString))
        {
            SqlCommand cmd = new SqlCommand("spViewMasterTransaction", con);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter parameterDate1 = new SqlParameter();
            parameterDate1.ParameterName = "@date1";
            parameterDate1.Value = date1;
            cmd.Parameters.Add(parameterDate1);

            SqlParameter parameterDate2 = new SqlParameter();
            parameterDate2.ParameterName = "@date2";
            parameterDate2.Value = date2;
            cmd.Parameters.Add(parameterDate2);


            con.Open();

            SqlDataReader dtReader = cmd.ExecuteReader();
            while (dtReader.Read())
            {
                dcTrans.no_trans = int.Parse(dtReader["no_trans"].ToString());
                dcTrans.name = dtReader["name"].ToString();
                dcTrans.sum = int.Parse(dtReader["sum"].ToString());
                dcTrans.dates = DateTime.Parse(dtReader["dates"].ToString());
            }
        }
        return dcTrans;
    }

What i got here is, the wcftestclient show only the last record that i request too.. ex : from date 1/1/2010 to 1/1/2014 (the sql query is written in stored procedure in my sql server)

and then when i give wcftestclient a value 1/1/2010 to 1/1/2014, the wcftestclient just return last date record, i think it won't print the loop, what am i missing here? i think WHILE(dataReader.read()) is already loop right?

Here is my stored procedure in my sql server

 CREATE PROCEDURE spViewMasterTransaksi
@date1 datetime,
@date2 datetime

AS
BEGIN
select * from MasterTransaction WHERE date >= @date1 AND date <= @date2

END

when i execute my stored procedure via sql server it seems fine, it show the date that between the value i enter..

Answer 1

You aren't returning a list of objects. You do the loop, but each item overwrites the previous one's values.

You could look into yield return here and return an IEnumerable<dcTransaction>, or just do a classic List<T> and add on each pass through the loop.

public IEnumerable<dcTransaction> SelectMasterTransaction(DateTime date1, DateTime date2)
{
    string conString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (con = new SqlConnection(conString))
    using(SqlCommand cmd = new SqlCommand("spViewMasterTransaction", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter parameterDate1 = new SqlParameter();
        parameterDate1.ParameterName = "@date1";
        parameterDate1.Value = date1;
        cmd.Parameters.Add(parameterDate1);

        SqlParameter parameterDate2 = new SqlParameter();
        parameterDate2.ParameterName = "@date2";
        parameterDate2.Value = date2;
        cmd.Parameters.Add(parameterDate2);

        con.Open();

        using (SqlDataReader dtReader = cmd.ExecuteReader())
        {
            while (dtReader.Read())
            {
                dcTransaction dcTrans = new dcTransaction();

                dcTrans.no_trans = dtReader.GetInt32(0); // Index for "no_trans"
                dcTrans.name = dtReader.GetString(1); // Index for "name"
                dcTrans.sum = dtReader.GetInt32(2); // Index for "sum"
                dcTrans.dates = dtReader.GetDateTime(3); // Index for "dates"

                yield return dcTrans;
            }
        }
    }
}