Before the control does anything, it has to be able to read the schema from the database based on the Select statement. Let's create a public subprocedure and call it ReadData. We want the procedure to behave a little differently when the control is displayed in a form designer than when it is running in an application. When the control is in a designer, we don't want to read data from the database. What we really want to do is to read the schema. The DataAdapter has a method just for this purpose called FillSchema. The question is, how do we detect if the control is in a designer or in design mode?
If you never developed controls in VB 6, you might be wondering what the heck I'm talking about. This can get a little confusing so please be patient. Controls execute in two modes, User and Design. When a control is in Design mode, it is sitting on a form that is being designed or open in the form designer. The control is actually executing. The form is being designed. The control is executing in Design mode.
When a control is in User mode it is running in the actual application. The control is executing in User (or application) mode. Both modes refer to a compiled control executing. One is executing in a designer, the other is executing in an application. Got It? Good, you're half way there!
In VB 6, you could access a property of the ambient object called User-Mode. This returned a value that indicated how the control was running. VB .NET controls no longer need the ambient object, so they have a Direct property called DesignMode which returns True if the control is running in a designer. So here is our ReadData procedure. We also will add a few more support procedures:
Public Sub ReadData() Try If mAdapter.SelectCommand.CommandText = "" Then Exit Sub If mDataSet.Tables.Count > 0 Then mDataSet.Tables.RemoveAt(mintBoundTable) End If If Me.DesignMode Then mAdapter.FillSchema(mDataSet, SchemaType.Source, _ mintBoundTable) Else mAdapter.Fill(mDataSet, mintBoundTable) RaiseEvent DataSetFilled(mDataSet) UpdatePosition() End If Catch errobj As Exception Throw errobj End Try End Sub Private Sub UpdatePosition() If Not Me.ParentForm Is Nothing Then If mblnAutoText Then lblText.Text = "Record: " & _ Me.ParentForm.BindingContext(Me).Position + 1 & _ " of " & _ mDataSet.Tables(mintBoundTable).Rows.Count End If End If End Sub Public Property AutoText() As Boolean Get AutoText = mblnAutoText End Get Set(ByVal Value As Boolean) mblnAutoText = Value Me.Text = "AutoText" End Set End Property Public Property SelectCommandText() As String Get SelectCommandText = mAdapter.SelectCommand.CommandText End Get Set(ByVal Value As String) mAdapter.SelectCommand.CommandText = Value ReadData() End Set End Property
It's pretty straightforward. If there is no Select statement assigned, the procedure exits. If the DataSet already has any table objects, then they are removed. We could also have used mDataset.Clear. This would clear all of the DataTables from the DataSet. If the DataSet were created outside the control and then passed in via the DataSet property, this is not what we would want to happen. We really want to remove only the bound table, or the table we will use to bind controls to. We want to leave any other tables alone.
Next we check to see if we are in Design mode. If we are, we want to get the schema from the database, but not the actual data. If we are not, we grab the whole kit and caboodle. We then fire an event that notifies the host that we have replaced the data in the DataSet. We then call a procedure that updates the Text property with Record: n of x if we have the autoText property turned on. The autoText property is a Boolean value that indicates if we want the Text property automatically updated when the DataTable's current row changes. If any errors occur they are thrown back to the calling procedure.