How do I pass through parameter values to @T (MSSQL Table Type) in a Stored procedure that updates TableX?
Background:
Example MSSQL TableX Create and Load
CREATE TABLE [dbo].[TableX](
[ID] [char](9) NOT NULL,
[Status] [char](10) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO TableX
(ID,Status)
VALUES
(1,'OPEN')
,(2,'OPEN')
,(3,'OPEN')
,(4,'OPEN')
,(5,'OPEN')
GO
The stored procedure:
CREATE TYPE [dbo].[TableX_TableType] AS TABLE(
[ID] [char](9) NOT NULL
)
GO
CREATE PROCEDURE [dbo].[Update_TableX]
@T TableX_TableType READONLY
,@Status char(10)
AS
SET NOCOUNT ON
/*This procedure sets the Status field with @Status for multiple records of Table X joined on _T.
The procedure accepts two parameters:
@T can contain multiple IDs (char(9))
@Status values possible are: ('Open', 'Close') varchar(20)
*/
UPDATE tx
SET tx.[Status] = @Status
FROM TableX tx
INNER JOIN @T t
ON tx.[ID] = t.[ID]
GO
-- Declare a variable that references the type.
DECLARE @Temp AS TableX_TableType;
-- Add data to the table variable
INSERT INTO @Temp VALUES
(2),(3),(4)
;
-- Pass the table variable data to a stored procedure.
EXEC [Update_TableX] @Temp,'CLOSE';
GO
Example Main Page:
On the client-side in Radzen, I have customized the C# generated code for TableX so the DataGrid bound to Table X collects multiple record id's into @multipleValues.
To populate Status, Button1 has been added with Title='Bulk Edit'.
The Button1 OnClick Event opens the Edit TableX dialog where the Status field is empty.
NOTE: The intention is to allow users to enter a Status change and have it applied to all records with matching ID in @T.
<RadzenContent Container="main">
<ChildContent>
<RadzenHeading Size="H1" Text="Example Main">
</RadzenHeading>
<div class="row">
<div class="col-md-12">
<RadzenButton Icon="add_circle_outline" style="margin-bottom: 10px" Text="Add" Click="@Button0Click">
</RadzenButton>
<RadzenButton Icon="edit" style="margin-bottom: 10px" Text="Edit" Click="@Button1Click">
</RadzenButton>
<RadzenDataGrid @ref="grid0" FilterCaseSensitivity="FilterCaseSensitivity.CaseInsensitive"
AllowPaging="true" AllowSorting="true" AllowFiltering="true"
Data="@TableXs" TItem="TableX" PageSize="8" ColumnWidth="200px"
FilterMode="FilterMode.Advanced" LogicalFilterOperator="LogicalFilterOperator.Or"
EditMode="DataGridEditMode.Multiple"
RowSelect="@Grid0RowSelect"
SelectionMode="DataGridSelectionMode.Multiple" @bind-Value=@multipleValues>
<Columns>
<RadzenDataGridColumn TItem="Sample.Models.SampleDB.TableX"
Width="40px" Sortable="false" Filterable="false" Title="BulkEdit">
<EditTemplate Context="data">
<RadzenCheckBox TriState="false" TValue="bool" Value="@(TableXs.Any(i => multipleValues != null && multipleValues.Contains(i)))"
Change="@(args => multipleValues = args ? TableXs.ToList() : null)" />
</EditTemplate>
<Template Context="data">
<RadzenCheckBox TriState="false" Value="@(multipleValues != null && multipleValues.Contains(data))" />
</Template>
</RadzenDataGridColumn>
<RadzenDataGridColumn TItem="Sample.Models.SampleDB.TableX" Property="ID" Title="ID">
</RadzenDataGridColumn>
<RadzenDataGridColumn TItem="Sample.Models.SampleDB.TableX" Property="Status" Title="Status">
</RadzenDataGridColumn>
<RadzenDataGridColumn TItem="Sample.Models.SampleDB.TableX" Filterable="false" Sortable="false" TextAlign="TextAlign.Center" Width="70px">
<Template Context="SampleModelsSampleDBTableX">
<RadzenButton ButtonStyle="ButtonStyle.Danger" Icon="close" Size="ButtonSize.Small" Click="@((args) =>GridDeleteButtonClick(args, SampleModelsSampleDBTableX))" @onclick:stopPropagation="true">
</RadzenButton>
</Template>
</RadzenDataGridColumn>
</Columns>
</RadzenDataGrid>
@code { IEnumerable<TableX> TableXs;
IEnumerable<Statuses> status;
IList<TableX> multipleValues;
int count;
protected override void OnInitialized()
{
TableXs = dbContext.TableXs.ToList();
}
</div>
</div>
</ChildContent>
</RadzenContent>
Example Dialog Box (Edit TableX):
<RadzenContent Container="main">
<ChildContent>
<div class="row">
<div class="col-md-12">
<RadzenTemplateForm Data="@TableX" TItem="Sample.Models.SampleDB.TableX" Visible="@(efparrest != null)" Submit="@Form0Submit">
<ChildContent>
<div style="margin-bottom: 1rem" class="row">
<div class="col-md-3">
<RadzenLabel Component="Status" style="width: 100%" Text="Status">
</RadzenLabel>
</div>
<div class="col-md-9">
<RadzenTextBox MaxLength="10" style="width: 100%" TabIndex="3" Name="Status">
</RadzenTextBox>
</div>
</div>
<div class="row">
<div class="col offset-sm-3">
<RadzenButton ButtonStyle="ButtonStyle.Primary" ButtonType="ButtonType.Submit" Icon="save" Text="Save" Click="@Button1Click">
</RadzenButton>
<RadzenButton ButtonStyle="ButtonStyle.Light" style="margin-left: 1rem" Text="Cancel" Click="@Button2Click">
</RadzenButton>
</div>
</div>
</ChildContent>
</RadzenTemplateForm>
</div>
</div>
</ChildContent>
</RadzenContent>
Two Questions:
1. How to display the dialog box with a Status field (it displays, but with only a header)?
2. How to:
- Invoke [Update_TableX] from the 'Save' button OnClick Event,
- pass two parameters (@T, @Status),
- receive a return code,
- populate an error message if the return code != 0.
As things stand, the @multipleValues from the Main Page is **not** available as a parameter in the context of the Dialog box parameters.
2a) What is the best approach to @multipleValues formatting in Radzen generated code? String list, a temporary table, XML, other?
Any help appreciated -