DataRelations in ADO.NET

http://msdn.microsoft.com/en-us/library/cc188919.aspx#S1

SELECT    c.CustomerID,    c.CompanyName,    c.ContactName,    o.OrderID,    o.OrderDate,    od.ProductID,    p.ProductName,    od.UnitPrice,   od.Quantity

FROM    Customers c    INNER JOIN Orders o ON c.CustomerID = o.CustomerID

    INNER JOIN [Order Details] od ON o.OrderID = od.OrderID

    INNER JOIN Products p ON od.ProductID = p.ProductID

Figure 2 Creating a DataSet

string sCn = ". . . ";

SqlConnection oCn = new SqlConnection (sCn);

DataSet oDs = new DataSet();

//— Fill the customer DataTable

string sSqlCustomer = "SELECT CustomerID, CompanyName, ContactName FROM Customers";

SqlDataAdapter oDaCustomer = new SqlDataAdapter(sSqlCustomer, oCn);

oDaCustomer.Fill(oDs, "Customer");

//— Fill the order DataTable

string sSqlOrder = "SELECT CustomerID, OrderID, OrderDate FROM Orders";

SqlDataAdapter oDaOrder = new SqlDataAdapter(sSqlOrder, oCn);

oDaOrder.Fill(oDs, "Order");

//— Fill the order detail DataTable

string sSqlOrderDetail = "SELECT od.OrderID, od.ProductID,

                p.ProductName, " +

                " od.UnitPrice, od.Quantity FROM [Order Details] od " +

                " INNER JOIN Products p ON od.ProductID = p.ProductID";

SqlDataAdapter oDaOrderDetail = new SqlDataAdapter(sSqlOrderDetail, oCn);

oDaOrderDetail.Fill(oDs, "OrderDetail");

Figure 3 Creating DataRelation Objects

//— Create the DataRelation and relate the customers to their orders

DataRelation oDr_Customer2Order = new DataRelation("Customer2Order",

            oDs.Tables["Customer"].Columns["CustomerID"],

            oDs.Tables["Order"].Columns["CustomerID"]);

oDs.Relations.Add(oDr_Customer2Order);

//— Create the DataRelation and relate the orders to their order details

DataRelation oDr_Order2OrderDetail = new        

            DataRelation("Order2OrderDetail",

            oDs.Tables["Order"].Columns["OrderID"],

            oDs.Tables["OrderDetail"].Columns["OrderID"]);

oDs.Relations.Add(oDr_Order2OrderDetail);

dataGrid1.DataSource = oDs;

ForeignKeyConstraint oFKey;

oFKey = new ForeignKeyConstraint("CustomerForeignkey",

    oDs.Tables["Customer"].Columns["CustomerID"],  oDs.Tables["Order"].Columns["CustomerID"]);

oFKey.DeleteRule = Rule.Cascade;

oFKey.UpdateRule = Rule.Cascade;

oDs.Tables["Customer"].Constraints.Add(oFKey);

oDs.EnforceConstraints = true;

Figure 4 Looping Through Child Rows

//— Retrieve the child rows for the first customer

DataRow[] oRows = oDs.Tables["Customer"].Rows[0].GetChildRows(oDr_Customer2Order);

string sMsg = "The orders for the first customer are: \n";

//— Loop through the child rows for the first customer

for (int i=0; i < oRows.Length; i++)

{

//— Grab the values for each child row of the first customer

    DataRow oRow = oRows[i];

    sMsg += "\t" + oRow["CustomerID"].ToString() +

            " " + oRow["OrderID"].ToString() +

            " " + oRow["OrderDate"].ToString() + "\n";

}

//— Display the values of the child rows

MessageBox.Show(sMsg);

DataRow oRow =  oDs.Tables["Order"].Rows[0].GetParentRow(oDr_Customer2Order);

MessageBox.Show(oRow["CompanyName"].ToString());

MessageBox.Show(oDs.GetXml());

.net Transaction

http://msdn.microsoft.com/en-us/library/ms971557.aspx

1.       Open the transaction using Connection.BeginTransaction().

2.       Enlist statements or stored procedure calls in the transaction by setting the Command.Transaction property of the Command objects associated with them.

3.       Depending on the provider, optionally use Transaction.Save() or Transaction.Begin() to create a savepoint or a nested transaction to enable a partial rollback.

4.       Commit or roll back the transaction using Transaction.Commit() or Transaction.Rollback().

public void SPTransaction(int partID, int numberMoved, int siteID)

{

   // Create and open the connection.

   SqlConnection conn = new SqlConnection();

   string connString = "connection string";

   conn.ConnectionString = connString;

   conn.Open();

   // Create the commands and related parameters.

   // cmdDebit debits inventory from the WarehouseInventory

   // table by calling the DebitWarehouseInventory stored procedure.

   SqlCommand cmdDebit = new SqlCommand("DebitWarehouseInventory", conn);

   cmdDebit.CommandType = CommandType.StoredProcedure;

   cmdDebit.Parameters.Add("@PartID", SqlDbType.Int, 0, "PartID");

   cmdDebit.Parameters["@PartID"].Direction =ParameterDirection.Input;

   cmdDebit.Parameters.Add("@Debit", SqlDbType.Int, 0, "Quantity");

   cmdDebit.Parameters["@Debit"].Direction = ParameterDirection.Input;

   // cmdCredit adds inventory to the SiteInventory

   // table by calling the CreditSiteInventory stored procedure.

   SqlCommand cmdCredit = new SqlCommand("CreditSiteInventory", conn);

   cmdCredit.CommandType = CommandType.StoredProcedure;

   cmdCredit.Parameters.Add("@PartID", SqlDbType.Int, 0, "PartID");

   cmdCredit.Parameters["@PartID"].Direction =ParameterDirection.Input;

   cmdCredit.Parameters.Add("@Credit", SqlDbType.Int, 0, "Quantity");

   cmdCredit.Parameters["@Credit"].Direction = ParameterDirection.Input;

   cmdCredit.Parameters.Add("@SiteID", SqlDbType.Int, 0, "SiteID");

   cmdCredit.Parameters["@SiteID"].Direction = ParameterDirection.Input;

   // Begin the transaction and enlist the commands.

   SqlTransaction tran = conn.BeginTransaction();

   cmdDebit.Transaction = tran;

   cmdCredit.Transaction  = tran;

   try

   {

      // Execute the commands.

      cmdDebit.Parameters["@PartID"].Value = partID;

      cmdDebit.Parameters["@Debit"].Value = numberMoved;

      cmdDebit.ExecuteNonQuery();

      cmdCredit.Parameters["@PartID"].Value = partID;

      cmdCredit.Parameters["@Credit"].Value = numberMoved;

      cmdCredit.Parameters["@SiteID"].Value = siteID;

      cmdCredit.ExecuteNonQuery();

      // Commit the transaction.

      tran.Commit();

   }

   catch(SqlException ex)

   {

      tran.Rollback();

      // Additional error handling if needed.

   }

   finally

   {      conn.Close();   }}

Using Transactions with a DataAdapter

public void CBTransaction()

{

   // Create and open the connection.

   SqlConnection conn = new SqlConnection();

   string connString = "...";

   conn.ConnectionString = connString;

   conn.Open();

        

   // Create the DataAdapters.

   string cmdString = "Select WIID, PartID, Quantity from WarehouseInventory";

   SqlDataAdapter daWarehouse =  new SqlDataAdapter(cmdString,conn);

   cmdString = "Select SiteID, PartID, Quantity from SiteInventory";

   SqlDataAdapter daSite = new SqlDataAdapter(cmdString,conn);

   // Create the DataSet.

   DataSet ds = new DataSet();

   // Create the CommandBuilders and generate

   // the INSERT/UPDATE/DELETE commands.

   SqlCommandBuilder cbWarehouse = new SqlCommandBuilder(daWarehouse);

   SqlCommand warehouseDelete = cbWarehouse.GetDeleteCommand();

   SqlCommand warehouseInsert = cbWarehouse.GetInsertCommand();

   SqlCommand warehouseUpdate = cbWarehouse.GetUpdateCommand();

   SqlCommandBuilder cbSite = new SqlCommandBuilder(daSite);

   SqlCommand siteDelete = cbSite.GetDeleteCommand();

   SqlCommand siteInsert = cbSite.GetInsertCommand();

   SqlCommand siteUpdate = cbSite.GetUpdateCommand();

   // Fill the DataSet.

   daWarehouse.Fill(ds, "WarehouseInventory");

   daSite.Fill(ds, "SiteInventory");

   // Begin the transaction and enlist the commands.

   SqlTransaction tran = conn.BeginTransaction();

   warehouseDelete.Transaction = tran;

   warehouseInsert.Transaction = tran;

   warehouseUpdate.Transaction = tran;

   siteDelete.Transaction = tran;

   siteInsert.Transaction = tran;

   siteUpdate.Transaction = tran;

   // Modify data to move inventory from WarehouseInventory to SiteInventory.           

   try

   {

      //Execute the commands

      daWarehouse.Update(ds, "WarehouseInventory");

      daSite.Update(ds, "SiteInventory");

      //Commit the transaction

      tran.Commit();

   }

   catch(SqlException ex)

   {

      //Roll back the transaction.

      tran.Rollback();

      //Additional error handling if needed.

   }

   finally

   {           conn.Close();   }}

Using Savepoints

public void SavepointTransaction()

{

   // Create and open the connection.

   SqlConnection conn = new SqlConnection();

   string connString = ". . .";

   conn.ConnectionString = connString;

   conn.Open();

   // Create the commands.

   // cmdInsertCustomer creates a new customer record  by calling the DebitWarehouseInventory

      SqlCommand cmdInsertCustomer =       new SqlCommand("CreateCustomer", conn);

   cmdInsertCustomer.CommandType = CommandType.StoredProcedure;

   cmdInsertCustomer.Parameters.Add ("@FirstName", SqlDbType.NVarChar, 50, "FirstName");

   cmdInsertCustomer.Parameters.Add ("@LastName", SqlDbType.NVarChar, 50, "LastName");

   cmdInsertCustomer.Parameters.Add ("@Email", SqlDbType.NVarChar, 50, "Email");

   cmdInsertCustomer.Parameters.Add("@CID", SqlDbType.Int, 0);

   cmdInsertCustomer.Parameters["@FirstName"].Direction = ParameterDirection.Input;

   cmdInsertCustomer.Parameters["@LastName"].Direction =  ParameterDirection.Input;

   cmdInsertCustomer.Parameters["@Email"].Direction = ParameterDirection.Input;

   cmdInsertCustomer.Parameters["@CID"].Direction =  ParameterDirection.Output;

   // cmdRequestMaterials creates a pick list of the materials requested by the customer

   // by calling the InsertMaterialsRequest stored procedure.

   SqlCommand cmdRequestMaterials =  new SqlCommand("InsertMaterialsRequest", conn);

   cmdRequestMaterials.CommandType = CommandType.StoredProcedure;

   cmdRequestMaterials.Parameters.Add ("@CustomerID", SqlDbType.Int, 0, "CustomerId");

   cmdRequestMaterials.Parameters.Add ("@RequestPartID", SqlDbType.Int, 0, "PartId");

   cmdRequestMaterials.Parameters.Add ("@Number", SqlDbType.Int, 0, "NumberRequested");

   cmdRequestMaterials.Parameters["@CustomerID"].Direction = ParameterDirection.Input;

   cmdRequestMaterials.Parameters["@RequestPartID"].Direction = ParameterDirection.Input;

   cmdRequestMaterials.Parameters["@Number"].Direction = ParameterDirection.Input;

   // cmdUpdateSite debits the requested materials from the inventory of those available by calling the UpdateSiteInventory stored procedure.

   SqlCommand cmdUpdateSite = new SqlCommand("UpdateSiteInventory", conn);

   cmdUpdateSite.CommandType = CommandType.StoredProcedure;

   cmdUpdateSite.Parameters.Add("@SiteID", SqlDbType.Int, 0, "SiteID");

   cmdUpdateSite.Parameters.Add("@SitePartID", SqlDbType.Int, 0, "PartId");

   cmdUpdateSite.Parameters.Add("@Debit", SqlDbType.Int, 0, "Debit");

   cmdUpdateSite.Parameters["@SiteID"].Direction =  ParameterDirection.Input;

   cmdUpdateSite.Parameters["@SitePartID"].Direction = ParameterDirection.Input;

   cmdUpdateSite.Parameters["@Debit"].Direction = ParameterDirection.Input;

        

   // Begin the transaction and enlist the commands.

   SqlTransaction tran = conn.BeginTransaction();

   cmdInsertCustomer.Transaction = tran;

   cmdUpdateSite.Transaction  = tran;

   cmdRequestMaterials.Transaction  = tran;

   try

   {

      // Execute the commands.

      cmdInsertCustomer.Parameters["@FirstName"].Value = "Mads";

      cmdInsertCustomer.Parameters["@LastName"].Value= "Nygaard";

      cmdInsertCustomer.Parameters["@Email"].Value = "MadsN@AdventureWorks.com";

      cmdInsertCustomer.ExecuteNonQuery();

      tran.Save("Customer");

      cmdRequestMaterials.Parameters["@CustomerID"].Value = cmdInsertCustomer.Parameters["@CID"].Value;

      cmdRequestMaterials.Parameters["@RequestPartID"].Value = 3;

      cmdRequestMaterials.Parameters["@Number"].Value= 22;

      cmdRequestMaterials.ExecuteNonQuery();

      cmdUpdateSite.Parameters["@SitePartID"].Value= 3;

      cmdUpdateSite.Parameters["@Debit"].Value = 22;

      cmdUpdateSite.Parameters["@SiteID"].Value = 4;

      cmdUpdateSite.ExecuteNonQuery();

      // Commit the transaction.

      tran.Commit();

   }

   catch(SqlException sqlEx)

   {

      try

      {

         // Roll back the transaction to the savepoint.

         Console.WriteLine(sqlEx.Message);

         tran.Rollback("Customer");

         tran.Commit();

         // Add code to notify user or otherwise handle   the fact that the procedure was only

         // partially successful.

      }

      catch(SqlException ex)

      {

         // If the partial rollback fails, roll back the whole transaction.

         Console.WriteLine(ex.Message);

         tran.Rollback();

         // Additional error handling if needed.

      }

   }

   finally

   {           conn.Close();   }}

Using Nested Transactions

public void NestedTransaction(string UID, string pwd)

{

   // Create and open the connection.

   OleDbConnection conn = new OleDbConnection();

   StringBuilder sb = new StringBuilder();

   sb.Append("Jet OLEDB:System database=");

   sb.Append(@"C:\Databases\system.mdw;");

   sb.Append(@"Data Source=C:\Databases\orders.mdb;");

   sb.Append("Provider=Microsoft.Jet.OLEDB.4.0;");

   sb.Append("User ID=" + UID + ";Password=" + pwd);

   string connString = sb.ToString();

   conn.ConnectionString = connString;

   conn.Open();

   // Create the commands.

   string cmdString = "Insert into Orders"

      + " (OrderID, OrderDate, CustomerID)"

      + " values('ABC60', #4/14/04#, 456)";

   OleDbCommand cmdInsertOrder = new OleDbCommand(cmdString,conn);

   //No need to insert OrderLineID, as that is an AutoNumber field.

   cmdString = "Insert into OrderLines (OrderID, PartID, Quantity)"

      + " values('ABC60', 25, 10)";

   OleDbCommand cmdInsertOrderLine = new OleDbCommand(cmdString,conn);

   cmdString = "Insert into PickList (OrderID, PartID, Quantity) values('ABC60', 25, 10)";

   OleDbCommand cmdCreatePickList = new OleDbCommand(cmdString,conn);

   // Begin the outer transaction and enlist the order-related commands.

   OleDbTransaction tran = conn.BeginTransaction();

   cmdInsertOrder.Transaction = tran;

   cmdInsertOrderLine.Transaction = tran;

           

   try

   {

      // Execute the commands   to create the order and order line items.

      cmdInsertOrder.ExecuteNonQuery();

      cmdInsertOrderLine.ExecuteNonQuery();

      // Create a nested transaction that allows the pick list creation to succeed or fail

      // separately if necessary.

      OleDbTransaction nestedTran = tran.Begin();           

      // Enlist the pick list command.

      cmdCreatePickList.Transaction = nestedTran;

      try

      {

         // Execute the pick list command.

         cmdCreatePickList.ExecuteNonQuery();

         // Commit the nested transaction.

         nestedTran.Commit();

      }

      catch(OleDbException ex)

      {

         //Roll back the transaction.

         nestedTran.Rollback();

         // Add code to notify user or otherwise handle the fact that the procedure was only

         // partially successful.

      }

      // Commit the outer transaction.

      tran.Commit();

   }

   catch(OleDbException ex)

   {

      //Roll back the transaction.

      tran.Rollback();

      //Additional error handling if needed.

   }

   finally

   { conn.Close();  }}