.Net Zone: 2009年2月

William さんのプロフィール.Net Zoneブログリスト

ツール

ヘルプ

ブログ

概要

並べ替え:

<< 最初へ < 前へ次へ > 最後へ >>

2月20日

ReportViewer with nested tables

Sometimes you may need a report that can not be done easily with standard grouping in a table. Say you want to a repeatable data region that is repeated by an outer group, but contains two other tables an maybe an image. I can't think of a good way to do that with standard row grouping using a tablix. However, the List control makes this easy. In short, here is what is needed. A List control that contains all other elements and is grouped by some value. The List gets repeated for each unique group value. Put two Tables in the List and toss in any other controls you need such as Textboxes or Images.

Here is the controls layed out in Visual Studio report designer:

We get our data from the database using anything such as Linq or other. However, we "flatten" it because reports do not understand nested data.

We can make developing the report simplier by skipping the DB step and making our own objects like:

public class Offers
{
    public string OfferID { get; set; }
    public string Offerer { get; set; }
    public bool IsProgram { get; set; }
    public string ProgramName { get; set; }
    public int ProgramHours { get; set; }
    public bool IsPrereq { get; set; }
    public string PrereqName { get; set; }
    public int PrereqCode { get; set; }
    public byte[] Image { get; set; } 

    public static List<Offers> GetData()
    {
        byte[] im = File.ReadAllBytes(@"C:\Users\William\Pictures\MarkTwain.jpg");
        byte[] im2 = File.ReadAllBytes(@"c:\users\william\pictures\captain_john_smith.jpg"); 

        // Offer1
        Offers of1 = new Offers(){ OfferID="03453", Offerer="John Smith", IsProgram=true, ProgramName="admission", ProgramHours=1};
        of1.Image = im2;
        Offers of2 = new Offers(){ OfferID="03453", Offerer="John Smith", IsProgram=true, ProgramName="actual crisis", ProgramHours=10};
        of2.Image = im2;
        Offers of3 = new Offers(){ OfferID="03453", Offerer="John Smith", IsProgram=true, ProgramName="discussion", ProgramHours=5};
        of3.Image = im2;
        Offers of4 = new Offers(){ OfferID="03453", Offerer="John Smith", IsProgram=true, ProgramName="final notes", ProgramHours=2};
        of4.Image = im2; 

        Offers of5 = new Offers(){ OfferID="03453", Offerer="John Smith", IsPrereq=true, PrereqName="Macro econ II", PrereqCode=3463};
        of5.Image = im2;
        Offers of6 = new Offers(){ OfferID="03453", Offerer="John Smith", IsPrereq=true, PrereqName="Finance", PrereqCode=34634};
        of6.Image = im2;
        List<Offers> offers = new List<Offers>() {of1, of2, of3, of4, of5, of6}; 

        // Offer2
        of1 = new Offers(){ OfferID="80345", Offerer="Mark Twain", IsProgram=true, ProgramName="admission2", ProgramHours=2};
        of1.Image = im;
        of2 = new Offers(){ OfferID="80345", Offerer="Mark Twain", IsProgram=true, ProgramName="actual crisis2", ProgramHours=3};
        of2.Image = im;
        of3 = new Offers(){ OfferID="80345", Offerer="Mark Twain", IsProgram=true, ProgramName="discussion2", ProgramHours=4};
        of3.Image = im;
        of4 = new Offers(){ OfferID="80345", Offerer="Mark Twain", IsProgram=true, ProgramName="final notes2", ProgramHours=7};
        of4.Image = im; 

        of5 = new Offers(){ OfferID="80345", Offerer="Mark Twain", IsPrereq=true, PrereqName="Macro econ II2", PrereqCode=3466};
        of5.Image = im;
        of6 = new Offers(){ OfferID="80345", Offerer="Mark Twain", IsPrereq=true, PrereqName="Finance2", PrereqCode=34638};
        of6.Image = im;
        List<Offers> offers2 = new List<Offers>(){of1, of2, of3, of4, of5, of6};
        offers.AddRange(offers2);
        return offers;
    }
}

Now in the Report load event of your report Form do something like:

private void Offers_Load(object sender, EventArgs e)
{
    var list = Offers.GetData();
    this.OffersBindingSource.DataSource = list;
    this.reportViewer1.LocalReport.EnableExternalImages = true;
    this.reportViewer1.RefreshReport();
}

Now you have a report that has grouped "regions" and inside each region can contain other lists and/or other elements related to the group.

Output:

23:32 | コメントの投稿 | コメントの表示 (3 件) | 固定リンク | この記事を引用

2月1日

Astoria query Cheat Sheet

Samples Data Model:

Query Samples:

Below are some samples of using Astoria to query against the model. I tried to hit the most popular that will hit most of the features, but far from complete. Please let me know others you would like to see and I will keep adding.

var db = SocialEntities.GetDB(); // Get the DataContext.

Navigation1

public void Test()
{
    var q = from u in db.Users
            where u.UserID == 2
            from en in u.Entries
            select en;
    //http://localhost:7777/RestService.svc/Users(2)/Entries()
}

Navigation2

public void Test()
{
    var q3 = (from u in db.Users
              where u.UserID == 2
              from e in u.Entries
              where e.EntryID > 1
              select e).Take(10);
    //http://localhost:7777/RestService.svc/Users(2)/Entries()?$filter=EntryID gt 1&$top=10
}

String filter

public void Test()
{
    var q2 = from u in db.Users
             where u.Tag.Contains("will") || u.Tag.EndsWith("ne")
             select u;
    //http://localhost:7777/RestService.svc/Users()?$filter=substringof('will',Tag) or endswith(Tag,'ne')
}

Reverse Navigation

public void Test()
{
    var q4 = from e in db.Entries
             where e.EntryID == 85 // Must be 1 primary key. Range not supported.
             select e.Users1; // Get the user that owns this entry.
    // http://localhost:7777/RestService.svc/Entries(85)/Users1
}

Date filter

public void Test()
{
    var q5 = (from u in db.Users
              where u.Created >= DateTime.Parse("10/1/05") && u.Created <= DateTime.Parse("11/1/09")
              select u).Take(5);
    //http://localhost:7777/RestService.svc/Users()?$filter=(Created ge datetime'2005-10-01T00:00:00') and (Created le datetime'2009-11-01T00:00:00')&$top=5
}

Using Expand on Service operation

This example shows a couple concepts. It shows how one can use Expand on a service operation, and it shows how we can continue to "compose" over query. Here we use a service operation that takes 1 parameter, but we could use more or none. Linq is used to "build" the uri query, which is then executed by the .Net client stack, which is sent to the server. The servers gets the IQueryable from the method and composes over it (i.e. adds the filters and the Expand) using the client's query and returns the results.

private void Test()
{
    // Using Expand on a service operation proxy.
    var db = SocialEntities.GetDB();
    var q3 = from u in db.GetUsers("").Expand("Entries")
             where u.Tag == "william"
             select u;
    Console.WriteLine(q3.ToString());
    // URI: http://localhost:7777/RestService.svc/GetUsers()?$filter=Tag eq 'william'&$expand=Entries&pattern=''
    var user = q3.First();
    Console.WriteLine("{0} {1}", user.Tag, user.Entries.Count() );
}

// Client proxy method.
public DataServiceQuery<Users> GetUsers(string pattern)
{
    var q = this.CreateQuery<Users>("/GetUsers").AddQueryOption("pattern", "'" + pattern + "'");
    return q;
}

// Service operation
[WebGet]
public IQueryable<Users> GetUsers(string userPattern)
{
    if (userPattern == null || userPattern.Length == 0)
        return this.CurrentDataSource.Users;
    userPattern = userPattern.Replace('*', '%');
    userPattern = userPattern.Replace('?', '_');
    string filter = string.Format("it.Tag LIKE '{0}'", userPattern);
    var q = this.CurrentDataSource.Users.Where(filter).Select(u => u);
    return q;
}

Limit number of "Expanded" items on the many side (rSide)

TMK, you can't use Expand to limit the number items on the many side. However, you can reverse the query to get the same effect.

public void Test()
{
    // Take X from many side and expand the 1 side.
    var q2 = (from en in db.Entries.Expand("Users")
              where en.Users.Tag == "william"
              orderby en.Created descending
              select en).Take(1);
    foreach (var en in q2)
        Console.WriteLine("{0} {1} {2}", en.EntryID, en.Created, en.Users.Tag);
}

Return "partial" types from server using Service Operation and EFExtentions to "hide" data:

Using the Entity Framework Extentions (http://code.msdn.microsoft.com/EFExtensions), you can materialize entities using all or some of the fields. Here we just return ID and Firstname from the database. One drawback here is you can not use Expand on the client side when using Materialize this way. Not sure why yet, but I assume it has something to do with the way it creates Entity Collections (or not).

[WebGet]
public IQueryable<Employees> GetEmployee(string empID)
{
    // Note we create a partial employee with only selected columns, so we can return
    // just the data we need.  The rest will be the default values of the types.
    string sql = string.Format("select EmployeeID, FirstName from Employees where EmployeeID='{0}'", empID);
    var cmd = this.CurrentDataSource.CreateStoreCommand(sql);
    var q = cmd.Materialize<Employees>();
    return q.AsQueryable();
}

Update entity without query first:

You can skip the initial query to download the object just to update it by attaching a client-side object and setting the key property.

MyDataServiceContext ctx = new MyDataServiceContext(myServiceRoot);
Customer cust = new Customer();
cust.ID = 1;                    // Set primary key for object tracking.
ctx.AttachTo("Customer", cust); // Attache customer to context.
cust.Name = "Bill";             // Update members of object.
ctx.UpdateObject( cust );       // Set for update.
ctx.SaveChanges();