Display records in ASP.NET from SQL database using Stored Procedure

I am creating a new Stored Procedure in Visual studio. Stored procedure takes data from database and its data displayed by DetailsView. Stored Procedure is precompiled group of Transact-SQL statements and it is fast, consistent and reliable.  We no need to compile it again and again.

Step 1: Click on Tools on menu bar of Microsoft Visual Studio.
Step 2: Click on Connect to Database, as shown the following figure.



Figure 1

Step 3: After clicking you will see Add Connection dialogue box. Enter your server name, use any one from Windows or SQL Server authentication and choose your database.

Step 4: Click on Test Connection button to know connection established properly or not. Click OK button on Test Connection box, then Click OK button on dialogue box as well, which should now look like figure 2.



Figure 2

Step 5: Expand master database in Data Connections option.



Figure 3

Step 6: Right click on Stored Procedures and Click on Add New Stored Procedure.



Figure 4

Step 7: You will see this type of window by default (figure 5).



Figure 5

Step 8: Edit dbo.StoredProcedure... \sqlexpress.master), which should look like figure 6.



Figure 6

We are creating Stored Procedure on Employee1 table that's look like figure 7.



Figure 7

Step 9: Select all from dbo.StoredProcedure and then right click from mouse. Click on Run Selection option to execute Stored Procedure, which should look like below figure 8.



Figure 8

You will get output look like below figure 9.



Figure 9

Step 10: Add 1 DetailSView and SqlDataSource control.

Step 11: Configure SqlDataSource. Click on Configure Data Source, as shown the figure 10.



Figure 10

Step 12: Select connection string name from drop down list or you can create new connection by click on New Connection button. Follow step 3 and 4, then click on Next button. This should look like figure 11.



Figure 11

Step 13: Select first radio button which specify a custom SQL statement or stored procedure and then click on Next button (see below figure 12).



Figure 12

Step 14: Click on Stored Procedure radio button and then select SP_Employee stored procedure name from drop down list.



Figure 13

Step 15: Click on Test Query button to know data bound properly or not and then click on Finish button.



Figure 14

Step 16: Select data source id from DetailsView. Click right top arrow on DetailsView  and then select data source from drop down list. This should look like figure 15.



Figure 15

Default.aspx
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeFile="Default.aspx.cs" Inherits="_Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px"
        DataSourceID="SqlDataSource1" AllowPaging="True">
    </asp:DetailsView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        SelectCommand="SP_Employee" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
    </asp:Content>

Debug this application and see the output should look like figure 16.



Figure 16