Developing with dbicList

Unlike a grid control, the dbicList control is primarily a data presentation interface. NOTE: The control does accept user input into its checkbox data type, however all other data types are read only. The dbicList control does provide an excellent interface for selecting a record(s) in a table for editing or deletion. Accordingly, data binding the dbicList control is essentially a read data style of binding with consideration for selecting a record (or records) and presenting the detail in or deleting the record from the database.

To bind the control to a table or view, the developer should perform the following;

1. Create a Connection to the database containing the data.

2. Create a Data Adapter to maintain a snapshot of the data in memory.

3. Create a Data Table containing the records to bind into the dbicList control.

4. Iterate (For/Each) through the Data Table to add the records to the dbicList control as dbiNodeItems.

5. Store the Record (Row in the Data Table) in the Tag property of the dbiNodeItem representing the record.

NOTE: The following example requires a reference to System.Data in the project and creates a connection to a Microsoft Jet (.mdb) format database.

Example:

VB.NET

‘NOTE: These declarations are scoped for the application to use.

'Create a Connection, a DataAdapter and a DataTable

Dim mdbConnection As Data.OleDb.OleDbConnection

Dim daContacts As OleDb.OleDbDataAdapter

Dim dtContacts As DataTable

Dim mdbConnectionString As String

Dim strRecordsToSelect As String

mdbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + <PATH TO DATABASE>

mdbConnection = New Data.OleDb.OleDbConnection(mdbConnectionString)

'Select all fields from all records in the Contacts Table in the database

strRecordsToSelect = "SELECT * FROM Contacts"

'Create the Data Adapter with the Select statement and the connection

daContacts = New OleDb.OleDbDataAdapter(strRecordsToSelect, mdbConnection)

'Add the delete and update schemas for the table.

daContacts.MissingSchemaAction = MissingSchemaAction.AddWithKey

Public dcbContacts As New OleDb.OleDbCommandBuilder(daContacts)

'Create the Data Table

dtContacts = New DataTable("Contacts")

'Fill the Data Table with the Data Adapter

daContacts.Fill(dtContacts)

//NOTE: These declarations are scoped for the application to use.

//Create an Connection, a DataAdapter and a DataTable

System.Data.OleDb.OleDbConnection mdbConnection;

System.Data.OleDb.OleDbDataAdapter daContacts;

DataTable dtContacts;

String mdbConnectionString;

String strRecordsToSelect;

mdbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + <PATH TO DATABASE>;

mdbConnection = new System.Data.OleDb.OleDbConnection(mdbConnectionString);

//Select all fields from all records in the Contacts Table in the database

strRecordsToSelect = "SELECT * FROM Contacts";

//Create the Data Adapter with the Select statement and the connection

daContacts = new System.Data.OleDb.OleDbDataAdapter(strRecordsToSelect, mdbConnection);

//Add the delete and update schemas for the table.

daContacts.MissingSchemaAction = MissingSchemaAction.AddWithKey;

System.Data.OleDb.OleDbCommandBuilder dcbContacts = new _

System.Data.OleDb.OleDbCommandBuilder(daContacts);

//Create the Data Table

dtContacts = new DataTable("Contacts");

//Fill the Data Table with the Data Adapter

daContacts.Fill(dtContacts);

The data table consists of “rows” of data corresponding to the Select statement issued against the adapter. The rows correspond to the dbiNodeItem objects in the dbicList control. The fields in the rows represent the data to be bound to the columns in the dbiNodeItems. For the example, to fill the list with Contact Names, Addresses, and Telephone Numbers, the dbicList would require 3 columns, one for each piece of data. For more information on adding columns see the Developing With dbicList - Adding Columns topic.

Example:

VB.NET

'Add three columns to the dbicList control

Dim columnName As New Dbi.WinControl.dbiColumnItem

Dim columnAddress As New Dbi.WinControl.dbiColumnItem

Dim columnTelephone As New Dbi.WinControl.dbiColumnItem

