oledbdatareader vs dataset?

thejeraldo

Well-known member
Joined
Jun 9, 2009
Messages
70
Location
Philippines
Programming Experience
1-3
hi guys. i'm just new here at vb.net forums. so this is my first post here. hope this one will be helpful to me and i hope that i can help others too someday.

i just graduated from college and i've studied VB6 at school but not that deep. now, i'm currently learning vb.net. i have a company project called HRIS (human resource information system) and i intend on using vb.net on it. i just learned how to connect to a database and add,edit,delete records by myself thanks to some good tutorials on the internet. i don't use a wizard to connect to a database and bind it to a control. i code it.

i came across this thing called OleDBDataReader. they say its used to read data FAST. i use a dataset and fill it with the data from the dataapadter. i'm really concerned about speed issues on .net since the computer i'm using on our office doesn't run vs2008 that fast even in a dual core processor. i've barely convinced my partner that we use vb.net on it. but i'm just concerned that the dataset might run slow when the database gets filled up with 1000+ records or so. (sorry for the long introduction)

my question: can oledbdatareader replace dataset or dataadapter? which one is faster? i use a dataset to fill up a listview control. here is the code i currently use.

VB.NET:
Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim cmd As New OleDb.OleDbCommand
    Dim sqlemp, sqlcmd, askdel, pic, picslash(), picpath As String
    Dim x, id As Integer

    con.ConnectionString = "Provider=SQLNCLI;Server=MIS\SQLEXPRESS;Database=HRISdb;Initial Catalog=HRISdb;Integrated Security=SSPI;"
    con.Open()

    sqlemp = "SELECT * FROM employeetbl"
    da = New OleDb.OleDbDataAdapter(sqlemp, con)
    da.Fill(ds, "Employeetbl") 'Fill the DataSet

    Private Function filllist()
        'Add ListView Columns
        lvw1.Columns.Add("ID", 80, HorizontalAlignment.Left)
        lvw1.Columns.Add("Last Name", 100, HorizontalAlignment.Left)
        lvw1.Columns.Add("First Name", 100, HorizontalAlignment.Left)
        lvw1.Columns.Add("Middle Name", 100, HorizontalAlignment.Left)
        lvw1.Columns.Add("Picture", 100, HorizontalAlignment.Left)
        lvw1.Width = 385
        'Add ListView Items and Subitems
        Dim lvi As ListViewItem
        For x = 0 To (ds.Tables("Employeetbl").Rows.Count - 1)
            lvi = New ListViewItem
            lvi.Text = ds.Tables("Employeetbl").Rows(x).Item(0)
            lvi.SubItems.Add(ds.Tables("Employeetbl").Rows(x).Item(1))
            lvi.SubItems.Add(ds.Tables("Employeetbl").Rows(x).Item(2))
            lvi.SubItems.Add(ds.Tables("Employeetbl").Rows(x).Item(3))
            lvi.SubItems.Add((ds.Tables("Employeetbl").Rows(x).Item(4)) & "")
            lvw1.Items.Add(lvi)
        Next x
    End Function

    Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click
        lvw1.Enabled = True
        cmd = New OleDb.OleDbCommand
        cmd.Connection = con
        sqlcmd = "INSERT INTO Employeetbl (lname,fname,mname,picture) VALUES ('" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & pic & "')"
        cmd.CommandText = sqlcmd
        cmd.ExecuteScalar()
        lvw1.Items.Clear()
        x = 0
        lvw1.Refresh()
        ds.Clear()
        da.Fill(ds, "Employeetbl")
        lvw1.Clear()
        filllist()
        sqlcmd = Nothing
    End Sub

As you see, i posted my connectionstring and how i fill the dataset with the dataadapter and how i add a new record.

thanks in advance guys!
 
Databases are where data lives, not client machines. You don't load 1000 records into your dataset unless you actually plan on doing something with all of them, like letting the user edit all 1000. Normally, you tell your users "No, i'm not going to do an app that lets you scroll all 1 billion transactions in the database. You can have a search function and review just a few of them"

DataAdapter uses a datareader to retrieve its data into a dataset (something like a recordset from old vb6). Try to keep the amount of data you hold locally very small.

Additionally, youre worrying about a problem that you havent encountered yet. Cross bridges when you come to them, not when you think they might exist somewhere
 
thanks for the response guys.

well, a lot of people say datareader is faster, some says go for dataset if you need more functionality. and of course, its up to the programmer which one will he use according to the app he is making.

well, can i just retrieve data and put it a listview. then when a user clicks on a record, string values are passed to textboxes and other controls then i use an oledbcommand to execute like an add, update and delete. then refresh the datareader and fill the listview again. do you see any issues here or what?
as you can see in my first post, in the code i wrote i dont update a dataset like a dataset.update or something just using SQL to execute.

thanks.
 
Last edited:
Additionally, youre worrying about a problem that you havent encountered yet. Cross bridges when you come to them, not when you think they might exist somewhere
You mean like storing a date as dd.mm.yy because 2k was far away in the 80s and memory was expensive?

