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
- SAP Managed Tags:
4 Comments
