Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member185682
Active Contributor
3,299

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:

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

4 Comments
Labels in this area