Multiple dataTables in dataset, insert problem

[SimonV]

Hy,
I have a dataset with 2 tables in: Stuff and Price
in the Stuff table are the collumns: Id, Name, PriceID
in the Price table there are: Id, Price
This is excatly the same as in my database.

I'm using SqlDataAdapter, SqlCommand and so.

How can i insert a new dataset correctly?
I'm using a stored procedure that asks a name and a price, and fills in
the data corectly by himself.

When I'm using this:
SqlParameter pPrice = new SqlParameter();
pPrice .ParameterName = "@Price";
pPrice .SourceColumn = "Price";
cmd.Parameters.Add(pPrice );

I get an error, I think he's working with the wrong table, but how can
I say witch table he must use?

 

[W.G. Ryan - MVP]

Simon - just to be clear, I think you're problem isn't with the insert into
the dataset is it, it's into the Db right?

What you need to do to handle this scenario is create two dataadapters or
one and switch he insert/update/delete commands (create two). Then call
update on the parent adapter/table then the child. Your parameters could
be perfect but if you pass in a table that's it's not configured for, then
it'll blow up. So specifically to answer your question, you can tell which
table you're passing in b/c you should only be referencing one table at a
time

 

[SimonV]

Thanks,
The problem is indeed with the Db.

I think I get the point by working with 2 dataAdapters, but what is the
advantage then by putting 2 tables in 1 dataset over working with 2
tables each in a seperate dataset?

 

[Marina]

If you don't have any relations, etc, then none. In fact, you don't need to
put your tables in a dataset at all unless you are going to take advantage
of some dataset functionality.

 

[W.G. Ryan - MVP]

Simon - essentially the same reason that working with two tables in one
database (when they are related) is preferable to storing them in two
different databases If you search for DataRelation on www.knowdotnet.com
http://search.msn.com/results.aspx?q=DataRelation+KnowDotnet&FORM=QBHP -
i've written a lot on it, but mainly it's just to enforce cascading updates
and deletes, to support databinding in controls easier, and most importantly
to enforce integrity

 

[SimonV]

I do have a relation between these two tables, between Stuff.Price and
Price.PriceID.
I've created the dataset in the designer by dragging the 2 tables from
the server explorer to the dataset. The relation is automatically set
because I did this in sqlserver.

But I'm getting confused, I'm using the whole thing different I think,
and I dont see how to implement those solutions in my code:

In de DataAcces I set my connection
StuffData is the name of my dataset (with the two tables, Stuff and
Price in)

public class StuffDA : DataAccess
{
public StuffData GetStuff()
{
StuffData ds = new StuffData();
this.GetAdapter().Fill(ds);
return ds;
}

public void UpdateStuff(StuffData ds)
{
this.GetAdapter().Update(ds);
}

private SqlDataAdapter GetAdapter()
{
SqlConnection con = this.GetConnection();
SqlDataAdapter da = new SqlDataAdapter();

da.TableMappings.Add("Table", "Stuff");

da.SelectCommand = this.GetSelectCommand(con);
da.InsertCommand = this.GetInsertCommand(con);
da.DeleteCommand = this.GetDeleteCommand(con);
da.UpdateCommand = this.GetUpdateCommand(con);

return;
}

private SqlCommand GetSelectCommand(SqlConnection con)
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_getStuff";

return cmd;
}

private SqlCommand GetInsertCommand(SqlConnection con)
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_addStuff";

SqlParameter pName = new SqlParameter();
pName .ParameterName = "@Name";
pName .SourceColumn = "Name";
cmd.Parameters.Add(pNaam);

SqlParameter pPrice = new SqlParameter();
pPrice .ParameterName = "@Price";
pPrice .SourceColumn = "Price";
cmd.Parameters.Add(pPrice );

return cmd;
}

private SqlCommand GetDeleteCommand(SqlConnection con)
{
SqlCommand cmd = con.CreateCommand();

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_delStuff";

SqlParameter id = new SqlParameter();
id.ParameterName = "@id";
id.SourceColumn = "Id";

cmd.Parameters.Add(id);

return cmd;

}

private SqlCommand GetUpdateCommand(SqlConnection con)
{
SqlCommand cmd = con.CreateCommand();

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_updateStuff";

SqlParameter id = new SqlParameter();
id.ParameterName = "@id";
id.SourceColumn = "Id";
cmd.Parameters.Add(id);

SqlParameter pName = new SqlParameter();
pName .ParameterName = "@Name";
pName .SourceColumn = "Name";
cmd.Parameters.Add(pName);

SqlParameter pPrice= new SqlParameter();
pPrice.ParameterName = "@Price";
pPrice.SourceColumn = "Price";
cmd.Parameters.Add(pPrice);

return cmd;
}
}

 

[SimonV]

I've tried it this way:

public void UpdateStuff(StuffData ds) {
this.GetAdapterP().Update(ds,"Price");
this.GetAdapterS().Update(ds,"Stuff");

DataRelation Stuff_Price = new DataRelation("ds",
ds.Stuff.PriceIdColumn, ds.Price.IdColumn);
ds.Relations.Add(Stuff_Prijs);
}

And then the both GetAdapter functions with there own select, update...
commands

When I trie to instert stuff I get an error thats saying the insert
statement confilicted with the foreign key.
I understand this, I dont see how the database could know the PriceId.
But how to solve this?

I set the values of the dataset this way:

PriceData.PriceRow prijceR = new
PriceData.PriiceDataTable().NewPriceRow();
prijceR.Price= Convert.ToInt32(txtPrice.Text);
ds.Stuff.AddStuffRow(txtName.Text, prijceR);
try{
StuffData dsChanged = (StuffData)ds.GetChanges();
new StuffDA().UpdateStuff(dsChanged);
ds.AcceptChanges();
}
catch...