Xamarin Forms Storing some Json in a Standard Library

In one of my Xamarin forms app I wanted to include some default data for the app in Json format.

Rather than create a web service to load the data on the first run I went ahead and created a Json file to include the default data.

[
{"Question":"Name two key components that make up Sitecore XP","Answer":"Experience CMS, and marketing platform"}

etc...


I added the Json file to my Xamarin Forms standard library and set its build action to embedded resource

To extract the data I used a function like this.

        public ObservableCollection<QuestionPair> GetQuestions()
        {
            var assembly = typeof(DataService).GetTypeInfo().Assembly;
            Stream stream = assembly.GetManifestResourceStream("SitecoreFlashCards.questions.json");
            StreamReader streamReader = new StreamReader(stream);
            string json = streamReader.ReadToEnd();
            ObservableCollection<QuestionPair> questions = new ObservableCollection<QuestionPair>();
            var list = Newtonsoft.Json.JsonConvert.DeserializeObject<List<QuestionPair>>(json);
            foreach(var item in list)
            {
                questions.Add(item);
            }

            return questions;
        }

First I created a stream so I can access the Json file in the standard library.  The I used a stream reader to get the text from the file and deserialize it with Json.net

The path to the resource is the class library default namespace and the path to the file and file name in the dll.

Replace DataService with the name of the class you are trying to get the Json file from.

Using GitHub with Visual Studio for the Mac

First lets create a new Repository on GitHub .

For this demo I created a public repository that has a readme, and a Visual Studio Git Ignore file.

Now I created a simple .Net Core Console app to check into GitHub.  I checked use git for version control.

Once I created the app I built it to make sure it runs.   Now the first thing to do is create an initial commit to the local git repository.  In the version control menu select Review and Commit.

In Git Hub open the Repository you created. Press the clone or download button and copy the url for the repository.

In Visual Studio Version Control Menu select Manage Branches and Remotes

Add a new Remote source with the url from the GitHub website

I made a minor code change and Selected Review and Commit changes

In the dialog I selected Push to remote.  First time you connect to GitHub it will ask for you credentials

Once you push your changes you should see it in GitHub.  Make sure before you push your changes you update the solution from GitHub.

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