Michael Maddison
michael at ddisolutions.com.au
Tue Jul 31 01:33:46 CDT 2007
Hi Guys,
Heres a sample of the CRUD generated by NetTiers an a simple lookup
table.
I've used a couple of times now.
Of course it also generates all the C# objects to go with it.
IMO for complex systems CRUD makes good sense.
CREATE PROCEDURE dbo.Agency_GetPaged
(
@WhereClause varchar (2000) ,
@OrderBy varchar (2000) ,
@PageIndex int ,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
-- Set the page bounds
SET @PageLowerBound = @PageSize *
@PageIndex
SET @PageUpperBound = @PageLowerBound +
@PageSize
-- Create a temp table to store the
select results
Create Table #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT
NULL,
[AgencyID] int
)
-- Insert into the temp table
declare @SQL as nvarchar(4000)
SET @SQL = 'INSERT INTO #PageIndex
(AgencyID)'
SET @SQL = @SQL + ' SELECT'
IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' TOP ' +
convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' [AgencyID]'
SET @SQL = @SQL + ' FROM dbo.[Agency]'
IF LEN(@WhereClause) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' +
@WhereClause
END
IF LEN(@OrderBy) > 0
BEGIN
SET @SQL = @SQL + ' ORDER BY ' +
@OrderBy
END
-- Populate the temp table
exec sp_executesql @SQL
-- Return paged results
SELECT O.[AgencyID], O.[AgencyName],
O.[AgencyAbbr]
FROM
dbo.[Agency] O,
#PageIndex PageIndex
WHERE
PageIndex.IndexID > @PageLowerBound
AND O.[AgencyID] =
PageIndex.[AgencyID]
ORDER BY
PageIndex.IndexID
-- get row count
SET @SQL = 'SELECT COUNT(*) as
TotalRowCount'
SET @SQL = @SQL + ' FROM dbo.[Agency]'
IF LEN(@WhereClause) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' +
@WhereClause
END
exec sp_executesql @SQL
END
GO
CREATE PROCEDURE dbo.Agency_Delete
(
@AgencyID int
)
AS
DELETE FROM dbo.[Agency] WITH (ROWLOCK) WHERE [AgencyID] = @AgencyID
CREATE PROCEDURE dbo.Agency_Find
(
@SearchUsingOR bit = null ,
@AgencyID int = null ,
@AgencyName nvarchar (50) = null ,
@AgencyAbbr nvarchar (5) = null
)
AS
IF ISNULL(@SearchUsingOR, 0) <> 1
BEGIN
SELECT
[AgencyID]
, [AgencyName]
, [AgencyAbbr]
FROM
dbo.[Agency]
WHERE
([AgencyID] = @AgencyID OR @AgencyID is null)
AND ([AgencyName] = @AgencyName OR @AgencyName is null)
AND ([AgencyAbbr] = @AgencyAbbr OR @AgencyAbbr is null)
END
ELSE
BEGIN
SELECT
[AgencyID]
, [AgencyName]
, [AgencyAbbr]
FROM
dbo.[Agency]
WHERE
([AgencyID] = @AgencyID AND @AgencyID is not null)
OR ([AgencyName] = @AgencyName AND @AgencyName is not null)
OR ([AgencyAbbr] = @AgencyAbbr AND @AgencyAbbr is not null)
Select @@ROWCOUNT
END
GO
CREATE PROCEDURE dbo.Agency_Get_List
AS SELECT [AgencyID], [AgencyName], [AgencyAbbr] FROM
dbo.[Agency]
Select @@ROWCOUNT
GO
CREATE PROCEDURE dbo.Agency_GetByAgencyID
(
@AgencyID int
)
AS
SELECT [AgencyID], [AgencyName], [AgencyAbbr] FROM dbo.[Agency]
WHERE [AgencyID] = @AgencyID
Select @@ROWCOUNT
GO
CREATE PROCEDURE dbo.Agency_Insert
(
@AgencyID int OUTPUT,
@AgencyName nvarchar (50) ,
@AgencyAbbr nvarchar (5)
)
AS
INSERT INTO dbo.[Agency]
(
[AgencyName]
,[AgencyAbbr]
)
VALUES
(
@AgencyName
, at AgencyAbbr
)
-- Get the identity value
SET @AgencyID = SCOPE_IDENTITY()
GO
CREATE PROCEDURE dbo.Agency_Update
(
@AgencyID int ,
@AgencyName nvarchar (50) ,
@AgencyAbbr nvarchar (5)
)
AS
-- Modify the updatable columns
UPDATE
dbo.[Agency]
SET
[AgencyName] = @AgencyName
,[AgencyAbbr] = @AgencyAbbr
WHERE
[AgencyID] = @AgencyID
GO
cheers
Michael M
To: 'Access Developers discussion and problem solving'
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com