Tuesday, 5 November 2013

ExecuteScalar Example in ASP.NET Using C# and VB.NET

ALTER PROCEDURE CountTest
     @id int
AS
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;
     DECLARE @count int=0
     SELECT @count=COUNT(Name) from Employee WHERE Emp_ID =@id
     SELECT @count
END
GO


In ASP.NET:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>ExecuteScalar Example in ASP.NET Using C# and VB.NET</title>
    <style type="text/css">
    .color
    {
        color:Red;
    }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
       <tr>
          <td> Count:</td>
          <td><asp:Label runat="server" ID="lblCount" CssClass="color"></asp:Label>
          </td>
       </tr>
       <tr>
          <td>Name in First Row First Column:</td>
          <td><asp:Label runat="server" ID="lblString" CssClass="color"></asp:Label></td>
       </tr>
       <tr>
         <td><asp:Button ID="btnCount" Text="Get Count" runat="server" style="margin-left:100px"
                 onclick="btnCount_Click" /></td>
         <td><asp:Button ID="btnName" Text="Get Name" runat="server"
                 onclick="btnName_Click" /></td>
       </tr>   
    </table>
    </div>
    </form>
</body>
</html>

In C#:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class SPCountTest : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
    protected void btnCount_Click(object sender, EventArgs e)
    {
        //below code is for get the count (integer value)       
        SqlCommand cmd = new SqlCommand("CountTest", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@id", 1);
        con.Open();
        int i;
        i = Convert.ToInt32(cmd.ExecuteScalar());
        con.Close();
        lblCount.Text = i.ToString();
    }
    protected void btnName_Click(object sender, EventArgs e)
    {
        //below code is for get the Name (String value)
        SqlCommand cmd1 = new SqlCommand("SELECT NAME FROM EMPLOYEE", con);
        cmd1.CommandType = CommandType.Text;
        con.Open();
        string s;
        s = (string)(cmd1.ExecuteScalar());
        con.Close();
        lblString.Text = s;
    }
}

In VB.NET:
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Partial Public Class SPCountTest
    Inherits System.Web.UI.Page
    Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
    Protected Sub btnCount_Click(ByVal sender As Object, ByVal e As EventArgs)
        'below code is for get the count (integer value)       
        Dim cmd As New SqlCommand("CountTest", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id", 1)
        con.Open()
        Dim i As Integer
        i = Convert.ToInt32(cmd.ExecuteScalar())
        con.Close()
        lblCount.Text = i.ToString()
    End Sub
    Protected Sub btnName_Click(ByVal sender As Object, ByVal e As EventArgs)
        'below code is for get the Name (String value)
        Dim cmd1 As New SqlCommand("SELECT NAME FROM EMPLOYEE", con)
        cmd1.CommandType = CommandType.Text
        con.Open()
        Dim s As String
        s = DirectCast(cmd1.ExecuteScalar(), String)
        con.Close()
        lblString.Text = s
    End Sub
End Class

The output of the above code as shown in the below figure.
"If you like my blog or articles, you can appreciate by leaving your comments or Liking my Facebook pageAspdotnet-kishore, following on Google+ Aspdotnet-Kishore, Twitter  on AspdotnetKishore, Linked in Aspdotnet-Kishore, stumbling my posts on stumble upon and subscribing on  RSSfeed Aspdotnet-Kishore for free updates directly to your Email inbox . Watch my blog  for more articles."

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.