Hi All
I have three tables
1.[record set]
RecordSetId InventoryId
701 11
702 11
703 12
704 12
705 13
706 13
707 11
708 11
709 19
710 19
711 20
2.app_fields
Fieldid FieldTypeid FieldName IsMandatory IsEditable InventoryId ValidationSize
201 100 WebsitetName 1 0 11 NULL
202 100 WebsiteUrl 1 0 11 NULL
203 100 CountryName 1 0 11 NULL
204 100 KraftRegion 1 0 11 NULL
205 100 WebAnalytics 1 0 11 NULL
206 100 HostingProvider 1 0 11 NULL
207 100 Channel 1 0 11 NULL
208 100 Company 1 0 11 NULL
209 100 ArticleName 1 0 13 NULL
210 100 ArrticleLocation 1 0 13 NULL
211 100 CountryName 1 0 13 NULL
212 100 KraftRegion 1 0 13 NULL
213 100 WebAnalytics 1 0 13 NULL
214 100 ArticleProvider 1 0 13 NULL
215 100 ArticleChannel 1 0 13 NULL
216 100 ArticleCompany 1 0 13 NULL
217 100 AssetName 1 0 12 NULL
218 100 AssetLocation 1 0 12 NULL
219 100 CountryName 1 0 12 NULL
220 100 KraftRegion 1 0 12 NULL
221 100 AssetAnalytics 1 0 12 NULL
222 100 AssetProvider 1 0 12 NULL
223 100 AssetType 1 0 12 NULL
224 100 AssetCompany 1 0 12 NULL
225 100 WebsiteName 0 0 17 0
226 100 Region 0 0 17 0
227 100 WebsiteName 0 0 18 0
228 100 Region 0 0 18 0
229 100 AssetName 1 0 19 50
230 102 AssetType 1 0 19 20
231 100 Comobject 1 0 20 20
232 101 Comcomp 0 1 20 100
233 102 ThirdList 1 1 20 0
3.[Asset detail]
AttributeId RecordSetId FieldId FieldValue
801 701 201 Google
802 701 202 www.Google.com/ncr
803 701 203 USA
804 701 204 Washington
805 701 205 Google Search Engine
806 701 206 Google Web Server
807 701 207 Website
808 701 208 GOOGLE INC
809 702 201 Yahoo
810 702 202 www.yahoo.com
811 702 203 USA
812 702 204 New York
813 702 205 Yahoo Website
814 702 206 Yahoo Web Server
815 702 207 Website
816 702 208 Yahoo Inc
817 703 217 Printer
818 703 218 CP-1st floor
819 703 219 Bangalore-India
820 703 220 India
821 703 221 Printing
822 703 222 HP Laser jet
823 703 223 Print
824 703 224 Infy
825 707 201 WebsitetName1
826 707 202 WebsiteUrl1
827 707 203 CountryName1
828 707 204 KraftRegion1
829 707 205 WebAnalytics1
830 707 206 HostingProvider1
831 707 207 Channel1
832 707 208 Company1
833 708 201 WebsitetName2
834 708 202 WebsiteUrl2
835 708 203 CountryName2
836 708 204 KraftRegion2
837 708 205 WebAnalytics2
838 708 206 HostingProvider2
839 708 207 Channel2
840 708 208 Company2
841 709 229 AssetName1
842 709 230 AssetType1
843 710 229 AssetName2
844 710 230 AssetType2
845 711 231 Col1
846 711 232 Col2
847 711 233 Col3
I want the output based on inventoryid [
for example:inventoryid=11] so I queried like this with the corresponding datas to load in it.
and the very most important things to keep it in mind that ...all the tables are Dynamic and will updated using excel sheet by administrator and in the UI part the User have to Enabled for update operation alone..I am in need of Updating operation in the stored procedure
SELECT AF.FIELDNAME,AD.FIELDVALUE FROM [APP_FIELDS] AF INNER JOIN [ASSET DETAIL] AD ON AD.FIELDID = AF.FIELDID INNER JOIN [RECORD SET] AS RS ON AD.RECORDSETID=RS.RECORDSETID WHERE RS.INVENTORYID =11
and the output is
FIELDNAME FIELDVALUE
WebsitetName Google
WebsiteUrl www.Google.com/ncr
CountryName USA
KraftRegion Washington
WebAnalytics Google Search Engine
HostingProvider Google Web Server
Channel Website
Company GOOGLE INC
WebsitetName Yahoo
WebsiteUrl www.yahoo.com
CountryName USA
KraftRegion New York
WebAnalytics Yahoo Website
HostingProvider Yahoo Web Server
Channel Website
Company Yahoo Inc
I want to transpose the resultant table with for the inventoryid=11 with the table headers and Datas should be arranged in rows like this
the output i want to be
WebsitetName WebsiteUrl CountryName KraftRegion WebAnalytics HostingProvider Channel Company
Google www.Google.com USA Washington Google Search Engine Google Web Server Website GOOGLE INC
yahoo www.yahoo.com .............................................................................
WebsitetName1 ..................................................................................
what i tried so far is
just pasting the pseudocode of a first try:
DECLARE #temptable table
temptable=select * from app_fields where inventoryid=11 into ##temptable where 1=2
Declare @RecordSetId,FieldId
Declare @TableData
set @ReacordSetId=select RecordSetId from [Record Set] from where inventoryid=11
set FieldId=temptable[0]
foreach row in temptable
TableData+= getFieldValue(@RecordSetId,FieldId) as temptable[row][2]
end foreach
@print TableData
create function getFieldValue( @RecordSetId int,@FieldId int)
RETURNS TABLE AS RETURN
( select FieldValue from [Asset Detail] where RecordSetId=@RecordSetId and FieldId=@FieldId )
GO
SELECT * FROM [ASSET DETAIL]
------------------------------------------------------------
the second thing is the stored proc :
CREATE PROCEDURE [dbo].[SP_APP_FIELDS]
@inventoryid [int]
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX),
@cols_WITH_MAX AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SELECT @cols_WITH_MAX =
STUFF((SELECT DISTINCT ',MAX('+ QUOTENAME(fieldname) +') AS ' + QUOTENAME(fieldname) FROM app_fields
WHERE inventoryid = @inventoryid FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
select @cols =
STUFF((SELECT distinct ',' + QUOTENAME(fieldname) from app_fields
where inventoryid = @inventoryid FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = 'SELECT '+ @cols_WITH_MAX +' FROM
(SELECT [inventoryid], ' + @cols + ' from
(SELECT FieldValue,fieldname,AF.inventoryid FROM [app_fields] AF join [ASSET DETAIL] AD on AD.FieldId = AF.FieldId
--join [RECORD SET] RS ON AF.inventoryid = RS.inventoryid
where AF.inventoryid ='+cast(@inventoryid as varchar(20)) +')X
pivot
(
max([FieldValue])
for [fieldname] in (' + @cols + ')
) p )a GROUP BY [inventoryid]'
print(@query)
execute(@query)
end
GO
Even I can give you guys the DB scripts too...
1.Record Set
CREATE TABLE [dbo].[RECORD SET](
[RecordSetId] [int] NOT NULL,
[InventoryId] [int] NULL,
CONSTRAINT [PK_RECORD SET] PRIMARY KEY CLUSTERED
(
[RecordSetId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (701, 11)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (702, 11)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (703, 12)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (704, 12)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (705, 13)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (706, 13)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (707, 11)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (708, 11)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (709, 19)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (710, 19)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (711, 20)
2.Asset Detail
CREATE TABLE [dbo].[ASSET DETAIL](
[AttributeId] [int] NOT NULL,
[RecordSetId] [int] NULL,
[FieldId] [int] NULL,
[FieldValue] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_ASSET DETAIL] PRIMARY KEY CLUSTERED
(
[AttributeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (801, 701, 201, N'Google')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (802, 701, 202, N'www.Google.com/ncr')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (803, 701, 203, N'USA')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (804, 701, 204, N'Washington')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (805, 701, 205, N'Google Search Engine')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (806, 701, 206, N'Google Web Server')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (807, 701, 207, N'Website')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (808, 701, 208, N'GOOGLE INC')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (809, 702, 201, N'Yahoo')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (810, 702, 202, N'www.yahoo.com')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (811, 702, 203, N'USA')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (812, 702, 204, N'New York')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (813, 702, 205, N'Yahoo Website')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (814, 702, 206, N'Yahoo Web Server')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (815, 702, 207, N'Website')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (816, 702, 208, N'Yahoo Inc')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (817, 703, 217, N'Printer')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (818, 703, 218, N'CP-1st floor')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (819, 703, 219, N'Bangalore-India')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (820, 703, 220, N'India')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (821, 703, 221, N'Printing')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (822, 703, 222, N'HP Laser jet')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (823, 703, 223, N'Print')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (824, 703, 224, N'Infy')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (825, 707, 201, N'WebsitetName1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (826, 707, 202, N'WebsiteUrl1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (827, 707, 203, N'CountryName1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (828, 707, 204, N'KraftRegion1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (829, 707, 205, N'WebAnalytics1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (830, 707, 206, N'HostingProvider1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (831, 707, 207, N'Channel1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (832, 707, 208, N'Company1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (833, 708, 201, N'WebsitetName2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (834, 708, 202, N'WebsiteUrl2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (835, 708, 203, N'CountryName2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (836, 708, 204, N'KraftRegion2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (837, 708, 205, N'WebAnalytics2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (838, 708, 206, N'HostingProvider2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (839, 708, 207, N'Channel2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (840, 708, 208, N'Company2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (841, 709, 229, N'AssetName1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (842, 709, 230, N'AssetType1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (843, 710, 229, N'AssetName2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (844, 710, 230, N'AssetType2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (845, 711, 231, N'Col1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (846, 711, 232, N'Col2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (847, 711, 233, N'Col3')
3.App_fields
CREATE TABLE [dbo].[APP_FIELDS](
[Fieldid] [int] NOT NULL,
[FieldTypeid] [int] NOT NULL,
[FieldName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsMandatory] [bit] NULL,
[IsEditable] [bit] NULL,
[InventoryId] [int] NULL,
[ValidationSize] [int] NULL,
CONSTRAINT [PK_APP_FIELDS] PRIMARY KEY CLUSTERED
(
[Fieldid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (201, 100, N'WebsitetName', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (202, 100, N'WebsiteUrl', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (203, 100, N'CountryName', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (204, 100, N'KraftRegion', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (205, 100, N'WebAnalytics', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (206, 100, N'HostingProvider', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (207, 100, N'Channel', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (208, 100, N'Company', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (209, 100, N'ArticleName', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (210, 100, N'ArrticleLocation', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (211, 100, N'CountryName', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (212, 100, N'KraftRegion', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (213, 100, N'WebAnalytics', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (214, 100, N'ArticleProvider', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (215, 100, N'ArticleChannel', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (216, 100, N'ArticleCompany', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (217, 100, N'AssetName', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (218, 100, N'AssetLocation', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (219, 100, N'CountryName', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (220, 100, N'KraftRegion', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (221, 100, N'AssetAnalytics', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (222, 100, N'AssetProvider', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (223, 100, N'AssetType', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (224, 100, N'AssetCompany', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (225, 100, N'WebsiteName', 0, 0, 17, 0)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (226, 100, N'Region', 0, 0, 17, 0)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (227, 100, N'WebsiteName', 0, 0, 18, 0)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (228, 100, N'Region', 0, 0, 18, 0)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (229, 100, N'AssetName', 1, 0, 19, 50)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (230, 102, N'AssetType', 1, 0, 19, 20)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (231, 100, N'Comobject', 1, 0, 20, 20)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (232, 101, N'Comcomp', 0, 1, 20, 100)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (233, 102, N'ThirdList', 1, 1, 20, 0)
the UI page is like this
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Digital_Asset_Inventory.Default1" %>
<%@ Register src="Header.ascx" tagname="Header" tagprefix="uc1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<link href="/Styles/Site.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
<table border="0" cellpadding="0" cellspacing="0" width="950px" align="center">
<%----%>
<tr><td valign="top"><uc1:Header ID="Header1" runat="server" /></td></tr>
<tr>
<td>
<asp:DropDownList ID="drp_Inventory" runat="server" AutoPostBack="true">
<asp:ListItem Text="Reusable Assets" Value="11"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr><td class="h10"></td></tr>
<%----%>
<tr>
<td>
<asp:GridView ID="gv_UserContents" runat="server" HeaderStyle-CssClass="GridHeadC" CssClass="GridDataC" AllowPaging="true" AllowSorting="true">
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
and the code behind of the page is, this what i tried so far
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace Digital_Asset_Inventory
{
public partial class Default1 : System.Web.UI.Page
{
static string ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["TEST"].ConnectionString.ToString();
public void Page_Load(object sender, EventArgs e)
{
bindgridviewheader(drp_Inventory.SelectedValue.ToString());
}
public DataTable FlipDataTable(DataTable dthead,DataTable dt)
{
DataTable table = new DataTable();
for (int i = 0; i <= dthead.Rows.Count; i++)
{
table.Columns.Add(Convert.ToString(i));
}
DataRow dr;
for (int j = 0; j < dt.Columns.Count; j++)
{
dr = table.NewRow();
dr[0] = dt.Columns[j].ToString();
for (int k = 1; k <= dt.Rows.Count; k++)
dr[k] = dt.Rows[k - 1][j];
table.Rows.Add(dr);
}
return table;
}
public DataTable ConvertasColumns(DataTable d,bool IsHeader)
{
DataTable dtColums = new DataTable();
if (IsHeader)
{
dtColums = new DataTable();
string ColName;
if (d.Rows.Count == 0)
return null;
foreach (DataRow r in d.Rows)
{
ColName = (string)r[0];
dtColums.Columns.Add(new DataColumn(ColName, typeof(string)));
}
}
else
{
dtColums = (DataTable)ViewState["NewTable"];
int RowId;
RowId = Convert.ToInt32(d.Rows[0][0]);
DataRow row = null;
int col = dtColums.Columns.Count;
int idx = 0;
row = dtColums.NewRow();
dtColums.Rows.Add(row);
int J = 0;
int K = dtColums.Columns.Count;
for (int i = 0; i < (d.Rows.Count / dtColums.Columns.Count); i++)
{
row[idx] = d.Rows[i][1];
if (i > 0)
{
J = dtColums.Columns.Count*(i);
K = dtColums.Columns.Count * (i + 1);
row = dtColums.NewRow();
idx++;
dtColums.Rows.Add(row);
}
int n = 0;
for (int j=J; j < K; j++)
{
dtColums.Rows[i][n] = d.Rows[j][1];
n++;
}
}
}
ViewState["NewTable"] = dtColums;
return dtColums;
}
public void bindgridviewheader(string id)
{
DataTable dthead = new DataTable();
DataTable dtdata = new DataTable();
DataTable dt = new DataTable();
using(SqlConnection con=new SqlConnection(ConnStr))
{
string sqlstr = "SELECT FIELDNAME FROM APP_FIELDS WHERE INVENTORYID=" + id + " order by fieldid";
string sqlstrdat = "SELECT RS.recordsetID,Fieldvalue FROM [APP_FIELDS] AF INNER JOIN [ASSET DETAIL] AD ON AD.FIELDID = AF.FIELDID INNER JOIN [RECORD SET] AS RS ON AD.RECORDSETID=RS.RECORDSETID WHERE RS.INVENTORYID = " + id;
SqlCommand cmdhd = new SqlCommand(sqlstr, con);
SqlCommand cmddat = new SqlCommand(sqlstrdat, con);
SqlDataAdapter dahd = new SqlDataAdapter(cmdhd);
SqlDataAdapter dadat = new SqlDataAdapter(cmddat);
dahd.Fill(dthead);
dadat.Fill(dtdata);
dt = ConvertasColumns(dthead,true);
dt = ConvertasColumns(dtdata, false);
gv_UserContents.DataSource = dt;
gv_UserContents.DataBind();
}
}
}
}
and the output so far i got is like
WebsitetName WebsiteUrl CountryName KraftRegion WebAnalytics HostingProvider Channel Company
www.Google.com/ncr www.Google.com/ncr USA Washington Google SearchEngine Google Web Server Website GOOGLE INC
Yahoo USA USA New York Yahoo Website Yahoo Web Server Website Yahoo Inc
WebsitetName1 WebsiteUrl1 Washington KraftRegion1 WebAnalytics1 HostingProvider1 Channel1 Company1
WebsitetName2 WebsiteUrl2 CountryName2 KraftRegion2 WebAnalytics2 HostingProvider2 Channel2 Company2
Try 1 on 29-November-2012 :
Altered in C# code behind yesterday and I can able to bind the resultant Query to
the Gridview as like this..you can see the modified code But still struggling with
SQL Procedure to create it simple and for fast Execution..I am looking for Performance too with my page..
Look at the Code that is Updated from my Last Code;
public partial class Default1 : System.Web.UI.Page
{
static string ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["TEST"].ConnectionString.ToString();
TEST_Business_Entities BusinessLogic = new TEST_Business_Entities();
public void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ViewState["sortOrder"] = "";
AssetDetail("", "", Convert.ToInt32(drp_Inventory.SelectedValue.ToString()));
}
}
private DataTable ConvertasColumns(DataSet AssetDataset)
{
DataTable AssetDataTbl = new DataTable();
string ColName;
if (AssetDataset.Tables[0].Rows.Count == 0)
{
return null;
}
foreach (DataRow r in AssetDataset.Tables[0].Rows)
{
ColName = (string)r[0];
AssetDataTbl.Columns.Add(new DataColumn(ColName, typeof(string)));
}
DataRow row = null;
row = AssetDataTbl.NewRow();
int col = AssetDataset.Tables[0].Rows.Count;
int index = 0;
for (int i = 0; i < AssetDataset.Tables[1].Rows.Count; i++)
{
if (i % col == 0 && i / col != 0)
{
AssetDataTbl.Rows.Add(row);
row = AssetDataTbl.NewRow();
index = 0;
}
row[index] = AssetDataset.Tables[1].Rows[i][0];
index++;
}
AssetDataTbl.Rows.Add(row);
return AssetDataTbl;
}
public void AssetDetail(string sortexp, string sortdirection, int id)
{
using (SqlConnection con = new SqlConnection(ConnStr))
{
ViewState["InventoryID"] = id;
ViewState["sortexp"] = sortexp;
ViewState["sortdirection"] = sortdirection;
DataSet ds = new DataSet();
ds.Tables.Add(ConvertasColumns(BusinessLogic.GetAssetData(id)));
DataView AssetDataView = new DataView();
AssetDataView = ds.Tables[0].DefaultView;
if (sortexp != string.Empty)
{
AssetDataView.Sort = string.Format("{0} {1}", sortexp, sortdirection);
}
gv_UserContents.DataSource = AssetDataView;
gv_UserContents.DataBind();
}
}
public void gv_UserContents_Sorting(object sender, GridViewSortEventArgs e)
{
AssetDetail(e.SortExpression, sortOrder, Convert.ToInt32(ViewState["InventoryID"]));
}
public string sortOrder
{
get
{
if (ViewState["sortOrder"].ToString() == "desc")
{
ViewState["sortOrder"] = "asc";
}
else
{
ViewState["sortOrder"] = "desc";
}
return ViewState["sortOrder"].ToString();
}
set
{
ViewState["sortOrder"] = value;
}
}
protected void gv_UserContents_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gv_UserContents.PageIndex = e.NewPageIndex;
AssetDetail(ViewState["sortexp"].ToString(), ViewState["sortdirection"].ToString(), Convert.ToInt32(ViewState["InventoryID"]));
}
}
Business Access Layer :
public class TEST_Business_Entities
{
public DataSet GetAssetData(int id)
{
return TEST__DataAccess.GetAssetDetail(id);
}
}
Data Access Layer :
public static DataSet GetAssetDetail(int id)
{
DataSet dtTemplate = new DataSet();
SqlDataAdapter da = null;
try
{
using (SqlConnection sqlConn = new SqlConnection(ConnStr))
{
using (SqlCommand SqlCmd = sqlConn.CreateCommand())
{
//SqlCmd.CommandText = "select fieldname from APP_FIELDS where inventoryid = " + id + ";select fieldvalue,rs.recordsetid from [ASSET DETAIL] ad join [record set] rs on ad.recordsetid = rs.recordsetid join [APP_FIELDS] af on af.fieldid = ad.fieldid where rs.inventoryid = " + id + " order by rs.recordsetid,af.fieldid";
SqlCmd.CommandText = "select fieldname from APP_FIELDS where inventoryid = " + id + ";select fieldvalue,rs.recordsetid from [ASSET DETAIL] ad join [record set] rs on ad.recordsetid = rs.recordsetid join [APP_FIELDS] af on af.fieldid = ad.fieldid where rs.inventoryid = " + id + " order by rs.recordsetid,af.fieldid";
SqlCmd.CommandType = CommandType.Text;
if (sqlConn.State != ConnectionState.Open)
{
sqlConn.Open();
}
SqlCmd.Connection = sqlConn;
da = new SqlDataAdapter(SqlCmd);
da.Fill(dtTemplate);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
da = null;
}
return dtTemplate;
}
But for updating and deleting process...
Its better to have stored proc and bind to the Gridview and to customize we can pass a
boolean whether for update or delete the data that we need..
any pointers will be highly appreciated ..
thanks in advance...
Prasyee.