Creating a Working Business Logic Layer with MySQL

Question

User2135110853 posted

***Moderator. Please do not delete this post again. It is NOT a repeat. You deleted my original posts, so now no other exists.*** I need help creating a Business Logic Layer (BLL) using Visual Studio 2005 Pro with MySQL 5.1 as my database. I've tried without success to follow the tutorials step-by-step, but apparently they are not correct. Perhaps it is because I'm using MySQL, but regardless, I need to create a BLL, and the tutorial does not work. I've looked without success to find other tutorials, but they all repeat the same non-working tutorial. The following code is what I currently have as a BLL for one of my tables that I made using the tutorials as a step-by-step pattern. I've indicated several of my questions and concerns in the comments (shown in green). The biggest problems seem to be at the end of my Insert and Update methods, when it gets to " Adapter.UpdatePeopleTransaction(transactions)".

Imports Microsoft.VisualBasic

Imports PhoenixTableAdapters

<System.ComponentModel.DataObject()> _

Public Class PeopleTransactionsBLL

'This BLL is to handle transactions associated with a particular person.

'The primary key for table "TransPeople" is "TransPeopleID" and is

'an auto-incremented, non-null field. It then uses the foreign key

'"PeopleID" to link the transaction (child record) to the appropriate

'"People" record (parent record).

Private _peopleTransAdapter As PeopleTransTableAdapter = Nothing

Protected ReadOnly Property Adapter() As PeopleTransTableAdapter

   Get

   'Why is a precediing underscore used here? Is that some sort

   'of standard convention, or is it actually interpreted differently?

   If _peopleTransAdapter Is Nothing Then

      _peopleTransAdapter = New PeopleTransTableAdapter

   End If

   Return _peopleTransAdapter

   End Get

End Property

'This is where the problems begin. For starters, there's no such

'object listed in the Intellisense for "DataObjectMethodAttribute".

'There is "System.ComponentModel.DataObjectMethod", which is of

'class "DataObjectMethodAttribute", and if I manually append

'"Attribute" at the end, it recognizes it. It appears this may have

'just been a deprecated way of showing it, and either way, with or

'without "Attribute" added seems to "work". I went with how the

'tutorial had it here.

<System.ComponentModel.DataObjectMethodAttribute _

(System.ComponentModel.DataObjectMethodType.Select, True)> _

Public Function GetPeopleTransactions() As Phoenix.PeopleTransactionsDataTable

   Return Adapter.GetPeopleTransactions()

End Function

 

<System.ComponentModel.DataObjectMethodAttribute _

(System.ComponentModel.DataObjectMethodType.Select, False)> _ Public Function GetPeopleTransByPeopleID(ByVal peopleID As Integer) _

As Phoenix.PeopleTransactionsDataTable

   Return Adapter.GetPeopleTransByPeopleID(peopleID)

End Function

 

'I don't know if this is a problem (or how to change it), but while the

'tutorial shows all these parameters without the "ByVal" keyword, my

'Visual Studio keeps automatically adding them in.

'

'I also don't understand why the tutorial shows the strings in its

'example as not needing to be declared as Nullable. Why not? I definitely

'need to be able to leave some strings blank at times.

<System.ComponentModel.DataObjectMethodAttribute _

(System.ComponentModel.DataObjectMethodType.Insert, True)> _

Public Function AddPeopleTransaction( _

ByVal PeopleID As Nullable(Of Integer), ByVal RecordDate As Nullable(Of DateTime), _ ByVal RecordTime As Nullable(Of DateTime), ByVal TransType As String, _ ByVal Regarding As String, ByVal Detail As String, ByVal Processed As Boolean) As Boolean

Dim transactions As New Phoenix.PeopleTransactionsDataTable()

Dim transaction As Phoenix.PeopleTransactionsRow = transactions.NewPeopleTransactionsRow()

'PeopleID foreign key to associate with People record. Should not ever really be null.

If Not PeopleID.HasValue Then

   transaction.SetPeopleIDNull()

Else

   transaction.PeopleID = PeopleID.Value

End If

 

'RecordDate.

If Not RecordDate.HasValue Then

   transaction.SetRecordDateNull()

Else

   transaction.RecordDate = RecordDate.Value

End If

 

'RecordTime. Field to be deprecated after time merged with RecordDate.

If Not RecordTime.HasValue Then

   transaction.SetRecordTimeNull()

Else

   transaction.RecordTime = RecordTime.Value

End If

 

'TransType. Defined in MySQL as varchar(30)

If TransType Is Nothing Then

   transaction.SetTransTypeNull()

Else

   transaction.TransType = TransType

End If

 

'Regarding. Defined in MySQL aa varchar(255)

If Regarding Is Nothing Then

   transaction.SetRegardingNull()

Else

   transaction.Regarding = Regarding

End If

 

'Detail. This is memo field of MySQL type 'text'.

If Detail Is Nothing Then

   transaction.SetDetailNull()

Else

   transaction.Detail = Detail

End If

 

'Processed. Following the tutorial, Boolean values are not checked to see if they are null. Is this right?

transaction.Processed = Processed

 

'Add new transaction

transactions.AddPeopleTransactionsRow(transaction)

 

'This next line displays an error for the part

'"Adapter.UpdatePeopleTransaction(transactions)"

'The error says: "Argument not specified for parameter 'Regarding'

'of Public Overridable Overloads Function UpdatePeopleTransaction(

'PeopleID as System.Nullable(Of Interger),... all the parameters)

'As Interger"

'

'This is where I'm most stuck.

'Plus, why am I supposed to use the Update method here instead

'of the Insert method? [And yes, I've tried the Insert method too

'without success. Please help.

Dim rowsAffected As Integer = Adapter.UpdatePeopleTransaction(transactions)

Return rowsAffected = 1

End Function

 

'This Update method again follows the tutorial's pattern without success.

<System.ComponentModel.DataObjectMethodAttribute _

(ComponentModel.DataObjectMethodType.Update, True)> _

Public Function UpdatePeopleTrans(ByVal peopleID As Nullable(Of Integer), _

ByVal RecordDate As Nullable(Of DateTime), ByVal RecordTime As Nullable(Of DateTime), _ ByVal TransType As String, ByVal Regarding As String, ByVal Detail As String, _

ByVal Processed As Boolean, ByVal TransPeopleID As Integer) As Boolean

Dim transactions As Phoenix.PeopleTransactionsDataTable = _

Adapter.GetPeopleTransByPeopleID(peopleID)

If transactions.Count = 0 Then

   Return False

End If

Dim transaction As Phoenix.PeopleTransactionsRow = transactions(0)

'PeopleID foreign key to associate with People record. Should not ever really be null.

If Not peopleID.HasValue Then

   transaction.SetPeopleIDNull()

Else

   transaction.PeopleID = peopleID.Value

End If

 

'RecordDate.

If Not RecordDate.HasValue Then

   transaction.SetRecordDateNull()

Else

   transaction.RecordDate = RecordDate.Value

End If

 

'RecordTime. Field to be deprecated after time merged with RecordDate.

If Not RecordTime.HasValue Then

   transaction.SetRecordTimeNull()

Else

   transaction.RecordTime = RecordTime.Value

End If

 

'TransType. Defined in MySQL as varchar(30)

If TransType Is Nothing Then

   transaction.SetTransTypeNull()

Else

   transaction.TransType = TransType

End If

 

'Regarding. Defined in MySQL as varchar(255)

If Regarding Is Nothing Then

   transaction.SetRegardingNull()

Else

   transaction.Regarding = Regarding

End If

 

'Detail. This is memo field of MySQL type 'text'.

If Detail Is Nothing Then

   transaction.SetDetailNull()

