[AccessD] Going CRUD way...

Hello Arthur,

Thank you for your prompt feedback.

May I say you're a "generated CRUDs fun"? (I'm getting like that here too
therefore this "generated CRUDs fun" is a respectful nickname here :) ...)

Do you have any samples of small CRUDs, which you use *in production*, which
therefore proved themselves as stable and which you can publish here? - just
one for every type of CRUD (Insert, Update, Delete SQL) + Select (by Id)
SQL...

Thank you.


I must say that views used with WinForms (.NET 2.0), even when parameters
are used but when views are run against large amounts of back-end data -
such views are (very) slow comparing to parameterized stored procedures.

I do use views but only as a "middle-/abstraction tier" between SPs and base
tables...

--
Shamil
 
P.S. Here is a sample of Update SP I use made based on what OlyMars
originally did generate - writing that stuff manually is a "no-go"
obviously:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[a_Table_Update]') and OBJECTPROPERTY(id, N'IsProcedure')
= 1)
drop procedure [dbo].[a_Table_Update]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure [a_Table_Update]

-- Update an existing record in [a_Table] table

(
  
     @rowId [int] = Null  -- for [a_Table].[ID] column
    , @ConsiderNull_iD bit = 0
    , at bitField [bit] = Null  -- for [a_Table].[bitField] column
    , @ConsiderNull_bitField bit = 0
    , at intField [int] = Null  -- for [a_Table].[intField] column
    , @ConsiderNull_intField bit = 0
    , at decimalField [decimal] = Null  -- for [a_Table].[decimalField] column
    , @ConsiderNull_decimalField bit = 0
    , at numericField [numeric] = Null  -- for [a_Table].[numericField] column
    , @ConsiderNull_numericField bit = 0
    , at floatField [float] = Null  -- for [a_Table].[floatField] column
    , @ConsiderNull_floatField bit = 0
    , at smallMoneyField [smallmoney] = Null  -- for
[a_Table].[smallMoneyField] column
    , @ConsiderNull_smallMoneyField bit = 0
    , at charField [char](10) = Null  -- for [a_Table].[charField] column
    , @ConsiderNull_charField bit = 0
    , at varcharField [varchar](50) = Null  -- for [a_Table].[varcharField]
column
    , @ConsiderNull_varcharField bit = 0
    , at textField [text] = Null  -- for [a_Table].[textField] column
    , @ConsiderNull_textField bit = 0
    , at nvarCharField [nvarchar](50) = Null  -- for [a_Table].[nvarCharField]
column
    , @ConsiderNull_nvarCharField bit = 0
    , at ntextField [ntext] = Null  -- for [a_Table].[ntextField] column
    , @ConsiderNull_ntextField bit = 0
    , at dateTimeField [datetime] = Null  -- for [a_Table].[dateTimeField]
column
    , @ConsiderNull_dateTimeField bit = 0
    , at uniqueidentifierField [uniqueidentifier] = Null  -- for
[a_Table].[uniqueidentifierField] column
    , @ConsiderNull_uniqueidentifierField bit = 0
    , at imageField [image] = Null  -- for [a_Table].[imageField] column
    , @ConsiderNull_imageField bit = 0 
)

As

Set NoCount On

Declare @retValue int

    If @ConsiderNull_iD Is Null Set @ConsiderNull_iD = 0
    If @ConsiderNull_bitField Is Null Set @ConsiderNull_bitField = 0
    If @ConsiderNull_intField Is Null Set @ConsiderNull_intField = 0
    If @ConsiderNull_decimalField Is Null Set @ConsiderNull_decimalField = 0
    If @ConsiderNull_numericField Is Null Set @ConsiderNull_numericField = 0
    If @ConsiderNull_floatField Is Null Set @ConsiderNull_floatField = 0
    If @ConsiderNull_smallMoneyField Is Null Set
@ConsiderNull_smallMoneyField = 0
    If @ConsiderNull_charField Is Null Set @ConsiderNull_charField = 0
    If @ConsiderNull_varcharField Is Null Set @ConsiderNull_varcharField = 0
    If @ConsiderNull_textField Is Null Set @ConsiderNull_textField = 0
    If @ConsiderNull_nvarCharField Is Null Set @ConsiderNull_nvarCharField =
0
    If @ConsiderNull_ntextField Is Null Set @ConsiderNull_ntextField = 0
    If @ConsiderNull_dateTimeField Is Null Set @ConsiderNull_dateTimeField =
0
    If @ConsiderNull_uniqueidentifierField Is Null Set
@ConsiderNull_uniqueidentifierField = 0
    If @ConsiderNull_imageField Is Null Set @ConsiderNull_imageField = 0

Update [dbo].[a_Table]

Set
     [rowId] = Case @ConsiderNull_iD When 0 Then IsNull(@rowId, [rowId])
When 1 Then @rowId End
    , [bitField] = Case @ConsiderNull_bitField When 0 Then IsNull(@bitField,
[bitField]) When 1 Then @bitField End
    , [intField] = Case @ConsiderNull_intField When 0 Then IsNull(@intField,
[intField]) When 1 Then @intField End
    , [decimalField] = Case @ConsiderNull_decimalField When 0 Then
IsNull(@decimalField, [decimalField]) When 1 Then @decimalField End
    , [numericField] = Case @ConsiderNull_numericField When 0 Then
IsNull(@numericField, [numericField]) When 1 Then @numericField End
    , [floatField] = Case @ConsiderNull_floatField When 0 Then
IsNull(@floatField, [floatField]) When 1 Then @floatField End
    , [smallMoneyField] = Case @ConsiderNull_smallMoneyField When 0 Then
IsNull(@smallMoneyField, [smallMoneyField]) When 1 Then @smallMoneyField End
    , [charField] = Case @ConsiderNull_charField When 0 Then
IsNull(@charField, [charField]) When 1 Then @charField End
    , [varcharField] = Case @ConsiderNull_varcharField When 0 Then
IsNull(@varcharField, [varcharField]) When 1 Then @varcharField End
    , [textField] = Case @ConsiderNull_textField When 0 Then
IsNull(@textField, [textField]) When 1 Then @textField End
    , [nvarCharField] = Case @ConsiderNull_nvarCharField When 0 Then
IsNull(@nvarCharField, [nvarCharField]) When 1 Then @nvarCharField End
    , [ntextField] = Case @ConsiderNull_ntextField When 0 Then
IsNull(@ntextField, [ntextField]) When 1 Then @ntextField End
    , [dateTimeField] = Case @ConsiderNull_dateTimeField When 0 Then
IsNull(@dateTimeField, [dateTimeField]) When 1 Then @dateTimeField End
    , [uniqueidentifierField] = Case @ConsiderNull_uniqueidentifierField
When 0 Then IsNull(@uniqueidentifierField, [uniqueidentifierField]) When 1
Then @uniqueidentifierField End
    , [imageField] = Case @ConsiderNull_imageField When 0 Then
IsNull(@imageField, [imageField]) When 1 Then @imageField End
Where
        ([rowId] = @rowId)

	select @retValue = @@ERROR

Set NoCount Off

Return(@retValue)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Monday, July 30, 2007 5:02 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Going CRUD way...

Since you wrote this to the AccessD list, I'll begin there with my response.
I'm currently doing an ADP for a riding stable (which will soon be for sale,
so if any listers have any friends with riding stables... LOL). The general
approach in it is forms bound to views. (Nobody but me gets to talk directly
to tables.) Some of the views use table-UDFs to simplify joins. All of the
combo-boxes and list-boxes use named queries (views) to retrieve their
contents. This app is simple, so there's not much need for complicated
sprocs, except here and there. These fall mainly in the Reports realm; a
dialog box opens, requests parameters such as "Horse", Start Date and End
Date, then invokes the report which invokes the sproc and passes the
parameters, so you end up with a cross-tab report showing the particular
horse's activities ( group lesson, private lesson, sem-private lesson,
injury day, etc.) during the date range.

In the larger scheme of things, I use ERwin. Its code-gen capabilities are
totally wonderful. It has a template language built-in which will generate
your CRUD code automatically, and even give you a choice between returning a
rowset and a set out OUTPUT parameters. I hadn't realized the benefit of the
latter strategy until I worked on a large project with my friend Dejan
Sunderic (who wrote a great book about SQL 2005). That type of sproc is
useful only when you want exactly one record back, but if you're searching
millions of rows, it's demonstrably faster than returning a rowset. You
don't even need a timer to note the difference.

In Access, there are significant advantages to using views as the data
source, IMO, not the least of which is how easily subforms behave. Access
does the dirty work for you. You just create a subform based on a view,
plonk it onto a master form, and Access handles the plumbing. It couldn't be
easier, and in addition you insulate the actual tables. Suppose that your
app contains a form that only selected people (let's call them Managers)
ought to see. So in SQL you create a Managers role and grant access to the
view(s) in question. Then even if you forget to program around it in your
Access app, it's ok -- no one but managers will be able to run that report.
The message they will receive isn't elegant, but the data is safe.

I'm not an ERwin expert but I have worked with one or two. At one point, I
asked my friend and colleague Andrei Pascal whether we could customize the
template to place what ERwin calls a description into the Extended
Properties code. It took Andrei about 5 minutes to modify the template so it
did this. That's two "hats off" -- one to the template language and one to
Andrei. The template language is pretty much beyond my feeble intellect, but
Andrei just whipped out a tiny little loop that walked every table and added
an extended property to every table for every column that had a Description,
and poof! All done.

I used to hate ERwin and I much preferred PowerDesigner and Dezign (whose
interface is pretty much a clone of PowerDesigner, although it lacks lots of
the PD power). I was dragged kicking and screaming into using ERwin, but
have since grown into an enthusiast, not least because generating CRUD and
even customized CRUD is a one-click operation.

Arthur

On 7/30/07, Shamil Salakhetdinov <shamil at users.mns.ru> wrote:
>
> Hi All,
>
> It looks like we haven't yet have here CRUD vs. (mainly) dynamic manually
> written SQL vs. metadata-driven application (frameworks) development
> debate?
> Or did I miss it?
>
> Anyway my question is what do you prefer to use when developing
> applications
> against MS SQL backend:?
>
> - 1) CRUD SPs based approach to work with base tables + custom SPs(views,
> UDFs,...) to implement custom functionality - and SPs only "visible to
> outer
> world"?
>
> - 2) dynamic SQL - DAO, ADO, ADO.NET (mainly) manually written etc. with
> or
> without SPs (views, UDFs,...)?
>
> - 3) metadata driven (flexible) dynamic SQL approach?
>
> - 4) you do not use not the first not the second not the third approach -
> you do use a "mixture" of them IOW you just write code to implement custom
> functionality and whatever approach to use in every certain case you
> usually
> decide as you go...
>
> - 5) something else (please add other useful approached I missed to
> mention
> here)...
>
> Thank you.
>
>
> --
> Shamil
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com