ExecuteNonQuery
ExecuteNonQuery method will return number of rows effected with INSERT, DELETE or UPDATE operations.This ExecuteNonQuery method will be used only for insert, update and delete, Create, and SETstatements.
Before implement this example first design one table UserInformation in your database as shown below
Column Name
|
Data Type
|
Allow Nulls
|
UserName
|
varchar(50)
|
Yes
|
LastName
|
varchar(50)
|
Yes
|
Location
|
Varchar(50)
|
Yes
|
Once table designed in database write the following code in your aspx page
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Example of ExecuteNonQuery in asp.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" /><br />
<b>Number of Rows Effected: </b><asp:Label ID="lblDetails" runat="server" />
</div>
</form>
</body>
</html>
|
Now add the following namespaces in code behind
C# Code
using System;
using System.Data.SqlClient;
|
After add namespaces write the following code in code behind
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection con=new SqlConnection("Data Source=SREENIVAS-PC\\SQLEXPRESS;Initial Catalog=Users;User ID=sa;Integrated Security=true"))
{
con.Open();
SqlCommand cmd = new SqlCommand("insert into UserInformation(UserName,FirstName,LastName,Location) values(@Name,@FName,@LName,@Location)", con);
cmd.Parameters.AddWithValue("@Name", "testname"
cmd.Parameters.AddWithValue("@FName", "test");
cmd.Parameters.AddWithValue("@LName", "t");
cmd.Parameters.AddWithValue("@Location","hyd");
int result= cmd.ExecuteNonQuery();
if(result>=1)
{
lblDetails.Text = result.ToString();
}
else
{
lblDetails.Text = "0" ;
}
con.Close();
}
}
ExecuteScalar
Execute Scalar will return first row first column value i.e. it will return single value and ignore other values on execution of SQL Query or Stored procedure using command object. It’s very fast to retrieve single values from database.
Before implement this example first design one table UserInformation in your database as shown below
Column Name
|
Data Type
|
Allow Nulls
|
UserName
|
varchar(50)
|
Yes
|
LastName
|
varchar(50)
|
Yes
|
Location
|
Varchar(50)
|
Yes
|
Once table designed in database enter some dummy data to test after that write the following code in your aspx page
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Example of ExecuteNonQuery in asp.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" /><br />
<b>First Row First Column Value: </b><asp:Label ID="lblDetails" runat="server" />
</div>
</form>
</body>
</html>
|
Now add the following namespaces in code behind
C# Code
using System;
using System.Data.SqlClient;
|
After add namespaces write the following code in code behind
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection con=new SqlConnection("Data Source=SREENIVAS-PC\\SQLEXPRESS;Initial Catalog=Users;User ID=sa;Integrated Security=true"))
{
con.Open();
SqlCommand cmd = new SqlCommand("Select UserName,LastName,Location FROM UserInformation", con);
string result = (string)cmd.ExecuteScalar();
if (!string.IsNullOrEmpty(result))
{
lblDetails.Text = result;
}
else
{
lblDetails.Text = "No value Selected" ;
}
con.Close();
}
}
ExecuteReader
Execute Reader will be used to return the set of rows, on execution of SQL Query or Stored procedure using command object. This one is forward only retrieval of records and it is used to read the table values from first to last.
Before implement this example first design one table UserInformation in your database as shown below
Column Name
|
Data Type
|
Allow Nulls
|
UserName
|
varchar(50)
|
Yes
|
LastName
|
varchar(50)
|
Yes
|
Location
|
Varchar(50)
|
Yes
|
Once table designed in database enter some dummy data to test after that write the following code in your aspx page
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Example of ExecuteReader in asp.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<b>Bind Gridview with ExecuteReader command object Data</b><br /><br />
<asp:GridView ID="gvUserInfo" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White"/>
</asp:GridView>
</div>
</form>
</body>
</html>
|
Now add the following namespaces in code behind
C# Code
using System;
using System.Data.SqlClient;
using System.Data;
|
After add namespaces write the following code in code behind
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridview();
}
}
// This method is used to bind gridview from database
protected void BindGridview()
{
using (SqlConnection con=new SqlConnection("Data Source=SREENIVAS-PC\\SQLEXPRESS;Initial Catalog=Users;User ID=sa;Integrated Security=true"))
{
con.Open();
SqlCommand cmd = new SqlCommand("Select UserName,LastName,Location FROM UserInformation", con);
SqlDataReader dr = cmd.ExecuteReader();
gvUserInfo.DataSource = dr;
gvUserInfo.DataBind();
con.Close();
}
}
|
|
|
No comments:
Post a Comment