Page 1 of 1

Database To Typed-DataSet, w/o BindingSource IV

#1 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3240
  • View blog
  • Posts: 10,871
  • Joined: 12-December 12

Posted 18 April 2014 - 04:34 AM

Part 1, Part 2, Part 3

This fourth, and final, part is more of a collection of individual topics. In particular, I use a new WinForm Application purely to demonstrate some of these topics, not to build a working application.

  • Include a Related Field in a DataTable
  • Filling Using Criteria
  • Filling with Data From Two Tables
  • Computed Totals
  • Working with a Single DataRow
  • Create a DataReader
  • LINQ to DataSets
  • WPF in World-Record Time




For this part I use a new WinForm application, partly to show how easy it is to re-use our DAL.

I am not using BindingSources this time, but only to demonstrate that they are not essential. I still recommend using a BindingSource whenever appropriate.




Include a Related Field in a DataTable

To add Department to the StaffTable, open the DAL and Configure the StaffTableAdapter - right-click on it in the DataSet Designer. Modifying the SELECT statement will in-turn modify the INSERT, DELETE and UPDATE statements. However, although we can modify the SELECT statement we cannot use joins, because the INSERT, DELETE, UPDATE statements (that are based on the SELECT statement) will no longer work, or be provided. However, we can use subqueries as these won't affect the INSERT, UPDATE and DELETE statements.

Modify the SELECT statement to include the Department from the second table, either directly or by using the Query Builder:
SELECT StaffID, FirstName, LastName, DeptID, Grade, Salary, StartDate, EndDate, Bonus,
  (SELECT Department FROM Department 
  WHERE Department.DeptID = Staff.DeptID) as DeptName 
FROM dbo.Staff

Using a subquery is not very efficient, but at least the INSERT, UPDATE and DELETE statements will work.

This additional field will be displayed in the DataSet Designer as a new field (DataColumn) of the StaffDataTable.

Attached Image

The INSERT, UPDATE and DELETE queries will be rebuilt, but we would need to modify any existing TableAdapter queries (methods) to include this additional field. This is because the StaffTableAdapter works with a StaffTable, and StaffRows, which now have an additional column.

It is useful to have this value available as we might want to display it in a TextBox rather than buiding a ComboBox as we did originally. Our StaffApp1 application will not be affected by this addition because we customized the DataGridView to only display specific columns. (If AutoGenerateColumns were used the Department would just appear as an additional column.)




Including the Department with the StaffDataTable is convenient but it does mean that this field will always be included with the staff-data, and the Department-table will be queried.

I also didn't find a simple way to create a query using this DeptName, rather than DeptID as I do in the following section. (I query the Department-name in the section Filling with Data From Two Tables.)

Filling Using Criteria

We can Filter a BindingSource, create DataViews, even use LINQ (see below), but we still need to fill a DataTable first. I want to use criteria before filling what could be a very large DataTable.



Words on Filtering:

A DataTable doesn't have a Filter() method, a BindingSource does. DataTable's have a Select() method that returns an array of DataRows, or we can create a DataView from a DataTable and use its RowFilter property.

Alternatively, we can just use the DataTable's DefaultView to achieve the equivalent of a BindingSource's Filter:
    _staffDT.DefaultView.RowFilter = "DeptID = 1";


This will filter a DGV that is bound to this DataTable.

These filter-expressions do not offer the full query-syntax of sql WHERE clauses, although LINQ (discussed below) does.




Create a new query on the StaffTableAdapter so that we will be able to fill a StaffDataTable with just those staff having a particular DeptID:

SELECT StaffID, FirstName, LastName, DeptID, Grade, Salary, StartDate, EndDate, Bonus,
  (SELECT Department
    FROM Department
    WHERE (DeptID = Staff.DeptID)) AS DeptName
  FROM Staff
  WHERE (DeptID = @DeptID)

Use the names: FillByDeptID(), GetDataByDeptID()

Importantly, the query has to re-create the original/main SELECT statement, including the subquery that we added in the previous section. We cannot just treat DeptName as though it exists as a field in the staff-table or StaffDataTable.

I thought I read that the name of the parameter @DeptID should be the same as the field-name, because it interprets the type based on this name. You might want to check this detail.

You can test the query as you build it, using the Query Builder. You can also right-click it in the DataSet Designer and choose Preview Data....

Create this wrapper-method in the StaffDataTable partial-class:
        public void FillItByDept(int dept) {
            StaffDAL.StaffDSTableAdapters.StaffTableAdapter adaptStaff = new
                StaffDAL.StaffDSTableAdapters.StaffTableAdapter();
            adaptStaff.FillByDeptID(this, dept);
        }


We will use this method later in the WinForm Application.

Filling with Data From Two Tables

We will create a new StaffDeptTableAdapter that combines the data from these two tables. We will then be able to use LIKE against the Department-name in a query, rather than using StaffID as we did in the previous section.

An alternative is to first create a View (a saved query) in the database itself and drag this onto the design surface. I don't explore this here.

Right-click an empty area of the DataSet Designer to Add a TableAdapter. Gather fields from both the Staff and Department tables, perhaps using the Query Builder:

SELECT Staff.StaffID, Staff.FirstName, Staff.LastName, Staff.DeptID, Staff.Grade, 
    Staff.Salary, Staff.StartDate, Staff.EndDate, Staff.Bonus, Department.Department
  FROM Staff INNER JOIN Department ON Staff.DeptID = Department.DeptID

This uses a JOIN so INSERT, UPDATE and DELETE statements will not be created for us.

Once this has been created you can right-click this table-adapter and its table in the DataSet Designer to rename them.

Add a new query to this new TableAdapter that uses LIKE on the Department-name:

SELECT Staff.StaffID, Staff.FirstName, Staff.LastName, Staff.DeptID, Staff.Grade, 
  Staff.Salary, Staff.StartDate, Staff.EndDate, Staff.Bonus, Department.Department
FROM Staff INNER JOIN
  Department ON Staff.DeptID = Department.DeptID
WHERE (Department.Department LIKE @Department + '%')

Add a wrapper method for this in StaffDeptTableAdapter's partial-class. Recall that you can obtain this class by double-clicking the TableAdapter name.
    partial class StaffDeptDataTable {
        public void FillItByDeptName(string dept) {
            StaffDAL.StaffDSTableAdapters.StaffDeptTableAdapter adaptStaffDept = new
                StaffDAL.StaffDSTableAdapters.StaffDeptTableAdapter();
            adaptStaffDept.FillBy(this, dept);
        }
    }


We've finished with the DAL: build, save and close.

Our New WinForm Application

Create a new application named StaffApp2 (or whatever). Add a Reference to our DAL and rename the form to frmStaff2.

Attached Image

(The full code is shown at the end of the tutorial.)
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;
using StaffDAL;

namespace StaffApp2 {
    public partial class frmStaff2 : Form {
        private StaffDS _staffDS = new StaffDS();
        private StaffDS.StaffDataTable _staffDT;

        public frmStaff2() {
            InitializeComponent();
        }

        private void frmStaff2_Load(object sender, EventArgs e) {
            _staffDT = _staffDS.Staff;
            _staffDT.FillIt();

            dgvStaff.AutoGenerateColumns = true;
            dgvStaff.DataBindingComplete += (s, f) => {
                ((DataGridView)s).AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
            };

            dgvStaff.DataSource = _staffDT;


As mentioned, I am not using any BindingSources for this example, just populating a StaffDataTable and setting this as the DataSource for the main DataGridView.
            StaffDS.DepartmentDataTable _deptDT = _staffDS.Department;
            _deptDT.FillIt();
            lstDepts.ValueMember = _deptDT.DeptIDColumn.ColumnName;
            lstDepts.DisplayMember = _deptDT.DepartmentColumn.ColumnName;
            lstDepts.DataSource = _deptDT;


A little bit of information about a ListBox: the ValueMember is the value (the property) that is set as the value of the ListBox, referenced as SelectedValue. Effectively, this is the field (DataColumn) to which it is bound. DisplayMember is what is displayed in the ListBox, typically referenced as SelectedItem.ToString() (or .Text for a simple object).

You can run the application at this point, although it doesn't do much yet.

ColumnErrors on DGV

If you key a Grade of 20 you'll notice that the ColumnErrors we generated in the DAL appear automatically, even without an ErrorProvider. I would prefer their display to be optional. However, we can, if we want, use the validation features, and events, of the DGV itself - in the presentation-layer.

If you type an incorrect value, then press Escape, the value will revert. However, the ColumnError remains in place. We can probably correct everything using DGV events but this type of behaviour should make us consider the benefits of Exception Handling. If a ColumnError occurs in the DAL it can raise an Exception that the presentation-layer needs to handle. What the presentation-layer does with the Exception is of no interest to the DAL; it has completed its work by notifying the presentation-layer of the error. Because ColumnErrors exist, any attempt to update the data-layer (with the errors in-place) will still fail, because we check HasErrors before using Update().

Computed Totals

It is very common to want to display a total, or other aggregate figure, below a DGV. The quickest/simplest way is to use the Compute Method of the DataTable.
    txtTotalSalary.Text = _staffDT.Compute("SUM(Salary)", "").ToString();


The empty string argument could be a filter-expression.

This figure will not update automatically if a value in the column is changed, but we can update it on an appropriate event. In this case, after a cell of the DGV has been edited:
    private void dgvStaff_CellEndEdit(object sender, DataGridViewCellEventArgs e) {
        if (e.ColumnIndex == ((DataGridView)sender).Columns["Salary"].Index) {
            txtTotalSalary.Text = _staffDT.Compute("SUM(Salary)", "").ToString();
        }
    }


(For a large DataTable it is worth checking if the edit happened in a column of interest.)

Remember that, if necessary, we can add additional calculated columns to the DataTable before computing an aggregate figure.

Other Options

  • It is possible, but unnecessary, to iterate the rows of the DGV to obtain a total.
  • You could even iterate the rows of the DataTable.. but why would you?
  • We can use LINQ against the DataTable (see below).

Some people want a totals-row at the bottom, in the grid itself. It is possible to do this by creating a fake row, but it is a bit of work, and messy. It won't remain fixed at the bottom either, although I suppose it is possible to achieve this (or something similar) with a custom control. I just use Compute in a Label or TextBox and position it neatly underneath the DGV.

Of course, if you need complicated figures, you could create a view (a saved query) or stored procedure in the underlying database, or from the DataSet Designer.

Working with a Single DataRow

We have been working with DataTables and BindingSources, but it is just as easy to work with single DataRows (StaffRow, etc.). The DataSet Designer already provides a number of important methods we can use to work with these.

To demonstrate, when you double-click a staff-row in the grid it will display a very simple form displaying this person's details.
    private void dgvStaff_CellDoubleClick(object sender, DataGridViewCellEventArgs e) {
        DataRowView rowv = (DataRowView)dgvStaff.CurrentRow.DataBoundItem;
        if (rowv == null) return;

        StaffDAL.StaffDS.StaffRow srow = (StaffDAL.StaffDS.StaffRow)rowv.Row;
        //MessageBox.Show(String.Format("You clicked on {0}",srow.StaffID.ToString()));
        frmSingleRow frmSingle = new frmSingleRow(srow);
        frmSingle.ShowDialog(this);
    }


A DataTable contains DataRows and DataColumns. However, a DataTable uses a DefaultView (we can create other DataViews on the DataTable) so what we really have are DataRowViews, not DataRows. Anyway, this just means that we have to jump through a couple of hoops before we can obtain the StaffRow that we want. (I have included a null-check in case we are on the new row.)

I haven't shown a screenshot of this new form, as it just contains a few textboxes and labels:
using System;
using System.Data;
using System.Windows.Forms;
using StaffDAL;

namespace StaffApp2 {
    public partial class frmSingleRow : Form {
        private StaffDAL.StaffDS.StaffRow _srow;

        public frmSingleRow() {
            InitializeComponent();
        }
        public frmSingleRow(StaffDAL.StaffDS.StaffRow srow) {
            InitializeComponent();
            _srow = srow;
        }

        private void frmSingleRow_Load(object sender, EventArgs e) {
            txtFirstName.Text = _srow.FirstName;
            txtLastName.Text = _srow.LastName;
            //txtDepartment.Text = _srow.DepartmentRow.Department;
            txtDepartment.Text = _srow.DeptName;
        }
    }
}


We pass the current row-instance to the form. I am not binding here, just displaying some data from the row. Notice that we can use our recently added field/DataColumn _srow.DeptName or use _srow.DepartmentRow.Department. Either is a (strongly-typed) string so we don't need ToString().

If we wanted to bind to the StaffRow we could just use:
        txtFirstName.DataBindings.Add("Text", _srow, "FirstName");


If you test this you will see that changing the textbox-text will update the firstname in the main form.




Returning to the main form, I've added a couple of buttons and a textbox to demonstrate the two methods that we added to our StaffDataTable and StaffDeptDataTable, using the helper-methods that we created in their partial-classes:
    private void btnUseQuery_Click(object sender, EventArgs e) {
        int deptid;
        if (lstDepts.SelectedItem == null) {
            MessageBox.Show("Please select a department above.");
            return;
        }
        deptid = (int)lstDepts.SelectedValue;
        _staffDT.FillItByDept(deptid);
        dgvStaff.DataSource = _staffDT;
    }

    private void btnUseLike_Click(object sender, EventArgs e) {
        string deptBegin = txtDeptBegin.Text;
        StaffDS.StaffDeptDataTable staffDept = _staffDS.StaffDept;
        staffDept.FillItByDeptName(deptBegin);
        MessageBox.Show(String.Format("{0}* has {1} staff.", 
            deptBegin, staffDept.Compute("Count(DeptID)", "")));
        dgvStaff2.DataSource = staffDept;
    }





Create a DataReader

We usually choose between either a DataReader or TableAdapter (DataAdapter).

MSDN said:

You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader. Using the DataReader can increase application performance both by retrieving data as soon as it is available, and (by default) storing only one row at a time in memory, reducing system overhead.

DataAdapters and DataReaders :MSDN

A DataReader is representative of the ADO.NET connected-layer and provides a fast, forward-only, set of data. DataAdapters represent the ADO.NET disconnected-layer. Using these we don't use commands to open and close database-connections - the DataAdapters do this for us, but only when necessary, allowing us to work with (disconnected) DataSets and DataTables, and then use adapters to update the underlying data-source.

Even though these two modes appear mutually exclusive, we can can use the CreateDataReader() method of a DataTable to obtain a DataReader.

A simple button on the main form can demonstrate the use of this DataReader using a MessageBox:
        private void btnUseReader_Click(object sender, EventArgs e) {
            DataTableReader reader = _staffDT.CreateDataReader();
            string message = String.Empty;
            while (reader.Read()) {
                message += reader["FirstName"] + " " + reader["LastName"] + "\n";
            }

            MessageBox.Show(message);
        }


It is interesting that we can do this, and a DataReader is very efficient. However, it is not immediately obvious under what circumstances this would be useful. We have already filled a DataTable into memory, and we can Filter a BindingSource (if we have one), create DataViews, use LINQ, etc., on this DataTable. It might be useful if we have a large DataTable and want to stream its output elsewhere.

LINQ to DataSets

This is only a very brief introduction to this subject - it doesn't teach LINQ.

LINQ to DataSet :MSDN
LINQ Query Expressions :MSDN

A DataTable's DataRowCollection does not implement IEnumerable<T>. In order to use LINQ to DataSet we need to use the AsEnumerable() extension method on the DataTable, which returns IEnumerable<DataRow> that we can query using LINQ. The CopyToDataTable() extension method will then copy the results of the LINQ expression to a new DataTable (if required). We can also obtain a DataView with AsDataView<T>.

Double-clicking a department in the ListBox can demonstrate the use of LINQ, populating the second DGV with staff from the selected department:
        private void lstDepts_DoubleClick(object sender, EventArgs e) {
            dgvStaff.EndEdit();
            
            EnumerableRowCollection<StaffDS.StaffRow> staffc = _staffDT.AsEnumerable();

            // only if there are staff in this dept..
            int count = staffc.Count(x => x.Field<int>("DeptID") == (int)lstDepts.SelectedValue);
            if (count < 1) {
                MessageBox.Show(String.Format("There are no staff in {0}", lstDepts.Text));
                return;
            }

            //dgvStaff2.DataSource = (
            //    from staff in _staffDT.AsEnumerable() 
            //    where staff.Field<int>("DeptID") == (int)lstDepts.SelectedValue 
            //    select staff).CopyToDataTable();

            dgvStaff2.DataSource = (
                from staff in staffc
                where staff.DeptID == (int)lstDepts.SelectedValue
                select staff).CopyToDataTable();
        }


Within the LINQ expression we can use either x.Field<int>("DeptID") or staff.DeptID. We have a strongly-typed DataSet but, within the LINQ expression, Field<T>() provides the strong-typing. Field<T> is less convenient but reduces compile-time checking.

Generic Field and SetField Methods (LINQ to DataSet) :MSDN

WPF in World-Record Time

My objective with WPF (Windows Presentation Foundation) is simply to re-use our DAL and get our Staff-data to show in a Window, WPF's version of a (Win)Form. I do not attempt to do anything with WPF in a proper fashion, I simply want to demonstrate that it would work.

There are a number of WPF tutorials here @dreamincode:

WPF & Silverlight Tutorials

WPF for the WinForms coder


Create a new WPF Application and add a reference to our DAL.

The equivalent to a WinForm's DataGridView is a DataGrid. Drag this control and drop it between the Grid-tags in the XAML to obtain this:
    <Grid>
        <DataGrid />
    </Grid>


Modify to this:
    <Grid>
        <DataGrid HorizontalAlignment="Left" VerticalAlignment="Top" ItemsSource="{Binding}" />
    </Grid>


Click into the Mainwindow.xaml.cs tab and add this:
using StaffDAL;


then modify to this:
    public partial class MainWindow : Window {
        public MainWindow() {
            InitializeComponent();
            StaffDS staffDS = new StaffDS();
            staffDS.Staff.FillIt();

            this.DataContext = staffDS.Staff.DefaultView;
        }
    }


Run the application and it should show the staff-data.

This tutorial looks reasonable: WPF DataGrid Practical Examples (codeproject).

Andy.

Full code for frmStaff2:

Spoiler

This post has been edited by andrewsw: 18 April 2014 - 04:45 AM


Is This A Good Question/Topic? 1
  • +

Page 1 of 1