Need to add composite keys to sql data source table : The Official Microsoft ASP.NET Forums

Reply

mcupryk

Member

243 Points

392 Posts

Need to add composite keys to sql data source table

Jul 31, 2012 04:18 AM|LINK

1) question is this right syntax wise

protected void RadGrid1_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
        {
            this.RadGrid1.DataSource = this.SqlDataSourceRateDataTable;
            this.SqlDataSourceRateDataTable.PrimaryKey = new DataColumn[]
            {
                this.SqlDataSourceRateDataTable.Columns["SnapShotID"],
                this.SqlDataSourceRateDataTable.Columns["Type"],
                this.SqlDataSourceRateDataTable.Columns["Engine"],
                this.SqlDataSourceRateDataTable.Columns["Usage"]
                   
            };
        }

CREATE TABLE [dbo].[OpRate](
	[SnapShotID] [int] NOT NULL,
	[Type] [int] NOT NULL,
	[Engine] [nvarchar](255) NOT NULL,
	[Usage] [nvarchar](255) NOT NULL,
	[UsageRate] [nvarchar](255) NULL,
	[MaxTerm] [float] NULL,
 CONSTRAINT [PK_OpRates] PRIMARY KEY CLUSTERED
(
	[SnapShotID] ASC,
	[Type] ASC,
	[Engine] ASC,
	[Usage] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

2) If a user wants to do an update how can I setup

   protected void RadGrid1_UpdateCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
        {
            GridEditableItem editedItem = e.Item as GridEditableItem;
            UserControl userControl = (UserControl)e.Item.FindControl(GridEditFormItem.EditFormUserControlID);

            //string DataKeyName = editedItem.OwnerTableView.DataKeyValues[editedItem.ItemIndex]["SnapShotID"].ToString();  // You will get the DataKeyName
            int rowindex = editedItem.ItemIndex;  //Row index to identify the row edited


DataKeyName ????

UpdateCommand="UPDATE
	dbo.OpRate
SET
	UsageRate 
	=  
	CASE Usage
		WHEN 'H' THEN @UsageRate_Heavy_Rate
		WHEN 'M' THEN @UsageRate_Medium_Rate
		WHEN 'L' THEN @UsageRate_Light_Rate
		ELSE UsageRate
	END,
	MaxTerm
	=
	CASE Usage
		WHEN 'H' THEN @MaxTerm_Heavy_Rate
		WHEN 'M' THEN @MaxTerm_Medium_Rate 
		WHEN 'L' THEN @MaxTerm_Light_Rate 
		ELSE MaxTerm
	END
WHERE
	SnapShotID = 1
AND
	Type = @Type
AND
	Engine = @Engine
AND
	Usage IN ('H',' M','L')">
                                <SelectParameters>
                                    <asp:Parameter DefaultValue="1" Name="SnapShotID"  Type="Int32" />
                                </SelectParameters>
                                <UpdateParameters>
                                    <asp:Parameter Name="UsageRate_Heavy_Rate" Type="String" />
                                    <asp:Parameter Name="UsageRate_Medium_Rate" Type="String" />
                                    <asp:Parameter Name="UsageRate_Light_Rate" Type="String" />
                                    <asp:Parameter Name="MaxTerm_Heavy_Rate" Type="Decimal" />
                                    <asp:Parameter Name="MaxTerm_Medium_Rate" Type="Decimal" />
                                    <asp:Parameter Name="MaxTerm_Light_Rate" Type="Decimal" />
                                    <asp:Parameter Name="Type" Type="Int32" />
                                    <asp:Parameter Name="Engine" Type="String" />
                                    <asp:Parameter Name="Usage" Type="String" />
                                </UpdateParameters>
                            </asp:SqlDataSource>

www.omegalove.com