Windows Forms Data Binding

4.1 Does MySql work with ADO.NET?

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

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

4.2 Where can I find a discussion of databinding and windows forms?

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.

4.3 How do I test for a null value in DataView.RowFilter?

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"

4.4 I don't have SQL Server. Is there any way to run the samples that need SQL server?

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.

4.5 How can I programmatically move through a dataset that has bound controls?

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.

4.6 When I try to update a dataset I get an error that the system is unable to find "Table"?

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

4.7 How can I bind an ArrayList to a DataGrid?

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 <>

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);}

}

}

4.8 When I try to bind two comboboxes to the same datatable, both boxes show the same values, changing one changes the other. How do I make them have distinct values?

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";

4.9 How do I bind a mdb file to a datagrid?

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;

}

4.10 How do I bind a listbox or a combobox to a MDB file?

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.

4.11 In my databound ComboBox, how do I set the SelectedItem property?

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.

4.12 Are server side cursors supported in .NET?

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.

4.13 I am populating a ListBox by binding it to an ArrayList. The initial display is OK, but later changes to my ArrayList are not shown in my ListBox. How can I get the changes to show properly?

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;

}

4.14 My SQL server runs on a non-default port. How can I specify this port in my connection string?

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/

4.15 What is a DataSet?

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.

4.16 How do I add updating support to a dataset?

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");

4.17 How can I import a CSV file into a DataTable?

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

4.18 How can I prevent a ComboBox and DataGrid bound to the same DataTable from sharing the same current position?

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

4.19 I get a 'This would cause two bindings in the collection to bind to the same property' error message. What might cause this?

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")

4.20 How do I bind a TextBox to a nested relation?

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")

4.21 How do I call a SQL stored procedure?

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();

4.22 I programatically change a bound TextBox value, but the value does not get pushed back into the bound datasource. How can I make sure the DataSource is updated?

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"

4.23 Why does the CheckedListBox lose checked state when placed in a tab page that gets hidded and shown?

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 <>

{

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 <>

{

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

No comments: