Solved

DataAdapter, stored procedures and parameters

Posted on 2014-03-11
2
43 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
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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
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 create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now