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

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()..)

The call is ambiguous between the following methods or properties:

We’ve learnt that using the named parameters we can make a call to any function and pass the arguments as we wished to; like:

SomeMethod(int a, float b){ }

If we want to call the above method, we can make a call to the function in any of the two ways as shown below:

SomeMethod(a:2, b:2.1F);

Or

SomeMethod(b:2.1F, a:2);

That’s all fine but if we have overloaded method for the above method?; like:

SomeMethod(float b, int a){ }

This is all acceptable because C# allows us to define overloaded methods. But there raises an ambiguousity when we make call to the either of the methods using Named Parameters.
Let us consider a call to above overloaded methods as:

SomeMethod(b:2.1F, a:2);

When we make a call this way, the compiler throws an exception stating: “The call is ambiguous between the following methods or properties”..!!! So, consider not to use Named Parameters when you are dealing with Overloaded Methods/Constructors as that would raise many issues..!!

Note: If we give the different names for the arguments in the overloaded methods, then its possible to use Named Parameters.. 

jQuery click event for dynamically created elements:

After scratching my head for 2-3 hours(before searching it on the internet) on this problem, i got to know the reason why this(.click()) doesn't work on dynamically created elements.

If you want to click on any element(button/span/div/textbox/.....etc) using jQuery, we can do that by using this code,

$('selector').click(function(){
//your code
})

This works fine if you have created that element already.

But, if you created your elements dynamically(using javascript), then this code doesn't work.
Because, .click() will attach events to elements that already exists. As you are dynamically creating your elements using javascript, it doesn't work.

For this you have to use some other functions which works on dynamically created elements. This can be done in different ways..

Earlier we have .live() function

$('selector').live('click', function()
{
//your code
});

but .live() is deprecated.This can be replaced by other functions.

Delegate():

Using delegate() function you can click on dynamically generated HTML elements.

Example:

$(document).delegate('selector', 'click', function()
{
//your code
});

ON():

Using on() function you can click on dynamically generated HTML elements.

Example:

$(document).on('click', 'selector', function()
{
// your code
});

In this way you can click on dynamically generated HTML elements..

For more posts on JQuery visit: JQuery

Dynamically change the stylesheet of a page using javascript/jquery :

We may come across some situations where we need to change the stylesheet of the page dynamically.
This sort of situations arise when we want to give our users more customization(UI). We can acheive that either with javascript or jquery:

$("link").attr("href", relativePathOfTheStyleSheet);

Eg: $("link").attr("href","~/Site.css");

Difference betweene DBNull and Null(when dealing with ADO.NET)

To compare the values against the db values, generally we use NULL to filter out the null values in the db; which is as shown below:

SqlDataReader dr= sqlCommand.ExecuteReader();

While(dr.read())
{
if(dr[index] != null) // Index depends on the no:of values in the table/the column mentioned in the sqlcommand text..
// Some operation x
else
// Some operation y
}

But the thing here is, null in C#/VB is used to check against the reference of the object. But whenever we compare a db value against the null, then the condition becomes true obviously as that is a db value and not the object. Hence, in such sort of conditions we can make use of DBNull.Value property, which could sort out our problem; as shown below:

SqlDataReader dr= sqlCommand.ExecuteReader();

While(dr.read())
{
if(dr[index] != DBNull.Value)
// Some operation x
else
// Some operation y
}

So, make use of DBNull.Value when dealing with ADO.NET.. :)

~Happy coding.. :)

HTTP GET AND HTTP POST

HTTP GET :

Let's say you have a page and want the user to click a link to view the first article in this series.

In this case a simple hyperlink is all you need.

<a href="http://coding-issues.com/Articles/741.aspx">Part I</a>

When a user clicks on the hyperlink in a browser, the browser issues a GET request to the URL

specified in the href attribute of the anchor tag. The request would look like this:

GET http://odetocode.com/Articles/741.aspx HTTP/1.1

Host: odetocode.com

HTTP POST :

