AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1015&pId=-1
Working with DBISAM Using Microsoft .NET
page
by Bhuban Mohan Mishra
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 48893/ 126

Overview

DBISAM is a word that may seem new for many of us and many would be astonished to know that it is a database.  Like any other database, it has the ability to maintain huge amounts of data.  This article aims at providing an overview of DBISAM and the ways we can interact with the database through .NET.  Let us first discuss about some of the aspects of DBISAM.

About DBISAM

DBISAM is an embedded database engine which is available for programming languages that can use ODBC for data access.  DBISAM can be used as a single-user, multi-user or client-server engine.

General Architecture

DBISAM is session based where each session is equivalent to a virtual user.  In a given application there can be many active sessions.  The sessions are of 2 types.

·         Local Session: A local session can directly access the database tables via Windows or Linux APIs to the local storage medium.

·         Remote Session: A remote session uses sockets to communicate to a database server over a network using TCP/IP protocol.

The main drawback of DBISAM is that it does not support Referential Integrity.

Databases

DBISAM uses the physical directories in Operating System’s file system to represent databases. The tables in DBISAM are represented by three physical files in the database.

·         .dat (Data Files) which are the actual tables in the Database that store records.

·         .idx (Index Files) which store index definitions and pages.

·         .blb (BLOB Files) that store BLOB blocks related to tables.

Data Types

The data types used in DBISAM are also supported by other databases or relational databases, but with some exceptions.  A comparison between the data types of DBISAM and SQL Server is given below.

Listing 1

Data Type

Description

SQL Server Equivalent

String

Fixed length fields that can store up to 512 characters. The trailing blank spaces are automatically trimmed from any strings entered into string fields.

VARCHAR

FixedChar

Same as string fields with the exception that trailing blank spaces are not automatically removed from any strings entered into them.

CHAR or

CHARACTER

GUID

Same as string fields with the exception that they are fixed at 38 bytes in length and are always used to store the string representation of a GUID value.

UNIQUEIDENTIFIER

Bytes

Fixed in length and can store up to 512 bytes in a single field.

BYTES

Blob

Variable in length and may contain up to 2 gigabytes of data.

NTEXT or IMAGE

Memo

Variable in length and may contain up to 2 gigabytes of data minus a NULL terminator.

TEXT or LONGVARCHAR

Graphic

Variable in length and may contain up to 2 gigabytes of data.

IMAGE

Date

Date fields contain dates only.

DATETIME

Time

Time fields contain times only.

DATETIME

TimeStamp

Contain both a date and a time.

TIMESTAMP

Boolean

Contain logical True/False values.

BIT

SmallInt

Contain 16-bit, signed integers.

SMALLINT

Word

Contain 16-bit, unsigned integers.

 

Integer

Contain 32-bit, signed integers.

INTEGER or INT

AutoInc

Contain 32-bit, signed integers.

INT with Identity

LargeInt

Contain 64-bit, signed integers.

BIGINT

Float

Contain 64-bit floating-point numbers (doubles).

FLOAT

Currency

Currency fields are the same as Float fields.

MONEY

BCD

BCD fields contain a 34-byte TBcd type.

NUMERIC

 
Working with DBISAM

After knowing a bit about database structure, let us put our hands on the programming part with relation to .NET.  As every database has accepted SQL as the standard language for querying, so too has DBISAM.  We do not need to worry about syntax; our same old concept on SQL will work fine here.  Now to connect to the DBISAM Database from our application, we need the DBISAM ODBC driver.  It is an ODBC level 3 driver.  The driver works with Microsoft Data Access Components (MDAC) version 2.7 or higher and many other applications including .NET Applications.

Missing Features

There are still a few things missing from the driver like support for bulk operations and a few ODBC extended scalar functions (UNION, INTERSECT, LIKE, etc).

Using ODBC Driver

To use the ODBC Driver we can either setup a DSN which we access from our application or can directly access the database through a connection string from our application.

C# Code Snippets

Depending on our application need and location of the database (local or remote), the connections may vary.  It can be either done through a DSN or we can use a connection string to connect to the database.

Connection String

For direct connection strings the Keywords play a major role and are case sensitive.  The Connection String Keywords available in DBISAM ODBC Driver are as follows:

·         DRIVER: It specifies the ODBC driver name used.

·         ConnectionType: Depends on the connection, i.e. Local or Remote.

·         CatalogName: Specifies the name of the database.

These were the required Keywords for both Local and Remote Connections.  The following keywords are required only for Remote connections.

·         UID: Specifies the User ID for the remote connection.

·         PWD: Specifies the password for the remote connection.

·         RemoteHostName: Specifies the Host name of the remote Database server.

·         RemoteIPAddress: Specifies the IP address of the remote Database Server.

From the RemoteHostName or RemoteIPAddress above, any one is used.

Examples

The different connection types that can be used are listed below.

1.      Connection through a preconfigured DSN

Listing 2

OdbcConnection objODBCCon = new OdbcConnection(“DSN=?”);
//? : Your preconfigured DSN

2.      Connection to the Database present on a local system through a connection string.

Listing 3

string txtConStr = “DRIVER={DBISAM 4 ODBC Driver}”;
txtConStr += “ConnectionType=Local;CatalogName=path”;
//path : The complete path to the folder, where the DBISAM Tables 
//(i.e. *.dat files) are present.
OdbcConnection objODBCCon = new OdbcConnection(txtConStr);

3.      Connection to the Database present on a remote system through a connection string.

Listing 4

string txtConStr = “DRIVER={DBISAM 4 ODBC Driver}”;
txtConStr += “ConnectionType=Remote;CatalogName=employee;”;
//employee : The database present on the Remote Server.
txtConStr += “UID=userid;PWD=password;”;
txtConStr += “RemoteIPAddress=xxx.xxx.xxx.xxx”;
//xxx.xxx.xxx.xxx : The IP Address of the Remote Server.
OdbcConnection objODBCCon = new OdbcConnection(txtConStr);

After establishing this connection we can access the database and the tables through a DataSet or DataReader as we usually do in any other ODBC Connections.  The following code shows a way to get all the Table names from a specified folder (dbisamFolder) and display it in a list box (lstAvailable).

Listing 5

string table;
objODBCCon.Open();
string[] files;
// Gets all the DBISAM Tables from the user Specified folder
files = Directory.GetFiles(dbisamFolder.ToString(),"*.dat");
// Extracts the DBISAM table names from the .dat files
// and excludes the tables.
for(int Count = 0; Count < files.Length; Count++)
{
table = files[Count].Remove(files[Count].LastIndexOf("."), 4);
table = table.Substring(table.LastIndexOf("\\"+ 1);
lstAvailable.Items.Add(table);
// lstAvailable is a ListBox
}

Now the table names can be used to capture the table into a DataSet and do the required operations.

Listing 6

table = lstAvailable.Items[0].ToString();
OdbcDataAdapter objDA;
DataSet objDS = new DataSet("DbiSam");
objODBCCon.Open();
objDA = new OdbcDataAdapter("SELECT * FROM " + table, objODBCCon);
objDA.Fill(objDS);
References
Conclusion

There are many other ways of dealing with the DBISAM Tables and far more ways to work with DBISAM Database itself, starting from Database Locking to Multithreaded applications with a blend of Local and Remote Sessions.  It supports a wide range of Development Platforms, Programming Languages and Reporting Tools.  This is a small effort to make you familiar with this Database.



©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-03-09 6:24:27 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search