用.Net Call SAP RFC，將RFC回傳資料存入SQL Server Table中
最近接到一個需求，要將客戶的SAP部份資料轉入SQL Server中供外系統使用，對方的SAP顧問只有提供RFC供呼叫
這輩子從來沒用過SAP，連它長的如何都不知，只好上股溝大神求救
果然網路神人及善心人士一堆，所以東抄西抄也抄出來了，果然天下程式一大抄
重點在抄對地方，貼對地方就好，試過可以Work後，把它整理一下，好供日後有需要時可用，也在這筆記一下，免得年紀大又忘了
接下來筆記了，我把它包成一個dll，這樣好像要用比較方便，要用時變成這樣很簡單，用法如下
一定要引用Nco元件，但下載好幾個都不能用，我是用.netframework4.5，最後在下面下載到我可以用的.dll超開心
https://github.com/ion-sapoval/NSAPConnector
//範例
//RFC叫 RFCJOJO,回傳Table名稱為 JOJO
//我要存入SQL Server的資料表叫tbl_JOJO
//調用方式如下
CallRFC _sap = new CallRFC();
SAPResult execresult = new SAPResult();
_sap.SetPartTableName = "tbl_"; //你要轉入SQL Server的table名稱
_sap.SetSAPConn = "DEV";
_sap.SetDBConn = Properties.Settings.Default["DBConn"].ToString();
_sap.UserID = "我叫UserID";
execresult = _sap.ConvertDataFromSAPRFC("RFCJOJO", "JOJO", true);
MessageBox.Show(execresult.execMessage);
//如果要存入SQL ServerTable名稱和變數都無關,可以自行在SetPartTableName給值,call ConvertDataFromSAPRFC最後參數設False
CallRFC _sap = new CallRFC();
SAPResult execresult = new SAPResult();
_sap.SetPartTableName = "DaiDai"; //你要轉入SQL Server的table名稱
_sap.SetSAPConn = "DEV";
_sap.SetDBConn = Properties.Settings.Default["DBConn"].ToString();
_sap.UserID = "我叫UserID";
execresult = _sap.ConvertDataFromSAPRFC("RFCJOJO", "JOJO", false);
MessageBox.Show(execresult.execMessage);
dll重點內容如下囉
public SAPResult ConvertDataFromSAPRFC(string strRFCName, string strTableName,bool isIncludeTableName)
{
SAPResult _sapresult = new SAPResult();
IDestinationConfiguration ID = new SAPBackendConfig();
try
{
RfcDestinationManager.RegisterDestinationConfiguration(ID);
RfcDestination _rfcDes = RfcDestinationManager.GetDestination(SAPConn);
_sapresult = ConvertDataFromSAPRFC(_rfcDes, strRFCName, strTableName, isIncludeTableName);
//blResult = true;
}
catch (Exception e)
{
}
finally
{
RfcDestinationManager.UnregisterDestinationConfiguration(ID);
}
return _sapresult;
}
public SAPResult ConvertDataFromSAPRFC(RfcDestination _rfcDes, string strRFCName, string strTableName,bool isIncludeTableName)
{
SAPResult _sapresult = new SAPResult();
RfcRepository _rfcrpo = _rfcDes.Repository;
IRfcFunction _rfcfunction = _rfcrpo.CreateFunction(strRFCName); //調用RFC名稱
_rfcfunction.Invoke(_rfcDes);
IRfcTable _rfctable = _rfcfunction.GetTable(strTableName); //RFC回傳Table 名稱
//=========================================================
//4個欄位是目的資料表固定要加的
DataTable _dtResult = new DataTable(strTableName);
_dtResult.Columns.Add("CREATIONTIME", typeof(DateTime));
_dtResult.Columns.Add("CREATEDBY", typeof(string));
_dtResult.Columns.Add("LASTUPDATETIME", typeof(DateTime));
_dtResult.Columns.Add("LASTUPDATEDBY", typeof(string));
//=========================================================
for (int liElement = 0; liElement < _rfctable.ElementCount; liElement++)
{
RfcElementMetadata metadata = _rfctable.GetElementMetadata(liElement);
_dtResult.Columns.Add(metadata.Name, GetDataType(metadata.DataType));
}
_sapresult = TranData(_rfctable, _dtResult, strTableName, isIncludeTableName);
_rfcDes = null;
_rfcrpo = null;
return _sapresult;
}
private SAPResult TranData(IRfcTable _rfctable, DataTable _dtreslt, string strTableName,bool isIncludeTableName)
{
SAPResult _sapresult = new SAPResult();
string strMessage = "";
bool blResult = false;
foreach (IRfcStructure _rfcdr in _rfctable)
{
DataRow _dr = _dtreslt.NewRow();
for (int liElement = 0; liElement < _rfctable.ElementCount; liElement++)
{
RfcElementMetadata metadata = _rfctable.GetElementMetadata(liElement);
_dr[metadata.Name] = _rfcdr[metadata.Name].GetValue();
}
_dr["CREATIONTIME"] = System.DateTime.Now;
_dr["CREATEDBY"] = userid;
_dr["LASTUPDATETIME"] = System.DateTime.Now;
_dr["LASTUPDATEDBY"] = userid;
_dtreslt.Rows.Add(_dr);
}
strMessage = "這裡可以給執行訊息";
blResult = BulkInsertByTruncateTable(_dtreslt, strTableName, isIncludeTableName);
_sapresult.blResult = blResult;
_sapresult.execMessage = strMessage;
return _sapresult;
}
private bool BulkInsertByTruncateTable(DataTable _dtreslt, string strTableName,bool isIncludeTableName)
{
bool blResult = false;
string strOnLineTableName = "";
//依參數是否包含RFC回傳Table的Name
if (isIncludeTableName)
{
strOnLineTableName = partTableName + strTableName;
}
else
{
strOnLineTableName = partTableName;
}
using (SqlConnection conn = new SqlConnection(dbConn))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
//SqlBulkCopy批次處理新增 沒有檢驗比對處理
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, trans))
{
bulkCopy.DestinationTableName = strOnLineTableName;
//原則上,目前的名稱和DB欄位名稱一致才對
foreach (DataColumn column in _dtreslt.Columns)
{
//SqlBulkCopyColumnMapping mapID =
// new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName);
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName));
}
bulkCopy.WriteToServer(_dtreslt);
}
try
{
trans.Commit();
blResult = true;
}
catch
{
}
finally
{
}
}
return blResult;
}
public class SAPBackendConfig : IDestinationConfiguration
{
public RfcConfigParameters GetParameters(String destinationName)
{
RfcConfigParameters parms = new RfcConfigParameters();
switch (destinationName.ToUpper())
{
case "DEV":
parms.Add(RfcConfigParameters.AppServerHost, "主機位置"); //SAP主機IP
parms.Add(RfcConfigParameters.SystemNumber, "00");
parms.Add(RfcConfigParameters.User, "LoginName"); //用户名
parms.Add(RfcConfigParameters.Password, "Password"); //密碼
parms.Add(RfcConfigParameters.Client, "100"); // Client
parms.Add(RfcConfigParameters.Language, "ZF"); //語系
parms.Add(RfcConfigParameters.PoolSize, "5");
parms.Add(RfcConfigParameters.PeakConnectionsLimit, "10");
parms.Add(RfcConfigParameters.IdleTimeout, "60");
parms.Add(RfcConfigParameters.SystemID, "DEV");
break;
case "PRD":
parms.Add(RfcConfigParameters.AppServerHost, "主機位置"); //SAP主機IP
parms.Add(RfcConfigParameters.SystemNumber, "01");
parms.Add(RfcConfigParameters.User, "LoginName"); //用户名
parms.Add(RfcConfigParameters.Password, "Password"); //密碼
parms.Add(RfcConfigParameters.Client, "500"); // Client
parms.Add(RfcConfigParameters.Language, "ZF"); //語系
parms.Add(RfcConfigParameters.PoolSize, "5");
parms.Add(RfcConfigParameters.PeakConnectionsLimit, "10");
parms.Add(RfcConfigParameters.IdleTimeout, "60");
parms.Add(RfcConfigParameters.SystemID, "PRD");
break;
default:
parms = null;
break;
}
return parms;
}
public bool ChangeEventsSupported()
{
return false;
}
public event RfcDestinationManager.ConfigurationChangeHandler ConfigurationChanged;
}
public class SAPResult
{
public bool blResult { get; set; } //執行狀態
public string execMessage { get; set; } //執行訊息
}
打雜打久了，就變成打雜妹
程式寫久了，就變成老乞丐