Search code examples
.netsqlibm-midrange

OLEDB ConnectionString for iSeries in C# 5.0 VisualStudio 2012

I need to establish a ConnectionString using OLEDB inside VisualStudio 2012 in C# 5.0 so below is coded:

 SQLConn = new SqlConnection("Data Source=STX;Persist Security Info=False;
  UserID=JG0149;Password=MYPASS;Initial Catalog=LIBJG");

The credentials work pretty good on the STX iSeries system

There are many examples using VS 2010 and C# 4.0 but it does not work on C# 5.0 similar to those found in www.connectionstring,com/as-400/

SQLConn = new SqlConnection("Provider=IBMDA400;Data Source=STX;User Id=JG0149; Password=MYPASS;
Default Collection=LIBJG")

but VS2012 does not accept Provider parameter and requests other ones

May someone have a ConnectionString that works in VS2012 to connect into a iSeries to share, please?

Solution

  • I can give you three connection strings that work. I use these in VS2012 to connect to an iSeries V6R1M0.

    Using IBM DB2 for i .NET Provider:

    DataSource=*ServerIP*;UserID=*user*;SchemaSearchList="*list of libraries separated by commas*";Database=*database_name*;PersistSecurityInfo=True

    Using .NET Framework Data Provider for OLE DB

    Provider=IBMDA400.DataSource.1;Data Source=*ServerIP*;Persist Security Info=True;OLE DB Services=-1;User ID=*user*;Initial Catalog=*database_name*;Catalog Library List="l*ist of libraries separated by commas*"

    Connection Used in a WebService

    DataSource=*ServerIP*; Default Collection='';User ID=*user*; Password=*password*;Data Compression=True; Connection Timeout = 180;

    In this last one I don't use SchemaSearchList neither Catalog Library List because when we establish a connection to iSeries there is one specific Library always present. In that Library we placed a stored procedure, that we always run in every new connection, defining the library list.

    I know this question has one year but it's one more help for someone needing.

    Update

    One more thing, IBM Data Provider allows you to establish a connection to DB2 on iSeries whithout any problem but when we talk about Visual Studio integration it's painfull many times.

    As an example, until last week I was using the IBM Data Provider that came with the IBM Client Access (client software for iSeries). I could establish sql connections in all my applications, without OLEDB, in Visual Studio 2010 but not in 2012 or 2013.

    This was because my Client Access was released prior to VS2012. Last week I've installed an update to Client Access and now I already have sql connections to DB2 iSeries on VS2012 and VS2013. So, it's convenient that you have always the last version of Client Access compatible with your iSeries version.

    Also, when you install Client Access check the files machine.config in these folders

    C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG

C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config

C:\Windows\Microsoft.NET\Framework64\v2.0.50727\CONFIG

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config

    Check for the DbProviderFactories tags and all tags inside it. Client Access usually, when installing, leave these malformed.

    If possible, for best performance, don't use ODBC or OLE DB when you have a data provider with managed code and optimized by the same company that made the database server. In this case, IBM it's not an exception.