none
SqlDataReader Update???

    Question

  • Can u use the SqlDataReader to update data in sql server using a stored proc??

    by like if(dr.Read())

    _Property = dr["value"].ToString()

    Thursday, September 28, 2006 2:26 PM

Answers

  • As name implies its only for reading purposes not for the insertion and updation in the database.

    Cheers ;)

    Thursday, September 28, 2006 2:34 PM
  • no you can't, SQLDataReader is a fast forward only reader. The only way to do so is to create a proper SQLCommand to insert data/update data or call SPROCS giving it the parameters
    Thursday, September 28, 2006 5:12 PM

All replies

  • As name implies its only for reading purposes not for the insertion and updation in the database.

    Cheers ;)

    Thursday, September 28, 2006 2:34 PM
  • no you can't, SQLDataReader is a fast forward only reader. The only way to do so is to create a proper SQLCommand to insert data/update data or call SPROCS giving it the parameters
    Thursday, September 28, 2006 5:12 PM
  • Really after a long time!

    But SQLDataReader has a property RecordsAffected and it is documented as "Gets the number of rows changed, inserted, or deleted by execution of the Transact-SQL statement." that gives an impression that the class can be used to execute DML statements.

    Can somebody tell whether the class can be used for DML statements as well or should provider throw an exception.

    Tuesday, July 20, 2010 12:14 PM
  • RecordsAffected in this case may return number of rows that reader will read based on provided SQL statement. Technically when you call ExecuteReader and provide SqlCommand object for it, you can specify any type of SQL statement or stored procedure that insert, deletes some rows, and it will be executed successfully, but the SqlDataReader will not return any result from execution. I, personally, do not see why someone would need to use SqlDataReader to update rows or modify structure of database if there are better ways to do this.


    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, July 21, 2010 10:11 AM
  • Thank you for your reply. I fully agree with you that there are better ways of executing DML statements instead of using the reader method. The reason for my dilemma as to whether the reader method should be allowed to execute DML statements is that I have to do more coding in implementing my .NET data provider to support the DML statements! If some application programmer used SQL server provider and used the method for such statements, the programmer may expect the same behavior from my provider for the method. So, I would like to know whether there is any specific reason why SQL Server provider and the base class in common name space  were designed to allow DML statements through the reader method. Any reply will be greately appreciated.

    Wednesday, July 21, 2010 11:41 AM
  • In terms of SQL statements, I do not think reader has any limitations and you can execute any kind of SQL statement
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, July 22, 2010 10:20 AM
  • I have found that Update methods of DbDataAdapter class use reader methods of DbCommand class to execute DML statements! Looks like either I have to write my own update methods of the adapter class or support execution of DML statements through the reader methods of the command class. 

    Friday, August 20, 2010 7:43 AM