Category: Software Development

Passing Tables to Stored Procedures

Sometimes we need to pass a table as a parameter to the sproc – For some reason I always forget how, so here it is…

DECLARE @temptable AS UserDefinedTableTypeOrTable
INSERT INTO @temptable ([ColumnName]) VALUES ('MyValue')

EXEC   [dbo].[SprocName]
             @Parameter1 = @temptable,
             @Parameter2 = null,
             @Parameter3 = 0
             
GO

MVC and Ajax via JQuery

I’m not a fan of the Ajax stuff built into MVC – I much prefer to do it via jQuery – I feel I have more control over everything that is happening.

There are various ways to achieve the same thing but I often use the full “long winded” way of doing it using the $.ajax method over the shorter $.get and $.post functions.

I often need to update the UI depending on the result of the ajax call to the controller so my preferred method is to return a partial view from the controller that I can inject into my page. The partial view is responsible for displaying the appropriate success or fail result.

The Code

This is a basic example to unlock a users account.

jQuery

function AttachUnlockUserClickEvent() {
    $('#btnUnlockUser').unbind().on('click', function () {

        var userId = $('#selectedUserId').val();
        var password = $('#tbUnlockUserNewPassword').val();

        $.ajax({
            url: '@Url.Action("ActionName","ControllerName")',
            cache: false,
            data: { userId: userId, newPassword: password },
            type: 'GET',
            success: function (data) {
                $('#unlockResult').html(data);//this line injects the partial view HTML into the unlockResult div
                //do whatever else you need to…
           },
        });
    });
}

MVC Controller

I create an AjaxResponse object and fill it with messages I want to display to the user, also setting a success/fail stauts.

public async Task UnlockUserAccount(string userId, string newPassword)
{
    AjaxResponse ajaxResponse = new AjaxResponse()
    {
        Status = Utils.Enums.StatusType.Error //default
    };

    var result = DoWhatEverYouNeedToChangeThePasswordAndUnlockAccount();
    if (result.Succeeded)
    {
        ajaxResponse.Status = Utils.Enums.StatusType.Success;
        ajaxResponse.Messages.Add("Password reset successfully.");
        ajaxResponse.Messages.Add("User unlocked.");
    }
    else
    {
        foreach (var error in result.Errors)
        {
            ajaxResponse.Messages.Add(error);
        }
    }    

    return PartialView("_AjaxResponse", ajaxResponse);
}

The above returns a partial view that the jQuery injects into the page to feedback to the user the result.

Partial View

The class of the div and the title is set depending on the status of the model. Then each of the messages in the Model.Messages list is displayed.

@model KpmgMiddleware.ViewModels.AjaxResponse
@model KpmgMiddleware.ViewModels.AjaxResponse

@{
    string title = "";
    string className = "alert alert-dismissible";
    switch (Model.Status)
    {
        case KpmgMiddleware.Utils.Enums.StatusType.Error:
            title = "Error";
            className += " alert-danger";
            break;
        case KpmgMiddleware.Utils.Enums.StatusType.Success:
            className += " alert-success";
            title = "Success";
            break;
        case KpmgMiddleware.Utils.Enums.StatusType.Warning:
            className += " alert-warning";
            title = "Warning";
            break;
    }
}

<p>
    <div class="@className" role="alert">
        <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
        <strong>@title!</strong>
        @if (Model.Messages.Count > 0)
        {
            <ul>
                @for (int i = 0; i < Model.Messages.Count; i++)
                {
                    <li>@Model.Messages[i]</li>
                }
            </ul>
        }
    </div>
</p>

Partial View Model

This is a very simple view model and it can be expanded to include whatever you need for your UI.

public class AjaxResponse
{
    public StatusType Status { get; set; }
    public List Messages { get; set; }

    public AjaxResponse()
    {
        Messages = new List();
    }
}

SQLite DateTime format – BigInt to DateTime

In a C# project I have a DateTime property. This is being saved to a Sqlite Database as a BigInt. Everything I can find online suggests it’s a unixepoch format, so using an online converter I thought I’d be able to get a human readable format. Unfortunately not!

An example:

This is a date in my Sqlite database: 635923872000000000. When I put this into the converter at Epoch Converter the result is Mon, 08 Jul 2171 07:12:00 GMT – I know this isn’t correct as I’m expecting a date around 1st April 2017.

After some random Googling and a zero score answer on StackOverflow I hit upon the golden nugget.

To convert to unixepoch you need to use this formula:

DateColumnValue/10000000 - 62135596800

So for my example it’s:

635923872000000000/10000000 - 62135596800 = 1491087600

If you then paste this into the converter at Epoch Converter the result is Sat, 01 Apr 2017 23:00:00 GMT – exactly as I was expecting.

You shouldn’t need to do this in code as it happens “under the hood” but you may want to run a SQL command to do it when you’re looking at your database, this should do it:

SELECT datetime(dateColumnName/10000000 - 62135596800, 'unixepoch')
FROM tableName

Custom Events in XAML

I had a Calendar Page in a UWP XAML project for WindowsPhone10 used to display trips a user had planned. The calendar showed these trips on a month by month basis so the user could see all trips for the selected month.

I created a UserControl for each day and within that day each trip is also a UserControl.

The trip UserControl has 2 buttons, a StartedDayHere button and an EndedDayHere button. I needed the day UserControl to know when these had been tapped.

To do this I create custom events and delegates so the day UserControl could hook into these on the button click events:

public delegate void StartedDayHereClickedEventHandler(object sender, EventArgs e);
public event StartedDayHereClickedEventHandler StartedDayHereClicked;
        
public delegate void EndedDayHereClickedEventHandler(object sender, EventArgs e);
public event EndedDayHereClickedEventHandler EndedDayHereClicked;

Then the button click event in the trip UserControl would trigger these events as appropriate (the Trip object is a member variable of the trip UserControl:

private void btnStartedDay_Click(object sender, RoutedEventArgs e)
{
    if (Trip.StartedDayHere)
    {
        return;//do nothing - only process when moving from off to on
    }
    else
    {
        StartedDayHereClicked(this, new SkyronEventArg() { TripId = Trip.Id });
    }
}

private void btnEndedDay_Click(object sender, RoutedEventArgs e)
{
    if (Trip.EndedDayHere)
    {
        return;//do nothing - only process when moving from off to on
    }
    else
    {
        EndedDayHereClicked(this, new SkyronEventArg() { TripId = Trip.Id });
    }
}

As you can see, I created a SkyronEventArg object so I could pass the trip ID, looking back, I don’t think this was required as the trip UserControl is sent as the “sender” in the event, so I could just cast sender to a trip UserControl and get the trip ID from the Trip property. Having said that, this is still a good way of sending additional data should you need to.

Then, in my day UserControl I would create the trip UserControls as necessary, hooking into the custom click events just created, and adding it to the StackPanel in the UI. This function is called during the initial page load and as the trip UserControl buttons are tapped:

private void CreateCalendarTripUserControls(int year, int month, int day, List tripsForDay)
{
    stackPanelMainContent.Children.Clear();

    Date = new DateTime(year, month, day);
    tbDate.Text = day.ToString();
    tbDay.Text = Date.DayOfWeek.ToString().Substring(0, 3).ToUpper();

    if (tripsForDay.Count > 0)
    {
        for (int i = 0; i < tripsForDay.Count; i++)
        {
            CalendarTripUserControl calendarTripUserControl = new CalendarTripUserControl(tripsForDay[i]);
            stackPanelMainContent.Children.Add(calendarTripUserControl);

            //hook into clicked events
            calendarTripUserControl.StartedDayHereClicked += (sender, eventargs) =>
            {
                int? newStartedDayHereTripId = (eventargs as SkyronEventArg).TripId;
                if (newStartedDayHereTripId.HasValue)
                {
                    ChangeStartedDayHereTrip(newStartedDayHereTripId.Value);
                }
            };

            calendarTripUserControl.EndedDayHereClicked += (sender, eventargs) =>
            {
                int? newEndedDayHereTripId = (eventargs as SkyronEventArg).TripId;
                if (newEndedDayHereTripId.HasValue)
                {
                    ChangeEndedDayHereTrip(newEndedDayHereTripId.Value);
                }
            };
        }
    }
    else
    {
        CalendarTripUserControl calendarTripUserControl = new CalendarTripUserControl(null);
        stackPanelMainContent.Children.Add(calendarTripUserControl);
    }

    stackPanelMainContent.UpdateLayout();
}

As you can see above, I ensure there is a Trip ID before proceeding. If there is one, I call the ChangeStartedDayHereTrip function passing the trip ID. This is where you would do whatever it is you need to do in the day UserControl when a button is clicked in one of the child trip UserControls. In my project I needed to save some changes to the database, re-order the trips and update the UI.

Here’s my full ChangeStartedDayHereTrip function:

private void ChangeStartedDayHereTrip(int newStartedDayHereTripId)
{
    //this trip should be set as the startedDayHere trip - all other trips on this date should have startedDayHere set to false
                
    List todaysTrips = new List();
    for (int j = 0; j < stackPanelMainContent.Children.Count; j++)
    {
        if (stackPanelMainContent.Children[j].GetType() == typeof(CalendarTripUserControl))
        {
            todaysTrips.Add((stackPanelMainContent.Children[j] as CalendarTripUserControl).Trip);
        }
    }

    //update the previous startedDayHere trip - there should onle ever be one - but handle more than one in case the TT web service changes the data.
    List previousStartedDayHereTrips = todaysTrips.Where(p => p.StartedDayHere == true).ToList();
    previousStartedDayHereTrips.ForEach(p => p.StartedDayHere = false);
    DatabaseHelper.UpdateTrips(previousStartedDayHereTrips);

    Trip newStartedDayHereTrip = todaysTrips.SingleOrDefault(p => p.Id == newStartedDayHereTripId);
    if (newStartedDayHereTrip == null)
    {
        return;//shouldn't happen but failsafe
    }
    else
    {
        newStartedDayHereTrip.StartedDayHere = true;
        DatabaseHelper.UpdateTrip(newStartedDayHereTrip);
    }

    //put the new startedDayHere trip at the start of the list
    todaysTrips.Remove(newStartedDayHereTrip);
    todaysTrips.Insert(0, newStartedDayHereTrip);

    //re-create UI list
    CreateCalendarTripUserControls(newStartedDayHereTrip.Date.Year, newStartedDayHereTrip.Date.Month, newStartedDayHereTrip.Date.Day, todaysTrips);            
}

There is also an equivalent ChangeEndedDayHereTrip function.

UWP XAML Transparent Buttons

It’s crazy but creating a transparent button in XAML seemed to take me forever when I first started with UWP development! So heres the style I eventually came up with, hopefully it will save you guys some time too.

<Style TargetType="Button" x:Key="TransparentButton_Simple">
 <Setter Property="Background" Value="Transparent" />
 <Setter Property="BorderBrush" Value="Transparent" />
 <Setter Property="BorderThickness" Value="0" />
 <Setter Property="BorderBrush" Value="Transparent" />
 <Setter Property="Padding" Value="-1,-1,-1,-1" />
 <Setter Property="Margin" Value="0,0,0,10"/>
 <Setter Property="HorizontalContentAlignment" Value="Stretch"/>
 <Setter Property="HorizontalAlignment" Value="Stretch"/>
 </Style>

Using FontAwesome in UWP

This was pretty tricky to figure out but as with most problems like this, once you have the solution it’s pretty simple.

First, you need to install the FontAwesome.UWP Nuget package. Instructions on how to do this are on the Nuget page I’ve just linked to.

Next, on the Page or UserControl you want to use FontAwesome, you’ll need to add a reference at the top:

<UserControl 
 ...others omitted...
 xmlns:fa="using:FontAwesome.UWP"
 mc:Ignorable="d"
 d:DesignHeight="300" >

If your using the icons in a static manner, Robert Muehsig has a great guide to do this here.

Essentially, once you’ve referenced the Nuget Package, all you need to do is use the icons wherever you need them as below:

<fa:FontAwesome Icon="Flag" FontSize="90" Foreground="Chartreuse" HorizontalAlignment="Center" />

However, if you want to change icons programmatically then I couldn’t find a way to do this if you’ve created the icon initially in the front end/designer. You need to create the icon programmatically as well.

This is pretty simple thanks to the guys who made the Nuget package:

//insert activity icon
FontAwesome.UWP.FontAwesome fa = new FontAwesome.UWP.FontAwesome();
fa.Glyph = unicodeIconCode;//if you have the character in unicode
stackPanelX.Children.Insert(0,fa);//this line inserts the icon into my stack panel where required

The above works if you have the icon code as unicode, which I did. You can also use the built-in enums to select the icon:

//instead of
//fa.Glyph = unicodeIconCode;
//use this
fa.Icon = FontAwesome.UWP.FontAwesomeIcon.AddressBook;

Styling your icons

You can add styling to the icon like this:

fa.Style = (Style)Application.Current.Resources["Skyron_Icon2_Fa"];

You’ll need to create this style in you App.xaml file:

<Style TargetType="fa:FontAwesome" x:Key="Skyron_Icon2_Fa">
 <Setter Property="FontFamily" Value="Segoe MDL2 Assets"/>
 <Setter Property="FontSize" Value="22"/>
 <Setter Property="VerticalAlignment" Value="Center" />
 <Setter Property="Foreground" Value="White" />
</Style>

Again, you’ll need to reference the Nuget package at the top of the App.xaml file:

<Application
...others omitted...
xmlns:fa="using:FontAwesome.UWP"
RequestedTheme="Light">

Result

Entire code used in my project:

//insert activity icon
FontAwesome.UWP.FontAwesome fa = new FontAwesome.UWP.FontAwesome();
fa.Glyph = Trip.Activity.IconCode;
fa.Style = (Style)Application.Current.Resources["Skyron_Icon2_Fa"];
stackPanelX.Children.Insert(0,fa);

And the front end contains the stackpanel:

<StackPanel Name="stackPanelX" Grid.Row="1" Margin="5,0,0,0" Orientation="Horizontal" >
<!--Icon is injected programmatically-->
<TextBlock Name="tbTripReasonText" Text="Get Started" Style="{StaticResource Skyron_H3}" Foreground="White"/>
</StackPanel>

This should now all just work. Here’s the result in my project running in the Windows Phone 10 emulator:

Using HttpClient

using (var httpClient = new HttpClient())
{
    HttpResponseMessage response;

    HttpContent content = new StringContent(serialisedLoginCredentials, Encoding.UTF8, "application/json");
    try
    {
        response = await httpClient.PostAsync(url, content);
    }
    catch (WebException ex)
    {
        // handle web exception
        throw new Exception("WebException Auth(). PostAsync error posting to " + url, ex);
    }
    catch (TaskCanceledException ex)
    {
        throw new Exception("TaskCanceledException Auth(). PostAsync error posting to " + url, ex);//this happens on a timeout - it should be a WebException but there's a bug in HttpClient
    }
    catch (Exception ex)
    {
        throw new Exception("Exception Auth(). PostAsync error posting to " + url, ex);
    }

    if (response.StatusCode == HttpStatusCode.OK)
    {
        var responseContent = await response.Content.ReadAsStringAsync();
        //Deserialize into appropriate object
    }
    else
    {
        httpResponseMessage.StatusCode = response.StatusCode;
        httpResponseMessage.ReasonPhrase = response.ReasonPhrase;
    }
}