Sql ExecuteNonQuery() and ExecuteReader() with examples
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
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.