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.