Referencing External Data with Data Sync 3.0

4 January 2011

This example shows how you can reference data which is held in an external database from within the new Dynamic Column support of Data Sync 3.0.

This is a rather trivial example however it shows the concept.

Override the “Setup” method and implement the data loading in this method, for this example were creating a Dictionary of Email Addresses keyed on the Row ID. Setup is only called once during the load process of the Data Source so it's a good place to load this data.

The Settings object is a dictionary of settings configured for the Data Source so we can easily pull out the database connection string from this list.

The Property Email4 returns the Email address from the Dictionary rather than the actual Data source. We can even add out own Using statements at the top of the class.

Dynamic Columns

Our Code for the Project

using System.Data.OleDb;
class DataSourceRowOverride : DataSourceRowInternal
{
    public string Email4 { get { return Lookup[ID]; } }
    
    private Dictionary<int, string>; Lookup = new Dictionary<int, string>();
    
    public override void Setup() 
    {
        string connectionString = Settings["ConnectionString"];    
        
        using(OleDbConnection c = new OleDbConnection(connectionString)) 
        {
            c.Open();
            
            using(OleDbCommand cmd = new OleDbCommand("SELECT * FROM Clients_D", c)) 
            {
                OleDbDataReader dbReader = cmd.ExecuteReader();
                while(dbReader.Read())
                    Lookup.Add((int)dbReader["ID"], (string)dbReader["Email"]);
                    
            }    
        }
    }
}

Complete Generated Code

#region Usings
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using Simego.DataSync;
using Simego.DataSync.Interfaces;
#endregion
namespace Simego.DataSync.DynamicColumns_631D8F4B 
{
    using System.Data.OleDb;
    
    class DataSourceRowOverride_631D8F4B : DataSourceRowInternal_631D8F4B
    {
        public string Email4 { get { return Lookup[ID]; } }
        
        private Dictionary<int, string> Lookup = new Dictionary<int, string>();
        
        public override void Setup() 
        {
            string connectionString = Settings["ConnectionString"];    
            
            using(OleDbConnection c = new OleDbConnection(connectionString)) 
            {
                c.Open();
                
                using(OleDbCommand cmd = new OleDbCommand("SELECT * FROM Clients_D", c)) 
                {
                    OleDbDataReader dbReader = cmd.ExecuteReader();
                    while(dbReader.Read())
                        Lookup.Add((int)dbReader["ID"], (string)dbReader["Email"]);
                        
                }    
            }
        }
    }

    #region Generated Code
    class DataSourceRowInternal_631D8F4B : IDataSourceRow 
    {
        public Dictionary<string, string> Settings { get; set; }
        public IDataSourceReader Reader { get; set; }
        public DataRow Row { get; set; }
        public DataTable Table { get { return Row.Table; } }
        public TraceWriter Trace { get; set; }
        public int ID { get { return (int)Row["ID"]; } }
        public string Logo { get { return (string)(Row["Logo"] == DBNull.Value ? null : Row["Logo"]); } }
        public string Email { get { return (string)(Row["Email"] == DBNull.Value ? null : Row["Email"]); } }
        public virtual void Setup() { /* Override Setup to handle any Startup Initialization. */ }
        public virtual void BeginRow( /* Override BeginRow to handle any Initialization on each row. */) { }
        public virtual bool IncludeRow() { /* Override IncludeRow to filter out rows. */ return true; }
        public virtual void EndRow( /* Override EndRow to handle any Cleanup on each row. */ ) { } 
    }
    #endregion
}

| |