Now imagine you have a page where the user has to fill out information to create an account. Filling out information requires <input> tags, and we nest these inputs inside a <form> tag and tell the browser where to submit the information.

<form action="/account/create" method="POST">

<label for="firstName">First name</label>

<input id="firstName" name="firstName" type="text" />

<label for="lastName">Last name</label>

<input id="lastName" name="lastName" type="text" />

<input type="submit" value="Sign up!"/>

</form>

When the user clicks the submit button, the browser realizes the button is inside a form. The form tells the browser that the HTTP method to use is POST, and the path to POST is /account/create. The actual HTTP request the browser makes will look something like this.

POST http://localhost:1060/account/create HTTP/1.1

Host: server.com

firstName=Scott&lastName=Allen

Notice the form inputs are included in the HTTP message.

What is Fragment in URL

The part after the # sign is known as the fragment. The fragment is different than the other pieces we've looked at so far, because unlike the URL path and query string, the fragment is not processed by the server. The fragment is only used on the client and it identifies a particular section of a resource. Specifically, the fragment is typically used to identify a specific HTML element in a page by the element's ID.

Web browsers will typically align the initial display of a webpage such that the top of the element identified by the fragment is at the top of the screen. As an example, the URL http://www.coding-issues.blogspot.com/Asp.Net#feedback has the fragment value "feedback". If you follow the URL, your web browser should scroll down the page to show the feedback section of a particular blog post on my blog.

Your browser retrieved the entire resource (the blog post), but focused your attention to a specific area—the feedback section. You can imagine the HTML for the blog post looking like the following (with all the text content omitted):

<div id="post">

...

</div>

<div id="feedback">

...

</div>

The client makes sure the element with the “feedback” ID is at the top.

Programmatic DataBinding in Asp.Net

An alternative to declarative databinding, you can programmatically bind any of the List controls to a data source

Example:

Lets create a BulletList which shows the list of Fruits.

<asp:BulletedList

ID="blFruits"

runat="server" />

And in Page_Load() event write this code.

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

List<String> fruits = new List<string>();

fruits.Add("Apple");

fruits.Add("Banana");

fruits.Add("Mango");

blFruits.DataSource = fruits;

blFruits.DataBind();

}

}

if(!IsPostBack) prevents the re-initialisation of the BulletList items during postbacks.

Read About Declarative DataBinding : Declarative DataBinding in Asp.Net

Declarative DataBinding in Asp.Net

You can bind any of the List controls to a data source. The List controls support both

declarative databinding and programmatic databinding

Example:

Suppose you have a movies table in your database. It has two columns named Id and Title.

<asp:DropDownList

ID="ddlMovies"

DataSourceID="srcMovies"

DataTextField="Title"

DataValueField="Id"

runat="server" />

<asp:SqlDataSource

ID="srcMovies"

SelectCommand="SELECT Id, Title FROM Movies"

ConnectionString="yourConnectionString"

runat="server" />

The DropDownList control’s DataSourceID property points to the ID of the SqlDataSource control. The SqlDataSource control retrieves the records from the Movies database table. The DropDownList control grabs these records from the SqlDataSource control and creates a ListItem control for each data item.

The DropDownList control has both its DataTextField and DataValueField properties set. When the DropDownList control creates each of its list items, it uses the values of the DataTextField and DataValueField properties to set the Text and Value properties of each list item.

Read About Programmatic DataBinding : ProgrammaticDataBinding in Asp.Net

Radio Button List in Asp.Net

Radio Button List contains list of Radio Buttons.

Example:
<asp:RadioButtonList
ID="rblMovies"
runat="server">
<asp:ListItem
Text="The Remains of the Day"
Value="movie1" />
<asp:ListItem
Text="Star Wars"
Value="movie2" />
<asp:ListItem
Text="Pulp Fiction"
Value="movie3" />
</asp:RadioButtonList>

Above code contains a RadioButtonList control that contains three ListItem controls that correspond to the three radio buttons. All the List controls use the ListItem control to represent individual list items.

The ListItem control supports the following five properties:

Attributes—Enables you to add HTML attributes to a list item.
Enabled—Enables you to disable a list item.
Selected—Enables you to mark a list item as selected.
Text—Enables you to specify the text displayed by the List Item.
Value—Enables you to specify a hidden value associated with the List Item.
You use the Text property to indicate the text that you want the option to display, and the Value property to indicate a hidden value associated with the option. For example, the hidden value might represent the value of a primary key column in a database table.

The Selected property enables you to show a list item as selected. Selected radio buttons and check boxes appear checked. The selected option in a DropDownList is the default option displayed. Selected options in a ListBox appear highlighted. And in the case of a BulletedList control, the selected property has no effect whatsoever.

The Enabled property has different effects when used with different List controls. When you set a ListItem control’s Enabled property to the value False when using the DropDownList or ListBox controls, the list item is not rendered to the browser. When you use this property with a CheckBoxList, RadioButtonList, or BulletedList control, the list item is ghosted and nonfunctional.

Eval() and Bind() Two-Way DataBinding Expressions in Asp.Net

The ASP.NET Framework supports both one-way DataBinding expressions and two-way DataBinding expressions.

In a oneway DataBinding expression, you use the DataBinding expression to display the value of a data item. You use the Eval() method to display the value of a one-way DataBinding expression.

In a two-way DataBinding expression, you not only can display the value of a data item,
you also can modify the value of a data item. You use the Bind() method when working with a two-way DataBinding expression.

Example:

Suppose you have a movies table in your database. It has columns named Id, Title, Director, DateReleased.

<asp:FormView id="FormView1" DataKeyNames="Id" DataSourceId="srcMovies" DefaultMode="Edit" AllowPaging="true" Runat="server">
<EditItemTemplate>

<asp:Label
id="lblTitle"
Text="Title:"
AssociatedControlID="txtTitle"
Runat="server" />

<asp:TextBox
id="txtTitle"
Text='<%#Bind(“Title")%>'
Runat="server" />
<br />

<asp:Label
id="lblDirector"
Text="Director:"
AssociatedControlID="txtDirector"
Runat="server" />

<asp:TextBox
id="txtDirector"
Text='<%#Bind(“Director")%>'
Runat="server" />
<br />

<asp:Button
id="btnUpdate"
Text="Update"
CommandName="Update"
Runat="server" />
</EditItemTemplate>
</asp:FormView>

<asp:SqlDataSource id="srcMovies" ConnectionString="yourConnectionString"
SelectCommand="SELECT Id, Title,Director,DateReleased FROM Movies"
UpdateCommand="UPDATE Movies SET Title=@Title,
Director=@Director WHERE Id=@Id"
Runat="server" />

The FormView contains an EditItemTemplate. The EditItemTemplate contains three TextBox controls. Each TextBox control has a two-way DataBinding expression assigned to its Text property.

The DataBinding expressions associate the TextBox control properties with the properties of the data item being edited. When you click the Update button, any changes you make to the Text properties are updated in the Movies database table.

DataBinding Expressions in Asp.Net

A DataBinding expression is a special type of expression not evaluated until runtime. You mark a databinding expression in a page by wrapping the expression in opening <%# and closing %> brackets.

A DataBinding expression isn’t evaluated until a control’s DataBinding event is raised.

When you bind a DataBound control to a DataSource control declaratively, this event is raised automatically. When you bind a DataSource control to a data source programmatically, the DataBinding event is raised when you call the DataBind() method.

Example:

Suppose you have a movies table in your database. It has two columns named "Title" and "DateReleased".

<asp:DataList id="DataList1" DataSourceId="srcMovies" Runat="server">
<ItemTemplate>
<b>Movie Title:</b> <%#Eval("Title")%>
<br />
<b>Date Released:</b> <%#Eval("DateReleased", "{0:D}") %>
<hr />
</ItemTemplate>
</asp:DataList>

<asp:SqlDataSource
id="srcMovies"
ConnectionString="your connection string"
SelectCommand="SELECT Title,DateReleased FROM Movies"
Runat="server" />

The first DataBinding expression displays the title of the movie and the second DataBinding expression displays the date the movie was released. Both DataBinding expressions call the Eval() method. The Eval() method is a protected method of the Page class. Behind the scenes, the Page.Eval() method calls the static
(shared) DataBinder.Eval() method. If you want to be verbose, instead of using the Eval() method, you could use the following two expressions:

<%# DataBinder.Eval(Container.DataItem, “Title”) %>
<%# DataBinder.Eval(Container.DataItem, “DateReleased”, “{0:D}” ) %>

In ASP.NET version 1.x, you had to use DataBinder.Eval() when displaying data items in a template.
You can use <%# FormatTitle(Eval(“Title”)) %>. This method formats each of the titles displayed by the Repeater control by making each title bold and uppercase

Note: Technically, the Eval() method uses reflection when evaluating the data item to find
a property with a certain name. You do pay a performance penalty when you use
reflection.

As an alternative, you can improve the performance of your DataBinding expressions by casting the data items to a particular type like this:

<%# ((System.Data.DataRowView)Container.DataItem)[“Title”] %>

RequiredFieldValidator on DropDownList

You can use the RequiredFieldValidator control’s InitialValue property to specify a default value other than an empty string.

The RequiredFieldValidator control includes an InitialValue property. The value of the first list from the DropDownList control is assigned to this property.

Example:

<asp:DropDownList id="dropFavoriteColor" Runat="server">
<asp:ListItem Text="Select Color" Value="none" />
<asp:ListItem Text="Red" Value="Red" />
<asp:ListItem Text="Blue" Value="Blue" />
<asp:ListItem Text="Green" Value="Green" />
</asp:DropDownList>

<asp:RequiredFieldValidator id="reqFavoriteColor" Text="(Required)" InitialValue="none" ControlToValidate="dropFavoriteColor" Runat="server" />

FormView: Change Mode of FormView dynamically

FormView :

<asp:FormView runat="server" ID="formViewContactInformation" DataKeyField="ID" DataSourceID="dsContactInformation"> </asp:FormView>

1. To change the mode of FormView to Edit Mode, you have to call ChangeMode method of FormView and pass the mode.

formViewContactInformation.ChangeMode(FormViewMode.Edit);

2. To change the mode of FormView to Insert Mode

formViewContactInformation.ChangeMode(FormViewMode.Insert);

3. To change the mode of FormView to ReadOnly Mode

formViewContactInformation.ChangeMode(FormViewMode.ReadOnly);

Optional Parameters in C#.NET

And now, there’s an interesting lesson called OPTIONAL PARAMETERS that we all need to learn in C#.NET.

Normally, we write our Actual methods which some thing looks like:

Public void someMethod(string userName, string password){ }

And we call/pass the arguments as:

someMethod(“user1”,”password@user1”);

i.e., we pass the arguments from the calling method in the same way as of we’ve declared. But what if we have an option to pass the variables(irrespective of orders)?? And this flexible option is given to us by Optional Parameters. So now I can pass the variables in some way like:

someMethod(“password@user1”,”user1”);

But to let the compiler know which argument you are passing, we need to prefix the argument with the variable name(which is declared in the Actual Method/Called Method). So now I can write as:

someMethod(userName: “user1”, password: “password@user1”);

or

someMethod(password: “password@user1”, userName: “user1”);

So, finally we got to know that how flexible the optional parameters are and that’s even pretty cool…

And another interesting part of optional parameters is:

We can initialize the values of the variables in the Actual Method itself, which would something look like:

Public void someMethod(int x, int y=10, int z=20){ }

So, the magic here is, its not mandatory that we need to pass all the three arguments(except for the non-optional parameters) . So, now I can make a call the above method like:

1: someMethod(x: 5);

or

2: someMethod(x:5, y:20);

or

3: someMethod(x:5, y:20, z:30);

All the above mentioned ways are possible while invoking the function.

