0

I have an object place with some properties (name, longitude, latitude, etc) and one of the properties is an object "horizon".

Horizon had a Dictionary <float,float>(set of points azimut-height in the horizon of the place)

I want to build a table to store the object "place", and I have troubles with the object "horizon"

Since I don't know how much points there are in the dictionary, I can't just build a column for each point.

So I think I have to create another table "dbo.horizons" with the columns

  • placeName -varChar
  • azimut - real
  • height - real

and then use JOIN to select all the point

but I don't understand how to build the command

If I take a command like that:

SELECT places.name, places.longitude, places.latitude...,                 
horizon.azimut, horizon.height         
FROM places  LEFT JOIN dbo.horizons          
ON places.name = dbo.horizons.namePlace

how I read it?

I use dataRedaer.Read() to read a row in the database.

How I get all the points and build only one place?

And the same question in the insert, how I build an INSERT command to insert one place with horizon

Thanks

chmouel kalifa
  • 129
  • 2
  • 11

1 Answers1

1

There are lots of ways, but let's go with the way you are currently doing things.

With the query you have in your question, you will get multiple rows for each place, because your datareader will have one row for every horizon that is associated with a place. So you'll want to keep the place in a variable and check it every time the datareader loops to a new row to see if this is the same Place. If it is, then just add the Horizon to the current Place. If it has changed, then you know to start a new Place.

As for the INSERT, you are going to have to do a multi-step operation. You cannot insert into two tables with a single command. So you will INSERT the Place into the Place table, and then insert each of the Horizons into the Horizon table.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • 1
    To prevent future problems, you should also add an `ORDER BY` to your query to guarantee that the place names are ordered together. Otherwise, if someone makes changes to your SQL table, the query optimizer might decide to send your results back in a different order. – DeadZone Sep 21 '15 at 20:19
  • You can, but that's a different (and more complex) SQL statement. This SO question shows how it's done: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string – Tab Alleman Sep 21 '15 at 20:19
  • Thank you very much. I think I prefer to be simple and don't use the join at all, just do another query - `SELECT * FROM dbo.horizons` and add the horizon to each place. in performance I think it's better because in join the database pass on the data - `horizons.Count*places.Count` times. am I right? – chmouel kalifa Sep 21 '15 at 20:37
  • The performance difference is going to be pretty negligible, unless you're dealing with 10k's of rows, or 10k's of users simultaneously using this page. – Tab Alleman Sep 22 '15 at 13:40