How can I return a Object with Data in it.

Question

I know this maybe simple and I know the code I am posting is wrong but I am getting close I think.

I am wanting to get the function below from the file pageDAL.cs to return the object page with the values for whatever pageID I pass in. If I do not need to use a DataSet or DataTable that is fine. What is the best way?

 public page getPage(int _pageID)
    {
      DataTable dt = new DataTable;
      using (SqlDataAdapter da = new SqlDataAdapter("select PageID,ParentID,CategoryID,Name,PageHTMLContent,NavigationText,TopMenu,SubMenu,DisplayOrder,Active from Page where PageID = " + _PageID, ConnectionStrings.StaceysCakes))
      {
        da.Fill(dt);
        dt.AsEnumerable().Select(r => page)
      {
        page myPageOBJ = new page();
        myPageObj.PageID = r.Field<int>("PageID"),
        ParentID = r.Field<int>("ParentID"),
        CategoryID = r.Field<int>("CategoryID"),
        Name = r.Field<string>("Name"),
        PageHTMLContent = r.Field<string>("PageHTMLContent"),
        NavigationText = r.Field<string>("NavigationText"),
        TopMenu = r.Field<bool>("TopMenu"),
        SubMenu = r.Field<bool>("SubMenu"),
        DisplayOrder = r.Field<int>("DisplayOrder"),
        Active = r.Field<bool>("Active"),
      });
      }
     return page;
    }

page.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace sc2.Models.page
{  
  public class page
  {
    public int PageID { get; internal set; }
    public int ParentID { get; internal set; }
    public int CategoryID { get; internal set; }
    public string Name { get; internal set; }
    public string PageHTMLContent { get; internal set; }
    public string NavigationText { get; internal set; }
    public bool TopMenu { get; internal set; }
    public bool SubMenu { get; internal set; }
    public int DisplayOrder { get; internal set; }
    public bool Active { get; internal set; }

    public page()
    {
    }

  }
}

pageBLL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
//using sc2.Models;

namespace sc2.Models.page
{
  public class pageBLL
  {

    pageDAL myPageDAL = new pageDAL();


    public pageBLL(){
    }

    public page getPage()
    {
      page PageOBJ = new page();
      return PageOBJ;
    }

    public page getPage(int _pageID)
    {
      return myPageDAL.getPage(_pageID);
    }

    public string save(page _page)
    {
      return myPageDAL.save(_page);
    }


    public List<page> Select()
    {
      return (myPageDAL.Select());
    }

    public List<page> Select(string _OrderBy)
    {
      return (myPageDAL.Select(_OrderBy));
    }

    public DataSet Get(int _PageID)
    {
      return (myPageDAL.Get(_PageID));
    }

    public void DeletePage(int _PageID)
    {
      myPageDAL.DeletePage(_PageID);
    }


  }
}

pageDLL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;

namespace sc2.Models.page
{
  public class pageDAL
  {

    public pageDAL()
    {
    }

    // List Order By
    public List<page> Select(string _OrderBy)
    {
      string sqlStatement = _OrderBy;
      return All(sqlStatement);
    }


    // Select List of Objects
    public List<page> Select()
    {
      string sqlStatement = "DisplayOrder";
      return All(sqlStatement);
    }

    // Return List of Objects
    public List<page> All(string _sqlStatement)
    {
      var sqlResults = new DataTable();
      string sqlStatement = "select PageID,ParentID,CategoryID,Name,PageHTMLContent,NavigationText,TopMenu,SubMenu,DisplayOrder,Active from page";
      if (!String.IsNullOrEmpty(_sqlStatement))
      {
        sqlStatement += " Order By " + _sqlStatement;
      }
      using (SqlConnection conn = new SqlConnection(ConnectionStrings.StaceysCakes))
      {
        using (SqlCommand command = new SqlCommand(sqlStatement, conn))
        {
          var adapter = new SqlDataAdapter(command);
          adapter.Fill(sqlResults);
        }
      }

      return sqlResults.AsEnumerable().Select(r => new page()
      {
        PageID = r.Field<int>("PageID"),
        ParentID = r.Field<int>("ParentID"),
        CategoryID = r.Field<int>("CategoryID"),
        Name = r.Field<string>("Name"),
        PageHTMLContent = r.Field<string>("PageHTMLContent"),
        NavigationText = r.Field<string>("NavigationText"),
        TopMenu = r.Field<bool>("TopMenu"),
        SubMenu = r.Field<bool>("SubMenu"),
        DisplayOrder = r.Field<int>("DisplayOrder"),
        Active = r.Field<bool>("Active"),
      }).ToList();


    }

    public page getPage(int _pageID)
    {
      DataTable dt = new DataTable;
      using (SqlDataAdapter da = new SqlDataAdapter("select PageID,ParentID,CategoryID,Name,PageHTMLContent,NavigationText,TopMenu,SubMenu,DisplayOrder,Active from Page where PageID = " + _PageID, ConnectionStrings.StaceysCakes))
      {
        da.Fill(dt);
        dt.AsEnumerable().Select(r => page)
      {
        page myPageOBJ = new page();
        myPageObj.PageID = r.Field<int>("PageID"),
        ParentID = r.Field<int>("ParentID"),
        CategoryID = r.Field<int>("CategoryID"),
        Name = r.Field<string>("Name"),
        PageHTMLContent = r.Field<string>("PageHTMLContent"),
        NavigationText = r.Field<string>("NavigationText"),
        TopMenu = r.Field<bool>("TopMenu"),
        SubMenu = r.Field<bool>("SubMenu"),
        DisplayOrder = r.Field<int>("DisplayOrder"),
        Active = r.Field<bool>("Active"),
      });
      }
     return page;
    }

