InventoryID

InventoryID

关注
发信
关注(28)粉丝(399)

需要SQL过程进行更新，删除动态列和行并绑定到前端的asp.net gridview以进行更新和删除操作

本文介绍了需要SQL过程进行更新，删除动态列和行并绑定到前端的asp.net gridview以进行更新和删除操作的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 大家好 我有三张桌子 1. [记录集] Hi AllI have three tables 1.[record set]RecordSetIdInventoryId701 11702 11703 12704 12705 13706 13707 11708 11709 19710 19711 20 2.app_fields 2.app_fieldsFieldidFieldTypeidFieldNameIsMandatoryIsEditableInventoryId ValidationSize201100 WebsitetName1 0 11 NULL202100 WebsiteUrl1011NULL203100 CountryName1011NULL204100 KraftRegion1011NULL205100 WebAnalytics1011NULL206100 HostingProvider1011NULL207100Channel 1011NULL208100 Company1011NULL209100ArticleName1013NULL210100ArrticleLocation1013NULL211100CountryName1013NULL212100KraftRegion1013NULL213100WebAnalytics1013NULL214100ArticleProvider1013NULL215100ArticleChannel1013NULL216100ArticleCompany1013NULL217100AssetName1012NULL218100AssetLocation1012NULL219100CountryName1012NULL220100KraftRegion1012NULL221100AssetAnalytics1012NULL222100AssetProvider1012NULL223100AssetType1012NULL224100AssetCompany1012NULL225100WebsiteName00170226100Region00170227100WebsiteName00180228100Region00180229100AssetName101950230102AssetType101920231100Comobject102020232101Comcomp0120100233102ThirdList11200 3. [资产明细] 3.[Asset detail]AttributeIdRecordSetIdFieldIdFieldValue801701201Google802701202www.Google.com/ncr803701203USA804701204Washington805701205Google Search Engine806701206Google Web Server807701207Website808701208GOOGLE INC809702201Yahoo810702202www.yahoo.com811702203USA812702204New York813702205Yahoo Website814702206Yahoo Web Server815702207Website816702208Yahoo Inc817703217Printer818703218CP-1st floor819703219Bangalore-India820703220India821703221Printing822703222HP Laser jet823703223Print824703224Infy825707201WebsitetName1826707202WebsiteUrl1827707203CountryName1828707204KraftRegion1829707205WebAnalytics1830707206HostingProvider1831707207Channel1832707208Company1833708201WebsitetName2834708202WebsiteUrl2835708203CountryName2836708204KraftRegion2837708205WebAnalytics2838708206HostingProvider2839708207Channel2840708208Company2841709229AssetName1842709230AssetType1843710229AssetName2844710230AssetType2845711231Col1846711232Col2847711233Col3 我希望输出基于inventoryid [例如：inventoryid = 11 ]所以我这样查询并加载相应的数据。以及最重要的事情要记住......所有的表都是动态的，并且将由管理员使用excel表进行更新，并且在UI部分中，用户必须单独启用更新操作。我需要更新存储过程中的操作 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 procedureSELECT 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 isFIELDNAMEFIELDVALUEWebsitetNameGoogleWebsiteUrlwww.Google.com/ncrCountryNameUSAKraftRegionWashingtonWebAnalyticsGoogle Search EngineHostingProviderGoogle Web ServerChannel WebsiteCompany GOOGLE INCWebsitetNameYahooWebsiteUrlwww.yahoo.comCountryNameUSAKraftRegionNew YorkWebAnalyticsYahoo WebsiteHostingProviderYahoo Web ServerChannel WebsiteCompany Yahoo Inc 我想要将结果表转换为inventoryid = 11和表格标题，数据应该排列成这样的行 我想要的输出 I want to transpose the resultant table with for the inventoryid=11 with the table headers and Datas should be arranged in rows like thisthe output i want to be WebsitetNameWebsiteUrl CountryName KraftRegion WebAnalytics HostingProvider Channel CompanyGoogle www.Google.com USA Washington Google Search Engine Google Web Server Website GOOGLE INCyahoo www.yahoo.com .............................................................................WebsitetName1 .................................................................................. 我到目前为止尝试的是 只是粘贴第一次尝试的伪代码： what i tried so far isjust pasting the pseudocode of a first try:DECLARE #temptable tabletemptable=select * from app_fields where inventoryid=11 into ##temptable where 1=2Declare @RecordSetId,FieldIdDeclare @TableDataset @ReacordSetId=select RecordSetId from [Record Set] from where inventoryid=11set FieldId=temptable[0]foreach row in temptableTableData+= 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 )GOSELECT * FROM [ASSET DETAIL] -------------------------------- ---------------------------- 第二件事是存储的proc： ------------------------------------------------------------the second thing is the stored proc :CREATE PROCEDURE [dbo].[SP_APP_FIELDS] @inventoryid [int]WITH EXECUTE AS CALLERASBEGINDECLARE @cols AS NVARCHAR(MAX), @cols_WITH_MAX AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);-- @inventoryidSELECT @cols_WITH_MAX =STUFF((SELECT DISTINCT ',MAX('+ QUOTENAME(fieldname) +') AS ' + QUOTENAME(fieldname) FROM app_fieldsWHERE inventoryid = @inventoryid FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')select @cols =STUFF((SELECT distinct ',' + QUOTENAME(fieldname) from app_fieldswhere 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)endGO 即使我也可以给你们DB数据库脚本...... 1.Record Set Even I can give you guys the DB scripts too...1.Record SetCREATE 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]ENDGOINSERT [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.资产明细 2.Asset DetailCREATE 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]ENDGOSET ANSI_PADDING OFFGOINSERT [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 3.App_fieldsCREATE 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]ENDGOSET ANSI_PADDING OFFGOINSERT [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 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"> <%--***** HEADER SECTION *****--%> <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> <%--****** DATA SECTION TO UPDATE ******--%> <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 and the code behind of the page is, this what i tried so farusing 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 % col == 0) //{ //if (i != 0) //{ 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++) { //row = dtColums.NewRow(); 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 + " order by AF.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 = FlipDataTable(dthead,dt); 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 ca n 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;我< 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; Da taSet 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 $b$ b { 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 : and the output so far i got is likeWebsitetName 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 f ieldname 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; } fi nally { 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..\t \t any pointers will be highly appreciated .. thanks in advance... Prasyee.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.推荐答案While I did not go through the whole thing, looking at the desired output you need a pivot query! blatant plug for article Pivot two or more columns in SQL Server 2005[^]. This may help it is perfectly valid for 2008 and later!While I did not go through the whole thing, looking at the desired output you need a pivot query!blatant plug for article Pivot two or more columns in SQL Server 2005[^]. This may help it is perfectly valid for 2008 and later!Hi you could try following query: Hi you could try following query:DECLARE @FieldName NVARCHAR(MAX)SET @FieldName = 'SELECT 'SELECT @FieldName = @FieldName + ''''+AD.FIELDVALUE + ''''+ ' AS ' + AF.FIELDNAME+ ',' FROM[APP_FIELDS] AFINNER JOIN [ASSET DETAIL] AD ON AD.FIELDID = AF.FIELDIDINNER JOIN [RECORD SET] AS RS ON AD.RECORDSETID=RS.RECORDSETID WHERE RS.INVENTORYID =11SET @FieldName = LEFT(@FieldName, LEN(@FieldName) - 1)EXEC sp_Executesql @FieldName 这篇关于需要SQL过程进行更新，删除动态列和行并绑定到前端的asp.net gridview以进行更新和删除操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！
10-29 00:10
Powered by LMLPHP ©2024 InventoryID 0.004467