RISE to Bloome Software
Log In    
r2bsoftware.se r2bsoftware.se
Click to hide navigation tree

C# for ODBC code generator

The RISE C# for ODBC code generator renders .NET C# source code for database access. The generated code implements the classes and methods corresponding to the information interfaces specified in the RISE model. This includes classes for database access and, optionally, classes implementing SOAP/JSON web services. If you are new to RISE, please read more in the RISE Resource Center and if you haven't done so yet, download RISE it's Free!

ODBC (Open DataBase Connectivity) is a standard API for accessing databases. The source code from the code generator is compliant with any DBMS having a database based on the same RISE model, i.e. it works with the output from the SQL ServerPostgreSQL and MySQL code generators.

All methods, arguments and return values in the generated code are named in accordance with the RISE model and its naming convention

RISE attribute types are mapped to C# data types according to:
RISE C# not null C# null
Identifier long/int¹ long?/int?
Int long/int¹ long?/int?
String(n) string string
Bool bool bool?
DateTime DateTime DateTime?
Float double double?
Blob byte[] byte[]
Text string string
¹ It's a model property whether to use 64- or 32-bit integers and sequences.

Web service configuration

Generated web services (SOAP/JSON) expects an ODBC connection string to be provided in the web.config file. Thus, there's no separate RISE specific configuration file introduced. This is accomplished by placing an add element in the appSettings element of web.config. The add element has two attributes: a key identifying the model and a value containing the connection string to use. 

    <add key="_PREFIX_HERE_" value="_CONNECTION_STRING_HERE_" />

If you're building a Rich Internet Application using JSON, you'd most likely want to increase the JSON string buffer length. This, too, is done by adding a section to the web.config file.

        <jsonSerialization maxJsonLength="_SIZE_IN_BYTES_HERE_"/>

ODBC configuration

The ODBC configuration and connection strings are database driver specific. See separate article on connection strings.

SQL Server No changes needed.
MySQL Expand the Details and check the "Return matched rows instead of affected rows" checkbox.
PostgreSQL Click on Options Datasource and then, on Page 1, clear the "Bools as char" checkbox and, on Page 2, check "bytea as LO". The driver requires "bytea as LO" in order for file/blob upload and download to work.
For further information on how to configure your ODBC driver, please, refer to vendor specific resources.

Code generator settings

You may change the settings prior to generating code. It's also possible to store the settings as your default settings. The default settings are automatically used when generating code inside the RISE Editor or from a command-line

The C# code generator has the following properties:
manualDefaults When set the code generator will place all column default values as part of the INSERT (New) operation. Compare with the MySQL code generator having IgnoreColumnDefaults set to TRUE.
generateWS Instructs the code generator to generate web service source code for all public interfaces and methods in the RISE model.
supportJSON Instructs the code generator to add support for JSON-style web services, see compliance below. When this option is set you need to include the pages in a project using .NET 3.5 or later. This option is only applicable when generateWS is set.
supportTicket When this option is set all web services methods have a "string ticket" argument prepended to their argument list. Read more on this topic below. This option is only applicable when generateWS is set.
userLogin When this option is set all web services methods has the "string userid, string password" arguments prepended to their argument list. Read more on this topic below. This option is only applicable when generateWS is set.

Notes on database compliance

ODBC is an API containing methods to connect to a database, execute SQL statements and retrieve possible results. It doesn't, however, address differences SQL syntax for various. The database specifics are cover in the table below.

Connection MySQL Execute extra command to assure ANSI mode.
List (select) SQL Server Statements are limited using TOP instead of the ANSI standard LIMIT, i.e. SQL Server uses
SELECT TOP 10 col FROM tab
where as MySQL and PostgreSQL uses
New (insert) MySQL By default MySQL can't execute multiple statement, thus, when inserting in MySQL the ID of the inserted row is fetched using a separate statement. PostgreSQL and SQL Server returns the ID using a single ODBC statement. 
New (id/seq) all There's no standard way to retrieve an auto-generated row identifier or, as in PostgreSQL, the last number in a sequence. For the various databases the instance ID is read using:
Postgres lastval()
¹ The SQL Server output is of type numeric(38) and is converted to the suitable type (bigint/int).

