TextBox Lazy Binding

Karanig

Rate me:

0.00/5 (No votes)

17 Aug 2009CPOL1 min read

17.8K

128

I feel lazy when need to bind data to TextBox

Download LazyBinding.zip - 37.95 KB

Introduction

This code used to bind data from DataTable to TextBox automatically.

Background

Due to my company's software development need to bind data to controls manually.

When I am working. It feel like pain in my ...

So I think it should be the way to make it easy for me. Then I try it out

Using the code

This code is very simple. It's compare controls name and field name.

Condition

Control name need to be the same as field/column name. With my company design we using prefix to tell type of control for control id such as "txtData1" (txt for TextBox)

In this code it have 4 TextBox

- txtData1

- txtData2

- txtData3

- txtData4

So I need to replace "txt" with "" when try to bind data to TextBox

In DataTable I added 4 Column

- Data1

- Data2

- Data3

- Data4

This is the key code.

VB.NET

        'This snipet will loop all control in form 
        'But if you have GroupBox or any container 
        'You have to check. if it any container you need to loop in that container too.
        For Each ctrl As Control In Me.Controls
            Try
                'Binding data to TextBox 
                'By Replace txt with blank 
                'Example we loop to txtData1.Text = dt.Rows(0)("txtData1".Replace("txt", ""))                             
                ctrl.Text = dt.Rows(0)(ctrl.Name.Replace("txt", ""))
            Catch ex As Exception
                'just ignore it if it cannot bind 
                '(Any control name that replace "txt" with "" and not match with field/column name)
            End Try
        Next

Form load I add some demo data and bind for first load.

VB.NET

Public Class Form1
    'Declare DataTable
    Dim dt As DataTable

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.dt = New DataTable

        'Add Column to DataTable
        Me.dt.Columns.Add("Data1")
        Me.dt.Columns.Add("Data2")
        Me.dt.Columns.Add("Data3")
        Me.dt.Columns.Add("Data4")
        Me.dt.AcceptChanges()

        'Insert Some Data To DataTable
        For i As Integer = 0 To 10
            Dim dr As DataRow = Me.dt.NewRow
            dr("Data1") = i
            dr("Data2") = i & i
            dr("Data3") = i & i & i
            dr("Data4") = i & i & i & i
            dt.Rows.Add(dr)
        Next

        'This snipet will loop all control in form 
        'But if you have GroupBox or any container 
        'You have to check. if it any container you need to loop in that container too.
        For Each ctrl As Control In Me.Controls
            Try
                'Binding data to TextBox 
                'By Replace txt with blank 
                'Example we loop to txtData1.Text = dt.Rows(0)("txtData1".Replace("txt", ""))                             
                ctrl.Text = dt.Rows(0)(ctrl.Name.Replace("txt", ""))
            Catch ex As Exception
                'just ignore it if it cannot bind 
                '(Any control name that replace "txt" with "" and not match with field/column name)
            End Try
        Next
        'Set label text to current row number
        Me.lblRecrodNumber.Text = 0
    End Sub

btnBack I use it for navigate back.

VB.NET

'Navigate Back
Private Sub btnBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBack.Click
    'Get current row and minus for navigate back
    Dim curRow As Integer = CInt(Me.lblRecrodNumber.Text) - 1
    If curRow > 0 Then
        For Each ctrl As Control In Me.Controls
            Try
                ctrl.Text = dt.Rows(curRow)(ctrl.Name.Replace("txt", ""))
            Catch ex As Exception

            End Try
        Next
        'Set label text to current row number
        Me.lblRecrodNumber.Text = curRow
    Else
        MessageBox.Show("Already at first record.")
    End If
End Sub

btnNext I use it for navigate Next.

VB.NET

'Navigate Next
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
    'Get current row and plus for navigate next
    Dim curRow As Integer = CInt(Me.lblRecrodNumber.Text) + 1
    If curRow < Me.dt.Rows.Count Then
        For Each ctrl As Control In Me.Controls
            Try
                ctrl.Text = dt.Rows(curRow)(ctrl.Name.Replace("txt", ""))
            Catch ex As Exception

            End Try
        Next
        'Set label text to current row number
        Me.lblRecrodNumber.Text = curRow
    Else
        MessageBox.Show("Already at last record.")
    End If
End Sub

But I cannot use this code with my company software.

Because it doesn't follow my company software design.

And it may be confuse another developer in my company.

Points of Interest

This code may be useful for another project or may applied for anything else.

Even if it's looks like crazy or useless but I think it's good for me to try.

History

14/08/2009 23:47

This is my first article I just want to share what I interested.

Don't be hesitate to tell if you have any advice

PS. Sorry for my bad Grammar.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Karanig

Thailand

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

Hi, you're onto something!

You'll want to recursively scan controls list in panels and groupboxes.

I'm using a *very* similar technique, you can teach it to react to missing stuff intelligently, so data-bound screens react intelligently if bound to a slightly different schema, it takes *way* less boilerplate than MS tactics.

I also built my class so it mimics how I used ADO recordsets under VB6 - after I delete the VB6_AddADODataBinding and rename the fields, I not only have less code than the ADO.NET bloat you get with their generated classes, I have less code than the original VB6 stuff. Yeehaw.

Forcing the control names to match the schema makes your code easier to read / maintain.

Most consulting companies will despise this tactic, it takes lots less time, and is more resilient in the face of schema changes. It doesn't optimize for billable hours. People will claim this is inefficient, binding at run-time - I've tested a form with over 120 data-bound controls, the overhead isn't detectable.

I'm very, very new to ADO.NET, so pardon anything dumb you encounter in here, feedback way welcome. This is working, but probably not fully debugged or complete yet. There may still be bits in what I pasted here that you need to comment out...

Also, if someone knows how to force it to generate update commands that take advantage of the primary key explicitly, rather than depending on the DB's query optimizer, I'd like to hear about it.

Here's what I did (it's for PostgreSQL, but I think it can be easily adapted for other ADO.NET providers.)

'
'	Simplified, developer-friendly data binding.  Hopefully.
'	Uses control names to infer required binding at run time.
'
Class ADOBindery
	'
	Public Event PositionChanged()
	'
	Private WithEvents MyCurrencyManager As CurrencyManager

	Dim m_conn As Npgsql.NpgsqlConnection
	Dim m_cmd As Npgsql.NpgsqlCommand
	Dim m_ds As System.Data.DataSet
	Dim m_da As Npgsql.NpgsqlDataAdapter
	'
	'	These are saved at RunBoundQuery to support Refresh
	'
	Dim mvarSQL As String
	Dim mvarControlPrefix As String
	'
	Dim CallingForm As System.Windows.Forms.Form
	'
	'	If provided, we feed back info in these labels.
	'
	Dim LABEL_NumRows As System.Windows.Forms.Label
	Dim LABEL_Row As System.Windows.Forms.Label
	Dim LABEL_StatusMessage As System.Windows.Forms.Label
	Dim EditPanel As Object	' panel or groupbox
	'
	'
	'
	Structure TableBinder
		Dim ControlNamePrefix As String
		Dim BindToTable As System.Data.DataTable
	End Structure
	'
	'
	'
	Dim Bindery As Collections.Generic.Dictionary(Of String, TableBinder)
	'
	'
	'
	'		LastUsedBy = NVL(PasswordsDataSet.Tables(0).Rows(PasswordsBindingSource.Position)("UsedBy_UsersID"), 0)
	Public Function ColumnValue(ByVal ColName As String) As Object
		On Error GoTo Bink
		ColumnValue = m_ds.Tables(0).Rows(MyCurrencyManager.Position)(ColName)
		Exit Function
Bink:  msgbox(ColumnValue" & ColName & " failed: " & Err.Description)
		Resume B1
B1:
	End Function
	'
	'
	'
	Public Sub SetColumn(ByVal ColName As String, ByVal ToWhat As Object)
		On Error GoTo bink
		m_ds.Tables(0).Rows(MyCurrencyManager.Position)(ColName) = ToWhat
		Exit Sub
Bink:  msgbox("SetColumn " & ColName & " failed: " & Err.Description)
		Resume b1
B1:
	End Sub
	'
	'	HasRows
	'
	Public Function HasRows() As Boolean
		HasRows = (m_ds.Tables(0).Rows.Count > 0)
	End Function
	'
	'
	'
	Public Sub New(ByVal pCallingForm As System.Windows.Forms.Form _
					, ByVal Callback_Label_NumRows As System.Windows.Forms.Label _
					, ByVal Callback_Label_Row As System.Windows.Forms.Label _
					, ByVal Callback_Label_StatusMessage As System.Windows.Forms.Label _
					, ByVal Callback_EditPanel As Object _
					)
		CallingForm = pCallingForm
		LABEL_NumRows = Callback_Label_NumRows
		LABEL_Row = Callback_Label_Row
		LABEL_StatusMessage = Callback_Label_StatusMessage
		EditPanel = Callback_EditPanel
	End Sub
	'
	'
	'
	Public Function RunBoundQuery(ByVal ControlNamePrefix As String _
								, ByVal TheQuery As String _
								, ByVal ConnectionString As String _
								) As Integer
		On Error GoTo Bink
		mvarSQL = TheQuery
		mvarControlPrefix = ControlNamePrefix
		'
		Bindery = New Collections.Generic.Dictionary(Of String, TableBinder)
		'
		' clear the grid.
		' no idea which parts of this next block are actually necessary.
		'
		m_conn = New Npgsql.NpgsqlConnection
		m_cmd = New Npgsql.NpgsqlCommand
		m_ds = New System.Data.DataSet
		m_da = New Npgsql.NpgsqlDataAdapter
		m_da.SelectCommand = m_cmd
		m_conn.ConnectionString = ConnectionString
		m_cmd.Connection = m_conn
		RunBoundQuery = Refresh()
		Exit Function

Bink:  msgbox(Err.Description)
	Resume B1
B1:
	End Function
	'
	'	Enter here after first setup, and to 'refresh'.
	'
	Public Function Refresh() As Integer
		On Error GoTo Bink
		'
		'
		'
		m_cmd.CommandText = ""	' remove the query

		m_ds.Clear()			' clear the dataset
		m_ds.Tables.Clear()		' critically necessary magic.  Wingardium, Poofiosa.
		m_da.Fill(m_ds)			' fill the dataset from the adapter
		'
		' run query and put results in grid
		'
		m_cmd.CommandText = mvarSQL
		m_da.Fill(m_ds)	' adapter fills dataset
		'
		'	Create a currencymanager to keep track of position in dataset.
		'
		MyCurrencyManager = CType(CallingForm.BindingContext(m_ds.Tables(0)), CurrencyManager)

		AddBindTable(mvarControlPrefix, m_ds.Tables(0))
		BindOMatic(CallingForm)

		Dim MyBuilder As Npgsql.NpgsqlCommandBuilder
		MyBuilder = New Npgsql.NpgsqlCommandBuilder(m_da)
		If m_ds.Tables(0).Rows.Count > 0 Then
			m_da.UpdateCommand = MyBuilder.GetUpdateCommand(m_ds.Tables(0).Rows(0))
			DebugPrint(m_da.UpdateCommand.CommandText)
		End If
		UpdateStatusStuff()
		If (m_ds.Tables(0).Rows.Count > 0) Then
			RaiseEvent PositionChanged()
		Else
			If Not EditPanel Is Nothing Then
				EditPanel.visible = False
			End If
		End If
		Exit Function

Bink:  msgbox(Err.Description)
	Resume B1
B1:
	End Function
	'
	'
	'
	Private Sub UpdateStatusStuff()
		On Error GoTo Bink
		If Not LABEL_NumRows Is Nothing Then
			LABEL_Row.Text = MyCurrencyManager.Position + 1
		End If
		If Not LABEL_Row Is Nothing Then
			LABEL_NumRows.Text = m_ds.Tables(0).Rows.Count
		End If

		If Not EditPanel Is Nothing Then
			If (m_ds.Tables(0).Rows.Count > 0) Then
				EditPanel.visible = True
				EditPanel.bringtofront()
			Else
				EditPanel.visible = False
			End If
		End If
		Exit Sub
Bink:  msgbox("UpdateStatusStuff: " & Err.Description)
	Resume B1
B1:
	End Sub
	'
	'
	'
	Public Sub Delete()
		MyCurrencyManager.RemoveAt(MyCurrencyManager.Position)
		Update()
		UpdateStatusStuff()
		If m_ds.Tables(0).Rows.Count > 0 Then
			RaiseEvent PositionChanged()
		End If
	End Sub
	'
	'
	'
	Public Sub Update()
		On Error GoTo Bink
		If MyCurrencyManager Is Nothing Then
			Exit Sub ' somebody trying to save updates in generic routine, no need to save anything
		End If
		'
		Dim RowsAffected As Integer
		MyCurrencyManager.EndCurrentEdit()	 ' pushes out changes to current row - but not for addnew?
		RowsAffected = m_da.Update(m_ds.Tables(0))		' applies changes to DB
		DebugPrint("Update: Rows Affected: " & RowsAffected)
		If Not LABEL_StatusMessage Is Nothing Then
			LABEL_StatusMessage.Text = Now & " Rows updated: " & RowsAffected
		End If
		Exit Sub
Bink:  msgbox(Err.Description)
	Resume B1
B1:
	End Sub
	'
	'
	'
	Public Sub AddNew()
		MyCurrencyManager.AddNew()
		MyCurrencyManager.EndCurrentEdit()
		UpdateStatusStuff()
		If Not EditPanel Is Nothing Then
			EditPanel.visible = True
		End If
	End Sub
	'
	'
	'
	Public Sub MoveNext()
		If MyCurrencyManager.Position < m_ds.Tables(0).Rows.Count - 1 Then
			MyCurrencyManager.Position += 1
			RaiseEvent PositionChanged()
			If Not LABEL_Row Is Nothing Then
				LABEL_Row.Text = MyCurrencyManager.Position + 1
			End If
		Else
			Beep()
		End If
	End Sub
	'
	'
	'
	Public Sub MovePrevious()
		If MyCurrencyManager.Position > 0 Then
			MyCurrencyManager.Position -= 1
			RaiseEvent PositionChanged()
			If Not LABEL_Row Is Nothing Then
				LABEL_Row.Text = MyCurrencyManager.Position + 1
			End If
		Else
			Beep()
		End If
	End Sub
	'
	'	Add binding prefix, e.g. "DB".
	'		Any control starting with DB_ gets bound.
	'
	Public Sub AddBindTable(ByVal ControlNamePrefix As String _
							, ByRef WhichTable As System.Data.DataTable _
							)
		If ControlNamePrefix = "" Then
				Beep()
				msgbox("Error: AddBindTable, blank prefix.")
				Exit Sub
		End If
		'
		Dim Tbl As TableBinder
		Tbl = New TableBinder
		Tbl.ControlNamePrefix = ControlNamePrefix
		Tbl.BindToTable = WhichTable
		Bindery.Add(ControlNamePrefix, Tbl)
	End Sub
	'
	'	Call this with Form, Panel, or Groupbox.  Anything with list of controls.
	'
	Public Sub BindOMatic(ByVal anObject As Object _
							)
		Dim aControl As System.Windows.Forms.Control
		For Each aControl In anObject.Controls
			If TypeOf (aControl) Is System.Windows.Forms.Panel _
			Or TypeOf (aControl) Is System.Windows.Forms.GroupBox _
			Then
				BindOMatic(aControl)
			Else
				MaybeBindThis(aControl)
			End If
		Next
	End Sub
	'
	'	Check control name against list of prefixes
	'
	Private Sub MaybeBindThis(ByVal aControl As System.Windows.Forms.Control)
		On Error GoTo Bink
		Dim BoundTable As TableBinder
		For Each BoundTable In Bindery.Values
		If Left(aControl.Name, 3) = "DGI" Then
			Debug.Print("Aha.")
		End If
			If BoundTable.ControlNamePrefix & "_" = Left(aControl.Name, Len(BoundTable.ControlNamePrefix) + 1) Then
				Dim ExtractedFieldName As String
				Dim PropertyName As String
				Select Case TypeName(aControl)
					Case "CheckBox"
						PropertyName = "Checked"
					Case "TextBox", "Label", "AxBetterTextBoxControl"
						PropertyName = "Text"
					Case "DateTimePicker"
						PropertyName = "Value"
					Case "AxBetterCheckbox"
						PropertyName = "Value"
					Case "ComboBox"
						PropertyName = "Text"
					Case Else
						PropertyName = "Text"	' tbd.
						msgbox("MaybeBindThis - type: " & TypeName(aControl))
				End Select
				ExtractedFieldName = Mid(aControl.Name, Len(BoundTable.ControlNamePrefix) + 2)
				BindSomething(aControl, ExtractedFieldName, PropertyName, BoundTable.BindToTable)
			End If
		Next
		Exit Sub
Bink:  msgbox(MaybeBindThis: " & Err.Description))
	Resume B1
B1:
	End Sub
	'
	'	Attempt to bind something.  We are only slightly annoyed if it's not available,
	'	we will not throw a tantrum, just mutter a bit.
	'
	Public Sub BindSomething(ByVal AControl As Windows.Forms.Control _
							, ByVal AFieldName As String _
							, ByVal APropertyName As String _
							, ByVal BindToTable As System.Data.DataTable _
							)
		On Error GoTo Bink
		AControl.DataBindings.Clear()	' in case we ran this before
		AControl.DataBindings.Add(New System.Windows.Forms.Binding(APropertyName, BindToTable, AFieldName, False, DataSourceUpdateMode.OnPropertyChanged))
		Exit Sub

Bink:
		DebugPrint("Error binding '" & AFieldName & "' of type '" & APropertyName & "'" & vbCrLf & Err.Description)
		AControl.DataBindings.Clear()	' clear evil binding.
		AControl.Visible = False
	Resume B1
B1:
	End Sub
	'
	'
	'
	Public Function RecordCount() As Integer
		On Error GoTo bink
		RecordCount = m_ds.Tables(0).Rows.Count
		Exit Function

Bink:  Debug.Print("Recordcount got error: " & Err.Description)
		Resume b1
B1: RecordCount = 0
	End Function
	'
	'
	'
	Public Property Position() As Integer
		Get
			Position = MyCurrencyManager.Position
		End Get
		Set(ByVal value As Integer)
			MyCurrencyManager.Position = value
		End Set
	End Property
	'
	'
	'
	Public Sub Close()
		m_ds.Dispose()
		m_da.Dispose()
		m_conn.Dispose()
		m_cmd.Dispose()
	End Sub
End Class

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.