How do I store multiple values from database in a array

Question

0.00/5 (No votes)

See more:

So I have got 3 tables, 1 of them is a relation table with 2 foreign keys set up like so:

Recipes---FKRecipesIngredients---Ingredients

Say that recipe with ID 1 has 3 ingredients assigned to it, then the FK table would look like so:

RecipeID 1 -- IngredientID 1
RecipeID 1 -- IngredientID 2
RecipeID 1 -- IngredientID 3

Now I want to delete recipe with id 1 and all the ingredients with it, meaning first I have to see which ingredients are assigned to recipeID 1... how do I get those ID's in this case 1, 2 and 3 from the FKRecipesIngredients table and store them in a int[] array so that I can use those values to delete those from the Ingredient table and eventually delete the recipe from the Recipe table?

Posted 5-Jul-15 11:27am

R3spect

Add a Solution

Comments

PIEBALDconsult 5-Jul-15 17:33pm

You like doing things the hard way?

R3spect 5-Jul-15 17:38pm

Is there an easier way, I am new to using sqldatabases within c#

PIEBALDconsult 5-Jul-15 17:41pm

Learn to use a DELETE with a JOIN.

R3spect 5-Jul-15 18:12pm

But with join you can only delete data from 2 tables right? not 3... so it would be impossible for me to delete Recipe 1 from the RecipeTable, all the RecipeIDs with value 1 from FKRecipesIngredients and all the ingredients tied to the recipeID from the IngredientTable

PIEBALDconsult 5-Jul-15 18:46pm

There would be three DELETE statements; one for each table. But they could be in one batch/transaction.

Suvendu Shekhar Giri 5-Jul-15 19:43pm

Why not delete from the FK table first and PK table in the last. I don't think you need to store these in array.

R3spect 6-Jul-15 2:46am

Well I don't know how I would do it without storing the values in an array, how am I going to know which ingredients to delete from the ingredient table if I don't have them stored in a array or whatever

Suvendu Shekhar Giri 6-Jul-15 3:51am

Something like -
delete Ingredients where IngredientID in (select IngredientID from FKRecipesIngredients where RecipeID=@RecipeID)

delete FKRecipesIngredients where RecipeID=@RecipeID

delete Recipes where RecipeID=@RecipeID

Does it make sense?

R3spect 6-Jul-15 15:13pm

Makes perfect sense I put it all in a code and it works perfectly fine :) thanks for showing pointing me in the right direction. It was mainly this line that helped me: delete Ingredients where IngredientID in (select IngredientID from FKRecipesIngredients where RecipeID=@RecipeID)

I don't much about Sql queries and it was hard to find what I needed on the internet, thanks a lot! you saved me!

Suvendu Shekhar Giri 6-Jul-15 18:33pm

Glad to know that it helped :)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

st_249 · Accepted Answer · 2015-07-07T01:39:00

Solution 2

If you link and cascade the relation in sql, when you delete the primary key it will delete all the relations automatically.

Posted 7-Jul-15 1:39am

st_249