converting tableAdaptor queries into stored procs

[Andy B]

Is there an easy way to convert tableAdaptor queries into stored procs
without messing up the dataTables in the dataSet or losing the queries
themselves?

 

[Nicholas Paldino [.NET/C# MVP]]

Andy,

Well, you will have to change the table adapters to work with the stored
procedure now and not the queries themselves.

This means that you will have to take the queries that are in the table
adapters and then move them to your database, parameterizing them correctly
(in the case of insert, delete, and update queries) and then changing the
commands on the table adapter to call the appropriate stored procedures.

And all of this you have to do by hand.

Do you have a specific reason you want to make them into stored
procedures? Unless you are adding more complex logic to the operation, you
really aren't getting that much of a benefit.

 

[Andy B]

The queries are quite involved really. The ones I have in the tableAdaptors
only go to the point of subqueries. The insert, update and delete queries
are a different story. The biggest problem would be the insert and update
queries. We will start off with delete since its easier to deal with. I have
to delete rows from 2 different tables: NewsArticles and NewsHeaders. The
newsHeaders table has a reference to the newsId and CategoryId so to delete
the categories for a news article it would be: delete from NewsHeaders where
NewsId=@NewsID. Then move on to delete the news article itself: delete from
NewsArticles where NewsID=@NewsID. Now for insert and update. I will cover
insert since update is close enough to insert anyways. I have to take a
NewsCategoryCollection I created in C# and loop through it inserting the
CategoryID and the associated NewsId into NewsHeaders (there can be multiple
categories for a news article). I have this NewsCategoriesCollection linked
to a NewsArticle object that has the NewsID in it. After the
NewsCategoryCollection is inserted into NewsHeader, I insert the rest of the
news article into NewsArticles table. Sounds complicated and maybe I'm
explaining it the wrong way but hope it helps ....If you need to know more
let me know...

Andy,

Well, you will have to change the table adapters to work with the
stored procedure now and not the queries themselves.

This means that you will have to take the queries that are in the table
adapters and then move them to your database, parameterizing them
correctly (in the case of insert, delete, and update queries) and then
changing the commands on the table adapter to call the appropriate stored
procedures.

And all of this you have to do by hand.

Do you have a specific reason you want to make them into stored
procedures? Unless you are adding more complex logic to the operation,
you really aren't getting that much of a benefit.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Is there an easy way to convert tableAdaptor queries into stored procs
without messing up the dataTables in the dataSet or losing the queries
themselves?