August 4, 2011 at 1:57 pm
this is the core question for me:
Create controls dynamically per template
How is it doing that? from specific list?
tweaking the templates behavior is somethign i'd consdier.
you could have the template loop through all columns in the big table,and create hidden fields/controls for the ones not on the list, and the appropriate control for everything else...then you put the values back in the datatable on postback and update.
Lowell
August 4, 2011 at 2:27 pm
Lowell,
The problem with this approach is, I need to modify the .NET code every time if there is control retired or add a new control. I don't want to do that. I will do all of that in the database table and the UI will remain the same.
These templates are created by SMEs. They have another interface to create templates. They will go to that screen and select the columns for each type of template.
August 4, 2011 at 2:32 pm
Impedance Mismatch! Can you change to an EAV data model in the database, or some other type of columnar model?
If you're stuck with the data model I may have something else for you. I am testing a theory with TVPs on my side so I may be back with another option.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 4, 2011 at 4:45 pm
Option 1: Change to a columnar structure in the database.
Reporting will be a pain but the data can always be flattened later. Weigh the effort required to flatten data in a columnar store for reporting with the effort required to retrieve and store columnar data from the front-end to a flattened structure in the database. If you want more info on this just ask, I spent most of the time on a solution for the flat attribute table.
Credit for Demo Code: http://dotnetspeaks.com/DisplayArticle.aspx?ID=47
Create test table:
CREATE TABLE TestTVP(id INT, name VARCHAR(50)) ;
CREATE TYPE TestTVPType AS TABLE
(
id INT,
name VARCHAR(10)
) ;
CREATE PROCEDURE sp_testtvp_i(@testtvptype TestTVPType READONLY)
AS
INSERT INTO TestTVP(id, name)
SELECT id , name FROM @testtvptype;
GO
Now let's run some .NET code via a simple Console App to add data to the table using the proc and TVP:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace TvpDemo
{
class Program
{
static void Main(string[] args)
{
using (
SqlConnection conn = new SqlConnection("Data Source=.\\R2;Initial Catalog=test;Integrated Security=SSPI;"))
{
// build the DataTable dynamically from the meta-data you should
// have access to from building the .NET form
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("age"));
dt.Columns.Add(new DataColumn("name"));
// add data to the DataTable as needed, per the form submission
dt.Rows.Add(1, "John");
dt.Rows.Add(2, "Andy");
// commit the data to the database
SqlCommand insertCmd = new SqlCommand("sp_testtvp_i", conn);
insertCmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam =
insertCmd.Parameters.AddWithValue("@testtvptype", dt);
tvpParam.SqlDbType = SqlDbType.Structured;
conn.Open();
insertCmd.ExecuteNonQuery();
}
}
}
}
That's the initial deployment...
...now let's look at what it would be like in maintenance mode.
Remember the .NET code will need to dynamically create and populate a DataTable that matches the flat attribute table metadata. Comments are inline in the .NET code complete with hand-waiving about how the "real" code will need to by dynamic. All we need to do is make sure the proc and table-type handle the data per the metadata that is now in place after adding a column to our flat attribute table.
-- add a new column to our flat attribute table
ALTER TABLE TestTVP ADD new_attribute VARCHAR(10);
GO
-- must drop the proc before we can drop the type
DROP PROCEDURE sp_testtvp_i
GO
-- must drop the type before we can alter it
DROP TYPE TestTVPType
GO
-- create a new table-type that has the new column we just added to
-- our flat attribute table
CREATE TYPE TestTVPType AS TABLE
(
id INT,
name VARCHAR(10),
new_attribute VARCHAR(10)
);
GO
-- add the proc, with a new definition that handles our new_attribute column
CREATE PROCEDURE sp_testtvp_i(@testtvptype TestTVPType READONLY)
AS
INSERT INTO TestTVP(id, name, new_attribute)
SELECT id , name, new_attribute FROM @testtvptype;
GO
So to recap adding column #401 will look like this:
1. Add a column to the table.
2. Add a column to the table type.
3. Modify the stored procedure to update the table with the data in the new table-type column.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 4, 2011 at 6:51 pm
Thank you so much for your sample code and looking into it.
What I am looking to do is not to change the Table Valued Parameter every time. With your example, I need to create 7 different TVPs (each one has its own set of columns) and if any of the template changes, this will change. Also, I need 7 different INSERT statements in the stored procedure. The number 7 refers to my templates count. Each template displays different set of columns (out of 400 columns) from my main table.
I hope I explained my requirement correctly.
The goal is to have a form with dynamic list of controls based on the selected template which can INSERT and UPDATE a single record (this record will have only columns selected out of those 400 columns for a given template) in the database table. I can't predefine the TVP or the list of columns in my INSERT or UPDATE statement. This can change dynamically. And its very hard to keep track of all columns in each template type.
I know this is frustrating to implement. Even I am implementing something like this for the first time in my 14 years career 🙂
Thanks
AK
August 4, 2011 at 8:50 pm
SQL_Developer (8/4/2011)
Thank you so much for your sample code and looking into it.What I am looking to do is not to change the Table Valued Parameter every time. With your example, I need to create 7 different TVPs (each one has its own set of columns) and if any of the template changes, this will change. Also, I need 7 different INSERT statements in the stored procedure. The number 7 refers to my templates count. Each template displays different set of columns (out of 400 columns) from my main table.
I don't see it that way at all, however I will drop it. I think it could be done such that one TVP would be all that is needed. That said, I do not know your data or your 7 templates so you could bump into the 8060 byte row limit with TVPs that does not apply to actual table rows.
The goal is to have a form with dynamic list of controls based on the selected template which can INSERT and UPDATE a single record (this record will have only columns selected out of those 400 columns for a given template) in the database table. I can't predefine the TVP or the list of columns in my INSERT or UPDATE statement. This can change dynamically. And its very hard to keep track of all columns in each template type.
I get it, but you're signing up for trouble.
I know this is frustrating to implement. Even I am implementing something like this for the first time in my 14 years career 🙂
I am sorry you felt the need to tell me how much experience you have. I hope I have not been too forceful or offensive in my demeanor. I have not been at this game for 14 years yet, however I did inherit and had to support a system much like this before so I am speaking from first-hand experience. It was for a different genre of data but the initial attempt at solving the world's problems was similar to what you are describing. It was supporting a survey app where users could design their own survey with 1-n questions, and those questions could be made up on the fly. Yes, it is frustrating, but please, do yourself and anyone else that may work after you a favor, if the dynamic piece is non-negotiable then move towards a columnar store. What you're thinking of doing will be a nightmare to maintain.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 5, 2011 at 12:12 pm
This is a long thread and I didn't have time to really study it, but I believe the easiest answer was posted a page or 2 earlier. I've done this sort of thing and I actually have some utilities that will generate the stored procedure that does the INSERT/UPDATE. It's not the most elegant solution, but it has worked for me.
First the table you're inserting must have just about every column, except the primary key, as nullable. Since you were going to do this dynamically, I believe they must already be nullable.
Create a procedure that looks something like:
-- Use the proper column names and data types per your table.
-- Procedure has to have all columns in the table
CREATE PROCEDURE InsertUpdateMyTable
@PKColumn = @PKColumn INT
, @Column1 = @Column1 VARCHAR() = NULL
, @Column2 = @Column2 VARCHAR() = NULL
.
.
. @LastColumn = @LastColumn VARCHAR() = NULL
AS
IF NOT EXISTS (SELECT 1 FROM MyTable WHERE PKColumn = @PKColumn)
INSERT INTO MyTable
(PKColumn, Column1, Column2,....LastColumn)
SELECT
@PKColumn, @Column1,@Column2,...@LastColumn
ELSE
UPDATE MyTable
SET Column1 = ISNULL(@Column1, Column1)
, Column2 = ISNULL(@Column2, Column2)
, LastColumn = ISNULL(@LastColumn, LastColumn)
-- The ISNULL allows for missing passed parameters and won't change existing column values
As I said, it's kind of ugly but this approach can work - it depends on your needs.
The front end form already knows which columns it is dealing with and should be able to easily create the stored procedure call.
Anyway, that's my 2 cents.
Todd Fifield
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply