Uploading and Downloading files (any) into Database using nHibernate in ASP.NET

In this post, I am going to give an example of how to Upload and download files into Database using nHibernate in ASP.NET applications. Here, we can upload any type of files into the database and when we download it will save the file in a specified location. This example does not give an idea of viewing the files in the page as the file can be any format like .pdf, .doc, .mp3, .wmv etc.,

Note: As this example implemented using nHibernate, the developer should have the knowledge of nHibernate for implementing the same.

To implement this example, I have three projects in a solution for clear understanding and separation of Entities and Data components.

1. SaveFileInDB – is WebApplication which has the User Interface screen and required functions.
2. SaveFileInDB.Entities – is class library project, has entity classes and .hbm files for defining entity attributes with database fields.
3. SaveFileInDB.DataAccess – is class library project, has the database related functions such as Save, Delete etc., these functions uses nHibernate for doing database operations.

Below is the screenshot how the solution looks like.

The SaveFileInDB.Entities project used for defining entities and related .hbm files. It contains three files.

1. Document.cs – defines the class Document, which has the properties of a document.
2. DocumentContent.cs - defines the class DocumentContent which has the actual file content. This class inherited by class Document, so by referring class DocumentContent refers to file content with its properties and referring class Document refers the file properties only. This is useful when transferring only the document properties to other layers or serializing the document properties for further use.
3. DocumentContent.hbm.xml – has the nHibernate configuration for defining how the class name and properties linked with database table and columns. Important to remember is to make the BuildAction property of this file set to Embedded Resource.

Below code shows the content for each of the files.

Document.cs

public class Document
{
    public string Type { get; set; }
    public string Filename { get; set; }
    public string Description { get; set; }
    public long Id { get; set; }
    public Document() { }
}

DocumentContent.cs

/// <summary>
/// This class is used to hold the content of a document.
/// </summary>
public class DocumentContent : Document
{
    /// <summary>
    /// Gets or sets the view status of the document.
    /// </summary>
    /// <value>The view status of the document. It can hold any value.</value>
    public byte[] Content { get; set; }
    public DocumentContent() { }
}

DocumentContent.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   namespace="SaveFileInDB.Entities"
                   assembly="SaveFileInDB.Entities">
  <class name="DocumentContent" table="[document]" lazy="false">
    <id name="Id" column="[document_id]" type="Int64" >
      <generator class="native"/>
    </id>

    <property name="Content" column="[content]" type="BinaryBlob" />
    <property name="Type" column="[type]" type="String" length="50"/>
    <property name="Filename" column="[filename]" type="String" length="50"/>
    <property name="Description" column="[description]" type="String" length="50"/>

  </class>
</hibernate-mapping>

The DataAccess project (SaveFileInDB.DataAccess) contains a class for database functionalities. It contains a class DocumentDataAccess defining functions for uploading and downloading files into the datbase table.

The source code for DocumentDataAccess

public class DocumentDataAccess
{
    public DocumentDataAccess()
    {
    }
    private string GetConfigFilePath
    {
        get
        {
            string path = HttpContext.Current.Request.PhysicalApplicationPath;
            if (!path.EndsWith("\\"))
                path = path + "\\";

            path = path + "nhibernate.config";
            return path;
        }
    }
    /// <summary>
    /// For creating a document record in the database
    /// </summary>
    /// <param name="document">Document</param>
    /// <returns>Document</returns>
    public DocumentContent Create(DocumentContent document)
    {
        try
        {
            // Create the configuration object
            Configuration cfg = new Configuration();
            cfg.Configure(GetConfigFilePath);

            // Create the session
            ISessionFactory sessionFactory = cfg.BuildSessionFactory();

            // Create session
            using (var session = sessionFactory.OpenSession())
            {
                // Begin a transaction
                using (var tx = session.BeginTransaction())
                {
                    try
                    {
                        // Create
                        document.Id = (Int64)session.Save(document);

                        // Flush session and Commit Transaction
                        tx.Commit();
                    }
                    catch
                    {
                        // Rollback if exception thrown
                        tx.Rollback();
                        throw;
                    }
                }
            }
            // Return document
            return document;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    /// <summary>
    /// For updating a particular document record in the database
    /// </summary>
    /// <param name="document">Document</param>
    /// <returns>Document</returns>
    public DocumentContent Update(DocumentContent document)
    {
        try
        {
            // Create the configuration object
            Configuration cfg = new Configuration();
            cfg.Configure(GetConfigFilePath);

            // Create the session
            ISessionFactory sessionFactory = cfg.BuildSessionFactory();

            // Create session
            DocumentContent oldDocument;
            using (var session = sessionFactory.OpenSession())
            {
                // Begin a transaction
                using (var tx = session.BeginTransaction())
                {
                    try
                    {
                        // Get old entity
                        oldDocument = session.CreateCriteria(typeof(DocumentContent))
                            .Add(Restrictions.Eq("Id", document.Id)).UniqueResult<DocumentContent>();

                        // Throw exception if none found
                        if (oldDocument == null)
                        {
                            throw new System.IO.FileNotFoundException(
                                string.Format("The given document with id[{0}] does not exist.", document.Id));
                        }

                        // remove it from the cache
                        session.Evict(oldDocument);

                        // Do a clean update
                        session.SaveOrUpdate(document);

                        // Flush session and Commit Transaction
                        tx.Commit();
                    }
                    catch (Exception ex)
                    {
                        // Rollback if exception thrown
                        tx.Rollback();
                        throw ex;
                    }
                }
            }

            // Return document
            return document;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    /// <summary>
    /// For deleting a document record from the database
    /// </summary>
    /// <param name="documentId">Document Id</param>
    /// <returns>true if successful, error when failes</returns>
    public bool Delete(long documentId)
    {
        try
        {
            // Create the configuration object
            Configuration cfg = new Configuration();
            cfg.Configure(GetConfigFilePath);

            // Create the session
            ISessionFactory sessionFactory = cfg.BuildSessionFactory();

            // Create session
            DocumentContent document;
            using (var session = sessionFactory.OpenSession())
            {
                // Begin a transaction
                using (var tx = session.BeginTransaction())
                {
                    try
                    {
                        // Get old entity
                        document = session.CreateCriteria(typeof(DocumentContent))
                            .Add(Restrictions.Eq("Id", documentId)).UniqueResult<DocumentContent>();

                        // Throw exception if none found
                        if (document == null)
                        {
                            throw new System.IO.FileNotFoundException(
                                string.Format("The given document with id[{0}] does not exist.", documentId));
                        }

                        // To clean the session, because we just use the 'session.Get' method.
                        session.Clear();

                        // Delete the given entity.
                        session.Delete(document);
                        session.Flush();

                        // Flush session and Commit Transaction
                        tx.Commit();
                    }
                    catch (Exception ex)
                    {
                        // Rollback if exception thrown
                        tx.Rollback();

                       throw ex;
                    }
                }
            }

            // Return document
            return true;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    /// <summary>
    /// Get a particular document from the database
    /// </summary>
    /// <param name="documentId">Document Id</param>
    /// <returns>document</returns>
    public DocumentContent Get(long documentId)
    {
        try
        { 
            // Create the configuration object
            Configuration cfg = new Configuration();
            cfg.Configure(GetConfigFilePath);

            // Create the session
            ISessionFactory sessionFactory = cfg.BuildSessionFactory();

            // Create session
            DocumentContent document;
            using (var session = sessionFactory.OpenSession())
            {
                // Get document
                document = session.CreateCriteria(typeof(DocumentContent)).
                   Add(Restrictions.Eq("Id", documentId)).
                   UniqueResult<DocumentContent>();
            }

            // Return document
            return document;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    /// <summary>
    /// Get all the document from the database.
    /// </summary>
    /// <returns>List of documents</returns>
    public IList<Document> GetAll()
    {
        try
        {
            // Create the configuration object
            Configuration cfg = new Configuration();
            cfg.Configure(GetConfigFilePath);

            // Create the session
            ISessionFactory sessionFactory = cfg.BuildSessionFactory();

            // Open the session
            using (ISession session = sessionFactory.OpenSession())
            {
                // Create Criteria
                ICriteria criteria = session.CreateCriteria(typeof(Document));

                // Get Cusomers
                IList<Document> customers = criteria.List<Document>();

                return customers;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}

The Web application has a two functionalities, uploading and downloading files, each achieved in a single user interface. The functionalities of the screen are

1. The user can upload a file by selecting the file using FileUpload control and giving the description. Save button will trigger saving to the database.
2. The system will save the file into the database and show the list of file name, description and the size of the file stored in the database in GridView.
3. The user can download a particular file by selecting the hyperlink provided with the file name in the grid.
4. The system will pop up a screen for saving the file into a location.

Below is the script for Default.aspx file.

<div>
    <strong style="font-size:larger;color:Green">Uploading and Downloading file into Database using nHibernate</strong>
 
    <div style="width:500px">File name:</div><asp:FileUpload ID="fileName" runat="server" style="width:400px;" />
    <asp:RequiredFieldValidator ID="valFileName" runat="server" ControlToValidate="fileName" EnableClientScript="false">
            Fill a valid filename
    </asp:RequiredFieldValidator>

    <div style="width:500px">Description:</div><asp:TextBox ID="txtDescription" runat="server" TextMode="MultiLine" MaxLength="50" style="width:400px;" />

    <asp:Button ID="btnAttach" runat="server" OnClick="btnAttach_Click" Text="Attach" Width="100px" />

    <asp:GridView ID="gvAttachments" runat="server" CellPadding="4" 
        AutoGenerateColumns="False" GridLines="None" 
        AllowPaging="True" ForeColor="#333333" Width="*80%" 
        onrowdeleting="gvAttachments_RowDeleting" 
        onpageindexchanging="gvAttachments_PageIndexChanging" 
        onrowdatabound="gvAttachments_RowDataBound">
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <Columns>
            <asp:TemplateField Visible="false">
                <ItemTemplate>
                    <asp:Label ID="lblId" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "Id") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:HyperLinkField DataNavigateUrlFields="Id" DataNavigateUrlFormatString="DownloadFile.ashx?id={0}" Target="_blank"
                DataTextField="Filename" HeaderText="File Name" />
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Label ID="lblSize" runat="server" Text='0'></asp:Label>
                </ItemTemplate>
                <HeaderTemplate>
                    Size
                </HeaderTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="Description" HeaderText="Description" />
            <asp:CommandField ShowDeleteButton="True" HeaderText="Delete" />
        </Columns>
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#999999" />
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    </asp:GridView>
</div>

Default.aspx.cs

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        BindGrid();
    }
    private void BindGrid()
    {
        DocumentDataAccess documentDataAccess = new DocumentDataAccess();
        IList<Document> attachments = documentDataAccess.GetAll();
        if (attachments.Count == 0)
        {
            attachments = new List<Document> { new Document() };
        }
        gvAttachments.DataSource = attachments;
        gvAttachments.DataBind();
    }

    protected void btnAttach_Click(object sender, EventArgs e)
    {
        if (fileName.FileBytes.Length > 0)
        {
            DocumentContent doc = new DocumentContent();
            doc.Content = fileName.FileBytes;
            doc.Filename = Path.GetFileName(fileName.FileName);
            doc.Description = txtDescription.Text;
            doc.Type = fileName.PostedFile.ContentType;
            DocumentDataAccess documentDataAccess = new DocumentDataAccess();
            doc = documentDataAccess.Create(doc);
            txtDescription.Text = "";

            BindGrid();
        }
    }

    protected void gvAttachments_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        DocumentDataAccess documentDataAccess = new DocumentDataAccess();
        documentDataAccess.Delete(Convert.ToInt32(((Label)gvAttachments.Rows[e.RowIndex].FindControl("lblId")).Text));
        BindGrid();
    }

    protected void gvAttachments_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvAttachments.PageIndex = e.NewPageIndex;
        BindGrid();
    }

    protected void gvAttachments_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            LinkButton lnkDelete = (LinkButton)e.Row.Cells[4].Controls[0];
            Label lblSize = (Label)e.Row.FindControl("lblSize");
            if (((((DocumentContent)e.Row.DataItem).Content.GetLength(0)/1024)/1024) > 0)
                lblSize.Text = (((decimal)((DocumentContent)e.Row.DataItem).Content.GetLength(0) / 1024) / 1024).ToString("##.##") + " MB";
            else
                lblSize.Text = ((decimal)((DocumentContent)e.Row.DataItem).Content.GetLength(0) / 1024).ToString("##.##") + " KB";
            lnkDelete.Attributes.Add("onclick", "return confirm('Are you sure, you want to delete?')");
        }
    }
}

To download the file from the database, we have GenericHandler file - DownloadFile.ashx. Below is the code for the same.

public class DownloadFile : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/plain";
        DocumentDataAccess documentDataAccess = new DocumentDataAccess();

        DocumentContent documentContent = documentDataAccess.Get(long.Parse(context.Request["Id"]));
        context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + documentContent.Filename);
        context.Response.ContentType = documentContent.Type;
        context.Response.BinaryWrite(documentContent.Content);
    }
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

Importantly we need to have nhibernate.config, which hold all the nHibernate related configurations with the database connection string.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
  <session-factory >
    <!-- properties -->
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
    <property name="connection.connection_string">Data Source=THIRUMALAI-NOTE\SQLEXPRESS;Initial Catalog=Northwind;Persist Security Info=True;Trusted_Connection=Yes;Pooling=yes;connection lifetime=300;</property>
    <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
    <property name="show_sql">false</property>
    <!-- mapping files -->
    <mapping assembly="SaveFileInDB.Entities" />
  </session-factory>
</hibernate-configuration>

Note: By default, the FileUpload control allows us uploading file up to 4 MB. So to upload files more than 4 MB (4096 KB), required to add the below node in the Web.Config under <system.web> node.

<system.web>
    <httpRuntime maxRequestLength="1048576" executionTimeout="3600" />
</system.web>

Here, maxRequestLength attribute specifies the size of the file can be uploaded and executionTimeout attribute specifies the number of seconds allowed for upload operation.

For more information, please refer the following msdn link
http://msdn.microsoft.com/en-us/library/aa479405.aspx

The output of the screen for the code provided:

Download the source code in C# here and in VB here.

Getting Started with NHibernate - Part 2

This post is a continuation of previous post. This post gives a startup using NHibernate framework. In this post we are discussing the Data access components.

In Data Access project, I have created a class CustomerDA for specifying Data Access methods. This class has the following method.

1. GetConfigFilePath – This method is a Private method, used for getting the physical path of nhibernate.config file located in UI Project.
   

   /// <summary>
   /// Geting the nhibernate.config file path
   /// </summary>
   private string GetConfigFilePath
   {
       get
       {
           string path = HttpContext.Current.Request.PhysicalApplicationPath;
           if (!path.EndsWith("\\"))
               path = path + "\\";
   
           path = path + "nhibernate.config";
           return path;
       }
   }

2. Get – Method for getting Customer details for a particular Customer.
   

   /// <summary>
   /// Get the customer details with Customer Id 
   /// </summary>
   /// <param name="customerID"></param>
   /// <returns></returns>
   public Customer Get(string customerID)
   {
       try
       {
           // Create the configuration object
           Configuration cfg = new Configuration();
           cfg.Configure(GetConfigFilePath);
   
           // Create the session
           ISessionFactory sessionFactory = cfg.BuildSessionFactory();
   
           // Open the session
           using (ISession session = sessionFactory.OpenSession())
           {
               // Get Cusomers
               IList<Customer> customers = session.CreateCriteria(typeof(Customer))
                                               .Add(Expression.Eq("CustomerID", customerID))
                                               .List<Customer>();
   
               // Get the first customer (as CustomerID is unique, it will be always one)
               Customer customer = null;
               if ((customers != null) && (customers.Count > 0))
                   customer = customers[0];
   
               return customer;
           }
       }
       catch (Exception ex)
       {
           throw ex;
       }
   }

3. GetAll – Method for getting all the Customer in a list from the database.
   

   /// <summary>
   /// Get all the customer from the database.
   /// </summary>
   /// <param name="sortBy"></param>
   /// <param name="sortType"></param>
   /// <returns></returns>
   public IList<Customer> GetAll(string sortBy, SortType sortType)
   {
       try
       {
           // Create the configuration object
           Configuration cfg = new Configuration();
           cfg.Configure(GetConfigFilePath);
   
           // Create the session
           ISessionFactory sessionFactory = cfg.BuildSessionFactory();
   
           // Open the session
           using (ISession session = sessionFactory.OpenSession())
           {
               // Create Criteria
               ICriteria criteria = session.CreateCriteria(typeof(Customer));
   
               if (sortBy != null) //If sortBy values pass (Pass null if not required)
                   criteria.AddOrder(sortType == SortType.Ascending ? Order.Asc(sortBy) : Order.Desc(sortBy));
   
               // Get Cusomers
               IList<Customer> customers = criteria.List<Customer>();
   
               return customers;
           }
       }
       catch (Exception ex)
       {
           throw ex;
       }
   }

4. Create – Method for creating a Customer
   

   /// <summary>
   /// Create the Customer details
   /// </summary>
   /// <param name="customer"></param>
   public void Create(Customer customer)
   {
       ITransaction transaction = null;
       try
       {
           // Create the configuration object
           Configuration cfg = new Configuration();
           cfg.Configure(GetConfigFilePath);
   
           // Create the session
           ISessionFactory sessionFactory = cfg.BuildSessionFactory();
   
           // Open the session
           ISession session = sessionFactory.OpenSession();
   
           // Begin a new ITransaction
           transaction = session.BeginTransaction();
   
           // Save the given entity.
           session.Save(customer);
           session.Flush();
   
           transaction.Commit();
       }
       catch (Exception ex)
       {
           // Rollback the transaction.
           if (transaction != null)
           {
               transaction.Rollback();
           }
           throw ex;
       }
   }

5. Update – Method for Update an existing Customer.
   

   /// <summary>
   /// Update the Customer details
   /// </summary>
   /// <param name="customer"></param>
   public void Update(Customer customer)
   {
       ITransaction transaction = null;
       try
       {
           // Create the configuration object
           Configuration cfg = new Configuration();
           cfg.Configure(GetConfigFilePath);
   
           // Create the session
           ISessionFactory sessionFactory = cfg.BuildSessionFactory();
   
           // Open the session
           ISession session = sessionFactory.OpenSession();
   
           // Begin a new ITransaction
           transaction = session.BeginTransaction();
   
           // Get existing Cusomer
           Customer existingCustomer = session.Get<Customer>(customer.CustomerID);
           if (existingCustomer == null)
               throw new Exception(string.Format("Can not find the Customer with id: {0}.", customer.CustomerID));
           session.Clear();
           // Save the given entity.
           session.Update(customer);
           session.Flush();
   
           transaction.Commit();
       }
       catch (Exception ex)
       {
           // Rollback the transaction.
           if (transaction != null)
           {
               transaction.Rollback();
           }
           throw ex;
       }
   }

6. Delete – Method for Deleting an Existing Customer. Here I can call this method using Customer Id or Customer object from UI layer.
   

   /// <summary>
   /// Delete the Customer details
   /// </summary>
   /// <param name="customer"></param>
   public void Delete(Customer customer)
   {
       ITransaction transaction = null;
       try
       {
           // Create the configuration object
           Configuration cfg = new Configuration();
           cfg.Configure(GetConfigFilePath);
   
           // Create the session
           ISessionFactory sessionFactory = cfg.BuildSessionFactory();
   
           // Open the session
           ISession session = sessionFactory.OpenSession();
   
           // Begin a new ITransaction
           transaction = session.BeginTransaction();
   
           // Get existing Cusomer
           Customer existingCustomer = session.Get<Customer>(customer.CustomerID);
           if (existingCustomer == null)
               throw new Exception(string.Format("Can not find the Customer with id: {0}.", customer.CustomerID));
   
           // To clean the session, because we just use the 'session.Get' method.
           session.Clear();
   
           // Delete the given entity.
           session.Delete(customer);
           session.Flush();
   
           transaction.Commit();
       }
       catch (Exception ex)
       {
           // Rollback the transaction.
           if (transaction != null)
           {
               transaction.Rollback();
           }
           throw ex;
       }
   }

   /// <summary>
   /// Delete the Custoer Details
   /// </summary>
   /// <param name="customerID"></param>
   public void Delete(string customerID)
   {
       Customer customer = Get(customerID);
       Delete(customer);
   }

I also have an enum which is used in GetAll method for specifying the sorting type.

public enum SortType
{
    Ascending,

    Descending
}

The output of the running sample looks below:

Entry Screen for Customer (using NHibernate framework)

download the working example of the source code in C# here

Getting Started with NHibernate - Part 1

In this post I am going to provide some code to get start with nHibernate framework.

For more information about what is nHibernate and related information, you can refer the following links.

http://community.jboss.org/wiki/NHibernateForNET
http://www.summerofnhibernate.com/

Before starting our implementation, a short introduction about NHibernate.

NHibernate is an ORM (Object Relational Mapper) framework, which is equalent to LINQ to SQL. Normally to access database and do any CRUD (Create, Read, Update, Delete) operations, we will be writing Store Procedures, SQL statements in our projects. By writing such code, we must be care about the syntax of the SQL statements and SQL injection etc.,

But by using ORM, you will be creating a mapping file (that is called as .hbm file in Nhibernate) which specified the table name, corresponding entity class name, table column names with the type of the column and related properties etc., Once the mapping file created, the NHibernate automatically create SQL statement and do CRUD operations by calling related Methods.

So it will reduce the errors which can occur while writing normal SQL statements. This also gives a common framework for working with different databases such as SQL Server, Oracle, mySQL etc., So by changing only some configuration file, we can work with different database easily.

Below is the list of Advantages and Disadvantages using Nhibernate (Note: Points are listed here is my personal views. Incase if someone feels some points are wrong, please comment on this blog. I will verify and update accordingly)

Advantages:

1. It is very easy to work with multiple databases using nHibernate. For more information search with “using nhibernate with multiple databases”.
2. Once the mapping done with the database Table/View, we can do all the related database operation very easily.
3. Can use Caching feature for keeping the first loaded entities in to memory and use it further.
4. No need to worry about SQL injection and all. NHibernate will handle automatically.
5. If any complicated database job required, can create SQL store procedure and call with nHibernate. No restriction to use only NHibernate provided feature.
6. Can dynamically create Queries and execute. It means, there is no need to write any query in a single statement. It can be done thro’ a sequence of code with some conditions.
7. To build a query, nHibernate gives various options. They are –
   
   • Can use HQL in CreateQuery
   • Can use named queries in GetNamedQuery
   • Can use SQL directly in CreateSqlQuery
   • Can use the type of object in a CreateCriteria
8. As it is very mature and popular, there are multiple project executed with this framework. So we have proof of stability.
9. There is a big community for NHibernate. So if any queries can be clarified easily.
10. As it is open source, can explore more on how implemented. Can modify for our needs.

Disadvantages:

1. Learning curve. As every new technology has this, so not to worry about this.
2. Writing mapping XML is very difficult if the database is so big.
3. Low performance if designed wrongly.

In this post, I am going to take Customer entity from Northwind database. I also have a screen which shows Customer details. The functionality of the screen would be:

1. Initially when page gets loaded, it will show a list of customer in the screen.
2. The user can add a new Customer by entering the details in the screen and press Save button.
3. The user details can be viewed by clicking Select column in the GridView for a record.
4. The user can modify the details and update by Save button.
5. The user can delete a particular record by Delete button.

I create a solution with three projects, they are -

1. DotNetTwitter.HinHibernate – Project for specifying User interface
2. DotNetTwitter.HinHibernate.DataAccess – Project for specifying Data Access methods
3. DotNetTwitter.HinHibernate.Entities – Defined Entity classes.

The screen shot of the solution explorer is shown in the figure.

Solution Explorer

In the UI layer, there is a Default.aspx file and nhibernate.config. The nhibernate.config is the configuration file for specifying the Database connection string, provider and mapping assembly. This file will be accessed from Data Access methods when initializing NHibernate. Below is the nhibernate.config script.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
  <session-factory >
    <!-- properties -->
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
    <property name="connection.connection_string">Data Source=THIRUMALAI-NOTE\SQLEXPRESS;Initial Catalog=Northwind;Persist Security Info=True;Trusted_Connection=Yes;Pooling=yes;connection lifetime=300;</property>
    <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
    <property name="show_sql">false</property>
    <!-- mapping files -->
    <mapping assembly="DotNetTwitter.HinHibernate.Entities" />
  </session-factory>
</hibernate-configuration>

The Default.aspx file is used for user interface. For the source code of the aspx file, please verify the attachment located in the next post. The screen shot of the screen would look like below.

Entry Screen for Customer (using NHibernate framework)

In the entity project, we have Customer.cs Customer.hbm.xml file. The Customer.cs file is the entity class defining the properties for Customer object. The Customer.hbm.xml is the mapping xml file for defining the mapping between the database table and the entity class used in the project.

The Customer.cs class file would be:

public class Customer
{
    public string CustomerID { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string ContactTitle { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Region { get; set; }
    public string PostalCode { get; set; }
    public string Country { get; set; }
    public string Phone { get; set; }
    public string Fax { get; set; }
}

Customer.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="DotNetTwitter.HinHibernate.Entities.Customer, DotNetTwitter.HinHibernate.Entities" table="[Customers]" lazy="false">
    <id name="CustomerID" column="[CustomerID]" type="String" length="5" >
      <generator class="assigned"/>
    </id>
    <property name="CompanyName" column="[CompanyName]" type="String" length="40" />
    <property name="ContactName" column="[ContactName]" type="String" length="30" />
    <property name="ContactTitle" column="[ContactTitle]" type="String" length="30" />
    <property name="Address" column="[Address]" type="String" length="60" />
    <property name="City" column="[City]" type="String" length="15" />
    <property name="Region" column="[Region]" type="String" length="15" />
    <property name="PostalCode" column="[PostalCode]" type="String" length="10" />
    <property name="Country" column="[Country]" type="String" length="15" />
    <property name="Phone" column="[Phone]" type="String" length="24" />
    <property name="Fax" column="[Fax]" type="String" length="24" />
  </class>
</hibernate-mapping>

Note: Remember to change the Build Action property for this file to Embedded Resource.

Properties file of Customer.hbm.xml file

The remaining code will be covered in next post.

Database Pagination in GridView using nHibernate

If we develop a complex business application with GB of data and binding all the records to the GridView by doing pagination with it will drastically reduce the performance.

For my current application, I am using WCF service to fetch lakhs of record and return to the UI layer. There are two major issues I was facing. Those are:

There are two major issues I was facing. Those are:

1. I was using WCF service to fetch the records and return to the UI layer. As the amount of data getting transferred much higher, it was throwing error "WCF System.Net.WebException: The underlying connection was closed: The connection was closed unexpectedly".
2. Consider today I may not get any issue as my records are very less. But after 3 years down the line, what could be the output of the application?

Finally I came to conclusion, doing pagination on database side is better. I am blogging on the same concept here.

This post explains how to achieve database pagination using NHibernate and in other two posts I will be explaining (will be posting soon) on pagination using Store Procedure and LINQ concepts.

I am using Northwind database for this example, so please make sure you installed the same database to test the code.

I have three projects for this example (created with layers as normal business applications have.. )

1. DotNetTwitter.Entities - This project is used for defining the business entity class and related mapping .hbm.xml files. (I am using View as I required to bind the name of Categories, Suppliers)
2. DotNetTwitter.DataAccess - This project is used for database operation. Here is where the records are fetched for the required page.
3. DotNetTwitter.DBPagination - Web Application which contains GridView to show the records

The implementation as follows:

Database Script (To create ProductViewview).

Create View [dbo].[ProductView]
As
Select Products.ProductID,
  Products.ProductName,
  Suppliers.CompanyName,
  Categories.CategoryName,
  Products.QuantityPerUnit,
  Products.UnitPrice,
  Products.UnitsInStock,
  Products.UnitsOnOrder,
  Products.ReorderLevel
from Products
Join Suppliers on Suppliers.SupplierID = Products.SupplierID
Join Categories on Categories.CategoryID = Products.CategoryID
GO

nHibernate requires a mapping xml file, which explains how the columns from the dataset links to an entity.
ProductView.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="DotNetTwitter.Entities.ProductView, DotNetTwitter.Entities" table="[ProductView]" lazy="false">
    <id name="ProductID" column="[ProductID]" type="Int32">
      <generator class="assigned"/>
    </id>
    <property name="ProductName" column="[ProductName]" type="String" />
    <property name="CompanyName" column="[CompanyName]" type="String" />
    <property name="CategoryName" column="[CategoryName]" type="String" />
    <property name="QuantityPerUnit" column="[QuantityPerUnit]" type="String" />
    <property name="UnitPrice" column="[UnitPrice]" type="double" />
    <property name="UnitsInStock" column="[UnitsInStock]" type="Int32" />
    <property name="UnitsOnOrder" column="[UnitsOnOrder]" type="Int32" />
    <property name="ReorderLevel" column="[ReorderLevel]" type="Int32" />
  </class>
</hibernate-mapping>

nHibernate require a config file in which all database connection related configurations needs to be mentioned.
nhibernate.config

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
  <session-factory >
    <!-- properties -->
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
    <property name="connection.connection_string">Data Source=KEOWD00144756\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True</property>
    <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
    <property name="show_sql">false</property>
    <!-- mapping files -->
    <mapping assembly="DotNetTwitter.Entities" />
  </session-factory>
</hibernate-configuration>

I have three button and one text box in my gridview to navigate between pages (Next, Previous, Go and a Text box to enter PageNo). The Codebehind would be as

//C# Code behind
/// <summary>
/// Method which binds the data to the Grid
/// </summary>
private void BindGrid()
{
    ProductDAO objProductDAO = new ProductDAO();

    // Defining int variable to get the Total Record Count from Data layer
    int totalRecordCount = 0;

    // Get the SortByExpression and SortType from Hidden Control (by default, that will be updated in script)
    string strSortExpression = ViewState["SortExpression"].ToString().Split(",".ToCharArray())[0];
    SortType sortType = (SortType)Enum.Parse(typeof(SortType), ViewState["SortExpression"].ToString().Split(",".ToCharArray())[1], true);

    // Getting how many records required to show in the Grid per page
    int intPageRecordCount = Convert.ToInt32(ConfigurationManager.AppSettings["PageRecordCount"].ToString());

    IList<ProductView> ProductViewList = objProductDAO.GetProducts(Convert.ToInt32(ViewState["CurrentPage"].ToString()), intPageRecordCount, strSortExpression, sortType, out totalRecordCount);
    //Adding one empty row for just to show the grid
    if (ProductViewList.Count == 0)
        ProductViewList.Add(new ProductView()); 
    
    grdViewProducts.DataSource = ProductViewList;
    grdViewProducts.DataBind();

    grdViewProducts.BottomPagerRow.Visible = true;

    // Assign the Total Record count to 
    ViewState["TotalRecords"] = totalRecordCount.ToString();

    Label lblPageInfo = grdViewProducts.BottomPagerRow.FindControl("lblPageInfo") as Label;
    lblPageInfo.Text = "Page " + Convert.ToInt32(ViewState["CurrentPage"].ToString()).ToString() + " out of " + ((totalRecordCount % intPageRecordCount) > 0 ? (totalRecordCount / intPageRecordCount) + 1 : (totalRecordCount / intPageRecordCount)).ToString();

    // Try to find the sorted column
    for (int intRowIndex = 0; intRowIndex < grdViewProducts.Columns.Count; intRowIndex++)
    {
        if (strSortExpression == grdViewProducts.Columns[intRowIndex].SortExpression)
            ((LinkButton)grdViewProducts.HeaderRow.Cells[intRowIndex].Controls[0])
            .CssClass = (sortType == SortType.Ascending ? "sortup" : "sortdown");
    }
}

/// <summary>
/// Call when clicking Previous button on pagination
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnPrevious_Click(object sender, EventArgs e)
{
    if (Convert.ToInt32(ViewState["CurrentPage"].ToString()) > 1)
        ViewState["CurrentPage"] = (Convert.ToInt32(ViewState["CurrentPage"].ToString()) - 1).ToString();
    else
        ViewState["CurrentPage"] = "1";
    BindGrid();
}

/// <summary>
/// Call when clicking Go button on pagination
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnGo_Click(object sender, EventArgs e)
{
    int intPageRecordCount = Convert.ToInt32(ConfigurationManager.AppSettings["PageRecordCount"].ToString());
    TextBox txtGoPage = ((TextBox)((ImageButton)sender).Parent.FindControl("txtGoPage"));
    if (txtGoPage.Text.Trim().Length > 0)
    {
        if ((Convert.ToInt32(txtGoPage.Text) > 0) && (Convert.ToInt32(txtGoPage.Text) <= ((Convert.ToInt32(ViewState["TotalRecords"].ToString()) % intPageRecordCount) > 0 ? (Convert.ToInt32(ViewState["TotalRecords"].ToString()) / intPageRecordCount) + 1 : (Convert.ToInt32(ViewState["TotalRecords"].ToString()) / intPageRecordCount))))
        {
            ViewState["CurrentPage"] = Convert.ToInt32(txtGoPage.Text).ToString();
            BindGrid();
        }
    }
}

/// <summary>
/// Call when clicking Next button on pagination
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnNext_Click(object sender, EventArgs e)
{
    int intPageRecordCount = Convert.ToInt32(ConfigurationManager.AppSettings["PageRecordCount"].ToString());
    if (Convert.ToInt32(ViewState["CurrentPage"].ToString()) < ((Convert.ToInt32(ViewState["TotalRecords"].ToString()) % intPageRecordCount) > 0 ? (Convert.ToInt32(ViewState["TotalRecords"].ToString()) / intPageRecordCount) + 1 : (Convert.ToInt32(ViewState["TotalRecords"].ToString()) / intPageRecordCount)))
        ViewState["CurrentPage"] = (Convert.ToInt32(ViewState["CurrentPage"].ToString()) + 1).ToString();
    else
        ViewState["CurrentPage"] = (Convert.ToInt32(ViewState["TotalRecords"].ToString()) / intPageRecordCount).ToString();
    BindGrid();
}
/// <summary>
/// Sorts the GridView by clicked column.
/// </summary>
/// <param name="sender">The event sender.</param>
/// <param name="e">The event argument.</param>
protected void grdViewProducts_Sorting(object sender, GridViewSortEventArgs e)
{
    string strSortExpression = ViewState["SortExpression"].ToString().Split(",".ToCharArray())[0];
    SortType sortType = (SortType)Enum.Parse(typeof(SortType), ViewState["SortExpression"].ToString().Split(",".ToCharArray())[1], true);
    ViewState["CurrentPage"] = "1";

    if (strSortExpression == e.SortExpression)
        sortType = sortType == SortType.Ascending ? SortType.Descending : SortType.Ascending;
    else
    {
        strSortExpression = e.SortExpression;
        sortType = SortType.Ascending;
    }
    ViewState["SortExpression"] = e.SortExpression + "," + sortType.ToString();

    BindGrid();
}

'VB Code behind
    ''' <summary>
    ''' Method which binds the data to the Grid
    ''' </summary>
    Private Sub BindGrid()
        Dim objProductDAO As New ProductDAO()

        ' Defining int variable to get the Total Record Count from Data layer
        Dim totalRecordCount As Integer = 0

        ' Get the SortByExpression and SortType from Hidden Control (by default, that will be updated in script)
        Dim strSortExpression As String = ViewState("SortExpression").ToString().Split(",".ToCharArray())(0)
        Dim sortType As SortType = DirectCast([Enum].Parse(GetType(SortType), ViewState("SortExpression").ToString().Split(",".ToCharArray())(1), True), SortType)

        ' Getting how many records required to show in the Grid per page
        Dim intPageRecordCount As Integer = Convert.ToInt32(ConfigurationManager.AppSettings("PageRecordCount").ToString())

        Dim ProductViewList As IList(Of ProductView) = objProductDAO.GetProducts(Convert.ToInt32(ViewState("CurrentPage").ToString()), intPageRecordCount, strSortExpression, sortType, totalRecordCount)
        'Adding one empty row for just to show the grid
        If ProductViewList.Count = 0 Then
            ProductViewList.Add(New ProductView())
        End If

        grdViewProducts.DataSource = ProductViewList
        grdViewProducts.DataBind()

        grdViewProducts.BottomPagerRow.Visible = True

        ' Assign the Total Record count to 
        ViewState("TotalRecords") = totalRecordCount.ToString()

        Dim lblPageInfo As Label = TryCast(grdViewProducts.BottomPagerRow.FindControl("lblPageInfo"), Label)
        lblPageInfo.Text = "Page " & Convert.ToInt32(ViewState("CurrentPage").ToString()).ToString() & " out of " & (If((totalRecordCount Mod intPageRecordCount) > 0, (totalRecordCount \ intPageRecordCount) + 1, (totalRecordCount \ intPageRecordCount))).ToString()

        ' Try to find the sorted column
        For intRowIndex As Integer = 0 To grdViewProducts.Columns.Count - 1
            If strSortExpression = grdViewProducts.Columns(intRowIndex).SortExpression Then
                DirectCast(grdViewProducts.HeaderRow.Cells(intRowIndex).Controls(0), LinkButton).CssClass = (If(sortType = sortType.Ascending, "sortup", "sortdown"))
            End If
        Next
    End Sub

    ''' <summary>
    ''' Call when clicking Previous button on pagination
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Protected Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        If Convert.ToInt32(ViewState("CurrentPage").ToString()) > 1 Then
            ViewState("CurrentPage") = (Convert.ToInt32(ViewState("CurrentPage").ToString()) - 1).ToString()
        Else
            ViewState("CurrentPage") = "1"
        End If
        BindGrid()
    End Sub

    ''' <summary>
    ''' Call when clicking Go button on pagination
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Protected Sub btnGo_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        Dim intPageRecordCount As Integer = Convert.ToInt32(ConfigurationManager.AppSettings("PageRecordCount").ToString())
        Dim txtGoPage As TextBox = DirectCast(DirectCast(sender, ImageButton).Parent.FindControl("txtGoPage"), TextBox)
        If txtGoPage.Text.Trim().Length > 0 Then
            If (Convert.ToInt32(txtGoPage.Text) > 0) AndAlso (Convert.ToInt32(txtGoPage.Text) <= (If((Convert.ToInt32(ViewState("TotalRecords").ToString()) Mod intPageRecordCount) > 0, (Convert.ToInt32(ViewState("TotalRecords").ToString()) / intPageRecordCount) + 1, (Convert.ToInt32(ViewState("TotalRecords").ToString()) / intPageRecordCount)))) Then
                ViewState("CurrentPage") = Convert.ToInt32(txtGoPage.Text).ToString()
                BindGrid()
            End If
        End If
    End Sub

    ''' <summary>
    ''' Call when clicking Next button on pagination
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        Dim intPageRecordCount As Integer = Convert.ToInt32(ConfigurationManager.AppSettings("PageRecordCount").ToString())
        If Convert.ToInt32(ViewState("CurrentPage").ToString()) < (If((Convert.ToInt32(ViewState("TotalRecords").ToString()) Mod intPageRecordCount) > 0, (Convert.ToInt32(ViewState("TotalRecords").ToString()) / intPageRecordCount) + 1, (Convert.ToInt32(ViewState("TotalRecords").ToString()) / intPageRecordCount))) Then
            ViewState("CurrentPage") = (Convert.ToInt32(ViewState("CurrentPage").ToString()) + 1).ToString()
        Else
            ViewState("CurrentPage") = (Convert.ToInt32(ViewState("TotalRecords").ToString()) / intPageRecordCount).ToString()
        End If
        BindGrid()
    End Sub

    ''' <summary>
    ''' Sorts the GridView by clicked column.
    ''' </summary>
    ''' <param name="sender">The event sender.</param>
    ''' <param name="e">The event argument.</param>
    Protected Sub grdViewProducts_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
        Dim strSortExpression As String = ViewState("SortExpression").ToString().Split(",".ToCharArray())(0)
        Dim sortType As SortType = DirectCast([Enum].Parse(GetType(SortType), ViewState("SortExpression").ToString().Split(",".ToCharArray())(1), True), SortType)
        ViewState("CurrentPage") = "1"

        If strSortExpression = e.SortExpression Then
            sortType = If(sortType = sortType.Ascending, sortType.Descending, sortType.Ascending)
        Else
            strSortExpression = e.SortExpression
            sortType = sortType.Ascending
        End If
        ViewState("SortExpression") = Convert.ToString(e.SortExpression) & "," & sortType.ToString()

        BindGrid()
    End Sub

The nHibernate (ProductView.hbm.xml) file. Make sure you have set the Build Action = Embedded Resource to the property of this file.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="DotNetTwitter.Entities.ProductView, DotNetTwitter.Entities" table="[ProductView]" lazy="false">
    <id name="ProductID" column="[ProductID]" type="Int32">
      <generator class="assigned"/>
    </id>
    <property name="ProductName" column="[ProductName]" type="String" />
    <property name="CompanyName" column="[CompanyName]" type="String" />
    <property name="CategoryName" column="[CategoryName]" type="String" />
    <property name="QuantityPerUnit" column="[QuantityPerUnit]" type="String" />
    <property name="UnitPrice" column="[UnitPrice]" type="double" />
    <property name="UnitsInStock" column="[UnitsInStock]" type="Int32" />
    <property name="UnitsOnOrder" column="[UnitsOnOrder]" type="Int32" />
    <property name="ReorderLevel" column="[ReorderLevel]" type="Int32" />
  </class>
</hibernate-mapping>

Below is the code from data access class, which returns only records for particular page.

//C# Code
public IList<ProductView> GetProducts(int currentPageNo, int pageRecordsCount, string sortBy, SortType sortType, out int totalRecordCount)
{
    try
    {
        IList<ProductView> ProductViewList = null;

        // Create the configuration object
        Configuration cfg = new Configuration();
        cfg.Configure(GetConfigFilePath);

        // Create the session
        ISessionFactory sessionFactory = cfg.BuildSessionFactory();
        // Open the session
        ISession session = sessionFactory.OpenSession();

        // Create Criteria
        ICriteria criteria = session.CreateCriteria(typeof(ProductView));

        if (sortBy != null) //If sortBy values pass (Pass null if not required)
            criteria.AddOrder(sortType == SortType.Ascending ? Order.Asc(sortBy) : Order.Desc(sortBy));

        if ((pageRecordsCount > 0) && (currentPageNo > 0)) // IF pageRecordsCount is more then 0 then only pagination will be done
        {
            // Filter only the required page
            criteria.SetFirstResult((currentPageNo - 1) * pageRecordsCount);
            criteria.SetMaxResults(pageRecordsCount);
        }

        ProductViewList = criteria.List<ProductView>();

        criteria = session.CreateCriteria(typeof(ProductView));
        totalRecordCount = criteria.List<ProductView>().Count; //(int)criteria.SetProjection(Projections.RowCount()).UniqueResult();

        //return
        return ProductViewList;
    }
    catch (Exception ex)
    {
        // Log
        throw ex;
    }
}

'VB Code
Public Function GetProducts(ByVal currentPageNo As Integer, ByVal pageRecordsCount As Integer, ByVal sortBy As String, ByVal sortType__1 As SortType, ByRef totalRecordCount As Integer) As IList(Of ProductView)
    Try
        Dim ProductViewList As IList(Of ProductView) = Nothing

        ' Create the configuration object
        Dim cfg As New Configuration()
        cfg.Configure(GetConfigFilePath)

        ' Create the session
        Dim sessionFactory As ISessionFactory = cfg.BuildSessionFactory()
        ' Open the session
        Dim session As ISession = sessionFactory.OpenSession()

        ' Create Criteria
        Dim criteria As ICriteria = session.CreateCriteria(GetType(ProductView))

        If sortBy IsNot Nothing Then
            'If sortBy values pass (Pass null if not required)
            criteria.AddOrder(If(sortType__1 = SortType.Ascending, Order.Asc(sortBy), Order.Desc(sortBy)))
        End If

        If (pageRecordsCount > 0) AndAlso (currentPageNo > 0) Then
            ' IF pageRecordsCount is more then 0 then only pagination will be done
            ' Filter only the required page
            criteria.SetFirstResult((currentPageNo - 1) * pageRecordsCount)
            criteria.SetMaxResults(pageRecordsCount)
        End If

        ProductViewList = criteria.List(Of ProductView)()

        criteria = session.CreateCriteria(GetType(ProductView))
        totalRecordCount = criteria.List(Of ProductView)().Count
        '(int)criteria.SetProjection(Projections.RowCount()).UniqueResult();
        'return
        Return ProductViewList
    Catch ex As Exception
        ' Log
        Throw ex
    End Try
End Function

This code has been tested with IE 6.0/8.0, Chrome 10.0, Firefox 3.6, Opera 11.01

Here is the output of the example.

Initial Screen (by default Product Name sorted with first page)

Sorted by Category

Sorted in Desc order and showing 3rd page

Skip to 4th page (usage of Go page)

You can see the output in video here

download the working example of the source code in C# here and in VB here