Re: what am I doing wrong ?
- From: "Dave Sexton" <dave@jwa[remove.this]online.com>
- Date: Fri, 10 Nov 2006 12:17:05 -0500
Hi,
i"m somewhat confused as to why we need to generate all parameters of the
dataset to do an update. Doesn't the dataset already know which column is
which ?
We're not generating parameters on the DataSet, we're generating parameters
for the OleDbCommand object. The DataSet just contains data and isn't aware
of the database implementation, adapters, commands, connections or anything
else like that. The names of the DataColumns don't have to be the same as the
corresponding fields in the database.
can you do something like a "INSERT INTO who * VALUES *"
Unfortunately no, because that's not valid SQL.
The adapter doesn't process your SQL statement, the database does. The
adapter's job is to simply read each DataRow from the specified DataTable (or
all DataTables) and check the RowState to determine which command (e.g.,
InsertCommand, UpdateCommand, DeleteCommand) should be used to update the row
in the database. New rows are added using the specified InsertCommand,
modified rows are updated using the specified UpdateCommand and deleted rows
are removed using the specified DeleteCommand.
For each row, the adapter checks the SourceColumn of each parameter on the
command chosen for that row and grabs the values for the parameters from the
corresponding DataColumns. After the adapter sets the parameters on the
command from the SourceColumns the command is executed. The next row is then
processed.
That's the basics of how adapters work but they do offer more control and
functionality than I've described here.
If you can retrieve the datarow why can't we just update the datarow?
That's the adapter's job. The DataRow is just in-memory data and has no
relation at all to the actual data source. Of course, if you only have a
single row then you really don't even need an adapter at all. Just set the
parameters and execute the appropriate command yourself:
OleDbCommand comm = new OleDbCommand("INSERT INTO ...", conn);
comm.AddWithValue("pName", row["name"]);
comm.AddWithValue("pAddress", row["address"]);
comm.AddWithValue("pAge", row["age"]);
comm.ExecuteNonQuery();
please excuse my nubiness!!!
I"m so used to working with tables and records...
Can I retrieve the parameters from the database without needing to retype it
for the select statement ?
Once you have a select statement, as Cor pointed out, you can use an
OleDbCommandBuilder object to create the other commands. Your select
statement must return a unique or primary key field in order for the command
builder to work (an exception is thrown otherwise according to the article
below).
"Automatically Generating Commands"
http://msdn2.microsoft.com/en-us/library/tf579hcz.aspx
"OleDbCommandBuilder Class"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbCommandBuilderClassTopic.asp
If you're using stored procedures and would like to automatically derive the
parameters there's an easy way to do that as well:
"OleDbCommandBuilder.DeriveParameters Method"
http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbcommandbuilder.deriveparameters.aspx
--
Dave Sexton
.
- Follow-Ups:
- Re: what am I doing wrong ?
- From: SLIMSHIM
- Re: what am I doing wrong ?
- References:
- Re: what am I doing wrong ?
- From: Dave Sexton
- Re: what am I doing wrong ?
- From: SLIMSHIM
- Re: what am I doing wrong ?
- Prev by Date: Reading Excel dates into C# come out as "weird" ints - help?
- Next by Date: RE: Reading Excel dates into C# come out as "weird" ints - help?
- Previous by thread: Re: what am I doing wrong ?
- Next by thread: Re: what am I doing wrong ?
- Index(es):
Relevant Pages
|
|
