Introduction

We all know <%=expression%> features in asp.net. We can print any string on page from there. Mostly we are using them in asp.net mvc. Now we have one new features with asp.net 4.0 that we have HTML Encoded Expressions and this prevent Cross scripting attack as we are html encoding them.

ASP.NET 4.0 introduces a new expression syntax <%: expression %> which automatically convert string into html encoded. Let’s take an example for that.
I have just created an hello word protected method which will return a simple string which contains characters that needed to be HTML Encoded. Below is code for that.

protected static string HelloWorld()
{
   return "Hello World!!! returns from function()!!!>>>>>>>>>>>>>>>>>";
}

Now let’s use the that hello world in our page html like below. I am going to use both expression to give you exact difference.

<form id="form1" runat="server">
<div>
   <strong><%: HelloWorld()%></strong>
</div>
<div>
   <strong><%= HelloWorld()%></strong>
</div>
</form>

Now let’s run the application and you can see in browser both look similar. 



But when look into page source html in browser like below you can clearly see one is HTML Encoded and another one is not.



Cool, right?? Happy Programming.

Please stay focus in the first part of this article. In this article, we will focus on taking advantage of the new support for stored procedures in MySQL 5.0. We will be building upon the example application created in Part 1, so please refer to the scripts in the previous article if you wish to create the sample database and ASP.NET application.

MySQL Stored Procedures

A stored procedure is made up of one or more SQL statements or commands and is stored within the database. Stored procedures can be used to perform any type of database operation such as retrieving one or more rows, inserting, updating, deleting data, or perhaps multiple database operations at once.

Before moving on, let’s take a look at creating a stored procedure. Launch MySQL Query Browser, connect to your versedb database, open a new Script Tab, and execute the following script.

DELIMITER $$

DROP PROCEDURE IF EXISTS `versedb`.`usp_Verse_GetList`$$
CREATE PROCEDURE `usp_Verse_GetList`()
BEGIN
  SELECT  verse_id,
          verse_text,
          verse_ref
  FROM    verse
  ORDER BY
          verse_ref DESC;
END$$

DELIMITER ;

This simple stored procedure retrieves all the rows in the verse table ordered by verse reference in descending order. To see this stored procedure in action, open a new Query Tab and execute the following command. 

CALL usp_Verse_GetList(); 

What you should see (assuming you have data in your verse table from Part 1), is a list of all the rows in your table ordered by the verse reference in descending order, just as you might expect.

One of the primary benefits of using stored procedures is that SQL statements and logic can be maintained apart from the applications that use them. So, instead of embedding SQL commands in your application, your application only needs to know how to execute the stored procedures it needs.

Stored procedures also support parameters. In this way, a single stored procedure can be used in many scenarios without having to be modified. For example, you can create a stored procedure that can retrieve a single row in your table based upon a primary key value passed as a parameter.

DELIMITER $$

DROP PROCEDURE IF EXISTS `versedb`.`usp_Verse_Get_By_Id`$$
CREATE PROCEDURE `usp_Verse_Get_By_Id`(v_id INT)
BEGIN
  SELECT  verse_id,
          verse_text,
          verse_ref
  FROM    verse
  WHERE   verse_id = v_id;
END$$

To see the stored procedure in action, execute the following command.

CALL usp_Verse_Get_By_Id(2);

What you should see is a single row returned from the database where the verse_id field is equal to 2. To retrieve a different row, you would simply replace the value 2 with a different valid primary key value. If you specify a value that does not exist in the table, then no rows would be returned.

A couple more things to mention: it is possible to define output parameters as well as for input, and stored procedures do not have to return any rows. We will look at an example of both in a later article, but imagine the scenario of creating a stored procedure to insert a new row into a table. You might pass the values for the row as input parameters, return the new primary key ID assigned to the inserted row as an output parameter, and would not need to return any rows of data.

Calling MySQL Stored Procedures from ASP.NET

Executing a stored procedure using ASP.NET is nearly identical to executing straight SQL. Unless a procedure requires parameters, you only need to provide the name of the stored procedure in the command text, and specify the CommandType property is of type StoredProcedure

// Get the MySQL connection string stored in the Web.config
string cnnString = ConfigurationSettings.AppSettings["ConnectionString"];

// Create a connection object and data adapter
MySqlConnection cnx = new MySqlConnection(cnnString);
MySqlDataAdapter adapter = new MySqlDataAdapter();

// Create a SQL command object
string cmdText = "usp_Verse_GetList";
MySqlCommand cmd = new MySqlCommand(cmdText, cnx);

// Set the command type to StoredProcedure
cmd.CommandType = CommandType.StoredProcedure;

// Create and fill a DataSet
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds);

One difference you might notice from the previous article is retrieving the connection string from the web.config using ConfigurationSettings.AppSettings, which is typically best practice. If you are not familiar with this technique, you would simply create an block in your web.config that looks like the following

<appsettings>
  <add key="ConnectionString" value="Server=localhost;Port=3306;Database=versedb;Uid=root;Pwd=mySecret" />
  </add>
</appsettings>

You would need to add the section directly after and before. If you already have an section defined, you only need to insert the node for the connection string.

Executing Stored Procedures with Parameters

To execute a stored procedure with parameters, you must create and add a MySqlParameter object to the MySqlCommand.Parameters collection for each required parameter.  Also, parameter names in MySQL use a prefix of "?" which is similar to Microsoft SQL Server's use of "@."

// Hard-coding the Verse ID for example only
int verseID = 2;

// ...Code to create connection goes here...

// Create a SQL command object
string cmdText = "usp_Verse_Get_By_Id";
MySqlCommand cmd = new MySqlCommand(cmdText, cnx);

// Set the command type to StoredProcedure
cmd.CommandType = CommandType.StoredProcedure;

// Create the verse ID parameter
MySqlParameter param;
param = new MySqlParameter("?v_id", MySqlDbType.Int32);
param.Value = verseID;
param.Direction = ParameterDirection.Input;
cmd.Parameters.Add(param);

// ...Code to build DataSet goes here...

Of course, you would probably wrap all of this code in a method that takes a verseID as a parameter and returns a DataSet or DataRow

Summary

In summary, you might think of stored procedures as a public method exposed by your database that encapsulates your SQL code. You can make any change to the stored procedure’s code, and as long as the name of the procedure and its parameters, if any, do not change (in other words, the “method signature” to continue our illustration), there would be no need to modify your ASP.NET application.

Hopefully this gives you a starting point for using MySQL stored procedures.  However, we have only begun to scratch the surface of this very powerful feature.  In the future, we will look at creating more complex stored procedures, and using stored procedures to insert, update, and delete data in your database.

In this series of articles, I want to introduce you to using MySQL 5.0 with ASP.NET.  I hope to cover the most common uses for MySQL, as well as take advantage of some of the new features in version 5.0.  To do this, we will look at creating a database of Bible verses and ASP.NET pages to view and manage the verses.

To begin development, you will need to have the following tools. 

Required Components:

- .NET Framework 1.0 or higher
- MySQL 5.0 database server
- MySQL Connector/Net 1.0.6 or higher

Optional Components:

- MySQL Query Browser 1.1.15 or higher
- MySQL Administrator 1.1 or higher
- Visual Studio 2002 or higher

MySQL database server comes with command-line utilities to administer and develop MySQL.  However, MySQL Query Browser and MySQL Administrator are great utilities that will make your life much easier.  Visual Studio is not required, but certainly makes developing ASP.NET applications much easier.  Alternatively, you can use the free ASP.NET Web Matrix or simply create your ASP.NET web pages using any text editor. 

Installing each of these components is outside the scope of this article.  Please refer to the documentation provided with each for installation and configuration instructions.

Setting up the Database

Here are the scripts you’ll need to create the verse schema (database).  To execute the scripts, you’ll need to use the mysql command line utility or MySQL Query Browser application.

CREATE DATABASE versedb;
CREATE TABLE `verse` (
  `verse_id` int(10) unsigned NOT NULL auto_increment,
  `verse_text` varchar(1024) NOT NULL default '',
  `verse_ref` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`verse_id`)
)

As you can see, we are going to work with just a single table named verse to start with.  Before we can get data out of MySQL, obviously we have to put some data in.  Use the following scripts to add two verses to the table.

INSERT INTO `verse` (`verse_text`,`verse_ref`) VALUES
  ('And God is able to make all grace abound to you, so that in all things
  at all times, having all that you need, you will abound in every good work.',
  '2 Corinthians 9:8'),
  ('Whatever you do, work at it with all your heart, as working for the
  Lord, not for men, since you know that you will receive an inheritance from
  the Lord as a reward. It is the Lord Christ you are serving.',
  'Colossians 3:23-24');

Setting Up the Web Site

This article assumes you will set up a virtual directory on your local computer or you will upload the examples to a Web host that supports ASP.NET.  Setting up the test web site locally is outside the scope of this document.  If you plan to develop and debug ASP.NET on your local computer and are not already familiar with setting up Internet Information Server (IIS) or creating virtual directories, you can use the links provided on the Online Resources page to find tutorials that cover this subject.

Connecting to MySQL from ASP.NET

As mentioned before, you will need to download and install the MySQL Connector/Net ADO.NET library.  Once you’ve added the MySql.Data.dll assembly as a reference to your project or placed it in your /bin folder, you are ready to start writing code to access MySQL.  Here is an example of connecting to MySQL and retrieving all of the rows from the verse table into a DataSet.

// Connection string for a typical local MySQL installation
string cnnString =
"Server=localhost;Port=3306;Database=versedb;Uid=root;Pwd=MySecretPassword";

// Create a connection object and data adapter
MySqlConnection cnx = new MySqlConnection(cnnString);
MySqlDataAdapter adapter = new MySqlDataAdapter();

// Create a SQL command object
string cmdText = "SELECT * FROM verse";
MySqlCommand cmd = new MySqlCommand(cmdText, cnx);

// Create a fill a Dataset
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds);

// Bind the DataSet
// ... Place your databinding code here ...

If you’re already accustomed to data binding in .NET, you should immediately recognize the similarities.  The only difference is you use the MySqlXxx classes instead of the OleDbXxx or SqlXxx classes. 

Hello World…

The following code provides a simple yet complete example of connecting to MySQL and displaying the data retrieved from the database.  To use this code, add a new Web Form (.aspx) to your ASP.NET project, copy and paste, change the connection string to match your MySQL database user name and password, and save.

<%@ Page language="c#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="MySql.Data.MySqlClient" %>
<html>
<head>
<script runat="server" type="text/javascript">
void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    BindVerses();
  }
}

private void BindVerses()
{
  try
  {
    // Connection string for a typical local MySQL installation
    string cnnString =
"Server=localhost;Port=3306;Database=versedb;Uid=root;Pwd=myPassword";

    // Create a connection object and data adapter
    MySqlConnection cnx = new MySqlConnection(cnnString);
    MySqlDataAdapter adapter = new MySqlDataAdapter();

    // Create a SQL command object
    string cmdText = "SELECT * FROM verse";
    MySqlCommand cmd = new MySqlCommand(cmdText, cnx);

    // Create a fill a Dataset
    DataSet ds = new DataSet();
    adapter.SelectCommand = cmd;
    adapter.Fill(ds);

    // Bind the DataSet
    VerseRepeater.DataSource = ds;
    VerseRepeater.DataBind();
  }
  catch (Exception ex)
  {
    lblError.Text = ex.Message;
  }
}
</script>

<title></title>    
<style type="text/css">
  body { font-family:Verdana, Arial, Sans-Serif; font-size:small; }
  h1, h2, h3, h4, h5 { font-family:Trebuchet MS, Verdana, Arial, Sans-Serif;
}
  input, textarea { font-family: Verdana, Arial, Sans-Serif; font-size:small;
}
  .Error { font-weight:bold; color:#c00; }
  .VerseList { width:400px; }
  .VerseHeader {
    padding:5px;
    border:1px solid #999;
    background-color:#ddd;
    font-weight:bold;
  }
  .VerseText {
    font-family: Trebuchet MS, Verdana, Arial;
    font-size: small;
    padding:5px 5px 0px 5px;
    border-left:1px solid #999;
    border-right: 1px solid #999;
  }
  .VerseRef {
    font-family: Arial, sans-serif;
    font-style:italic;
    font-size: x-small;
    text-align:right;
    padding:5px 15px 0px 5px;
    border-left:1px solid #999;
    border-right: 1px solid #999;
    border-bottom:1px solid #ccc;
  }
  .VerseFooter {
    border-left:1px solid #999;
    border-right: 1px solid #999;
    border-bottom:1px solid #999;
}
</style>
</head>
<body>

  <form runat="server" method="post" id="Form1">
    <p><asp:label cssclass="Error" enableviewstate="False" runat="server"
id="lblError"></asp:label></p>
    <div class="VerseList">
      <asp:repeater runat="server" id="VerseRepeater">
        <headertemplate>
          <div class="VerseHeader">Verse List</div>
        </headertemplate>
      
        <itemtemplate>
          <div class="VerseText"><%# DataBinder.Eval(Container.DataItem, "verse_text") %></div>
          <div class="VerseRef">--
            <%# DataBinder.Eval(Container.DataItem, "verse_ref") %>
          </div>
        </itemtemplate>
        <footertemplate>
          <div class="VerseFooter"><img width="1" height="2" alt="" src="images/pixel.gif" /></div>
        </footertemplate>
      </asp:repeater>
    
    </div>
  </form>
</body>
</html>

In this tips I will discuss how you can customize the rendering of ASP.NET CheckBoxList control with images.  Let’s consider a scenarios where you want to show some status images with every checkbox with in a CheckBoxList. Many developers use table with checkbox and images in different cell to display Images with checkbox.  But, we can easily achieve the same using CheckBoxList control itself.

Let’s  consider you have items in a CheckBoxList control.



If you run your web application with above CheckBoxList items, you will get below output



Now you want images instead of the text with the list of checkbox controls. If you look in to the code, as of now, nothing has been written with in NewCheckList_DataBound method.  To enable the adding icon  we have to override the content of items which is being  generating during  data bound. Below is a simple code which will enable to add  icons for every CheckBo  item in the CheckBoxList



What we did is just iterating through each element and override the  item text as per our requirement . Once run, you will get the output as below.



If you want avoid the text, you just need change the override content of item.Text.



So not only the icon, you can add any HTML Content as per requirement.

While working with ASP.NET web application you must be familiar with one of most important state management technique “Session”. ASP.NET Session State is on by default, hence you are paying for memory even if you don’t use it. There are several ways to optimize it.

Tip #1 :  Not using Session State at all ? Then turn it off completely in web.config



Tip #2 : Session is only required for few pages not all over the application 

Then first turn it off for all pages, for that you need to do following entry in web.config



then enable it for a specific page where you required the session



Tip #3 : If you are using Session for Reading Purpose, use Session State as “ReadOnly”

If you are a beginner, you must be wondering what is EnableSessionState=”Readonly” .  Well, if you look at your web application, not all the pages using Session or some of the pages is using session data for reading purpose. If there is no write operation happaning on session, then it’s always better to use session State is “ReadOnly”



The session request pass through different httpModule with in HTTPPipeline. Know more details on how session state ReadOnly works , please read the article Read Only Session State in ASP.NET. A quick summary from the referred article,

The session state module implements a readers – writers  locking mechanism and queues the access to session state values. A page that has session-state write access will hold a writer lock on the session until the request finishes. A page gains write access to the session state by setting the EnableSessionState attribute on the @Page directive to True. A page that has session-state read access — for example, when the EnableSessionState attribute is set to ReadOnly — will hold a reader lock on the session until the request finishes.

You can also set ReadOnly SessionState in web.config as well



Tip #4 : Programmatically Change Session State Behavior when required (ASP.NET 4.0)

We can enable or disabled session state either in web.config or using @Page directive’s   EnableSessionState attributes. But there was no provision to change the session state at runtime till date in ASP.NET. But using  ASP.NET 4.0, we can change the session  state programmatically . The .NET 4.0 framework adds a new method SetSessionStateBehavior  to the HttpContext class for ASP.NET. This method required SessionStatebehavior  value to set the current session mode. To call SetSessionStateBehavior   simply create a new HttpModule by Implementing IHttModule and hook the BeginRequest event. Most important you can only use the SetSessionStateBehavior  until the AcquireRequestState event is fired, because AcquireRequestState  Occurs when ASP.NET acquires the current state  that is associated with the current request

While calling SetSessionStatebehavior, You can pass the following values as SessionStatebehavior  :

- Default: This is default setting which means  everything works as before
- Disabled: Turned of Session Sate for Current Request.
- ReadOnly: Read only access to Session State;
- Required: Enabled session state for both Read and Write Access



Tip #5 : Compress Session Data while using OutProc Session mode based on Requirements (AP.NET 4.0)

ASP.NET 4.0 comes with a new option for compressing the Session data with Out Process Session mode. To enabling this functionality we need to add “compressionEnabled=”true” attribute with the SessionMode in web.config .



When Compression mode is enabled is web.config, ASP.NET  compress the serialized session data  and passed it to session storage and during retrieval same  deserialization and decompression happens in server side. ASP.NET 4.0 used System.IO.Compression.GZStream class to compress the session mode.



Tip #6 : Use HttpContext.Current.Items for very short term storage instead of Session

You can use HttpContext.Current.Items for very short term storage. By Short term storage means, this data is valid for a single HTTP Request.  There are many confusion around regarding storing data in HttpContext.Current.Items and storing data in Session variable. Items collections of HttpContext is and IDictionary key-value collections and that are shared across a single HTTPRequest. Yes, HttpContext.Current.Items  valid for  a single HTTPRequest.



Cheers…