Best methods for accessing and manipulating database from VB.NET


I'm new to programming in VB.NET and SQL. i'd like to ask a general question regarding accessing a database from a VB.NET application.

What would be the most efficient method for manipulating the database from the application? I've read that using stored procedures are the best way to do this. Do i use stored procedures for every and all ways to manipulate the db - adding, delting, modifying, querying etc.?
Is using datasets also the best way of connecting your application to the database?

Appreciate any advice you could give me on this. Thanks.

Who is Participating?
SanclerConnect With a Mentor Commented:
I don't think there is a general answer to your question.  "Manipulating the database from the application" can cover such a range of quite different detailed operations, and efficiency can be affected by so many considerations, that the judgment as to what the best approach is really has to be made in the context of each specific app.  

Having said that, a general principle is that the more that (consistently with what the app itself is trying to achieve) data manipulation can be done WITHIN the database itself, the more efficient things will be.  That, after all, is what the database is optimised for.  So it will very often be the case that getting your app to tell the database to execute a stored procedure will be better than your app putting together a series of instructions for the database to perform.  I would, though, personally advise against regarding that as an immutable "rule".  As I say, circumstances alter cases so much that I would always try to ask myself "Is a stored procedure the best approach for THIS particular operation/objective?" before going down that route.  It doesn't have to be all or nothing.

As to "Is using datasets also the best way of connecting your application to the database?", it all depends what you mean.  You will almost always, with VB.NET be using some app-based datastore: that is, bringing over data from the database, doing something with it in the app, and sending it back.  Whenever you do that the app-based datastore will normally be at least a datatable.  In your database, you will probably have a number of tables; possibly with relationships between them.  If you want to replicate that multi-table structure (or part of it) in your app, then you will need more than one datatable and the most convenient way of organising them is into a dataset: it is essential to do that if you want to replicate relations between datatables.  So, if by "using datasets" you simply mean grouping datatables together into datasets, the answer is: it depends on what you want to do with them in your app.  But, in that sense, "using datasets" is not [EMPHASIS added] a "way of CONNECTING your application to the database".

But if, in that question, you have in mind the facility in VB.NET 2005 for using wizards to configure datasources for your app - which create not only a strongly-typed dataset but also TableAdapters to fill and update the datatables in the app - there is a _connection_ element to those.  And my answer on that is that they are very convenient for creating apps which handle bog-standard operations.  But, if you want to do anything non-standard, the difficulties that you sometimes face in tapping into the dataobjects that the wizards create, or having to code round them, makes it questionable whether it would have been easier just to do the whole thing yourself to start with.


Well that really depends on one's temper and approach to things.
If it is a company solution that requere's you use SP or you are workinking with heavy loads of data then
SP would be the way to go, due to performance that you'll gain that the server provide.

Another approach is to control things you self by writing the specific requerements your self.

Imports System.Data

Public Class Form1

    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim daParent As OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Test.mdb"
        sql = "SELECT * FROM Parent"

        daParent = New OleDb.OleDbDataAdapter(sql, con)
        daParent.Fill(ds, "Parent")


        Parentgrd.DataSource = ds.Tables("Parent")

    End Sub

   'update all change's made in the datagridview
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cb As New OleDb.OleDbCommandBuilder(daParent)
        daParent.Update(ds, "Parent")

        MsgBox("Data updated")
    End Sub

End Class

>>Parentgrd.DataSource = ds.Tables("Parent")<<
MS insists on using BindingSource under VS 2005 to synchronize cursor
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).


MS insists on using BindingSource under VS 2005 to synchronize cursor

Just where, and how, does MS INSIST on that?  VS 2005 offers, for the first time, a BindingSource.  But that MS INSISTS on its use is news to me.

Hi Roger
<QUOTE>The DataGridView control supports the standard Windows Forms data binding model, so it will bind to a variety of data sources. In most circumstances, however, you will bind to a BindingSource component which will manage the details of interacting with the data source. The BindingSource component can represent any Windows Forms data source and gives you great flexibility when choosing or modifying the location of your data.</QUOTE>
<QUOTE>The BindingSource component is the preferred data source because it can bind to a wide variety of data sources and can resolve many data binding issues automatically.</QUOTE>
Also you can check MS samples on databindings - most of them use BindingSource. MS doesn't say "you should use BindingSources in all cases" but it says it's <<the preferred data source>>


I know the docs say that.  And they also say  

The DataGridView class supports the standard Windows Forms data-binding model. This means the data source can be of any type that implements one of the following interfaces:
The IList interface, including one-dimensional arrays.
The IListSource interface, such as the DataTable and DataSet classes.
The IBindingList interface, such as the BindingList class.
The IBindingListView interface, such as the BindingSource class.

To me, that doesn't look quite the same as "insists" ;-)

I wouldn't have quibbled if you'd said there were other methods, or even that other methods might be better.  But ...

Sorry, seems this is due to my poor English :)
OK, let it be nt "insists" but "strongly recommended" :)
Fine ;-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.