?
Solved

DataAdapter, stored procedures and parameters

Posted on 2014-03-11
2
Medium Priority
?
65 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 2000 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

777 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