WinForms FAQ - Data Binding

Say you have a Parent table related to a Child table related to a GrandChildTable, and you want to bind a TextBox to a column in the GrandChild table.

To do this you have to have nested relations defined, and you use this nesting to create the DataMember parameter for the the DataBinding that you add to the TextBox. Below are some code snippets. They show the DataMember as ”ParentToChild.ChildToGrandChild.Name” which creates a path from the parent table down to the field in the nested relation by stringing the relation names together separated with a period. You can also download both C# and VB projects.

       Dim dSet As New DataSet()

        ’get the tables
        Dim parentTable As DataTable = GetParentTable()
        Dim childTable As DataTable = GetChildTable()
        Dim grandChildTable As DataTable = GetGrandChildTable()
        dSet.Tables.AddRange(New DataTable() {parentTable, childTable, grandChildTable})

        ’setup the relations
        Dim parentColumn As DataColumn = parentTable.Columns(''parentID'')
        Dim childColumn As DataColumn = childTable.Columns(''ParentID'')
        dSet.Relations.Add(''ParentToChild'', parentColumn, childColumn)

        parentColumn = childTable.Columns(''childID'')
        childColumn = grandChildTable.Columns(''ChildID'')
        dSet.Relations.Add(''ChildToGrandChild'', parentColumn, childColumn)

        Me.TextBox1.DataBindings.Add(''Text'', parentTable, ''ParentToChild.ChildToGrandChild.Name'')

You have to access a property called the Binding Context and then retrieve the BindingContext associated with the dataset and data member that you used for binding. After you have access to this object you just set the position property. You can move backward and forward through the dataset.

Download a working sample that shows this: simpledata5.zip

form.BindingContext[this.dataSet, ''Customers''].Position -= 1;

Remember that when you scroll through the dataset all associated controls will scroll since they all depend on the same context. This is useful if you want to have several controls that display sections of a row operate in tandem.

To be able to write changes back to the datasource, the data adapter object that populates your dataset should have commands set for updating, deleting etc. Fortunately, there is a class called SqlCommandBuilder that generates these commands from our Select command. All we have to do is instantiate this class passing it in the data adapter that we use.

Enclosed is a complete sample: simpledata4.zip

// Command builder will generate the command required to update the
// datasource from your select statement
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(this.dataAdapter);

After this is done whenever you wish to write changes back to the data source simply call Update on the data adapter as shown below.

if(this.dataSet != null && this.dataSet.HasChanges())
     this.dataAdapter.Update(this.dataSet, ''Customers'');

Here is a technique for binding an arraylist of objects where the objects contain public property that can appear as columns in the datagrid. In this example, the object contains 2 public doubles, one named value and the other named sqrt. To bind this arraylist to a datagrid, add a custom tablestyle that has a MappingName of ”ArrayList”, and then use the property names as the MappingName for each column. Below are some code snippets. You can download a working project that also has code to delete rows and add new rows to the bound arraylist.

private void Form1_Load(object sender, System.EventArgs e)
    {
      CreateArrayList();
      BindArrayListToGrid();
    }

    private void BindArrayListToGrid()
    {
      dataGrid1.DataSource = arrayList1;

      //create a custom tablestyle and add two columnstyles
      DataGridTableStyle ts = new DataGridTableStyle();
      ts.MappingName = ''ArrayList'';

      int colwidth = (dataGrid1.ClientSize.Width - ts.RowHeaderWidth - SystemInformation.VerticalScrollBarWidth - 5) / 2;

      //create a column for the value property
      DataGridTextBoxColumn cs = new DataGridTextBoxColumn();
      cs.MappingName = ''value''; //public property name
      cs.HeaderText = ''Random Number'';
      cs.Format = ''f4'';
      cs.Width = colwidth;
      ts.GridColumnStyles.Add(cs);

      //create a column for the sqrt property
      cs = new DataGridTextBoxColumn();
      cs.MappingName = ''sqrt'';  //public property name
      cs.HeaderText = ''Square Root'';
      cs.Format = ''f4'';
      cs.Width = colwidth;
      ts.GridColumnStyles.Add(cs);

      dataGrid1.TableStyles.Clear();
      dataGrid1.TableStyles.Add(ts);
    }

    private void CreateArrayList()
    {
      arrayList1 = new ArrayList();

      //add some items
      Random r = new Random();
      for (int i = 0; i < 20; ++i)
        arrayList1.Add(new RandomNumber(r.NextDouble()));

    }

    //create a struct or class that defines what you want in each row
    //the different columns in the row must be public properties
    public struct RandomNumber
    {
      private double number;

      public RandomNumber(double d)
      {
        number = d;
      }

      public double value 
      {
        get{ return number; }
        set{ number = value;}
      }

      public double sqrt
      {
        get {return Math.Sqrt(this.value);}
      }
    }

