Akal Tech RSS Feed

Topic: How do I add columns dynamically?

Thu, 04 Sep 2008 15:44:40 GMT

Topic Description: Your product looks really good but I have a scenario that I am not sure if it can handle or not. I have a few tables that the "end user" can customize by adding custom columns. We are able to execute alter statements without any problem, and have the meta data for custom columns in xml files. What I would like to do is somehow tell the Quick Objects to include the custom columns in the Find methods so I could get a dataset with the custom columns included. This needs to happen at runtime and it is not possible to regen the code.

I have looked high and low for this type of functionality but no product so far can handle this. So can you do this?

Cheers!
Ryan

Replies: 4
Last Reply: Thu, 04 Sep 2008 15:44:40 GMT
Reply By: Ryan Aljets

Reply: Re: How do I add columns dynamically?

Thu, 04 Sep 2008 15:44:40 GMT

Thanks for the fast reply and sample code! Works great and it is easy!

Cheers :)
Ryan

Reply: Re: How do I add columns dynamically?

Tue, 02 Sep 2008 18:29:56 GMT

Here is the code that shows how to add validation to a field:

StringField sf = new StringField(this, xn.Attributes["Name"].Value); 
if (xn.Attributes["Required"].Value == "True") 
{ 
  new RequiredFieldValidator(sf, true); 
} 
if (xn.Attributes["RegularExpression"] != null) 
{ 
  RegularExpressionFieldValidator emValidator = new RegularExpressionFieldValidator(sf, true); 
  emValidator.Expression = xn.Attributes["RegularExpression"].Value; 
} 

If you need to access the validators at a later stage you can simply use the Validators collection on an instance of a field.

Thanks,
Ish

Quick Objects Support

Reply: Re: How do I add columns dynamically?

Tue, 02 Sep 2008 05:33:38 GMT

WOW! This looks like exactly what we need!!

I will give it a try but have one more question. We've got close to 50 tables that we are allowing the end user to add custom fields. If I understand correctly I would need to write this code for each of the tables or is there an easier way? Its no big deal as being able to do this is a big deal for our project.

Since you mentioned it can you also show how I can add validation to these dynamic fields?
Cheers!
Ryan

Reply: Re: How do I add columns dynamically?

Mon, 01 Sep 2008 23:04:40 GMT

The scenario you have mentioned can be easily handled. See the following link for information on how you can dynamically specify schema:

http://www.akaltech.com/Views/Forum/T/Dynamically_specify_object_schema/BPT/5/PT/5/APT/20/1/Topic.aspx

The above approach can also be mixed with a class that is generated. In your scenario if you have a Customers table that is customized by your end user you can capture the Customers object initialized event or override the method and loop through your xml nodes that contain field definition and add fields dynamically to the Customers object. The dynamically added fields are not only available in Find calls but are available to all methods and these fields can be included in Insert, Update methods etc.

foreach(XmlNode xn in MyFields.Nodes) 
{ 
   if (xn.Attributes["Type"].Value == "String") 
   { 
      new StringField(this, xn.Attributes["Name"].Value); 
      // Each field constructor has multiple overloads allow you to pass the instance of the business object and the name of the field.
   } 
   else (xn.Attributes["Type"].Value = "Integer");
   { 
      new Int32Field(this, xn.Attributes["Name"].Value); 
   } 
} 
 

The above can be done at any stage as per your requirement. Once you have added the fields you can access them like this:

using (Customers c = new Customers()) 
{ 
    c.ObjectMode = ObjectModes.Save; 
    c.CustomerID.Value = 1; 
    c.FirstName.Value = TextBox_FirstName.Text; 
    // Now if you want to access a dynamically created field you need to know the name 
    // and the code you will write: 
    c.Fields[myCustomFieldName1].Value = TextBox_Custom1.Text; 
    c.Fields[myCustomFieldName2].Value = TextBox_Custom2.Text; 
     
    if (c.Update()) 
    { 
        // tell user that changes saved 
    } 
} 

You can even include such a custom field in search criteria, sort or group by clause. Even field validators can be added to the dynamically created fields :)

Hope this is helpful, if you need any further help please don't hesitate to contact us.

Thanks,
Ish

Quick Objects Support