SQLDataAdapter update to multiple tables through a single stored procedure

Question

 

Is this at all possible?

I've got a dataset with 5 related tables, and an elegant solution would be to make one call to update and write to all the tables through a single stored procedure.  The problem seems to be mapping the sourceColumn parameter in the slqparameter - it would be great if the column could be defined as <tablename>.<columnname>, or the parameter could be associated with a table directly.

Is anyone aware of a solution to this?

 

 

 

 

Answer 1

A data adapter would do this in one line of code, it make some overhead but takes care of a lot of posible coding errors and does not care about you changing table structures in future releases.

public int updateDataset(SqlDataAdapter dad, DataSet setD, string strTableName)

{

SqlCommandBuilder cb = new SqlCommandBuilder(dad);

cb.RefreshSchema();

try

{

return dad.Update(setD, strTableName);

....

 

Answer 2

abrewerton wrote:

Is this at all possible? I've got a dataset with 5 related tables, and an elegant solution would be to make one call to update and write to all the tables through a single stored procedure.  The problem seems to be mapping the sourceColumn parameter in the slqparameter - it would be great if the column could be defined as <tablename>.<columnname>, or the parameter could be associated with a table directly. Is anyone aware of a solution to this?

Hi,

Have you considered aliases in your field names when you retrieve your table? I mean you can alias your fieldname and preceed it with the table name so that you would have a unique column name to be used in the maaping of your source column...

Alias:

SELECT id AS Table1_Id FROM Table1
...

 

cheers,

Paul June A. Domag

Answer 3

yes