SQL Server: Finding a column in multiple databases.

My client has many instances of the same database in various versions. The version I’m developing against is missing a field expected by a c# class, and I can’t find a script to create the column.

I came up with the following SQL Server query to allow me to search all the instances of the database to find one that contained the field.

EXEC sp_MSforeachdb 
 'SELECT ''Found in ?''
 FROM sys.columns 
 WHERE Name = N''FieldName''
 AND Object_ID = Object_ID(N''TableName'')'

Replace the field name and the table name with the values you’re looking for and it will search all the databases on the server for  a match.

Posted in Development, Uncategorized | Tagged | Leave a comment

Launching .EXE applications from a browser

My client has an Angular intranet portal for their main c# WinForm application. They have a requirement to launch the main application from the portal website.

There is a fairly easy way to go about this in Windows although it does require adding an entry to the registry, to set up a URI Scheme. Here is an example to load notepad.exe

HKEY_CLASSES_ROOT
   jmNotepad
      (Default) = "URL:jmNotepad Protocol"
      URL Protocol = ""
      DefaultIcon
         (Default) = "notepad.exe,1"
      shell
         open
            command
               (Default) = "C:\windows\notepad.exe"

In the web page, add a simple link:

<a href="jmNotepad:">Launch Notepad</a>

The first time it’s launched you get a warning, which you can click to ignore subsequently.

Posted in Development, Uncategorized, Web Development | Tagged , | Leave a comment

Trouble with Keys

I’ve just spent a couple of hours scratching my head with database/entity framework problem.

My client has a requirement to duplicate records from one database to another when the first is updated.

The tables were identical and based on a class inherited from the IdentityUserRole class, with another field added to the composite key.  However, although data saved OK into the original database, an error was being thrown when trying to duplicate the records into the second database.

After some debugging, I found out that it was an issue with the primary key. It wasn’t recognising the composite key.

The quick fix was to add the following into the dbContext class, in the OnModelCreating method:

modelBuilder.Entity<ApplicationUserTeamRole>().ToTable("AspNetUserRoles")
 .HasKey(x=>new{x.RoleId,x.UserId, x.TeamId});

Everything immediately started working.

Posted in Development, EntityFramework | Tagged | Leave a comment

WebApi2, CORS & HTTP 401 Unauthorized error

I got caught out with this issue again today, and it took me ages to find/remember the fix. Whilst using VS2013 to debug a website & web service running on separate ports on localhost, I kept getting a 401 error. I checked and rechecked that I had CORS set up properly, but the error just wouldn’t go away.

At last I remembered to set the default credentials on the WebClient instance:

        public User GetUser(string userName)
        {
            User model = null;
            string json = string.Empty;
            using (WebClient client = new WebClient())
            {
                client.UseDefaultCredentials = true;

                try
                {
                    string url = this.Url + "/"  + HttpUtility.UrlEncode(userName);

                    json = client.DownloadString(url);
                }
                catch (WebException)
                {
                    json = string.Empty;
                }
            }
            if (json.Length > 0)
            {
                model = JsonConvert.DeserializeObject(json);
            }
            return model;
        }
Posted in Development, Web Development, WebApi | Tagged , , , | Leave a comment

Automatically building NuGet packages for your project

I’ve been using the Nuget Package Explorer for ages, but recently found out how to automatically build  the NuGet for a project as part of your normal build.

  1. Enable NuGet Package Restore  for the solution. This will create the .nugget folder with NuGet.exe and a configuration file.
  2.  You need to edit the .csProj file and add the following lines:
    <Target Name="AfterBuild" Condition=" '$(Configuration)' == 'Release'">
       <GetAssemblyIdentity AssemblyFiles="$(TargetPath)"> 
          <Output TaskParameter="Assemblies" ItemName="AssemblyVersion" /> 
       </GetAssemblyIdentity> 
       <Exec Command="echo %(AssemblyVersion.Version)" /> 
       <Message Text="Released %(AssemblyVersion.Version)" Importance="high" /> 
       <Exec Command="nuget pack TG.DocumentManagement.RetrievalIntegration.csproj -Version %(AssemblyVersion.Version)">
       </Exec> 
    </Target>
  3. Create a .nuspec file:
    <?xml version="1.0"?>
    <package >
      <metadata>
        <id>$id$</id>
        <version>$version$</version>
        <title>$title$</title>
        <authors>$author$</authors>
        <owners>$author$</owners>
      <iconUrl>http://server//images/icon_32x32.png</iconUrl>
        <requireLicenseAcceptance>false</requireLicenseAcceptance>
        <description>$description$</description>
        <releaseNotes>Summary of changes made in this release of the package.</releaseNotes>
      <copyright>Copyright ©SilverCode Solutions Limited 2016</copyright>
        <tags>SilverCode  Project blah blah blah</tags>
      </metadata>
      <files>
       <file src="web.config.transform" target="content/web.config.transform"/>
      </files>
    </package>
  4. Reload the project, every build should create a nugget package for in the output folder.
Posted in Development, Uncategorized | Tagged , | Leave a comment

Login redirection using AngularJS & UI-Router

Angular.js

Angular

When a user follows a link on an email to the site, but needs to login before they can view the page, you need to remember the requested page and return to it after a successful login.

In the following code examples, I’m using an AuthenticationService which handles user login, and stores authentication state.

