You're welcome, Dmitry ... I hope it helps some. =)
~~Bonnie
>Bonnie,
>
>Thank you very much for your code. I will print it and study it in the morning (it is the only time I can think clearly <g>). I appreciate your help.
>
>>Here's how I do it. This is simplified ... some of it, like getting the changed/deleted rows, could be done in the Biz layer or the front end or the Web Service. This is basically the code you need in the DataAccess layer though:
>>
>>public bool SaveMyDataSet()
>>{
>> bool IsOK = true;
>> long MyPK;
>>
>> this.oConnection.Open();
>> SqlTransaction oTrans = this.oConnection.BeginTransaction();
>> SqlCommand sc = new SqlCommand();
>> sc.CommandType = CommandType.StoredProcedure;
>> sc.Connection = this.oConnection;
>> sc.Transaction = oTrans;
>>
>> DataSet dsChanged = this.MyDataSet.GetChanges();
>> DataSet dsDeleted = this.MyDataSet.GetChanges(DataRowState.Deleted);
>>
>>
>> if (IsOK == true)
>> IsOK = this.SaveMyData(sc, dsChanged);
>>
>> if (IsOK == true)
>> IsOK = this.DeleteMyData(sc, dsDeleted);
>>
>> // End the open transaction
>> if (IsOK == true)
>> {
>> oTrans.Commit();
>> MyPK = this.GetKey(dsChanged, "MyPK");
>> DataSet dsKeys = this.GetMyDataSetKeys(ref Message, MyPK);
>> // this spins through MyDataSet updating the keys from the dsKeys DataSet
>> this.MatchKeys(this.MyDataSet, dsKeys);
>> }
>> else
>> {
>> oTrans.Rollback();
>> Message = "Transaction rolled back!" + (char)13 + (char)13 +
>> "Changes in Personnel Information were not saved.";
>> }
>> this.oConnection.Close();
>> return IsOK;
>>}
>>
>>protected bool SaveMyData(SqlCommand Command, MyDataSet Data)
>>{
>> long MyPK;
>>
>> if (Data.Personnel.Rows.Count < 1)
>> return false;
>>
>> try
>> {
>> // -- First, the main table
>> Command.CommandText = "bsp_MyMainTable_Put";
>>
>> // The SetBasicParameters method loops through every column in the table, setting the parameters.
>> // It will also set the PK parameter's .Direction property to be ParameterDirection.InputOutput
>>
>> this.SetBasicParameters(Command, Data.Tables["MyMainTable"].Rows[0], "MyPK");
>> Command.ExecuteNonQuery();
>>
>> MyPK = (long)Command.Parameters["@MyPK"].Value;
>> Data.Tables["MyMainTable"].Rows[0]["MyPK"] = MyPK;
>>
>> // -- The SetKey method goes through every row in every table, setting the ForeignKey
>> // This assumes that all FK's in every table have the same name "MyPK"
>> this.SetKey(Data, MyPK, "MyPK");
>>
>> // Now that all the FK's are in place, we can update all the child tables
>> for (int i = 0; i < Data.Tables["MyChildTable"].Rows.Count; i++)
>> {
>> Command.CommandText = "bsp_MyChildTable_Put";
>> this.SetBasicParameters(Command, Data.Tables["MyChildTable"].Rows[i], "MyChildPK");
>> Command.ExecuteNonQuery();
>> }
>>
>> for (int i = 0; i < Data.Tables["MySecondChildTable"].Rows.Count; i++)
>> {
>> Command.CommandText = "bsp_MySecondChildTable_Put";
>> this.SetBasicParameters(Command, Data.Tables["MySecondChildTable"].Rows[i], "MySecondChildPK");
>> Command.ExecuteNonQuery();
>> }
>> }
>> catch (SqlException ex)
>> {
>> // Log the error
>> return false;
>> }
>>
>> return true;
>>}
>>
>>protected bool DeleteMyData(SqlCommand Command, MyDataSet Data)
>>{
>> // The Delete stuff is similar
>>}
>>>>
>>~~Bonnie
