Error with Cert when starting an Asp.Net core website on the Mac

On my Mac I tried to run a Asp.Net core web site and got an error when starting the web site about ssl cert was invalid.

Adding the certificate to the Trusted Root Certficates store failed with the following error: Failed with a critical error.

The sites were working a few days before so I tried this command line.

     

dotnet dev-certs https --trust

Got a response A valid HTTPS certificate is already present.

Ran app again and still got same error.

The way I fixed it on my Mac was to open the KeyChain Access app and deleted the localhost certificate

Ran 

dotnet dev-certs https --trust 

After this I was able to debug my asp.net core apps in vs for Mac 2019 and Rider

Using Dapper with Asp.net Core website on Mac

In this blog post we will create an Asp.Net Core 2.0 website with Visual Studio for the Mac and use Dapper to access a SQLite database.  Dapper is micro ORM written by the StackOverflow team.  It is meant to send the data from database to C# class.

To start create a new Asp.Net Core MVC app.

Now we need to add 2 NuGet packages.  Microsoft.Data.SQLite and Dapper

Next we are going to create an Interface for our DataAccess class.  I called it IDataAccess

    public interface IDataAccess
    {
        void InsertName(string Name);

        Name GetName(int id);

        void DeleteName(int Id);

        void UpdateName(int Id, string Name);
        
        List<Name> GetNames();
    }


Here is the class Name

    public class Name
    {
        public int Id { get; set; }

        public string name { get; set; }
    }


Now we are going to create an Class that Implements the IDataAccess interface

    public class SQLIteDataAccess : IDataAccess
    {
        private const string fileName = "MyDatabase.db";

        private IDbConnection connection = null;

        public SQLIteDataAccess()
        {
            string connectionString = $"FileName={fileName}";
            connection = new SqliteConnection(connectionString);
            if(!File.Exists(fileName))
            {
                FileStream fileStream = File.Create(fileName);
                fileStream.Close();

                try
                {
                    connection.Open();
                    connection.Execute("Create Table Names(Id int Primary Key AUTOINCREMENT, Name Text) ");
                }
                catch (Exception ex)
                {
                    Trace.WriteLine(ex.Message);
                }
                finally
                {
                    connection.Close();
                }

            }
        }

        public void DeleteName(int Id)
        {
            try
            {
                connection.Open();
                connection.Execute("Delete from Names Where Id = @Id", new {Id = Id});
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public System.Collections.Generic.List<Name> GetNames()
        {
            List<Name> results = null;
            try
            {
                connection.Open();
                results = connection.Query<Name>("Select * from Names").ToList();
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
            }
            finally
            {
                connection.Close();
            }
            return results;
        }

        public void InsertName(string Name)
        {
            try
            {
                connection.Open();
                connection.Execute("Insert into Names (Name) value (@Name)", new { Name = Name });
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public void UpdateName(int Id, string Name)
        {
            try
            {
                connection.Open();
                connection.Execute("Update Names set Name = @Name Where Id = @Id", 
                                   new { Name = Name, Id = Id });
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }


Ok lets talk about the Constructor before we move on to the Dapper code.

       public SQLIteDataAccess()
        {
            string connectionString = $"FileName={fileName}";
            connection = new SqliteConnection(connectionString);
            if(!File.Exists(fileName))
            {
                FileStream fileStream = File.Create(fileName);
                fileStream.Close();

                try
                {
                    connection.Open();
                    connection.Execute("Create Table Names(Id INTEGER Primary Key AUTOINCREMENT, Name Text) ");                }
                catch (Exception ex)
                {
                    Trace.WriteLine(ex.Message);
                }
                finally
                {
                    connection.Close();
                }

            }
        }

First thing I do is look and see if the database was created.  If it was not I am creating creating a new file with the database name I am looking for.  I store the file stream in a variable so I can close it.  If you don't do this the file will be locked and you won't be able to execute scripts on the database.

Next I create a connection to the database and run a create table script.  

connection.Execute("Create Table Names(Id INTEGER Primary Key AUTOINCREMENT, Name Text) ");


Now lets look at the dapper code.  It adds some methods to the connection that allows us to Execute a script or Query the database.

For example this command Queries the Database for all the records in the names table

results = connection.Query<Name>("Select * from Names").ToList();

To execute a command on the database with some parameters you do it like this

   connection.Execute("Insert into Names (Name) value (@Name)", new { Name = Name });

Please do not pass user input directly in to a database query I would use a parameter to reduce the chance of SQL injection



Finally we are going to add a controller to expose these methods via the web api

    [Route("api/[controller]")]
    public class SQLiteController : Controller
    {
        IDataAccess dataAccess;

        public SQLiteController(IDataAccess data)
        {
            dataAccess = data;
        }
        

        // GET: api/values
        [HttpGet]
        public List<Name> Get()
        {
            return dataAccess.GetNames();
        }

        // GET api/values/5
        [HttpGet("{id}")]
        public Name Get(int id)
        {
            return dataAccess.GetName(id);
        }

        // POST api/values
        [HttpPost]
        public void Post([FromBody]string value)
        {
            dataAccess.InsertName(value);
        }

        // PUT api/values/5
        [HttpPut("{id}")]
        public void Put(int id, [FromBody]string value)
        {
            dataAccess.UpdateName(id, value);
        }

        // DELETE api/values/5
        [HttpDelete("{id}")]
        public void Delete(int id)
        {
            dataAccess.DeleteName(id);
        }
    }

In the constructor I am injecting an Instance of the IDataAccess so I can use it in the controller.

       IDataAccess dataAccess;

        public SQLiteController(IDataAccess data)
        {
            dataAccess = data;
        }

To register the IDataAccess interface for dependency injection we do it in the Startup class

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
            services.AddTransient<IDataAccess, SQLIteDataAccess>();
        }


Since I am doing this on the Mac I am going to use curl in the terminal window to test the service

Lets insert a name in the database

kentucker$ curl -H "Content-Type: application/json" -X POST -d '"Bill Gates"' http://localhost:19266/api/SQLite

List the Names

curl http://localhost:19266/api/SQLite

In this post I showed how to use Dapper with Asp.Net core. When I develop software that interacts with a database I like to develop using a local database.  Since SQL Server my server data base of choice is not available for the Mac. I decided to use SQLite for local development.  It is possible to use host SQL Server on a Mac in docker container but since it uses a lot of resources I figured SQLite would work.    In my next post I will show how to refactor the code to allow the class to allow the code the connect to either a SQL Server of SQLite database.


The source code is available on GITHUB


https://github.com/vb2ae/AspNetCoreSQLite

Asp.Net Core 2 Configuration Values

In this blog post I will show you how to access the site settings in an asp.net core 2.0 website.  I am using Visual Studio for the Mac to doing the coding but it should work the same in the windows version of Visual Studio.

Configuration values are store in the file appsettings.json.   It is a json based configuration file.  An example file could look something like this.

{
  "NumberOfItemsToShow": 20,
  "Title": "Demo Application",
  "Topics": ["Asp.net","Asp.net core", ".net core", "Xamarin"],
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Warning"
    }
  }
}

In asp.net you stored your app settings in a file called web.config.  If you wanted different values for different environments you wrote a config transform to change the value for that environment.  In asp.net core you create a appsettings.[environment name].json to overwrite values for the other environment.


To start off we are going to create a class to hold our config values.  I am going to call it SiteSettings.

using System;
using System.Collections.Generic;
namespace SettingsDemo.Models
{
    public class SiteSettings
    {
        public int NumberOfItemsToShow { get; set; }
        public string Title { get; set; }

        public List<string> Topics { get; set; }
    }
}

Now in the file startup.cs we need to update the startup function to get the hosting environment and load the config values.

        private readonly IHostingEnvironment hostingEnvironment;


        public Startup(IHostingEnvironment env, IConfiguration config)
        {
            var builder = new ConfigurationBuilder()
               .SetBasePath(env.ContentRootPath)
               .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
               .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
               .AddEnvironmentVariables(); 
            hostingEnvironment = env;
            Configuration = config;
        }

To load the setting we need to change the Configure Service method.


        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.Configure<SiteSettings>(Configuration);
            services.AddMvc();
        }

Finally we need to use dependency injection to get access to them in the controller we need to use them in.


        private SiteSettings siteSettings;

        public HomeController(IOptions<SiteSettings> settings)
        {
            siteSettings = settings.Value;
            
        }

Hope this helps

You can find the sample code on GitHub

https://github.com/vb2ae/AspNetCoreConfiguration

ASP.Net core 2 caching

With classic Asp.Net you used the HtppContext in the System.Web namespace for caching. Since Asp.Net Core was created to be cross platform caching is done differently.

I am using Visual Studio for Mac to do this but it will work the same in Visual Studio 2017 (for windows).

Lets create a new Asp.Net core application

Once the app is created go to the startup.cs class. and change the ConfigureServices method to this

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
            services.AddMemoryCache();
        }  

Now to use this we need to get access to the IMemoryCache in the controllers constructor.  In the demo I will use the about page in the project created

        IMemoryCache caching= null;


        public HomeController(IMemoryCache cache)
        {
            caching = cache;
        }

To use it. I am just going to cache the DateTime

        public IActionResult About()
        {
            ViewData["Message"] = "Your application description page.";
            DateTime currentTime;
            if (!caching.TryGetValue<DateTime>("currentTime", out currentTime))
            {
                currentTime = DateTime.Now;
                caching.Set<DateTime>("currentTime", currentTime, DateTimeOffset.Now.AddMinutes(10));
            }
        
                
            ViewBag.Time = currentTime;

            return View();
        }

To cache something use the Set method.  I would recommend using the version that allow you to specify the type of object.

When setting the cache you pass in the key, what you want cached and optionally how long you want it cached.  I set it to 10 minutes in this example.

Getting items from the cache you use TryGetValue it will return true if the value was found and it passes it out in an out parameter.

If you need to remove something use the cache Remove method where you pass in the key of the object you want to remove.

To show the item is cached I changed the About.cshtml to show the current date time and the cached value

@{
    ViewData["Title"] = "About";
}
<h2>@ViewData["Title"]</h2>
<h3>@ViewData["Message"]</h3>

<p>Use this area to provide additional information.</p>
@DateTime.Now
<br/>
@ViewBag.Time

Please be sure to check out my course on Packt about asp.net core to learn more about asp.net core

https://www.packtpub.com/application-development/building-aspnet-application-visual-studio-2017-video