Hello guys, in the day by day of development, any code that is written more than one time, should receive an special attention. We should try abstracting them, so that, it doesn’t need be rewritten again.

Then I will share with you some classes that I created to facilitate my day by day with development in the SAP B1.

In this first text, I will show one class that implements the basics database operations as add, update, delete and “get by key” for an User Defined Table(UDT) of type not object.

Notes:

1. To understand better the operation of the class presented, you should be familiarized with the classes and methods presented in the system.reflection namespace. See here: System.Reflection Namespace

2. This class is not prepared to work with UDT of type “Master Data (Rows)” or “Documents (Rows)”.

3. For development I use .NET Framework 4.5 and VS 2015. Maybe some functionalities can be unavailable in older versions.

The class that I call UDTModelBase(explanation in comments of the class):


using SAPbobsCOM;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleSample
{
    /// <summary>
    /// Note¹: The property in your class should have the same name that your field in database and the same type.
    /// Note²: Where you see Program.oCompany replaces with your Company object
    /// </summary>
    public abstract class UDTModelBase
    {
        #region Private properties
        /// <summary>
        /// Prefix of SAP database fields
        /// (!) Note: SAP added automatically "U_" in front of name of all user defined fields.
        /// </summary>
        private const string PREFIX_FIELD = "U_";
        #endregion
        #region Public Properties
        /// <summary>
        /// Code of your object, should be unique.
        /// (!) Note: SAP creates this field automatically when you create a user defined table.
        /// </summary>
        public int Code { get; private set; }
        /// <summary>
        /// Name of your object, should be unique
        /// (!) Note: SAP creates this field automatically when you create a user defined table.
        /// </summary>
        public string Name { get; private set; }
        #endregion
        #region Private Methods
        /// <summary>
        /// Gets the value from an property, and converts in a sap type field
        /// </summary>
        /// <param name="type">Type of SAP field</param>
        /// <param name="property">Property that should be extracted the value</param>
        /// <returns></returns>
        private dynamic getValue(BoFieldTypes type, PropertyInfo property)
        {
            switch (type)
            {
                case BoFieldTypes.db_Alpha:
                    if (property.PropertyType.Name.Equals("Boolean"))
                        return property.GetValue(this, null).ToString().Equals("True") ? "Y" : "N";
                    else
                        return property.GetValue(this, null).ToString();
                case BoFieldTypes.db_Numeric:
                    return (int)property.GetValue(this, null);
                case BoFieldTypes.db_Float:
                    return Convert.ToSingle(property.GetValue(this, null));
                case BoFieldTypes.db_Date:
                    return property.GetValue(this, null);
                default:
                    break;
            }
            return null;
        }
        /// <summary>
        /// Set the value to a property
        /// </summary>
        /// <param name="value">Value that should be set to the property</param>
        /// <param name="prop">property that receives the value</param>
        /// <param name="owner">owner instance of the property</param>
        private void setValue(dynamic value, PropertyInfo prop, dynamic owner)
        {
            if (prop.CanWrite)
            {
                if (prop.PropertyType.IsEnum)
                {
                    prop.SetValue(owner, Convert.ChangeType(value, Enum.GetUnderlyingType(prop.PropertyType)), null);
                }
                else if (prop.PropertyType.Name.Equals("Boolean"))
                {
                    prop.SetValue(owner, value.Equals("Y") ? true : false, null);
                }
                else
                {
                    prop.SetValue(owner, Convert.ChangeType(value, prop.PropertyType), null);
                }
            }
            else
            {
                //Code and Name has "private set", and then, they are defined here
                typeof(UDTModelBase).GetProperty(prop.Name).SetValue(this, Convert.ChangeType(value, prop.PropertyType), null);
            }
        }
        /// <summary>
        /// Get a new code to the object
        /// </summary>
        /// <returns>Returns a new code</returns>
        private int getNewCode()
        {
            int maxChave = 1;
            Recordset oRs = null;
            try
            {
                //Do a query to convert Code in an int and get the max value present in the table.
                string sql = @"SELECT MAX(CONVERT(INT, Code)) FROM [@" + this.TableName() + "]";
                oRs = Program.oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
                oRs.DoQuery(sql);
                if (oRs != null && !oRs.EoF)
                    maxChave = Convert.ToInt32(oRs.Fields.Item(0).Value);
            }
            finally
            {
                if (oRs != null)
                {
                    Marshal.ReleaseComObject(oRs);
                    oRs = null;
                }
            }
            return maxChave + 1;
        }
        /// <summary>
        /// Creates Default query to use in GetByKey, ListAll
        /// </summary>
        /// <returns></returns>
        private string selectQuery()
        {
            string sql = "SELECT ";
            //walks in each property to get the property name and create the select query
            foreach (PropertyInfo prop in this.GetType().GetProperties())
            {
                if (prop.Name.Equals("Code") || prop.Name.Equals("Name"))
                    sql += prop.Name + ", ";
                else
                    sql += PREFIX_FIELD + prop.Name + ", ";
            }
            sql = sql.Substring(0, sql.Length - 2);
            sql += " FROM [@" + this.TableName() + "]";
            return sql;
        }
        #endregion
        #region Public Methods
        /// <summary>
        /// Table name in SAP that need be managed
        /// </summary>
        /// <returns></returns>
        public abstract string TableName();
        /// <summary>
        /// Add or update a register
        /// </summary>
        /// <returns>true if the action is ok</returns>
        public virtual bool AddOrUpdate()
        {
            SAPbobsCOM.UserTable oUserTable = null;
            bool sucess = true;
            try
            {
                //Instantiate the class
                oUserTable = Program.oCompany.UserTables.Item(this.TableName());
                bool isUpdate = false;
                //if code is different 0, verifies if is an update
                if (this.Code != 0)
                    isUpdate = oUserTable.GetByKey(this.Code.ToString());
                //walks in each property of the class to get the value and set in correspondent field in the table
                foreach (PropertyInfo prop in this.GetType().GetProperties())
                {
                    if (prop.Name.Equals("Code"))
                    {
                        if (!isUpdate) //if is not an update, gets the new code
                        {
                            this.Code = this.getNewCode(); //Sets the new code to Code property
                            oUserTable.Code = this.Code.ToString();
                            this.Name = "K" + oUserTable.Code;
                            oUserTable.Name = this.Name;
                        }
                    }
                    else if (prop.Name.Equals("Name"))
                        continue;
                    else
                    {
                        //convert the value of the property to value compatible with SAP type
                        oUserTable.UserFields.Fields.Item(PREFIX_FIELD + prop.Name).Value = this.getValue(oUserTable.UserFields.Fields.Item(PREFIX_FIELD + prop.Name).Type, prop);
                    }
                }
                int ret = 0;
                if (isUpdate) //if is an update, updates the register
                    ret = oUserTable.Update();
                else //or add the new register
                    ret = oUserTable.Add();
                if (ret != 0)
                {
                    sucess = false;
                    throw new Exception(Program.oCompany.GetLastErrorDescription());
                }
            }
            catch (Exception ex)
            {
                sucess = false;
                throw ex;
            }
            finally
            {
                Marshal.ReleaseComObject(oUserTable);
                oUserTable = null;
                GC.Collect();
            }
            return sucess;
        }
        /// <summary>
        /// Delete a register
        /// </summary>
        /// <returns>true if the action is ok</returns>
        public virtual bool Delete()
        {
            SAPbobsCOM.UserTable oUserTable = null;
            bool sucess = true;
            try
            {
                oUserTable = Program.oCompany.UserTables.Item(this.TableName());
                //Verifies if exist in the table
                if (oUserTable.GetByKey(this.Code.ToString()))
                {
                    //if exists , remove it.
                    if (oUserTable.Remove() != 0)
                    {
                        sucess = false;
                        throw new Exception(Program.oCompany.GetLastErrorDescription());
                    }
                }
            }
            catch (Exception ex)
            {
                sucess = false;
                throw ex;
            }
            finally
            {
                Marshal.ReleaseComObject(oUserTable);
                oUserTable = null;
            }
            return sucess;
        }
        /// <summary>
        /// Loads the register into the object
        /// </summary>
        /// <param name="code">Code of your register</param>
        /// <returns>true if the action is ok</returns>
        public virtual bool GetByKey(int code)
        {
            bool sucess = true;
            Recordset oRs = null;
            try
            {
                oRs = Program.oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
                //Build the query to search in the database
                string sql = this.selectQuery();
                //filter by the code
                sql += " WHERE Code = " + code;
                oRs.DoQuery(sql);
                //In this situation you can too get the values by UserTable.GetByKey and then
                //get values in UserFields.Fields.Item("field").Value and define in properties of your class
                if (!oRs.EoF)
                {
                    //walks in each property and sets the value that was returned from the database
                    foreach (PropertyInfo prop in this.GetType().GetProperties())
                    {
                        if (!prop.Name.Equals("Code") && !prop.Name.Equals("Name"))
                            this.setValue(oRs.Fields.Item(PREFIX_FIELD + prop.Name).Value, prop, this);
                        else
                            this.setValue(oRs.Fields.Item(prop.Name).Value, prop, this);
                    }
                }
                else
                    sucess = false;
            }
            catch (Exception ex)
            {
                sucess = false;
                throw ex;
            }
            finally
            {
                if (oRs != null)
                {
                    Marshal.ReleaseComObject(oRs);
                    oRs = null;
                }
            }
            return sucess;
        }
        /// <summary>
        /// List all registers from the database
        /// </summary>
        /// <returns>Return a list with all registers of this object</returns>
        public virtual List<dynamic> ListAll()
        {
            //Create a dynamic list, in this moment I don't know the type of my object
            List<dynamic> lst = new List<dynamic>();
            Recordset oRs = null;
            try
            {
                oRs = Program.oCompany.GetBusinessObject(BoObjectTypes.BoRecordset);
                //Gets the query without Where clause.
                string sql = this.selectQuery();
                oRs.DoQuery(sql);
                while (!oRs.EoF)
                {
                    //create an instance of the same type of the current class
                    dynamic oInstance = Activator.CreateInstance(this.GetType());
                    //walks in each property and sets the value that was returned from the database
                    foreach (PropertyInfo prop in oInstance.GetType().GetProperties())
                    {
                        //Set the value from the database to the property
                        if (!prop.Name.Equals("Code") && !prop.Name.Equals("Name"))
                            oInstance.setValue(oRs.Fields.Item(PREFIX_FIELD + prop.Name).Value, prop, oInstance);
                        else
                            oInstance.setValue(oRs.Fields.Item(prop.Name).Value, prop, oInstance);
                    }
                    //Adds the new object to our list
                    lst.Add(oInstance);
                    oRs.MoveNext();
                }
            }
            finally
            {
                if (oRs != null)
                {
                    Marshal.ReleaseComObject(oRs);
                    oRs = null;
                    GC.Collect();
                }
            }
            return lst;
        }
        #endregion
    }
}




To demonstrate an example in the practical, I created a console application, that will be attached in the end of this text.(It is not permited add a .rar file, then I will attach all class that I created for this sample).

Using the class:

Creates an UDT as follow:

Table Name: Product.

Type: Not object.

Fields:

Product.PNG

Now I will implement my class of the UDT that I created.

I call my class of ProductModel and it inherits UDTModelBase. After this, you need implement the abstract methods. UDTModelBase has just one abstract method called TableName(), within this, returns your table name, in this case “Product”. Now create your properties as public and with the same name and type of your fields in database. After this , your class should be seems like this:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleSample
{
    public class ProductModel : UDTModelBase
    {
        #region Public Methods
        public override string TableName()
        {
            return "Product";
        }
        #endregion
        #region Public properties
        public string Description { get; set; }
        public Double Price { get; set; }
        public DateTime RegistrationDate { get; set; }
        public bool Active { get; set; }
        #endregion
    }
}




Now, we will test the functionalities presented. In the Program class write some test like this:


static void Main(string[] args)
        {
            try
            {
                if (oCompany == null || !oCompany.Connected)
                {
                    connect();
                }
                if (oCompany != null && oCompany.Connected)
                {
                    try
                    {
                        int code = 0;
                        #region Add
                        ProductModel product = new ProductModel();
                        product.Description = "Cellphone";
                        product.Price = 100.50;
                        product.RegistrationDate = DateTime.Now;
                        product.Active = true;
                        if (product.AddOrUpdate())
                        {
                            Console.WriteLine("Operation completed successfully");
                            code = product.Code;
                        }
                        product = null;
                        #endregion
                        #region Update
                        product = new ProductModel();
                        if (product.GetByKey(200))
                            Console.WriteLine("Register found");
                        else
                            Console.WriteLine("Register not found");
                        if(product.GetByKey(code))
                        {
                            product.Description += " new";
                            if(product.AddOrUpdate())
                            {
                                Console.WriteLine("Operation completed successfully");
                            }
                        }
                        product = null;
                        #endregion
                        #region Delete
                        product = new ProductModel();
                        if(product.GetByKey(code))
                        {
                            if(product.Delete())
                                Console.WriteLine("Operation completed successfully");
                        }
                        #endregion
                        #region ListAll
                        //Inserts some registers in database before run this.
                        product = new ProductModel();
                        List<ProductModel> lst = product.ListAll().Cast<ProductModel>().ToList();
                        foreach(ProductModel p in lst)
                        {
                            Console.WriteLine("Code [" + p.Code + "], Description [" + p.Description + "]");
                        }
                        #endregion
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
            }
            catch
            {
                Console.WriteLine(ex.Message);
            }
        }




I hope that you like this post.

If you liked it, give me your feedback.

If you used it and made some change, please share with others members of this community.

I intend to continue with this series of posts, if this helping other members.

Regards,

Diego

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Andrew May

    Hi Diego.

    I’m contributing to this framework with “MakeUserEntity” class, as such class registers at sab b1 the attributes of productModelclass.

    The object “sapbo” must be replaced by your company conector.

    class MakeUserEntity
        {
            SapBo sapCom;
    
            public MakeUserEntity(SapBo com)
            {
                sapCom = com;
            }
    
            /// <summary>
            /// 
            /// </summary>
            /// <param name="Name"></param>
            /// <param name="Description"></param>
            /// <param name="type">NoObject | MasterData | MasterDataLines | Document | DocumentLines</param>
            /// <returns></returns>
            public Boolean CreateTable(string Name, string Description, BoUTBTableType type)
            {
                UserTablesMD Table = (SAPbobsCOM.UserTablesMD)sapCom.oCompany.GetBusinessObject(BoObjectTypes.oUserTables);
                Table.TableName = Name;
                Table.TableDescription = Description;
                Table.TableType = type;
    
                if (Table.Add() != 0)
                {
                    Table = null;
                    GC.Collect();
                    sapCom.showLastError();
                    return false;
                }
                else
                {
                    Table = null;
                    GC.Collect();
                    return true;
                }
            }
    
    
            /// 
            /// <summary>
            /// ### Var Type = Sap Type, Sub Type ###
            /// String = Alpha, Defult
            /// Int = Numeric
            /// DateTime = Date/Time, Date
            /// Float = Units and Total, Taxe
            /// Price = Units and Total, Double
            /// Total = Units and Total, decimal
            /// </summary>
            /// <param name="TableName"></param>
            /// <param name="Name"></param>
            /// <param name="Description"></param>
            /// <param name="size"></param>
            /// <param name="editsize"></param>
            /// <param name="type">
            /// Var Type    = Sap Type, Sub Type
            /// string      = Alpha, Defult
            /// int         = Numeric
            /// DateTime    = Date/Time, Date
            /// float       = Units and Total, Rate
            /// double      = Units and Total, Price
            /// decimal     = Units and Total, Total
            /// </param>
            /// <returns>Retorna se adicionou coluna ou Não</returns>
            public Boolean CreateFilds(string TableName, string Name, string Description, Type type)
            {
                UserFieldsMD Fild = sapCom.oCompany.GetBusinessObject(BoObjectTypes.oUserFields);
                // Chave criada no PN
                Fild.TableName = TableName;
                Fild.Name = Name;
                Fild.Description = Description;
                
                if(type == typeof(string))
                {
                    Fild.Type = BoFieldTypes.db_Alpha;
                    Fild.Size = 250;
                    Fild.EditSize = 250;
                }
                else if (type == typeof(int))
                {
                    Fild.Type = BoFieldTypes.db_Numeric;
                    Fild.Size = 11;
                    Fild.EditSize = 11;
                }
                else if (type == typeof(DateTime))
                {
                    Fild.Type = BoFieldTypes.db_Date;                
                }
                else if (type == typeof(float))
                {
                    Fild.Type = BoFieldTypes.db_Float;
                    Fild.SubType = BoFldSubTypes.st_Rate;
                }
                else if (type == typeof(double))
                {
                    Fild.Type = BoFieldTypes.db_Float;
                    Fild.SubType = BoFldSubTypes.st_Price;
                }
                else if (type == typeof(decimal))
                {
                    Fild.Type = BoFieldTypes.db_Float;
                    Fild.SubType = BoFldSubTypes.st_Sum;
                }else
                {
                    throw new Exception("Invalid attribute: "+ Name);
                }
    
                if (Fild.Add() != 0)
                {
                    Fild = null;
                    GC.Collect();
                    sapCom.showLastError();
                    return false;
                }
                else
                {
                    Fild = null;
                    GC.Collect();
                    return true;
                }
            }
        }

     

    You must add 3 methods at UDTModelBase class:

    public abstract string TableDescription();
    public abstract BoUTBTableType TableType();
    public void createTable();

    The code follows bellow:

     public abstract string TableDescription();
            /// <summary>
            /// Table Type in sab B1
            /// </summary>
            /// <returns>NoObject = 0 | MasterData = 1 | MasterDataLines = 2 | Document = 3 | DocumentLines = 4</returns>
            public abstract BoUTBTableType TableType();
            public void createTable()
            {
                MakeUserEntity UDT = new MakeUserEntity(Program);
                UDT.CreateTable(TableName(), TableDescription(), TableType());
                UDT = null;
                GC.Collect();
                UDT = new MakeUserEntity(Program);
                bool sucess = true;
    
                foreach (PropertyInfo prop in this.GetType().GetProperties())
                {
                    if(prop.Name.Length > 17)
                        throw new Exception("The number of characters is greater than seventeen");
                    try
                    {
                        UDT.CreateFilds(TableName(), prop.Name, prop.Name,prop.PropertyType);
                    }
                    catch (Exception ex)
                    {
                        Program.showLastError();
                        sucess = false;
                        throw ex;
                    }
                }
            }

     

    It’s needed to instance the code “productModel” and apply “CreateTable” method to make the call and create the chart and it’s columns. I intend to develop the UDO registration in the future.

    Any suggestion is welcome.

    (0) 

Leave a Reply