Notes on JSON compliance

The code generator creates code relying entirely on the .NET web service support. SOAP (Simple Object Access Protocol) usage is properly standardized and, thus, there are no compliance issues with respect to RISE. JSON usage, however, is a slightly different matter. You need to pay attention to following specifics introduced by the .NET script service.

Result packaging The script service puts the actual result in a member named d to which it adds an extra member, __type, containing the name of the underlying return type, e.g. the JSON result for a RISE Get method could look like:
 "myString":"just a string",
Date format Dates are returned as a strings on the format /Date(ticks)/ where ticks is the number of milliseconds since epoch (UTC). Pursuing the above example, assuming the JSON data is assigned to the object response, you'd get the actual javascript Date object using:
var t = eval('new'+ response.d.myDate.replace(/\//g,' '));
Binary data Binary data is passed as an array of integers. A column, myBlob, containing data corresponding to the string "@ABC\r\n" is passed in JSON as:
... "myBlob":[64,65,66,67,13,10] ...

Custom code extensions
RISE support definition of custom methods as part of the model. The interface of a custom method is generated by the code generator whereas the implementation is not. 

Suppose we've got a model with naming prefix MyPrefix in which we add the custom method MyMethod, with a string argument, to the interface MyInterface. For MyMethod we specify that it's implemented using MyClass in MyNamespace. The code generator will generate code assuming the method specified namespace, class and method are all available in our project and we need to provide the implementation. In this case it would look something like:

using System;
using MyPrefix.DB.MyInterface; // needed for return values
namespace MyNamespace
  public class MyClass
    public returnMyMethod MyMethod(string myArg)
      return new returnMyMethod(); // do whatever

or in case the custom method is specified to use the database

using System;
using System.Data.Odbc;
using MyPrefix.DB.MyInterface;
namespace MyNamespace
  public class MyClass
    private OdbcCommand _cmd = null;
    public MyClass(OdbcCommand cmd) 
      _cmd = cmd;
    public returnMyMethod MyMethod(string myArg)
      return new returnMyMethod(); // do whatever

Ticket and user login

There are two authentication mechanisms available that you may turn on to rapidly secure your C# web services: tickets and user login. In both case, these mechanisms instructs the code generator to add extra arguments to your web service methods and make all methods invoke a custom authentication solution. For this to work, your solution must implement the expected authentication methods. 

Note! There are other, more comprehensive, ways to secure your web services such as using composed methods. You'll find further information about these and other concepts in our server security guidelines.

using System.Data.Odbc;
namespace MyPrefix
  public class Login
    public static bool Verify(OdbcConnection dbConn, string userid, string password)
      return false; // Your code to verify the userid and password, return true if ok
  public class Ticket
    public static bool Verify(OdbcConnection dbConn, string ticket)
      return false; // Your code to verify the ticket, return true if ok
If your implementation returns false the web service will terminate the execution and raise an exception. The database connection is passed to the Verify methods solely for convenience, in case your implementation somehow relies on the database for the actual authentication. It's configured but not open (connected), i.e. you need to call dbConn.Open() before using the connection. 

Uploading and downloading files

The C# for ODBC code generator supports chunked uploading and downloading of BLOBs (files). See our article on handling BLOBs for further details on how to make use of these methods.
  1. Generated database access classes provides a property, TempDir, controlling where files are stored during transfer. Each transfer is performed via a uniquely (GUID) named file stored in TempDir. When transfer is completed the file is removed. If the client application violates the protocol, not ending the transfer, the transfer file will remain in the temporary directory.
  2. Generated web services will specify App_Data as TempDir. Thus, for web service upload and download to work the App_Data folder must exist in your web application and the web application must be permitted to modify App_Data.
Note! You shouldn't deploy openly available web services containing file upload/download methods in the public domain (Internet) or in an, otherwise, untrusted environment. Instead, mark these methods as private, to exclude them from the web services, and use the generated database access classes in a server-side (.aspx) solution.