    // (DataSet) get
    public DataSet Get(int _PageID)
    {
      using (SqlDataAdapter da = new SqlDataAdapter("select PageID,ParentID,CategoryID,Name,PageHTMLContent,NavigationText,TopMenu,SubMenu,DisplayOrder,Active from Page where PageID = " + _PageID, ConnectionStrings.StaceysCakes))
      {
        DataSet ds = new DataSet();
        da.Fill(ds, "Pages");
        return (ds);
      }
    }

    // Save
    public string save(page _page)
    {
      string errorMessage = "";

      if (_page.CategoryID == 0)
      {
        InsertPage(_page.Name, _page.PageHTMLContent, _page.NavigationText, _page.TopMenu, _page.SubMenu, _page.DisplayOrder, _page.Active);
      }
      else
      {
        UpdatePage(_page.CategoryID, _page.Name, _page.PageHTMLContent, _page.NavigationText, _page.TopMenu, _page.SubMenu, _page.DisplayOrder, _page.Active);
      }
      return errorMessage;
    }


    // Insert Page
    public string InsertPage(string _Name, string _PageHTMLContent, string _NavigationText, bool _TopMenu, bool _SubMenu, int _DisplayOrder, bool _Active)
    {
      SqlConnection myConnection = new SqlConnection(ConnectionStrings.StaceysCakes);
      string SqlStatement = "insert into Page (Name,PageHTMLContent,NavigationText,TopMenu,SubMenu,DisplayOrder,Active) values ('"
        + _Name + "','"
        + _PageHTMLContent + "','"
        + _NavigationText + "','"
        + _TopMenu + "','"
        + _SubMenu + "',"
        + _DisplayOrder + ",'"
        + _Active + "');";
      string errorMessage = "";
      try
      {
        SqlCommand myCommand = new SqlCommand(SqlStatement);
        myCommand.Connection = myConnection;
        myConnection.Open();
        myCommand.ExecuteNonQuery();
      }
      catch (Exception e)
      {
        errorMessage = "There was an error with the database insert.";
        Trace.Write("Database unavailable with Message: ", e.Message);
        Trace.Write("Stack Trace: ", e.StackTrace);
        // Throw the exception higer for logging and notification
        throw;

      }

      // Clean up any loose ends.
      finally
      {
        myConnection.Close();
      }

      // Return the error message if there is one.
      return errorMessage;

    }

    // Update Page
    public string UpdatePage
      (int _PageID,
       string _Name,
       string _PageHTMLContent,
       string _NavigationText,
       bool _TopMenu,
       bool _SubMenu,
       int _DisplayOrder,
       bool _Active)
    {
      string SqlStatement = "UPDATE Page SET Name='" + _Name + "', PageHTMLContent='" + _PageHTMLContent + "', NavigationText='" + _NavigationText + "', TopMenu='" + _TopMenu + "', SubMenu='" + _SubMenu + "', DisplayOrder=" + _DisplayOrder + ", Active='" + _Active + "' where PageID = '" + _PageID + "'";
      SqlConnection myConnection = new SqlConnection(ConnectionStrings.StaceysCakes);
      string errorMessage = "";
      try
      {
        SqlCommand myCommand = new SqlCommand(SqlStatement);
        myCommand.Connection = myConnection;
        myConnection.Open();
        myCommand.ExecuteNonQuery();
      }
      catch (Exception e)
      {
        errorMessage = "There was an error with the database update.";
        Trace.Write("Database unavailable with Message: ", e.Message);
        Trace.Write("Stack Trace: ", e.StackTrace);
        // Throw the exception higer for logging and notification
        throw;
      }

      // Clean up any loose ends.
      finally
      {
        myConnection.Close();
      }

      // Return the error message if there is one.
      return errorMessage;
    }

    // Delete Page
    public string DeletePage(int _PageID)
    {

      string SqlStatement = "Delete from Page where PageID = '" + _PageID + "'";

      SqlConnection myConnection = new SqlConnection(ConnectionStrings.StaceysCakes);
      string errorMessage = "";
      try
      {
        SqlCommand myCommand = new SqlCommand(SqlStatement);
        myCommand.Connection = myConnection;
        myConnection.Open();
        myCommand.ExecuteNonQuery();
      }
      catch (Exception e)
      {
        errorMessage = "There was an error with the database delete.";
        Trace.Write("Database unavailable with Message: ", e.Message);
        Trace.Write("Stack Trace: ", e.StackTrace);
        // Throw the exception higer for logging and notification
        throw;
      }

      // Clean up any loose ends.
      finally
      {
        myConnection.Close();
      }

      // Return the error message if there is one.
      return errorMessage;
    }
  }
}

Answer 1

Have a look at the SqlDataReader class. It is a lot simpler and has less overhead than the SqlDataAdapter and DataTable.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

Also, look here

var results = new List<page>();
string queryString = "SELECT PageID,ParentID...";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        page p= new page();
        p.PageID = reader["PageID"]; 
        //...
        results.Add(p);
    }
    reader.Close();
}
return results;

Answer 2

Don't you just need to return myPageOBJ, not page in your getPage() method?

You'll also need to precede your assignments to myPageOBJ members with "myPageObj.". You might also want to check out object initializers to make the setup of your page object cleaner.

Answer 3

You should avoid SQL Injection and look in to using an ORM like NHibernate or Fluent NHibernate that will be able to give you paged results, for example see: http://stackoverflow.com/questions/54754/how-can-you-do-paging-with-nhibernate.

It takes a little while to get up to speed with NHibernate but once you do it will make things much easier and simpler.