Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: Help: Creating a table 'host'

    Hi Guys,

    I have an issue regarding an application I am writing for a company. It is to do with creating reports and such, most of the application is done, however I have one slight problem where I have currently created a master/slave table.

    The problem is that the master table is static, it only really provides the actual design information regarding the table to be drawn. Whereas the slave table as a regular table does, only has a specific amount of columns which I can use. Therefore if my client wanted to created another table representation in my program, I would need to create a whole new table.

    My question is, how can I create a set of tables where I can virtually create my own table (and still be able to edit it) so that when I use my C# application, I can throw it into a single datagridview and try and change the data there.


    So far what I am thinking is having:

    TableHost
    TableHostID - int
    Title - varchar
    ShowHeader - bool

    TableColumns
    TableColumns ID - int
    TableHostID - FK
    ColumnName - varchar

    TableCells
    ID - int
    TableColumnsID - FK
    CellData - varchar

    Basically I would want the view or whatever it is to be created to get the tablehost table row, figure out how many columns are created for the table, then basically get all of the tablecells values and put them on one row as long as they each represent a column within the table.

    An example would be:

    TableHost
    1 TableTest True

    TableColumns
    1 1 Column1
    2 1 Column2
    3 1 Column3

    TableCells
    1 1 DataA
    2 2 DataB
    3 3 DataC


    This would become the following, in my child datagridview:

    DataA DataB DataC

    I really have no idea how to actually get this functionality to work, I am not really sure if a view is what I am after, but i'm sure there must be a way to get it to work.

    Sorry the post is so huge..

    Lach.

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    really the only time you can't use a view for this is when the volume of data is so high that you have to pre-process the data to reduce execution time.

    however that is the general answer, the specific one depends on the situation and that isn't hugely clear there

    correct me if i'm wrong but you seem to be saying that you have a template of the db saved in a dband you want to use that template to produce a new db on the fly? and then edit the data in c#? to save back to the db?
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Sep 2009
    Posts
    4
    Hi m.timoney

    Thanks for the reply!

    Sorry I didn't make it too clear! Yeah, you are technically correct, I have a sort of template through the database, specifying the table columns and then each cell.

    I am really unsure of how this would work out, but i guess it would be needing me to actually write this in C# to properly edit the data. I only really asked the question, because usually I do something like this, and then there is always an answer which is much easier....

    I know databases are designed to be only really using the tables that you create within the database, but I wanted a way to sort specific columns into a virtual table which i could manipulate, but still save data back in the correct spots.

    Kinda weird i know, but I dont see any other way, how would i make it so that the user can have the ability to create a table within their report?

    Do you have any suggestions of a better way to go about it? =D

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    well that sounds like exactly what a view is used for, you draw data from separate tables and combines then into 1 meaningful one. i can't see why you would need to save the data in to temporary tables.

    you just need to write a stored procedure that uses update queries to write the changes back to the source table


    say as an example you have

    T1(Key, d1,d2,d3,d4)
    T2(key, d1,d2,d3,d4)
    T3(Key,t1Key,T2Key,d1,d2,d3)

    then your view would be
    Select *
    From T1 inner join t3 on T1.Key = T3.t1Key
    inner join T2 on T2.Key = T3.T2Key

    Update T1
    Set T1.D1 = @NewValue
    Where T1.Key = @T1KeyToChange
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Sep 2009
    Posts
    4
    Thanks for the reply,

    Now I know that there is a better way to do it (in sql rather than C# coding)

    I should probably start using stored procedures, but at the moment my application just has basic read/write with nothing fancy going on in the background!

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Stored procs, main advantage are that you can get the DB to crunch the numbers meaning your app is free to do other things, plus if you cut down the data the DB sends then you have smaller network overheads
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Sep 2009
    Posts
    4
    Hi Timoney,

    I have gotten it to work.... sort of. It's pretty much all done in C# however, and I am a little nervous about the way I have done it. The selection of data is easy and painless, its just that the updating is sorta.... bad!

    Basically, every time the user saves the data, it will remove every row, and cell from the current table selected, and then readd it, its almost impossible to do it without removing and deleting, i'm just worried, because i could potentially have about >90 insert statements due to the way the Cells table works, and I don't want to hog network bandwidth, even if it is only a VERY small insert query.

    Here is a diagram of my tables. (It is attached)

    I can also post the code if you want to see it, i have put comments all throughout it, just not sure if this forum is really the right place to post my code, even though I'm more asking in terms of the actual method I have done.

    Cheers dude, you have been a big help so far.
    Attached Thumbnails Attached Thumbnails diagram.png  

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    looks like you have mirrored the dataset fuctionality in the DB

    i think i have an better idea whar your doing now, but i'd hate to see the problem that needs to save a totally unspecified tabke to a database.

    also you are right about the number of inserts and deletes you will have to do, however if you really do need such a dynamic structure then there is little other choice

    as for hogging network band width i wouldn't worry just don't multi-thread your application and you wont run more than 1 query ar a time which will mean very little band width will actually be consumed. the problem you will have is table locking, having to insert so many rows into a table will mean you are locking the tables alot however even that should be a minimal issues as each query will be individually small

    i know how it can be programming where you tunnel vision on the first solution you think off, i'd advise you to look at the problem with fresh eyes and see if there is a better way to store the data you need stored

    remember that while a varient data type is very useful it also breaches the rules of normalisation. in the real world you sometimes have to break the rules to do the job but you do need to make sure your right in doing so
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •