locked
SQLDataAdapter update to multiple tables through a single stored procedure RRS feed

  • 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?

     

     

     

     

    Friday, September 8, 2006 5:14 AM

Answers

  •  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

    Wednesday, September 13, 2006 4:03 PM

All replies

  • 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);

    ....

     

    Monday, September 11, 2006 11:17 PM
  •  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

    Wednesday, September 13, 2006 4:03 PM
  • yes
    Wednesday, September 13, 2006 9:09 PM