Saturday, 10 September 2016

Execute Reader, Execute Scalar, Execute NonQuery in ADO.NET

12:39    No comments

Execute Reader
It is used when data returned by query is in a table form.
Let's see with an example:
select * from EmployeeTest




In the aspx file lets take a gridview
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="gdvEmlpoyee" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
                <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                <EditRowStyle BackColor="#999999" />
                <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#E9E7E2" />
                <SortedAscendingHeaderStyle BackColor="#506C8C" />
                <SortedDescendingCellStyle BackColor="#FFFDF8" />
                <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
            </asp:GridView>
        </div>
    </form>
</body>

</html>

In code behind add the following code

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ADONET
{
    public partial class ExecuteReader : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
            using (SqlConnection sqlcon = new SqlConnection(CS))
            {
                SqlCommand sqlcmd = new SqlCommand("Select * from EmployeeTest", sqlcon);
                sqlcon.Open();
                SqlDataReader sqlreader = sqlcmd.ExecuteReader();
                gdvEmlpoyee.DataSource = sqlreader;
                gdvEmlpoyee.DataBind();
                sqlcon.Close();
            }
        }
    }
}

Lets Execute our application we will get the following output


Execute Scalar
It is used to when single value is returned by the query.
In the aspx file lets take a label
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Label ID="lblName" runat="server"></asp:Label>
        </div>
    </form>
</body>
</html>

 In code behind add the following code

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ADONET
{
    public partial class ExecuteScalar : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
            using (SqlConnection sqlcon = new SqlConnection(CS))
            {
                SqlCommand sqlcmd = new SqlCommand("Select EmpDesignation from EmployeeTest where EmpID=1", sqlcon);
                sqlcon.Open();
                string strName = (String)sqlcmd.ExecuteScalar();
                lblName.Text = strName;
                sqlcon.Close();
            }
        }
    }
}

Lets Execute our application we will get the following output


Execute NonQuery
It is used when we are inserting, updating or deleting data in database.
In the aspx file lets take a gridview
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Label ID="lblMessage" runat="server" Text="Before Insert operation"></asp:Label><br /><br />
            <asp:GridView ID="gdvEmlpoyee" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
                <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                <EditRowStyle BackColor="#999999" />
                <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#E9E7E2" />
                <SortedAscendingHeaderStyle BackColor="#506C8C" />
                <SortedDescendingCellStyle BackColor="#FFFDF8" />
                <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
            </asp:GridView>
        </div>
        <div>
            <br /><br />
            <asp:Label ID="lblMessage1" runat="server" Text="After Insert operation"></asp:Label><br /><br />
            <asp:GridView ID="gdvEmployeeInsert" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
                <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                <EditRowStyle BackColor="#999999" />
                <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#E9E7E2" />
                <SortedAscendingHeaderStyle BackColor="#506C8C" />
                <SortedDescendingCellStyle BackColor="#FFFDF8" />
                <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
            </asp:GridView>
        </div>
    </form>
</body>
</html>

In code behind add the following code

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ADONET
{
    public partial class ExecuteNonQuery : System.Web.UI.Page
    {
        public int count = 0;
        protected void Page_Load(object sender, EventArgs e)
        {
            String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
            using (SqlConnection sqlcon = new SqlConnection(CS))
            {
                ShowGrid(sqlcon);
                sqlcon.Open();
                SqlCommand sqlcmd1 = new SqlCommand("insert into EmployeeTest values(7,'Preeti','Software Engineer')", sqlcon);
                sqlcmd1.ExecuteNonQuery();
                sqlcon.Close();
                ShowGrid(sqlcon);
            }
        }
        public void ShowGrid(SqlConnection sqlcon)
        {
            SqlCommand sqlcmd = new SqlCommand("Select * from EmployeeTest", sqlcon);
            sqlcon.Open();
            SqlDataReader sqlreader = sqlcmd.ExecuteReader();
            if(count==0)
            {
                gdvEmlpoyee.DataSource = sqlreader;
                gdvEmlpoyee.DataBind();
                count++;
            }
            else
            {
                gdvEmployeeInsert.DataSource = sqlreader;
                gdvEmployeeInsert.DataBind();
            }
            sqlcon.Close();
        }
    }
}

Lets Execute our application we will get the following output


0 comments:

Post a Comment