Using XML for ItemsSource

[Richard Harrigan]

Hi,

I used the GridView example "Various Data Sources" as a guide in loading xml data into a Grid View. That is fill a dataset and then executes a DataSet.GetXml() to get the xml.  This works great except that all the columns are of system type string. Can you tell me how to get the correct system data types into the GridView?

Thanks
Rich

[Dimitrina]

Hello Rich,

You could assign the correct DataType for the columns manually. For example if you would like the type to be System.Int32, then the code would be similar to this one:

foreach (var column in this.clubsGrid.Columns)
{
    (column as GridViewBoundColumnBase).DataType = typeof(Int32);
}

Regards,
Didie
the Telerik team

[Richard Harrigan]

Hi Didie,

I seem to be having some trouble with this.  I tried a simple query with only one column,  a date field.  The foreach loop does not execute because it says the count is zero.  However all the rows display in the grid.  Is it to soon for the ItemsSource to be loaded?  Also should I  be creating the columns programmatically instead of auto ( I am using auto).  Could you send me a code snippet that creates a few columns programmatically that includes defining the system datatype.?

Thanks
Rich

myData = oaHelper.GetDataSheet(SqlQueryResult, conn, ref this.Line1, ref this.Line2);

gridQueryResult.ItemsSource = myData.Data;

foreach (var column in this.gridQueryResult.Columns)

{

     (column as GridViewBoundColumnBase).DataType = typeof(DateTime);

}

[Dimitrina]

Hello Rich,

If your columns are auto generated, then you can execute the suggested code when the DataLoaded event is raised.
If your columns are manually generated, then you can check this help article for a reference.

Kind regards,
Didie
the Telerik team

[Richard Harrigan]

Hi Didie

I tried with auto generated columns but there were a least two problems.  In looking at the gridview in debugging there are a number of datatype properties and only the one I set had the changed type.  What happents when filtering that column.  Also I tested with a DateTime column but it did not format properly on the screen.  It still looked like the text string generated by GetXml.  Maybe the text string generated by GetXml is the problem? 

I  then tried generating the columns programmatically and set auto generate to false (See all relevant code below).In this test nothing is displayed.  Looking at it's ItemSource everything seems to be ok. It must be a binding problem with A Dynamic Class Object? 

The data displays when auto generate is true but does not display when auto generate is false and the columns are created manually.  The same data is being set in  the gridview ItemsSource.

Thanks
Rich
 

// Get Xml
           
MyData myData = new MyData();
myData = oaHelper.GetDataSheetXml(sqlStatement, conn, ref this.Line1, ref this.Line2);
gridQueryResult = new RadGridView();

foreach (MySchemaColumn col in myData.Schema)
{
       GridViewDataColumn dataColumn = new GridViewDataColumn();
        dataColumn.DataMemberBinding = new Binding(col.ColumnName);
        dataColumn.Header = col.ColumnName;
        dataColumn.UniqueName = col.ColumnName;
        dataColumn.DataType = Type.GetType(col.DataType);
        gridQueryResult.Columns.Add(dataColumn);
}

gridQueryResult.ItemsSource = myData.Data;

======================================================================================

public MyData GetDataSheetXml(StringBuilder sqlForQuery, ConnectionInfo connInfo, ref TextBlock line1, ref TextBlock line2)
{
 string CmdString = string.Empty;
        string xmlData = string.Empty;
        string xmlSchema = string.Empty;
        MyData myData = new MyData();
        XmlDocument doc;
        ObservableCollection<MySchemaColumn> mySchemaColumns = new ObservableCollection<MySchemaColumn>();

        using (SqlConnection con = new SqlConnection(connInfo.ConnString))
        {
         string strMsg = string.Empty;
                DataSet dsData = new DataSet("Rows");
                DataSet dsSchema = new DataSet("Columns");
                try
                {
                    SqlCommand cmd = new SqlCommand(sqlForQuery.ToString(), con);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(dsData);
                   
                    foreach (DataColumn col in dsData.Tables[0].Columns)
                    {
                        MySchemaColumn mySchemaColumn = new MySchemaColumn();
                        mySchemaColumn.ColumnName = col.ColumnName;
                        mySchemaColumn.DataType = col.DataType.FullName;
                        mySchemaColumns.Add(mySchemaColumn);
                    }

                    dsData.Tables[0].TableName = "Row";
                    xmlData = dsData.GetXml();
                    myData.Status = 0;
                }
                catch (SqlException e)
                {
    ...
                }
                strMsg = String.Format("{0} row(s) affected", dsData.Tables[0].Rows.Count);
                line1.Text = strMsg;
                line2.Text = string.Empty;

                myData.Schema = mySchemaColumns;

                var xDoc = XDocument.Parse(@"<?xml version=""1.0"" encoding=""utf-8"" ?> " + xmlData);
                myData.Data = new ObservableCollection<dynamic>(from element in xDoc.Descendants("Row") select new MyDataRow(ToDictionary(element)));
                return myData;
            }
        }

========================================================================================

public partial class MySchemaColumn
{
        public string ColumnName { get; set; }
        public string DataType { get; set; }

        public MySchemaColumn()
        {
        }
}

=======================================================================================

public static IDictionary<string, object> ToDictionary(XElement element)
{
 var dict = new Dictionary<string, object>();
        foreach (var e in element.Elements())
        {
         dict.Add(e.Name.LocalName, e.Value);
        }
        return dict;
}

=====================================================================================

<telerik:RadGridView
 Name="gridQueryResult" Grid.Row="1"
        ShowGroupPanel="true"
        GridLinesVisibility="Both"
        ShowColumnHeaders="True"
        BorderThickness="0"
        AutoGenerateColumns="False"
        CanUserFreezeColumns="False"                           
        RowIndicatorVisibility="Visible">
</telerik:RadGridView>

==========================================================================

public partial class MyData
{
 public Int32 Status { get; set; }
        public ObservableCollection<MySchemaColumn> Schema { get; set; }
        public ObservableCollection<dynamic> Data { get; set; }

        public MyData()
        {
        }
}

===========================================================================

public class MyDataRow : DynamicObject, INotifyPropertyChanged
{
        readonly IDictionary<string, object> data;

        public MyDataRow()
        {
            data = new Dictionary<string, object>();
        }

        public MyDataRow(IDictionary<string, object> source)
        {
            data = source;
        }

        public override IEnumerable<string> GetDynamicMemberNames()
        {
            return data.Keys;
        }

        public override bool TryGetMember(GetMemberBinder binder, out object result)
        {
            result = this[binder.Name];

            return true;
        }

        public override bool TrySetMember(SetMemberBinder binder, object value)
        {
            this[binder.Name] = value;

            return true;
        }

        public object this[string columnName]
        {
            get
            {
                if (data.ContainsKey(columnName))
                {
                    return data[columnName];
                }

                return null;
            }
            set
            {
                if (!data.ContainsKey(columnName))
                {
                    data.Add(columnName, value);

                    OnPropertyChanged(columnName);
                }
                else
                {
                    if (data[columnName] != value)
                    {
                        data[columnName] = value;

                        OnPropertyChanged(columnName);
                    }
                }
            }
        }

        private void OnPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }

        #region INotifyPropertyChanged Members

        public event PropertyChangedEventHandler PropertyChanged;

        #endregion
}

==========================================================================================

 

 

[Dimitrina]

Hi Rich,

Actually the bound value should be of type Object, so that the DataType set for the column to be respected and the value to be interpreted as it is supposed to. I am sorry for not being clear about that. 

I would suggest you check the "Various Data Sources" WPF Demo and check how the XML data is bound. I hope it will help you.

Kind regards,
Didie
the Telerik team

[Richard Harrigan]

Hi Didie

I originally started this thread saying that I used "Various Data Sources"  as my basis (see my first post).  That example does not use a Dynamic Class Object.  Would it be possible to get a very basic example that would use three columns of string, datetime and int32.  It would also have grouping, filtering and sorting properties set.  I have tried but I cannot get it to bind and I am burning time.  I'm hoping you can provide this as it is critical to what I am doing.  This is handled very nicely with a DataSet but I am interested in getting a companion project for a Windows 8 store app to display dynamically generated adhoc queries and want to go without a DataSet from now on.   

Thanks
Rich

[Richard Harrigan]

Hi,

PLEASE CANCEL THIS REQUEST.  I figured it out.  Sorry for any inconvenience.

Rich