Basic SQL StoredProcedure for CRUD operations

DESCRIPTION

Basic SQL StoredProcedure for Create, Read, Update, Delete (CRUD) operations.

SOLUTION

This example will be using the OrderID, OrderDate, Freight, ShipName and ShipCountry columns from the Orders table of the Northwind database. But you can test other tables as well.

Northwind Database Diagram

If you do not have the Northwind database, you can download the sql file from Northwind database - GitHub and import it via SQL Server Management Studio (SSMS), or the Server Explorer in Visual Studio.

Creating the StoredProcedures

Below you will find the Basic Procedures for Creating, Inserting, Updating and Deleting records of the Orders table.

CREATE

CREATE PROCEDURE [dbo].[InsertProcedure]
    @OrderDate datetime,
    @Freight money,
    @ShipName nvarchar(40),
    @ShipCountry nvarchar(15)
AS
    INSERT INTO [Orders] ([OrderDate], [Freight], [ShipName], [ShipCountry]) VALUES (@OrderDate, @Freight, @ShipName, @ShipCountry)
GO

READ

CREATE PROCEDURE [dbo].[SelectProcedure]
AS
    SELECT [OrderID], [OrderDate], [Freight], [ShipName], [ShipCountry] FROM [Orders]
GO

UPDATE

CREATE PROCEDURE [dbo].[UpdateProcedure]
    @OrderID int,
    @OrderDate datetime,
    @Freight money,
    @ShipName nvarchar(40),
    @ShipCountry nvarchar(15)
AS
    UPDATE [Orders] SET [OrderDate] = @OrderDate, [Freight] = @Freight, [ShipName] = @ShipName, [ShipCountry] = @ShipCountry WHERE [OrderID] = @OrderID
GO

DELETE

CREATE PROCEDURE [dbo].[DeleteProcedure]
    @OrderID int
AS
    DELETE FROM [Orders] WHERE [OrderID] = @OrderID
GO

The official Microsoft documentation for Creating stored procedure can be found at MSDN - Create a stored procedure. You can also search on the internet for tutorials. There are many good examples with a variety of scenarios.

Testing the StoredProcedures

Now, let's test the StoredProcedures. One of the quickest and easiest ways is to use the Telerik WebForms Grid as it requires little to nothing to configure.

This scenario requires a valid ConnectionString with the name NorthwindConnectionString to work. If you haven't done it yet, be sure to create a ConnectionString to the web.config. For more details, check out the ConnectionString section in the bottom.

Here is a code snippet with the Telerik WebForms Grid and a SqlDataSource configured to handle the Create, Read, Update, Delete (CRUD) operations using the SQL StoredProcedures we created above.

Example 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" OnInserting="SqlDataSource1_Inserting" OnUpdating="SqlDataSource1_Updating"

    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>

ConnectionStrings

Depending on your environment, you might need to use different connections strings. In the SQL Server connection strings article, you will find different ConnectionStrings for different scenarios. (e.g. if using database files .md, or if using SQLExpress, maybe LocalDB, etc.)

In the web.config, add a key to the <connectionStrings> element with a relevant ConnectionString.

<connectionStrings>
    <add name="NorthwindConnectionString" connectionString="Your_ConnectionString_Goes_Here" providerName="System.Data.SqlClient"/>
</connectionStrings>

In case you're using other Database servers, you can check out The Connection Strings Reference article.