August 3, 2011 at 3:12 pm
Hello All,
I am working on dynamically generated web form with bunch of controls in an ASP.NET project. When I save the data, I am passing all the controls (FieldName, FieldValue and FieldDatatype) as TVP to a stored procedure. Now I need to convert these rows into an INSERT statement. Basically each row represents a column in my main table. So, I need to build the INSERT statement out of these rows and execute it so INSERT a row into the database table. I need to do similar thing for an UPDATE statement.
Any help would be greatly appreciated.
Regards,
AK
August 3, 2011 at 4:03 pm
please provide some sample data including the expected result.
Will those columns be static or is a dynamic solution required? If so, please describe the dynamic scenario.
August 3, 2011 at 9:06 pm
Lutz,
Thanks for your response. My columns would be dynamic. Here is a scenario..
-- The following temp table is similar to my Table Valued Parameter.
CREATE TABLE #Test (ColumnName VARCHAR(100), ColumnValue VARCHAR(100), ColumnType VARCHAR(100))
INSERT INTO #Test VALUES ('FirstName','John','VARCHAR')
INSERT INTO #Test VALUES ('LastName','Isner','VARCHAR')
INSERT INTO #Test VALUES ('City','Athens','VARCHAR')
INSERT INTO #Test VALUES ('State','GA','VARCHAR')
INSERT INTO #Test VALUES ('EmpID','2','INT')
SELECT * FROM #Test
-- The paramater (Table Valued Parameter) to my INSERT stored procedure will have data similar to the data in #Test table.
-- I need to build the following statement.
-- Remember I may have any number of rows in temp table #Test.
-- In this situation, I have 5 rows
-- Next time I may have 4 or 8 rows (they are dynamic).
-- Each row represents a column in my underlying data table.
-- My output would be like this...
INSERT INTO dbo.Person (FirstName, LastName, City, State, EmpID)
VALUES ('John', 'Isner', 'Athens', 'GA', 2)
August 4, 2011 at 4:47 am
Try this:
[font="Courier New"]declare @strSQL varchar(1000)
set @strSQL = 'INSERT INTO dbo.Person ('
select @strSQL +=ColumnName + ',' from #Test
set @strSQL = left(@strSQL,len(@strsql)-1)
select @strSQL += ') VALUES ('
select @strSQL += case when ColumnType = 'INT' then ColumnValue + ','
when ColumnType = 'VARCHAR' then '''' + ColumnValue + ''',' end
from #Test
set @strSQL = left(@strSQL, len(@strsql)-1)
select @strSQL += ')'
select @strSQL[/font]
Jamie
August 4, 2011 at 10:02 am
SQL_Developer (8/3/2011)
Hello All,I am working on dynamically generated web form with bunch of controls in an ASP.NET project. When I save the data, I am passing all the controls (FieldName, FieldValue and FieldDatatype) as TVP to a stored procedure. Now I need to convert these rows into an INSERT statement. Basically each row represents a column in my main table. So, I need to build the INSERT statement out of these rows and execute it so INSERT a row into the database table. I need to do similar thing for an UPDATE statement.
Any help would be greatly appreciated.
Regards,
AK
First I must commend you for making use of TVPs. That's as far as my praise will go however because beyond using a new feature of SQL 2008 the rest of your design is COMPLETELY MISGUIDED!
If you must build SQL statements on the fly the least you could do is build them in your application tier, not in the data tier. Dynamic SQL will allow you to do the job, but this design puts you (or any DBA that walks through the door) behind the 8-ball from the outset in terms of trying to monitor and tune the database for performance.
Is this your design, or was this design imposed on you? If you can, change it now before you get beyond the point of no return!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 4, 2011 at 10:28 am
SSCrazy,
Thanks for your reply and comments. I totally agree with you on the future maintenance. But for me there is no other way of doing this requirement. Believe it or not this is the first time I am choosing dynamic SQL option.
The ASP.NET form has dynamic set of controls (out of 200 fields in the DB), based on the user selection, I should display 100 to 150 fields at a time (most of them of varchar fields) dynamically. And then insert/update those fields into the database. So I choose this route of displaying fields dynamically and pass the field values as TVP to the stored procedure for insert and update. I can't create in-line SQL in .NET code and pass it to stored procedure.
Do you think of any other options of implementing this scenario?
Thanks
August 4, 2011 at 10:54 am
Although I have been called crazy many a time, and a lot lately coincidentally, SSCrazy is just my designation on the site based on the number of points I have earned on this site 🙂 My nick is actually opc.three.
SQL_Developer (8/4/2011)
SSCrazy,Thanks for your reply and comments. I totally agree with you on the future maintenance. But for me there is no other way of doing this requirement. Believe it or not this is the first time I am choosing dynamic SQL option.
Yes, there are...many other ways of accomplishing what you're trying to do. You can choose to use or not to use them.
The ASP.NET form has dynamic set of controls (out of 200 fields in the DB), based on the user selection, I should display 100 to 150 fields at a time (most of them of varchar fields) dynamically. And then insert/update those fields into the database. So I choose this route of displaying fields dynamically and pass the field values as TVP to the stored procedure for insert and update. I can't create in-line SQL in .NET code and pass it to stored procedure.
I was with you until you said "and pass it to stored procedure". Why would you build SQL code and then pass that into a stored procedure? Just issue the SQL after you have built it? So, in your ASP.NET code you would build the SQL INSERT statement and then execute it against the database using ExecuteNonQuery().
Do you think of any other options of implementing this scenario?
Yes! Here are a couple that will let you stay dynamic when it comes to managing an ever-changing (i.e. dynamic) number of attributes...maybe others will drop by and comment on these or more options:
1. As stated above build your SQL statements in ASP.NET using StringBuilder and issue them using ExecuteNonQuery(). If you do this explore turning on the "optimize for ad hoc workloads" option in your database.
2. Consider extending your schema to support an EAV sub-model for storing sparse attributes. Keep your core attributes (e.g. name, address, phone, birthdate) normalized as you have them today and store other attributes (e.g. hat size, eye color, favorite song, etc.) in your EAV. Knowing which attributes to store as a core attribute in a new column on an existing table and which attributes as sparse in your EAV table will require some knowledge of the data and it will be an ongoing process to make those determinations as attributes are added.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 4, 2011 at 10:54 am
what's wrong with a single datatable with 200 columns, maybe with just the one row the client would be fiddling with?
then the GUI can present you 100 or 150 data bound controls however you like,and then you simply use the normal UpdateDataTable command form your datalayer?
i'm probablyt glossing over something, but i don't see the need for dynamic SQL yet.,
Lowell
August 4, 2011 at 11:01 am
Lowell (8/4/2011)
what's wrong with a single datatable with 200 columns, maybe with just the one row the client would be fiddling with?then the GUI can present you 100 or 150 data bound controls however you like,and then you simply use the normal UpdateDataTable command form your datalayer?
i'm probablyt glossing over something, but i don't see the need for dynamic SQL yet.,
I re-read the OP, and I may have assumed that the number of attributes will need to change in the database as well.
If that is not the case then an EAV will not buy you much so kick that and go with a flat tables. Even a very wide table, or a set of sub-classed tables from the core Person table would be fine. Sparse Column feature could be employed to help things on very wide, sparsely populated columns.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 4, 2011 at 11:11 am
when i read the OP, it seemed that the data layer knew which items to capture, since his presentation will create a control for input for each item if needed, but some items were hidden/not needed, based on selection criteria.
Even if all the captured datapoints come from a suite of interrelated tables, it seems a lot easier to just have a typed dataset int here somewhere.
I'm sure I'm missing something, but I'd like to see more detail as to why the OP is thinking dynamic SQl is needed int his case.
Lowell
August 4, 2011 at 11:20 am
Yeah, too little info to say on the whole system so I made some leaps. But it's OK...I only jumped in to steer this one away from using Dynamic SQL to build INSERT and UPDATE statements in the data tier.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 4, 2011 at 1:15 pm
Thanks for your replies....
OK, Here is my requirement. I have a table with 400 columns. We have customized templates for different scenarios. So in each template, I may display some of these columns (may be 200 or 250 or some other number of columns) for each template type dynamically. Right now these templates are limited to 7 or 8. But in future we may add more templates. So a template is subset of columns from the large table. I need to display them in the ASP.NET form (which is already completed). The only database operations are.
1. Display fields.
2. Insert or Update record.
These fields are basically a collection of data items about a Cancer.
Right now I am able to display the fields in web form and sending back the user entered data to a stored procedure. These data items are passed to the stored procedure using Table Valued Parameter (TVP).
And the TVP has the following structure:
CREATE TYPE [dbo].[ControlTableData] AS TABLE(
[ColumnName] [varchar](500) NULL,
[ColumnValue] [nvarchar](1000) NULL,
[ColumnType] [varchar](100) NULL
)
So when I receive the user data into stored procedure. I need to either insert a row (if new) or update an existing row. The data is coming back as a row for each column. I need to convert these rows into either INSERT or UPDATE statements. In other way, I need either insert a row or update a row into my large table based on this TVP.
Hope this helps you to understand the requirement.
Thanks for your support...
August 4, 2011 at 1:39 pm
Million dollar question: Is there a requirement to avoid application code changes when you add column #401 to your table?
Per this article a stored procedure can have up to 2,100 input parameters. Why not create a proc with 400 input parameters mapping one-to-one to your table columns? If the row exists, update it, if not, insert it? Could it really end up being that simple?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 4, 2011 at 1:48 pm
Million dollar question: Is there a requirement to avoid application code changes when you add column #401 to your table?
Per this article a stored procedure can have up to 2,100 input parameters. Why not create a proc with 400 input parameters mapping one-to-one to your table columns? If the row exists, update it, if not, insert it? Could it really end up being that simple?
Yes, we don't want this to be static and define all 400 parameter to a table.
And there is every chance, we add column #401 in future. So this is so dynamic. So far I am weighing on the following approach.
1. Create controls dynamically per template.
2. Capture the data and pass it to stored procedure (one row for each column).
3. Build INSERT or UPDATE statements inside the stored procedure like Jamie Ashton (Forum Newbie) suggested.
I think this will solve my issue. If I find a better option I would change my code. But the I can't change the following.
1. Using static controls or creating a form for each template type.
2 Fixed number of parameters to a stored procedure.
3. Building SQL in .NET code and executing UPTDATE or INSERT commands.
Thanks for discussing again...
August 4, 2011 at 1:50 pm
Yes, we don't want this to be static and define all 400 parameters in a stored procedure.
And there is every chance, we add column #401 in future. So this is so dynamic. So far I am weighing on the following approach.
1. Create controls dynamically per template.
2. Capture the data and pass it to stored procedure (one row for each column).
3. Build INSERT or UPDATE statements inside the stored procedure like Jamie Ashton (Forum Newbie) suggested.
I think this will solve my issue. If I find a better option I would change my code. But the I can't change the following.
1. Using static controls or creating a form for each template type.
2 Fixed number of parameters to a stored procedure.
3. Building SQL in .NET code and executing UPTDATE or INSERT commands.
Thanks for discussing again...
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply