Databases Access Objects Model with ConceptDraw PRO

ConceptDraw Basic gives the opportunity of interaction with any ODBC-compatible databases. For this the Database Access Objects Model is provided. All calls to the database are made by certain methods of objects of this model.

This model has a strict hierarchical structure. There is the object of the upper level in it named DBEngine. All the other objects are included in its collection. Collections arrange instances of one-type objects. For example, any instance of Workspace objects is included in Workspace collection. Each collection belongs to some other object of the hierarchy upper level. Each object, except DBEngine, has a corresponding collection.

All collections have the same methods which allow to obtain an object by its name or index in the collection, add, delete and count the objects in this collection. Organization in collection, even though adding numerous objects allows to uniformly add, delete and count objects without remembering names of specific methods for objects of each type.

Note. Objects in the collections of this Database access objects model are numerated from zero unlike the objects of the ConceptDraw Access Objects model.

To start work with the databases, you should create an instance of DBEngine object. This is the object of the upper level in CDBasic-model access to databases. It operates the driver for database access (odbc32.dll is used for Windows by default) and allows to reach other objects necessary for work with databases.

An instance of this object is created by means of the key-word new.

Dim eng as DBEngine
set eng=new DBEngine

Note. DBEngine is one of the few (to be more exact of the three) ConceptDraw objects, an instance of which can be created manually by means of the operator new, and not obtain using other objects.

Work with DB runs within workspaces, which manage connections with databases and transactions. A Workspace object corresponds to such a workspace. An instance of this object is stored in Workspaces collection of DBEngine object. At the beginning of the work this collection is empty. That is why a new Workspace instance should be created by calling Create Workspace() method of DBEngine object. This method allows to specify the workspace name as well as login and password for access to the database.

Dim eng as DBEngine, ws as Workspace
set eng=new DBEngine
set ws=eng.CreateWorkspace("name","login","password")

Within one workspace it is possible to open several connections and work with them within one transaction - a set of logically connected actions with a database, which transforms the database from one consistent state into another. For work with transactions at workspace level Workspace object has the following methods:

BeginTrans() - Opens transaction. All the following actions will be included in this transaction.

CommitTrans() - Confirms transaction. Applies all changes in the database, which occurred from the moment of call BeginTrans().

RollbackTrans() - Rolls back transaction. All changes in the database, from the moment of call BeginTrans() will be ignored.

For connection with DB either of the two objects can be used: Database or Connection. Database object is a model of open database and it provides access to collections which represent structural elements of the database: tables (collection TableDefs), relations between them (collection Relations), and stored procedures (collection QueryDefs)... Stored procedures and direct SQL queries can be fulfilled using the objects of collection Recordsets of the same Database object. This object also allows to manage transactions.

Connection object is a reduced variant of Database object. It allows to make queries to a database, including the stored procedures. It also allows to manage transactions, though not letting manage the database structure directly. In the language of objects it means that Connection object contains collections QueryDefs and Recordsets, but not collections TableDefs and Relations.

As at queries to the database Connection object does not have to refresh collections TableDefs and Relations, a query called at Connection object, is being responded quicker. That's why, if you do not work with the database structure, being only interested in the target information contained there, it is recommended to use this object. Basically, it is possible to change and obtain information on the structure with the help of SQL-queries without using Database object at all. But using Database makes stress on the structure also eliminating differences among SQL dialects and makes the code more portable (at least, while it is going about client-server ODBC-drivers).

Depending on whether Database or Connection has been chosen for connection to the database, this connection can be found in Databases or Connections collection of the Workspace object. Workspace allows to create and add to its own collections new Database and Connection instances by means of OpenDatabase() and OpenConnection() methods accordingly.

Dim eng as DBEngine, ws as Workspace, db as Database

set eng=new DBEngine
set ws=eng.CreateWorkspace("University","admin","")
set db=ws.OpenDatabase("University",TRUE,FALSE,"DSN=University_DSN")

or

Dim eng as DBEngine, ws as Workspace, cn as Connection

set eng=new DBEngine
set ws=eng.CreateWorkspace("University","admin","")
set cn=ws.OpenDatabase("University",TRUE,FALSE,"DSN=University_DSN")

At the end of the work the connection and the workspace should be closed by Close() method of Connection or Database object.

After connection has been opened it can be used for making SQL-queries to the database or for work with its structure. To make a query OpenRecordset() method is used. The direct text of the SQL-query / the table name / the name of the stored procedure is given as a parameter. The method returns the Recordset object instance - the object which provides methods of scanning and modification of the resulting data.

Dim cn as Connection, rs As Recordset
... 
set rs=cn.OpenRecordset("SELECT ExpResult FROM ExperimentData")

The same method OpenRecordset() allows to specify the cursor type which defines sensitivity of the resulting Recordset to the database changes, that may occur after responding the query. The cursor type is specified by the second parameter.

OpenRecordset() method is used for any query to the database. In case, it is going about selection queries, it is the data that is of interest. Recordset object allows for access to them.

Recordset object instance contains collection Fields of the given selection. Field is an object, containing name, value, and necessity of an atomic element of the selection. The type and the value are chosen by using one of the properties AsBoolean, AsDouble, AsLong and AsString that return the current field value in according representation. In table representation, field names correspond to the data columns headlines. Selection, as a rule, does not represent just one row (record). But every moment of time one certain row is active and it is this (line) row values that can be extracted from Field object instances. To move between the rows Recordset methods such as MoveNext(), MovePrevious(), MoveFirst(), MoveLast() or Move() are used. The ability of the cursor to move by this or that method depends on cursor type that is passed via OpenRecordset() method, and level of support of the given functionality by the driver and the database. To find out whether the cursor supports the given method, CanMove() method of the same Recordset object can be used.

Dim cn as Connection, rs As Recordset, fld as Field
Dim sSQL as String

... 
sSQL = "SELECT ExpResult FROM ExperimentData"
set rs=cn.OpenRecordset(sSQL)
i=1
Do
set fld = rs.Fields.GetByName("ExpResult")
trace fld.AsDouble
i=i+1 
Loop while rs.MoveNext
rs.Close()
cn.Close()

At the end of the work with Recordset its method Close() is called, which closes the cursor and frees the memory allocated for records.

Except direct SQL-query strings, in method OpenRecordset() it is possible to pass queries stored in the database as stored procedures. CDBasic supports stored procedures in Transact-SQL format, used in MS SQL Server. CDBasic also allows to obtain and change information about the procedures stored in the database, and also create new ones.

For work with the stored procedures QueryDef object exists. Collections of the already existing in the database stored procedures QueryDefs are stored in Database and Connection. To create a new procedure method CreateQueryDef() is called with the name of the procedure and the query string as the parameters.

There is also a possibility of creating parameter procedures. For this QueryDef object contains collection Parameters. At the beginning, when creating a new QueryDef instance this collection is empty. To create a new parameter for the stored procedure you need to create a Parameter object instance by calling CreateParameter() method of QueryDef object, customize its properties by specifying its name, type, value and direction (input, input and output, output). The type and the value as well as for Field object are specified by AsBoolean, AsLong, AsDouble, AsString properties. After all the properties have been configured the Parameter instance is joined to a corresponding collection by Append() method.

Having finished configuring the QueryDef instance, if necessary, i.e. having joined all the parameters to the collection, the QueryDef itself is joined to the collection of the open database (Connection or Database object). For appending method Append() of QueryDefs collection is used.

dim cn as Connection, qd as QueryDef, pr as Parameter

...

set qd=cn.CreateQueryDef("new_qd","Select * From Letter WHERE Subject = @sbj")

set pr=qd.CreateParameter("sbj", 1, cddbText)
qd.Parameters.Append(pr)

cn.QueryDefs.Append(qd)

Now to call the query saved in the procedure "new_qd" passing its name to OpenRecordset() method is just enough.

Now let's move to the database structure. Presentations of the database tables are stored in TableDefs collections. Thus, for instance, it's easy to scan the database to find out what tables it contains.

dim eng as DBEngine, ws as Workspace, db as Database, tdf as TableDef

set eng=new DBEngine
set ws=eng.CreateWorkspace("Workspace 1","login","password")
set db=ws.OpenDatabase("Database 1",0,0,"DSN=MyDSN")

for i=0 to db.TableDefs.Count-1
set tdf = db.TableDefs.GetByNumber(i)
trace tdf.Name
next

TableDef object represents information about the database fields and table indexes. The first ones are represented in Fields collection (Field object and Fields collection have already been described, when it was going about receiving information from the selection, presented by a Recordset object), the latter in Indexes collection.

Besides, TableDef object has the OpenRecordset() method, by means of which you can obtain a selection of all the table contents. It calls with out parameters.

To add a new table to the database you need to:

  • create a TableDef object instance
  • fill in its Fields collection
  • join the instance itself to TableDefs collection of the Database.

A TableDef instance is created by the Database method. CreateTableDef(), in which the name of the future table is passed. Then, you create the table fields (new instances of Field object). Each of them should be added to the Fields collection table. And then, the already prepared TableDef is added to the TableDefs collection of the Database. It is after it has been added to the collection the new table appears in the active database.

Dim db as Database, tbl as TableDef, fld as Field
...
set tbl = db.CreateTableDef("TableName")

set fld=tbl.CreateField("Name1", cddbByte)
tbl.Fields.Append(fld)
set fld=tbl.CreateField("Name2", cddbText, 5)
tbl.Fields.Append(fld)

db.TableDefs.Append(tbl)

After the table has physically appeared in the database, indexes can be added to it: the primary key, unique indexes, and the foreign keys. The primary or a unique key can be added directly to Indexes collections of the TableDef. You can do it according to the scheme of adding a table.

  • Index object instance is created
  • For the index necessary fields are created and then are added to Fields collection
  • Index type is specified (primary or unique)
  • Prepared Index object instance is added to Indexes collection.

Index fields are selected from the already existing in the table. CreateField() method of Index object considers only the field name.

Dim tbl as TableDef, ind as Index, fld as Field
...
set ind=tbl.CreateIndex("PR_1")
' the field Student_Id should exist in the table
set fld=ind.CreateField("Student_Id")
ind.Fields.Append(fld)
ind.Primary=True
tbl.Indexes.Append(ind)

Foreign keys are displayed in Indexes collection of the corresponding TableDef object, but creating them by the CreateIndex() method is impossible. Because it is not about just a peculiarity of a single table, but about links between two different tables, there is a special object for their operation in CDBasic: Relation. This object represents relations between the tables by the following:


Relation object contains three properties: Name, Table and ForeignTable. Name property identifies an object instance in the collection. Table property contains the table with the primary key name. ForeignTable contains the name of the table with the foreign key, based on this primary key. Besides, there is Fields collection, which contains the fields defining this relation (there is just one such field illustrated here - Field3, but the index can be based on a larger quantity of fields). This field name coincides with the field name in the first table that presents the primary key. ForeignName property - the name of the field, connected to the Name field by a relation coincides with the name of the field from the according table, and SourceTable - with the analogous property of the first table.

For adding a relation and the outer index accordingly, you need to:

  • Create Relation object by CreateRelation() method. At this you need to specify the name of the new relation, the name of the table with the primary key, and the name of the table in which it is referred to.
    Dim rl as Relation
    ... 
    set rl=db.CreateRelation("FR_1","Table","ForeignTable")
    
  • Fill in its Fields collection, i.e. specify which of the table fields make up the primary index, by specifying for each of them the name of the corresponding field in the second table.
    set fld=rl.CreateField("FieldFromPrimaryKey")
    fld.ForeignName="FieldFromForeignKey"
    rl.Fields.Append(fld)
    
  • And then add the ready relation to the collection
    db.Relations.Append(rl)

The given model represents one more type of the database characteristics. It is going about characteristics that can be connected to any object: link, table, stored procedure, relation, index, or field: Any object (but not collection!) has Properties collection. A voluntary number of characteristics (Property object) can be added to it in the most common format: you can give a name, type, and value for such a characteristic. This is the very mechanism, which is used by the database for storing characteristics of its objects. Thus, relation characteristics (Name, Table, ForeignTable) are enumerated in this collection. It can be obtained from CDBasic with the help of Properties collection. Apart from standard characteristics you may need some other ones. Say, for instance, you need to store the relation along with the date of its creation. The following peculiarity is of interest then. Creating a characteristic, you can require it to be inherited by all the objects from its collection. I.e. if you set the appropriate flag when creating a characteristic for the date of creating a relation, then all its fields will get such a characteristic.

To create a new characteristic it's enough to call CreateProperty() method of the voluntary object and give him a name by parameters, value and hereditability feature. And then add the appeared object to Properties collection.

Dim rl as Relation, pr as Property
...
set pr=rl.CreateProperty("CreationDate", Date(), TRUE)
rl.Properties.Append(pr)

Database scheme example

TEN RELATED HOW TO's:

Process Flowchart

When trying to figure out the nature of the problems occurring within a project, there are many ways to develop such understanding. One of the most common ways to document processes for further improvement is to draw a process flowchart, which depicts the activities of the process arranged in sequential order — this is business process management. ConceptDraw PRO is business process mapping software with impressive range of productivity features for business process management and classic project management. This business process management software is helpful for many purposes from different payment processes, or manufacturing processes to chemical processes. Business process mapping flowcharts helps clarify the actual workflow of different people engaged in the same process. This samples were made with ConceptDraw PRO — business process mapping software for flowcharting and used as classic visio alternative because its briefly named "visio for mac" and for windows, this sort of software named the business process management tools. This flowchart diagram shows a process flow of project management. The diagram that is presented here depicts the project life cycle that is basic for the most of project management methods. Breaking a project into phases allows to track it in the proper manner. Through separation on phases, the total workflow of a project is divided into some foreseeable components, thus making it easier to follow the project status. A project life cycle commonly includes: initiation, definition, design, development and implementation phases. Distinguished method to show parallel and interdependent processes, as well as project life cycle relationships. A flowchart diagram is often used as visual guide to project. For instance, it used by marketing project management software for visualizing stages of marketing activities or as project management workflow tools. Created with ConceptDraw PRO — business process mapping software which is flowcharting visio alternative or shortly its visio for mac, this sort of software platform often named the business process management tools.business process flowcharts, flowchart symbols, process flow diagram, workflow diagram, flowchart maker
Picture: Process Flowchart
Related Solution:

UML Object Diagram. Design Elements

UML Object Diagram shows the structure of a modeled system at a specific time. ConceptDraw has 393 vector stencils in the 13 libraries that helps you to start using software for designing your own UML Diagrams. You can use the appropriate stencils of UML notation from UML Object library.UML Object Diagram: Design Elements
Picture: UML Object Diagram. Design Elements
Related Solution:

Sales Dashboard - Access Anywhere

ConceptDraw Sales Dashboard is an automatically updated sales dashboard you can customize to see KPI you need. You can access sales dashboard anywhere to get fresh data and use any minute to see how your business can improve.Sales Dashboard - Design Elements icons software diagraming tools mac
Picture: Sales Dashboard - Access Anywhere

IDEF0 Visio

ConceptDraw PRO extended with IDEF0 Diagrams solution from the Software Development area of ConceptDraw Solution Park is a powerful diagramming and vector drawing IDEF0 software. All IDEF0 diagrams created in ConceptDraw PRO are vector graphic documents and can be reviewed, modified and converted to MS Visio XML format. To obtain the IDEF0 Visio documents from ConceptDraw PRO documents use the wide export possibilities of ConceptDraw PRO.IDEF0 Visio
Picture: IDEF0 Visio
Related Solution:

Entity Relationship Diagram - ERD - Software for Design Crows Foot ER Diagrams

There are many ways to describe a database structure. One of the most usual is to draw an entity relationship diagram (ERD) using a Crow’s Foot notation to represent database elements. If you don’t want to draw it on paper, you should use an appropriate software. An entity-relationship (ER) diagram is used to show the structure of a business database. ERD represents data as objects (entities) that are connected with standard relationships symbols which Illustrate an association between entities. ERD, there is a wide range of ERD notations used by data bases architects for reflecting the relationships between the data entities. According to the crow’s foot notation relationships are drawn as single labeled lines designating a certain kinds of relationship. Crow foot notation is a most frequently used ERD standard, because of improved readability of diagrams, with a more accurate use of space on the page.Entity Relationship Diagram Software, Design Elements - Crows Foot (Windows, Macintosh)
Picture: Entity Relationship Diagram - ERD - Software for Design Crows Foot ER Diagrams
Related Solution:

Basic Flowchart Symbols and Meaning

While creating flowcharts and process flow diagrams, you should use special objects to define different statements, so anyone aware of flowcharts can get your scheme right. There is a short and an extended list of basic flowchart symbols and their meaning. Basic flowchart symbols include terminator objects, rectangles for describing steps of a process, diamonds representing appearing conditions and questions and parallelograms to show incoming data. This diagram gives a general review of the standard symbols that are used when creating flowcharts and process flow diagrams. The practice of using a set of standard flowchart symbols was admitted in order to make flowcharts and other process flow diagrams created by any person properly understandable by other people. The flowchart symbols depict different kinds of actions and phases in a process. The sequence of the actions, and the relationships between them are shown by special lines and arrows. There are a large number of flowchart symbols. Which of them can be used in the particular diagram depends on its type. For instance, some symbols used in data flow diagrams usually are not used in the process flowcharts. Business process system use exactly these flowchart symbols.Flowchart Symbols and Meaning, workflow diagram symbols, process flow diagram symbols
Picture: Basic Flowchart Symbols and Meaning
Related Solution:

Workflow Flowchart Symbols

The Workflow diagrams are the specialized type of flowcharts which allow to visualize any sorts of step-by-step processes. They are usually created as flowcharts, with objects that represent in a simple visual manner the steps in the process connected by arrows that indicate the next step. The ability easily create Workflow diagrams visually describing the tasks, documents associated with the tasks, people which are responsible for execution at each stage of workflow is made possible with Workflow Diagrams solution. This solution extends ConceptDraw PRO with a Workflow Diagram library that contains large collection of predesigned workflow flowchart symbols.Workflow Diagram Symbols
Picture: Workflow Flowchart Symbols
Related Solution:

Pyramid Diagram

When thinking about the process of decision making, it's easy to imagine it as a pyramid. We will look at a three level pyramid model as an example. Operational decisions are first that come to mind, and it's the first level. Tactical and strategic decisions are the second and the third levels. Pyramid (triangle) diagrams are used to show some hierarchical data. Because to the pyramid form of diagram, each diagram slice has a different width. The width of each segment depends on its hierarchical level. The current image illustrates the model of information system types. Most graphically the model can be represented as a three level pyramid. The first level shows a system of transaction processing It is concentrated on operational tasks. The second level is devoted to tactical solutions of the information system management. And at least, the third level addresses strategy and represents the executive information system. This three level pyramid sample is supplied with the ConceptDraw Pyramid Diagrams solution from Marketing area of ConceptDraw Solution Park.Pyramid Diagrams. 3 level pyramid model of information systems types
Picture: Pyramid Diagram
Related Solutions:

UML Diagram

While developing software, it is very important to have a visual model, because it helps to represent the logic and the architecture of an application. Experienced engineers use UML diagrams to denote relationships between classes and their instances. UML is a general language for a set of diagrams like deployment diagrams, object diagrams or use case diagrams. This diagram represents UML class diagram used for a software system development using an object-oriented method. Class diagrams are categorized as static structure diagrams that depict the physical structure of a system. Class diagram divides a software system's structure into "classes". Classes are defined by the methods and variables of objects. UML Class diagram is used to depict relationships and source code dependencies between objects.UML Diagrams
Picture: UML Diagram
Related Solution:
Related How To's
1 Diagram Software
Professional business graphics tool for drawing schemes, diagrams and illustrating business documentation
1 Mind Map Software
Excellent tool for Mind Mapping, Planning, Brainstorming, and Building Processes
1 Project Management Software
The full complement of features needed to successfully plan and execute projects, including task and resource management, reporting, and change control