Managing database operations using ADO and C++, Part 1: Introduction to SQL

by Patrick Mancier

For this article we will be using some very simple SQL tables and procedures. This is a brief encapsulation of SQL and more advanced concepts are beyond the scope of this article. Transact SQL (T-SQL) will be used in the syntax of the procedures to keep the operation as generic as possible. For a complete reference to T-SQL, see the MSDN library.

In describing the T-SQL scripting the format used is not the standard one used in the T-SQL documentation. For the purposes of this article it is much more compact and direct to use in our examples. The syntax we are using here does not take into account any constraints or foreign key relationships, for the purposes of this article we will not need to document the syntax. For ease of creating these tables and procedures it is recommended to do this within a management piece of software such as Microsoft Management Studio for example.

Tables

To create a table in SQL, the following syntax is used:

CREATE TABLE <schema>.<table name> (
   <columnname identity value> INT IDENTITY(seed, increment) NOT NULL,
   <columnname 1> <type> [size] NULL,
   <columnname 2> <type> [size] NULL,
   .
   .
   <columnname n> <type> [size] NULL)

The identity(seed,increment) statement creates an auto-increment record ID field. This column is incremented every time a new record is added to the table. In a database schema design it is vital that each record in a table is unique in some way. Using this auto-increment column is the easiest way to ensure this. A more advanced way would be to use some key that encompasses two or more columns that make the record unique.

In a table, there must be a column name, a column type, and the size in bytes of the column if applicable. This list is only a small sample of possible datatypes:

INT: 32 bit number
DATETIME: Date combined with time of day and seconds, based on a 24 hr clock
VARCHAR: A variable string that is accompanied by a size. This has certain size limitations and is dependent on the MS-SQL server that is being used. This type of variable does need to have the size in bytes specified.

Example of a scripted table:

CREATE TABLE [dbo].[People](
        [idRecord] [int] IDENTITY(1,1) NOT NULL,
        [Name] [varchar](50) NULL,
        [Age] [int] NULL
) ON [PRIMARY]

Stored Procedures

To create a stored procedure in SQL the following syntax is used:

CREATE <schema>.<procedure name> 
      [Parameter1 name] [type] [size] [ = default value],
      [Parameter2 name] [type] [size] ] [ = default value],
        .
        .
      [Parameter(n) name] [type] [size] ] [ = default value]
AS
BEGIN
[DECLARE <local variable name> <type> <size>]
        <procedure logic>
END

Parameters

In many stored procedures parameters are used which are similar to the parameters used in a C++ function. They have a type, size and a variable name.

The parameter list contains a set of variables that are passed into the procedure from the caller. The format of this list is very similar to the table definition in that it contains a name, variable type and size in bytes if applicable. However, each variable must be proceeded by an "at" symbol (@). For MS-SQL an @ symbol precedes all variable names whether they are parameters or local variables.

Return parameters

In a stored procedure there is an option for what is called a return parameter. The return parameter is a specialized procedure parameter and is set upon exit of the procedure. You can set ADO up to allow the retrieval of this return parameter upon executing a stored procedure. In the stored procedure simply put the desired return parameter after the "return" keyword and at the application level read this value from the returned status after execution of the procedure.

Example of a stored procedure:

CREATE PROCEDURE dbo.People_Select 
     @idRecord int = 0
AS
BEGIN
        SET NOCOUNT ON;

        if(@idRecord>0)
        begin
                SELECT PersonName, PersonAge from People where idRecord = @idRecord; 
                return;
        end
        else
                SELECT PersonName, PersonAge from People; 
END

Basic SQL operations

The following descriptions of SQL syntax are not meant to be comprehensive; rather they are being described as basic operations. These statements can get rather advanced depending on the needs of the database schema but for the purposes of designing our ADO management class they will be kept simple.

Delete Syntax

DELETE <table name> [WHERE [column = condition] [AND, OR] [column = condition] ]

Insert Syntax

INSERT INTO <table name> VALUES (@value1,@value2, . @valuen)

Select Syntax

SELECT [*, column names] FROM <table name> [WHERE [column = condition] [AND, OR] [column = condition] ]

Update Syntax

UPDATE <table name> SET 
[Column1] = @value1,
[Column2] = @value 2,
.
.
.
[Columnn] = @value n
[WHERE [column = condition]];

Here are some examples of using these operations. Please refer to the previous table example for the names of the tables and columns in this example.

DELETE People where idRecord = @idRecord;
INSERT INTO People VALUES (.John Smith.,23);
SELECT * FROM People where PersonAge = 23;
UPDATE People SET PersonAge = 40 WHERE idRecord = @idRecord;

Naming convention for database class manager design

In order to look forward and design our ADO management class correctly it would be a good thing to consider a standard naming convention for the tables and stored procedures in the database. Generally the design will require that all operations on the database take place in a stored procedure. However, it is rather tedious to remember every single procedure name for every type of operation and to manually configure the ADO manager class to call all these different procedures.

It can generally be stated that the operations of DELETE, INSERT, SELECT and UPDATE are considered to be the four basic operations that take place on tables in a database schema. Therefore it makes sense to modularize these operations as procedures.

Given a table name of TestTable, the four basic procedures against it could be written as

TestTable_Delete
TestTable_Insert
TestTable_Select
TestTable_Update

With this naming scheme in place it will be very easy to design the ADO manager class to operate against this and all other tables in the database schema.

Database Schema

This is a very simple database schema as an example to illustrate the concepts of managing database operations with C++. We will be using this later when we build our database ADO manager class.

Table: People

Column Name	Type	Size (bytes)	Description
idRecord	int	4	Identity column of the table, auto increments as new records are added
PersonName	varchar	50	Full Name of the person
PersonAge	int	4	Age of the person

List of procedures:
People_Delete
People_Insert
People_Select
People_Update

The database installation script that creates the above database schema is included in this article. In viewing the script notice that in the People_Insert procedure, a term @@IDENTITY is used as a return parameter. This can be useful to return the last known identity of a table after an insert is performed in order to do further processing on the record that was just inserted. It can also be used to reference this record such as for a node on a tree list control for example.

The script was generated from MS-SQL management studio from an existing database. It will wipe out any existing database called ADOTest and replace it with a new one. If there are any connections or "locks" to the database when this script is generated, i.e. you have a window open in the management studio or an ADO connection to the database, the script will fail because it will be unable to drop the existing database until all connections are severed.

Continue to part 2 of the tutorial
Back to the ADO tutorial index