So, in the case 1: the values would be: x=5, y=10, z=20 (at the called method). This is because y and z are optional parameters and its not mandatory to pass the values to them...

And in the case 2: the values at the called method would be: x=5, y=20, z=20. This is because as y is an optional parameter its up to us whether to pass the values or not. If we pass a value from the calling method, then that would override the default value( y=20 in this case) and if it is not passed, the default value will be present.

And in the last case(3): The values at the called method would be: x=5, y=20, z=30. The reason is same as of we discussed with case 2.

And this is all pretty about Optional Parameters...J

Note: The convention here to be followed is that we need to declare the optional parameters after all the required parameters i.e., we can have something like:

Public void someMethod(int x, int y=10){ }

but we cannot have something like:

public void someMethod(int x=10,y) { }

So if you try to declare a method which is similar to the last one, the compiler would throw us an error stating: “Optional parameters must appear after all required parameters”.

So, the usage of this optional parameters would serve us better at times… J

Understanding MSIL, CLR and JIT

MSIL = Microsoft Intermediate Language
CLR = Common Language Runtime
JIT = Just In Time compiler

MSIL is the "machine code like" language that the C# compiles your code into.

The CLR is the "machine" that the MSIL runs on.

When runinng the MSIL the CLR converts your code into real machine code using the JIT.

In this context just in time stands for that only the MSIL needed for the moment is beeing compiled into machine code.

The real machine code is run on yor computers actual CPU.
CodeExecution cycle

CodeExecution cycle

WebForms UnobtrusiveValidationMode requires a ScriptResourceMapping for 'jquery'. Please add a ScriptResourceMapping named jquery(case-sensitive)

You need a web.config appSetting key to enable the pre 4.5 validation mode.

Specifies how ASP.NET globally enables the built-in validator controls to use unobtrusive JavaScript for client-side validation logic.

Type: UnobtrusiveValidationMode
Default value: None

Remarks: If this key value is set to "None" [default], the ASP.NET application will use the pre-4.5 behavior JavaScript inline in the pages) for client-side validation logic. If this key value is set to "WebForms", ASP.NET uses HTML5 data-attributes and late bound JavaScript from an added script reference for client-side validation logic.

Add this to your web.config :
<appSettings>
<add key="ValidationSettings:UnobtrusiveValidationMode" value="None" />
</appSettings>

Using statement in C# (ASP.NET)

When you are dealing with objects in c#, you have to make sure that when you are done with the object, the object's Dispose method is called.

This can be done using try and finally blocks, where you use the object in try Block and destroy the object in finally block. But this can be easily done by "using" statement.

The using statement gets translated into a try and finally block. A using statement is translated into three parts: acquisition, usage, and disposal. The resource is first acquired, then the usage is enclosed in a try statement with a finally clause. The object then gets disposed in the finally clause. For example the following lines of code using the using statement,

Normally in the try catch block we would have written:

SqlConnection con;
try
{
con=new Sqlconnection(connectionString);
}
catch
{
//Do Something with the exception..
}
finally
{
con.close(); //Disposing the object manually.
}

But using block has made this simple(rather to dispose objects manually) :

using(SqlConnection con=new Sqlconnection(connectionString))
{
con.open();
//Do Some Operation
}

And now, you dont need to worry about the object's memory release; since using block takes care of it automatically by invoking con.close() and everyother object that is used inside it.. :)

And thus, it helps us in managing the memory in a more efficient way..

Note: One advantage with the using block is that, even in the case if the statements in that block raises an exception, that block disposes the memory.. And thats good of it.. And even we need to look at the cons of it like: what if there is an error while acquiring the source? (like if we use the filestream), in that case we should use using block nested inside the try-catch block... :)

Clear all fields in ASP.NET form

You can make use of OnClientClick event. This will reset all all the control present on the form. OnClientClick="this.form.reset();return false;"

See the Code :

<asp:Button ID="Reset_Button" runat="server" Text="Reset"
Width="81px" OnClientClick="this.form.reset();return false;" />

Subscribe to: Posts (Atom)