Our biggest customer uses an AS400 as their database. The very limited programming I have done for them was years ago using Perl and ODBC. Most of the work we do for them currenty is done in RPG but they are now asking for stuff that is better suited to .NET, specifically ASP.NET, for their customers to view inventory on the web.



I have been looking and have some leads but if someone here has some practical expreience with something that works ( a data provider), I would sure appreciate it.

Keith Chisarik

*wishes for self edit privs again*



Also, the best lead I have would implement an OLEDB solution, I think you guys told me that would work fine with Strataframe, and that I would just lose some of the strong typing benefits and other benefits of a provider written specifically for the data source?

Keith Chisarik

Actually, most of the benefits of the .NET providers written specifically for a database are not used by StrataFrame itself... we have to rely on what's available in the classes in the System.Data.Common namespace to keep SF provider independent.  However, you will get better performance finding a DB2 provider instead of using OLEDB.  I'm pretty sure DataDirect has a good DB2 provider, as does IBM itself.

I have a prototype working for my AS400 data access project, I now want to convert the project to use Strataframe.



How do I take what I have to create the connection....



Dim oConn As OleDb.OleDbConnection

oConn = New OleDb.OleDbConnection("Provider=IBMDA400; Data Source=192.168.42.100; User ID=xxxxxxx; Password=yyyyyyy;")

oConn.Open()



and make it into something like this the framework requires...

'-- Visual Fox Pro

'DataLayer.DataSources.Add(New VfpDataSourceItem("", "myconnectionstring"))



as I dont see a datasource item for OLE.



I realize I cant use the ConnectionManager with OLE.



Thanks.

Keith Chisarik

I would use either the OleDb* objects or some other ADO.NET provider, like the one from DataDirect (http://www.datadirect.com/products/net/index.ssp).  Once you have the provider chosen, you will need to create your own DbDataSourceItem, like SqlDataSourceItem.  Your best bet is going to be to copy and modify the SqlDataSourceItem class's code file to create your own DbDataSourceItem for DB2.  The DbDataSourceItem class creates all of the actual SQL commands from the QueryInfo objects that are supplied by the DataLayer within the business objects.  It will take some work, and you will probably want to setup some unit tests to make sure it's all working, but you can use most of what is in the SqlDataSourceItem, because the two are very similar.  You can also cut out pieces of the implementation, for example, if you don't want to be able to support stored procedures.  The AccessDataSourceItem class uses OleDb* objects, but does not support stored procedures, so it is much smaller than the SqlDataSourceItem or OracleDataSourceItem.

Write AS400DataSourceItem, check.



Tell me how realistic this is to save some up-front work. Initially all we need to do for phase one is export, read, and report data. Could I just convert/modify the SELECT type methods from the AccessDataSourceItem template to create a sort of read-only DbDataSourceItem. All I really want to do do is populate a BO, not update it (yet).



Then later I could layer on the more complicated (I'm sure) INSERT, UPDATE, and concurrency methods?



What do you think? Is that viable at all?



Thanks.

Keith Chisarik

If all you want do to is create a read only DB2 item, then it will actually be quite easy.  You will probably have it done before the day is out.  You'll still want to copy over the SqlDataSourceItem and modify it, but you can take the methods like UpdateRow(), and CreateInsertCommand(), etc. and just throw a NotSupportedException from within them.  Then, all you need to do is modify the CreateSelectCommand() method to match what the AS400 is expecting.  As far as I can tell, the only thing that needs to be modified up front is to remove the TOP keyword and make it a LIMIT at the end of the command (you'll see where it is when you look at the code).  The other methods that need to be changed are one-liners... like CreateBlankDbCommand()... it just returns a new OleDbCommand object, or whatever provider you're using.

Awesome, you guys are the best.

Keith Chisarik

Next question....



I got the initial work done so at least it doesnt blow up on me using:

DataLayer.DataSources.Add(New AS400DataSourceItem("", "Provider=IBMDA400; Data Source=192.168.42.xxx; User ID=xxx; Password=xxx;"))



I didnt actually change much in the custom datasource item, I figured once I mapped it I could debug.



My problem is this, I dont seem to be able to use the BO Mapper. I have the project set to use the OLE DB connection string and it connects, but the mapper only shows SQL Server, then the IP address of the server in the treeview when I hit "Specify Data Source", no tables. The AS400 uses libraries, then files instead of databases and tables, this may be the issue.



This is the code that works not using Strataframe if that helps any. It is just a simple "proof of concept" that loads a single field into a grid.



Being able to use Strataframe with our AS400 client base is huge for us, I appreciate the ongoing help.



Try

Dim sqlstring As String

sqlstring = "SELECT CSANCS FROM FRUITF.PLT"

'setup our connection to the database

Dim oConn As OleDb.OleDbConnection



oConn = New OleDb.OleDbConnection("Provider=IBMDA400; Data Source=192.168.42.xxx; User ID=xxx; Password=xxx;")

oConn.Open()



'setup the data adapter

Dim myDataAdapter As OleDb.OleDbDataAdapter



myDataAdapter = New OleDb.OleDbDataAdapter()



myDataAdapter.SelectCommand = New OleDb.OleDbCommand(sqlstring, oConn)



Dim myData As DataSet

myData = New DataSet()



myDataAdapter.Fill(myData, "PLT")



Dim dt As DataTable

dt = myData.Tables("PLT")



Me.BindingSource1.DataSource = dt

''binding the table to datagrid

Me.DataGridView1.DataSource = Me.BindingSource1

Me.DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)

Me.DataGridView1.Refresh()



'close the connection

oConn.Close()



Catch ex As Exception

MsgBox(ex.ToString)

End Try

Keith Chisarik

I have things cooking in "raw ADO" using the DataDirect provider as well. Since I can access data from my AS400 using both OLE and ADO now, I guess I just need help making these work with SF.

Keith Chisarik