Stored Procedure with ADO.NET Entity Data Model in ASP.NET

Stored Procedure with ADO.NET Entity Data Model in ASP.NET

In this article I have described how to use Stored Procedure in ASP.NET using ADO.NET Entity Data Model. Normal Stored Procedure andParameterized Stored Procedure both are described here and binded with ADO.NET Entity Data Model. For demonstration I have usedASP.NET 2010 with Framework 4.0 and SQL Server 2008. Steps are given below

Step 1: First created one table and two stored procedure (one normal and second with parameter). Query are given below

-- Create database CREATE DATABASE dbEntityFramework GO -- Used created database USE dbEntityFramework GO -- Create table CREATE TABLE [dbo].[tblPatientStatusDetails] (       [SNo] INT IDENTITY PRIMARY KEY,       [PatientID] INT NOT NULL,       [AdmitDate] DATETIME NOT NULL,       [DischargeDate] DATETIME NOT NULL,       [PatientName] VARCHAR(100) NOT NULL,       [Treatment] VARCHAR(100) NOT NULL,       [Doctor] VARCHAR(50) NOT NULL,       [PatientStatus] VARCHAR(500) NULL       ) GO -- Insert value into table INSERT [dbo].[tblPatientStatusDetails] ([PatientID], [AdmitDate], [DischargeDate],[PatientName], [Treatment], [Doctor], [PatientStatus]) VALUES ('101','2013-01-05 00:00:00.000' ,'2013-01-06 00:00:00.000', 'Jacob', 'Canavan disease', 'Dr. Jemsh', 'Good') INSERT [dbo].[tblPatientStatusDetails] ([PatientID], [AdmitDate], [DischargeDate],[PatientName], [Treatment], [Doctor], [PatientStatus]) VALUES ('101','2013-01-06 00:00:00.000' ,'2013-01-06 00:00:00.000', 'Jacob', 'Aphasia', 'Dr. Devid', 'Good') INSERT [dbo].[tblPatientStatusDetails] ([PatientID], [AdmitDate], [DischargeDate],[PatientName], [Treatment], [Doctor], [PatientStatus]) VALUES ('101','2013-01-15 00:00:00.000' ,'2013-01-15 00:00:00.000', 'Joshua ', 'Vocal fold cysts', 'Dr. Mark', 'Good') INSERT [dbo].[tblPatientStatusDetails] ([PatientID], [AdmitDate], [DischargeDate],[PatientName], [Treatment], [Doctor], [PatientStatus]) VALUES ('101','2013-01-05 00:00:00.000' ,'2013-01-08 00:00:00.000', 'Michael', 'Ectropion', 'Dr. Freank', 'Good') INSERT [dbo].[tblPatientStatusDetails] ([PatientID], [AdmitDate], [DischargeDate],[PatientName], [Treatment], [Doctor], [PatientStatus]) VALUES ('101','2013-01-02 00:00:00.000' ,'2013-01-09 00:00:00.000', 'Michael', 'Bladder cancer', 'Dr. Alderson', 'Good') GO -- Stored Procedure with no parameter CREATE PROCEDURE [dbo].[spPatientStatusDetailsInfo] AS SELECT[SNo],[PatientID],[PatientName],[Treatment],[AdmitDate],[DischargeDate],[Doctor],[PatientStatus]FROM tblPatientStatusDetails GO -- Stored Procedure with Parameter CREATE PROCEDURE [dbo].[spPatientStatusDetails] ( @SNo INT, @PatientID INT ) AS SELECT[SNo],[PatientID],[PatientName],[Treatment],[AdmitDate],[DischargeDate],[Doctor],[PatientStatus]FROM tblPatientStatusDetails WHERE [SNo]=@SNo AND [PatientID] = @PatientID GO

Screen Shot of Table

Figure 1:

Step 2: Create ASP.NET application with ASP.NET Web Site, ASP.NET Empty Web Site or open any existing ASP.NET application. As you prefer you can chose, but for demonstration, here I have chosen “ASP.NET Empty Web Site “and created two .aspx page (e.g. Default.aspx andDefault2.aspx).

Step 3: Drag and Drop GridView Control from toolbox on .aspx (e.g. Default.aspx) page, where we display records. See below image (Figure 2).

 Figure 2:

Note: If you want to modify GridView Design then use below line of code in place of default generated GridView code.

Defult.aspx

<asp:GridView ID="GridView1" runat="server" ShowHeaderWhenEmpty="false"AutoGenerateColumns="False">             <Columns>                 <asp:TemplateField>                     <HeaderTemplate>                         S.No.</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblPatientID" runat="server" Text='<%#Bind("SNo")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         Patient ID</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblPatientID" runat="server" Text='<%#Bind("PatientID")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         PatientName</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblPatientName" runat="server" Text='<%#Bind("PatientName")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         Treatment</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblTreatment" runat="server" Text='<%#Bind("Treatment")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         Admit Date</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblAdmitDate" runat="server" Text='<%#Bind("AdmitDate")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         Discharge Date</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblDischargeDate" runat="server" Text='<%#Bind("DischargeDate")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         Doctor</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblDoctor" runat="server" Text='<%#Bind("Doctor")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         Patient Status</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblPatientStatus" runat="server" Text='<%#Bind("PatientStatus")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>             </Columns>         </asp:GridView>

Drag and Drop TextBox, Button and GridView Control from toolbox on second .aspx (e.g. Default2.aspx) page, where we entered value for parameterized stored procedure and display records into GridView.

Default2.aspx

<div>         <fieldset style="width: 230px;">             <legend>Login</legend>             <table>                 <tr>                     <td style="text-align: right">                         <span>S.No. :</span>                     </td>                     <td>                         <asp:TextBox ID="txtSNo" runat="server" Width="150px"></asp:TextBox>                     </td>                 </tr>                 <tr>                     <td>                         <span>Patient ID :</span>                     </td>                     <td>                         <asp:TextBox ID="txtPatientID" runat="server" Width="150px"></asp:TextBox>                     </td>                 </tr>                 <tr>                     <td>                     </td>                     <td style="text-align: center;">                         <asp:Button ID="btnLogin" runat="server" Text="Button"                             onclick="btnLogin_Click"/>                     </td>                 </tr>             </table>         </fieldset>     </div> <div>         <asp:GridView ID="GridView1" runat="server" ShowHeaderWhenEmpty="false"AutoGenerateColumns="False">             <Columns>                 <asp:TemplateField>                     <HeaderTemplate>                         S.No.</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblPatientID" runat="server" Text='<%#Bind("SNo")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         Patient ID</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblPatientID" runat="server" Text='<%#Bind("PatientID")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         PatientName</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblPatientName" runat="server" Text='<%#Bind("PatientName")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         Treatment</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblTreatment" runat="server" Text='<%#Bind("Treatment")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         Admit Date</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblAdmitDate" runat="server" Text='<%#Bind("AdmitDate")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         Discharge Date</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblDischargeDate" runat="server" Text='<%#Bind("DischargeDate")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         Doctor</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblDoctor" runat="server" Text='<%#Bind("Doctor")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField>                     <HeaderTemplate>                         Patient Status</HeaderTemplate>                     <ItemTemplate>                         <asp:Label ID="lblPatientStatus" runat="server" Text='<%#Bind("PatientStatus")%>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>             </Columns>         </asp:GridView>     </div>

Step 4: Add ADO.NET Entity Data Model in your application. For adding ADO.NET Entity Data Model, right click on application name from solution explorer and select “Add New Item” option, then select ADO.NET Entity Data Model option and give model’s name as below image.

Figure 3:

Note: If popup window (as below image) appears then click button Yes. It ask for create App_Code folder where ADO.NET Entity Data Modelwill be created.

Figure 4:

Step 5: After clicking button Yes, Entity Data Model Wizard will appear as below image for configure data source.

Figure 5:

Click on Next to proceed

Step 6: Here you choose your database connection. Click on button New Connection as below image and follow next steps.

Figure 6:

Step 7: Fill information of your database as below image.

Figure 7:

Note:

1.       Put your server name. Here I have given dot (.) operator. Here dot (.) operator indicate local server.

2.       Chose your server authentication

3.       Select your database name. Here I have given dbEntityFramework as database name; because in step 1 we have created own database name is dbEntityFramework.

4.       We can check our connection here.

5.       Click on button OK for moving next steps.

Step 8: Chose options as below image and click button Next.

Figure 8:

Step 9: Select option from appeared new window of Entity Data Model Wizard as below image.

Figure 9:

Note:

1.       Select required table and Stored Procedures.

2.       Check both options.

3.       Change Model Namespace name or left default.

4.       Click on button Finish for completing Entity Data Model Wizard.

Step 10: Select Entity .edmx (e.g. StoredProcEntityDataModel.edmx) and right click on .edmx page and select Model Browser option. See below image.

Figure 10:

Step 11:  Select Function Import option and then Add Function Import option as below image.

Figure 11:

Step 12: Now select option from Add Function Import wizard for adding stored procedure (spPatientStatusDetailsInfo) reference as below image.

Figure 12:

Note:

1.       Give function name

2.       Select stored procedure name.

3.       Select Entities name.

4.       You can check your query

5.       Click button OK for moving next steps.

Follow same step (Step 12) for added referenced of second stored procedure (spPatientStatusDetails) as below image.

Step 13: You can check added stored procedure references in Model Browser explorer as below image.

Figure 13:

Step 14: Now, open Designer.cs (e.g.  StoredProcEntityDataModel.Designer.cs) page. Here you can see namespace (e.g.dbEntityFrameworkModel), class (e.g. dbConnection) and methods (function) (e.g. PatientStatusDetailsInfo and PatientStatusDetails) name, which are we have given in above steps, remember these name for farther use.

Step 15: Open first .cs page (e.g. Default.aspx.cs) and add entity model namespace (e.g. dbEntityFrameworkModel) and write below line of code within Page_Lode event.

using System; using dbEntityFrameworkModel; public partial class _Default : System.Web.UI.Page {     protected void Page_Load(object sender, EventArgs e)     {         dbConnection obj = new dbConnection();         GridView1.DataSource = obj.PatientStatusDetailsInfo();         GridView1.DataBind();     } }

Note: Build and Save application and run .aspx (e.g. Default.aspx) page and see output as below image.

Figure 14:

Step 15: Open second .cs page (e.g. Default2.aspx.cs) and add entity model namespace (e.g. dbEntityFrameworkModel) and write below line of code within button click event.

using System; using dbEntityFrameworkModel; public partial class Default2 : System.Web.UI.Page {     protected void Page_Load(object sender, EventArgs e)     {     }     protected void btnLogin_Click(object sender, EventArgs e)     {         dbConnection obj = new dbConnection();         GridView1.DataSource = obj.PatientStatusDetails(Convert.ToInt32(txtSNo.Text.Trim()),Convert.ToInt32(txtPatientID.Text.Trim()));         GridView1.DataBind();     } }

Note: Build and Save application and run .aspx (e.g. Default.aspx) page and see output as below image.

Figure 15: