OOP in C# connecting to SQL Server Database

I have an application I am setting up which has a Web
Services Layer,
Business Logic Layer, Data Access Layer and a Data Layer.

In my Data Layer I have a class that represents an Asset
such as a
computer or laptop. Within that class I have some common
attributes and
properties than I have some attributes and properties
representing
other objects such as Location and User

so I have

public class Asset
{

public Asset(System.Data.DataRow row)
{
this.FromDataRow(row);
}

private int assetId;
private Location location;
private User createdByUser;

public int AssetId
{
get { return assetId; }
set { assetId = value; }
}

public Location Location
{
get { return location; }
set { location = value; }
}

public User CreatedBy
{
get { return createdByUser; }
set { createdByUser = value; }
}

private void FromDataRow(System.Data.DataRow row)
{
this.assetId = Convert.ToInt32(row["AssetId"]);
this.location = (Location)row["Location"];
this.createdByUser = (User)row["CreatedByUser"];
}
}

Then I have a button in which a user clicks it will then go
through a
web service through the business logic and get to the Data
Access Layer

Which gets the following method:
public Data.AssetObjects
GetAssetsByStatusIdUsingObjects(Int32
assetStatusId)
        {
            SqlConnection con = null;
            SqlCommand cmd = null;
            SqlDataAdapter da = null;

            try
            {
                con = new
SqlConnection(Common.Configuration.strConn);
                con.Open();

                cmd = new SqlCommand();
                cmd.CommandText =
"usp_AssetsGetAllByStatusId";
                cmd.CommandType =
CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.AddWithValue("AssetStatusId",
assetStatusId);

                da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();

                da.Fill(dt);

                Data.AssetObject datAsset = null;
                Data.AssetObjects datAssets = new
Data.AssetObjects();

                foreach (DataRow drow in dt.Rows)
                {
                    datAsset = new Data.AssetObject(drow);
                    datAssets.Add(datAsset);
                }

                return datAssets;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }

                con = null;
                cmd = null;
            }
        }

Which returns data using the following Stored Procedure:
ALTER PROCEDURE dbo.usp_AssetsGetAllByStatusId
	(
	AssetStatusId int
	)
AS
	SET NOCOUNT ON

	SELECT
		Assets.AssetId,
		Assets.SerialNumber,
		Assets.AssetTagNumber,
		Assets.InventoryId,
		Assets.AssetTypeId,
		AssetTypes.AssetTypeName,
		Assets.AssetStatusId,
		AssetStatuses.AssetStatusName,
		Assets.ManufacturerId,
		Manufacturers.ManufacturerName,
		Assets.Model,
		Assets.LocationId,
		Locations.LocationName,
		Assets.BuildingId,
		Buildings.BuildingName,
		Assets.RoomId,
		Rooms.RoomName,
		Rooms.Faculty,
		Assets.EndUserId,
		(EndUsers.FirstName + ' ' + EndUsers.LastName) AS
EndUserName
	FROM
		AssetTypes RIGHT JOIN
		Assets LEFT JOIN
		Users AS EndUsers ON Assets.EndUserId = EndUsers.UserId
LEFT JOIN
		Locations ON Assets.LocationId = Locations.LocationId LEFT
JOIN
		Buildings ON Assets.BuildingId = Buildings.BuildingId LEFT
JOIN
		Rooms ON Assets.RoomId = Rooms.RoomId LEFT JOIN
		Manufacturers ON Assets.ManufacturerId =
Manufacturers.ManufacturerId
LEFT JOIN
		AssetStatuses ON Assets.AssetStatusId =
AssetStatuses.AssetStatusId
ON AssetTypes.AssetTypeId = Assets.AssetTypeId
	WHERE
		Assets.AssetStatusId = AssetStatusId


	RETURN



In doing this I get the following error:
Column 'Location' does not belong to table

Can anyone provide some information in how to do this
correctly or why
I am getting this error. 

Thanks to all,
SBProgrammer

In your query you never select the column Location but you
are trying
to extract it from the recordset

 this.location = (Location)row["Location"]; 

Have fun!

Marty

But Location is another object which contains two properties
LocationId
and LocationName.

Ok, some programming basics. Let us brake the statement in
the simplest
logical operations

Retrieves the value of "location" from a recordset
row
Casts that value to a Location object
Assigns the casted value to a private field called location.

So, the first logical statement is invalid because there is
no
"location" cell in that row. The names of cells in
that row are
Assets.AssetId,
                Assets.SerialNumber,
                Assets.AssetTagNumber,
                Assets.InventoryId,
                Assets.AssetTypeId,
                AssetTypes.AssetTypeName,
                Assets.AssetStatusId,
                AssetStatuses.AssetStatusName,
                Assets.ManufacturerId,
                Manufacturers.ManufacturerName,
                Assets.Model,
                Assets.LocationId,
                Locations.LocationName,
                Assets.BuildingId,
                Buildings.BuildingName,
                Assets.RoomId,
                Rooms.RoomName,
                Rooms.Faculty,
                Assets.EndUserId,
                (EndUsers.FirstName + ' ' +
EndUsers.LastName) AS
EndUserName

Any other name will lead to the error you are recieving.
Also even if you have a cell called Location this will
return a STRING.
Casting it to Location will no work unless you do an
explicit cast
overload.

Marty

how would one go about getting data from a database using
such objects.
I am reading the book Beginning C# Objects from Concepts to
Code By
Jacquie Barker and Grant Palmer published by Apress.

And they represent objects in such a manner that in object
is an
attribute/property in another object


So that Location being an object is an attribute/property of
Asset, so
how is one supposed to get asset data from the database with
location
data in one Stored Procedure?

Or am I supposed to use two stored procedures?

public class Asset
{
   private Location location;

   public Asset()
   {
     //loads the dataset

    //fills the object
    location = new Location(row["LocationID"],
row["LocationName"]);
   }

   public Location Location
   {
      get   {return location;}
   }
}

internal class Location
{
   private int id;
   private string name;

   internal Location(int id, string name)
   {
      this.id = id;
      this.name = name;
   }

   public string ID
   {
      get   {return id;}
   }

   public string Name
   {
      get   {return name;}
   }
   
}

Thanks for the reply martin, that provides more insight to
my problem,
but now when I use a stored procedure to get assets do I
just specify
the assets object to represent the data and if so how does
it now
explain the location data.

i don't really understand what you mean but if you mean how
you access
the location information, then it would be
Asset asset = new Asset();
Response.Write(asset.Location.ID);

Try to be clearer.

Marty