Creating your own INSERT, DELETE and UPDATE commands

  • Thread starter Thread starter Mike Lerch
  • Start date Start date
M

Mike Lerch

I need a bit of guidance if someone has a second.

If I have a dataset with a lot of records, and I have a form that
allows users to edit and/or add a lot of records at once, what do my
insert and update statements need to look like?

For instance, assume that I'm using Northwind and I use the query
"select supplierID, companyname, contactname from suppliers" as my
select query. Assume I have a form that lets the user edit a bunch of
contactnames all at once and then click a submit button. What would
the insert and update statements look like?

I guess the real question is whether the dataset fires the insert,
delete and update commands *per row* or whether it somehow does it for
the whole dataset.

Any help is greatly appreciated; I hope this isn't too vague!

Lerch
 
The Insert, Delete and Update commands are fired *per row* based on the
RowState of each row. So rows with a status of 'unchanged' don't set off
any commands but states such as 'added', 'modified' and 'deleted' set
the appropriate commands. Of course this is all trhough the use of a
suitably set up DataAdapter.
 
Mike:

currently it's per row. In the 2.0 framework, ADO.NET 2.0 will have support
for batch updates but that won't be out for a while unless you want to use
Whidbey.

You'll want to define an Update Statement, Insert Statement, Delete
Statement and a Select Statement. If you have a keyed table, you may
consider using a commandBuilder (although I tend to avoid them) and just
give it a Select, it will infer the rest for you. Check out Bill Vaughn's
article here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/ht
ml/commandbuilder.asp
or his other stuff at www.betav.com

This should explain the nuances to you.

HTH,

Bill
 
You'll want to define an Update Statement, Insert Statement, Delete
Statement and a Select Statement. If you have a keyed table, you may
consider using a commandBuilder (although I tend to avoid them) and just
give it a Select, it will infer the rest for you. Check out Bill Vaughn's
article here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/ht
ml/commandbuilder.asp
or his other stuff at www.betav.com
Click to expand...

Thanks, William and Mortos, this was great. One more question:

I assume that I need to "wire up" (for lack of a better word) the
insert/update statements?

Here's the narrative: In that case I'm working with, I am using
nested repeaters to present hierarchical information. If someone goes
into one of the child textboxes and makes a change, then clicks a
button on the form to fire the update, do I need to go find the
repeaters that have changed info and enter them as parameters to the
update procedure? (because it will be a parameterized stored
procedure). I'm thinking that I have to do that but am wondering
about the relation between data that the DataAdapter knows has changed
with the physical instances of the repeater.

Aw, hell, I'm barely following: this is too big for my brain today!

Lerch
 
Hi Mike:
Mike Lerch said:
t

Thanks, William and Mortos, this was great. One more question:

I assume that I need to "wire up" (for lack of a better word) the
insert/update statements?

Here's the narrative: In that case I'm working with, I am using
nested repeaters to present hierarchical information. If someone goes
into one of the child textboxes and makes a change, then clicks a
button on the form to fire the update, do I need to go find the
repeaters that have changed info and enter them as parameters to the
update procedure? (because it will be a parameterized stored
procedure). I'm thinking that I have to do that but am wondering
about the relation between data that the DataAdapter knows has changed
with the physical instances of the repeater.

Aw, hell, I'm barely following: this is too big for my brain today!

Lerch
Click to expand...

If you look at the code generated when you run a DataAdapterConfiguartion
wizard for instance, you'll see the last argument of the parameter
declaration is a field name - this is actually the ColumnMapping which maps
that column back to the database. So anything that's inserted in a given
row in whatever column will be used as that parameter's value. Also
remember that the DataAdapter (until version 2.0 anyway) updates records one
at a time.) Ok, so if you add a new row and set the value of Column2 to
"Bill" then add another one and Set it to "Lerch" we have two new rows and
the paratmers @Column2 will have each of those values.

This is how one part of the mapping takes place. If you are updating
everything programatically without a UI, then this is pretty much all there
is to it. However, if you are using a UI, then you can either use the
values of the controls and manually set the values of the underlying table
accordingly, or you can use DataBindings. If you use Bindings, you have to
reference a specific field of a DataTable/DataView. So if we had a textbox
and used myTextBox.DataBindings.Add("Text", myDataSet, "Column2") everything
we did to that textbox would be reflected in the given record's Column2
column. This would then be mapped to parameter @Column2 which is part of
the insert statement. When the update is fired and the DataAdapter sees a
row marked as Inserted (or Modified/Deleted etc) then it will use this
value.

The BindingContext will take care of moving the position of the current row
and then the UI is used to modify/insert/delete values. IF you only have a
grid, then you don't even necessarily need to specify a context you can just
set it's datasource property. Also,I used the DataAdapter Configuration
wizard as my example above, but whatever way you create your update logic,
you can explicitly create the mappings (like the Wizard does) or you can
just set the param values manually without the mapping.

Using DataBindings makes this a whole lot easier though b/c everything is
mapped behind the scenes and it's very straightforward to deal with.

HTH,

Bill
 
reference a specific field of a DataTable/DataView. So if we had a textbox
and used myTextBox.DataBindings.Add("Text", myDataSet, "Column2") everything
we did to that textbox would be reflected in the given record's Column2
column.
...
and then the UI is used to modify/insert/delete values. IF you only have a
grid, then you don't even necessarily need to specify a context you can just
set it's datasource property.
Click to expand...

I'm using databinding but still can't get it to work. I think I've
figured out the problem, though: does any of this apply to a repeater?
Nested repeaters, specifically? I have the aspx below.

Everything is working great for the select: the parameters are
loading, it's looking good, etc. But when I change the value of one
of the textboxes or type in some comments and click my submit button,
which fires daResponseAnswers.Update(myDS, "myTable"), the database
doesn't change.

Thanks for your hep thus far Bill, and I hope you can help with this
piece. I fear that I can't do what I'm looking for with a Repeater.

<asp:repeater id="RepeaterSection" Runat="server">
<ItemTemplate>
<hr>
<asp:Label Runat="server" Font-Bold="True">
<%#DataBinder.Eval (Container.DataItem, "Section")%>
</asp:Label>
<hr>
<!-- start child repeater -->
<asp:repeater id="RepeaterQuestion" datasource='<%#
((DataRowView)Container.DataItem)
.Row.GetChildRows("MyRelation") %>' runat="server">
<ItemTemplate>
<p>
<asp:Label Runat="server" Visible="False"
ID="lblQuestionID" Text='<%#DataBinder.Eval (Container.DataItem,
"[\"QuestionID\"]")%>'/>
<asp:Label runat="server" ID="lblQuestionText" Text =
'<%#DataBinder.Eval (Container.DataItem, "[\"Question\"]")%>'/><br>
<asp:dropdownlist id="cbQuestion" runat="server"
SelectedValue='<%#DataBinder.Eval (Container.DataItem,
"[\"Answer\"]")%>'>
<asp:ListItem Value=""></asp:ListItem>
<asp:ListItem Value="No"
Text="No">No</asp:ListItem>
<asp:ListItem Value="Yes"
Text="Yes">Yes</asp:ListItem>
</asp:dropdownlist>
<br>
<asp:textbox runat="server" Font-Names="Arial"
Width="622px" Height="77px" TextMode="MultiLine" ID="txtComment"
text='<%#DataBinder.Eval (Container.DataItem, "[\"Comment\"]")%>'>
</asp:textbox>
</p>
</ItemTemplate>
</asp:repeater>
<!-- end child repeater -->
</ItemTemplate>
</asp:repeater>
 
Back
Top