SqlDataAdapter, DataSet and updates

[Guest]

I am currently on my first ASP.Net application (Spell Checker), and have some
confusion/questions regarding the ADO.Net part of it.

I have a method that uses the SqlDataAdapter to fill a DataSet with results
from a query. The DataSet contains a table of blocks of text from many web
applications which is then returned from the method.

As the spell checking application checks words, the DataSet is updated with
any corrected words. So at the end of the spell checking the DataSet no
longer contains just the original text blocks. I have now found out that the
SqlDataAdapter.UpdateCommand will not work - the data adapter must have the
original DataSet.

If my Sql method returns the DataAdpater instead of the DataSet, is there
still an active connection to Sql Server?

If the connection is closed, will it work if I maintain the state of the
DataAdapter (and so the DataSet it filled), and thus use the .UpdateCommand
in the end?

Is there a better way to do this without recreating the original DataSet at
the end and merging the original and updated?

Thank you.

 

[Sahil Malik]

I presume that you are using DataAdapter.Update without specifying an
UpdateCommand, and given your table structure, thats a problem.

Let me answer your questions one by one --

If my Sql method returns the DataAdpater instead of the DataSet, is there
still an active connection to Sql Server?

If the connection is closed, will it work if I maintain the state of the
DataAdapter (and so the DataSet it filled), and thus use the ..UpdateCommand
in the end?

DataAdapter will preserve the state of the connection i.e. if before fill
the connection was closed, it will open it, fill, and then close it. If it
was open already, it will simply fill. DataReader on the other hand requires
an active open connection for as long as how you actively use it (read data
from it).

Is there a better way to do this without recreating the original DataSet at
the end and merging the original and updated?

Yes there is - use your own UpdateCommand, specify explicitly what you want
the DataAdapter to do !!

- Sahil Malik
You can reach me thru my blog at
http://www.dotnetjunkies.com/weblog/sahilmalik

 

[Guest]

I actually did create my own UPDATE commands (e.g. "UPDATE CR_Comment SET
comment = @pComment WHERE commentid = @pCommentid"). The following code sets
the parameters for all of the UPDATEs I am using.

// Initialize a SqlDataAdapter.
sqlDataAdapter = new SqlDataAdapter();
// Get UPDATE scripts.
SqlCommand sqlUpdateCommand = new SqlCommand(SqlQueries.UpdateComments(),
sqlConnection);
// CommentId
sqlUpdateCommand.Parameters.Add(new SqlParameter("@pCommentId",
SqlDbType.Int));
sqlUpdateCommand.Parameters["@pCommentId"].SourceVersion =
DataRowVersion.Current;
sqlUpdateCommand.Parameters["@pCommentId"].SourceColumn = "CommentId";
// Unique Response
sqlUpdateCommand.Parameters.Add(new SqlParameter("@pCustomResponse",
SqlDbType.Text));
sqlUpdateCommand.Parameters["@pCustomResponse"].SourceVersion =
DataRowVersion.Current;
sqlUpdateCommand.Parameters["@pCustomResponse"].SourceColumn =
"CustomResponse";
// Generic Response
sqlUpdateCommand.Parameters.Add(new SqlParameter("@pGenericResponse",
SqlDbType.Text));
sqlUpdateCommand.Parameters["@pGenericResponse"].SourceVersion =
DataRowVersion.Current;
sqlUpdateCommand.Parameters["@pGenericResponse"].SourceColumn =
"GenericResponse";
//Assign the SqlCommand to the UpdateCommand property of the SqlDataAdapter.
sqlDataAdapter.UpdateCommand = sqlUpdateCommand;

When the ".UpdateCommand" line is reached, the error stating that a SELECT
statement was not first executed is raised. This was before I looked into
preserving the SqlDataAdapter object. Am I on the right track by preserving
the object? Do you have a good reference I could use?

Second, just to confirm the data connection state with the DataAdapter -
when the object is not Disposed or set to null, the connection to the
database is closed?

Thank you for your time.
TBerry

 

[Guest]

I actually did create my own UPDATE commands (e.g. "UPDATE CR_Comment SET
comment = @pComment WHERE commentid = @pCommentid"). The following code sets
the parameters for all of the UPDATEs I am using.

// Initialize a SqlDataAdapter.
sqlDataAdapter = new SqlDataAdapter();
// Get UPDATE scripts.
SqlCommand sqlUpdateCommand = new SqlCommand(SqlQueries.UpdateComments(),
sqlConnection);
// CommentId
sqlUpdateCommand.Parameters.Add(new SqlParameter("@pCommentId",
SqlDbType.Int));
sqlUpdateCommand.Parameters["@pCommentId"].SourceVersion =
DataRowVersion.Current;
sqlUpdateCommand.Parameters["@pCommentId"].SourceColumn = "CommentId";
// Unique Response
sqlUpdateCommand.Parameters.Add(new SqlParameter("@pCustomResponse",
SqlDbType.Text));
sqlUpdateCommand.Parameters["@pCustomResponse"].SourceVersion =
DataRowVersion.Current;
sqlUpdateCommand.Parameters["@pCustomResponse"].SourceColumn =
"CustomResponse";
// Generic Response
sqlUpdateCommand.Parameters.Add(new SqlParameter("@pGenericResponse",
SqlDbType.Text));
sqlUpdateCommand.Parameters["@pGenericResponse"].SourceVersion =
DataRowVersion.Current;
sqlUpdateCommand.Parameters["@pGenericResponse"].SourceColumn =
"GenericResponse";
//Assign the SqlCommand to the UpdateCommand property of the SqlDataAdapter.
sqlDataAdapter.UpdateCommand = sqlUpdateCommand;

When the ".UpdateCommand" line is reached, the error stating that a SELECT
statement was not first executed is raised. This was before I looked into
preserving the SqlDataAdapter object. Am I on the right track by preserving
the object? Do you have a good reference I could use?

Second, just to confirm the data connection state with the DataAdapter -
when the object is not Disposed or set to null, the connection to the
database is closed?

Thank you for your time.
TBerry

****************************