Using a Sybase with S#arp Architecture and nHibernate

This article will discuss on how I achieved using a Sybase database alongside SQL Server with S#arp Architecture / nHibernate.   In this article I would assume that you use S#arp and have already a running project where you want to include Sybase in your scenario.  So lets start.

1. Create your Sybase Connection String

Go to your web config and add a connection string like such

  <connectionStrings>
    <add name="SybaseConnectionString" connectionString="Data Source='{YOURSYBASEDATABASE}';Port=5000;UID='{xxxxxx}';Password='{xxxxxx}';Database='{SomeDatabase}'"/>
  </connectionStrings>

2. Create a Domain for your Sybase Tables

For this demo we will reference a table from a Sybase database called Activity.

First we create a class library project to separate you existing Domain to your Sybase Domain.  We will call it Sybase.Domain

In that project we will create a class to represent the “Activity” table

namespace Sybase.Domain
{
    public class Activity
    {
        public virtual string Code { get; set; }

        public virtual string Name { get; set; }

        public virtual string Type { get; set; }

        public virtual string SubType { get; set; }
    }
}

3. Create a class to grab that Connection String

For a cleaner implementation we create an Infrastructure Class Library Project, under the Infrastructure Solution Folder and call it Sybase.Infrastructure. This project will be a container for infrastructure related items such as repositories.  We will also create an Interface for it so we don’t reference Infrastructure in our main Web project and use the already exposed Domain.  Having said that we will be creating that Interface in the Sybase.Domain project.

At this stage import the highlighted references on the image below

Lets create your RepositoryConfiguration Class which will expose your connection string

using System.Configuration;
using Sybase.Domain.Contracts.Configuration;

namespace Sybase.Infrastructure
{
    public class RepositoryConfiguration : IRepositoryConfiguration
    {
        public string ConnectionString
        {
            get
            {
                string connectionString = ConfigurationManager.ConnectionStrings["SybaseConnectionString"].ConnectionString;

                return connectionString;
            }
        }
    }
}

Then the interface in your Sybase.Domain project

namespace Sybase.Domain.Contracts.Configuration
{
    public interface IRepositoryConfiguration
    {
        string ConnectionString { get; }
    }
}

4. Create a mapping Extension

Now you have your domain we need to create a reusable Mapping mechanism so Sybase column names map properly to the Sybase Domain Classes.  At this stage we will be creating it in another class library project which we call Framework, this will contain Framework Related Items like Referenced Assemblies, Extensions as well as Helpers.  Let’s call it Demo.Framework.

Inside the project we create and Extensions folder and create our AutoMappingExtension class

using System;
using System.Collections.Generic;
using System.Data;

namespace Demo.Framework.Extensions
{
    public static class AutoMappingExtension
    {
        public static IList<T> Map<T>(this IDataReader dataReader) where T : new()
        {
            IList<T> list = new List<T>();

            if (dataReader != null)
            {
                while (dataReader.Read())
                {
                    var destination = new T();
                    Type destinationType = destination.GetType();

                    for (int i = 0; i < dataReader.FieldCount; i++)
                    {
                        string sourceFieldName = dataReader.GetName(i);
                        string destinationPropertyName = sourceFieldName.InflectTo().Pascalized;

                        var destinationProperty = destinationType.GetProperty(destinationPropertyName);

                        if (destinationProperty != null && destinationProperty.CanWrite)
                        {
                            object destinationValue = FormatDestinationValue(dataReader[i], destinationProperty.PropertyType.GetUnderlyingType());
                            destinationProperty.SetValue(destination, destinationValue, null);
                        }
                    }

                    list.Add(destination);
                }

                dataReader.Close();
            }

            return list;
        }

        public static Type GetUnderlyingType(this Type source)
        {
            if (source.IsGenericType && (source.GetGenericTypeDefinition() == typeof(Nullable<>)))
            {
                // source is a Nullable type so return its underlying type
                return Nullable.GetUnderlyingType(source);
            }

            // source isn't a Nullable type so just return the original type
            return source;
        }

        private static object FormatDestinationValue(object value, Type destinationType)
        {
            if (value == null || value == DBNull.Value)
            {
                return null;
            }

            TypeCode destinationTypeCode = Type.GetTypeCode(destinationType);

            switch (destinationTypeCode)
            {
                case TypeCode.String:
                    value = value.ToString().TrimEnd();
                    break;

                case TypeCode.DateTime:
                    value = Convert.ToDateTime(value);
                    break;
            }

            return value;
        }
    }
}

Please note that we will use Inflector Extension (http://brendanjerwin.com/blog/2010/02/25/inflector-extension/) to provides a convenient Inflect() extension method on string and int data types

5. Create your first Sybase Repository

Now you have everything you need lets create your first Repository which will be in your Sybase.Infrastructure project, Lets call it ActivitiesRepository.  You will also need to create your Interface in Sybase.Domain project.

Lets do a simple Get By, so here is your method

using System;
using Sybase.Domain.Contracts.Configuration;
using System.Collections.Generic;
using Sybase.Domain;
using Sybase.Data.AseClient;
using Sybase.Domain.Repositories;
using System.Data;
using Demo.Framework.Extensions;
namespace Sybase.Infrastructure
{
    public class ActivitiesRepository : IActivitiesRepository
    {
        private readonly IRepositoryConfiguration repositoryConfiguration;
        public ActivitiesRepository(IRepositoryConfiguration repositoryConfiguration)
        {
            this.repositoryConfiguration = repositoryConfiguration;
        }
        public IList<Activity> GetBy(string activityCode)
        {
            try
            {
                IList<Activity> activities;

                using (var connection = new AseConnection(repositoryConfiguration.ConnectionString))
                {
                    var command = connection.CreateCommand();
                    command.CommandType = CommandType.Text;

                    command.CommandText = @"SELECT
                                            activity_code as Code,
                                            activity_name as Name,
                                            activity_type as Type,
                                            activiy_subtype as SubType
                                            FROM dbo.activity 
                                            WHERE activity_code = '" + activityCode + "'";

                    connection.Open();

                    using (var dataReader = command.ExecuteReader())
                    {
                        activities = dataReader.Map<Activity>();
                    }
                }

                return activities;
            }
            catch (Exception ex)
            {
                // TODO: Log exception
                return null;
            }
        }
    }
}

Then your Interface in your Sybase.Domain project

using System.Collections.Generic;

namespace Sybase.Domain.Repositories
{
    public interface IActivitiesRepository
    {
        IList<Activity> GetBy(string activityCode);
    }
}

6. Register the projects you just created in CastleWindsor’s Component Registrar

Go to your Presentation Layer, in this case its Demo.Web.Mvc.  In the CastleWindsor Folder there is a Class called ComponentRegistrar.  Add the following codes to under the AddQueryObjectsTo object

container.Register(
    AllTypes.FromAssemblyNamed("Sybase.Infrastructure")
        .Pick()
        .WithService.FirstNonGenericCoreInterface("Sybase.Domain"));

You also need to reference your Sybase Related Projects here

7. Create your query object and test your code

In this instance we create a method in your controllers queries and call it GetActivities.

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

namespace Demo.Web.Mvc.Controllers.Queries
{
    public class HomeQuery : NHibernateQuery
    {
        private readonly Sybase.Domain.Contracts.Repositories.IActivitiesRepository activitiesRepository;
        public HomeQuery(
              Sybase.Domain.Contracts.Repositories.IActivitiesRepository activitiesRepository
            )
        {
            this.activitiesRepository = activitiesRepository;
        }

        public void GetActivities(string activityCode)
        {
            activitiesRepository.GetBy(activityCode);
        }

    }
}

Now run and test your code.

Upgrading S#arp to MVC4

First of all I can assure you that S#arp will work on MVC4 and I am using Sharp Architecture 2.0.4.  We just recently migrated and tested 2 large S#arp projects and it is fully working and is running in production.

Now lets go to the details, I guess you came across tis article either because you want to use MVC4 with your S#arp project or you got this “Entry point was not found” error because you recently installed MVC4 in your machine

If its the second reason yes, it is MVC4 doing it and not S#arp your project just auto referenced the latest MVC version.  Now to fix that issue is that you need to upgrade your S#arp project to use MVC4 this can also be used in any .Net web projects that is using MVC3 so this guide is not specific to S#arp projects.  Lets start!

1. Go to all of your web projects in this case if its S#arp its in the presentation layer.  You need to replace all instances of

System.Web.Mvc, Version="3.0.0.0"
System.Web.WebPages="", Version="1.0.0.0"
System.Web.Helpers="", Version="1.0.0.0"
System.Web.WebPages.Razor="", Version="1.0.0.0"

to

System.Web.Mvc="", Version="4.0.0.0"
System.Web.WebPages="", Version="2.0.0.0"
System.Web.Helpers="", Version="2.0.0.0"
System.Web.WebPages.Razor="", Version="2.0.0.0"

You can see this in 3 different locations

First is in the configSections

which means your old web.config lines which looks like this

will become like this

Next is in system.web.webPages.razor section under host and in system.web under assemblies

which means your old web.config lines which looks like this

will become like this

Finally check also under the views folder, there is one web.config file in there.

Old one will be like this

change it so it looks like this

In the root Web.config file update or add the following is not yet there

<appSettings>
  <add key="webpages:Version" value="2.0.0.0" />
  <add key="PreserveLoginUrl" value="true" />
</appSettings>

3. In Solution Explorer, right-click the project name (if using s#arp its the {ProjectName}.Web.Mvc) and then select Unload Project this will unload you project and you can edit what’s in the code behind.  Once unloaded right-click the name again and select Edit {ProjectName}.csproj.

Find the ProjectTypeGuids element and replace {E53F8FEA-EAE0-44A6-8774-FFD645390401} with {E3E379DF-F4C6-4180-9B81-6769533ABE47}.

so from this

will become like this

Save and close file you just edited, right-click the project, and then select Reload Project.  This will load the project with the new settings.

4. If the project references any third-party libraries that are compiled using older versions of MVC (definitely S#arp is) then you have to add the following
three bindingRedirect elements under the configuration section in your web.config

here is what you need to add:

<dependentAssembly>
  <assemblyIdentity name="System.Web.Helpers"
        publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0" newVersion="2.0.0.0"/>
</dependentAssembly>
<dependentAssembly>
  <assemblyIdentity name="System.Web.Mvc"
        publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="4.0.0.0"/>
</dependentAssembly>
<dependentAssembly>
  <assemblyIdentity name="System.Web.WebPages"
        publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0" newVersion="2.0.0.0"/>
</dependentAssembly>

so it should look like this

Using SQL Server Reporting Services in MVC Web Application

You might be wondering how to use SQL Server Reporting Services Reports with an MVC Application hence you’re in this page, well let me explain to you in this simple steps

At the time of this post there is no controls yet for Report Viewer in MVC so we will be doing some workaround to make it happen. I will also assume that you already have a Reporting Server with reports in place so I will not discuss that section

Lets start.

First is you need is a Classic Web Form where you will include the Report Viewer, I suggest to place it outside of your view folder so you don’t have to register or ignore a Route.  In my case I added it in a folder outside the view called Content, this is where I store my images as well as other documents that a part of the whole project.

Let us name it ReportingServices.aspx

Now go to your design view and add a Script Manager an Report Viewer

Now go to code view and give it the right ReportPath and ReportServerUrl

<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>

<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" 
    Font-Size="8pt" InteractiveDeviceInfos="(Collection)" ProcessingMode="Remote" 
    WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Height="100%" 
    Width="100%">
    <ServerReport ReportPath="/YourApplicationName/YourReportName" 
        ReportServerUrl="http://yourserver/ReportServer" />
</rsweb:ReportViewer>

Now you have your viewer, now lets use it in your view

Create a view and lets call it SampleReport.cshtml

Inside that view use an IFrame with the source pointing to the path of your ReportingServices.aspx (I will leave the adjusting of height and width with you)

Now you have your view all you need is call it from your controller, lets create an ActionResult and call it GetSampleReport.

public class ReportsController : Controller
{

    public ActionResult Index()
    {
        return View();
    }

    public ActionResult GetSampleReport()
    {
        return View("SampleReport");
    }   
}

There you have it you can now run a report within an MVC application

Programmatic Execution of an Excel Macro on Remote Machine from a website

I tried searching everywhere for a solution on this issue but found nothing. To give you an idea what I want to achieve here is the scenario.

We have a website where users generate and Excel report using a macro, when I try to run it in my local machine it generates perfectly and runs the macro inside the excel. When I publish it into the server and at the same time I am logged in there (RDP open session) and try to run it from a browser outside that server it is also running as expected. The problem occurs when I am logged off in the server (RDP) then run it in a browser outside the server (ie from my machine) the macro does not run but creates my Excel.

This is the code that I am using

public class Report
{
    protected Workbook Workbook { get; set; }
    protected Application Excel { get; set; }

    public void RunReport()
    {
        // Launch Excel on the server
        Excel = new Application
        {
            DisplayAlerts = false,
            ScreenUpdating = false,
            Visible = false
        };

        // Load the workbook template  
        Workbook = Excel.Workbooks.Open(@"C:\{YourTemplateName}.xlt");

        // You your stuff here
        DoStuff();

        // Execute macros 
        ExecuteMacros();

        Workbook.SaveAs(@"C:\{YourExportedFileName}.xls", XlFileFormat.xlExcel8);

        // Properly Close Excel
        QuitExcel();

    }

    private void QuitExcel()
    {
        if (Workbook != null)
        {
            Workbook.Close(false);
            Marshal.ReleaseComObject(Workbook);
        }

        if (Excel != null)
        {
            Excel.Quit();
            Marshal.ReleaseComObject(Excel);
        }
    }    

    private void ExecuteMacros()
    {
        const string moduleName = "{YourModuleName}";
        const string macroName = "{YourMacroName}";

        bool macroExists = false;
        try
        {
            var macroModule = Workbook.VBProject.VBComponents.Item(moduleName);
            if (macroModule != null)
            {
                int macroStartLine = macroModule.CodeModule.ProcStartLine[macroName, Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc];
                macroExists = macroStartLine > 0;
            }
        }
        catch (Exception)
        {
            //no macro found
            macroExists = false;
        }

        if (!macroExists)
        {
            return;
        }

        // VBA code for the dynamic macro that calls 
        var moduleCode = new StringBuilder();
        moduleCode.AppendLine("Public Sub LaunchMacro()");
        moduleCode.AppendLine(string.Format("{0}.{1}", moduleName, macroName));
        moduleCode.AppendLine("End Sub");

        // Add the dynamic macro 
        var workbookMainModule = Workbook.VBProject.VBComponents.Item("ThisWorkbook");
        workbookMainModule.CodeModule.AddFromString(moduleCode.ToString());

        // Execute the dynamic macro
        Microsoft.VisualBasic.Interaction.CallByName(Workbook, "LaunchMacro", Microsoft.VisualBasic.CallType.Method, new object[] { });
    }
}

as explained everything works properly until you log off to the server.  Now after some investigation we notice that the Trust access to the VBA project object model is turned off when you log out of the server which makes your macro useless as it would not run.

It was a bit of a search to come up with this conclusion but nice to know there was a solution and here it is.

Since we know that is being turned off when a user logs out we have to create a method on our application to turn that on so we can run our macro happily and that is done through a registry setting like such.

private static void ModifyExcelSecuritySettings()
{
    // Make sure we have programmatic access to the project to run macros
    using (var key = Microsoft.Win32.Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\14.0\Excel\Security", true))
    {
        if (key != null)
        {
            if ((int)key.GetValue("AccessVBOM", 0) != 1)
            {
                key.SetValue("AccessVBOM", 1);
            }
            key.Close();
        }
    }
}

Just change the path depending on the Excel version you are using.

This is then used before you launch Excel on the server like such.

public void RunReport()
{
    ModifyExcelSecuritySettings();

    // Launch Excel on the server
    Excel = new Application
    {
        DisplayAlerts = false,
        ScreenUpdating = false,
        Visible = false
    };

    // Load the workbook template  
    Workbook = Excel.Workbooks.Open(@"C:\{YourTemplateName}.xlt");

    // You your stuff here
    DoStuff();

    // Execute macros 
    ExecuteMacros();

    Workbook.SaveAs(@"C:\{YourExportedFileName}.xls", XlFileFormat.xlExcel8);

    // Properly Close Excel
    QuitExcel();

}

BTW for those interested the site is deployed in IIS7 using Integrated Application Pool Identity and Passtrough Authentication / Identity Impersonation.

Draggable Sorting and Saving Changes using MVC and jQuery UI

We’ve been posting a lot of MVC and jQuery lately and I guess you have to get used to it for a couple of months more as this the technology that I am using currently in most of my projects. For today’s session I will be discussing on how to make a sortable list which you can drag and pass that sorted list information back to your server.

Not let’s make some assumptions.

Lets say you want a list to be sortable in your web project, usually Old school UI’s do it the Sharepoint way like this

or the old windows way like this

and I can’t blame developers who did this as this is the easy way to do it for a web UI, I myself made some sorting like this before. But now thanks to jQuery and jQuery UI things are much better and tasks like this just needs an element id and call the .sortable() method.

Now before we start lets dissect first what we need, and here it is

1. A view which we call SortingOptions
2. A view model to define the Sorting Options, we call the parent view model as UserPreferenceViewModel and the child view model which contains the details of each item on the list as UserPreferenceDetailViewModel
3. A controller which contains two actions, InitializeModifyView which populates your view model and SaveUserPreferenceDetails which saves the ordering of the list

Lets start.

First here is how your View Model should look like for this example, I think the property names are self-explanatory.

public class UserPreferenceViewModel 
{
    public int Id { get; set; }
    public IList<UserPreferenceDetailViewModel> UserPreferenceDetails { get; set; }
}

public class UserPreferenceDetailViewModel 
{
    public int Id { get; set; }
    public string Description { get; set; }
    public bool IsEnabled { get; set; }
    public int ColumnOrder { get; set; }
}

Next is the View. There will be 3 main sections that I want to cover here, first is to import the jQuery and jQuery UI Scripts

<script src="@Url.Content("~/Scripts/jquery-1.7.1.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.ui.core.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.ui.widget.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.ui.mouse.js")" type="text/javascript"></script>   
<script src="@Url.Content("~/Scripts/jquery.ui.sortable.js")" type="text/javascript"></script>

You need all of these references for the sortable function to work, it’s the documented dependencies you will see in the jQuery UI site. If you don’t have the jQuery UI you can get it here, the downloads can be customized to what you need. You can also use NuGet but there is a catch, it will work but the jQuery dependency listed is between versions 1.4.4 and 1.6(as of this posting date)

which will automatically download the 1.4 if you have a different version of jQuery. And yes the jQuery UI will work in 1.7 thats what I am using.

Now let’s go to the UI elements, it’s a simple <ul> and <li>’s with id’s so we can refer to them easily. You will also notice that we will be looping to all the UserPreferenceDetails to show that on the <li>element. The span will show the image and there will be a checkbox if you want to enable/disable the list item. There will be also button for saving the users preference.

@model UserPreferenceViewModel
@using YourNamespace;
@{
    Layout = null;
}

<div id="modify-view-command">
    <p class="message information">Drag the columns up and down according to the order you want, you can choose the items you want to enable/disable by ticking the check box</p>
    <button id="save-view-preferences" class="t-button">
        Save Changes</button>
</div>

<div id="modify-view-elements">
    <ul id="sortable">
        @foreach (UserPreferenceDetailViewModel m in Model.UserPreferenceDetails)
        {
            <li><span id="sort-updown"></span>
                @Html.CheckBox("chkEnabled" + m.Id, m.IsEnabled, new { @class = "EnabledFlag" })
                @m.Description
                @Html.Hidden("hdnId-" + m.Id, m.Id, new { @class = "ItemId" })
            </li>
        }
    </ul>
</div>

Finally the jQuery that makes everything happen, here you now refer to the sortable element which is our <li>,this is what makes it happen when you apply a sortable() method on it. You will also notice that we are passing an array to our Action / Controller and you need to make sure the names are exactly the same as your View Model in this case the UserPreferenceDetail, MVC will automatically then map this so you don’t need to interpret the data, all you need is to pass it as a JSON String. Also on that array you will pass the index which is the sorted index value of the list, this will take care of your Sorting Order value.

<script type="text/javascript">

$(function () {
    $("#sortable").sortable();
    $("#sortable").disableSelection();
    $("#save-view-preferences").click(saveUserPreferenceDetails);
});

function saveUserPreferenceDetails() {
    var userViewPreferenceDetails = new Array();
    $("#sortable li").each(function (index) {
        var userPreferenceItem = new Object();
        userPreferenceItem.Id = $(this).find($(".ItemId")).val();
        userPreferenceItem.IsEnabled = $(this).find($(".EnabledFlag")).attr("checked") == "checked" ? true : false;
        userPreferenceItem.ColumnOrder = index;
        userPreferenceDetails.push(userPreferenceItem);
    });
    $.ajax({
        type: 'POST',
        url: '/YourActionName/SaveUserViewPreferenceDetails',
        data: JSON.stringify(userPreferenceDetails),
        dataType: 'json',
        contentType: 'application/json; charset=utf-8',
        success: function () {
            window.location.replace("http://whereyouwantogoafter.com");
        }
    });
}

</script>

Now for the Actions. InitializeModifyView is a simple as popualting the view model and SaveUserPreferenceDetails takes care of the saving. And if you notice it accepts a list of UserPreferenceDetailViewModel and thats the one that we created as an array and stringify from our jQuery code.

[HttpPost]
public JsonResult InitializeModifyView()
{
    var viewModel = yourQuery.PopulateViewModel();

    return Json(new { viewHtml = RenderRazorViewToString("SortingOptions", viewModel) }); 
}

public virtual void SaveUserViewPreferenceDetails(List<UserPreferenceDetailViewModel> savedItems)
{
    foreach (var item in savedItems)
    {
        yourTasks.SaveUserPreferenceDetail(item);
    }
}

Easy enough? well imagine life without jquery!