Quote
I need to fetch imageUrl and productName for products using productId.
OK, that's easy enough.
select imageUrl, productName
from table1
where product_id ...
Quote
The problem is the the productIds are not continues (so i can't user range).
You should
never rely on ID numbers being consecutive.
Unique? Yes.
Consecutive? No.
Quote
This means that if I have 100 products I will need to have 100 calls per product. Is there any option to do that instead of making 100 calls?
I don't understand the "100 calls per product" bit. To display 100 products you still only need one query, possibly using the "limit" and "offset" clauses:
select imageUrl, productName
from table1
limit 100 offset 100
As you suspect, issue many queries for every product is going to be appallingly bad for performance.
Quote
2. I have an order object (which includes 4 sub arrays and other class members) which I want to save in the db (C# class). I don't need to do select on that record. Only store and and fetch it.
That suggests that you'll only have one Order. Ever. At any time. (Only one User of your application?)
Aim a
little higher. :-)
As soon as you admit you need more than one of these records, then you need to be able to query it. Do yourself a favour and do it right, from the outset.
Read up on Data Normalisation.
An Order will generally be made up of a number of Order "lines"; that's two Tables right there. The more detail your order includes, the more tables you might need. Each of your four "sub-arrays" represent a child table in the database, linked to the Order.
Regards, Phill W.