none
typed datasets - table with tons of fields but only need 2 - new class? RRS feed

  • Question

  • Hello,

    Using visual studio 2008, typed datasets. I am working with a database and one table in particular has over 100 fields. It's a a really bad database design, but that's not my deal right now. I have a need to retrieve only 2 fields. Actually, it will retrieve exactly one record, and I only need 2 fields out of the 100+. I did the drag and drop thing to get my typed datatable and datatableAdapters created. Now, for this particular query I need, I'm not sure if I should just add a new query to this existing table adapter class that only only selects the 2 columns I need or drag another instance of this table out and remove all the other columns, so that there is a new typed datatable and tableAdapter created with just the 2 columns I need. If I just add the new query, it still returns that typed datatable with all 100+ columns, but only the 2 I need populated of course... That seems wasteful and ineffecient. I do have to keep the typed datatable that has all the columns for other reasons, but I'm assuming I should make an additional one for things that only require a very small subset of columns, yes?

    1. just create an additional query on existing typed dataTable/tableAdapter?

    2. create a new typed dataTable/tableAdapter that has only the required fields?

    I'm new to typed datasets, new to doing the queries client side at all really, I've only done stored procs on previous projects. I should also note that this project will be running on a handheld device with limited memory and cpu resources.

    let me know, thanks!

    Saturday, March 27, 2010 9:52 PM

Answers

  • Okay, this is an example of clicking the checkbox on the DataTable instead of simply choosing the columns you need when using the designer. It also occurs when you drag from the Server/database explorer you get all Columns as well.

    So once a TableAdapter is built, there is nothing to stop you from opening the TableAdapter designer and right-clicking the Fill method to reconfigure the query. There you can change the SELECT to choose just the columns you need--and add a WHERE clause to focus on the rows you need.

    This is explained in detail in my 7th Edition book (see my sig). I walk through best-practice use of the TableAdapter.

    Now as to your Database design, I suggest reading about normalization. It's not that hard to do and will yield dramatic results performance-wise.

    hth


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Sunday, March 28, 2010 1:27 AM
    Moderator
  • Hi c0pe,

    I suggest you add a new query with just the two columns to the TableAdapter. I think it is waste if creating a new TableAdapter just for retrieving the two columns' data because it does not utilize the function of TableAdapter. You can get just the two columns' data but not all the 100+ columns by calling the additional typed method based on your request, something like this:

    NorthwindDataSetTableAdapters.OrdersTableAdapter daOrders;
    NorthwindDataSet.OrdersDataTable tblOrders;
    
    daOrders = new NorthwindDataSetTableAdapters.OrdersTableAdapter();
    
    tblOrders = daOrders.GetDataBy();
    
    DataGridView1.DataSource = tblOrders;
    
    Best regards,
    Alex Liang
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, March 31, 2010 3:33 AM
    Moderator

All replies

  • Okay, this is an example of clicking the checkbox on the DataTable instead of simply choosing the columns you need when using the designer. It also occurs when you drag from the Server/database explorer you get all Columns as well.

    So once a TableAdapter is built, there is nothing to stop you from opening the TableAdapter designer and right-clicking the Fill method to reconfigure the query. There you can change the SELECT to choose just the columns you need--and add a WHERE clause to focus on the rows you need.

    This is explained in detail in my 7th Edition book (see my sig). I walk through best-practice use of the TableAdapter.

    Now as to your Database design, I suggest reading about normalization. It's not that hard to do and will yield dramatic results performance-wise.

    hth


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Sunday, March 28, 2010 1:27 AM
    Moderator
  • Hi thanks William, I appreciate the response. I think you missed a small detail, which is I have to keep the query that returns all fields for other parts of the app. Considering that, I was wondering about just adding an additional query with only the columns I need to the existing class, or creating a new class with only the columns I needed for this particular task. Returning a datatable structure with 100+ columns when I only need 2 of them seems pretty silly, so I have already created a new class with only the columns I need, but since I'm new to this, I wanted to ask... in case there are things going on under the hood that would somehow make the obvious choice the wrong choice, like more classes with less methods is ideal, or less classes with more methods, etc...

    1. new method/query on existing class or new class with only the required fields?

    2. also, just fyi, the database design is out of my control. I just thought I would mention the fact that I'm aware of the bad database design since I had to mention the 100+ fields in this one table. That was to prevent off topic responses telling me to stop what I'm doing and fix the database first :). I know that should be done but it's not going to be, and it's out of my control. To bad the original designer of this db had not read one of your books, hehe, then maybe this thing might actually resemble a relational database. Believe me, data normalization is not something this designer had a clue about.

    thanks again, and any further input would be welcome and appreciated.

    Sunday, March 28, 2010 3:03 PM
  • Hi c0pe,

    I suggest you add a new query with just the two columns to the TableAdapter. I think it is waste if creating a new TableAdapter just for retrieving the two columns' data because it does not utilize the function of TableAdapter. You can get just the two columns' data but not all the 100+ columns by calling the additional typed method based on your request, something like this:

    NorthwindDataSetTableAdapters.OrdersTableAdapter daOrders;
    NorthwindDataSet.OrdersDataTable tblOrders;
    
    daOrders = new NorthwindDataSetTableAdapters.OrdersTableAdapter();
    
    tblOrders = daOrders.GetDataBy();
    
    DataGridView1.DataSource = tblOrders;
    
    Best regards,
    Alex Liang
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, March 31, 2010 3:33 AM
    Moderator