473,549 Members | 3,109 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

internalization of (generic) application layer software to the database TSQL

Greetings to all database professionals and laymen,

Let us make a bold assumption that we have developed a software
tool for the SQL Server environment which simply acts as an interface
between an end-user in an organization and the database, through
the exclusive use of stored procedures which are authored by the
organization or by software developers.

All development work at the application software level may thereby
be conducted within SQL, by the development of TSQL stored
procedures and their coordination across an organization.

The question then needs to be asked what are the advantages of this
arrangement and what are the disadvantages. I would appreciate
your comments here, as it is difficult for folk heavily involved (eg: me)
in something to obtain objective opinion otherwise.
Potentially it is possible to construct an entire database application
software package using only TSQL stored procedures and this
tool. When a database backup is conducted not only the data is
backed up but also all the "program development" suite, in the
form of stored procedures (and their scheduling, for example).

One of the advantages from my perspective is that this arrangement
implies the possibility that all software external to the database may
be made redundant (except this tool), and along with it, all the other
redundancies of managing application development (life-cycles) within
the database and then coordinating these changes with software
external the database (particulary loading it to the client environment)

You see, I believe that (for example) any and every application written
in VB, C, etc, etc, etc (external to rdbms) expressly for database software
(ie: rdbms software such as SQL Server) involves a redundancy of data
definitions required ---- once within the VB and once within the db.
I would appreciate any feedback on any of the above issues, and
wish everyone the compliments of the season.

Pete Brown
Falls Creek
NSW
Oz
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~
EDITOR:
BoomerangOutPos t: Mountain Man Graphics, Newport Beach, {OZ}
Thematic Threading: Publications of Peace and Of Great Souls
Webulous Coordinates: http://www.mountainman.com.au
QuoteForTheDay:

"Consciousn ess is never experienced in the plural, only in
the singular. How does the idea of plurality (emphatically
opposed by the Upanishad writers arise at all? .... the
only possible alternative is simply to keep the immediate
experience that consciousness is a singular of which the
plural is unkown; that there *is* only one thing and that
what seems to be a plurality is merely a series of different
aspects of this one thing produced by deception (the Indian
maya) - in much the same way Gaurisankar and Mt Everest turn
out to be the same peak seen from different valleys."

- E. Schrodinger, "What is Life"
--------------------------------------------------------------------



Jul 20 '05 #1
18 4578
> All development work at the application software level may thereby
be conducted within SQL, by the development of TSQL stored
procedures and their coordination across an organization.


Hi,

I already write 80% of my code in stored procedures. I also use
xp_cmdshell inside SPs for FTP and other DOS commands. So I'm all for
the idea of encapsulating all development work inside TSQL stored
procedures. With the coming of Yukon and the ability to embed C# and
VB.net code directly in SPs, I would probably write even more of my
code inside stored procedures. The one area where I think code
belongs on the client, involves data-entry applications. Here I want
to instantaneously respond to user clicks & inputs. I don't want to
make a round-trip to the server every time the user clicks - so data
validation and instant totals need to remain on the client.

Ramblings. I have a lot invested in the DB backend and web client
paradigm. The web services and windows app paradigm is not something
I really want to explore. The MSSQLSERVER2000 reporting services
looks promising, but I'm somewhat wary as my users are happy and
comfortable going to our current web sites. If the reporting services
gives me the capability to develop something that looks like a web
site, with logins, bread crumbs, hyperlinks, etc then I'll pursue it.
The coming of Asp.net 2.0 is also promising. The new sqldatasource
and gridview tags will save me a lot of coding and allow me to
concentrate on business logic, instead of how to make ado.net work
with asp.net. I guess my major wish is for M$ to evolve asp.net into
a meta language for all web client functionality. If they do that,
I'll gladly brain dump html, style sheets, javascript ... and
concentrate on Tsql, asp.net and C#.
Jul 20 '05 #2
mountain man (hobbit@souther n_seaweed.com.o p) writes:
Potentially it is possible to construct an entire database application
software package using only TSQL stored procedures and this
tool. When a database backup is conducted not only the data is
backed up but also all the "program development" suite, in the
form of stored procedures (and their scheduling, for example).
Might be that the stored procedures are backed up too, but stored
procedures in a database is to be regarded as binaries. The source
should be elsewhere, in a version-control system. (Which may be implemented
on the top of an SQL database.)
You see, I believe that (for example) any and every application written
in VB, C, etc, etc, etc (external to rdbms) expressly for database
software (ie: rdbms software such as SQL Server) involves a redundancy
of data definitions required ---- once within the VB and once within the
db.


And maybe once in the business layer.

And this is not without good reason. If you were a military commander
who were set to defend something important, would you settle on one
single defense line?

Take an example: order registration. The database needs to know that
customer ID must be registred, and this is probably enforced with
a NOT NULL constraint. But the user-interface also needs to know this,
so that it give the user a comprehensible error message. It is not
sufficient to slam "Attempt to insert NULL in column...".

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
mountain man (hobbit@souther n_seaweed.com.o p) writes:
Potentially it is possible to construct an entire database application
software package using only TSQL stored procedures and this
tool. When a database backup is conducted not only the data is
backed up but also all the "program development" suite, in the
form of stored procedures (and their scheduling, for example).
Might be that the stored procedures are backed up too, but stored
procedures in a database is to be regarded as binaries. The source
should be elsewhere, in a version-control system. (Which may be

implemented on the top of an SQL database.)

Of course.

You see, I believe that (for example) any and every application written
in VB, C, etc, etc, etc (external to rdbms) expressly for database
software (ie: rdbms software such as SQL Server) involves a redundancy
of data definitions required ---- once within the VB and once within the
db.


And maybe once in the business layer.

Absolutely, and then again possibly in an executive information
system of some format spanning everything above. You get
the drift.
And this is not without good reason. If you were a military commander
who were set to defend something important, would you settle on one
single defense line?

Really, it would entirely depend upon the nature of the battle and
what resources the commander had at his or her disposal.

For example, when faced with a superior foe, one's best chance
of survival might necessitate putting all the eggs into the one basket
and retreating to an emminently defensible location. (Helm's Deep ;-)
I think the reason for the redefinition of the data in multiple layers
mapped onto the database has been the historical development of
the technology --- not because it is any more secure or defensible.
Take an example: order registration. The database needs to know that
customer ID must be registred, and this is probably enforced with
a NOT NULL constraint. But the user-interface also needs to know this,
so that it give the user a comprehensible error message. It is not
sufficient to slam "Attempt to insert NULL in column...".

Data validation is obviously the weak link in the model I have
put forward for discussion, however the problems associated
with this issue need to be isolated from some of the benefits
to be gained by operating all development within one environment.

The user interface can be re-arranged as a data staging area.
Data validation can be engineered by multi-process steps and
managed as a separate task.

I appreciate the pin-point response.

Compliments of the season,


Pete Brown
Falls Creek
OZ
--------------------------------------------------------------------
BoomerangOutPos t: Mountain Man Graphics, Newport Beach, {OZ}
Thematic Threading: Publications of Peace and Of Great Souls
Webulous Coordinates: http://www.mountainman.com.au

QuoteForTheDay:

"A skillful soldier is not violent;
An able fighter does not rage;
A mighty conqueror does not give battle;
A great commander is a humble man.

You may call this pacific virtue;
Or say that it is mastery of men;
Or that it is rising to the measure of God,
Or to the stature of the ancients.

- Lao Tzu (about 300BC) - The Way of Life
http://www.mountainman.com.au/tao_7_9.html
--------------------------------------------------------------------


Jul 20 '05 #4
"louis nguyen" <lo************ @hotmail.com> wrote in message
news:b0******** *************** **@posting.goog le.com...
All development work at the application software level may thereby
be conducted within SQL, by the development of TSQL stored
procedures and their coordination across an organization.
Hi,

I already write 80% of my code in stored procedures.


This is often documented as an exceedingly efficient practice
and for good reason due to the efficiency of the RDBMS software
layer dealing directly with the RDBMS software layer rather than
any other external software environment (layer).

One might easily make the hypothesis that there is a migration of
code from the environment external to the database, to the code
resident internal to the database. That is, from an historical and
developmental perspective.

This thread concerns the hypothetical end-point of such a migration
wherein all code relevant to the application is internal --- ie: stored
procedures.
I also use
xp_cmdshell inside SPs for FTP and other DOS commands. So I'm all for
the idea of encapsulating all development work inside TSQL stored
procedures. With the coming of Yukon and the ability to embed C# and
VB.net code directly in SPs, I would probably write even more of my
code inside stored procedures.
I will await the implementation to comment on this aspect.

The one area where I think code
belongs on the client, involves data-entry applications. Here I want
to instantaneously respond to user clicks & inputs. I don't want to
make a round-trip to the server every time the user clicks - so data
validation and instant totals need to remain on the client.

Data entry and its validation processes ---- the "user interface" --- is
indeed the traditional bastion of "client side code" due to its historical
development in that environment.

However, we both know that every single user interface screen of code
connected to an application represents a redundancy of definitions which
have already been categorically defined within SQL.

An application is consistent of a suite of say 999 user-interface programs
which connect in different manners to a great range of the data in an
extensive and comprehensive database.

There are 999 sets of redundant database definitions requiring attentive
and costly coordinated review possibly every new build. This is alot of
redundancy.
My point here is simply pointing out the (known) redundancies existent
in the current technological development associated with rdbms and
asking what would it be like operating under a methodology of zero
redundancies, where all the code is TSQL, with the exception of
some user-interface rdbms portal software which only deals in
allowing the user to effectively select stored procedures to be run,
or returning to the user the data set output of their execution.
Ramblings. I have a lot invested in the DB backend and web client
paradigm. The web services and windows app paradigm is not something
I really want to explore. The MSSQLSERVER2000 reporting services
looks promising, but I'm somewhat wary as my users are happy and
comfortable going to our current web sites. If the reporting services
gives me the capability to develop something that looks like a web
site, with logins, bread crumbs, hyperlinks, etc then I'll pursue it.
The coming of Asp.net 2.0 is also promising. The new sqldatasource
and gridview tags will save me a lot of coding and allow me to
concentrate on business logic, instead of how to make ado.net work
with asp.net.

Without beating around the bush, it is really the huge efficiencies
and rich arrays of controls made available in the modern rdbms
to effectively and automatically manage data that make the modern
database environment a relative haven of well-greased bearings.

Outside of this environment there is great thrashing about of software
development driven a great deal by the marketing teams. Change
is piecemeally managed or extra change management utility software
applications are employed to track change.

The management of change in the IT environment for a few decades
has driven me to seek a theoretical solution to the enormous problems
associated with coordinating change external and internal to the
database.

My conclusion is all this change is being made far more difficult to
manage due to the fact that the code (ie: the quanta of business
intelligence) is in fact being formally defined across two system
software environments. (ie: one in the client (VB) code,
and once again in the database, repeatedly throughout the
entire extent of the code)

If the base of operations can be reduced to one environment
(ie: the database) then what is now known as the client application
environment will fall away from future technology like the booster
rocket in an outbound shuttle rocket.
I guess my major wish is for M$ to evolve asp.net into
a meta language for all web client functionality. If they do that,
I'll gladly brain dump html, style sheets, javascript ... and
concentrate on Tsql, asp.net and C#.

From my perspective, if M$ bundled some form of SQL Server
portal software which enabled all levels of database related ppl
to write applications in stored procedures, then there exists the
outlandish possibility that every single diverse form of application
that can be written for a database could in theory be run on the
one machine using the same user interface to many SQL databases.

It could unify the application software layer by subsuming it in
entirety within the database software layer. This is about the
theory of the benefits of a model and operations with a smaller
number of moving parts.

At any rate, I appreciate your response and was interested
to read your approach to this, and your plans.

Compliments of the season.


Pete Brown
Falls Creek
OZ
--------------------------------------------------------------------
BoomerangOutPos t: Mountain Man Graphics, Newport Beach, {OZ}
Thematic Threading: Publications of Peace and Of Great Souls
Webulous Coordinates: http://www.mountainman.com.au

QuoteForTheDay:

"Nothing is rich
But the inexhaustible wealth of nature.
She shows us only surfaces,
But she is a million fathoms deep"

- Ralph Waldo Emerson
--------------------------------------------------------------------


Jul 20 '05 #5
mountain man (hobbit@souther n_seaweed.com.o p) writes:
However, we both know that every single user interface screen of code
connected to an application represents a redundancy of definitions which
have already been categorically defined within SQL.

An application is consistent of a suite of say 999 user-interface programs
which connect in different manners to a great range of the data in an
extensive and comprehensive database.

There are 999 sets of redundant database definitions requiring attentive
and costly coordinated review possibly every new build. This is alot of
redundancy.


Writing applications is not about composing beautiful structures with
minimal redudancy, but to create applications that meets business needs,
and a standard business need is that the application provides compre-
hensible error message in case of user errors.

There are just too many programmers out there who fail to understand this.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6
"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
mountain man (hobbit@souther n_seaweed.com.o p) writes:
However, we both know that every single user interface screen of code
connected to an application represents a redundancy of definitions which
have already been categorically defined within SQL.

An application is consistent of a suite of say 999 user-interface programs which connect in different manners to a great range of the data in an
extensive and comprehensive database.

There are 999 sets of redundant database definitions requiring attentive
and costly coordinated review possibly every new build. This is alot of
redundancy.
Writing applications is not about composing beautiful structures with
minimal redudancy, but to create applications that meets business needs,
and a standard business need is that the application provides compre-
hensible error message in case of user errors.


I agree with your second claim, but cannot understand why you
cannot also institute both claims simultaneously. That is, granted
that the business application mandates integrity constraints, and
sometimes at various levels, if these are catered for then one is
bound to concientiously consider redundancies.

The minimisation of redundancies is imo a task relegated to the management
and coordination of the business software development cycles,
perhaps at the level of the IT manager of that business.

It might certainly not be a task for some appointed programmer,
but the existence of these redundancies - you must admit (?)---
represents an expense to the funding business that is directly
proportional to the number of redundancies.

There are just too many programmers out there who fail to understand this.

My aim is to engineer a tool by which any manner of application software
components can be quickly and easily created by TSQL programmers
using stored procedures alone.

Your earlier points concerning data validation are a key issue when first
examining this methodology I am proposing, and I will address them in
more depth elsewhere.

Thanks,


Pete Brown
Falls Creek
Oz

Jul 20 '05 #7
Hi Loius,

Here is a separate response on two separate issues central
to this thread (thanks for the dialogue) ....
"louis nguyen" <lo************ @hotmail.com> wrote in message
news:b0******** *************** **@posting.goog le.com...
All development work at the application software level may thereby
be conducted within SQL, by the development of TSQL stored
procedures and their coordination across an organization.
Hi,

I already write 80% of my code in stored procedures. I also use
xp_cmdshell inside SPs for FTP and other DOS commands. So I'm all for
the idea of encapsulating all development work inside TSQL stored
procedures. With the coming of Yukon and the ability to embed C# and
VB.net code directly in SPs, I would probably write even more of my
code inside stored procedures.

My object is to write 100% of code in stored procedures.
If this is achieved, then all client-side code will be obviated
as will its change-management costs --- which are considerable
to any given organisation in todays world.

The headaches associated will change management will not
be removed, however its domain will be moved from where
it is at the moment - in two software layers (rdbms & apps),
to one unified software layer (rdbms). This is simpler.
The one area where I think code
belongs on the client, involves data-entry applications. Here I want
to instantaneously respond to user clicks & inputs. I don't want to
make a round-trip to the server every time the user clicks - so data
validation and instant totals need to remain on the client.

Here I would like to say to you --- what if your SQL Server network
and database were given the sufficient resources whereby the roundtrip
to the server is "quite acceptable"?

Also, is not the database the final authority on data validation and
instant totals? Consequently, how are you going to ultimately avoid
database IO to get updated data?

Thanks again for your thoughts.


Pete Brown
Falls Creek
OZ
Jul 20 '05 #8
"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
mountain man (hobbit@souther n_seaweed.com.o p) writes:
....[trimmed]...
You see, I believe that (for example) any and every application written
in VB, C, etc, etc, etc (external to rdbms) expressly for database
software (ie: rdbms software such as SQL Server) involves a redundancy
of data definitions required ---- once within the VB and once within the
db.

Take an example: order registration. The database needs to know that
customer ID must be registred, and this is probably enforced with
a NOT NULL constraint. But the user-interface also needs to know this,
so that it give the user a comprehensible error message. It is not
sufficient to slam "Attempt to insert NULL in column...".

What if the user interface queried the database and provided this
information
from a comprehensible error message table? Or better yet gave the option
to enable workflow screens for immediate customer registration.

Are you talking about saving database IO in reading the vendor table
and an error table?
Thanks for the dialogue.


Pete Brown
Falls Creek
Oz

Jul 20 '05 #9
mountain man (hobbit@souther n_seaweed.com.o p) writes:
What if the user interface queried the database and provided this
information from a comprehensible error message table?
Configuring such a table is a major task, and not even a wise move. Just
because the database complains about NOT NULL violation, does not mean
that the user forgot to input the information. In stead the problem was
a bug somewhere in the food chain that mutilated a variable.
Or better yet gave the option to enable workflow screens for immediate
customer registration.
You mean wizards? Bang! Here, I just laid 150 new customers that you
have to register. It should be done by lunch. I promise you, you don't
want a wizard. You want a form that permits you to work fast, and which
informs you if you miss something.
Are you talking about saving database IO in reading the vendor table
and an error table?


I'm talking about user experience.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
7629
by: Atlas | last post by:
I've developed years ago a small business application (about ten tables, basic relationships, 10 forms, 3 reports a few queries) with Borland Paradox 7, most of it developed with wizards and little ObjectPAL coding. Old'n'crappy. Seriuosly thinking rewriting it from scratch using MS SQL server 2000 as the DB platform. Low on time.
2
1229
by: Daniel | last post by:
Here's the situation. We have a very tight development schedule and the application must be done using Visual Studio .Net. It is a multi-tiered database application It has VB.Net DAL (Data Access Layer) and VB.Net BLL (Business Logic Layer) with ASP.Net Web Forms as the front end. The problem is, this is our first .Net project and we...
2
555
by: Shantanu Bhattacharya | last post by:
Hi, I have a 2-tier application that allows the end user to create a form containing controls of the user's choice. The same user can then populate the database by entering data created using the form he created. I have to convert this 2-tier application to a 3-tier application. Since the form is created on the fly, all the information...
50
2787
by: Jay Balapa | last post by:
Hello, Currently my application has three tiers- 1. Presentation Layer (Asp.Net / Win Forms/ Pocket PC UI.) This predominantly contains User Controls, Custom Controls and Win/Web Forms. I have one base form and I inherit from that form. All validation is done in this tier. There is zero Data Access code in this tier. This tier...
1
3510
by: dennis | last post by:
hello, I am using Oracle Stored Procedures to return and modify data in my application which is developed using ASP. NET(VB.NET). How are the SP's invoked from the application 1) Are they directly called from the application like sql = "{ CALL PROCEDURE_NAME(?,{resultset 0, io_cursor} ) }" OR 2) Can we have a generic class (VB/C#) which has...
0
1342
by: Mathieu Cartoixa | last post by:
Hi, I have a simple 2-tiers (client+database) application with simple Domain Model objects The Data Access Layer is abstracted via Data Mappers which use Data Transfer Objects to communicate with the Domain Model objects. My Domain Model objects are declared this way : public class City /* Inheritance removed for brevity...*/ { private...
7
25704
by: Vincent Delporte | last post by:
Hello I'm interested in hearing reflections by seasoned web app developpers about the different ways to write PHP apps, and especially how they compare in terms of performance, whether it's the PHP part or connections to MySQL. As far as I know, there are different ways to write a PHP application: - CGI, ie. the usual way : some PHP code...
8
1227
by: James | last post by:
Say I have a project that I have hosted locally. I have a separate machine which is our production environment. To do our deploys, we've been copying/pasting the folder from our local machines and overwriting the production folder(s) after backing them up. This works ok. Both folders have the same name. Now, say we're weeks into...
7
10371
by: j4richard | last post by:
Help please, I am getting this "Unhandled Exception has occurred in your application" " A Generic error occurred in GDI+" See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box. ************** Exception Text **************...
0
7520
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main...
0
7446
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7718
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7956
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
5088
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3480
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1936
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1058
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
763
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.