ADO.NET ConnectionString for Rocket Universe database

 

If you have not been keeping up with the latest developments in .NET in regards to Rocket Universe development, you may not know that Rocket has developed libraries that now support use of ADO.NET as well as Entity Framework!

You can download the latest .NET libraries that are part of the U2 Client from here: http://www.rocketsoftware.com/products/rocket-u2-clients-and-dbtools/try-now

However, once you download and install the latest .NET Client, you will notice that none of the code samples demonstrate how to use a connectionString in the App.config or Web.config file!  Instead, the samples simply use the U2ConnectionStringBuilder to build a connection string manually in the code file.

Fortunately, you can readily deduce the required structure for the App.config or Web.config file from this code sample which results in the following connection string format:

<connectionStrings><add name="(Connection String Name)" connectionString="Server=(Server Name);Database=(Database Name);UID=(Windows user ID);Pwd=(Windows Password);ServerType=UNIVERSE;Pooling=false;" providerName="U2.Data.Client" /></connectionStrings>

A sample of the connection string looks like this:

<connectionStrings>
    <add name="UNIVERSEConn" connectionString="Server=localhost;Database=HS.SALES;UID=MyUser;Pwd=MyPassword;ServerType=UNIVERSE;Pooling=false;" providerName="U2.Data.Client"/>
  </connectionStrings>

Creating a Linked Server to a Universe database in SQL Server 2012

If you ever have to work with the Universe database from Rocket Software and you are a Microsoft developer  used to the niceties of using a standard RDBMS like SQL Server, chances are that you want to simplify your life by connecting to the Universe database by adding it as a Linked Server in SQL Server.

The MSDN Documentation for creating a Linked Server can be found here: http://msdn.microsoft.com/en-us/library/aa259589(v=sql.80).aspx

As you can tell from the documentation, if you are using an ODBC-based provider/ODBC Driver, you will need to either provide a System DSN or an ODBC Connection String to the database.  Well, by taking a quick look at connectionstrings.com (http://connectionstrings.com/ibm-universe), there does not seem to be an up-to-date connection string to indicate the ODBC Drivers now produced by Rocket Software (Universe was recently acquired/sold to Rocket Software from IBM).

Therefore, given that I have very little experience with the Universe platform and had absolutely no idea what the ODBC connection string meant, I opted to use a System DSN instead.  Since I had just installed the 64-bit ODBC Driver, I was simply able to type odbcad32.exe at the Run command to open the following dialog:

Note that I checked the checkbox for "Save Password" just so that I did not have to deal with re-configuring the user credentials while creating my Linked Server.

Finally, I used SQL Server Management Studio to create my Linked Server as follows:

Since I was using the OLE DB Provider for ODBC Drivers, my Product Name was "MSDASQL" while my Data Source was the name of my System DSN--in this case "UNIVERSE".

After clicking OK, my Linked Server was successfully created!!