3-tier ... no

It's probably my lack of understanding but it is a rather big concern to me...

I've seen people create components (dll's) for say their specific project so that they have some sort of 3-tier solution...

The component actually manipulates a database and its objects. Mostly one can create an object and call methods / functions on this object returning booleans, objects, recordsets, etc.

But I think and this is specifically for SQL Server, that this method is not ideal. For instance, with a three-tier system the client may create an object, then functions of that components are called from the dll...creating a connection and pulling back a recordset or updating a recordset directly. However, my concern is this is inefficient in my opinion. Why do people even bother creating components to represent a database for things such as UPDATES, SELECTS, and DELETES?

Why is there even a need to place SQL in the component when a stored procedure can yield much faster results...It helps to give an example:

Project: Employee
Class: clsEmployee

VB Code:

Dim objEmp As Employee.clsEmployee
Dim rst as ADODB.Recordset
 
Set rst = objEmployee.SelectData
 
'do something with rst
do while not rst.eof
  'blah
  rst.MoveNext
loop

Finally inside of the employee class I have a function called SelectData which returns recordsets/objects or whatever...but I dont see the great performance in implementing SQL on the component / client side. The component is still sitting on the client side processing the code...so an example might be the following:

VB Code:

private sub SelectData() as Object 'can be recordset here
 
'make connection to the database
'code...etc
 
Dim strSQL as String
 
strSQL = "SELECT blah1, blah2, blah3 FROM BlahTable"
 
SelectData = objConn.Execute(strSQL)

Where is the efficiency here ??? I'd rather have a two-tier system which processes recordsets via the ADODB.Command object and a simple stored procedure on the back end. SQL on the component side in my opinion is pointless...why not let the server do the grunt work?

Anyone with some comments ?
:confused:

I guess I could still use the ADODB.Command object and call the stored procedure from the component and return the result to the client...

Err...ADO is choke full of too many things...

:p

We left the VAX/VMS mainframe world and choose VB/SQL two-tier as a replacement method for our apps.

The VB Client is extremely light weight - has absolutely no business logic at all.

The SPROCS have everything.

The development team spends most of it's time writting SPROCS. We occasionally enhance the client to handle a new twist or two - but basically the VB client is static and all logic changes occur in the SPROCS.

Eventual goal is to have the VB client be a "single" FORM MDI Parent/Child program - cloning the child form over and over again based on what the user wants to do and "building" the labels and grids at runtime from a TABLE in SQL that says what X/Y location and size they should be on the form.

Here is an example of an INQUIRE SPROC - called to fill labels on a form and flex grids on a form.

It's named so it can self-bind to the VB client - the form name "frmTrans" is the begining of the SPROC name and "_Inquire" is the tail end. This means it gets "called" when the user clicks on "INQUIRE" button on the form.

The first 9 parameters are standard boilerplate - allow the SPROC to know/change state on the form. Parameters after that self-bind to either labels on the form or columns in a grid.

SELECT statements return data that gets bound to labels on the form based on the "name" of the column of data. As long as the label is named the same as the "column", the VB client puts the data in that spot.

You'll notice that one of the SELECT statements has a first column with a name like "~grdTrans" - this means that the data from that RS gets put into a FLEX GRID called "Trans" on the form.

The columns of the flexgrid come right from the SELECT statement itself - you'll notice that the columns are "padded with spaces".

After the GRID SELECT STATEMENT is a ugly little "GRID-CONTROL-STRING" select. This SELECT data tells the VB Client how the grid columns will behave. Start column, end columns - you'll notice one is a 'lookup=Account' for "~col=1". This means that column one will call another SPROC call frmTrans_View_Account to "validate" that data. I've included that SPROC as well.

Code:

---

USE Acctfiles
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[frmTrans_Inquire]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[frmTrans_Inquire]
GO
CREATE PROCEDURE frmTrans_Inquire
        @PassConnId int
        ,@RetStat int Output
        ,@RetText varchar(100) Output
        ,@RetMode int Output
        ,@RetGrid int Output
        ,@RetExtra varchar(100) Output
        ,@PassMode int
        ,@PassExtra varchar(50)
        ,@PassTest varchar(10)
        ,@FiscalYr int
        ,@Owner varchar(12)
        ,@BatchInfo varchar(35)
        ,@ReleaseDt datetime
        ,@CycleNo tinyint
        ,@BatchEntry int
        ,@Collapse int
AS

Declare @BatchStat varchar(1)

Set @RetStat = 0
Set @RetText = ''

If @PassMode=1
Begin
        Select Top 1 CycleNo,'O' "BatchStat" From Cycle_T CY
                        Where CY.FiscalYr=@FiscalYr and CY.StartDate<=@ReleaseDt
                        Order by CycleNo Desc --"CycleNo"
        If @@RowCount=0
        Begin
                Select @FiscalYr "FiscalYr", @Owner "Owner", @BatchInfo "BatchInfo", @BatchInfo "BatchNo"
--                Select 9999 "FiscalYr", 'BAD' "Owner", 'BNO' "BatchNo"
                Set @RetStat=-1
                Set @RetText='Release Date is not found in Fiscal Year Cycles'
                Return
        End
        Set @BatchStat='O'
End
Else
Begin
        Set @BatchStat=(Select BatchStat From Batch_T BT Where BT.FiscalYr=@FiscalYr and BT.BatchEntry=@BatchEntry)
        Select --BatchNo "BatchInfo",
        Convert(Char(10),ReleaseDt,101) "ReleaseDt",
        CycleNo,BatchStat from Batch_T BT Where BT.FiscalYr=@FiscalYr and BT.BatchEntry=@BatchEntry
End

If @Collapse=1
Begin
        Select        Convert(char(10),LE.TransDate,101) "~grdTrans/Trans Date  "
        ,LE.Reference "Reference"
        ,Sum(ABS(LE.CreditAmt) - LE.DebitAmt)  ">Total Amount          "
        ,Sum(ABS(LE.CreditAmt)) ">Credit Amount          "
        ,Sum(LE.DebitAmt) ">Debit Amount          "
        from Ledger_T Le 
        Left Join Batch_T BA on BA.FiscalYr=LE.FiscalYr and BA.BatchEntry = LE.BatchEntry
        Left Join SLASN_T SL on LE.SLASN = SL.SLASN
        where ( (Le.FiscalYr = @FiscalYr)-- and (BA.FiscalYr = @W_FiscalYr)
                and (LE.BatchEntry = @BatchEntry) )
        group by LE.TransDate,LE.Reference
        order by LE.TransDate,LE.Reference

        Select        'nofixed'                "~col=0"
                ,'money'                "~col=2"
                ,'ttl=0'                "~col=2"
                ,'money'                "~col=3"
                ,'money'                "~col=4"

        Set @RetText='Cannot Modify when Entries are collapsed'
        Set @RetMode=4
End
Else
Begin
        Select        Convert(char(10),LE.TransDate,101) "~grdTrans/Trans Date  "
        ,CASE WHEN LE.SLType='G' Then GL.GLAcctDesc + ' (' + LE.GLFund + LE.GLObject + LE.GLSubobj + ')'
                Else SL.SLASNDesc + ' (' + LE.SLASN + LE.SLYear +')' End
        "Account                                                                                  "
--        ,LE.RcptType "Type"
        ,LE.TransDesc "Description                                                "
        ,LE.Reference "Reference "
        ,ABS(LE.CreditAmt) - LE.DebitAmt  ">Amount          "
