We have need to connect to an AS/400 from .NET (C#) and access (read and write) data.
The issue is the data is stored in relational files on the mainframe, not in a DB2 database.
What is the best way to accomplish this? I would love to use an ADO.NET Entity Framework type solution, but I believe the compatible data providers only work with DB2.
I am slightly confused by your terminology, but I am going to make some assumptions and respond.
You mentioned that you are accessing "files" from the AS/400 (aka iSeries, aka IBM i). A "file" is a table. A library is a collection, a record is a row and a field is a column. They are the same thing.
iSeries Access comes with a .NET ADO.NET driver. You can use that and then use SQL as one normally would in an MS SQL database. Some of the functions are different but you can use the manual to figure out those quirks.
There is a DB2 lite offering and I believe it is offered on the Windows and Linux platforms. The iSeries (AS/400) as well as z-Series (mainframe) use the "full version" of DB2 because it is integrated into the operating system. If I were you, I would ask the mainframe guys if they have the .NET ADO.NET driver from IBM and the connection information for you.
IBM has provided a good redbook on the subject of connecting to the AS/400 (i5). Basically you need to make sure you have the licensed product iSeries Access For Windows (5722-XE1) and you need to setup a data provider on the Systemi. The redbook discusses setting the provider up. The resource for 5722-XE1 is found on the IBM website.
If you would like a older example, there is an article on the four hundred guru site.
Hope this helps -- feel free to post if there is additional information you need or if your administrators have questions about the setup. If you need additional info, make sure you post the OS version (like V5R4 or something like that)
Here is an article about accessing as400 from .NET: http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/rzaik/rzaikdotnetprovider.htm
It's important to understand that the DB2 engine is embedded in the operating system. Therefore any table in any place is part of the DB2 database. It is ABSOLUTELY NOT important how the file is created (e.g. SQL, RPG, CRTPF, JDBC, COBOL, C). Also, it is ABSOLUTELY NOT important if someone calls the object a file. Everything is really a database table that is in the scope of DB2. You can test that yourself by using the system catalog on the iSeries (e.g. SYSTABLES). Notice that you can find your table in that place. Also, notice that you can use any JDBC or ODBC software to access the data on the iSeries.
That having said, there are some subtle differences. First of all, the DB2 for iSeries is a 100% real DB2 application, but is missing some things from his fellows. For example, MERGE is available on Windows and Unix, but not on the iSeries. Check the IBM Information center for this.
On the iSeries you do have some other objects that can store data. Most used are the DATAAREA and the DTAQ. These objects are NOT part of the DB2 database. Something like the FIFO object on unix is not part of the database
At last but not least, you can work with DB2 the iSeries way, or the DB2 way. The iSeries way is available for guys that grew up with the AS400. For example, you use CATALOG/TABLE instead of CATALOG.TABLE. Normally you would use the DB2 way. Every tool with iSeries support that I know off, does also support the DB2 way. Choose that option and everything works like the books.
How we did it on the mainframe: using CICS web services, we have some transactions that serve up data using SOAP. From a programmer standpoint, it's essentially just writing to a TS queue. Then we use an WSDL generation tool provided by IBM to build the WSDL that describes the service accordingly (with methods, input/output parms, etc). Then on the .NET side, just add a web reference to the WSDL and you are on your way. One caveat: we had create a class that inherits the web service, and overrides GetWebRequest to set KeepAlive to false. You may need to do this as well.
Going the other way (mainframe acting as client), it's basically the same thing (a temp TS que write), except there are a couple different parameters required (such as the URL to the web server to be invoked, name of the method, etc).
There is probably a better way, but we couldn't figure out any other way. I believe we had to take the more difficult route because our OS is not MVS, but rather VSE, which is a cheaper version and does not have very many nice features as MVS. The initial code samples we referenced were written by Richard Smrcina.