VS.net Secret to Rapid Application Development - Databinding and Adapters

I'll let you in on the VS.net secret to RAD.

Databinding is the key. Never manually code an adapter or a command object in a form's code behind as it will slow down development significantly.
You should drag a dataadapter object from the data toolbar.

Configure the dataadapter using the wizard. This will let you specify a connection, design sql queries, optionally create stored procs, and will create all your select/insert/update/delete commands as well as parameters (in the advanced options you should disable insert/update/delete command creation for read-only forms).

• <Tip>
  In your select statement you can add a parameter to your where clause as follows: " .... from Customers where CustID = @CustID"
  In code you can set this parameter through the adapter's select command: me.adapterCustomers.selectcommand.parameters("@CustID").value = 1103
  For adapters other than sql, use "?" and the index of the "?" within the query.
  " .... from Customers where CustID = ?" (this is the first "?" so it has index 0)
  me.adapterCustomers.selectcommand.parameters(0).value = 1103

Rename your dataadapter (i.e. AdapterCustomerDetail).
Right click on your newly added dataadapter, then click generate dataset. Create a new dataset with a proper name (i.e. DatasetCustomerDetail).

• <Tip>
  After initial adapter creation you can edit the select/insert/update/delete commands seperately through the properties window for the adapter or you can reconfigure them by right clicking on the adapter and selecting configure dataadapter. (the first method is useful for adding additional read-only columns after generating the insert/update/delete code using the wizard.)
  In either case you will want to regenerate your dataset. (this time select the existing dataset rather than a new one)

Now that you have your dataset and adapter on your form, you are ready to databind.
Drop any number of controls on your form. In the control's property window, select databinding then text or value or selected value, or whatever other property you want to bind to. Then select your dataset.table.column from the dropdown. Do this for each control.

• <Tip>
  To populate comboboxes (and other list controls): add another dataadapter(select command only) and generate another dataset. Then set the combobox datasource property to the new dataset.table and the displaymember to the display column name and the valuemember to the value column.

Now you have to code something:

• <Windows>
  <To populate the form (usually on form load)>
  'If you have parameters set them here
  me.adapterCustomers.selectcommand.parameters("@CustID").value = 1103
  'Fill the dataset
  me.adapterCustomers.Fill(me.datasetCustomers)
  'If you have multiple records you will want to specify which record you are working on
  Me.BindingContext(me.datasetCustomers, "CustomerTableName").Position = 10 'The record's index within the table (0 based)
  
  <To save changes back to the database (usually on form close or save button click)>
  'Save any in progress edits.
  Me.BindingContext(me.datasetCustomers, "CustomerTableName").EndCurrentEdit()
  'Save dataset changes to the database
  me.adapterCustomers.Update(me.datasetCustomers)
  
  
• <Web>
  <To populate the form (usually on page load if not isPostback>)
  'If you have parameters set them here
  me.adapterCustomers.selectcommand.parameters("@CustID").value = 1103
  'Fill the dataset (this should return only the record you are working on based on your parameter above)
  me.adapterCustomers.Fill(me.datasetCustomers)
  
  <To save changes back to the database (usually on save button click)>
  'Manually set each element in the database based on the values in the form controls. Example for a Soc Sec Number column.
  If me.textboxSocialSecurityNumber.text = "" then
  me.datasetCustomers.CustomerTableName(0).SetSocialSecurityNumberNull
  Else
  me.datasetCustomers.CustomerTableName(0).SocialSecurityNumber = me.textboxSocialSecurityNumber.text
  End If
  'Save dataset changes to the database
  me.adapterCustomers.Update(me.datasetCustomers)

--To convert to C#
me = this
use the C# "if else" syntax.

That's it.

Using ADO.NET OleDb with Access to lock a record and modify a table.

