How can an output parameter update a DataTable in a DataSet

[Tony Johansson]

Hello!

This test is just for understanding. In the code below I have numbered four
rows in main with 1,2,3 and 4.
At 1 I call method GenerateInsertCommand to configure a SqlCommand to
prepare it for calling a stored procedure.
The stored procedure is listed below and called RegionInsert.and contains
two parameter the first is an input and the second is an output parameter
At 2 I fill the Region DataTable with select * from Region
The DataTable Region now looks like this
1 Eastern
2 Middle
3 Northern
4 Southern


The definition for the Region database table is the following
(PK) RegionID int
RegionDescription nchar(50)

At 3 I add a new row to the DataTable so the DataTable Region now looks like
this
1 Eastern
2 Middle
3 Nothern
4 Southern
999 North West

At 4 I call SqDataAdapter.Update which will execute the insert command
because there is a row that have rowstate added.
The insert command is refering to the stored procedure RegionInsert which is
called.

If I look at the DataTable Region in the DataSet after the Update has been
called it looks like this. The database is updated ok.
1 Eastern
2 Middle
3. Northern
4 Southern
5 North West

Now to my question I know that RegionID is an output parameter from the
stored procedure but how can this parameter update the RegionID in the
DataTable Region as you can see.
Before Update was called it was
999 North West
but now it is
5 North West

Can somebody explain that ?

static void Main(string[] args)
{
SqlConnection thisConnection = new SqlConnection("Data
Source=HEMPC\\SQLEXPRESS;Initial Catalog=northwind;Integrated
Security=True");
thisConnection.Open();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select * from
Region",thisConnection);
1 da.InsertCommand = GenerateInsertCommand(thisConnection);
2 da.Fill(ds, "Region");
3 DataRow r = ds.Tables["Region"].Rows.Add(new object[] { 999,"North
West"});
4 da.Update(ds, "Region");
}

static SqlCommand GenerateInsertCommand(SqlConnection conn)
{
SqlCommand aCommand = new SqlCommand("RegionInsert", conn);
aCommand.CommandType = CommandType.StoredProcedure;

aCommand.Parameters.Add(new SqlParameter("@RegionDescription",
SqlDbType.NChar,
50,
"RegionDescription"));

aCommand.Parameters.Add(new SqlParameter("@RegionID",
SqlDbType.Int,
0,
ParameterDirection.Output,
false,
0,0,
"RegionID",
DataRowVersion.Default,
null));

aCommand.UpdatedRowSource = UpdateRowSource.None;
return aCommand;
}

ALTER procedure RegionInsert (@RegionDescription NCHAR(50),
@RegionID INTEGER OUTPUT) AS
SET NOCOUNT OFF
SELECT @RegionID = max(RegionID) + 1
from Region

insert into Region(RegionID, RegionDescription)
values(@RegionID, @RegionDescription)

//Tony