Solved

BindingSource?

Posted on 2006-11-09
11
1,998 Views
Last Modified: 2008-08-29
I have a datagridview which is bound to a datasource like this:

    Public Sub RefreshForm()
        Dim i As Byte
        'For i = 1 To dgvTeachers.Rows.Count
        '    dgvTeachers.Rows.RemoveAt(0)
        'Next
        frmMain.connWorkshop.Open()
        Dim strSQL As String = "select ID, FName ,LName from tblTeachers"
        Dim daTeachers As New SqlDataAdapter(strSQL, frmMain.connWorkshop)
        daTeachers.TableMappings.Add("Table", "tblTeachers")
        dsTeachers.Reset()
        daTeachers.Fill(dsTeachers)
        With dgvTeachers
            .DataSource = dsTeachers.Tables("tblTeachers")
            .Columns(0).ReadOnly = True
            .Columns(0).HeaderText = ""
            .Columns(0).Width = 30
            .Columns(1).HeaderText = "Name"
            .Columns(2).HeaderText = "Surname"
            .Refresh()
        End With
        frmMain.connWorkshop.Close()
    End Sub

    Private Sub dgvTeachers_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvTeachers.CellEndEdit
        BindingContext(dsTeachers.Tables("tblTeachers")).EndCurrentEdit()
        frmMain.connWorkshop.Open()
        Dim strSQL As String = "select ID, FName ,LName from tblTeachers"
        Dim daTeachers As New SqlDataAdapter(strSQL, frmMain.connWorkshop)
        daTeachers.TableMappings.Add("Table", "tblTeachers")
        Dim command_builder As New SqlCommandBuilder(daTeachers)
        daTeachers.ContinueUpdateOnError = False
        daTeachers.Update(dsTeachers)
        frmMain.connWorkshop.Close()
        Me.RefreshForm()
    End Sub

When I compare this code to how Visual Studio itself creates a databound DGV by using its wizards, I see that I'm not using a BindingSource, nor a BindingNavigator.

Regarding the fact that my code works just as I like, is there any benefit in using a BindingSource? If yes, please describe how to implement it in my code.

Please notice that the connection string to the Database is created in Run-Time (and is not stored in the code.)
0
Comment
Question by:huji
  • 6
  • 5
11 Comments
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
Although your current code is working OK it is far more complex and will be far less efficient in operation than it needs to be.  You keep

1) re-making a similar dataadapter
2) re-creating and filling a similar datatable
3) re-binding and re-formatting your datagridview
4) updating your database (and doing all of the above) every time a change is made even to one cell/field in a record
5) expressly opening and closing your connection when it is not necessary: a dataadapter handles that automatically.

Provided you declare your dataadapter with scope for all sub-routines it can be re-used by any one of those routines.  Once you have filled a datatable you can "refresh" it just by calling the dataadapter's .Fill method or, if you want to make sure that everything is totally in synch as between the database and the datatable, by calling the datatable's .Clear method followed by the dataadapter's .Fill method.  Once you have set up a datagridview and bound it to the datatable once any changes to the datatable are automatically reflected in the datagridview.

With the above points in mind, I would replace your code with this

At the start of the form

        Private daTeachers As SqlDataAdapter

In the form load sub (or in some other sub that is called shortly after your app starts up)

        Dim strSQL As String = "select ID, FName ,LName from tblTeachers"
        daTeachers As New SqlDataAdapter(strSQL, frmMain.connWorkshop)
        daTeachers.TableMappings.Add("Table", "tblTeachers")
        dsTeachers.Reset()
        daTeachers.Fill(dsTeachers)
        Dim command_builder As New SqlCommandBuilder(daTeachers)
        With dgvTeachers
            .DataSource = dsTeachers.Tables("tblTeachers")
            .Columns(0).ReadOnly = True
            .Columns(0).HeaderText = ""
            .Columns(0).Width = 30
            .Columns(1).HeaderText = "Name"
            .Columns(2).HeaderText = "Surname"
            .Refresh()
        End With

That would set everything up once.  None of it would need to be repeated.  Then, in a sub called when I wanted to update the database

        BindingContext(dsTeachers.Tables("tblTeachers")).EndCurrentEdit()
        daTeachers.Update(dsTeachers)

There is no "rule" against placing that in a CellEndEdit sub, although I would normally regard it as overkill.  Your datatable will hold all changes until the dataadapter's update is called.  If the database itself is multi-user, so that you need to make sure that all changes - no matter how small - are reflected in it immediately, then such an approach might be desirable.  Even then, however, I would expect an .Update call to be delayed until a whole row's editing was completed.  But in more normal circumstances I would expect .Updates to happen at less frequent intervals.

I might even go further.  There can be all sorts of reasons for using datasets rather than free standing datatables, and some of those may apply in this case.  But if there is no particular reason for using a dataset here I would use this code

        Private daTeachers As SqlDataAdapter
        Private tblTeachers As New DataTable("tblTeachers")

In the loading sub

        Dim strSQL As String = "select ID, FName ,LName from tblTeachers"
        daTeachers As New SqlDataAdapter(strSQL, frmMain.connWorkshop)
        daTeachers.Fill(tblTeachers)
        Dim command_builder As New SqlCommandBuilder(daTeachers)
        With dgvTeachers
            .DataSource = tblTeachers
            .Columns(0).ReadOnly = True
            .Columns(0).HeaderText = ""
            .Columns(0).Width = 30
            .Columns(1).HeaderText = "Name"
            .Columns(2).HeaderText = "Surname"
            .Refresh()
        End With

In the updating sub

        BindingContext(tblTeachers).EndCurrentEdit()
        daTeachers.Update(tblTeachers)

I appreciate that none of the above actually answers your question.  But, in my view, making alterations on the above lines would bring you much more benefit - for the purposes of this particular part of you code, anyway - than introducing a BindingSource.

Roger
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
It occurred to me that perhaps I ought to offer you a bit more of an explanation as to why, in this scenario anyway, I don't think a Binding Source is worth bothering with.  In relatively simple set ups - which that in your example is - the more sophisticated facilities of the BindingSource are unlikely to be used.  The one that can be valuable at even the simplest level is that it allows a BindingNavigator to be used.  But, when your data is displayed in a DataGridView - rather than each field being displayed in separate controls such as listboxes - that gives little advantage.  This is because the DataGridView itself provides the sort of facilities that the BindingNavigator offers.  You can move between records just by clicking on them.  New records can be added to the end of the grid.  Selecting a record and pressing delete is all that is needed to delete a record.  So, in this sort of scenario anyway, the BindingNavigtor is not really needed, which in turn means that a BindingSource is not really needed.  Adding them in produces little advantage, but (a) requires more coding to be done and (b) provides more to go wrong and to have to be searched through for the cause when something does go wrong.  The balance may tilt the other way when everything is automatically coded by the wizards: although I am not sure that that is always true.  Otherwise, my recommendation is to keep things as simple as you can.

Roger
0
 
LVL 14

Author Comment

by:huji
Comment Utility
Roger,

Thanks for your complete answer. It has really helped me understand the different aspects better. A few notes:

1) I didn't really need that overkill, but I did it as a practice to myself. Thanks for your comments about it.
2) I'm not allowing the user to deleted a row in DGV and adding new records is not allowed as well. Deletion is handled through selecting a row and pressing a Deleted button, which checks some things before deleting the row. Addition is going ot be allowed through a differnt form, with its own textboxes, etc.
3) About the other form which is going to give the user a way to add a record to the table, through filling textboxes and pressing a button, do I benefit using a datanavigator there? (That form is only used for additions, not modifications of records.) And how would you code it, for the above example? (I mean how would you code a form with two textboxes for name and surname, and an add button, to add a row to the tblTeachers?)
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
No, I can see no reason whatsoever for a binding source in your "new record" form.  I cannot really see why, if you are to use a separate form for this purpose, you would want to "bind" it to your datatable in any way.