--        ,LE.AcctNo "Account Number                                                                            "
        ,LE.TransEntry "TransEntry"
        ,CASE WHEN LE.SLType='G' Then LE.GLFund + LE.GLObject + LE.GLSubobj
                Else LE.SLASN+LE.SLYear END "ASNYR"
        from Ledger_T Le 
        Left Join Batch_T BA on BA.FiscalYr=LE.FiscalYr and BA.BatchEntry = LE.BatchEntry
        Left Join SLASN_T SL on LE.SLASN = SL.SLASN
        Left Join GLAcct_T GL on GL.GLFund=LE.GLFund and GL.GLObject=LE.GLObject and GL.GLSubObj=LE.GLSubObj
        where ( (Le.FiscalYr = @FiscalYr)-- and (BA.FiscalYr = @W_FiscalYr)
                and (LE.BatchEntry = @BatchEntry) )
        order by LE.TransDate, LE.TransEntry, LE.SLASN

        Select        'nofixed'                "~col=0"
                ,'start'                "~col=0"
                ,'date'                        "~col=0"
                ,'def=.prev'                "~col=0"
                ,'mustenter'                "~col=0"
                ,'def=@pcol6'                "~col=1"
                ,'lookup=Account'        "~col=1"
--                ,'def=.prev'                "~col=2"
--                ,'lookup=RcptType'        "~col=2"
                ,'mustenter'                "~col=2"
                ,'def=.prev'                "~col=3"
                ,'money'                "~col=4"
                ,'mustenter'                "~col=4"
                ,'ttl=0'                "~col=4"
                ,'end'                        "~col=4"
                ,'retextra'                "~col=5"
                ,'hide'                        "~col=5"
                ,'hide'                        "~col=6"
        If @BatchStat<>'C'
                Set @RetMode=5
        Else        Set @RetMode=4
End
go

GRANT EXECUTE ON frmTrans_Inquire TO AcctfilesUser
Go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---

Code:

---

USE Acctfiles
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[frmTrans_View_Account]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[frmTrans_View_Account]
GO

--execute frmTrans_View_Account 0, '', '', '', 0001

CREATE PROCEDURE frmTrans_View_Account
        @PassConnId int
        ,@RetStat int Output
        ,@RetText varchar(100) Output
        ,@RetMode int Output
        ,@RetGrid int Output
        ,@RetExtra varchar(100) Output
        ,@PassMode int
        ,@PassExtra varchar(50)
        ,@PassTest varchar(10)
        ,@Account varchar(50)
        ,@FiscalYr int
        ,@Col6 varchar(50)
AS

Declare @W_SLASN varchar(4)
Declare @W_SLASNDesc varchar(50)
Declare @W_SLYear varchar(2)
Declare @W_Right varchar(8)
Declare @W_GLFund varchar(2)
Declare @W_GLObject varchar(4)
Declare @W_GLSubObj varchar(4)
Declare @W_Check varchar(50)

Restart:

If IsNull(@Account,'')=''
Begin
        Set @RetStat=-1
        Set @RetText='Must enter an Account Number (ASN + Year)'
        Return
End

If ISNUMERIC(@Account) = 1
Begin
        If Len(@Account) = 6
        Begin
                Set @W_SLASN = LEFT(@Account,4)
                Set @W_SLYear = Right(@Account,2)
        End
        Else
        Begin
                If Len(@Account) = 10
                Begin
                        Set @W_GLFund = LEFT(@Account,2)
                        Set @W_GLObject = SubString(@Account,3,4)
                        Set @W_GLSubObj = Right(@Account,4)
                End
                Else
                Begin
                        Set @RetStat=-1
                        Set @RetText='Must enter a 6 digit ASN/Year or 10 digit GL Account'
                        Return
                End
        End
End
Else
Begin
        Set @W_Check='(' + @Col6 + ')'
        If Right(@Account,Len(@W_Check)) = @W_Check
        Begin
                Set @Account=@Col6
                Goto Restart
        End
        Set @W_SLASNDesc=@Account + '%'
End

If IsNull(@W_GLFund,'')=''
Begin
        Select TOP 51 --SL.SLASN + LE.SLYear "Account",SL.SLASN + LE.SLYear
        SL.SLASNDesc + ' (' + LE.SLASN + LE.SLYear + ')' "Account"
        ,LE.SLASN + LE.SLYear "ASNYR"
        From Ledger_T LE
                Left Join SLASN_T SL on LE.SLASN = SL.SLASN
        Where LE.FiscalYr = @FiscalYr
                and ( (@W_SLASN Is Null) Or ((LE.SLYear = @W_SLYear) And (LE.SLASN=@W_SLASN)) )
                and ((@W_SLASNDesc Is Null) Or (SL.SLASNDesc=@W_SLASNDesc) Or (SL.SLASNDesc Like @W_SLASNDesc))
        Group By SL.SLASNDesc,LE.SLASN, LE.SLYEar
        Order By SL.SLASNDesc,LE.SLASN, LE.SLYear
