Basic steps to create a new #SQLSERVER database account

Once or twice a year I need to create an account for a SQL Server database that utilizes database authentication.  And every time I have to do this I end up spending too much time searching for the correct steps.  So this post is to help keep me from forgetting what to do for the rest of my career.

In the example below, the database we want to create an account for is [TestDatabase].  The login that will be used for authentication is DbLoginAcct.  And the user that will grant the login access to [TestDatabase] and the [dbo] schema is DbUserAcct.

USE [TestDatabase]
CREATE LOGIN [DbLoginAcct] WITH PASSWORD=N'test', DEFAULT_DATABASE=[TestDatabase]
GO
CREATE USER [DbUserAcct] FOR LOGIN [DbLoginAcct]
GO
ALTER USER [DbUserAcct] WITH DEFAULT_SCHEMA=[dbo]
GO
-- We want this user to have full control of the database so make it a db_owner
ALTER ROLE [db_owner] ADD MEMBER [DbUserAcct]
GO

 

After running the script we can log into the database by using the DbLoginAcct credentials.  If this is the first account on the server using database authentication you may run into issues when authenticating.  To fix these issues take a look at  How to Fix Microsoft SQL Server Login Failed Error 18456?

Finally, two things worth noting are the difference between the LOGIN and USER objects created as part of the script.  As it relates to the server, the LOGIN is used for authentication and the USER is used for authorization.  In other words, the LOGIN gets you into the server while the USER gets you access to the specific database.

Removing #MVC Form #ModelState Errors when the form data model can’t be altered #csharp

When working on .NET MVC projects you may run into a situation where you need to override the state reported by the ModelState object.  In case you aren’t familiar with what the ModelState object is, in the case of MVC controllers the ModelState object provides details on whether the data in a form sent to the controller passes all model defined attribute validation checks.  A call to ModelState.IsValid returns a boolean value to indicate the state, true for all checks passed, false for one or more errors present.  Further details on the reasons why the model is not valid can be retrieved from the ModelState.Errors collection.  In some cases it may be necessary to remove some validation errors from the ModelState before calling ModelState.IsValid.  

I recently had to do this when a complex form required a major overhaul.  Ideally we would have built new form data models but constraints on the project didn’t permit the investment.  Instead a function was created that would remove validation errors for the few form elements causing validation issues.

The function wasn’t complex.  For our needs we wanted to remove all errors against an element that contained a given name.  This was because our form would contain input elements with names like these that created a natural hierarchy and grouping:  Car.Door.Front.Passenger.Color and Car.Body.Windshield.Size.  To do this we’d check each ModelState Key to see if it contained the element name of interest.  If it did then the ModelState.Values object at the same index of the found key would have its Errors collection cleared.

public void RemoveModelStateErrors(string elementName) {
    for(var idx = 0; idx < ModelState.Keys.Count; idx++) {
        if (ModelState.Keys.ElementAt(i).Contains(elementName)
            && ModelState.Values.ElementAt(i).Errors.Count > 0) { 
            ModelState.Values.ElementAt(i).Errors.Clear();
        }
    }
}

The function could be simplified if you always know the exact element name or it could be refactored to handle a list of element names.  Either way once the function is called, any errors related to the elementName would be removed and the ModelState.IsValid call will be updated accordingly.  If the only errors were related to the elements that were just removed then it would return true for the valid model.

Setting up #TypeScript and Runtime Compilation in #VS2019 for #ASPNETCore

Last week I started a new web project to test out ASP.NET Core and Visual Studio 2019.  With all of the changes between the IDE, project configuration, and MVC boilerplate code I figured waiting until work and life slowed down a bit would be ideal.  With the end of the year near the slowdown has finally happened.

The boilerplate code setup hasn’t been difficult, and as expected, well documented.  At this point in the project I’ve setup all references to data access classes outside of the main project to utilize dependency injection.  .NET Core makes this extremely simple I and recommend all developers who are hesitant to use DI to take a look at Microsoft’s documentation.

Two areas that stumped me for a bit were areas that were relatively simple in Visual Studio 2017.  The first one was getting TypeScript to compile and build the mappings properly for debugging.  Prior to .NET Core a tsconfig.json file in the project’s Scripts directory was all that was needed.  However, ASP.NET MVC projects in .NET Core have a different setup.  The guidance is to create a scripts directory outside of the wwwroot directory in the project to hold the TypeScript files.  In order to get the JavaScript code that is generated from those files to be used in the deployed site you then need to copy the JavaScript, TypeScript, and Map files over to your JavaScript directory within wwwroot.  From what I have read, to do this you need to perform a few steps.  They are pretty basic and I encourage you to read the article on TypeScriptLang.org as it has more details on the steps needed.  What follows are the two files I had to play with to get the mappings working.

The first file to add to your project is tsconfig.json.  You can place the file at the root of your project or in the main folder holding your TypeScript files.  For the purposes of this post I’ll be putting the file in the root of my project.

tsconfig.json

{
  "compileOnSave": true,
  "compilerOptions": {
    "noImplicitAny": false,
    "noEmitOnError": true,
    "removeComments": false,
    "sourceMap": true, // Generate the source Map file connecting JS to TS
    "target": "es5",
    "typeRoots": [ // Point to NPM for typings
      "node_modules/@types"
    ]
  },
  "exclude": [
    "node_modules",  // Folder to ignore
    "wwwroot" // Folder to ignore
  ],
  "include": [
    "scripts" // The folder to look for TypeScript files
  ]
}

Once this configuration file is in place any build launched will fire off the TypeScript compiler and generate the JavaScript and Map files.

The next piece of the puzzle is getting the generated files into the correct directory in the wwwroot folder of the project.  To accomplish this another file needs to be added to the project in order to use gulp.  The gulpfile.js will have a simple job, delete the old JavaScript directory and copy over the TypeScript, JavaScript, and Map files to the wwwroot directory.

gulpfile.js

var gulp = require('gulp');
var del = require('del');

var paths = {
    scripts: ['scripts/app/**/*.js', 'scripts/app/**/*.ts', 'scripts/app/**/*.map'],
};

gulp.task('clean', function () {
    return del(['wwwroot/js/**/*.js', 'wwwroot/js/**/*.map', 'wwwroot/js/**/*.ts']);
});

gulp.task('default', function () {
    gulp.src(paths.scripts).pipe(gulp.dest('wwwroot/js'))
});

Once those files were in place the next battle was getting my project to rebuild and run any updates made while debugging the site.  I originally thought the problem resided with the IDE configuration.  Turns out this isn’t the case and what was needed were changes at the Project level and in the code.

The steps are all spelled out by Microsoft in Razor file compilation in ASP.NET Core.  To help with the code piece of the update take a look below.  In the Startup.cs file the constructor and ConfigureServices sections of code will need to be updated in a similar fashion.

Startup.cs

private IWebHostEnvironment _env { get; set; }

public IConfiguration Configuration { get; }

public Startup(IConfiguration configuration, IWebHostEnvironment env)
{
    Configuration = configuration;
    _env = env;
}

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
    IMvcBuilder builder = services.AddRazorPages();
#if DEBUG
    if (_env.IsDevelopment())
    {
        builder.AddRazorRuntimeCompilation();
    }
#endif
  ...
}

Once these changes were finally realized my project was back in a productive development state.  Visual Studio 2019 does have TypeScript compilation settings that can be set in the project Properties that could be used instead of the tsconfig.json file.  And it might be possible to not use the gulp task by having your TypeScript files within the wwwroot/js folder.   I’ve shied away from this setup for the time being since multiple articles have emphasized keeping the TypeScript code outside of wwwroot and using the tsconfig.json file makes the project more automated build friendly.

 

Entity Framework returning partial objects?

There have been a few times when using Entity Framework that a LINQ query seems to return a partial object.  In my own experience the problem appears when the when querying for data that was just updated or inserted into the database.  When retrieving the new entries the first query for the data returns a partial object, or in some cases the old data, while the second query, or a query executed shortly afterwards, will result in the full object.

The issue that we’re running into is that the DbContext hasn’t had time to refresh its own model of the data.  Any foreign-key references might show correctly but the object that would be referenced will show up as null.  If you were to query the database directly you won’t have the same issue.  All foreign keys and the associated data are all set immediately after the UPDATE or INSERT command is completed.  To handle these situations in Entity Framework there are a few options.

One option is to create a new instance of DbContext each time you are querying for the data.  The new instance will have no recolection of the previous state of the database and will only retreive the latest instance.

Another option is to use the .AsNoStracking() option in your query.  This is the method I’ve used most as it allows me to specify which queries I want to grab the latest data out of the database with the least amount of overhead.  The syntax is clean and doesn’t require special configurations.

var result = (from color in _context.Colors.AsNoTracking()
              where color.ColorId = 44
              select color).FirstOrDefault();

The above query is a basic example of retrieving the latest instance of the Color object based on the data within the database.  Any Foreign Keys linking the Color table to other tables will also be retrieved from the database.

