I'm relatively new to VB.NET 2.0 and am having A LOT of trouble getting my head around the correct usage of DataSets, DataAdapters, DataTables etc particularly in an N-tier environment. Many/all examples I have been able to find regarding these objects use DbConnections on forms, in-line SQL statements, databinding etc and I'm finding this really confusing as I have always believed that best practices suggests otherwise?!?! I'm also struggling to come to terms with how I can use my own SELECT, INSERT, UPDATE, DELETE Stored Procedures (in SQLServer 2000). I realise the concept of ADO.NET is very different from traditional ADO and I guess I'm really struggling to let go of the old and accept the new, particularly when I don't fully understand how it works in a real-world situation. My history is VB6.0 utilising ADO, COM+ in a disconnected N-tier environment.
Assuming I want a Presentation Layer, Business Layer (and/or) DataAccess Layer and a DataLayer, how do I use a DataSet, DataAdapter, DataTables etc. correctly if I want my own StoredProcs to do all the real work in the DataLayer? I'm really struggling to work out how I pass my Selects, Inserts, Updates, and Deletes across the different layers to my Stored Procs at the Data Layer using these new tools particularly where I am only wanting to view a single record at a time rather than an entire table.
I'd be really appreciative if someone is able to provide me a simple code sample illustrating how I could achieve the following
1. PresLayer - a Form with say CompanyID, CompanyName, AccountManagerName (as a combobox underlying value being AccountMangerID based on some seletion )
2 . Bus/DataAccess Layer - Utilising (simple) custom Stored Procs as follows:
"SELECT C.CompanyID, C.CompanyName, AM.AccountManagerName, C.AccountManagerID
FROM Company C LEFT OUTER JOIN AccMangers AM ON C.AccountManagerID = AM.AccountManagerID
WHERE C.CompanyID = @CompanyID"
"UPDATE Company
SET CompanyName = @CompanyName,
AccountManagerID = @AccountmangerID
WHERE CompanyID = @CompanyID"
In terms of my form here's what I have so far
Public Class frmDemo
Private m_CompanyID As Integer
Public Property CompanyID() As Integer
Get
Return m_CompanyID
End Get
Set(ByVal value As Integer)
m_CompanyID = value
End Set
End Property
Private Sub frmDemo_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim ds As New DataSet
Try
'i'd probably like something in here also filling the combo box based on a Select Stored Proc
Dim objCompany As New clsCompany '===>My Bus/DataAccess Layer
objCompany.CompanyID = CompanyID ' init my Company class with CompanyID
If objCompany.bGetCompanyDeta
ils(ds) Then
' assuming my Dataset is now containing my select Company Details assign values to form
controls ? but how?
Else
MsgBox(objCompany.ErrMessa
ge)
End If
Catch ex As Exception
MsgBox(ex.Message)
Finally
ds = Nothing
End Try
End Sub
End Class
'=========================
==========
=========
'and my Dataaccess layer clsCompany
Imports System.Data.SqlClient
Public Class clsSQLTasks
Private m_CompanyID as Integer
Private mdbSQLConn As New SqlConnection
Private sErrMessage As String
Const sServer = "mySQLSVR"
Const sDBName = "myTestDb"
Const sUID = "sa"
Const sPWD = "manager"
Public Property CompanyID() As Integer
Get
Return m_CompanyID
End Get
Set(ByVal value As Integer)
m_CompanyID = value
End Set
End Property
Public Property ErrMessage() As String
Get
Return sErrMessage
End Get
Set(ByVal value As String)
sErrMessage = value
End Set
End Property
Public Sub New()
Dim sConnect As String
Try
If mdbSQLConn.State = ConnectionState.Closed Then
sConnect = "Data Source=" & sServer & ";Initial Catalog=" & sDBName & ";Password=" & sPWD & ";Persist Security Info=True;User ID=" & sUID
mdbSQLConn.ConnectionStrin
g = sConnect
mdbSQLConn.Open()
End If
Catch ex As Exception
ErrMessage = "Sub New() - " & ex.Message
End Try
End Sub
Public Function bGetCompany(ByRef ds As DataSet) As Boolean
Dim cmd As New SqlCommand
Dim da As SqlDataAdapter
Try
With cmd
.CommandText = "spGetCompany" ' refer Stored Proc SQL Statement above
.CommandType = CommandType.StoredProcedur
e
.Connection = mdbSQLConn
End With
da = New SqlDataAdapter()
da.SelectCommand = cmd
da.Fill(ds)
Return True
Catch ex As Exception
sErrMessage = "bGetCompany() - " & ex.Message
Return False
Finally
cmd = Nothing
da = Nothing
End Try
End Function
End Class
Any help would be fantastic.....Thanx Mark