I envisage two textboxes - txtName and txtSurname - and one button - btnSave.  That's the operational minimum: you would probably also need labels to indicate what each textbox was for, and perhaps a cancel button so the user could quit the form without actually making a new record.

The only code (for the minimum version) would need to go in the btnSave_Click event.  You would probably need some validation code to check that both textboxes had something in them.  Then

      Dim dr As DataRow = datatable.NewRow
      dr("Name") = txtName.Text
      dr("Surname") = txtSurname.Text
      datatable.Rows.Add(dr)

The only real issues that this raises are (a) how do you make the datatable (which is in the form with the datagridview on it) visible to this form and (b) how do you then update the database because, again, the dataadapter is on the other form.  One way would be to move the declarations of the dataadapter and datatable that I suggested before

        Private daTeachers As SqlDataAdapter
        Private tblTeachers As New DataTable("tblTeachers")

from the other form to a Module and make them Public, rather than Private: then they would be available to both this form and the other one.  Or to leave them where they are but change the declaration from Private to Public or Friend and reference them via the other form.  Another would be to add Properties to this form and pass the datatable (and maybe the dataadapter) to this form from the other form when it calls it.  A third would be just to do that with the datatable then call this form with ShowDialog and, on the other form, use its dataadapter to .Update the database in the normal way.

But, I have to say, I don't think I would use a separate form at all.  Where a new record is complicated then there might be merit in it.  Here, however, with just a couple of fields to enter, I cannot see what problem there is with allowing new records to be entered directly into the datagridview.  There's plenty of opportunities to validate any record that it attempted to be entered in that way before it is committed.

Still, it's your call.

Roger
0
 
LVL 14

Author Comment

by:huji
Comment Utility
OK Roger, I have updated the code to such. I would be glad if you review this and see if I've made the changes correctly.
Also please answer three questions:

1) What is the benefit of using a datatable instead of a dataset.
2) I'm going to continue changing it to a datatable, and follow your suggestions on how to make the second form add a row to the table in the database by running   datatable.Rows.Add(dr)   . Does running that command automatically refresh the DGV of the first form too?
3) First of all, I can't understand why simply creating the command_builder is enough for the system to work correctly about updating and deleting rows. We are creating command_builder but not using anywhere in the code! Please help me understand it. Secondly, does command_builder need to be Friend, for the second form to work? Or is making the sqlDataAdapter Friend is enough for this task?

Here is the current version of the code:

Imports System.Data.SqlClient

Public Class frmTeachersList
    Const strSQL As String = "select ID, FName ,LName from tblTeachers"
    Friend daTeachers As New SqlDataAdapter
    Private dsTeachers As New DataSet
    Friend command_builder As New SqlCommandBuilder

    Public Sub New()
        Me.MdiParent = frmMain
        InitializeComponent()
    End Sub

    Private Sub frmTeachersList_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.WindowState = FormWindowState.Maximized
        daTeachers = New SqlDataAdapter(strSQL, frmMain.connWorkshop)
        daTeachers.TableMappings.Add("Table", "tblTeachers")
        command_builder = New SqlCommandBuilder(daTeachers)
        dsTeachers.Reset()
        daTeachers.Fill(dsTeachers)
        With dgvTeachers
            .DataSource = dsTeachers.Tables("tblTeachers")
            .Columns(0).ReadOnly = True
            .Columns(0).HeaderText = ""
            .Columns(0).Width = 30
            .Columns(1).HeaderText = "Name"
            .Columns(2).HeaderText = "Surname"
            .Refresh()
        End With
    End Sub

    Private Sub dgvTeachers_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvTeachers.CellEndEdit
        BindingContext(dsTeachers.Tables("tblTeachers")).EndCurrentEdit()
        daTeachers.ContinueUpdateOnError = False
        daTeachers.Update(dsTeachers)
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Hide()
    End Sub

    Private Sub frmTeachersList_Resize(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Resize
        Me.WindowState = FormWindowState.Maximized
    End Sub

    Private Sub btnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDel.Click
        Select Case dgvTeachers.SelectedRows.Count
            Case 0
                MsgBox("Choose a row to delete.")
            Case 1
                dgvTeachers.Rows.Remove(dgvTeachers.Rows(dgvTeachers.CurrentCell.RowIndex))
                BindingContext(dsTeachers.Tables("tblTeachers")).EndCurrentEdit()
                daTeachers.Update(dsTeachers)
            Case Else
                MsgBox("Chooe only one row to delete.")
        End Select
    End Sub
End Class
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 14

Author Comment

by:huji
Comment Utility
I'm also increasing the points to 500 as you deserve it.
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
1) In broad terms a dataset equates to a database.  Just as a database consists of a number of tables a dataset can consist of a number (or collection) of datatables.  Sometimes, in an application, you want to reproduce the relationships which can exist between tables in a database.  Then a dataset is necessary.  Sometimes it is worth using a dataset even when you don't want to do that.  For instance, it is a tidy way of organising a number of tables; it allows you to check a number of datatables' status with one line of code - If myDataSet.HasChanges.

But using a dataset (a) introduces an overhead and (b) makes references to particular objects more long winded.  If there is only one datatable being used then, on the one hand, using a dataset just to contain it gives no benefit and, on the other hand, not using a dataset avoids the overhead and those reference complications.

2) Yes.  The object that you are updating and the object that the DGV is bound to are one and the same object.

3) For many purposes it is useful to think of a commandbuilder as giving commands to the dataadapter which is referenced in its creation.  That is not (as I understand it) how it actually works.  The commandbuilder makes the update commands with reference to that dataadapter's select command but it does not pass them over to the dataadapter.  What is does, instead, is (in effect) put a hook from itself into the dataadapter's message stream.  So, when an instruction is issued to the dataadapter that the dataadapter cannot itself deal with because it has not got the appropriate command, the commandbuilder, which is listening in, picks up that instruction and executes it on the dataadapter's behalf.

No, the commandbuilder does not need to be Friend.  It is sufficient in my experience just to put

    Dim command_builder As New SqlCommandBuilder(daTeachers)

in the procedure in which you set up your dataadapter.  It is here that thinking of the commandbuilder as having given commands to the dataadapter is quite useful.  The listening hook that I have described above becomes, in effect, part of the dataadapter and, as long as that remains live (ie in scope) that hook, and consequently the commandbuilder, also remains live and in scope for the purposes described.  The only case in which you might need expressly to declare the command builder with wider scope is if you wished, in some other part of your code, expressly to reference it - e.g. with

   Dim thisCommand As SqlCommand = command_builder.GetUpdateCommand

But such direct reference is seldom necessary in the scenario as described above, so such a wider declaration usually serves no useful purpose.

As to your code, it looks OK to me.  But that's just reading it on screen.  Your best bet is to test it and see if it works.

Roger
0
 
LVL 14

Author Comment

by:huji
Comment Utility
All right, I've changed the code to what you see below. It seems to work correctly. I've also implemented the second form, which adds a row to the datatable. It works great but there is only one bug: When I add a new row to the database, it appears in the DGV too, but the first column of the DGV which is the ID field (bound to an autoincrement field in the database table) shows as empty. However, if I close this child window that contains the DGV and open it again, the field shows correctly. How can I fix this?

The other question is, if the remote database goes down (for example I stop it manually) I want the program to show an error message and close. What it currently does is to hang! How can I achieve that? With Try? Or do you know a better way?

=======
  Codes
=======

Imports System.Data.SqlClient

