Because we kept the default “GeneratedDBDirect Methods”
checkbox selected, the Create Table Adapter wizard automatically added default
Insert, Update and Delete methods to the SuppliersTableAdapter. You can see
these, as well as edit/customize them further, by selecting the SuppliersTableAdapter
object within the DataSet Designer and then looking at the property-grid (note:
you must select the SuppliersTableAdapter heading to have these methods show up
in the property grid – they won’t show up if you just select the Suppliers
heading):
Figure 21
Using the “CommandText” property in the property-grid, you
can pull up a query designer for each of the default statements and customize
them:
Figure 22
You can also optionally add your own custom
Insert/Update/Delete methods to each TableAdapter. For example, if I wanted to
add a custom Insert method that had the additional behavior of returning the
new identity column value of a newly created Supplier row (which has an
auto-increment property set for the primary key), I could do so by right-clicking
on the SuppliersTableAdapter and choosing “New Query”:
Figure 23
I’ll then pick doing the INSERT command with a SQL statement, and choose to create an Insert:
Figure 24
The DataSet designer will then automatically suggest the
below SQL statement for me (it looks at the SELECT statement you entered
earlier to suggest a default INSERT statement):
Figure 25
Note that the “SELECT @@Identity” statement at the end will
return the newly created primary key from the insert operation. I could further
customize the INSERT operation however I want.
I can then use the wizard to name the method
“InsertSupplier” (or any other name I want). The wizard will then add this
method to the SuppliersAdapter. The last step I’ll do (since I want to return
the @@Identity value from the insert), is to change the type of the
InsertSupplier method from “NonQuery” to “Scalar”:
Figure 26
You could then write the code below to add a new supplier,
and then update the supplier values, and then delete the supplier within the
Suppliers table:
Dim supplierAdapter As New NorthwindTableAdapters.SuppliersTableAdapter
Dim supplierId As Integer
supplierId = supplierAdapter.InsertSupplier("Microsoft" _
,"ScottGu" _
,"General Manager" _
, "One Microsoft Way" _
, "Redmond" _
, "USA" _
,"98004" _
,"425-555-1212")
supplierAdapter.Update("Microsoft" _
, "Someone Else" _
, "New title" _
, "New Address" _
, "New City" _
, "UK" _
, "New Zip" _
, "New Number" _
, supplierId)
supplierAdapter.Delete(supplierId)Note that I did not have to write any ADO.NET code, manually
create any parameter collections, or manage connection objects etc. Because
the data designer generates typed methods and DataTables, I’ll get both
intellisense/compilation-checking as well as type validation within my DAL (so
for example: if I try to pass an integer instead of a DateTime it would give me
a compile error).