scnr ;)
 
i don't use a wizard to connect to a database and bind it to a control. i code it.
Do you also not use a wizard to design the layout of your form? Write your own code for that? Why even use Visual Studio at all? Why not use notepad?
Seriously though, VB is for RAD. Seems a shame to not use all the tools at your disposal.
 
@Paszt:
what i mean is i dont use a wizard for my connection, i use a Typed Dataset. then i just code when i need to bind it to a list view like the code i posted.
 
thanks for the response guys.

well, a lot of people say datareader is faster
A lot of people are idiots too. VB uses a datareader to fill a dataset. Exactly how is your use of it going to be faster than that? It's like saying that you can fill a bucket from the garden tap faster than your dad can - you both use the same tap, and open it to the same amount

some says go for dataset if you need more functionality. and of course, its up to the programmer which one will he use according to the app he is making.
There really are quite clear situations when you would use one or the other. It simply reduces to:
Are you going to download a small ammount of data, edit it and send it back to the db? Use a DataAdapter to fill a DataSet
Are you going to pull a large amount of data off, write it to a file (some kind of report) and send it somewhere? Use a DataReader

Neither decision is based on SPEED, it is based on memory usage in the client. There is no point downloading a million rows into a dataset if all youre going to do is write them to disk; you might as well just write as you go and save the memory overhead

well, can i just retrieve data and put it a listview. then when a user clicks on a record, string values are passed to textboxes and other controls then i use an oledbcommand to execute like an add, update and delete. then refresh the datareader and fill the listview again. do you see any issues here or what?
Yes. It's a waste of your life, taking hours to write code that I can get Visual Studio to do a better job of in seconds. Read the DW2 link in my signature, start with Creating a Simple Data App and you'll get a feel for how we do trivial things like shoeing data from and to a database and letting the user see it and edit it
 
@Paszt:
what i mean is i dont use a wizard for my connection, i use a Typed Dataset. then i just code when i need to bind it to a list view like the code i posted.


But as Paszt points out, that's hypocritical. You're writing code to bind it to a list view when Visual Studio will do that for you at the same time as it sets everything else (like location, size, anchoring, background colour, font etc - all the stuff you used visual studio to set).

Additionally; have you seen the code VS generates for you when you use it to make the connection? Honestly tell me that you can do a better job faster - I've seen too many people make that claim and then it turns out their data access code is string concatenated SQLs in button click event handlers.. :$ Using VS for your connection builds you a well-encapsulated, reusable, strongly typed data access layer geared for multi-user performance in seconds. Humans just cannot do that
 
Yeah because the world really fell over didn't it?
Personally I never believed that the world would implode on 1.1.2k

Still it remains a fact, that fixing old programs (like making sure that a grandgrandma doesnt become a toddler) was a quite expensive tasks. Didn't they even teach ppl Cobol again, bc many old apps where done with it and the original programmers wherent available anymore?

Anyway: Depending on the situation it DOES make sense to look into the future. Though "performance" probably is not the biggest problem, because if the problem arises somewhere in the future, available hardware will probably be able to handle it.
 
Personally I never believed that the world would implode on 1.1.2k

Still it remains a fact, that fixing old programs (like making sure that a grandgrandma doesnt become a toddler) was a quite expensive tasks. Didn't they even teach ppl Cobol again, bc many old apps where done with it and the original programmers wherent available anymore?
Yep, and the doom mongers said "too little, to late"

The y2k problem was known about and being catered for in the 80s in the systems where it mattered, so there was some future thinking going on.

I also got to thinking more about my statement "cross bridges when you come to them" and the parallel of the millennium bug and I dont really see it. Concluding that a lack of the former precludes events such as the latter is like responding to me that I'm advocating developing a program that ius broken and doesnt work because you'll cross that [doesnt work] bridge when you come to [release the program] -> there's a base level of foresight that's necessary and also it must be considerd what is actually worth thinking about in terms of future need.

Depending on the situation it DOES make sense to look into the future. Though "performance" probably is not the biggest problem, because if the problem arises somewhere in the future, available hardware will probably be able to handle it.

Totally agree, though Moore's law shouldnt be an excuse for burning up resources unnecessarily but the games industry tends to do it (release games that are possibly beyond the capabilities of all but the very best hardware) and it drives the hardware companies to play a useful game of catchup. Quite mercenary, IMHO
 
the parallel of the millennium bug and I dont really see it
Because I tend to disagree that y2k was a "known problem". Hardly none (I guess only of course) of the original programmers ever expected that their code would be used more or less unchanged for more than a decade.

Back to topic:
I would think that "dataset vs datareader" is a major design decision, that can not easily be changed somewhere in the future without more or less completely rewriting most parts of the app?
 
I would think that "dataset vs datareader" is a major design decision, that can not easily be changed somewhere in the future without more or less completely rewriting most parts of the app?

It's probably something you'd migrate gradually..
 
Back
Top