Pulling SAP Data From .NET

Tue Aug 14, 2007 by Jon Hermiz in sap, net, sql-server

Pulling SAP Data From .NET Technologies

SAP – What it is

SAP is the biggest European software company specializing in CRM software. Its competitors include companies such as Oracle, PeopleSoft, SalesForce, and a whole slew of other software vendors. These companies are huge, we are talking about billions of dollars when it comes to writing, selling, training, talking, and working on these software platforms. Companies sit on top of these platforms and rely on these tools to run there business. In this topic we are going to concentrate on SAP specifically. A lot of big and small companies run SAP in there companies work processes. The tool is very diverse allowing you to store purchasing information, sales orders, human resources, engineering data, etc. The tool is flexible and handles pretty much any department in an organization.

SAP – Security is a killer

There is one nuisance however with SAP. It is a very tightly sealed application; security is a vital branch in this huge CRM tool. Due to the security in SAP many people lose interest in trying to make things work because they cannot or don’t know how to. However, managers seem to always want some sort of data dump from SAP, and sometimes SAP itself doesn’t have the nicest export features. Or maybe you simply need to pull data from SAP to feed other database applications. Common data that you don’t want to administer in two systems, say for instance SQL Server and SAP. You want your employees who administer SAP to do all the dirty work, that is, handling data entry of new sales orders, customers, etc. However, you need this data in your SQL Server database because the application you are working on needs this data. Data duplication really sucks and you want to avoid it at all costs. Maintaining two sets of data is difficult and very costly. Problems such as data sync issues, administration costs, latency, data corruption, and even bad spelling can lead to problems in your company’s data.

So this post will give you some insight on how to pull data from an SAP system to a SQL Server database from a .net application. I am assuming the reader is a .net developer and he / she should understand at least the basics of SAP, you do not have to be an SAP guru to understand this process. This post will be pretty lengthy, because the process is pretty lengthy and we are dealing with SAP here, those who use it on a regular basis know how cumbersome it really is.

How This All Plays Out:

Take a look at the image above, get an understanding of how the flow works and how to pull the data before writing any code. It always helps to understand what your doing before writing any code. As you see a .net app such as VB.net or C# uses a proxy object.

That object is used to call a BAPI function or table from SAP. The result can be returned from SAP and stored in a datatable or dataset.. That’s basically it at a high level.

First what you need:

In order to pull data from SAP through a .net application you need to have:

S-Number : An S-Number must be requested by you to your SAP team / anyone who can communicate with the SAP folks in order to request your S-Number. An S-Number is generally 11 characters long with the first letter being an ‘S’ followed by 10 digits. You need this S-Number to log into the SAP marketplace. You will also need this S-Number in order to download the .net SAP Connector (which is needed in order to pull the SAP data).
- No I will not send you my S-Number so don’t bother asking
- No I will not upload the SAP .net connector application for you neither so again don’t bother asking me
- The S-Number is a personal ID provided by SAP so giving it out is illegal, unethical, and should be avoided. Basically get your own!
After you have an S-Number head on over here to download the .net connector. You can get the 1.0 release for .net 2003 or the 2.0 release for .net 2005.
Once you’ve downloaded the .net SAP connector simply run and install it, it will become an add on to visual studio .net.

· If you have the connector installed, you have an SAP system available, and you have some specific rights on the SAP system than you are good to go. Let’s begin the coding of this system (we’ll do it in vs.net 2k3 but it works the exact same way in 2k5).

Creating the Project

Begin by creating a new say C# project (you can make it vb.net if you’d like, the code is very similar if not exact). I choose a Console Application because I am simply going to write a console application to pull data from SAP and place it into SQL Server. So select File->New Project. Highlight C# and select Console Application. Call it anything you want:

To connect to a SAP database SAP needs 4 pieces of key information:

SAP SID
Client Number (what client you are connecting to)
User name (with good security rights)
Password

If you do not know what any of these are then you wont be able to understand the code or connect to the database. In this case talk to your SAP Basis administrator for more information.

Once you have this data start of with something like this in your code…

Main Console Code

using System;

using System.Data;

namespace SAPCommonData