End
Else
Begin
        Select GLAcctDesc + ' (' + GLFund + GLObject + GLSubObj +')' "Account"
        ,GLFund+GLObject+GLSubObj "ASNYR"
        From GLAcct_T GL
                Where GL.GLFund=@W_GLFund and GL.GLObject=@W_GLObject and GL.GLSubObj=@W_GLSubObj
End

Go
GRANT EXECUTE ON frmTrans_View_Account TO AcctfilesUser
Go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---

SO basically you are using a two-tier approach...

I think you too could use a 3-tier approach...having the component make the calls to the database (combination of the Command object and your stored procedure). That way your client is really really thin.

But again the performance I feel in your case might not be worth it...it may be very little. My point was too many people write components and then write the actual SQL in that component rather than creating a stored procedure. I don't see why:confused: :confused:

two-tier - yes...

Our VB CLIENT has DOZENS of Forms - but the code (event code) behind each form is 100% the same from form to form.

The events call to functions/subs in a BAS MAIN that do the BINDING and LOADING of grids - etc. In effect this is our "middle tier". Almost every call from a FORM event into a SUB in the BAS MAIN passes the FORM itself as a parameter.

We have complex arrays that are built at FORM startup time for tracking what CONTROL ARRAY INDEX is what.

I guess the one point I should have made is that this model is working well at small businesses (dozens of users) with tables that contain millions of rows. It's also working well at school districts - hundreds of users - across town - many buildings - tables with less than million rows.

Our opinion is that T-SQL is really the data manipulation tier - and packaging it into the SQL box with the data makes a neat little package at that.

This is exactly the same setup I have done.

I think middle tiers as component (dll's) representing a 3-tier system are a tad bit exaggerated...especially when using a huge rdbms such as SQL Server.

The client dev folks dont quite see the benefits of what the server can handle.

O well..I guess just maybe next time I'll look into three tier.
For now..I don't see too many benefits.

Thanks for sharing your own work experiences.

:)

Here's the lowdown as I understand it. What most people call 3 tier or n-tier really isn't. It's actualy 3-layer or n-layer.

True 3/n-tier architecture means that the client holds the UI and some basic logic for the UI, that's it, nothing else. All of the business logic resides in components (and here's the key) on a separate server on the network. This then becomes the BL server. The UI client only communicates with the BL server. It is then up to the BL server to determine if it needs data or not. It then contacts, yet another server and requests the data. The BL doesn't care where the data comes from, nor how it is retrieved. It could be a flat file, Access, or SQL, or even Oracle.

This second server is the Data Access Server. If the data is to then be retrieved from a database, then it makes the necessary calls to the database, gathers the info, and returns the data, preferably in a non-database format (text stream, XML, data objects, etc) to the BL, which then in turn sends it (object independant) to the UI.

Most people think that putting all BL or DA into DLLs automatically makes it a 3/n-tier application. All it does is make it a multi-layered app. The idea behind both methods is to separate the UL from the Business from the data. By making each piece independant, it becomes easier to maintain and upgrade.

Another advantage is that you can then have one UI, with multiple data access components for different database types. You simply just include/install which data access type that you need.

The only reason I can see of embedding SQL right into the code is because the data source doesn't support stored procs.

TG

Yes TG I think has a great point.

I think the term three tier or n tier is loosely used by many. Including components into a project doesn't make it a specific tier, just a certain layer. A lot of books, I cant believe this, state this as tiers :cry: ...NOT!!

I think the main point is that these components sit on another server (BL Server). Anytime a component is updated you don't have to update each client, you just place that activeX dll or .exe onto the BL server and no need for running around. So I do see some advantages I guess of true n-tier (3 tier in this case) applications.

