ASP.NET Display records from SQL database using Stored Procedure

Create a new stored procedure in Visual Studio by opening the Server Explorer window, expanding a Data Connection, Add new stored procedure from Stored Procedures.
  • 4185
 

 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.

1Connection.gif

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.

2Sql.gif

Figure 2

Step 5: 
Expand master database in Data Connections option.

3ServerExplorer.gif

Figure 3

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

4AddNewSP.gif

Figure 4

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


Figure 5

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

6eDIT.gif

Figure 6

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

7Table.gif

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.

8RunSelection.gif
Figure 8

You will get output look like below figure 9.

9Output.gif

Figure 9

Step 10: 
Add 1 DetailSView and SqlDataSource control.

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

10Configure.gif

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.

11ChooseDataConnection.gif

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).

12selectradio.gif

Figure 12

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

13selectSP.gif

Figure 13

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

14TestQuery.gif

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.

15.gif

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.


16output.gif

Figure 16

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.