When working on a new project or a database that is relatively small you might not see this problem right away.  As the data becomes more complex or the database gets larger the problem will most-likely crop up.  To keep from tracking down a tricky bug pick a way to solve the problem early and apply it consistantly.

Nuget and .NET Standard

I just pushed out my first production ready library, RandomSelection, to NuGet.  I’ve been meaning to publish it for over a year but never got around to it.  I honestly thought the process was going to be more involved but in the end it was pretty simple.  The library was originally written in .NET 4.X but before I published it I wanted to move it over to .NET Standard.  The motivation was to enable its use across a wider range of projects.  To make the change I followed three steps.

First, created a new project in Visual Studio 2019 targeting the .NET Standard Library project template.

Second, copied over the classes and project structure from the original project over to the new one.

Third, updated my tests since the library I was using had made a change that impacted my code.  Also made small updates to the way I was throwing exceptions so that the message received by the caller would be clear.

That was it.  Then to build the NuGet package I used the VS 2019 project properties view to set the parameter values used by NuGet.  For a detailed set of instructions to follow take a look at Microsoft’s Create and publish a package using Visual Studio.  No need to create your own nuspec file or sign it.  Give it a shot.  It was amazingly easy.

Regex String Replace Rescue

I ran into an issue recently where I needed to fix two fields in a SQL statement.  Wasn’t a big deal except there were over 8,000 SQL statements requiring the change.  Would have loved to do a simple Replace String in Notepad++ but each value was unique.  The job that needed to be done was change a value from a string with single quotes around it to be a decimal number.  Simple enough, a value like ‘12.34’ needs to be come 12.34 for the SQL statement to be valid.

Anytime I working with random text files I tend to work in Notepad++.  I like the user experience it presents, it is light-weight, and fast.  Within the application you can perform the standard search and replace or you can use regular expressions to search the file.  I knew my search would have to involve regular expressions but what I wasn’t sure was whether I could replace the text with the cleaned version of itself.  To my surprise, and due to regex ignorance, this was 100% possible.  By taking advantage of a feature called Capture Groups a user can specify a search pattern as well as the specific part of the pattern you want to keep.  Then, for the replace value in the search and replace window you set the replacement value as \1 which represents the first, and in my case only, capture group.

Notepad++ Regular expression string replace

What this all came down to was using the regular expression ‘(d\d\.d\d\)’ created a search term that looked for a two digit number with a decimal and two more digits after the decimal surrounded by single quotes.  The digits and decimal will be saved as part of capture group one.  Now I know there were probably much better ways to create a regex search term but this worked so I went with it.  Thank you to StackOverflow for getting me squared away with this problem.

Developer Podcasts

Back in 2013 I changed from developing embedded systems in Linux with Java and C++ to developing enterprise applications on Microsoft Windows in .NET.  The new development stack turned me into a fish out of water looking for some direction.  I had 30 to 40 minute commute to work so I looked to fill it with podcasts that could give that direction.  Over the the last few years I’ve narrowed them down to a half dozen that I subscribe to and recommend to other developers.

 

One thing I can’t recommend enough is to explore areas outside of your core area of interest.  Broaden your knowledge with podcasts like American History which go out and have a different lecture from across American universities each week.

Don’t forget to remove the end forward slash when enabling CORS

You ever run into an issue with your code, spend way to long looking at it and not get anywhere?  Especially one that you’ve solved before but having a lapse in memory.  That was me all too recently.  I was setting up an app that consisted of three projects in a solution.  One project for the API, one for the DTO, and another for the MVC.  The problem with this setup is that any Javascript call from the MVC project to the API will be blocked when the data comes back due to the browser enforcing same-origin policy.  Microsoft has a great write-up on how to Enable CORS within an ASP.NET Web API 2 project.  For your own sanity please make sure you go the whole through section.  I mean, the whole thing, all the way to the end, because at the end of the section is a rather important fact.  When you are defining the EnableCors attribute on the API controller you need to make sure that the forward slash at the end of the origins parameter is removed.

Good: [EnableCors(origins: "http://localhost:49332", headers:"*", methods:"*")]

Bad: [EnableCors(origins: "http://localhost:49332/", headers:"*", methods:"*")]

That is it.  That single forward slash tripped me up.  The error written to the web DevTools console was
Access to XMLHttpRequest at 'http://localhost:49342/api/users/name/smith/' from origin 'http://localhost:49332' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.

Hope this helps you waste a little less time.

Generating Entity Framework classes for Database First project

