Solved

Any tips or examples on using SQL Server stored procedures from within Delphi?

Posted on 2004-08-27
9
733 Views
Last Modified: 2010-04-05
Hi all,

I have read and was told by the SQL Server fundies that it is preferrable to use stored procedures to manage (insert, update, delete and select) information in SQL Server.

Aparently this is faster than standard queries and also is more robust if one decides to make the front-end application a web application.

 - I would like to know how to go about achieving this.
 - Can I still use DB editor controls?
 - What would such a front-end look like?
 - Will I need to have a TADOStoredProc (or similar) component for each of the stored procedures to insert, update, delete, select one record and select all records?
 - Will I still be able to use a DBNavigator?

I was thinking of a screen split in two with the upper part a grid (read only if it must be) and the lower part a panel with all the relevant columns as DB Editor components and a DBNavigator so that I can insert, update and delete using the panel and have the grid automatically maintained.

I got the feeling that this is not the suggested way of handling database access from within Delphi as it seems to me that I will have to write quite a bit of code to accomplish this.

Any comments are welcome and examples even more!

I have allocated the maximum points to this question with the idea of splitting it among all the best answers.

Regards,
Johan Swart

PS I've got a feeling that I might have opened a can of worms here :-)

0
Comment
Question by:johan777
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 6

Expert Comment

by:bpana
ID: 11911943
>> - I would like to know how to go about achieving this.
>> - Can I still use DB editor controls?
Yes

>> - What would such a front-end look like?
use TADOConnection, TADOStoredProcedure (maybe TADOTable also)

>> - Will I need to have a TADOStoredProc (or similar) component for each of the stored procedures to insert, update, delete, select one record and select all records?
It depends how you will implement this. For example if you set up a StoredProcedure which will return a result set (if the result set is not too complicated), the component will work just as a TADOTable component. So you can use Data Controls to affect the database tables directly.

If you will work with a disconnected recordset, it's up to you to inmplement the stored procedures for data update (insert, delete)

>> - Will I still be able to use a DBNavigator?
Yes

Bogdan
0
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 11914319
using stored procedure is definetely better than using queries from your program, they give you a lot of flexibility and allow you to change many things without having to touch your program's source code

you can do everything you can do using queries or tables, the only "problem" is that there is a bit more of coding involved

However if your database is small (say... 100,000 records or less) you shouldn't have any problems using Tables or Queries, you are encouraged to use Stored Procedure whenever possible, because of the flexibility they provide, but if is a simple and small database and simple program logic you could use queries and tables and that way would be easier
0
 
LVL 1

Expert Comment

by:TomBig
ID: 11914586
Johan

consider this. If you're using SQL Server stored procedures you will be stuck with SQL Server for the rest of your applications lifecycle.
Using SQL Server as an application server by implementing buisness logic on the database seems not to be a good idea to me.
Databases should store data and should be exchangeable in flexible design.

ADO sure is a good technology to consider, because (in theory, but in most cases even in practice) it abstracts the database, and there is a stable and reasonable OLEDB provider (it comes with MDAC 2.7).

Hence: Use something like ADO. Don't use Stored procedures.

cheers TomBIg
0
 

Author Comment

by:johan777
ID: 11916739
Hi all,

Thanx for all the responses so far.

[Bogdan] Could you please expand a bit on your comments, maybe set up an example. Are you saying I must return a result set, even when inserting, updating or deleting? Will it make sense to create one stored proc that takes an 'action' parameter to indicate what to do? I still don't get it, how will I base a grid as well as some dbcontrols all on one (or more) stored procs?

[BlackTigerX] What is the accepted way to implement stored procs in Delphi? What code are you refering to?

[TomBig] Just as I thought! The battle begins :-)      For-Stored-Procs = 2 vs Against-Stored-Procs = 1      How do you usually implement database access and what your preferred access technology.

[My penny] I used to prefer the direct method, but can clearly understand the advantages of using stored procedures. I have seen an implementation in .NET where a stored proc was created for each action to do insert, update, delete, select one rec, select all records and one to bring back one rec together with a denormalized set of all data in tables for which a foreign key was part of the table referred to. As you can imagine this caused quite a bit of stored procs to be created and maintained.

