开发者

Why do typed DataSet methods return unexpected results

I use typed DataSets for accessing data, configuring them via DataSet Designer (the general idea is explained in this tutorial).

For each table a GetData() method is automatically created at first. It returns a DataTable having the same schema (i.e. columns) as the underlying table in the database. If now I create a custom method using 'Add query' option, which is supposed to return only part of data (say three columns out of six), the DataTable returned by the autogenerated method still consists of six columns, while three of them include no data.

This has an annoying side effect that when I bind this DataTable for example to the GridView (apparently using ObjectDataSource), these empty columns appear in the GridView as well, so I have to remove them manually.

Another related problem is that if I add to the corresponding SQL-query columns with data from another table (e.g. using inner joins or sub-queries), these columns on the contrary are not being added to the GridView, though I can bind them manually in the Boundfield.

So if anybody is present 开发者_运维知识库here who had not completely moved to the Entity Framework and can explain this behavior and help to overcome these issues, I would highly appreciate your answer. Thank you in advance.

UPDATE:

My expectation is to find the way to get a DataTable from the get method created by the DataSet Designer ontop of my SQL query, which would reflect the results of this query in its schema. In the tutorial they say that GetData() method creates and fills the DataTable for you and returns it as the method's return value. So intuitively why not create a new table with the matching fields.

So I was hoping I was doing something wrong. Or my expectations were wrong, but then somebody could explain me, why.


I haven't tried this, but I imagine you can resolve these issues by transferring your data table results into another known type, and binding the grid accordingly. (I know that makes little sense, bear with me...)

As an example (albeit with an untyped dataset):

EDIT - swapped to C# for you:

EDIT2 - wow, online converter failed me massively - retyped so it'll actually compile now

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

public class myView 
{ 
    public string OrderNumber { get; set; } 
    public int Quantity { get; set; } 
    public decimal PricePerUnit { get; set; } 
    public decimal TotalPrice { get; set; } 
}

public void SO9(System.Data.DataTable mockTypedDatatable)
{
    IEnumerable<myView> results = mockTypedDatatable.AsEnumerable().Select(n => new myView {    
                OrderNumber = Convert.ToString(n["OrderNumber"]),    
                Quantity = Convert.ToInt32(n["Quantity"]),    
                PricePerUnit = Convert.ToDecimal(n["Price"])});
    results.ToList().ForEach(x => x.TotalPrice = x.Quantity * x.PricePerUnit);
    myGridView.DataSource = results;      
    myGridView.DataBind();
}

In the example I assigned columns from the pretend datatable to some properties, whilst other properties are populated by other means (in the example, a simply multiplication of Quanty * Price but you can extend this easily).

Hope that helps


Ok I can say I've found out how to solve it.

The explanation is that the corresponding TableAdapter creates and returns by any of its GetData() methods (including customized methods like GetReducedSetOfData()) not just a generic DataTable, but exactly typed DataTable (e.g. CarSetsDataTable in my case). Perhaps that was exactly essence of strongly typed DataSets which I had overlooked.

And the solution is easy in my opinion. You've got just not to create new custom methods with probably modified data schema as return value in the existing DataAdapter, but create other DataAdapters for the same db table which would return exactly the result of the other custom SQL-query you need with their default GetData() methods.

I do not know if creating many DataAdapters can have any side effects, but it seems to be a safe way to get exactly those data you need from the typed DataSet with minimal effort.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