Question Help With Data Classes & Concepts

[dukeofawesome]

Hi,

After about 2 days of working on using a Data Class to return values from a database I'm finally getting somewhere. I can return data and read data without issue. What I'm having a major issue with is editing and writing data back to the database. My class looks like so:

Imports System.Data.OleDb 

Public Class clsDatabase 
Dim cnn As New OleDbConnection 
Dim dbProvider As String 
Dim dbSource As String 

Dim da As OleDb.OleDbDataAdapter 
Dim ds As New DataSet 

Sub ConnectDB() 
dbProvider = My.Settings.DatabaseCon 
cnn.ConnectionString = dbProvider 
cnn.Open() 
End Sub 
Sub CloseDB() 
cnn.Close() 
End Sub 

Function sqlSelect(ByVal strSQL As String) 
da = New OleDbDataAdapter(strSQL, cnn) 
da.Fill(ds, "Results") 
Return ds 

End Function 

End Class

I tried to add the following, but can't make it work:

Sub AddNew() 
Dim cb As New OleDb.OleDbCommandBuilder(da) 

da.Update(ds, "Results") 
End Sub

I'm assuming there is a better way of doing this, but this is what I've managed to put together so far so hopefully someone can point me to a good tutorial becuase this sort of stuff is very hard to find on Google. Any guidance will be most appreciated.

Thanks

 

[jmcilhinney]

This line:

da.Update(ds, "Results")

is how you save changes so you've got that bit right. Maybe you could explain what actually happens. I'm sure that you wouldn't expect a doctor to diagnose you without explaining the symptoms. There are only really three possibilities when that line is executed:

1. The call fails and an exception is thrown.
2. The call succeeds and returns zero.
3. The call succeeds and returns a non-zero value.

Which is it in your case?

 

[dukeofawesome]

I've managed to sort it out, thank you. For future reference if someone else need to get this working, here is my Class:

Imports System.Data.OleDb

Public Class clsDatabase
    Dim cnn As New OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String

    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet

    Sub ConnectDB()
        dbProvider = My.Settings.DatabaseCon '"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\cadata.accdb;" 
        'MsgBox(My.Settings.DatabaseCon)
        cnn.ConnectionString = dbProvider
        cnn.Open()
    End Sub
    Sub CloseDB()
        cnn.Close()
    End Sub

    Function sqlSelect(ByVal strSQL As String)
        da = New OleDbDataAdapter(strSQL, cnn)
        da.Fill(ds, "Results")
        Return ds

    End Function
    Sub AddNew(ByVal strSQL As String)
        Dim cmd As New OleDbCommand(strSQL, cnn)

        cmd.ExecuteNonQuery()

    End Sub
    Sub Update(ByVal strSQL As String)
        Dim cmd As New OleDbCommand(strSQL, cnn)

        cmd.ExecuteNonQuery()

    End Sub
    Sub Delete(ByVal strSQL As String)
        Dim cmd As New OleDbCommand(strSQL, cnn)

        cmd.ExecuteNonQuery()

    End Sub

End Class

 

[jmcilhinney]

Have you noticed how your AddNew, Update and Delete methods are exactly the same? There is nothing at all in any of them that is specific to the operation implied by the method name. That class needs a lot more thought. For one thing, there's no provision for using ADO.NET parameters so, besides any other problem that that may pose, it absolutely precludes you from saving binary data to the database.

 

[dukeofawesome]

The info regarding creating a Data Class that I have found over the past week is so cryptic, it's like it belongs to a secret club, and unless you're a member there is no way of finding out how to do it.

I know the AddNew, Update and Delete methods are exactly the same, but I just pass a SQL Statement to it and it works, so it does what I need for the moment.

If you have a link to a good guide on how to create one on these or alternatively, can make some suggestion on how best to modify mine to get the best out of it, I'm all ears.

I have been working on this stuff for about 4 days, and it has been hard work let me tell you...

 

[jmcilhinney]

If you have to pass SQL code in from outside this class then it's not a data access layer at all.

 

[dukeofawesome]

Like I said, if you can show me how a well structured DAL is done, I would love to see it. Is it a secret? layful: