Friday, April 24, 2015

DotNet-SAP Connector - How to Read/Write data to SAP from DotNet



Read and Write data into SAP by DotNet console application.
1. Create VS console application.
2. To connect to SAP need 3 ddl as below:

  •  SAPConnectionManager
  • sapnco
  • sapnco_utils
download the above dlls and add into your application as a reference.

3. Enter SAP configuration settings entries in the app.config app settings.


<appSettings>
    <add key="SAP_ApplicationServerHost" value="ServerName/IP"/>
    <add key="SAP_SystemID" value="DEV"/>
    <add key="SAP_SystemNumber" value="00"/>
    <add key="SAP_User" value="userid"/>
    <add key="SAP_Password" value="password"/>
    <add key="SAP_Client" value="040"/>
    <add key="SAP_Language" value="EN"/>
</appSettings>

4. Add one new class for SAP configuration.
add "SAP.Middleware.Connector" namespace.
inherit the IDestinationCofiguration interface, and right click the call on it to implement the interface

using SAP.Middleware.Connector;

namespace SAPConenctCnsl
{
    public class SAPDstinConfig : IDestinationConfiguration
    {
        public bool ChangeEventsSupported()
        {
            return false;
        }

        public event RfcDestinationManager.ConfigurationChangeHandler ConfigurationChanged;


        public RfcConfigParameters GetParameters(string destinationName)

        {
            RfcConfigParameters parms = new RfcConfigParameters();
            parms.Add(RfcConfigParameters.Name, "DEV");
            parms.Add(RfcConfigParameters.AppServerHost, ConfigurationManager.AppSettings["SAP_ApplicationServerHost"]);
            parms.Add(RfcConfigParameters.SystemNumber, ConfigurationManager.AppSettings["SAP_SystemNumber"]);
            parms.Add(RfcConfigParameters.SystemID, ConfigurationManager.AppSettings["SAP_SystemID"]);
            parms.Add(RfcConfigParameters.User, ConfigurationManager.AppSettings["SAP_User"]);
            parms.Add(RfcConfigParameters.Password, ConfigurationManager.AppSettings["SAP_Password"]);
            parms.Add(RfcConfigParameters.Client, ConfigurationManager.AppSettings["SAP_Client"]);
            parms.Add(RfcConfigParameters.Language, ConfigurationManager.AppSettings["SAP_Language"]);
            parms.Add(RfcConfigParameters.PoolSize, "10");

            return parms;


        }

    }
}

5. add one more new class for Interface , to write your logics


using SAP.Middleware.Connector;
namespace SAPConenctCnsl
{
    public class SAPConntInterface
    {
        private RfcDestination rfcDestination;

// To test the connection

        public bool TestConnection(string destinationName)
        {
            bool result = false;

            try

            {
                rfcDestination = RfcDestinationManager.GetDestination(destinationName);
                if (rfcDestination != null)
                {
                    rfcDestination.Ping();
                    result = true;
                }

            }

            catch (Exception ex)
            {

                result = false;

                throw new Exception("Connection Failed :" + ex.Message);
            }
            return result;
        }
// Getting data with out RFC parameters
 public DataTable GET_VENDOR_CODE(string destinationName)
        {
            RfcRepository rfcRepository = GetRepository(destinationName);
            IRfcFunction rfcFunction = rfcRepository.CreateFunction("Z__GET_VENDOR_CODE");
            rfcFunction.Invoke(rfcDestination);
            IRfcTable tableVendorDetails = rfcFunction.GetTable("GT_VENDORCODE_TAB");
            DataTable dt = new DataTable();
            ConvertToDotNetTable(tableVendorDetails, dt);
            return dt;
        }

// Getting Data from RFC passing string params, and invoke from Table ==> Structure, but return as Table

        public DataTable Get__BANKCODE_Data(string destinationName,string strBankCode)
        {
            RfcRepository rfcRespository = GetRepository(destinationName);
            IRfcFunction rfcFunction = rfcRespository.CreateFunction("Z__GET_BANKCODE_RET");
            IRfcTable t_items = rfcFunction.GetTable("BANK_COUNTRY_KEY");
            RfcStructureMetadata am = rfcRespository.GetStructureMetadata("ZBNKA_BANKS");
            IRfcStructure articol = am.CreateStructure();
            articol.SetValue("ZBANKS",strBankCode);
            t_items.Append(articol);
            rfcFunction.Invoke(rfcDestination);
            IRfcTable tableBankCode = rfcFunction.GetTable("GT_BNKA");
            DataTable dt = new DataTable();
            ConvertToDotNetTable(tableBankCode, dt);
            return dt;
        }


       //Insert data into One table

public void test(string destinationName)
        {
            try
            {
         RfcRepository rfcRepository = GetRepository(destinationName);
        IRfcFunction bapiTEST = rfcRepository.CreateFunction("Z__SET_PAYMENT_PLAN");
             IRfcTable tblInput = bapiTEST.GetTable("GT_CPP");

             RfcStructureMetadata metaData = rfcRepository.GetStructureMetadata("Z_CPP_TAB");

             IRfcStructure tblInputSt = metaData.CreateStructure();

             tblInputSt.SetValue("SCHONO", "10000001");

             tblInputSt.SetValue("COUTY", "MSY");
             tblInputSt.SetValue("BANKL", "BR");
             tblInputSt.SetValue("ENDDT", "2015-01-01");
    tblInput.Append(tblInputSt);

 IRfcStructure tblInputSt1 = metaData.CreateStructure();


tblInputSt1.SetValue("SCHONO", "11200000");

tblInputSt1.SetValue("COUTY", "MSY");
tblInputSt1.SetValue("BANKL", "BR");
tblInputSt1.SetValue("ENDDT", "2015-01-01");
tblInput.Append(tblInputSt1);
   
RfcSessionManager.BeginContext(rfcDestination);
bapiTEST.Invoke(rfcDestination);
IRfcTable t_items = bapiTEST.GetTable("LOG");
        List<string> lstReturn = new List<string>();
if (t_items.RowCount > 0)
{
   for (int i = 0; i < t_items.RowCount; i++)
   {
       lstReturn.Add(t_items[i].GetString(0));
   }
      }

RfcSessionManager.EndContext(rfcDestination)

   
            }
            catch (RfcCommunicationException ex)
            {
            }
            catch (RfcLogonException ex)
            {
                // user could not logon...
            }
            catch (RfcAbapRuntimeException ex)
            {
                // serious problem on ABAP system side...
            }
            catch (RfcAbapBaseException ex)
            {
                // The function module returned an ABAP exception, an ABAP message
                // or an ABAP class-based exception...
            }
        }

              //Insert data into mutiple table using Changing RFC Param

        public void SET__SCHOLAR_MASTER(string destinationName, DataSet ds)
        {
            try
            {

            

            object obj = null;
            RfcRepository rfcRepository = GetRepository(destinationName);
            IRfcFunction rfcFunction = rfcRepository.CreateFunction("Z__SET_SCHOLAR_MASTER");
            foreach (DataTable dt in ds.Tables)
            {
            IRfcTable tblGTCPP = rfcFunction.GetTable(dt.TableName);
            string strTblStName = string.Empty;
            if (dt.TableName.Contains("GT_ZT"))
            {
                strTblStName = dt.TableName.Replace("GT_Z", "");
            }
            //GT_ZB011
            if (dt.TableName.Contains("GT_ZB"))
            { 
                strTblStName = dt.TableName.Replace("GT_", "");
            }
            RfcStructureMetadata metaData = rfcRepository.GetStructureMetadata(strTblStName);
            IRfcStructure tblInputStructM = metaData.CreateStructure();

            

                foreach (DataRow dr in dt.Rows)
                {
                    IRfcStructure tblInputStruct = metaData.CreateStructure();
                    foreach (DataColumn column in dt.Columns)
                    {
                        obj = dr[column];
                        tblInputStruct.SetValue(column.ToString(), obj);

                    }

                    
                    tblGTCPP.Append(tblInputStruct);
                }
            }

            RfcSessionManager.BeginContext(rfcDestination);

            rfcFunction.Invoke(rfcDestination);
            IRfcTable tblLog = rfcFunction.GetTable("LOG");

            List<string> lstReturn = new List<string>();

            if (tblLog.RowCount > 0)
            {
                for (int i = 0; i < tblLog.RowCount; i++)
                {
                    lstReturn.Add(tblLog[i].GetString(0));

                }


            }

            RfcSessionManager.EndContext(rfcDestination);
            }
            catch (RfcCommunicationException ex)
            {
            }
            catch (RfcLogonException ex)
            {
                // user could not logon...
            }
            catch (RfcAbapRuntimeException ex)
            {
                // serious problem on ABAP system side...
            }
            catch (RfcAbapBaseException ex)
            {
                // The function module returned an ABAP exception, an ABAP message
                // or an ABAP class-based exception...
            }
        }
      
// Build Repository        
        private RfcRepository GetRepository(string destinationName)
        {
            rfcDestination = null;
            rfcDestination = RfcDestinationManager.GetDestination(destinationName);
            RfcRepository rfcRepository = rfcDestination.Repository;
            return rfcRepository;
        }

// Convert to DotNet DataTable

        private static void ConvertToDotNetTable(IRfcTable tableVendorDetails, DataTable dt)
        {
            for (int i = 0; i < tableVendorDetails.ElementCount; i++)
            {
                RfcElementMetadata metadata = tableVendorDetails.GetElementMetadata(i);
                dt.Columns.Add(metadata.Name);
            }
            foreach (IRfcStructure row in tableVendorDetails)
            {
                DataRow dr = dt.NewRow();
                for (int elem = 0; elem < tableVendorDetails.ElementCount; elem++)
                {
                    RfcElementMetadata metadata = tableVendorDetails.GetElementMetadata(elem);
                    
                }
                dt.Rows.Add(dr);
            }
        }

        


        public DataSet buildTestChangeDataSet()

        {
            DataSet ds = new DataSet();

            // Table GT_ZT9B01

            DataTable dtGT_ZT9B01 = new DataTable("GT_ZT9B01");
             
                     
            dtGT_ZT9B01.Columns.Add("MANDT", typeof(string));
            dtGT_ZT9B01.Columns.Add("DISPCD", typeof(int));
            dtGT_ZT9B01.Columns.Add("DISPNAME", typeof(string));
            dtGT_ZT9B01.Columns.Add("COURSE", typeof(int));
            dtGT_ZT9B01.Columns.Add("CATEG", typeof(int));

            DataRow drGT_ZT9B01 = dtGT_ZT9B01.NewRow();

            
            drGT_ZT9B01["MANDT"] = "2014-01-01";
            drGT_ZT9B01["DISPCD"] = 2;
            drGT_ZT9B01["DISPNAME"] = "BR";
            drGT_ZT9B01["COURSE"] = 2;
            drGT_ZT9B01["CATEG"] = 3;

            dtGT_ZT9B01.Rows.Add(drGT_ZT9B01);


            drGT_ZT9B01 = dtGT_ZT9B01.NewRow();


            drGT_ZT9B01["MANDT"] = "2014-02-02";

            drGT_ZT9B01["DISPCD"] = 21;
            drGT_ZT9B01["DISPNAME"] = "BR";
            drGT_ZT9B01["COURSE"] = 2;
            drGT_ZT9B01["CATEG"] = 3;

            dtGT_ZT9B01.Rows.Add(drGT_ZT9B01);



            // Table GT_ZT9B08

            DataTable dtGT_ZT9B08 = new DataTable("GT_ZT9B08");
           // dtGT_ZT9B08.DisplayExpression = "T9B08";

            dtGT_ZT9B08.Columns.Add("CLIENT", typeof(string));

            dtGT_ZT9B08.Columns.Add("LEVSD", typeof(int));
            dtGT_ZT9B08.Columns.Add("DESCRIP", typeof(string));
           
            DataRow drGT_ZT9B08 = dtGT_ZT9B08.NewRow();

            drGT_ZT9B08["CLIENT"] = "111";

            drGT_ZT9B08["LEVSD"] = 2;
            drGT_ZT9B08["DESCRIP"] = "BR1";
            
            dtGT_ZT9B08.Rows.Add(drGT_ZT9B08);

            drGT_ZT9B08 = dtGT_ZT9B08.NewRow();


            drGT_ZT9B08["CLIENT"] = "112";

            drGT_ZT9B08["LEVSD"] = 21;
            drGT_ZT9B08["DESCRIP"] = "BR2";

            dtGT_ZT9B08.Rows.Add(drGT_ZT9B08);


            // adding tables into dataset


            ds.Tables.Add(dtGT_ZT9B08);

            ds.Tables.Add(dtGT_ZT9B01);
             
            return ds;
        
        }
    }

}

6. At Program file


            string destinationConfigName = "DEV";
            IDestinationConfiguration destinationConfig = null;
            

             SAPConntInterface objSAPConntInterface = new  SAPConntInterface();

            objSAPConntInterface.TestConnection(destinationConfigName);
           objSAPConntInterface.GET__VENDOR_CODE(destinationConfigName);         
           objSAPConntInterface.Get__BANKCODE_Data(destinationConfigName, "MYS");
           objSAPConntInterface.test(destinationConfigName);
           
          DataSet ds=  objSAPConntInterface.buildTestChangeDataSet();

          objSAPConntInterface.SET__SCHOLAR_MASTER(destinationConfigName, ds);

Tuesday, April 14, 2015

Read Data from Excel in C-Sharp


public DataTable ReadExcelData()
        {
            string strFilePath=@"D:\SampleFolder\Sample.xls";
            DataTable dt=new DataTable();
            string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=Yes;'";
            string strQuery = "select * from [Sheet1$] where ColumnName='No'";
            using (OleDbConnection connection = new OleDbConnection(con))
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand(strQuery, connection);

                using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter())
                {
                    dbAdapter.SelectCommand = command;
                    command.Connection = connection;
                 DataSet   resultsDataSet = new DataSet();
                   
                    {
                        dbAdapter.Fill(resultsDataSet);
                        dt = resultsDataSet.Tables[0];
                    }
                 
                }
            }
            return dt;
        }

Convert WordDocument to PDF using C-Sharp


// Create a new Microsoft Word application object
               MSWord.Application word = new MSWord.Application();

               // C# doesn't have optional arguments so we'll need a dummy value
               object oMissing = System.Reflection.Missing.Value;

               // Get list of Word files in specified directory
               DirectoryInfo dirInfo = new DirectoryInfo(path);
               FileInfo[] wordFiles = dirInfo.GetFiles(@"D:\HtmlFolder\Sample.doc");

               word.Visible = false;
               word.ScreenUpdating = false;

               foreach (FileInfo wordFile in wordFiles)
               {
                   // Cast as Object for word Open method
                   Object filename = (Object)wordFile.FullName;

                   // Use the dummy value as a placeholder for optional arguments
                   MSWord.Document doc = word.Documents.Open(ref filename, ref oMissing,
                       ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                       ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                       ref oMissing, ref oMissing, ref oMissing, ref oMissing);
                   doc.Activate();

                   object outputFileName = wordFile.FullName.Replace(".doc", ".pdf");
                   object fileFormat = MSWord.WdSaveFormat.wdFormatPDF;

                   // Save document into PDF Format
                   doc.SaveAs(ref outputFileName,
                       ref fileFormat, ref oMissing, ref oMissing,
                       ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                       ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                       ref oMissing, ref oMissing, ref oMissing, ref oMissing);

                   // Close the Word document, but leave the Word application open.
                   // doc has to be cast to type _Document so that it will find the
                   // correct Close method.              
                   object saveChanges = MSWord.WdSaveOptions.wdDoNotSaveChanges;
                   ((MSWord._Document)doc).Close(ref saveChanges, ref oMissing, ref oMissing);
                   doc = null;
               }

               // word has to be cast to type _Application so that it will find
               // the correct Quit method.
               ((MSWord._Application)word).Quit(ref oMissing, ref oMissing, ref oMissing);
               word = null;

Convert HTML to WordDocument in C-Sharp


string path = @"D:\HtmlFolder\";
              object filename1 = path + "Sample.Html";
              object oMissing = System.Reflection.Missing.Value;
              object readOnly = false;
              object oFalse = false;

              MSWord.Application oWord = new MSWord.Application();
              MSWord.Document oDoc = new MSWord.Document();
              oDoc = oWord.Documents.Add(ref oMissing, ref oMissing, ref oMissing, ref oMissing);
              oWord.Visible = false;

              oDoc = oWord.Documents.Open(ref filename1, ref oMissing, ref readOnly, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);

              filename1 = path + "Sample.doc";
              object fileFormat = MSWord.WdSaveFormat.wdFormatDocument;
              oDoc.ActiveWindow.View.Type = MSWord.WdViewType.wdPrintView;
              oDoc.PageSetup.Orientation = MSWord.WdOrientation.wdOrientLandscape;
              oDoc.SaveAs(ref filename1, ref fileFormat, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);

              oDoc.Close(ref oFalse, ref oMissing, ref oMissing);
              oWord.Quit(ref oMissing, ref oMissing, ref oMissing);

Download HTML Page Programmatically C#


using (WebClient client = new WebClient()) // WebClient class inherits IDisposable
               {
                   ServicePointManager.ServerCertificateValidationCallback = new System.Net.Security.RemoteCertificateValidationCallback(delegate { return true; });
// If credentials is required use this below line
                   client.Credentials = new System.Net.NetworkCredential("UserId", "Password", "DomainName");

                   // you can get the file content without saving it:
                   strHtmlCode = client.DownloadString(htmlDisplyFormUrl);

                   FileStream fStream = new System.IO.FileStream(@"D:\HtmlFolder\Sample.Html", System.IO.FileMode.Create);
                   byte[] b = System.Text.Encoding.UTF8.GetBytes(strHtmlCode);
                   fStream.Write(b, 0, b.Length);
                   fStream.Close();
                   //...
               }

Overwrite the values in New/Edit Form using PreSaveMethod in SharePoint


function PreSaveItem()
{

var titleid = document.querySelector("input[title='Sitting DateTime']").id;
var calval=document.getElementById(titleid).value;
var calYear=calval.split('/')[2];
var calMonth=calval.split('/')[0];
var YearID= document.querySelector("input[title='Year']").id;
document.getElementById(YearID).value=calYear;
var mnth='';
switch (calMonth)
{
case '1':
  mnth="January";
  break;
case '2':
 mnth="February";
  break;
case '3':
  mnth="March";
  break;
case '4':
  mnth="April";
  break;
case '5':
  mnth="May";
  break;
case '6':
  mnth="June";
  break;
case '7':
  mnth="July";
  break;

case '8':
  mnth="August";
  break;

case '9':
  mnth="September";
  break;

case '10':
  mnth="October";
  break;
case '11':
  mnth="November";
  break;

case '12':
  mnth="December";
  break;


}
var MonthID= document.querySelector("input[title='Month']").id;

document.getElementById(MonthID).value=mnth;
return true;
}

How to Hide the fields at Edit Form in SharePoint using jQuery


$(document).ready(function() {
/*For TextBox*/
         var hYear= document.querySelector("input[title='Year']").id;
/*For Choice Field*/
                      var hCategory= document.querySelector("select[title='Division Category']").id;
                     (((document.getElementById(hYear).parentNode).parentNode).parentNode).style.display="none";
                                 
                                    (((document.getElementById(hCategory).parentNode).parentNode).parentNode).style.display="none";
                                   });