Monday, October 10, 2005

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.

No comments: