Resources » .NET programming » Databases

Sql ExecuteNonQuery() and ExecuteReader() with examples


Posted Date: 29-Jul-2013  Last Updated:   Category: Databases    
Author: Member Level: Silver    Points: 15


Here i am discussing about the ExecuteNonQuery() and ExecuteReader() sql methods. Why these methods are used and how we can use these methods to get data from database and how we can execute sql queries. I am going to give some examples which makes this concept to understand easily.



1. ExecuteNonQuery() :

ExecuteNonQuery executes a Transact-SQL like INSERT, DELETE or UPDATE statement against the connection and will return the number of rows effected with sql operations. ExecuteNonQuery method will be used only for insert, update and delete, Create, and SET statements. The ExecuteNonQuery does not return any rows, any output parameters or return values mapped to parameters are populated with data. To get data from database we use ExecuteReader() method(which will be discussed later).

Example:

Consider that you have a table called student in your database. The student table having the fields Id, Name , Standard , Marks.

Now to insert a new record into student table we use ExecuteNonQuery() like this:

code in aspx page

Name:<asp:TextBox runat="server" ID="tbName"></asp:TextBox>

Standard:<asp:TextBox runat="server" ID="tbStandard"></asp:TextBox>

Marks:<asp:TextBox runat="server" ID="tbMarks"></asp:TextBox>

<asp:Button runat="server" ID="btnSubmit" Text="Submit" OnClick="btnSubmit_Click" />


In your code behind file add the following event

protected bool AddContact_Click(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnection("write your connection string here..");
SqlCommand command = new SqlCommand("Insert into Students (Name,Standard,Marks) Values (@Name, @Standard, @Marks)", connection);
command.Parameters.AddWithValue("@Name",tbName.Text );
command.Parameters.AddWithValue("@Standard", tbStandard.Text);
command.Parameters.AddWithValue("@Marks", tbMarks.Text);

try
{
connection.Open();
int n=command.ExecuteNonQuery();
if(n > 0){
return true;
}
else{
return false;
}
}
finally
{
connection.Close();
}
}


look at the line where i am writing int n=command.ExecuteNonQuery();. This will return the number of rows effected. If record is succesfully added in table, then it will return 1 indicating that 1 row got effected. So, in n>0 i am returning true which tells that the record is inserted.

2. ExecuteReader();

ExecuteReader method sends the CommandText to the Connection and builds a SqlDataReader. Execute Reader is used to return the set of rows, on execution of SQL Query. This one is forward only retrieval of records and it is used to read the table values from first to last.

Note: If you set the CommandType property to StoredProcedure, the CommandText property should be set to the name of the stored procedure. The command executes this stored procedure when you call ExecuteReader.

Example:
Consider the same student table discussed above. If that table has some rows say 20. If you want to read(or retrieve) all the rows you can do it using executeReader() method.

Consider you have a student class with properties Id, Name, Standard and Marks. You can add the rows read using executeReader to this object.

Write this method in your code behind file.

protected void GetStudents()
{
//define student object

Student student;

//create a students list to add all your individual students
List students = new List();


SqlConnection connection = new SqlConnection("write your connection string here..");
SqlCommand command = new SqlCommand("Select * from Students", connection);
try
{
connection.Open();

SqlDataReader reader = command.ExecuteReader();

//now you got all the rows in reader
while (reader.Read())
{
student = new Student();
student.Id = int.parse(reader["Id"].ToString());
student.Name = reader["Name"].ToString();
student.Standard = int.parse(reader["Standard"].ToString());
student.Marks = int.parse(reader["Marks"].ToString());
students.Add(contact); //add individual student to students list
}
reader.Close();


In this way you can get all the records from a database table using ExecuteReader() method.


Did you like this resource? Share it with your friends and show your love!

Responses to "Sql ExecuteNonQuery() and ExecuteReader() with examples"

No responses found. Be the first to respond...

Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Priya jain
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India