How To Get Storis / UniData Data From a C# .Net Application (or just about anything) via ODBC in Windows

Chris — Sun, 08 Mar 2009 17:25:23 GMT

I have a client that uses Storis as his point-of-sale system. We've got a C#/ASP.NET app running on their intranet that monitors and reports on some other systems, and we wanted to add Storis to the mix. Storis uses IBM UniData as its back end, so this is where we started.

One of the things we got from Storis was a UniData Client CD, which includes an Admin, the UniDK, UniData ODBC, VSG, UniTools, and Dynamic Connect. Included in the UniDK are an OLEDB driver and UniObjects.NET. You might think that UniObjects.NET was the thing to use but that is not what we discovered. We WERE able to pull data out of Storis with UniObjects.NET, but the data was in its native form and SQL was not available. We had to do weird stuff, like query a table for its keys, get them in an array, and then query the table again by its keys. You couldn't just pull back data where Date >= '2009-03-08', for example. At least in no way that was apparent to us.

We installed all that stuff, but it turns out not all of it is necessary. We ended up only using the ODBC driver and VSG.

How we did it:

Install the ODBC Driver
Edit the C:\IBM\UniDK\Config\uci.config file, adding this to the bottom:
<storis>
DBMSTYPE = UNIDATA
network = TCP/IP
service = udserver
host = [your storis IP address]
Add an ODBC System DSN

Click Administrative Tools - Data Sources. Note that on a 64-bit system, you will need to access the 32-bit ODBC applet, which you can get to (on Vista x64, for example) in Control Panel -> View 32-Bit Control Panel Items
On the System DSN Tab, Click Add...
Select the IBM UniData ODBC Driver
Click Finish

Configure the DSN

Select your DSN from the list
Click Configure...
Give it a Data Source Name. We went with "StorisUnidata"
For the server, specify "storis", which is the first line you added to the uci.config file above.
Specify your Database name. In the UniData world, they often refer to this as the Account Name, which is a little confusing.
For the User, specify a windows account that can access your database. To eliminate permissions issues, we started with a local system administrator account. We entered that account name under User.
Click OK.

Before you can access your data with SQL, you need to use the Visual Schema Generator to create a SQL Mapping:

Prepare yourself, because this app is a bit ugly :). Think Lotus Notes...
Launch the VSG
This is just like configuring the DSN:

For the server, specify "storis", which is the first line you added to the uci.config file above.
Specify your Database name. In the UniData world, they often refer to this as the Account Name, which is a little confusing.
For the User, specify a windows account that can access your database. To eliminate permissions issues, we started with a local system administrator account. We entered that account name under User.
Enter the password for that windows account.
Click OK.

Click List on the Main Menu, to see all your tables.
Select a table, such as SALES.FLASH.DAILY
Click Open. Note that the dots are illegal in SQL table names, so they will be changed to underscores.
It was not necessary for us to configure any privileges. Possibly because the account was an admin. You may wish to try an Administrator account first as well, then come back here to refine the permissions after you have it working.
Click Map Attributes
Click the columns you want, and hit Add. You may be prompted to rename your column, swapping out illegal characters with underscores.
Click OK.
Click Close.
Hit List on the Main Menu and you should see your SQL Table listed in the right column.
You can exit VSG.
At this point, you should be able to access data in your Storis database via ODBC. So if you're an Access user, you're just about done. You can create a linked table in your usual way, specifying this DSN for your connection information. For .NET programmers, continue:
Your ConnectionString will look something like this: <add key="StorisSystemDsn" value="dsn=StorisUnidata;srv=storis;dbq=YourDatabaseName;uid=YourUserName;pwd=YourPassword;" />
If you're using the Enterprise Library Data Access Application Block (DAAB), your connection string will look more like: <add name="StorisOdbc" connectionString="dsn=StorisUnidata;srv=storis;dbq=YourDatabaseName;uid=YourUserName;pwd=YourPassword;" providerName="System.Data.Odbc" />
You're ready to code!
If your server is 64-bit, your app will not be able to load the 32-bit ODBC driver, and as far as I know, no 64-bit one is available. To get it working in IIS7 on a 64-bit box:

Create a new application pool, and call it something like StorisApp32
In its Advanced Settings, Set Enable 32-Bit Applications to True.
Set your app to use this application pool.
It will now run in 32-bit mode and load the ODBC driver.
You do not have to change the way you compile (no need to specify x86 only, or anything like that).

OdbcConnection
odbcConnection = new OdbcConnection(ConfigurationManager.AppSettings["StorisSystemDsn"]);
OdbcDataAdapter odbcDataAdapter = new OdbcDataAdapter("select
* from from SALES_FLASH_DAILY", odbcConnection); DataSet dataSet = new DataSet();
odbcDataAdapter.Fill(dataSet); gvRecords.DataSource = dataSet.Tables[0];
gvRecords.DataBind();

Database
db = DatabaseFactory.CreateDatabase("StorisOdbc");
DbCommand dbCommand = db.GetSqlStringCommand("select
* from from SALES_FLASH_DAILY"); DataSet dataSet = db.ExecuteDataSet(dbCommand);
gvRecords.DataSource = dataSet.Tables[0];
gvRecords.DataBind();

Continuum Drift - Storis

How To Get Storis / UniData Data From a C# .Net Application (or just about anything) via ODBC in Windows