LSTANCZYK
10/17/2014 - 3:10 PM

ExecuteQueryWithMetaDataSample.cs


namespace WpfApplication1
{
  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Windows;

  using DevExpress.Xpo;
  using DevExpress.Xpo.DB;

  public partial class MainWindow : Window
  {
    public MainWindow()
    {
      InitializeComponent();
      DataContext = new DataSource();
    }
  }

  public class DataSource
  {
    public DataSource()
    {
      Setup();
    }

    public XPDataView Data { get; private set; }

    private void Setup()
    {
      var connectionString = MSSqlConnectionProvider.GetConnectionString("sever", "user", "passwd", "database");
      var dataLayer        = XpoDefault.GetDataLayer(connectionString, AutoCreateOption.SchemaAlreadyExists);

      using (var uow = new UnitOfWork(dataLayer))
      {
        var sql    = "SELECT Id, Name FROM BookGenre WHERE Name = @Name";
        var dbData = uow.ExecuteQueryWithMetadata(sql, new[] { "Name" }, new[] { "Computer" });

        Data = new XPDataView().FillAndLoad(dbData.ResultSet);
      }
    }
  }

  public static class XPDataViewExtensions
  {
    public static XPDataView FillProperties(this XPDataView self, SelectStatementResult schemaResult)
    {
      foreach (var row in schemaResult.Rows)
      {
        var propName = row.Values[0] as string;
        var propType = DBColumn.GetType((DBColumnType)Enum.Parse(typeof(DBColumnType), row.Values[2] as string));

        self.AddProperty(propName, propType);
      }

      return self;
    }

    public static XPDataView FillAndLoad(this XPDataView self, SelectStatementResult[] results)
    {
      self.FillProperties(results[0])
          .LoadData(new SelectedData(results[1]));

      return self;
    }

    public static XPDataView FillAndLoad(this XPDataView self, Session sess, string sql)
    {
      return self.FillAndLoad(sess.ExecuteQueryWithMetadata(sql).ResultSet);
    }
  }
}