How To Get Storis / UniData Data From a C# .Net Application (or just about anything) via ODBC in Windows
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).
Grab some data and bind it to a GridView called gvRecords like this:
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();If using the Enterprise Library, try this:
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();That worked for us, and I hope it works for you!
Notes: I was not able to get the OLEDB Driver to work. I kept getting errors about the provider name not being correct. I tried different provider names, even using a script to get the exact name registered with the system, but to no avail. If you get it working, please leave a comment with some instructions :)