Thanks TG for your input.

Quote:

---

Originally posted by techgnome
The idea behind both methods is to separate the UL from the Business from the data. By making each piece independant, it becomes easier to maintain and upgrade.

Another advantage is that you can then have one UI, with multiple data access components for different database types. You simply just include/install which data access type that you need.

---

Nice definition, TG...

The only reason I could see for separating all three layers is if we were the size of M$ and wanted to support many different DB's...

Since our customers are M$ only - like most of the free world - we are happy to use VB, T-SQL and SQL backend...

Another reason we choose our method was that we start this project 3 years ago - too early to use VB.Net but fully aware that VB6 was going to go away. But T-SQL and SQL are here to stay...

Quote:

---

Originally posted by szlamany
Nice definition, TG...

The only reason I could see for separating all three layers is if we were the size of M$ and wanted to support many different DB's...

Since our customers are M$ only - like most of the free world - we are happy to use VB, T-SQL and SQL backend...

Another reason we choose our method was that we start this project 3 years ago - too early to use VB.Net but fully aware that VB6 was going to go away. But T-SQL and SQL are here to stay...

---

Good logic :thumb:

I think I am in the same boat....too late for .NET now at least for this app.

Thanks again

Quote:

---

Originally posted by techgnome

True 3/n-tier architecture means that the client holds the UI and some basic logic for the UI, that's it, nothing else. All of the business logic resides in components (and here's the key) on a separate server on the network. This then becomes the BL server. The UI client only communicates with the BL server. It is then up to the BL server to determine if it needs data or not. It then contacts, yet another server and requests the data. The BL doesn't care where the data comes from, nor how it is retrieved. It could be a flat file, Access, or SQL, or even Oracle.

---

I agree almost entirely, except to point out that you might call them Services instead of Servers, as those services could reside on the same physical machine and still be considered different "tiers". As an example, an FTP service could reside on the same machine as an HTTP service, or they could be seperated to reside on different physical servers.

Most tiers are in fact located on different servers for performance/security reasons, but is not a requirement.

Quote:

---

Originally posted by Dave Sell
I agree almost entirely, except to point out that you might call them Services instead of Servers, as those services could reside on the same physical machine and still be considered different "tiers". As an example, an FTP service could reside on the same machine as an HTTP service, or they could be seperated to reside on different physical servers.

Most tiers are in fact located on different servers for performance/security reasons, but is not a requirement.

---

Good point, I didn't even think about services. So, I guess it comes down to how you define a tier vs a layer. To me a tier is a data independant object that resides somewhere. It is data source ignorant and care what you do with the data once you've got it. It's kinda like a faucet. IT doesn't care where the water comes from, it jsut knows where to get it from when it needs it. And it doesn't care what you do with it once it is served up to you.

A layer on the other hand is a bit more data aware, and you could multi-layer a single tier. I think.....

TG

This article (pushing COM development) has a good description of multi-tier (or is it multi-layer :confused: ) development (It's a good read).

http://msdn.microsoft.com/library/de...complus4vb.asp

Quote:

---

Originally posted by Dave Sell
This article (pushing COM development) has a good description of multi-tier (or is it multi-layer :confused: ) development (It's a good read).

http://msdn.microsoft.com/library/de...complus4vb.asp

---

I would say that multi tier would be components on various servers and registered by the client pc.

One example I have is an Update dll used to update a specific database written especially for outlook. Without getting into the nitty gritty. What I did was when a task was marked complete in outlook's task feature I updated a record in the database that corresponded to the task. The dll sat on our email server...I'd say that could be an example of a small tier where the client application does not directly access the db...all the client does is interact as a UI. The actuall dll component interacted with the database and updated records when needed.

The dll was registered and referenced on each machine. When I changed the dll I would only have to replace the old file with a new one and it would automatically reflect the end users settings.

There is a COM plug in / add in section in outlook under tools options advanced

n-tier vs n-layer...who cares?
It's EXACTLY the same coding...although yea, u need to do a tiny bit more to get it running in COM+.

Woof