How do I use the DataColumn.Expression property to add a computed/combined column to my datagrid
The idea is to load your datatable in a normal fashion. Once the datatable is loaded, you can add an additional column that is computed from the other columns in your datatable. In the sample(CS, VB), we load the CustomerID, CompanyName, ContactName and ContactTitle from the Customers table in the NorthWind database. We then add an additional column that concatenates the ContactName and ContactTitle into one column. To add the additional column, we create a DataColumn, set a mapping name, and then use the Expression property to define how the column is to be computed. [C#] DataColumn dc = new DataColumn(”Contact”, typeof(string)); dc.Expression = ”ContactName + ’:’ +ContactTitle”; _dataSet.Tables[”customers”].Columns.Add(dc); [VB.NET] Dim dc As DataColumn dc = New DataColumn(”Contact”, GetType(System.String)) dc.Expression = ”ContactName + ’:’ +ContactTitle” _dataSet.Tables(”customers”).Columns.Add(dc) The sample actually shows two datagrids. The first one uses the default binding to display the entire table including our added column. In the second datagrid, we add a custom DataGridTableStyle to only display the CustomerID, CompanyName and our added column.
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.
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”);
How can I bind two datagrids in a Master-Detail relationship?
Please download this sample before reading the rest of this FAQ. Looking through the sample will help follow the description. simpledata2.zip What this boils down to is this: 1) Load both Master and Details queries in a dataset. // I am using the SQL server NorthWind database this.dataAdapterMaster.Fill(this.dataSet, ”Customers”); this.dataAdapterDetails.Fill(this.dataSet, ”Orders”); 2) Bind the master data grid to the Master dataset table. // The master view grid.DataSource = this.dataSet; grid.DataMember = ”Customers”; 3) Create a relationship that describes how the two tables relate to each other. A primary key foreign key relationship is defined by two attributes. The primary key column in the master table The foreign key column in the details table The created relationship is added to the dataset. this.dataSet.Relations.Add(”CustomersToOrders”, dataSet.Tables[”Customers”].Columns[”CustomerID”], dataSet.Tables[”Orders”].Columns[”CustomerID”]); 4) Set the data member for the details table to be the name of relationship that was added to the dataset. // The name of the relation is to be used as the DataMember for the // details view details.DataSource = this.dataSet; // use the relationship called ”CustomersToOrders in the Customers table. // Remember that we called the relationship ”CustomersToOrders”. details.DataMember = ”Customers.CustomersToOrders”;
How can I bind the datagrid to a datasource without using any wizards?
Here is a really simple data binding sample. Just drag and drop a datagrid onto a default Windows Forms application. Follow the steps below to bind this grid to the NorthWind db in SQL server. Complete Sample: simpledata.zip // Create a connection SqlConnection connection = new SqlConnection(this.GetConnectionString()); // Create a data adapter. Think of the data adapter as an object that knows how to get the data from the // data source into a dataset SqlDataAdapter dataAdapter = new SqlDataAdapter(this.GetCommandText(), connection); // fill the dataset using the data adapter DataSet dataSet = new DataSet(”Customers”); dataAdapter.Fill(this.dataSet, ”Customers”); // bind to the grid grid.DataSource = this.dataSet; // the big picture grid.DataMember = ”Customers”; // the specific table that we want to bind to // The connection text looks like this // If your SQL server is running on the default port, you can remove the port attribute. private string GetConnectionString() { string server = ”your_server_name”; string serverPort = ”port_address”; string catalog = ”NorthWind”; string password = ”user_pass”; string userId = ”user_name”; string connectionString = ”data source={0},{1};initial catalog={2};” + ”password={3}; user id={4}; packet size=4096”; return string.Format(connectionString, server, serverPort, catalog, password, userId); } // The command text looks like this private string GetCommandText() { string commandText = ”Select * from customers”; return commandText; }