Inserting, Updating and Deleting Data using the Data Access Layer

There are two patterns commonly used for inserting, updating and deleting data.  

1. A database direct pattern - a rigid 1-to-1 replacement command
   
2. A batch update pattern - pass entire DataSet, DataTable or collection of DataRows to method call that determines appropriate request for each record

Guess which one is more flexible? Indeed, we will be looking at DAL implementations of pattern 2, batch update. By default the TableAdapter uses the batch update pattern. The capability to use this functionality is set in the wizard template under Advanced Options.  You can check the box "Generate Insert, Update and Delete statements," which I did.

If you look at the properties panel of the PropertiesTableAdapter, you will see the commands carried out using the InsertCommand, UpdateCommand and DeleteCommand.  They are available as well.

A generalized use of the batch update pattern to update a DB works like this:

1. Create instance of the ProductsDataTable
2. Call the Adapter method GetProducts() on the instance to populate it.
3. Crawl through each row, performing assignment operations where fitting.
4. Call the Adapter method Update(productsDataTableInstance) on the productsAdapter

That's all it takes.  The method evaluates changes to the table and assigns them to DB.

Now, what about creating a custom method to insert data?  Start by right-clicking on the TableAdapter and going into the wizard. Let's look at the procedure required to add a new product and then return the value of the newly added record's ProductID. Therefore, opt to create an INSERT query in the wizard.

When you get to the SQL Query window, type in this string in preparation of the new method:

INSET INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) 

VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);

--Return the ProductID for the newly created record...

SELECT SCOPE_IDENTITY()

Finally, name the new method InsertProduct.

Now all you have to do to add a new product is create parameters for the matching values:

NorthwindTableAdapters.ProductsTableAdapter productsAdapter = new NorthwindTableAdapters.ProductsTableAdapter();

//add a new product

int new_productID = Convert.ToInt32(productsAdapter.InsertProduct("New Product", 1, 1, "12 tins per carton", 12.95m, 10, 0, 10, false));

// or to delete the product

productsAdapter.Delete(new_productID);

It's a handy using the DAL once you get to know the details of it. More next time.