johan777
asked on
Any tips or examples on using SQL Server stored procedures from within Delphi?
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 :-)
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 :-)
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
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
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
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
ASKER
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
>> 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
ASKER
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
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
>> - 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