Easier Data Access

• Download component - 3.34
• Download source code - 2.08

Introduction

The presented classes can be considered as a layer on top of the core Data Access classes. The usage leads to simplicity of design as well as reduction in the amount of code in executing DML queries.

Background

In most business applications which are developed for small organizations, the amount of code is taken by lines of code which are used to access the data in data stores. The problems come out when these code segments are needed to be repeated regularly. As all of us know very well, object oriented systems give us the solution by putting those code segments in classes and using objects of those classes. All I have done is that, I put those classes in a single DLL package and use in all other application developments also.

Using the code

This DLL file consists of two classes, one is called DataProvider whose objects are used to manipulate the data between application and the data store, and the other class is DataProviderException which is used to provide right type of errors in case of any error.

How does it work?

The DataProvider class consists of protected data members used to encapsulate data source information as well as four type of methods used to insert, delete, select and update data. All these four types of method have several overloaded methods used to provide alternatives.

Constructors

public DataProvider() {}

public DataProvider(String ConnectionString) {}
 
public DataProvider(String ConnectionString, String table) {}
 
DataProvider dp = new DataProvider (
  "@"Data Source=INTERFACEMIRROR;Integrated Security=SSPI;Packet Size=4096;
   Initial Catalog=Northwind;Provider='SQLOLEDB.1';
   Workstation ID=INTERFACEMIRROR"");

dp.Table="Customer";

The main point that has to be noted down is that, before using objects of this class, two issues has to be solved. The first issue is connection string has to be provided through constructor or public property ConnectionString. The second issue is that the table name has to be assigned in a similar fashion.

Public Methods

public void ExecuteSingleRowInsert (String [] values){}

As the sample example, I perform all operation on the customer table of NorthWind data source.

String [] values = 
    {"CustomerID='JDBC2'"  ,"CompanyName='JANDOST'", 
    "ContactName='Jandost'" ,"ContactTitle='Mr'", "Address='******'",
    "City='karachi'", "Region='SOUTH ASIA'","PostalCode='75290'",
    "Country='Pakistan'","Phone='3653358'","Fax='02122510'"};

or in general fashion:

String [] values = {" Column = Value"};

If value is String, it has to be within ‘ ’.

dp.ExecuteSingleRowInsert (values);
  
public void ExecuteMultipleRowsInsert (String [][] vals){}

This method is used to insert more than one row in table at once. For this purpose, all you have to do is just create a two dimensional array, as the above example, in which a row holds a single record and a column holds different records to be inserted.

public void ExecuteDelete (){}

This method will delete all data within the table. For deleting records, you have to use its overloaded method:

public void ExecuteDelete (String cond){}

like

dp.ExecuteDelete ("CustomerId=’JDBC2’");

or

dp.ExecuteDelete ("CustomerId=’JDBC2’ AND Fax=’210254’");

public void ExecuteUpdate (string[] colums ,string condition){}

String [] vals = {"ColumnName = New Value","","", …};

E.g.

String [] vals = {"fax=66666","phoneno=22222"};
dp.ExecuteUpdate (vals , "CustomerId=’JDBC2’");

public ArrayList ExecuteSelect (string [] fields){}
public ArrayList ExecuteSelect (string [] fields, String cond){}

This method can be used in three different ways:

String [] fields = {"*"}; for all Rows Selection

String [] fields = {"Column Name1","Columns Name2"…};

For few columns selection,

ArrayList l =  dp.ExecuteSelect ( fields );
ArrayList l = dp.ExecuteSelect ( fields , "CustomerId=’JDBC2’");

are two verities of this method call.

Note that, the return is an ArrayList which wraps every row of selected table as an object array in itself. For example, suppose your query returns five tuples of the table, each tuple with four fields. The values can be obtained by:

ArrayList al = dp.ExecuteSelect (vals);

Object [] o = null ;
for ( int i = 0 ; i < al.Count ; ++i )
{
   o = (Object []) al [i] ;
   Console.WriteLine (o[0] + o[1] + o[2] + o[3]);
}