How to approach MS SQL Server database from Delphi

Posted on 2008-11-18
Last Modified: 2013-11-23

For my application I have migrated from MS Access to SQL Server. I'm currently figuring out what would be a good way to retrieve and store data from and to the database from within Delphi.
I use a ADOConnection to cennect to MS SQL Server 2005.
To retrieve data I currently use StoredProcedures.

Suppose I have a table "CUSTOMERS" that contains 100.000 records. Of course I don't want to retrieve all records from the database at once, but only a small part of it.
I know I can use "Select TOP  200", but how can I get to records 201 and up?

What is the best approach to accomplish this?
Should I use TADOQuery or TADOStoredProc to access the data?
How to deal with record locking?

Please feel free to ask any questions to clearify mine.
Question by:Delphiwizard
    LVL 17

    Expert Comment

    by:Shanmuga Sundaram
    You can use both tadoquery and tadostoredproc objects. This depends on what you want to do. Most tend to use tadostoredproc because we write stored procs for just about anything to add an extra layer of abstraction between the front end and the database engine. The reason is that it is easier to maintain that way for us. Also if you use a TAdostoredproc and then make a minor change to the stored proc, there is need to redistribute the app . With a TAdoQuery object, you should make changes to the delphi code, recompile the app and then tell everyone that there are changes made in the app and to update the exe with the latest version.Delphi's ADO objects have the Locate method which does the same thing. See the documentation for TCustomADODataSet.Locate in your help. Also please refer,M1

    for more details

    Author Comment

    Thanks for the info.
    The part that needs clarification for me is how to handle a large number of records.
    LVL 18

    Accepted Solution

    First it really helps in doing anything with large datasets to set your Database and query cursor to point at the server. This was 100,000 records are pushed to your local client.

    But to do a query of the top X reccrds then the next top X records, here is one way that works well. It acts similarly to MySQL's LIMIT 10, 1000 which will give you 1000 records starting at record 10. LIMIT X, Y.

    To start I want to pull the top 100 employees starting at 0 or the first record. I will put in an ADOQuery

    SELECT     TOP 100 *
    FROM         Employees
    WHERE     (EmployeeID NOT IN
                              (SELECT     TOP 0 EmployeeID
                                FROM          Employees
                                ORDER BY EmployeeID))

    When I want the next 100, I will change the 0 in my subquery to 100. So...

    SELECT     TOP 100 *
    FROM         Employees
    WHERE     (EmployeeID NOT IN
                              (SELECT     TOP 100 EmployeeID
                                FROM          Employees
                                ORDER BY EmployeeID))

    and etc.

    In summary MySQL's LIMIT X, Y in MSSQL :=

    SELECT     TOP Y *
    FROM         Employees
    WHERE     (EmployeeID NOT IN
                              (SELECT     TOP X  EmployeeID
                                FROM          Employees
                                ORDER BY EmployeeID))

    Hope this helps you. I did test on my table and works in ADOQuery and works best and quicker using server side cursors.


    Author Closing Comment

    Thank you very much. This helped a lot.
    LVL 36

    Expert Comment

    by:Geert Gruwez
    my 2 cents:

    there is still the ability of assigning a RANK() to a record
    and working according to this RANK()

    from EMPLOYEE E) A

    START = 1
    DELTA = 100

    you need to set columns starting with '__' invisible

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    733 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

    19 Experts available now in Live!

    Get 1:1 Help Now