{

class MainKNA1

{

[STAThread]

static void Main(string[] args)

{

//used for storing user name, client, etc

string strSID, mandt, strUsr, strPswd;

//will define later

clsSapData s;

DataTable dt;

System.Data.SqlClient.SqlDataReader dr;

try

{

strSID = Get_AppCfg("SAPSID");

mandt = Get_AppCfg("MANDT");

strUsr = Get_AppCfg("SAP_USER");

strPswd = Get_AppCfg("SAP_PSWD");

}

catch (Exception e)

{

throw new System.Exception(e.Message.ToString());

}

try

{

s = new clsSapData();

dt = new DataTable();

Console.WriteLine("Please wait while processing customer data...");

s.GetKNA1(ref dt,strSID,mandt,strUsr,strPswd);

s.ProcessCustomerData(dt);

Console.WriteLine("SAP customer(s) processed: " + dt.Rows.Count.ToString());

System.Threading.Thread.Sleep(2000);

Environment.Exit(0);

}

catch (Exception e)

{

throw new System.Exception(e.Message.ToString());

}

finally

{

s = null;

}

static string Get_AppCfg(string strKey)

{

System.Configuration.AppSettingsReader cfg = new System.Configuration.AppSettingsReader();

object obj = cfg.GetValue(strKey, strKey.GetType());

return obj.ToString();

}

So what exactly does this code do? It begins by pulling the SID, Client (Mandt), User, and Password from our App.Config file (you need to store these values in your app.config file). Once it gets this information it instantiates the clsSapData object. Which I define below:

clsSapData

using System;

using System.Data;

using System.Data.SqlClient;

using System.IO;

namespace SAPCommonData

{

public class clsSapData

{

private SAPProxy1 proxy;

private SQLClass s;

private readonly String logPath;

public DataTable GetKNA1(ref DataTable dt,string strSID,string mandt,string strUsr,string strPswd)

{

BAPIRETURN brtn = new BAPIRETURN();

KNA1Table tblKNA1 = new KNA1Table();

try

{

OpenSap(strSID,mandt,strUsr,strPswd);

}

catch (Exception e)

{

throw new System.Exception(e.Message.ToString());

}

try

{

proxy.Z_Rfc_Kna1_Getall(mandt,out brtn,ref tblKNA1);

dt = tblKNA1.ToADODataTable();

return dt;

}

catch (Exception e)

{

throw new System.Exception(e.Message.ToString());

}

finally

{

CloseSap();

brtn = null;

tblKNA1 = null;

}

private void OpenSap(string strSID,string mandt,string strUsr,string strPswd)

{

try

{

proxy = new SAPProxy1();

proxy.ConnectionString = GetConnString(strSID, mandt, strUsr, strPswd);

proxy.Connection.Open();

}

catch (Exception e)

{

throw new System.Exception(e.Message.ToString());

}

private String GetConnString(string strSID,string mandt,string strUsr,string strPswd)

{

String ConnStr = "ashost=" + strSID + " sysnr=0 trace=0 ABAP_DEBUG = 0";

ConnStr = ConnStr + " client=" + mandt + " passwd=" + strPswd;

ConnStr = ConnStr + " type=3 user=" + strUsr;

return ConnStr;

}

private void CloseSap()

{

try

{

proxy.Connection.Close();

}

catch (Exception e)

{

throw new System.Exception(e.Message.ToString());

}

public void ProcessCustomerData(DataTable dt)

{

//we have a valid connection to the database

if (dt.Rows.Count > 0)

{

try

{

s=new SQLClass();

}

catch (Exception e)

{

throw new System.Exception(e.Message.ToString());

}

foreach(DataRow dr in dt.Rows)

{

tw.WriteLine("Processing customer: " + dr["NAME1"].ToString());

Console.WriteLine("Processing customer: " + dr["NAME1"].ToString());

s.SetSQLCommand("insCustomer");

s.AddSQLCmdParameter("@ClientID", System.Data.SqlDbType.Int, dr["MANDT"]);

s.AddSQLCmdParameter("@CustomerID", System.Data.SqlDbType.BigInt, dr["KUNNR"]);

s.AddSQLCmdParameter("@CustomerName1", System.Data.SqlDbType.VarChar, ((string)dr["NAME1"]==String.Empty ? DBNull.Value : dr["NAME1"]));

s.AddSQLCmdParameter("@CustomerName2", System.Data.SqlDbType.VarChar, ((string)dr["NAME2"]==String.Empty ? DBNull.Value : dr["NAME2"]));

s.AddSQLCmdParameter("@Country", System.Data.SqlDbType.VarChar, ((string)dr["LAND1"]==String.Empty ? DBNull.Value : dr["LAND1"]));

s.AddSQLCmdParameter("@Region", System.Data.SqlDbType.VarChar, ((string)dr["REGIO"]==String.Empty ? DBNull.Value : dr["REGIO"]));

s.AddSQLCmdParameter("@City", System.Data.SqlDbType.VarChar, ((string)dr["ORT01"]==String.Empty ? DBNull.Value : dr["ORT01"]));

s.AddSQLCmdParameter("@ZipCode", System.Data.SqlDbType.VarChar, ((string)dr["PSTLZ"]==String.Empty ? DBNull.Value : dr["PSTLZ"]));

s.AddSQLCmdParameter("@Address", System.Data.SqlDbType.VarChar, ((string)dr["STRAS"]==String.Empty ? DBNull.Value : dr["STRAS"]));

s.AddSQLCmdParameter("@Telephone", System.Data.SqlDbType.VarChar, ((string)dr["TELF1"]==String.Empty ? DBNull.Value : dr["TELF1"]));

s.AddSQLCmdParameter("@Fax", System.Data.SqlDbType.VarChar, ((string)dr["TELFX"]==String.Empty ? DBNull.Value : dr["TELFX"]));

s.AddSQLCmdParameter("@DateAdded", System.Data.SqlDbType.DateTime, System.DateTime.Today);

s.AddSQLCmdParameter("@DateModified", System.Data.SqlDbType.DateTime, System.DateTime.Today);

s.AddSQLCmdParameter("@AddedBy", System.Data.SqlDbType.VarChar, DBNull.Value);

s.AddSQLCmdParameter("@ModifiedBy", System.Data.SqlDbType.VarChar, DBNull.Value);

s.SQLExecuteNonQuery();

Console.WriteLine("Processed customer: " + dr["NAME1"].ToString());

tw.WriteLine("Processed customer: " + dr["NAME1"].ToString());

}

s.CloseSQLDB();

s.Dispose();

}

Basically this class is used to open a connection to SAP using the user name, password, client, and SAP SID. Remember that you should not expect your code to work if all you are doing is copying and pasting. You have to understand how SAP works. You have to understand what BAPI functions are. You have to understand how to call RFC’s, etc, etc. There would be no way that I could possibly give you exact code for your system since everyone’s environment is slightly different. You will notice that in our Main Console Code we did something to this effect:

s.GetKNA1(ref dt,strSID,mandt,strUsr,strPswd);…

If you look at the clsSAPData class for the definition of GetKNA1 we see the following:

public DataTable GetKNA1(ref DataTable dt,string strSID,string mandt,string strUsr,string strPswd)

{

BAPIRETURN brtn = new BAPIRETURN();

KNA1Table tblKNA1 = new KNA1Table();

try

{

OpenSap(strSID,mandt,strUsr,strPswd);

}

catch (Exception e)

{

throw new System.Exception(e.Message.ToString());

}

try

{

proxy.Z_Rfc_Kna1_Getall(mandt,out brtn,ref tblKNA1);

dt = tblKNA1.ToADODataTable();

return dt;

}

catch (Exception e)

{

throw new System.Exception(e.Message.ToString());

}

finally

{

CloseSap();

brtn = null;

tblKNA1 = null;

}

This function GetKNA1 takes a datatable dt as well as the 4 pieces of information to open sap. After it is done processing it will return a datatable of SAP customers in your system. But pay particular attention to this line:

proxy.Z_Rfc_Kna1_Getall(mandt,out brtn,ref tblKNA1);

The object proxy is simply the .net connector proxy that you need to add to your project. Right click the project and add an existing item. Select the SAP .net connector proxy object and keep the name SAPProxy1 as the object name. In the class defined above the clsSAPData class we have an object of type SAPProxy1 called proxy. It is found in the class variable declarations section:

public class clsSapData

{

private SAPProxy1 proxy;

private SQLClass s;

…

Within the GetKNA1 function when we did:

proxy.Z_Rfc_Kna1_Getall(mandt,out brtn,ref tblKNA1);

Z_Rfc_Kna1_GetAll is an RFC function that we wrote in SAP, you again will not have the same setup as we do. Talk to your basis administrators (SAP) or your SAP ABAP / Java developers about how to call RFC functions or even how to write them. Once we call this function we pass tblKNA1 which is a KNA1Table. Doing this will allow us to call ToADODataTable function to convert the result to a datatable:

proxy.Z_Rfc_Kna1_Getall(mandt,out brtn,ref tblKNA1);

dt = tblKNA1.ToADODataTable();

return dt;

Got The SAP Data, Now What ?

Now that you have the datatable you simply process the datatable by doing what you normally do in .net, that is writing .net code to pass data to an SQL database. This is the simple stuff, the stuff you do everyday. Stuff like writing sprocs, passing parameters, inserting data, etc. So the line in our Main Console Command:

s.ProcessCustomerData(dt);

Basically calls a function to process our customer data, passing the datatable to it of course. Here is a look at the ProcessCustomerData method: