REIMERS CONSULT Company Logo

REIMERS CONSULT GmbH

Home Feedback Contents

RC.Sql Tutorial

HOME
Up

RC.Sql Download

RC.Sql Product Logo medium

Database Access Library


Contents


Introduction
This tutorial is a short introduction into the usage of the C#-library RC.Sql. In the first step some common tasks are mentioned. After that a short example explains, how an application can be built using RC.Sql. The example is written in C#, too. It is a program with console output, that deletes the current contents of a table, inserts two new rows and finally prints the new contents to the console. The source code can be found here. Code-snippets used in this tutorial are shown in the font Courier New with grey background.

to top


Requirements

to top


Common Issues to RC.Sql
RC.Sql is a class library for database access using the .NET framework. The central class is RcsqlDatabase. It creates all statement objects. Implementations of this class contain all necessary information about specific databases (MS Access, Sybase, DB2,...).
In the first step an application must create an instance of the corresponding database class (e.g. RcsqlOdbcDatabase for ODBC based data sources). After that it must be provided with logon information of a user (database name, user login, password,...). Now all desired statement objects for data manipulation can be created (Insert, Update,...) and executed.

to top


Example Application
The example application uses a table named USER contained in a Microsoft Access database. The table consists of the columns USER_RECNO, USER_ID and USER_NAME. USER_RECNO is the primary key and defined as Autokey (automatically set on insert into the database). USER_ID is defined as unique.
1) The Database Class
For all database classes the class RcsqlDatabase is the base class. Database- or driver specific implementations are derived from it. This example uses an instance of the class RcsqlOdbcDatabase.
RcsqlOdbcDatabase accessDatabase = new RcsqlOdbcDatabase();
The method CreateNewConnection creates and initializes an instance of the class RcsqlConnection. Supply of the logon string to accessDatabase enables the connection to be opened.
string connectionString = "DSN=" + dsn +
                         ";UID=" + userID +
                         ";Pwd=" + password +
                         ";Database=" + database;
...
accessDatabase.CreateNewConnection();
accessDatabase.SetConnString(connectionString);
accessDatabase.Open();
2) Create and Execute a Delete-statement
Table contents can be deleted using the class RcsqlDeleteStmt. It is created by a table object (RcsqlTable) for the table USER. (Attention: executing this statement without setting any condition will result in deleting the whole contents of the table!)
tableUser = accessDatabase.GetTable("USER");
RcsqlDeleteStmt delete = tableUser.CreateDeleteStmt();
delete.Execute();
For deleting certain rows, the Where-clause of the statement must be set with the appropriate values. The following example shows the structure of a condition in RC.Sql to delete a user from the table, if his name is XXX or his token is Z.
delete.Where = tableUser["USER_NAME"] == "Xxx"
               | tableUser["USER_ID"] == "z";
3) Create and Execute an Insert-statement
An Insert-statement is created the same way as a Delete-statement, only to use the CreateInsertStmt method. Then the columns, in which new data should be inserted, are set.
string[] colNames = new string[]{"USER_USERID" "USER_NAME"};
RcsqlInsertStmt insert = tableUser.CreateInsertStmt(colNames);
The index properties for the corresponding columns are supported with the new values and the statement is executed
insert[colNames[0]].Set("TU");
insert[colNames[1]].Set("Test User");
insert.Execute();
4) Create and Execute a Simple Select-statement
In most cases a Select-statement references more than only one table. For this reason it is, in opposition to Delete, Insert or Update, not created by the table object but by the database object.
RcsqlSelectStmt select = accessDatabase.CreateSelectStmt();
For parameters the method AddSelect takes column objects of the columns to be read. This example reads all columns.
select.AddSelect(tableUser);
The reading is done by the method ExecReader. After successful access it returns an RcsqlReader. The reader is used to get the result sets.
RcsqlReader reader = select.ExecReader();
while ( reader.Read() ) {
for (int i = 0; i < reader.ColumnCount; i++)
  Console.Write( reader.Object + "\t");
  Console.WriteLine();
}
5) Select-statement with Conditioned Where-clause
The next example uses a database containing the tables BENUTZER, KONTRAHENT and UMSATZ. The columns USER_RECNO, KD_RECNO and UM_RECNO are primary keys to their corresponding table. They are automatically created on inserting a row into the table.
This example reads from different tables and sorts the results.
RcsqlTable tableKd = accessDatabase.GetTable("KONTRAHENT");
RcsqlTable tableUm = accessDatabase.GetTable("UMSATZ");
sel.Where = tableUser["USER_KD"] == tableKd["KD_RECNO"] &
       tableUm["UM_KD"] == tableKd["KD_RECNO"] &
       ( tableUm["UM_BETRAG"] > 1000 &
         ( RcsqlExpr.UpCase(tableKd["KD_ORT"]) == "LONDON" |
         tableKd["KD_ADDNR"].Between(1000, 2000) ) |
         !tableUm["UM_BOOK"].In(RcsqlExpr.Text("('GGC', 'XYZ')"))
       );
sel.Where &= tableUser["USER_LEVEL"] != 5;
sel.OrderBy(tableUser["USER_NAME"])
    .OrderByDescending(tableUser["USER_SALE"])
    .OrderBy(tableKd["KD_ID"]);
The following SQL-statement is automatically created by RC.Sql:
SELECT t1.* FROM BENUTZER t1, KONTRAHENT t2, UMSATZ t3 WHERE t1.USER_KD = t2.KD_RECNO AND t3.UM_KD = t2.KD_RECNO AND (t3.UM_BETRAG > 1000 AND ({fn UCASE (t2.KD_ORT)} = 'LONDON' OR t2.KD_ADDNR BETWEEN 1000 AND 2000) OR NOT (t3.UM_BOOK IN ('GGC', 'XYZ'))) AND t1.USER_LEVEL <> 5 ORDER BY t1.USER_NAME ASC, t1.USER_SALE DESC, t2.KD_ID ASC
A detailed description of the tables shown here would exceed the range of this tutorial. The example should only show the creation of complex expressions using RC.Sql.

to top


Troubleshooting

RC.Sql within GAC
To enable an application to use a class library, which doesn't belong to the standard .NET framework nor it is located in the application's directory, it must be registered in the Global Assembly Cache (GAC). For the reason, that RC.Sql is licensed in the meaning of the .NET framework, the license implementation must be present at the same location as the class library file (dll-file).
Valid License for RC.Sql
The class library RC.Sql is licensed in the context of the .NET framework. On any compilation of an application using RC.Sql the compiler takes and checks the license and integrates it into the compiled code. For running this application the license is not needed. If there is no valid license at compile time an exception is thrown. The license file (RCSql.lic) must be present at the same directory as the library file.
License Help-file within the Application Source Directory
The license help file (licenses.licx) for RC.Sql must be present within the source directory of the application. Microsoft Visual Studio .NET 2003 and corresponding Borland products use this file to identify the licensed classes within the library.
Database Connection
If the connection to the database cannot be established, this example cannot be executed. In this situation the database connection parameters should be checked.

to top