MYSQL can be used with ADO.NET. There is more information available here:

http://www.mysql.com/articles/dotnet/

No. Nicholas Paldino gives the following response in a position to the microsoft.public.dotnet.framework.odbcnet newsgroup.
There are no updateable cursors ANYWHERE in .NET for that matter. The way .NET handles data is you push it into a dataset. The dataset keeps track of the changes that are made, and then you pass the data set back to a data adapter, which will fire off the appropriate insert, update, and delete statements in order to update the underlying data source. All of the data access in .NET is based on a disconnected data model.

In Usenet posts, MS has acknowledged this bug. The problem is essentially that any time the visibility changes on a CheckedListBox, it loses its previous selections. Naturally this happens all the time in tab controls when changing tabs.

This derived Control saves it’s state while getting hidden and reloads it while getting shown:

[C#]
# region Workaround for CheckedListBox bug
		//////////////////////////////////////////////////////////////////////
		// When CheckedList box becomes invisible (e.g. when the tab page
		// containing this control is overlapped with another tab page),
		// checked state of the items are getting lost. This workaround will
		// fix this problem
		//////////////////////////////////////////////////////////////////////

		private bool[] isItemChecked;
		protected override void OnDataSourceChanged(EventArgs e)
		{
			base.OnDataSourceChanged(e);

			int cnt = this.Items.Count;
			isItemChecked = new Boolean[cnt];
			for(int i = 0; i < cnt; i++)
			{
				isItemChecked[i] = GetItemChecked(i);
			}
		}

		protected override void OnItemCheck(ItemCheckEventArgs e)
		{
			base.OnItemCheck(e);
			isItemChecked[e.Index] = (e.NewValue == CheckState.Checked);
		}

		protected override void OnVisibleChanged(EventArgs e)
		{
			base.OnVisibleChanged(e);

			if (this.Visible == true)
			{
				// Reset the checked states when it becomes visible.
				for(int i =0; i < this.Items.Count; i++)
				{
					SetItemChecked(i, isItemChecked[i]);
				}
			}
		}
#endregion

[VB.Net]
Public Class MyCheckedListBox
   Inherits CheckedListBox
    ’////////////////////////////////////////////////////////////////////
    ’ When CheckedList box becomes invisible (e.g. when the tab page
    ’ containing this control is overlapped with another tab page),
    ’ checked state of the items are getting lost. This workaround will
    ’ fix this problem
    ’////////////////////////////////////////////////////////////////////
   Private isItemChecked() As Boolean
   
   Protected Overrides Sub OnDataSourceChanged(e As EventArgs)
      MyBase.OnDataSourceChanged(e)
      
      Dim cnt As Integer = Me.Items.Count
      isItemChecked = New [Boolean](cnt) {}
      Dim i As Integer
      For i = 0 To cnt - 1
         isItemChecked(i) = GetItemChecked(i)
      Next i
   End Sub ’OnDataSourceChanged
   
   
   Protected Overrides Sub OnItemCheck(e As ItemCheckEventArgs)
      MyBase.OnItemCheck(e)
      isItemChecked(e.Index) = e.NewValue = CheckState.Checked
   End Sub ’OnItemCheck
   
   
   Protected Overrides Sub OnVisibleChanged(e As EventArgs)
      MyBase.OnVisibleChanged(e)
      
      If Me.Visible = True Then
         ’ Reset the checked states when it becomes visible.
         Dim i As Integer
         For i = 0 To (Me.Items.Count) - 1
            SetItemChecked(i, isItemChecked(i))
         Next i
      End If
   End Sub ’OnVisibleChanged

End Class ’MyCheckedListBox 

(Contributed by Eric and Reddy Duggempudi in Syncfusion Windows Forms FAQ forums).

You can call the EndCurrentEdit method on the bindingmanager for the TextBox.

[C#]
	this.textBox1.Text = 'XXXX';   //set the value
	this.textBox1.DataBindings['Text'].BindingManagerBase.EndCurrentEdit();  //end the edit

[VB.NET]
	Me.TextBox1.Text = 'XXXX'
	Me.TextBox1.DataBindings('Text').BindingManagerBase.EndCurrentEdit()

You can call stored procedures in basically the same manner as executing other SQL commands.

When creating the SqlCommand, set the query string to be the name of the stored procedure, and then set the CommandType to be CommandType.StoredProcedure.

if(sqlConn.State == ConnectionState.Closed)sqlConn.Open();

SqlCommand cmd = new SqlCommand('sp_my_stored_procedure',sqlConn);
cmd.CommandTimeout = 180;
cmd.CommandType = CommandType.StoredProcedure;

After you setup the command type, you need to pass in any parameters required for the stored procedure. Here is an example of one input and one output parameter.

SqlParameter parm;

parm = cmd.Parameters.Add(new SqlParameter('@oid', SqlDbType.VarChar,50)); 
parm.Direction = ParameterDirection.Input; 
cmd.Parameters['@oid'].Value = OrderID;

parm = cmd.Parameters.Add(new SqlParameter('@custName', SqlDbType.VarChar,50)); 
parm.Direction = ParameterDirection.Output; 

If the stored procedure is returning a selection query at the end, you can fill your DataSet and retrieve any tables.

SqlDataAdapter tempDA = new SqlDataAdapter();
tempDA.TableMappings.Add('your mapping','your mapping');
tempDA.SelectCommand = cmd;
DataSet dataSet1 = new DataSet();
tempDA.Fill(dataSet1);
DataTable resultTable = dataSet1.Tables[0];

Or, if no tables are being returned, you can execute the command as a non-query

cmd.ExecuteNonQuery();

As the message suggests, the code is calling Control.DataBindings.Add twice with what amounts to the same parameters.

One way this might happen is if you call the same code more than once in your program to reload your datasource for some reason, and in this code, you have lines such as:

Me.TextBox1.DataBindings.Add('Text', myDataTable, 'Col1Name')
Me.TextBox2.DataBindings.Add('Text', myDataTable, 'Col2Name')
Me.TextBox3.DataBindings.Add('Text', myDataTable, 'Col3Name')

On the second call, this would attempt to add a duplicate binding to the DataBindings collection. One solution is to Clear the DataBindings collection before you add your new binding.

Me.TextBox1.DataBindings.Clear();
Me.TextBox1.DataBindings.Add('Text', myDataTable, 'Col1Name')
Me.TextBox2.DataBindings.Clear();
Me.TextBox2.DataBindings.Add('Text', myDataTable, 'Col2Name')
Me.TextBox3.DataBindings.Clear();
Me.TextBox3.DataBindings.Add('Text', myDataTable, 'Col3Name')

If you have two controls bound to the same datasource, and you do not want them to share the same position, then you must make sure that the BindingContext member of one control differs from the BindingContext member of the other control. If they have the same BindingContext, they will share the same position in the datasource.

If you add a ComboBox and a DataGrid to a form, the default behavior is for the BindingContext member of each of the two controls to be set to the Form’s BindingContext. Thus, the default behavior is for the DataGrid and ComboBox to share the same BindingContext, and hence the selection in the ComboBox is synchronized with the current row of the DataGrid. If you do not want this behavior, you should create a new BindingContext member for at least one of the controls.

[C#]
private void Form1_Load(object sender, System.EventArgs e)
{
	this.myDataTable = GetATable(); //get a datatable somehow...

	this.dataGrid1.DataSource = myDataTable;

	//set a new binding context for the combobox
	this.comboBox1.BindingContext = new BindingContext();
	this.comboBox1.DataSource = myDataTable;
	this.comboBox1.DisplayMember = 'Col1';
	this.comboBox1.ValueMember = 'Col1';
}

[VB.NET]
Private Sub Form1_Load(ByVal sender as Object, ByVal e as  System.EventArgs)
 
	Me.myDataTable = GetATable() ’get a datatable somehow...

	Me.dataGrid1.DataSource = myDataTable

	’set a new binding context for the combobox
	Me.comboBox1.BindingContext = New BindingContext()
	Me.comboBox1.DataSource = myDataTable
	Me.comboBox1.DisplayMember = 'Col1'
	Me.comboBox1.ValueMember = 'Col1'
End Sub

Here is a solution suggested by Elan Zhou (MS) on the microsoft.public.dotnet.languages.vb newsgroup.

You can also use the following code:
1. Put a DataGrid on the form.
2. Try the following sample code: (Suppose the csv is c:\test.csv.)

Imports System.Data
Imports System.Data.OleDb


Public Class Form1
	Inherits System.Windows.Forms.Form
	Dim objDataset1 As DataSet()

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        
		Dim sConnectionString As String = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=Text;'
		Dim objConn As New OleDbConnection(sConnectionString)
		objConn.Open()

		Dim objCmdSelect As New OleDbCommand('SELECT * FROM test.csv', objConn)
		Dim objAdapter1 As New OleDbDataAdapter()
		objAdapter1.SelectCommand = objCmdSelect

		Dim objDataset1 As New DataSet()
		objAdapter1.Fill(objDataset1, 'Test')
		DataGrid1.DataSource = objDataset1.Tables(0).DefaultView
		objConn.Close()
	End Sub
End Class

You can use the IsNull operator.

[C#]
	//the outer quotes are double quotes and the inner quotes are 2 single quotes
	//Fax is the column mapping name
	this.dataView1.RowFilter = 'IsNull(Fax, ’’) = ’’';

	// for a numeric null in custNo  (suggested by Bob Gibson)
	this.dataView1.RowFilter = 'IsNull(custNo, 0) = 0';

[VB.NET]
	’the outer quotes are double quotes and the inner quotes are 2 single quotes
	’Fax is the column mapping name
	Me.dataView1.RowFilter = 'IsNull(Fax, ’’) = ’’'

	’ for a numeric null in custNo  (suggested by Bob Gibson)
	Me.DataView1.RowFilter = 'IsNull(custNo, 0) = 0'

The data source component of your connection string should contain the port right after the IP address (or name). It should be separated from the IP by a comma.

data source=192.168.123.1, port number;

Take a look at this web site for great information on several connection strings.

http://www.connectionstrings.com/

You can use MSDE, a SQL server compatible database engine that MS makes available for free. You can download and install MSDE from a whole variety of sources. Here is one source.

MSDE also comes with Visual Studio.NET / .NET framework SDK. It is available under $(VS Install drive):\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Samples\Setup\msde. You can then run $(VS Install drive):\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Samples\Setup\configsamples.exe to configure the northwind database that the samples use.

Take a look at the Microsoft KB article, INFO: Roadmap for Windows Forms Data Binding (Q313482). This article gets you started. It also lists other Microsoft KB articles that have to do with using the Windows Forms DataGrid and databinding.

Make sure the two comboboxes use different BindngContext objects.

	BindingContext bc = new BindingContext();
	this.comboBox1.BindingContext = bc;
	comboBox1.DataSource = _dataSet.Tables['orders'];
	comboBox1.ValueMember = 'CustomerID';
	comboBox1.DisplayMember = 'CustomerID';

	bc = new BindingContext();
	this.comboBox2.BindingContext = bc;
	comboBox2.DataSource = _dataSet.Tables['orders'];
	comboBox2.ValueMember = 'CustomerID';
	comboBox2.DisplayMember = 'CustomerID';

Are you calling Update on the dataset like this without specifying the name of the table that you are updating. The problem is that when table names are not given the system assumes a table name of ’Table’. This of course causes the update to fail.

   this.dataAdapter.Update(this.dataSet);

If so just change this line to

   this.dataAdapter.Update(this.dataSet, 'Customers');
   // replace ’Customers’ with the table that you have

Think of a DataSet object as a local in memory copy of database tables. With the client server model, client applications held onto a connection and updated and added records at will. With ADO.NET the dataset presents a disconnected model. Data as well as data changes are contained in the dataset with no physical connection to the datasource.

Changes can be reconciled against any datasource at any time.

A Dataset is not limited to database tables. It can work with XML or for that matter any other data.

In an ArrayList, the ’plumbing’ is not available to support two-way binding as with a dataset. So, you have to handle the synchronization yourself. One way to do this is to set the listBox1.DataSource to null and then reset it to your ArrayList. Another way is to use the CurrencyManager as shown in the code below.

	private System.Windows.Forms.ListBox listBox1;
	private System.Windows.Forms.Button button1;
	private ArrayList myArrayList;

	public Form1()
	{
		//
		// Required for Windows Form Designer support
		//
		InitializeComponent();

		myArrayList = new ArrayList();

		myArrayList.Add('orange');
		myArrayList.Add('green');
		myArrayList.Add('blue');
		myArrayList.Add('red');
		
		listBox1.DataSource = myArrayList;
	}
	......
	//change the arraylist
	private void button1_Click(object sender, System.EventArgs e)
	{
		myArrayList[1] = 'pink';
		myArrayList.Add('lavendar');

		//use currency manger to sync up the listbox
		BindingManagerBase bm = this.listBox1.BindingContext[myArrayList];
		CurrencyManager cm = (CurrencyManager) bm;
		if (cm != null)
			cm.Refresh();
			
		//Or, you can just reset the datasource
		//listBox1.DataSource = null;
		//listBox1.DataSource = myArrayList;
	}

Use the FindStringExact method. For example, if you have set the ComboBox’s ValueMember property to ‘OrderID’, then you could call

comboBox1.SelectedIndex = comboBox1.FindStringExact(stringOrderID);

where stringOrderID is the ‘orderID’ of the row to be selected.

You can use the classes in the System.Data.OleDb namespace to read a MDB file into a ListBox and a ComboBox. You instantiate a OleDbConnection object using a connection string to your MDB file. You then instantiate a OleDbDataAdapter that uses this connection object and a SQL query. Next you create a DataSet object, use the OleDbDataAdapter to fill this dataset, and finally attached this dataset to your control. Here is the code that does this.

	private void Form1_Load(object sender, System.EventArgs e)
	{
		// Set the connection and sql strings
			// assumes your mdb file is in your root
			string connString = @'Provider=Microsoft.JET.OLEDB.4.0;data source=C:\northwind.mdb';
			string sqlString = 'SELECT * FROM customers';

			// Connection object
			OleDbConnection connection = new OleDbConnection(connString);

			// Create data adapter object
			OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sqlString, connection);

			// Create a dataset object and fill with data using data adapter’s Fill method
			DataSet dataSet = new DataSet();
			dataAdapter.Fill(dataSet, 'customers');
			
			// Attach dataset’s DefaultView to the combobox
			listBox1.DataSource = dataSet.Tables['customers'].DefaultView; 
			listBox1.DisplayMember = 'CustomerID';

			// Attach dataset’s DefaultView to the combobox
			comboBox1.DataSource = dataSet.Tables['customers'].DefaultView; 
			comboBox1.DisplayMember = 'CustomerID';
    	}

Notice that this code uses the same dataset object for both the listbox and the combobox. When done in this manner, the two controls are linked so that the selection in one control will be the selection in the other control. If you edit an entry in the combobox, the same entry is the listbox is changed. To avoid this linkage, just have a second dataset object for your second control.

You can use the classes in the System.Data.OleDb namespace to read a MDB file into a datagrid. You instantiate a OleDbConnection object using a connection string to your MDB file. You then instantiate a OleDbDataAdapter that uses this connection object and a SQL query. Next you create a DataSet object, use the OleDbDataAdapter to fill this dataset, and finally attached this dataset to your datagrid. Here is the code that does this.

	private void Form1_Load(object sender, System.EventArgs e)
	{
		// Set the connection and sql strings
		// assumes your mdb file is in your root
		string connString = @'Provider=Microsoft.JET.OLEDB.4.0;data source=C:\northwind.mdb';
		string sqlString = 'SELECT * FROM customers';

		// Connection object
		OleDbConnection connection = new OleDbConnection(connString);

		// Create data adapter object
		OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sqlString, connection);

		// Create a dataset object and fill with data using data adapter’s Fill method
		DataSet dataSet = new DataSet();
		dataAdapter.Fill(dataSet, 'customers');
			
		// Attach dataset’s DefaultView to the datagrid control
		dataGrid1.DataSource = dataSet.Tables['customers'].DefaultView;
    	}