First on the landing page, you need to store the current router state and the params needed to get back to the specific page.

if (!AuthenticationService.isAuthenticated) {
$cookies.loginDestination = $state.$current.name;
$cookies.loginParams = { id: $stateParams.id};
$state.transitionTo("Login");
};

In the AuthenticationService, after a successful login:

var loginDestination = $cookies.loginDestination || '/';
var loginParams = $cookies.loginParams || null;
$cookies.loginDestination = null;
$cookies.loginParams = null;
$state.transitionTo(loginDestination, loginParams);

Posted in Angular, Development, Uncategorized, Web Development | Tagged , , | Leave a comment

Path Shortener

I’ve recently been looking at some of the new web development tools like NodeJS, NPM, Yeoman and others. I have a blog post coming on that. However, I’ve discovered a problem, and I’m not the first.

I used Yeoman to create an Angular project. However NPM has an issue on Windows, it creates a folder structure that exceeds Windows maximum length. One of the issues is that you can’t delete the folder structure without a lot of manual effort.

After the second time of having to do this by hand, my developer’s laziness kicked in and I automated it.

I’ve written a little Windows Form app which recurses a folder structure and renames each folder to a single character. This should shorten the path enough to be able to delete it.

The code is on GitHub

Posted in Development | Tagged , , | Leave a comment

Javascript error in *.min.js.map files

I’ve been getting an JavaScript error:

JavaScript critical error at line 2, column 14 in http://localhost:53885/Scripts/jquery.unobtrusive-ajax.min.js.map\n\nSCRIPT1004: Expected ‘;’

This has happened in a few different files, I tried regenerating the *.min.js and *.min.js.map files, but it just wouldn’t go away.

Eventually with a bit of searching Ifound the cause of the problem, Asp.Net MVC Bundles.

Our code was:

bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
            "~/Scripts/jquery.unobtrusive*",
            "~/Scripts/jquery.validate*"
            ));

and we changed it to:

bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
            "~/Scripts/jquery.unobtrusive-{version}.js",
            "~/Scripts/jquery.validate-{version}.js"
            ));

This stops the bundle loading the map files.

Posted in Development, Web Development | Tagged , , | Leave a comment

Excel VBA merging rows

I’ve been given an excel spread sheet full of data to be loaded into a new table, however rather than being send as a multi column table the data was split into multiple key value pairs.

A simple bit of copy and pasting got it all into a multi column table, but left thousands of duplicate rows. The simplest way to fix it was a quick bit of VBA to merge the rows and remove the duplicates, before importing it into SQL. Example Code below:

Sub MergeRows()
Dim r As Long
Dim p As Long
Dim duplicates As Long
Dim keyCell As Variant
Dim rng As Range
Dim col_key As Long
Dim col_title As Long
Dim col_author As Long
Dim col_publisher As Long
Dim col_price As Long
' set columns
col_key = 1
col_title = 2
col_author = 3
col_publisher = 4
col_price = 5

On Error GoTo EndMacro

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' Select Cell A1 and set range to table
Cells(1, 1).Select
Set rng = Application.Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(ActiveCell.Column))

Application.StatusBar = "Processing Row: " + Format(rng.Row, "#,##0")

' initialise duplicate count
duplicates = 0

' step from end of range to the start
For r = rng.Rows.Count To 2 Step -1

If r Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " + Format(r, "#,##0")
End If

' get key value from current row
keyCell = rng.Cells(r, col_key)

' set previous row
p = r - 1

' check if key value = key value on previous row
If rng.Cells(p, col_key) = keyCell Then
' keys match
duplicates = duplicates + 1

' check each column and merge cell to previous row if previous row/cell is blank
If (IsEmpty(rng.Cells(p, col_title)) And Not IsEmpty(rng.Cells(r, col_title))) Then
rng.Cells(p, col_title) = rng.Cells(r, col_title)
End If

If (IsEmpty(rng.Cells(p, col_author)) And Not IsEmpty(rng.Cells(r, col_author))) Then
rng.Cells(p, col_author) = rng.Cells(r, col_author)
End If

If (IsEmpty(rng.Cells(p, col_publisher)) And Not IsEmpty(rng.Cells(r, col_publisher))) Then
rng.Cells(p, col_publisher) = rng.Cells(r, col_publisher)
End If

If (IsEmpty(rng.Cells(p, col_price)) And Not IsEmpty(rng.Cells(r, col_price))) Then
rng.Cells(p, col_price) = rng.Cells(r, col_price)
End If

'delete current row
rng.Rows(r).EntireRow.Delete
End If
Next r

EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = "Duplicates merged: " + Format(duplicates, "#,##0")
End Sub
Posted in Development | Tagged , , | Leave a comment

Facebook Spam

Facebook are filling my phone app with so many updated from pages I haven’t liked, that the app is becoming useless. They obviously know I’m a software developer, but this is terrible spamming. So much that I don’t see updates that I actually want to see from friends and pages that I have liked.

Today’s updates from unliked pages:
O’Reilly Media
opensource.com
at&t developer program *13
appCoda
Atmel Corporation
Smashing Magazine * 4
Lean startup circle
OMG! Chrome * 2
The Hacker News * 4
SQL Server Geeks *
Media Temple
SQL Authority

Posted in Uncategorized | Tagged | Leave a comment