Automatic CRUD operations using SQL StoredProcedures

DESCRIPTION

Using SqlDataSource with SQL StoredProcedures for Automatic CRUD operations to Insert, Update, Delete records in RadGrid.

Generally speaking, to handle Automatic CRUD operations (whether using StoredProcedure or Transact-SQL), the only required Grid specific settings is associating the SqlDataSource Control and enabling a few properties. The rest depends on the SqlDataSource Control's configuration.

This article will show an example of configuring the Grid and the SqlDataSource to handle CRUD operations automatically while using SQL StoredProcedures.

SOLUTION

This approach works with all EditModes (Batch, EditForm, PopUp, InPlace).

Grid specific settings

• Set the DataSourceID property to the ID of the SqlDataSource Control.
• Set the AllowAutomaticInserts property to true
• Set the AllowAutomaticUpdates property to true
• Set the AllowAutomaticDeletes property to true
• Define the PrimaryKey (PK) of the database table in the DataKeyNames property
• Set the AutoGenerateEditColumn to true - to use the built-in Edit Column
• Set the AutoGenerateDeleteColumn to true - to use the built-in Delete Column
• Set the CommandItemDisplay property to Top, Bottom or TopAndBottom - to enable the Command Bar on Top, Bottom or Top and Bottom of the Grid.

SqlDataSource specific settings

• Provide a connection string to the ConnectionString property. For more details you can check out the ConnectionStrings section at the bottom of the Basic SQL StoredProcedure for CRUD operations Knowledge Base article.
• Set the InsertCommandType, SelectCommandType, UpdateCommandType, and DeleteCommandType properties to StoredProcedure
• Set the InsertCommand, SelectCommand, UpdateCommand, and DeleteCommand properties to point to the SQL StoredProcedure names. If you do not have any SQL StoredProcedures, you can find a few basic examples in the Basic SQL StoredProcedure for CRUD operations Knowledge Base article.
• Add parameters for Insert, Update, and Delete operations. The parameters will be set to the field names you are planning to update.
• Optional: Add parameter to the Select operation if the StoredProcedure is taking a parameter to select records based one or more field values.

Complete Code Snippet

<telerik:RadGrid ID="RadGrid1" runat="server" AllowPaging="True" Width="800px"
    AutoGenerateEditColumn="true"
    AutoGenerateDeleteColumn="true"
    DataSourceID="SqlDataSource1"
    AllowAutomaticInserts="true"
    AllowAutomaticUpdates="true"
    AllowAutomaticDeletes="true">

    <MasterTableView DataSourceID="SqlDataSource1" AutoGenerateColumns="False" CommandItemDisplay="Top"
        DataKeyNames="OrderID">
        <Columns>
            <telerik:GridNumericColumn DataField="OrderID" DataType="System.Int32"
                FilterControlAltText="Filter OrderID column" HeaderText="OrderID"
                ReadOnly="True" SortExpression="OrderID" UniqueName="OrderID">
            </telerik:GridNumericColumn>
            <telerik:GridDateTimeColumn DataField="OrderDate" DataType="System.DateTime"
                FilterControlAltText="Filter OrderDate column" HeaderText="OrderDate"
                SortExpression="OrderDate" UniqueName="OrderDate">
            </telerik:GridDateTimeColumn>
            <telerik:GridNumericColumn DataField="Freight" DataType="System.Decimal"
                FilterControlAltText="Filter Freight column" HeaderText="Freight"
                SortExpression="Freight" UniqueName="Freight">
            </telerik:GridNumericColumn>
            <telerik:GridBoundColumn DataField="ShipName"
                FilterControlAltText="Filter ShipName column" HeaderText="ShipName"
                SortExpression="ShipName" UniqueName="ShipName">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="ShipCountry"
                FilterControlAltText="Filter ShipCountry column" HeaderText="ShipCountry"
                SortExpression="ShipCountry" UniqueName="ShipCountry">
            </telerik:GridBoundColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
    InsertCommandType="StoredProcedure" InsertCommand="InsertProcedure"
    SelectCommandType="StoredProcedure" SelectCommand="SelectProcedure"
    UpdateCommandType="StoredProcedure" UpdateCommand="UpdateProcedure"
    DeleteCommandType="StoredProcedure" DeleteCommand="DeleteProcedure">

    <InsertParameters>
        <asp:Parameter Name="OrderDate" DbType="DateTime" />
        <asp:Parameter Name="Freight" DbType="Decimal" />
        <asp:Parameter Name="ShipName" DbType="String" />
        <asp:Parameter Name="ShipCountry" DbType="String" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="OrderID" DbType="Int32" />
        <asp:Parameter Name="OrderDate" DbType="DateTime" />
        <asp:Parameter Name="Freight" DbType="Decimal" />
        <asp:Parameter Name="ShipName" DbType="String" />
        <asp:Parameter Name="ShipCountry" DbType="String" />
    </UpdateParameters>
    <DeleteParameters>
        <asp:Parameter Name="OrderID" DbType="Int32" />
    </DeleteParameters>
</asp:SqlDataSource>

See Also

• Basic SQL StoredProcedure for CRUD operations