Is possible to edit the table returned by SqlDataReader?

[Wing]

Hi all,

I execute a stored procedure in my C# code, assign the result to the
SqlDataReader object and display it with datagrid.

the question I like to ask, is possible to edit the datagrid output
(eg. delete a row in the datagrid) and update the change in the
corresponding table in the database?

thanks for your time.

Wing

 

[Guest]

No you cannot edit table returned by SQL reader you must have dataset as
datasource and then create adapter and call its Update method with dataset of
datagrid.

Hope this may help you...

 

[Guest]

For the most part no. A data reader is by definition a read-only, forward
view of the DB contents. You can't change the values it returns. However
you can enumerate the results of the data reader and build a new data source
(such as an array of custom objects) that you could then filter and bind to
the DataGrid.

Most people just find it easier to use a DataSet when supporting editable
DataGrids. That is the textbook answer to your common problem. That is one
of the reasons why DataSets were created to begin with. I'd recommend that
approach. You can then filter and sort your results as needed prior to
sending to the DataGrid.

FYI, there are 2 DataGrids; one in ASP.NET and one in WinForms. They are
quite different. In future posts specify which one you are talking about.
You should also post such questions in the WindowsForms or ASP.NET newsgroups
instead of this language specific group. The control works the same
irrelevant of the underlying language. In this group you will only get
answers from C# people.

Hope this helps,
Michael Taylor, MCP, MCAD - 6/29/05

 

[Mark Rae]

the question I like to ask, is possible to edit the datagrid output
(eg. delete a row in the datagrid) and update the change in the
corresponding table in the database?

Yes of course, but not through the underlying SqlDataReader object itself
because that is read-only.

You would need to include a button (or somesuch) in one of the columns of
the datagrid which would cause the page to postback, where you would delete
the record, requery the data and repopulate the datagrid. Not particularly
efficient, but perfectly feasible...

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Well a datareader is a readonly , forward only server side cursor, so the
short answer is no.

you should close the reader as soon as you finish to display it anyway,
otherwise the connection will be kept open and busy.
It goes like this:
SqlDataReader reader = GetData();
grid.DataSource = reader;
grid.DataBind();
reader.Close();

What you can do is this, if the set returned has a PK ( it does right? ) you
can bind it to a non-visible column or to a hidden field otherwise.
so when you select the delete action you can get this value back, pass it as
a parameter to a SP and then rerun the above piece of code.

Let me know if you need a complete code for all the operations involved.


cheers,

 

[Wing]

thanks for you guy advice. I had use dataset before, but my dataset
contained 2 tables which have relationship
between them, and I want to display the columns selectively from these
2 tables in the same datagrid by using the
databind function. However, the datagrid only display the first table
that filled into it, that make me try the data reader.

now I change my code to using the dataset again. the solution is join 2
tables first before calling the fill
function.

the code is showing below

DataSet shoppingCartDataSet = new DataSet();

SqlDataAdapter tableJoinAdapter = new SqlDataAdapter ("SELECT
ShoppingCart.RecordID,T_Photo.PhotoName, ShoppingCart.Quantity,
T_Photo.Price, ShoppingCart.CartID FROM ShoppingCart INNER JOIN T_Photo
ON ShoppingCart.PhotoID = T_Photo.PhotoID WHERE ShoppingCart.CartID =
'"+found+"'", thisConnection);

tableJoinAdapter.Fill(shoppingCartDataSet);

DataGrid1.DataSource = shoppingCartDataSet;
DataGrid1.DataBind();

Holpfully, I can update any changes to my database tables more easily.
If anyone find out any mistake in this code, please let me know.

thanks you very much.

wing