public DataSet GetData()
{
SqlConnection conn = new SqlConnection ("server=vcdb02;uid=sa;pwd=ricka;database=northwin d");
//Pull back the recent orders for the parent rows.
SqlDataAdapter daOrder = new SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'",conn);
//Get only the appropriate child rows for the parent rows.
SqlDataAdapter daDetails = new SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')",conn);
DataSet ds = new DataSet();
try
{
//Fill DataSet, and then set DataRelation to move through the DataGrid.
conn.Open();
daOrder.FillSchema(ds,SchemaType.Mapped,"Orders");
daOrder.Fill(ds,"Orders");
daDetails.FillSchema(ds,SchemaType.Mapped,"Details ");
daDetails.Fill(ds,"Details");
ds.Relations.Add("OrdDetail", ds.Tables["Orders"].Columns["OrderID"], ds.Tables["Details"].Columns["OrderID"]);
DataColumn dc = ds.Tables["Orders"].Columns["OrderID"];
dc.AutoIncrement = true;
dc.AutoIncrementSeed = -1;
dc.AutoIncrementStep = -1;
}
catch(SqlException ex)
{
Console.Write (ex.Message.ToString ());
Console.Write(ex.InnerException.ToString ());
}
return ds;
}
[WebMethod]
public DataSet UpdateData(DataSet ds)
{
SqlConnection conn = new SqlConnection ("server=vcdb02;uid=sa;pwd=ricka;database=northwin d");
//Pull back the recent orders for the parent rows.
SqlDataAdapter daOrders = new SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'",conn);
//Get only the appropriate child rows for the parent rows.
SqlDataAdapter daDetails = new SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')",conn);
try
{
conn.Open();
// Get commands for the Orders table.
// Reselect record after insert to get new Identity value.
// You must get the schema, which you did in GetData(), before you get commands;
// otherwise, the Command builder tries to insert new rows, based
// on the Identity column.
SqlCommandBuilder cb = new SqlCommandBuilder(daOrders);
daOrders.DeleteCommand = cb.GetDeleteCommand();
daOrders.UpdateCommand = cb.GetUpdateCommand();
daOrders.InsertCommand = cb.GetInsertCommand();
daOrders.InsertCommand.CommandText = String.Concat(daOrders.InsertCommand.CommandText, "; Select * From Orders Where OrderID = @@IDENTITY");
//UpdateRowSource tells the DataAdapter that there will be a re-selected record.
daOrders.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
//cb = null;
// Get commands for the Order Details table.
// Must set the QuotePrefix and QuoteSuffix;
// otherwise, the CommandBuilder does not put brackets ([])
// around the table name.
SqlCommandBuilder cb1 = new SqlCommandBuilder(daDetails);
cb1.QuotePrefix = "[";
cb1.QuoteSuffix = "]";
daDetails.DeleteCommand = cb1.GetDeleteCommand();
daDetails.InsertCommand = cb1.GetInsertCommand();
daDetails.UpdateCommand = cb1.GetUpdateCommand();
// Create a new DataAdapter based on the original one to prevent the
// CommandBuilder from modifying the SQL statements,
// specifically the custom InsertCommand.
// You do not need this if you roll your own commands and parameters
// or if you use the Visual Tools to do it.
SqlDataAdapter daOrd2 = new SqlDataAdapter();
daOrd2.DeleteCommand = daOrders.DeleteCommand;
daOrd2.InsertCommand = daOrders.InsertCommand;
daOrd2.UpdateCommand = daOrders.UpdateCommand;
// Use a delegate to prevent AcceptChanges from occurring on Deletes and Inserts.
// This is for a limitation of the DataAdapter; see Q313540.
daOrd2.RowUpdated += new SqlRowUpdatedEventHandler(OnOrd1RowUpdated);
daDetails.RowUpdated += new SqlRowUpdatedEventHandler(OnDetailsRowUpdated);
daDetails.Update(GetDeletedRows(ds.Tables["Details"]));
daOrd2.Update(GetDeletedRows(ds.Tables["Orders"]));
DataRow [] dsArray = ds.Tables["Orders"].Select("", "", DataViewRowState.ModifiedCurrent);
daOrd2.Update(ds.Tables["Orders"].Select("", "", DataViewRowState.ModifiedCurrent));
daDetails.Update(ds.Tables["Details"].Select("", "", DataViewRowState.ModifiedCurrent));
daOrd2.Update(ds.Tables["Orders"].Select("", "", DataViewRowState.Added));
ds.EnforceConstraints = false;
daDetails.Update(ds.Tables["Details"].Select("","", DataViewRowState.Added));
ds.EnforceConstraints = true;
conn.Close();
}
catch(SqlException ex)
{
Console.Write (ex.Message.ToString ());
Console.Write(ex.InnerException.ToString ());
}
return ds;
}
protected static void OnOrd1RowUpdated(object sender, SqlRowUpdatedEventArgs args)
{
if(args.StatementType == StatementType.Insert || args.StatementType == StatementType.Delete )
args.Status = UpdateStatus.SkipCurrentRow;
}
protected static void OnDetailsRowUpdated(object sender, SqlRowUpdatedEventArgs args)
{
if(args.StatementType == StatementType.Insert )
{
// Do not allow the AcceptChanges to occur on this row.
args.Status = UpdateStatus.SkipCurrentRow;
// Get the current, actual primary key value so that you can plug it back
// in after you get the correct original value that was generated for the child row.
int currentkey = (int)args.Row["OrderID"];
// This is where you get a correct original value key that is stored to the child row.
// You pull the original, pseudo key value from the parent, plug it in as the child row's primary key
// field, and then accept changes on it. Specifically, this is why you turned off EnforceConstraints.
args.Row["OrderID"] = args.Row.GetParentRow("OrdDetail")["OrderID",DataRowVersion.Original];
args.Row.AcceptChanges();
// Store the actual primary key value in the foreign key column of the child row.
args.Row["OrderID"] = currentkey;
}
if(args.StatementType == StatementType.Delete )
args.Status = UpdateStatus.SkipCurrentRow;
}
private DataRow [] GetDeletedRows(DataTable dt)
{
DataRow [] dr ;
if(dt == null)
return null;
dr = dt.Select("","",DataViewRowState.Deleted );
if(dr.Length ==0 || dr[0] != null)
return dr;
// Workaround:
// With a remoted DataSet, Select returns the array elements
// that are filled with Nothing/null instead of DataRow objects.
for(int i=0; i < (int)dt.Rows.Count; i++)
{
if(dt.Rows[i].RowState ==DataRowState.Deleted )
dr[i]=dt.Rows[i];
}
return dr;
}
|
