Solved

DataAdapter, stored procedures and parameters

Posted on 2014-03-11
2
61 Views
Last Modified: 2016-06-04
I have a few questions regarding stored procs and the SqlDataAdapter:

When I run the adapter's Update-method, I understand it will call the specified Insert-, Update- and Delete-commands where appropriate, based on the staus of the table's rows.
-If these commands are set to stored procedures, does it pass parameters for each column in the table with the correct values, or does it pass the table row in question?
-Do you have to specify all parameters manually, or does the adapter know which values to pass?
-Is it possible to make a sp for, let's say Insert, without defining parameters for each column? If you pass the table row the sp should know both column names and values (or not)?

What I actually try to achieve, is to have one Insert-, one Update- and one Delete-stored proc to work on all tables in my database. From wherever in my app where I have to do som data handling, I would call a data handler to prepare necessary parameters and values before running adapter.Update().
Impossible, bad approach or brilliant?
0
Comment
Question by:Klasen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 39920307
If you use stored procedures you need to define the SqlCommand/parameters manually, so you can pick and choose which columns you want to pass to the procedure.

What you are suggesting is possible, but not really recommended. Having a single stored procedure for all of your updates/inserts/deletes means that you will be relying on dynamic SQL, and you will lose the benefits of caching and optimization for individual queries.
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question