Here is an example of using the Server explorer in VS.net to run a "Stored Query" and select and modify from a "Select Query". Note, usually you wouldn't need to do the locking proc you could just use an autoincrementing column type and use the optimistic concurrency automatically built into the adapter's update and delete commands. Lets assume you need to lock the record for some other reason.

Start by setting up a table and two Queries in Access:
Table Table1:
IncrementalKey Int (Primary Key)
LockExpirationDate Date/Time (This will be used to Lock/Unlock the record)

Query SelectTable1:
SELECT [Table1].[IncrementalKey], [Table1].[LockExpirationDate] FROM Table1;

Query UpdateExpiration:
UPDATE Table1 SET Table1.LockExpirationDate = DateAdd("n",10,Now())
WHERE [Table1].[IncrementalKey]=[@IncrementalKey];


Now, Open your Server Explorer in VS.NET.
Right-click Data Connections and click "Add Connection".
Select the latest JET provider and specify the location of the mdb file.
Test your connection and press OK.



You should now see the connection begining with "ACCESS." under Data Connections.
Expand the Views and StoredProcedures sections under the connection.
Drag the SelectTable1 view and the UpdateExpiration Stored Procedure onto your form/control. You can choose to store your password or provide it at runtime.

You now have a command, an adapter, and a connection on your form.


Unfortunately, OleDb does not recognize parameters in "Stored Queries", so we must add one for our "@IncrementalKey" parameter.
Right click on the OleDbCommand1 below your form and select properties.
Select Parameters and click the "..." button in the Properties window.
Click Add. Change the type to Integer.

We also need to remove the optimistic concurrency for the LockExpirationDate field from the update and delete commands since we are using a stored procedure to modify them after the fill.
Right click the adapter and select properties.
For both the update and delete commands, modify the command text from:
WHERE (IncrementalKey = ?) AND (LockExpirationDate = ? OR ? IS NULL AND LockExpirationDate IS NULL)
To:
WHERE (IncrementalKey = ?)

To do this, expand the update/deleteCommand Property, select CommandText, and click the "..." button.
Modify the text and make sure "regenerate parameters is checked". Click OK. Select YES to regenerate the parameters (nothing is lost since we haven't modified the parameters collection outside of the initial generation).
Do that for both commands.

Now, Right click on the adapter and generate a dataset.
(See .NET Databinding Secret to RAD blog)
I named it DataSetAccessLocking
Now drop a datagrid and bind it to DataSetAccessLocking1.table1.

Put some code in your Sub New():

Private Sub myInitializer()
Me.OleDbCommand1.Connection.Open()
Dim dr As DataSetAccessLocking.Table1Row
Dim i As Integer
Me.OleDbDataAdapter1.Fill(Me.DataSetAccessLocking1)
For Each dr In Me.DataSetAccessLocking1.Table1
If dr.IsLockExpirationDateNull OrElse dr.LockExpirationDate < Date.Today Then
'Unlocked - Lock
Me.OleDbCommand1.Parameters(0).Value = dr.IncrementalKey
Me.OleDbCommand1.ExecuteNonQuery()
Else
'Locked - Set Row Error
dr.RowError = "Locked"
End If
Next
'Remove locked rows from results
With Me.DataSetAccessLocking1.Table1.Rows
For i = .Count - 1 To 0 Step -1
If .Item(i).RowError = "Locked" Then
.Remove(.Item(i))
End If
Next
End With
Me.OleDbCommand1.Connection.Close()
End Sub
#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'Add any initialization after the InitializeComponent() call
myInitializer()
End Sub

And override the OnClosed Subroutine to save and unlock:

Protected Overrides Sub OnClosing(ByVal e As System.EventArgs)
MyBase.OnClosed(e)
Dim dr As DataSetAccessLocking.Table1Row
For Each dr In Me.DataSetAccessLocking1.Table1.Rows
dr.SetLockExpirationDateNull()
Next
Me.OleDbDataAdapter1.Update(Me.DataSetAccessLocking1)
End Sub