Not all projects, or development teams, use Code First with Entity Framework for building and maintaining their database.  There are plenty of reasons for doing it either way but that discussion is outside of the scope of this post.  What this post is going to focus on is generating your EF classes in a Database First environment.

Microsoft has plenty of great documentation for developers and one such post that I used recently was Getting Started with EF Core on ASP.NET Core with an Existing Database.  My setup is Visual Studio 2017 Community Edition and a .NET Core 2.1 project structure.  The database is running on a free, local instance of SQL Server 2017 and consists of a handful of tables.  To generate the classes I utilized the Package Manager Console (PMC) in Visual Studio and the Scaffold-DbContext command.

Scaffold-DbContext "Data Source=localhost\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=True;" Microsoft.EntityFrameworkCore.SqlServer -outputDir Repository -Verbose

The connection string is pretty basic.  I’m telling it to connect to a database on my machine called MyDatabase and use my Windows Credentials for authentication.  The files generated should go into the directory called Repository in my project.  And for help with debugging any problems I include the -Verbose parameter.

Upon completion all generated files were open in my VS instance for inspection and located in the folder I set as the output destination.  If this didn’t happend and you have an error message make sure that your project builds on its own.  If the project doesn’t build then the Scaffold-DbContext won’t be able to generate the files.  One thing to check before you submit your code to any repository is that in your Context.cs class the connection string used for Scaffold-DbContext is hard-coded into the file.  If the code is going to a public repository you’ll want to make sure this line is removed.

Using AdMobFree plugin with Ionic 3

Recently I updated one of my applications to use the Cordova Plugin AdMobFree to show advertisements in my Ionic app to generate revenue. There are several plugins available to Ionic developers to use for showing ads from AdMob but this one provided the core features I needed without a bunch of bells and whistles I didn’t. This post will go over the steps I took in order to get the plugin to work in my project. If you run into any issues let me know and I’ll see if I missed a step.

To start, let me be clear that this is geared to projects developed utilizing the Ionic 3 framework. It should work for Ionic 2 applications as well but I would not base an Ionic 4 application off of this code given all the changes that have been made for that framework.

Installation

$ ionic cordova plugin add cordova-plugin-admob-free --save --variable ADMOB_APP_ID="<YOUR_ADMOB_APP_ID_AS_FOUND_IN_ADMOB>"
$npm install --save @ionic-native/admob-free@4

Make sure you include your APP ID from AdMob. You can get the value by going to AdMob then clicking on the application under Apps and going to App Settings.

In your config.xml file you should see a section for cordova-plugin-admob-free that has the ADMOB_APP_ID variable defined with the value you passed in during the installation. If not then run the remove command for the plugin and try adding it again.  

One thing worth remembering is that if you run into build issues where errors are reported about the Android version or support libraries used then you’ll want to install the Cordova Android Support Gradle Release npm package.  

When it comes to installing your Android platform I recommend installing version 6.3.0. 

$ ionic cordova platform add android@6.3.0

With that you should have all of the packages and configuration completed.  It is now time to dive into the code.

Code

The first changes to your code have to be made in the app.modules.ts file.  You’ll need to import the plugin and add it to the providers section.

import { AdMobFree, AdMobFreeBannerConfig } from '@ionic-native/admob-free';

@NgModule({
declarations: [...],
imports: [...],
bootstrap: [...],
entryComponents: [...],
providers: [...
AdMobFree,
....]
})

On the page you want to show the ads you’ll only need to make changes to the underlying TypeScript file.  You’ll first want to import the necessary packages.

import { AdMobFree, AdMobFreeBannerConfig } from '@ionic-native/admob-free';
In the constructor include the AdMobFree declaration.  
 
constructor(...,
    private admob:  AdMobFree) {}
 
private createBanner() {
let logger:LoggingProvider=this.logProvider;

try {
let bannerConfig:any= {
id: <android banner id>,
autoShow: true,
isTesting: true
};

this.admob.banner.config(bannerConfig);
this.admob.banner.prepare()
.then(() => {
//We have a banner :)
}).catch((error) => {
// Handle the error case
});
});
}
catch (exception) {
// Something bad happened
}
}

And that is it.  You should have a basic banner displaying on the application.  Once you are ready to go to production you’ll want to change the isTesting parameter to false so real advertisements come through.

If you want to give the option for your users to go advertisement free there is an easy method call to remove the banner.

this.admob.banner.remove(); 

When I get some time I’ll try to put up an example project or put in a pull request for a demo on the AdMobFree repo.  Until then, let me know if I missed anything and I’ll update the instructions.