locked
Adding new columns dynamically RRS feed

  • Question

  • User1216627406 posted

    Greetings experts,

    I am trying to add table columns programmatically to a database column and present it in GUI fashion so that when users new to add a new column, they can add that on their own without me having to do it for them.

    The following is my attempt to handle this programmatically:

    // Create 1 column and add them to the table's Columns collection. One column at a time.
    int numberOfColumns = 1;
    for (int x = 0; x < numberOfColumns; x++)
    {
        Transactions.Columns.Add(new TableColumn());
    
    }
    

    The table name is called Transactions.

    My issue is how to present this in GUI form.

    Any ideas?

    Thanks in advance

    Tuesday, January 8, 2019 3:24 PM

Answers

  • User475983607 posted

    This post is jumping around to much.  If the problem is a null field then use the IsNull() function or convert NULL to an empty string during the export process.  We cannot see the export code so there's not much we can do to help.

    When the member is ready to pay for his/her 2018 dues, then adding that amount without adding the member's info twice is the issue.

    I'm not sure what "adding the member's info twice" means.  Sounds like a design bug.  You should be able to simply add a debit/credit record for 2018 dues to the user's account. Then run an aggregate query will produce the +/- amount.

    The query shown looks like the user has a single record that holds calculated values.  I imagine the logic is designed in such a way that you must "add the member's info twice" to get the process to complete.   Not sure can't see the code.

    BTW, as you can see, the Years, 2016 through 2020 are hardcoded.

    Right, that's how a PIVOT query works.  If you want dynamic columns then you must craft a dynamic query.  

    https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 8, 2019 9:07 PM
  • User475983607 posted

    simflex

    Can you please tell me how to use this on my C# code?

    Are you asking how to submit a SQL query using C#? 

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples

    Maybe you are asking how to submit a raw query in Entity Framework?

    https://docs.microsoft.com/en-us/ef/ef6/querying/raw-sql

    simflex

    Even better, can I create this query using VIEWS so I can easily use the VIEW on my C# code.

    There are no restrictions on using a PIVOT in a View.  Are you you receiving an error?  Can you post your code?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 10, 2019 4:34 PM

All replies

  • User475983607 posted

    Can you clarify?  

    Do you want to allow web user users to add columns to database table?  If so, this requires SQL and bit of though as the change will ripple through the application.

    https://docs.microsoft.com/en-us/sql/relational-databases/tables/add-columns-to-a-table-database-engine?view=sql-server-2017

    If you want to add columns to a DataTable in the web server's memory then see the following reference.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/adding-columns-to-a-datatable

    Tuesday, January 8, 2019 3:40 PM
  • User753101303 posted

    Hi,

    The problem is often what happens next ie how those columns are then "consumed" by other web pages etc...

    AFAIK it is quite rate to really alter the database schema. Most often you handled hardcoded columns and then you use for example JSON or XML support in SQL Server for "user defined" stuff.

    For now it seems you are confusing DataTables (which are just an in memory representation for table data) and the actual underlying tables stored in your db. They are related but you can't change a SQL Server table by just changing a DataTable definition.

    Tuesday, January 8, 2019 3:49 PM
  • User1216627406 posted

    Thanks good people.

    I have done this before with classic ASP quite awhile back.

    In the administrative interface of the app, the admin managing the app often knows what column name they wish to add to the database.

    This has no impact on other parts of the app.

    They add the column and they begin to add data to the app.

    Here is specifically what we are trying to accomplish.

    There is a field called Year_of_payment.

    As the name suggests, the admin records the year each member pays his/her annual dues.

    The issue I am having with this approach is that when they display the records, rather than have the value of year_of_payment display under the column of year_of_payment, they want the values display as columns and the amount paid under each year.

    I had to use PIVOT function to accomplish this.

    For instance,

    2016             2017          2018            2019          2020

    $300.00       $100.00      $150.00       $150.00      -----

    As you can see some users can pay all or some of their dues.

    However, I have not figured a way to update the update records with PIVOT data and when I attempt to export these records to export, I get an error that NULL values cannot be exported.

    As a result, I decided to make each year a column in the database.

    To do so, I needed to provided them with ability to add Year via a GUI interface.

    I don't know if this answered your question.

    Thanks again for your help.

    Tuesday, January 8, 2019 4:03 PM
  • User475983607 posted

    As a result, I decided to make each year a column in the database.

    This approach breaks DB normalization rules and therefore, IMHO, the wrong approach.  You're existing code, as stated above, will not know about the new column(s) unless you specifically designed the code to use generic types like DataSet/DataTables and all the Data Bound controls iterate over the DataTable column names.  Or if you used a secondary XML or JSON data format.

    To do so, I needed to provided them with ability to add Year via a GUI interface.

    I do this sort of thing all the time by adding a row to a table.  Sounds like a standard accounting debit/credit pattern.  

    Tuesday, January 8, 2019 4:19 PM
  • User1216627406 posted

    True but the issue here is the ability to add remainder of payments without adding all the related data across the row.

    Yes, I have done some of the accounting stuff where you don't delete/edit existing data but add to it instead.

    How to do this with PIVOT data is the problem.

    Any suggestions?

    Tuesday, January 8, 2019 4:28 PM
  • User475983607 posted

    True but the issue here is the ability to add remainder of payments without adding all the related data across the row.

    And a very basic accounting pattern that's used the world over in business app; debit/credit.

    How to do this with PIVOT data is the problem.

    I'm not sure what problem you are having with PIVOT as I cannot see the schema and do not have the query.  If the design used a simple ledger (like your checkbook register) then you're looking at a very basic group by year, sum, pivot query.  If not and you have calculated fields and such then the query will be far more difficult. 

    Tuesday, January 8, 2019 4:36 PM
  • User1216627406 posted

    Not sure that this does any good but here is the query that I am using:

    SELECT *
    FROM
    	(SELECT t.transactionID, t.Amount, t.AmountOwed, t.AmountPaid, Year(t.TransactionDate) As [Year], p.Description, m.MemberName, m.JoinDate
    				
    			FROM transactionID t 
    			INNER JOIN PaymentType p ON t.TypeID = p.PaymentTypeID
    			INNER JOIN Members m ON t.MemberID = m.MemberID
    
    	) As SourceTable
    PIVOT
    	(
    	SUM(AmountPaid)
    	FOR [Year] IN ([2016], [2017], [2018], [2019], [2020])
    	) AS PivotTable

    So, if any member has made any payments for transact_Date of 2018, it displays it like:

    2018

    $300.00

    The rest display NULL values.

    When the member is ready to pay for his/her 2018 dues, then adding that amount without adding the member's info twice is the issue.

    BTW, as you can see, the Years, 2016 through 2020 are hardcoded.

    Tuesday, January 8, 2019 8:15 PM
  • User475983607 posted

    This post is jumping around to much.  If the problem is a null field then use the IsNull() function or convert NULL to an empty string during the export process.  We cannot see the export code so there's not much we can do to help.

    When the member is ready to pay for his/her 2018 dues, then adding that amount without adding the member's info twice is the issue.

    I'm not sure what "adding the member's info twice" means.  Sounds like a design bug.  You should be able to simply add a debit/credit record for 2018 dues to the user's account. Then run an aggregate query will produce the +/- amount.

    The query shown looks like the user has a single record that holds calculated values.  I imagine the logic is designed in such a way that you must "add the member's info twice" to get the process to complete.   Not sure can't see the code.

    BTW, as you can see, the Years, 2016 through 2020 are hardcoded.

    Right, that's how a PIVOT query works.  If you want dynamic columns then you must craft a dynamic query.  

    https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 8, 2019 9:07 PM
  • User753101303 posted

    As pointed already it will be much easier to just keep normalized data,(which is much more suitable for doing all kind of queries). Reporting products are able to pivot/unpivot data as well as SQL. It is useful basically only a convenience for showing/editing data for users.

    If you need something really flexible another option (beyon generating SQL on the fly) is to use a DataTable to pivot data as needed and to unpivot them when saivng back edited data to the database.

    Tuesday, January 8, 2019 9:50 PM
  • User1216627406 posted

    I agree with mgebhard that the topic is jumping but I provided the code that was asked of me.

    Again, in my original post, I suggested that ISNULL is the problem.

    I tried using isnull() as suggested here but it didn't work.

    I will try using the Isnull() in the export code as suggested. Good suggestion.

    As for using normalized data, that would be ideal but no suggestion on how to do that because I have clearly (I *THINK*) stated the reason for the PIVOT approach.

    If I can get some suggestion how to make better use of the YEAR (2016....20NN) in the DB, that would help greatly.

    Thanks again to you guys for continuing to respond.

    Wednesday, January 9, 2019 2:57 PM
  • User475983607 posted

    I tried using isnull() as suggested here but it didn't work.

    ISNULL() is a TSQL function.  There is no indication how it was applied and "didn't work" is specific enough to provide assistance.  If the export code is C# then you should be able to test for null.

    As for using normalized data, that would be ideal but no suggestion on how to do that because I have clearly (I *THINK*) stated the reason for the PIVOT approach.

    The design is not normalized which causes downstream SQL and code complexity.  The Pivot table is unrelated and used for display.  The display could just as easily be done in the UI (HTML).  Which might be a bit easier depending where you feel more comfortable coding.

    If I can get some suggestion how to make better use of the YEAR (2016....20NN) in the DB, that would help greatly.

    There are two approaches to make the IN clause dynamic.  1) build a dynamic query (SQL string) as shown in the SO link.  2) craft a sub query.

    Sub Query might look similar to...

    PIVOT
    	(
    	SUM(AmountPaid)
    	FOR [Year] IN (SELECT DISTINCT t.TransactionDate FROM transactionID t)
    	) AS PivotTable

    Wednesday, January 9, 2019 3:55 PM
  • User1216627406 posted

    Great stuff!


    Thank you sir.

    I test this out when I am back to the office.

    Thanks again.

    Wednesday, January 9, 2019 8:21 PM
  • User1216627406 posted

    Great solution mgebhard.

    The dynamic pivot query works great; just the solution I came here for in the first place.

    Thank you.

    Can you please tell me how to use this on my C# code?

    Even better, can I create this query using VIEWS so I can easily use the VIEW on my C# code.

    Thursday, January 10, 2019 4:20 PM
  • User475983607 posted

    simflex

    Can you please tell me how to use this on my C# code?

    Are you asking how to submit a SQL query using C#? 

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples

    Maybe you are asking how to submit a raw query in Entity Framework?

    https://docs.microsoft.com/en-us/ef/ef6/querying/raw-sql

    simflex

    Even better, can I create this query using VIEWS so I can easily use the VIEW on my C# code.

    There are no restrictions on using a PIVOT in a View.  Are you you receiving an error?  Can you post your code?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 10, 2019 4:34 PM
  • User1216627406 posted

    Here is the exact same code that I will be using in our prod environment which works great, thanks to your solution.

    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);
    
    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(d.dateCreated) 
                FROM DateDetails d
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    set @query = 'SELECT employeeName,empTitle,email ' + @cols + ' from 
                (
                    select employeeName
                        , empTitle
    					,email
                        , dateCreated,sourceincome
                    from SourceDetails inner join Employees on SourceDetails.employeeID = Employees.EmployeeID
                    inner join DateDetails on SourceDetails.employeeID = DateDetails.employeeID
               ) x
                pivot 
                (
                     max(sourceincome)
                    for dateCreated in (' + @cols + ')
                ) p '
    
    execute(@query)

    I will like to plug this into a VIEW so that I can just use the view in my C# code as SELECT fields from view.

    Thursday, January 10, 2019 4:42 PM
  • User475983607 posted

    Use a Stored Procedure.  AFAIK, View cannot have parameters.

    Thursday, January 10, 2019 5:19 PM
  • User1216627406 posted

    Thank you for all your help

    Thursday, January 10, 2019 5:53 PM