Asp.Net: returning a DataSet from a class

Question

I decided to start another thread based on the answers I received in this thread: Asp.Net: returning the reader from the class

I was returning the reader, but the participants suggested I better return the data set instead, and also try to separate the data access level from the presentation level.

This is what I still have: // class methods

   public DataSet GetSuppliers()
    {
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("con_spSuppliersList", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@blogid", HttpContext.Current.Request.QueryString["p"]);

        return FillDataSet(cmd, "SuppliersList");
    }

    //my FillDataSet method

    private DataSet FillDataSet(SqlCommand cmd, string tableName)
    {
     SqlConnection conn = new SqlConnection(connectionString);

     cmd.Connection = conn;
     SqlDataAdapter adapter = new SqlDataAdapter(cmd);

     DataSet ds = new DataSet();
         try
         {
             conn.Open();
             adapter.Fill(ds, tableName);
         }
         finally
         {
             conn.Close();
         }
     return ds;

    }

// on my ascx page, I call the method as follows:

protected void Page_Load(object sender, EventArgs e)
{

    //instantiate our class
    MyClass DB = new MyClass();

    // grab the table of data
    DataTable dt = DB.GetSuppliers().Tables["SuppliersList"];

    //loop through the results
    foreach (DataRow row in dt.Rows)
    { 
        this.supplierslist.InnerHtml += Server.HtmlEncode(row["Address"].ToString()) + "<br/>";
        this.supplierslist.InnerHtml += "<b>Tel: </b>" + Server.HtmlEncode(row["Telephone"].ToString()) + "<p/>";

    }


}

}

Would anyone like to suggest improvements?

Is my loop a "data level" or a "presentation level", should the loop be inside the class, and I just return the formatted string set in the dataset?

Thanks for the great advice.

Answer 1

Typed DataSet , , row["Address"], object.Address .

DataSets , , , - . - , , , .

- DAL ( ):

//Also pass in the blogID dont have the DAL get the value from the UI layer..
    //make the UI layer pass it in.
    public IList<Supplier> GetSuppliers(string connectionString, int blogID)
    {
        IList<Supplier> suppliers = new List<Supplier>();
        //wrap with the using statements
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("con_spSuppliersList", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@blogid", blogID);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    suppliers.Add(new Supplier 
                    { 
                        Address = reader.GetString(0),
                        Telephone = reader.GetString(1)
                    });
                }
            }
        }

        return suppliers;
    }
}

public class Supplier
{
    //I would have Address an object....but you have as string
    //public Address Address { get; set; }
    public string Address { get; set; }
    public string Telephone { get; set; }
}

//Example if you went with Address class...
    public class Address
    {
        //Whatever you want in the address
        public string StreetName { get; set; }
        public string Country { get; set; }
        public string Region { get; set; }
        public string City { get; set; }

}

Answer 2

, , Dispose() SqlConnection. - using, . :

private DataSet FillDataSet(SqlCommand cmd, string tableName)
{
     using(SqlConnection conn = new SqlConnection(connectionString))
     {
         cmd.Connection = conn;
         SqlDataAdapter adapter = new SqlDataAdapter(cmd);

         DataSet ds = new DataSet();
         try
         {
             conn.Open();
             adapter.Fill(ds, tableName);
         }
         finally
         {
             conn.Close();
         }
         return ds;
    }
}

Answer 3

, foreach Page_Load, (), IMO, , .

, foreach HTML (, asp: Repeater, DataList GridView). datatable , ( ASCX). . .

As a general note: you can find many guides at www.asp.net, for example. about access to data: http://www.asp.net/%28S%28pdfrohu0ajmwt445fanvj2r3%29%29/learn/data-access/