Adding param values to an InsertStatement if fields does not exsist in DataTable

Data Abstract
1

This is DataAbstract for .NET

Say I have the following InsertStatement and a BusinessProcessor that uses it.

INSERT
INTO [v_dgPric]
([s_id], [s_creid], [s_credate], [s_chgid], [s_chgdate],
[p_text], [p_acttype], [p_dep], [p_actrefid], [p_costprice])
VALUES
(:Id, :CreateBy, :CreateDate, :ChangeBy, :ChangeDate,
:Description, :ActivityTypeId, :Department, :ActivityId,
:Costprice)

I’ve added a BeforeProcessChange event handler and in it I have:

if (aEA.DeltaChange.Type == ChangeType.Insert)
{
…
aEA.DeltaChange.NewValues[“CreateBy”] = Convert.ToInt32(Session[“SID”]);
aEA.DeltaChange.NewValues[“CreateDate”] = DateTime.Now;
aEA.DeltaChange.NewValues[“ChangeBy”] = Convert.ToInt32(Session[“SID”]);
aEA.DeltaChange.NewValues[“ChangeDate”] = DateTime.Now;
aEA.DeltaChange.NewValues[“Department”] = Convert.ToInt32(Session[“DEPID”]);

This works great, but I would like to add these values to inserted record without exposing the fields through the DataTable so a minimum of info is sent to the client.

Would it be possible to set these values another place perhaps as parameter values to the InsertStatement so these fields can be removed from the schema?

Thanks!

2

Hello.

You can use solution described in the article http://blogs.remobjects.com/blogs/antonk/2011/09/07/p3034. According to it you need to define your InsertStatement as follows:

INSERT
  INTO [v_dgPric]
    ([s_id], [s_creid], [s_credate], [s_chgid], [s_chgdate], 
     [p_text], [p_acttype], [p_dep], [p_actrefid], [p_costprice])
  VALUES
    (:Id, {Macro_CreateBy}, {Macro_CreateDate}, {Macro_ChangeBy}, {Macro_ChangeDate}, 
     :Description, :ActivityTypeId, {Macro_Department}, :ActivityId, 
     :Costprice)

and then implement it in UnknownSqlMacroIdentifier event handler:

private void DataService_UnknownSqlMacroIdentifier(object sender, UnknownSqlMacroIdentifierEventArgs e)
{
   if (e.Identifier == "Macro_CreateBy")
   {
      e.Value = this.Connection.DatabaseProfile.QuoteIdentifier((string)Session["SID"]);
      e.Processed = true;
   }
   if (e.Identifier == "Macro_CreateDate")
   {
      . . .
   }
}

Hope this helps.

3

Ah, perfect! I’ll try this right away.

Thanks!