SQL Server: Finding a table in multiple databases.

Just a quick one – I needed to find which databases held a particular table:

EXEC sp_msforeachdb 'use [?] SELECT ''?'' as [datbaseName],SCHEMA_NAME(schema_id) as [schemaName], name as [tableName]
FROM sys.tables where name = ''MY_TABLE'''

Just replace MY_TABLE with the name of the table you’re looking for.

Advertisements
Posted in Development | Tagged | Leave a comment

Encrypting and Decrypting Web.Config sections

Another little personal reminder, to save a bigger google search next time:

Encrypting web Config

  1. Run CMD in admin mode
  2. Add .net folder to path, (if not already there): set path=%PATH%;C:\Windows\Microsoft.NET\Framework\v4.0.30319
  3. copy web.config web.config.backup
  4. aspnet_regiis -pef appSettings .

Decrypting web config

  1. aspnet_regiis -pdf appSettings .
Posted in Uncategorized | Leave a comment

Setting up Development Configurations in Visual Studio

Working in a medium sized development team, we have found a source of frustration is when one developer checks in a change to the web.config that they’ve been using for testing that affects everyone else.

While looking at ways to stop this we discovered that you can have the web.config load settings in from other files.

ConfigSource

Some sections of the web.config allow you to add a ConfigSource attribute. for instance the connectionStrings section.  Before we would have several connectionStrings sections, one for each environment, but commented out.

Now, by adding the ConfigSource attribute, we can save the different connectionStrings sections to separate files, e.g. Dev, Dev2, etc., and simply change which one we point to. It’s much cleaner and more readable.

 <connectionStrings configSource="ConfigFiles\Dev_ConnectionStrings.config" />

AppSettings

AppSettings doesn’t allow you to use the ConfigSource, however, it does allow you to use the file attribute. The difference is that the file attribute brings in settings from another file that override the current settings.

<appSettings file="LocalSettings\DeveloperAppSettings.config">
   …
   …
</appSettings>

Also, the file is optional, so that if it doesn’t exist, the project will still run.

Example web.config extract

<connectionStrings configSource="ConfigFiles\DEV_ConnectionStrings.config" /> 
<appSettings file="LocalSettings\DeveloperAppSettings.config">
   <add key=”settingToOverride” value=”1”/> 
</appSettings>

ConnectionStrings.config

<connectionStrings>
  <add name="Database1Connection" connectionString="data source=Server1;initial catalog=Database1;user id=Database1User;password=password;" providerName="System.Data.SqlClient" />
  <add name="Database2Connection" connectionString="data source=Server2;initial catalog=Database2;user id=Database2User;password=password;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />
</connectionStrings>

DeveloperAppSettings.Config

<appSettings>
   <add key=”settingToOverride” value=”2”/>
</appSettings>

Excluding the local files from TFS

First, the solution or project should have a “.tfignore” file created.

Example .tfignore file

###############################
# Ignore developer config files
\LocalSettings
# ignore logs
\logs
###############################
  • Check this file in.
  • Close and reopen the solution – This is important, as it seems to initialise the .tfIgnore.
  • Create the folder LocalSettings
  • Add the ConnectionStrings.config file
  • Add the DeveloperAppSettings.config file
    • Under Properties, set the Build Action to NONE
  • In Source Control Explorer, undo the changes to the DeveloperAppSettings.config file.

See also:

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

Automatically building NuGet packages for your project–updated

As a follow on from my last post on this subject, I’ve come across a simplified way of doing this.

  1. Build your .nuspec file as usual. (use NuGet spec in a CMD window in the project folder to generate the basic file).
  2. Update the AssemblyInfo.cs file with a description and a company, or the NuGet build will throw errors.
  3. Edit the project file:

Add these two lines to the first <PropertyGroup>:

<RestorePackages>true</RestorePackages>
<BuildPackage>true</BuildPackage>

Add these lines at the end for the file:

<ImportProject=”$(SolutionDir)\.nuget\NuGet.targets”Condition=”Exists(‘$(SolutionDir)\.nuget\NuGet.targets’)”/><TargetName=”EnsureNuGetPackageBuildImports”BeforeTargets=”PrepareForBuild”>    <PropertyGroup>
      <ErrorText>This project references NuGet package(s) that are missing on this computer. Enable NuGet Package Restore to download them. For more information, see http://go.microsoft.com/fwlink/?LinkID=322105. The missing file is {0}.
      </ErrorText>
   </PropertyGroup>
      <ErrorCondition=”!Exists(‘$(SolutionDir)\.nuget\NuGet.targets’)”Text=”$([System.String]::Format(‘$(ErrorText)’, ‘$(SolutionDir)\.nuget\NuGet.targets’))”/></Target>

Posted in Development | Tagged , | Leave a comment

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 , | 1 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