Public Class frmTeachersList
    Const strSQL As String = "select ID, FName ,LName from tblTeachers"
    Friend daTeachers As New SqlDataAdapter
    Private dtTeachers As New DataTable("tblTeachers")
    Friend command_builder As New SqlCommandBuilder

    Public Sub New()
        Me.MdiParent = frmMain
        InitializeComponent()
    End Sub

    Private Sub frmTeachersList_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.WindowState = FormWindowState.Maximized
        daTeachers = New SqlDataAdapter(strSQL, frmMain.connWorkshop)
        daTeachers.TableMappings.Add("Table", "tblTeachers")
        command_builder = New SqlCommandBuilder(daTeachers)
        dtTeachers.Reset()
        daTeachers.Fill(dtTeachers)
        With dgvTeachers
            .DataSource = dtTeachers
            .Columns(0).ReadOnly = True
            .Columns(0).HeaderText = ""
            .Columns(0).Width = 30
            .Columns(1).HeaderText = "Name"
            .Columns(2).HeaderText = "Surname"
            .Refresh()
        End With
    End Sub

    Private Sub dgvTeachers_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvTeachers.CellEndEdit
        BindingContext(dtTeachers).EndCurrentEdit()
        daTeachers.ContinueUpdateOnError = False
        daTeachers.Update(dtTeachers)
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Hide()
    End Sub

    Private Sub frmTeachersList_Resize(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Resize
        Me.WindowState = FormWindowState.Maximized
    End Sub

    Private Sub btnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDel.Click
        Select Case dgvTeachers.SelectedRows.Count
            Case 0
                MsgBox("Choose a row to delete.")
            Case 1
                dgvTeachers.Rows.Remove(dgvTeachers.Rows(dgvTeachers.CurrentCell.RowIndex))
                BindingContext(dtTeachers).EndCurrentEdit()
                daTeachers.Update(dtTeachers)
            Case Else
                MsgBox("Choose only one row to delete.")
        End Select
    End Sub
End Class

----------------------------------

Imports System.Data.SqlClient


Public Class frmTeachersAdd

    Public Sub New()
        Me.MdiParent = frmMain
        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Hide()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        'IF VALIDATED
        Dim dr As DataRow
        dr = frmTeachersList.dtTeachers.NewRow
        dr.Item("FName") = tbFName.Text
        dr.Item("LName") = tbLName.Text
        frmTeachersList.dtTeachers.Rows.Add(dr)
        BindingContext(frmTeachersList.dtTeachers).EndCurrentEdit()
        frmTeachersList.daTeachers.Update(frmTeachersList.dtTeachers)
        Me.Hide()
        frmTeachersList.dgvTeachers.Refresh()
    End Sub
End Class
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
Comment Utility
Question 1.  The simplest (although not the only) way is to use the dataadapter's .Fill method again immediately after you have done an .Update.  On these lines

        frmTeachersList.daTeachers.Update(frmTeachersList.dtTeachers)
        frmTeachersList.daTeachers.Fill(frmTeachersList.dtTeachers) '<<< NEW LINE
        Me.Hide()

The .Fill method in fact refreshes the datatable, so it should just bring over (with its new autogenerated ID) the new row as it stands in the Database and replace the same row (that doesn't have the ID) in the datatable.  If that gives any problems then you could clear the datatable first

        frmTeachersList.daTeachers.Update(frmTeachersList.dtTeachers)
        frmTeachersList.dtTeachers.Clear() '<<< ADDITIONAL NEW LINE
        frmTeachersList.daTeachers.Fill(frmTeachersList.dtTeachers) '<<< NEW LINE
        Me.Hide()

Question 2.  The ADO.NET model is disconnected.  That is, it only knows about the database when, for a short time on each occasion, it makes connections to exercise the dataadapter's methods.  So, to avoid hanging when the database is down you really need Try Catch blocks just round those.

Roger
0
 
LVL 14

Author Comment

by:huji
Comment Utility
Well, with some changes to the code (moving some parts of the code from Load sub to the New sub in the first form), and applying what you said (to Clear() and Fill() agin) it is working as I want. I've also implemented the Try..Catch statement and it is handling a lost connection successfully.

Thanks a lot for your help
Huji
0
 
LVL 14

Author Comment

by:huji
Comment Utility
I also need advice here: http:Q_22070696.html
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now