ASP.NET: Running EloqueraDb in Desktop Mode in an ASP.NET Web Application

Eloquera DB is a 100 percent .NET managed code object database that incorporates the best features of relational and object databases and overcomes their deficiencies. The database can be installed as a Windows Service (like SQL Server), but it can also be run in embedded "Desktop" mode. Here we show how you can use EloqueraDb in Desktop mode in an ASP.NET Web Application.

I've written about EloqueraDb before; you can view the first article here.   It shows database features, how to connect, and includes some basics regarding sample queries. You can get complete information and downloads here.

In order to package EloqueraDb in Desktop mode, you only need to have references to the Eloquera.Client.dll, the Eloquera.Common.dll, and the  Eloqera.Server.exe assemblies. Their combined size is 791K. There is also a Nuget package for this.
Also required is the eloquera.config file (a sample of which is in the downloadable demo solution).
The only other needed item is the modified connection string; you use (local) instead of the IP Address of the service:

db = new DB("server=(local);password=;options=none;");

All operations are performed on the "db" object that is returned from the connection.

There are several basic features of EloqueraDb that I like:

1) You can work with your objects directly. There is no need for funky Attributes or anything else. Plain old POCO's work fine, even if they have embedded additional classes as properties.
2) The database engine is extremely fast.
3) You can also use the SQL language. Complex queries with joins and functions are possible.
4) The database is free for commercial use. Did I say "Free"? It is!

There are many more features; you can refer to the previous article for a comprehensive list.

To start, I have a couple of static methods in my Global.asax.cs class:

public static Eloquera.Client.DB db;

public static Eloquera.Client.DB GetOpenConnection()
{
     db.OpenDatabase("SampleDb");
    return db;
}

protected void Application_Start(object sender, EventArgs e)
{
    const string dbName = "SampleDb";
    // Desktop mode:
   db = new DB("server=(local);options=none;");
    // Custom path:
    Eloquera.Common.ServerSettings settings = new ServerSettings();
    settings.DatabasePath = Server.MapPath("~/DB");
    // Does database exist? If not, create it
    if (!System.IO.File.Exists(Server.MapPath("~/DB/SampleDb.eq")))
db.CreateDatabase(dbName);
}

The only method you would normally ever need is the "GetOpenConnection" method. This returns an active instance of the Client.

In this demo, I have created a simple "Note" application with the following POCO class as the base object:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WebProject
{
    public class Note
    {
         public int ID { get; set; }
        public string Title { get; set; }
        public string Message { get; set; }
        public DateTime LastUpdated { get; set; }
    }
}

In the single ASP.NET web page, I have a Gridview, and a Panel which holds the fields to create a new Note (the ID and the LastUpdated fields are populated for you).

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebProject.Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
     <div align=center>
         <asp:GridView ID="gvNote" runat="server" AutoGenerateColumns="False" CellPadding="4"
            
DataKeyNames="ID"
             
onrowcancelingedit="gvNote_RowCancelingEdit"
             
onrowdatabound="gvNote_RowDataBound" onrowdeleting="gvNote_RowDeleting"
             
onrowediting="gvNote_RowEditing" onrowupdating="gvNote_RowUpdating"
             
onsorting="gvNote_Sorting" ForeColor="#333333" GridLines="None">
        <RowStyle BackColor="#FFFBD6" ForeColor="#333333"  />
            <AlternatingRowStyle BackColor="White" />
            <Columns>
                 <asp:CommandField ShowEditButton="True" />
                 <asp:CommandField ShowDeleteButton="True" />
                 <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True"
                     
SortExpression="ID"  />
                 <asp:TemplateField HeaderText="Title" SortExpression="Title">
                     <EditItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Title") %>'></asp:TextBox>
                     </EditItemTemplate>
                     <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("Title") %>'></asp:Label>
                     </ItemTemplate>
                 </asp:TemplateField>
                 <asp:TemplateField HeaderText="Message" SortExpression="Message">
                     <EditItemTemplate>
                        <asp:TextBox ID="TextBox2" runat="server" TextMode="MultiLine" Text='<%# Bind("Message") %>'></asp:TextBox>
                     </EditItemTemplate>
                     <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("Message") %>'></asp:Label>
                     </ItemTemplate>
                 </asp:TemplateField>
                   <asp:TemplateField HeaderText="Date" SortExpression="LastUpdated">
                     <EditItemTemplate>
                        <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("LastUpdated") %>'></asp:TextBox>
                     </EditItemTemplate>
                     <ItemTemplate>
                        <asp:Label ID="lblLast" runat="server" Text='<%# Bind("LastUpdated") %>'></asp:Label>
                     </ItemTemplate>
                 </asp:TemplateField>
            </Columns>
            <FooterStyle BackColor="#990000" ForeColor="White" Font-Bold="True" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#FDF5AC" />
            <SortedAscendingHeaderStyle BackColor="#4D0000" />
            <SortedDescendingCellStyle BackColor="#FCF6C0" />
            <SortedDescendingHeaderStyle BackColor="#820000" />
        </asp:GridView>
        <br />
        <asp:LinkButton ID="lbtnAdd" runat="server" onclick="lbtnAdd_Click">AddNew</asp:LinkButton>
        <br />
        <br />
        <asp:Panel ID="pnlAdd" runat="server" Visible="False">
            Title:
            <asp:TextBox ID="tbTitle" TextMode="MultiLine" runat="server"></asp:TextBox>
            <br />
            <br />
            Message:
            <asp:TextBox ID="tbMessage" TextMode="MultiLine" runat="server"></asp:TextBox>
            <br />
            <br />
            <asp:LinkButton ID="lbtnSubmit" runat="server" onclick="lbtnSubmit_Click">Submit</asp:LinkButton>
               
            <asp:LinkButton ID="lbtnCancel" runat="server" onclick="lbtnCancel_Click">Cancel</asp:LinkButton>
        </asp:Panel>
    </div>
    </form>
</body>
</html>

The GridView is set up for Sorting, Editing, and Updating. Here's the codebehind class:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Eloquera.Client;
using Eloquera.Common;


namespace WebProject
{
    public partial class Default : System.Web.UI.Page
    {
         protected void Page_Load(object sender, EventArgs e)
         {
             if (!IsPostBack)
             {
                 // Enable the GridView sorting option.
                gvNote.AllowSorting = true;
                 // Initialize the sorting expression.
                 ViewState["SortExpression"] = "ID ASC";
                 // Populate the GridView.
                 BindGridView();
             }
         }

        private void BindGridView(string sortExpression=null)
        {
            string query = "Select  note from Note ";
           if (sortExpression != null)
                query += " Order by " + sortExpression;
             else
            {
                query += " Order By ID";
            }

            var db = Global.GetOpenConnection();
            var notes = Global.db.ExecuteQuery(query);
                 // Bind the GridView control.
            gvNote.DataSource = notes;
                gvNote.DataBind();
             db.Close();
        }

         
         // GridView.RowDataBound Event
        protected void gvNote_RowDataBound(object sender, GridViewRowEventArgs e)
         {
             // Make sure the current GridViewRow is a data row.
            if (e.Row.RowType == DataControlRowType.DataRow)
             {
                 // Make sure the current GridViewRow is either
                 // in the normal state or an alternate row.
                 if (e.Row.RowState == DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate)
                 {
                     // Add client-side confirmation for deleting.
                     ((LinkButton)e.Row.Cells[1].Controls[0]).Attributes["onclick"] = "if(!confirm('Are you certain you want to delete this Note ?')) return false;";
                 }
             }
        }

        

         // GridView.RowEditing Event
        protected void gvNote_RowEditing(object sender, GridViewEditEventArgs e)
         {
             // Put the GridView control into edit mode
            // for the selected row.
            gvNote.EditIndex = e.NewEditIndex;
             // Rebind the GridView control to show data in edit mode.
            BindGridView();
            // Hide the Add button.
            lbtnAdd.Visible = false;
        }

         // GridView.RowCancelingEdit Event
        protected void gvNote_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
         {
             // Exit edit mode.
            gvNote.EditIndex = -1;
             // Rebind the GridView control to show data in view mode.
            BindGridView();
            // Re-Show the Add button.
            lbtnAdd.Visible = true;
        }

         // GridView.RowUpdating Event
        protected void gvNote_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            var db = Global.GetOpenConnection();
            string noteId = e.Keys["ID"].ToString() ;
            Note no = (Note)db.ExecuteScalar("Select top 1 note from Note WHERE ID = "+ noteId );
            no.Title =e.NewValues["Title"].ToString() ;
            no.Message = e.NewValues["Message"].ToString();
            no.LastUpdated = Convert.ToDateTime( e.NewValues["LastUpdated"]);
            db.Store(no);
        // Exit edit mode.
            gvNote.EditIndex = -1;
             // Rebind the GridView control to show data after updating.
            BindGridView();
            // Show the Add button.
            lbtnAdd.Visible = true;
        }

         // GridView.RowDeleting Event
        protected void gvNote_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            var id = (int) e.Keys["ID"];
            var db = Global.GetOpenConnection();
           IEnumerable listOfNotes = Global.db.ExecuteQuery("SELECT Note WHERE ID=" +id.ToString( ));
           foreach (Note note in listOfNotes)
           {
               // Delete - deletes an object that was retrieved from the database.
                db.Delete(note);
           }
            db.Close();
            // Rebind the GridView control to show data after deleting.
            BindGridView();
        }

         // GridView.Sorting Event
        protected void gvNote_Sorting(object sender, GridViewSortEventArgs e)
        {
            string[] strSortExpression = ViewState["SortExpression"].ToString().Split(' ');
            // If the sorting column is the same as the previous one,
            // then change the sort order.
            if (strSortExpression[0] == e.SortExpression)
             {
                 if (strSortExpression[1] == "ASC")
                 {
                     ViewState["SortExpression"] = e.SortExpression + " " + "DESC";
                 }
                 else
                 {
                     ViewState["SortExpression"] = e.SortExpression + " " + "ASC";
                 }
             }
             // If sorting column is another column,  
            // then specify the sort order to "Ascending".
            else
            {
                 ViewState["SortExpression"] = e.SortExpression + " " + "ASC";
            }
            // Rebind the GridView control to show sorted data.
            BindGridView( (string)ViewState["SortExpression"]);
        }

         protected void lbtnAdd_Click(object sender, EventArgs e)
         {
             // Hide the Add button and showing Add panel.
            lbtnAdd.Visible = false;
            pnlAdd.Visible = true;
        }

         protected void lbtnSubmit_Click(object sender, EventArgs e)
        {
            Note note = new Note();
            var db = Global.GetOpenConnection();
             // Get max Note Id from db;
             db.OpenDatabase("SampleDb");
            // Get max NoteID from store
            Note no = (Note)Global.db.ExecuteScalar("Select top 1 note from Note ORDER BY  ID DESC");
            int max = no.ID;
            note.ID = max+1;
            note.Title = tbTitle.Text;
            note.Message = tbMessage.Text;
            note.LastUpdated = DateTime.Now;
             db.Store(note);
             db.Close();
            BindGridView();
            // Empty the TextBox controls.
            tbTitle.Text = "";
            tbMessage.Text = "";
            // Show the Add button and hiding the Add panel.
            lbtnAdd.Visible = true;
            pnlAdd.Visible = false;
        }

         protected void lbtnCancel_Click(object sender, EventArgs e)
         {
             // Empty the TextBox controls.
            tbTitle.Text = "";
            tbMessage.Text = "";
            // Show the Add button and hiding the Add panel.
            lbtnAdd.Visible = true;
            pnlAdd.Visible = false;
        }
    }
}

Most of the above should be self-evident. You can download the fully-working Visual Studio 2010 solution, which includes all the needed EloqueraDb assemblies, as well as a starter database with a few Note objects in it.

By Peter Bromberg   Popularity  (9174 Views)