columnName.Text = "Contact Name"

columnName.Width = 120

columnName.DataType = Dbi.WinControl.enumDataType.Text

columnAddress.Text = "Address"

columnAddress.Width = 150

columnAddress.DataType = Dbi.WinControl.enumDataType.Text

columnTelephone.Text = "Telephone"

columnTelephone.Width = 75

columnTelephone.DataType = Dbi.WinControl.enumDataType.Text

Me.dbicList1.Columns.Add(columnName)

Me.dbicList1.Columns.Add(columnAddress)

Me.dbicList1.Columns.Add(columnTelephone)

'Set the delimiter for the columns to a semi-colon

'NOTE: When adding an item to the list, a semi-colon

'(as specified above) delimits the values for the fields.

Me.dbicList1.BreakChar = ";"

//NOTE: These declarations are scoped for the application to use.

//Add three columns to the dbicList control

Dbi.WinControl.dbiColumnItem columnName = new Dbi.WinControl.dbiColumnItem();

Dbi.WinControl.dbiColumnItem columnAddress = new Dbi.WinControl.dbiColumnItem();

Dbi.WinControl.dbiColumnItem columnTelephone = new Dbi.WinControl.dbiColumnItem();

columnName.Text = "Contact Name";

columnName.Width = 120;

columnName.DataType = Dbi.WinControl.enumDataType.Text;

columnAddress.Text = "Address";

columnAddress.Width = 150;

columnAddress.DataType = Dbi.WinControl.enumDataType.Text;

columnTelephone.Text = "Telephone";

columnTelephone.Width = 75;

columnTelephone.DataType = Dbi.WinControl.enumDataType.Text;

this.dbicList1.Columns.Add(columnName);

this.dbicList1.Columns.Add(columnAddress);

this.dbicList1.Columns.Add(columnTelephone);

//Set the delimiter for the columns to a semi-colon

//NOTE: When adding an item to the list, a semi-colon

//(as specified above) delimits the values for the fields.

this.dbicList1.BreakChar = ';';

To load the data into the dbicList control, iterate through the rows collection of the data table and add dbiNodeItems to the Items collection in the dbicList control. NOTE: When the record from the table (row) is added to the list as a dbiNodeItem, it is recommended that the row itself be stored in the “Tag” property of the dbiNodeItem object. This allows the developer to access the original record any time the dbiNodeItem receives an action (i.e. Click, Selected, Deleted, etc.) and reflect actions on the dbiNodeItem back to the record in the table.

Example:

VB.NET

Dim dbiNodeItem2Add As Dbi.WinControl.dbiNodeItem

Dim dbiNodeItemText As String

'Create a data row to represent each record in the table

Dim row As DataRow

For Each row In dtContacts.Rows

'Create a string consisting of the value for each column in the dbicList control.

'NOTE: The value for each column is delimited with a semi-colon as set above in the BreakChar property.

dbiNodeItemText = row("ContactName") + ";" + _

row("Address") + "," + row("City") + "," + row("Region") + " " + _

row("PostalCode") + ";" + _

row("Phone")

'Create a new dbiNodeItem object using the overloaded method that passes in a string

dbiNodeItem2Add = New Dbi.WinControl.dbiNodeItem(dbiNodeItemText)

'Store the original record (row) in the tag property of the dbiNodeItem.

'NOTE: This relates the item in the list back to the original record in the table.

dbiNodeItem2Add.Tag = row

'Add the new dbiNodeItem to the Items collection in the dbicList control.

Me.dbicList1.Items.Add(dbiNodeItem2Add)

Dbi.WinControl.dbiNodeItem dbiNodeItem2Add;

String dbiNodeItemText;

foreach (System.Data.DataRow currentRow in dtContacts.Rows)

{

//Create a string consisting of the value for each column in the dbicList control.

//NOTE: The value for each column is delimited with a semi-colon as set above in the BreakChar property.

dbiNodeItemText = currentRow["ContactName"] + ";" +

currentRow["Address"] + "," + currentRow["City"] + "," + currentRow["Region"] +

" " + currentRow["PostalCode"] + ";" +

currentRow["Phone"];

//Create a new dbiNodeItem object using the overloaded method that passes in a string

dbiNodeItem2Add = new Dbi.WinControl.dbiNodeItem(dbiNodeItemText);

//Store the original record (row) in the tag property of the dbiNodeItem.

//NOTE: This relates the item in the list back to the original record in the table.

dbiNodeItem2Add.Tag = currentRow;

//Add the new dbiNodeItem to the Items collection in the dbicList control.

this.dbicList1.Items.Add(dbiNodeItem2Add);

}

The final step in data binding is to relate actions on the dbicList items back to their corresponding records in the database. Binding the item by placing the row (original record) in the tag property allows the developer direct access to the record in the table. For example, selecting a record in the dbicList control and pressing the Delete key removes the record from the table …

Example:

VB.NET

Private Sub dbicList1_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles _ dbicList1.KeyUp

If e.KeyCode = Keys.Delete Then

'The delete key was pressed

'Iterate throught the SelectedItems collection in the dbicList control and ...

'1. Remove the DataRow from the table related to the list item (DataRow is stored in the tag

' property of the item

'2. Remove the item from the dbicList Items collection

'NOTE: The collection is processed from bottom to top to account for the code removing an item

' from the collection which would generate an error in the index of the collection

' if processed from top to bottom.

Dim test As Int32

Dim selectedItemsCount As Int32

selectedItemsCount = Me.dbicList1.SelectedItems.Count

For test = selectedItemsCount - 1 To 0 Step -1

'Create a reference to the dataRow being deleted.

'NOTE: The dataRow for the item is stored in the item's tag propert

Dim row2Delete As DataRow = Me.dbicList1.SelectedItems(test).Tag

row2Delete.Delete() 'Delete the row from the dataTable.

'Update the dataTable and write the changes back to the database

daContacts.Update(dtContacts)

'Remove the nodeItem from the dbicList control

Me.dbicList1.Items.Remove(Me.dbicList1.SelectedItems(test))

End If

End Sub

private void dbicList1_KeyUp(object sender, KeyEventArgs e)

{

if (e.KeyCode == Keys.Delete)

//The delete key was pressed

{

//Iterate throught the SelectedItems collection in the dbicList control and ...

//1. Remove the DataRow from the table related to the list item (DataRow is stored in the tag

// property of the item

//2. Remove the item from the dbicList Items collection

//NOTE: The collection is processed from bottom to top to account for the code removing

// an item from the collection which would generate an error in the index of the collection // if processed from top to bottom.

Int32 selectedItemsCount;

selectedItemsCount = this.dbicList1.SelectedItems.Count;

for (Int32 test = selectedItemsCount - 1; test >= 0; test--)

{

//Create a reference to the dataRow being deleted.

//NOTE: The dataRow for the item is stored in the item's tag property

Dbi.WinControl.dbiNodeItem row2DeleteCast =

(Dbi.WinControl.dbiNodeItem)dbicList1.SelectedItems[test];

DataRow row2Delete = (System.Data.DataRow) row2DeleteCast.Tag;

row2Delete.Delete(); //Delete the row from the dataTable.

//Update the dataTable and write the changes back to the database

daContacts.Update(dtContacts);

//Remove the nodeItem from the dbicList control

this.dbicList1.Items.Remove(row2DeleteCast);

}

The updating of the physical database takes place using the data adapter. NOTE: Following the creation of the data adapter object the delete and update schema were attached to the data adapter using the OleDBCommandBuilder. Executing the Delete method on the data row causes the data table to register the delete action on the row. Executing the Update method on the Data Adapter causes the changes to the table to be written back to the database.

dbicList	Send comments on this topic.
Developing with dbicList - Data Binding