C# for ODBC code generator

Web service configuration

Notes on database compliance

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 SELECT col FROM tab LIMIT 10
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: MySQL LAST_INSERT_ID() Postgres lastval() SQL Server SCOPE_IDENTITY()¹ ¹ The SQL Server output is of type numeric(38) and is converted to the suitable type (bigint/int).

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: {"d":{  "__type":"tjt.DB.IPage.returnGetPage",  "ID":1,  "myString":"just a string",  "myInt":14,  "myDate":"/Date(1178402400000)/",  "myBool":true}}
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] ...

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:

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

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     }   }    }

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.