Wow! I'm finally through the other side... what a quest and I thought I'd share some of the details with you.
RFC_READ_TABLE rfc can be used to call into SAP and retrieve table data - *sort of* (and that's a big sort of) like a 'DataSet'.
Using it requires a little work and understanding.
In your either BizTalk project or other project from VS.NET:
- Add the SAP bits to your VS.NET project - ready for action
- select 'Add Adapter Service Reference' (for BTS projects -> Add New Generated Item->Consume Service Adapter...)
- On the Binding Wizard Screen select sapBinding and configure the appropriate connection string details such as:
string
sapUri = "sap://CLIENT=800;LANG=EN;@A/sapsrv/00?GWHOST=sapsrv&GWSERV=sapgw00&RfcSdkTrace=true";
<You need to stick your own sapURI above - that is more or less a sample>
- Click on the Connect and under RFC->OTHER , select RFC_READ_TABLE (or you can type it in the box to search)
- Click Ok to generate the proxy and other details.
- Either your BizTalk Project or your non-BTS project has now all the relevant details to communicate to SAP.
I tend to build out all this functionality first in a Console App just so I know what is needed within the BTS environment, also I find it much quicker to test/debug etc. here.
- Ok - onto the code. I've got 2 routines for you, one that uses the Proxy Classes built by the wizard in the last step, and a routine from 'first principles'.
One of the things that I really like about the BTS Adapter Pack and certainly in this case, is that depending on the shape of the XML you pass to the adapter, it determines the table and type of operation that it is to do.
Both of these examples below you could wrap into a functoid/helper/whatever and use directly from code.
- Proxy Code - version 1 - here I define some parameters and make a straight call to the table CSKS.
NOTE: Use FieldNames not Field Labels (took me a few hrs on that one ;)
using
LOBTYPES = microsoft.lobservices.sap._2007._03.Types.Rfc;
private
static void GetDataFromSAP()
{
RfcClient clnt = new RfcClient(); //myproxy client
string[] data = GetAppDetailsForCurrentUser("SAP");
clnt.ClientCredentials.UserName.UserName = data[0];
clnt.ClientCredentials.UserName.Password = data[1];
LOBTYPES.TAB512[] rfcData = new microsoft.lobservices.sap._2007._03.Types.Rfc.TAB512[0];
LOBTYPES.RFC_DB_OPT[] rfcOps = new microsoft.lobservices.sap._2007._03.Types.Rfc.RFC_DB_OPT[0];
LOBTYPES.RFC_DB_FLD[] rfcFlds = new microsoft.lobservices.sap._2007._03.Types.Rfc.RFC_DB_FLD[]
{
new LOBTYPES.RFC_DB_FLD() {
FIELDNAME = "KOSTL",
LENGTH=10
},
new LOBTYPES.RFC_DB_FLD() {
FIELDNAME = "DATBI",
LENGTH=8
},
new LOBTYPES.RFC_DB_FLD() {
FIELDNAME = "DATAB",
LENGTH=8
}
};
try
{
clnt.Open();
clnt.RFC_READ_TABLE(";", string.Empty, "CSKS", 50, 0,ref rfcData, ref rfcFlds, ref rfcOps);
Console.WriteLine("RFC RESPONSE\r\n\r\nData:" + rfcData.Length.ToString());
}
catch (Exception ex)
{
Console.WriteLine("ERROR: " + ex.Message);
}
}
- More from first principles so this is to give you more of a BTS picture.
NOTE: The use of the '%' sign to get a wildcard match on a KOSTL field, despite in the SAP Client UI the users enter a '*'
private
static void GetDataFromSAPV1()
{
string[] data = GetAppDetailsForCurrentUser("SAP");
SAPBinding binding = new SAPBinding(); //A reference to Microsoft.Adapters.Sap is needed.
//set up an endpoint address
string sapUri = "sap://CLIENT=800;LANG=EN;@A/sapsrv/00?GWHOST=sapsrv&GWSERV=sapgw00&RfcSdkTrace=true";
EndpointAddress address = new EndpointAddress(sapUri);
try
{
ChannelFactory<IRequestChannel> fact = new ChannelFactory<IRequestChannel>(binding as Binding, address);
// add credentials
fact.Credentials.UserName.UserName = data[0];
fact.Credentials.UserName.Password = data[1];
// Open client
fact.Open();
//get a channel from the factory
IRequestChannel irc = fact.CreateChannel();
//open the channel
irc.Open();
string inputXml = "<RFC_READ_TABLE xmlns='http://Microsoft.LobServices.Sap/2007/03/Rfc/' xmlns:ns1='http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/'>"
+ "<DELIMITER>|</DELIMITER>"
+ "<QUERY_TABLE>CSKS</QUERY_TABLE>"
+ "<ROWCOUNT>10</ROWCOUNT><ROWSKIPS>0</ROWSKIPS>"
+ "<DATA /><FIELDS>"
+ "<ns1:RFC_DB_FLD><ns1:FIELDNAME>KOSTL</ns1:FIELDNAME></ns1:RFC_DB_FLD>"
+ "<ns1:RFC_DB_FLD><ns1:FIELDNAME>DATAB</ns1:FIELDNAME></ns1:RFC_DB_FLD>"
+ "<ns1:RFC_DB_FLD><ns1:FIELDNAME>DATBI</ns1:FIELDNAME></ns1:RFC_DB_FLD>"
+ "</FIELDS>"
+ "<OPTIONS>"
+ "<ns1:RFC_DB_OPT><ns1:TEXT>KOSTL LIKE '1234%' AND BUKRS EQ '63' AND KOKRS EQ 'APPL'</ns1:TEXT></ns1:RFC_DB_OPT>"
+ "</OPTIONS>"
+ "</RFC_READ_TABLE>";
//create an XML reader from the input XML
XmlReader reader = XmlReader.Create(new MemoryStream(Encoding.Default.GetBytes(inputXml)));
//create a WCF message from our XML reader
Message inputMessge = Message.CreateMessage(
MessageVersion.Soap11,
http://Microsoft.LobServices.Sap/2007/03/Rfc/RFC_READ_TABLE,
reader);
//send the message to SAP and obtain a reply
Message replyMessage = irc.Request(inputMessge);
//create a new XML document
XmlDocument xdoc = new XmlDocument();
//load the XML document with the XML reader from the output message received from SAP
xdoc.Load(replyMessage.GetReaderAtBodyContents());
XmlNodeList nds = xdoc.DocumentElement.SelectNodes("//*[local-name()='WA']");
foreach (XmlNode nd in nds)
{
string[] parts = nd.InnerText.Split('|');
Console.WriteLine("CC={0} From: {1} To: {2}", parts[0], parts[1], parts[2]);
}
xdoc.Save(@"d:\sapout.xml");
irc.Close();
fact.Close();
}
catch (Exception ex)
{
Console.WriteLine("ERROR: " + ex.Message);
}
}