Else

   transaction.Detail = Detail

End If

 

'Processed. Following the tutorial, Boolean values are not checked to see if they are null. Is this right?

transaction.Processed = Processed

 

'This next line also fails, the same as Insert method.

Dim rowsAffected As Integer = Adapter.UpdatePeopleTransaction(transaction)

Return rowsAffected = 1

End Function

 

<System.ComponentModel.DataObjectMethodAttribute _

(System.ComponentModel.DataObjectMethodType.Delete, True)> _ Public Function DeletePeopleTransaction(ByVal TransPeopleID As Integer) As Boolean

   Dim rowsAffected As Integer = Adapter.DeletePeopleTransaction(TransPeopleID)

End Function

End Class

Answer 1

User1747761241 posted

your missing something.  Tell us about the phoenix namespace where did that come from.  Why don't you call on phonix for support?  Here's a mysql adapter that has a select and insert.  No table paging or anything like that.  Returns a dotnet dataset.

drop the newest mysql.data.dll in the bin folder. 

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports MySql.Data.MySqlClient
Imports System.Web.Configuration
Imports System.Configuration
Imports System.Data

Namespace DWS
    Public Class MySql

        Public Function SelectRows() As System.Data.DataSet
            Dim cs As ConnectionStringSettings
            Dim conn As MySqlConnection
            Dim adapter As MySqlDataAdapter
            Dim ds As System.Data.DataSet

            cs = WebConfigurationManager.ConnectionStrings("BlogEngine")
            conn = New MySqlConnection(cs.ToString())
            adapter = New MySqlDataAdapter
            ds = New System.Data.DataSet

            Dim query As String = "SELECT ID, Title, Posted, Entry FROM posts ORDER BY Posted DESC;"
            adapter.SelectCommand = New MySqlCommand(query, conn)
            adapter.Fill(ds)
            Return ds
        End Function

        Public Function InsertRow(ByVal indate As DateTime, ByVal title As String, ByVal post As String) As Boolean
            Dim x As Integer
            Dim cs As ConnectionStringSettings
            Dim conn As MySqlConnection
            Dim cmd As New MySqlCommand
            cs = WebConfigurationManager.ConnectionStrings("BlogEngine")
            conn = New MySqlConnection(cs.ToString())
            conn.Open()
            Dim query As String = "INSERT INTO posts (Title, Posted, Entry) VALUES(@Title, @Posted, @Entry);"
            cmd.Connection = conn
            cmd.CommandText = query
            Dim P1 As New MySqlParameter("Title", title)
            Dim P2 As New MySqlParameter("Posted", indate)
            Dim p3 As New MySqlParameter("Entry", post)
            cmd.Parameters.Add(P1)
            cmd.Parameters.Add(P2)
            cmd.Parameters.Add(p3)
            x = cmd.ExecuteNonQuery()
        End Function



    End Class
End Namespace

 

 

 

 

Answer 2

User2135110853 posted

Hi Danny,

Phoenix is the name of my database which is in my Data Access Layer (DAL). I included it because the tutorial includes "Imports NorthwindTableAdapters". Don't I need to point it towards to my DAL? Looking at your example, it appears like your solution would be what I would need to do if I wanted to manually open and close my own connections, which is fine if that is what is necessary, but I thought part of the point of separating out the DAL from BLL was to let the DAL handle that part of the processs (opening and closing connection pools, etc.). Am I wrong? I'm trying diligently to use "best practices" patterns right from the start of this project so I can well document it and repeat it as needed going forward.

Answer 3

User2135110853 posted

Obviously, I'll need to add more business logic later, but I need to get the fundamental pattern working first. Does anyone have a working sample they can show me? What, if anything, needs to be different if working with MySQL?

Answer 4

User-319574463 posted

 For an example of working with MySql, please see

• http://www.codeplex.com/fckeditfilemanager
• http://www.codeproject.com/KB/database/MySQLCsharp.aspx
• http://www.codeproject.com/KB/vb/VBnet_to_mySQL_Server.aspx
• http://www.codeproject.com/KB/database/MySQLDBProgramming.aspx
  

Answer 5

User2135110853 posted

The very few replies that I've gotten on this all seem to answering someone else's question, but not mine. To be clear, I've been asking regarding how to make a working BUSINESS LOGIC LAYER (BLL), not a Data Access Layer (DAL). I know how to make a connection object, but in trying to adhere to best practices, I leave that in the DAL. Following the tutorials from this website, the DAL has already been created in an XML Schema Definition (XSD), and it handles the opening and closing of connections to the database. Now, the Business Logic Layer is supposed to interact with the DAL, and provide the logic or rules that come between the DAL and the Presentation Layer, such as how to handle null values when they are encountered.

The problem is that the tutorials here are wrong, do not work, and cause an error. This may be because I'm using MySQL and not the default SQL Server. Regardless, a 3-tier/n-tier solution must still be possible, even with MySQL, and hopefully someone has found the solution and is willing to share it here. Doesn't anyone know how to make a working BLL?

Answer 6

User-319574463 posted

 > I've been asking regarding how to make a working BUSINESS LOGIC LAYER (BLL), not a Data Access Layer (DAL).

The supposed advantage of BLL and DAL is that the DAL isolates the BLL from the various data sources. Like you I am surprised that nobody has volunteered experiance at writing a DAL specifically for MySQL.

Answer 7

User2135110853 posted

I have found the solution! To start with, the problem is a bug in how MySQL works (or fails to) with Microsoft's Visual Studio (Can't we all just get along?). The problem is this. When using the TableAdapter Configuration Wizard, users of MySQL cannot use the 'Advanced Options' feature to  Generate INSERT, UPDATE, and DELETE statements. Even if they check the box, it is ignored. So, it's not possible to follow the tutorials on this site completely if you use MySQL. However, not knowing that was a bug initially, I continued with the tutorial to 'Add Query' for an UPDATE method, an INSERT method, and DELETE method. Now the funky thing is that when called directly from the Presentation Layer, these all appeared to work fine, but not from the BLL as I've stated in my previous posts. What I did not know at the time was that even though I had created these queries, the TableAdapter was ignoring them! It didn't recognize that they existed! If I looked at the Properties sheet for the TableAdapter, the only Command that existed was the SelectCommand. The InsertCommand, UpdateCommand, and DeleteCommand showed a value of 'none'.

I have found the workaround for this bug where you don't need to manually write the class! After using the TableAdapter Configuration Wizard to create the SQL for your INSERT, UPDATE, and DELETE methods, copy the text of the SQL statement (I'll use UPDATE for this example), then open up the Properties sheet for the TableAdapter in question. You will see that while the SelectCommand exists, the InsertCommand, UpdateCommand, and DeleteCommand say 'none'. Click on that UpdateCommand 'none' and select 'new'. Then simply paste your working SQL statement into the CommandText and ...tada! It works! You can now use the UPDATE method from whereever you need it! Repeat for the INSERT and DELETE accordingly.

With UPDATE now properly available, I'm able to pass the PeopleDateTable as the only parameter needed to the DAL, thus avoiding the "Too many parameters" problem. 

I can now send this: rowsAffected = Adapter.Update(people)

instead of this: rowsAffected = CType(Adapter.UpdatePeople(person.RecordDate, person.LastUpdated, person.NamePrefix, person.UsePrefix, person.FirstName, person.MiddleName, person.UseMiddle, person.LastName, person.NameSuffix, person.UseSuffix, person.MailingName, person.Address1, person.Address2, person.City, person.State, person.ZipCode, ....[etc for 70 fields].... person.PeopleID), Boolean)

I've been wrestling this one for over a week now so I danced a jig when I got this to work. Hope this helps someone else!