Since this is all new to me, I would like to make sure that I approach the use of stored procs in Delphi 'correctly' from the start.

Please send more comments.

Regards,
Johan Swart
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Expert Comment

by:BlackTigerX
ID: 11916833
but having that many procedure (one for each operation) gives you a lot of flexibility, in many ways...

imagine you want to update another table when you update the current table, with a SP add 2 lines to the SP and you're done

same when deleting, etc, say now you only want to delete on certain condition, change your SP, done...

this works even better when you have your program in multiple computers, then you don't have to update all of them for a single change, all the changes are in the server

is structured programming... you should try to modularize your programs as much as possible, and stored procedures give you that

implementing business logic on the database should be something you always want to do, as opposed to what TomBig said, there are way more benefits than drawbacks
0
 
LVL 6

Accepted Solution

by:
bpana earned 500 total points
ID: 11918598
>> I was thinking of a screen split in two with the upper part a grid (read only if it must be) and the lower part a panel with
>> all the relevant columns as DB Editor components and a DBNavigator so that I can insert, update and delete using the
>> panel and have the grid automatically maintained.

Suppose you use TADOConnection, TStoredProcedure (and TADOTable, TDataSource connected to TADOTable and some Data Controls)
Basically you call a stored procedure which will return a Recordset. You set the TADOTable.Recordset to point to this Recordset. You disconnect it (that means that all changes to the recordset will not propagate to the database)

Now, to handle the updates for the recordset, write the BeforeInsert, BeforePost, BeforeDelete events for the TADOTable.
You will have to create a stored procedure for each event.

An example follows (I just made an example of retriving and updating data) and I have used just one TADOStoredProcedure component.

1. Table Contact_Test

CREATE TABLE [Contact_Test] (
      [ContactId] [int] IDENTITY (1, 1) NOT NULL ,
      [FirstName] [varchar] (15) NOT NULL ,
      [LastName] [varchar] (15) NOT NULL ,
      [Age] [int] NOT NULL ,
      [DateUpdated] [smalldatetime] NOT NULL CONSTRAINT [DF_Contact_Test_DateUpdated] DEFAULT (getdate())
) ON [PRIMARY]
GO

2. Two stored procedures

CREATE PROCEDURE [prGetContacts]
AS
select ContactId, FirstName, LastName, Age
from Contact_Test
GO

CREATE PROCEDURE [prUpdateContact]
  @ContactId int,
  @FirstName varchar(50),
  @LastName varchar(50),
  @Age int
AS
if @ContactId is NULL
  Return 1
update Contact_Test
set
  FirstName = @FirstName,
  LastNAme = @LastName,
  Age = @Age,
  DateUpdated = getdate()
where ContactId = @ContactId
Return 0
GO

The unit source code:

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ADODB, ExtCtrls, DBCtrls, StdCtrls, Mask, DB, Grids, DBGrids;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    ADOTable1: TADOTable;
    Label1: TLabel;
    DBEdit1: TDBEdit;
    Label2: TLabel;
    DBEdit2: TDBEdit;
    Label3: TLabel;
    DBEdit3: TDBEdit;
    DBNavigator1: TDBNavigator;
    ADOStoredProc1: TADOStoredProc;
    procedure FormShow(Sender: TObject);
    procedure ADOTable1BeforePost(DataSet: TDataSet);
  private
    { Private declarations }
    procedure GetContacts;
    procedure UpdateContact(AContactID: integer; AFirstName, ALastName: string; AAge: integer);
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormShow(Sender: TObject);
begin
  GetContacts;
end;

procedure TForm1.GetContacts;
begin
  if ADOStoredProc1.Active then
    ADOStoredProc1.Close;

  ADOStoredProc1.Connection := ADOConnection1;

  ADOStoredProc1.Parameters.Clear;
  ADOStoredProc1.ProcedureName := 'prGetContacts';
  ADOStoredProc1.Open;
  ADOTable1.Recordset := ADOStoredProc1.Recordset;
  ADOTable1.Connection := nil;
  ADOStoredProc1.Connection := nil;
end;

procedure TForm1.ADOTable1BeforePost(DataSet: TDataSet);
begin
  try
    UpdateContact(
      DataSet.FieldByName('ContactId').AsInteger,
      DataSet.FieldByName('FirstName').AsString,
      DataSet.FieldByName('LastName').AsString,
      DataSet.FieldByName('Age').AsInteger
      );
  except
    Raise;
    Abort;
  end;
end;

procedure TForm1.UpdateContact(AContactID: integer; AFirstName, ALastName: string; AAge: integer);
var
  ReturnValue: integer;
begin
  if ADOStoredProc1.Active then
    ADOStoredProc1.Close;

  ADOStoredProc1.Connection := ADOConnection1;

  ADOStoredProc1.ProcedureName := 'prUpdateContact';
  ADOStoredProc1.Parameters.Clear;
  ADOStoredProc1.Parameters.Refresh;
  ADOStoredProc1.Parameters.ParamByName('@ContactID').Value := AContactID;
  ADOStoredProc1.Parameters.ParamByName('@FirstName').Value := AFirstName;
  ADOStoredProc1.Parameters.ParamByName('@LastName').Value := ALastName;
  ADOStoredProc1.Parameters.ParamByName('@Age').Value := AAge;
  ADOStoredProc1.ExecProc;

  // if Error
  if ADOStoredProc1.Parameters[0].Value = 1 then
    Raise Exception.Create('Unable to Update data !');
end;

end.

Hope it helps,
Bogdan
0
 

Author Comment

by:johan777
ID: 11920438
Hi Bogdan,

Thanx for your answer! I'm going to allocate 250 points to you so far! This is the type of answer I was looking for.

I would still like to know if there is an industry accepted way of going about. Since it seems to be the more correct way of implementing, why is there no articles on it, or even just some examples?

What is the 'normal' set of stored procs that you would write per table, or even business entity? I'm expecting an insert, update and delete stored proc per record, but I'm interested in the select stored proc(s). Do you usually write one select stored proc or more than one?

Lastly, do you always use the primary key as parameter for the single select stored proc?

Once again, Bogdan for being so helpfull so far. I appreciate it very much.

Regards,
Johan
0
 
LVL 6

Expert Comment

by:bpana
ID: 11923895
Hi Johan,

>> I would still like to know if there is an industry accepted way of going about.
I didn't heart about, but I don't think so.

>> Since it seems to be the more correct way of implementing, why is there no articles on it, or even just some examples?
I wouldn't say it is more correct. Using stored procedures you can have some advantages (Performance by caching the execution plan, modularity, code reuse, security ...) but you will spend more time for the implementation.
I suppose that the reason there are no articles on it is due to the fact that you cannot give a general example. It is more a subject of defining the business rules (which are specific to each project).

>> What is the 'normal' set of stored procs that you would write per table, or even business entity? I'm expecting an insert,
>> update and delete stored proc per record, but I'm interested in the select stored proc(s). Do you usually write one select
>> stored proc or more than one?
As I said before, I cannot give a 'normal' or a 'general' example.
Normally the stored procedures are created when needed. You cannot first build up the database with all the stored procs, and after that to build the project (you may find that you may create additional stored procs ...)
You may create as many stored procs for select as you need. For example you may want to retrieve just a few fields from a table and for performance reasons you will create another stored proc.

>> Lastly, do you always use the primary key as parameter for the single select stored proc?
Yes, this is the primary key's role.

Hope this helps,
Bogdan
0
 

Author Comment

by:johan777
ID: 11924817
Hi Bogdan,

Thanx again for all the help! i will abide by your answers.

I have been working in SQL Server and Delphi for quite a while, but this is the danger of teaching yourself without any external help or references.

Since discovering Expert Exchange I feel like I have found a mentor :-)

Regards,
Johan Swart
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now