ExecuteReader in Asp.Net with Example

ExecuteReader is forward only retrieval of records and it is used to read the table values from first to last. It sends the CommandText to the Connection and builds a SqlDataReader.

Example :

Consider you have a " Employees " table in your database.

Now , you want to fetch all the record from your Employee table and show them in a GridView.

Add this code to youe aspx Page(Here i am using a GridView control to show Employee records).

<form id="form1" runat="server">
<asp:GridView ID="EmployeesList" runat="server">
</asp:GridView>
</form>

Write this code in your code behind file(Name.aspx.cs) :

//Add Following NameSpaces

using System;
using System.Data.SqlClient;
using System.Data;

//Add some code to your Page_Load Event

protected void Page_Load(object sender, EventArgs e)
{
if (! IsPostBack) //When you are posting the page for the first time then IsPostBack is false and !IsPostBack it becomes true
{
BindGridview();
}
}

// This method is used to bind gridview from database
protected void BindGridview()
{
using (SqlConnection connection = new SqlConnection("Write Your Connection string here.."))
{
connection.Open();
SqlCommand cmd = new SqlCommand("Select FirstName,LastName FROM Employees", connection);

SqlDataReader reader = cmd.ExecuteReader();
EmployeesList.DataSource = reader; //Adding datasource to our GridView
Employees.DataBind();  //Binding data to GridView
connection.Close();
}
}

In this way you can fetch(retrieve) records from your DataBase table using ExecuteReader  

Read more...

ExecuteNonQuery() in Asp.Net With Example

ExecuteNonQuery executes a SQL command(Insert,Update or Delete commands) against the connection and returns the number of rows affected.

Example :

Consider you have a " Employee " table in your database.

Now , you want to insert a record into your Employee table.

Consider you have to enter two values i.e., FirstName and LastName into your table. For that , write this code into your aspx(Name.aspx) page..

 <form id="form1" runat="server">
        <table>
            <tr>
                <td>FirstName</td>
                <td><asp:TextBox runat="server" ID="txtFirstName"> </asp:TextBox> </td>
            </tr>
            <tr>
                <td>LastName</td>
                <td><asp:TextBox runat="server" ID="txtLastName"> </asp:TextBox></td>
            </tr>
            <tr>
                <td><asp:Button runat="server" ID="btnSubmit" Text="SUBMIT" OnClick="btnSubmit_Click"/>                </td>
                <td></td>
            </tr>
        </table>
 </form>

The above form contains two textboxes to enter FirstName and LastName and a Button to Submit the data.

Note: Note the onClick attribute of Button. The event btnSubmit_Click is called when you click on the button.

In your code behind file(Name.aspx.cs) write this code...

//Add these two namespaces
using System;
using System.Data.SqlClient;

// Add btnSubmit_Click event(which is fired when you click on button) below Page_Load() event..

protected void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection connection=new SqlConnection("Write your connection string here.."))
{
connection.Open();
SqlCommand cmd = new SqlCommand("insert into Employee(FirstName,LastName) values(@FName,@LName)", connection);

//then add parameters to your command

cmd.Parameters.AddWithValue("@FName", txtFirstName.Text());
cmd.Parameters.AddWithValue("@LName", txtLastName.Text());

int result= cmd.ExecuteNonQuery();  //here you will get the no.of rows effected 

if(result > 0)  // It means atleast one row has been effected 
{
response.write("Employee added");
}
else
{
response.write("Error in adding Employee");
}
connection.Close();  //Don't forget to close the connection when you are done with the connection.
}
}

In this way, we can add records to our DataBase table(Using ExecuteNonQuery()..)

Read more...

Difference between inner and outer join in SQL

Assume you have two tables A and B.

⦁ Inner join of A and B gives the intersect of A and B. Inner join requires atleast one match in both tables. (i.e., in A and B)

⦁ Outer join of A and B gives the unoin of A and B. Outer join returns all the record from the left table even though they don't have any common record.

Example:

Assume you have two tables A and B. Both tables have one column in common:

Table A Table B

A     B

ID     ID
1     6
2     1
3     5
4     2

Inner join :

It results the intersection of the two tables, i.e. the two rows they have in common.

select * from A INNER JOIN B on A.ID = B.ID;

The result will be...
1  1
2  2

Left outer join :

It will return all rows from A , plus any common rows from B.

select * from A LEFT OUTER JOIN B on A.ID = B.ID

Result :
1   1
2   2
3   null
4   null


Full outer join :

It will return the union of A and B, i.e. All the rows in A and all the rows in B.

select * from A FULL OUTER JOIN B on A.ID = B.ID;

   1      1
   2      2
   3     null
   4     null
  null    5
  null    6

Read more...

Case statement in sql Server

Case expression can be used in Sql. This can be used with SELECT list, WHERE clause, HAVING clauses, IN list, DELETE and UPDATE statements.

Case is used to check one Expression over multiple values.

Syntax:

CASE Expression
WHEN expression1 THEN value1
WHEN expression2 THEN value2
ELSE value3
END

Example:

Consider a situation where you have to show EmployeeID and EmployeeName , so that if EmployeeID is 1 then the name should be 'John', when EmployeeID is 2 then the name should be 'Smith' and when the EmployeeID is 3, then the name should be 'Adam'. It can be done using CASE as below...

Select EmployeeID , EmployeeName =
CASE EmployeeID
WHEN  1 THEN 'John'
WHEN 2 THEN 'Smith'
WHEN 3 THEN 'Adam'
ELSE 'Something'
END From EmployeeTable.

For More Information :Case statement in Sql

Read more...

LINQ Single and SingleOrDefault

Single means you are expecting only one result from LINQ query. If you get more than one or zero matches, then it throws exception.

SingleOrDefault means if you get single result, it returns that result but if it doesn't find any matches it returns default value but you will not get any exception.

Read more...

Return only one result from LINQ Query

You can do it in two ways, either use First or use Single.

First returns only first row, even there are multiple rows

Single expects only one row to be returned, and if there are multiple rows, it throws exception.

So, use Single if you are expecting only one row to be returned.

EX: var book= from book in store where (book.price > 100) select book).First();
Generally , the query returns all books having price greater than 100. but , as we are using  " .First() " only first book will be returned.

Read more...