Browsing:

Tag: C#

Query a database through a C# REST API with Powershell (part 1)

It is probably known that you can query an SQL database in Powershell relatively easy, but wouldn’t it be great to quickly write a REST API in front of the database? So that you can add business logic if you wish? And use Powershell as a REST client? And then be able to code a decent frontend for the API for whatever device?

Let’s get started!
In this series I will first create a WebApi from scratch. Of course, you can also use the templates in Visual Studio, but I prefer to have a bit of knowledge of the code that’s in my project. It’s not that hard and you will end up with a clean code base.

Step 1. Get your dev environment ready

You can use a Vagrant box. If you use this Vagrantfile a install.ps1 script will be copied to your desktop. Run it, grab a coffee or go shopping because we are on Windows and Windows apps can be huge.

Step 2. Getting the VS Project in place

Start Visual Studio
Create a new empty solution:

ice_screenshot_20160508-093224

I named the empty solution BusinessApp (I’m lacking inspiration for a better name).

Then right click the newly made solution in the Solution Explorer (the pane on the right) and click Add and the New Project:

20150508-context

 

 

 

 

 

I named the new Project BusinessApp.Api. If you set your solution up like this you can add more projects as you continue extending the app, for example for an Angular (or whatever framework) frontend, or if you want to separate your datalayer. You can also put your Powershell client modules in a separate project if you wish.

Then open up the Nuget Package Manager Console and install the WebApi dll’s:

Install-Package Microsoft.AspNet.WebApi

Make sure to choose the correct Package source (Microsoft and .NET).

Step 3. Add routing

Add a new folder and name it App_Start.
Create a new class in the folder and name it WebApiConfig.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Http;


namespace BusinessApp.Api
{
    public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {
            config.MapHttpAttributeRoutes();
            GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
            GlobalConfiguration.Configuration.Formatters.Remove(GlobalConfiguration.Configuration.Formatters.XmlFormatter);

            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );
        }
    }
}

In this class we configure that we want our api to return and consume json. Also, we configure our routes to match the controller name, followed by id, wich is optional. E.g http://example.com/api/employees/1 would match a controllername Employees, and it would return employee with id 1.

Step 4. Enable CORS

We need to enable CORS¬†else we won’t be able to consume the api from from another domain outside the domain from which the resource originated. In a production web environment you should configure this very carefully. I will CORS very permissive because I want my code to work.

Install CORS with in Nuget console:

Install-Package Microsoft.AspNet.WebApi.Cors

Then modify the WebApiConfig.cs class as follows:

using System.Web;
using System.Web.Http;
using System.Web.Http.Cors;

namespace BusinessApp.Api
{
    public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {
            var cors = new EnableCorsAttribute("*", "*", "*");
            config.EnableCors(cors);
            config.MapHttpAttributeRoutes();
            GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
            GlobalConfiguration.Configuration.Formatters.Remove(GlobalConfiguration.Configuration.Formatters.XmlFormatter);

Step 5. Add a Controller

  • Create a folder named ‘Controllers’
  • Right click the Controllers folder and click Add and then Controller
  • Click Web API 2 Controller with read/write actions.

ice_screenshot_20160508-092302

I named the Controller Test Controller.

Step 5. Add a Global.asax file

We need to add a Global.asax file to call the WebApiConfig.cs methods at startup.

Right click the solution, click Add, click New Item and search for Global.asax, then Add it.

ice_screenshot_20160508-095951

Modify Global.asax (see the highlighted lines):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Http;
using System.Web.Security;
using System.Web.SessionState;

namespace BusinessApp.Api
{
    public class Global : System.Web.HttpApplication
    {

        protected void Application_Start(object sender, EventArgs e)
        {
            GlobalConfiguration.Configure(WebApiConfig.Register);
        }

 

Step 6. Test the API

Hit F5 and browse to http://localhost:/api/test

ice_screenshot_20160508-100831

And it works. You can also consume the API with Powershell at this point:

((Invoke-WebRequest http://localhost:53601/api/test).content) | ConvertFrom-Json

It should return value1 and value2.

Done! Now let’s query a database. This will be explained in Part 2.


ASP.NET Web API, Angularjs and MongoDb part 5

This is the sequel to this.

You can find the finished application here: https://github.com/jacqinthebox/AddressBook

angular

When I was creating this I was really confused about ‘the double MVC’ setup. The one that comes with ASP.NET and the one that comes with Angularjs.

It felt like what I was doing was bloathed and wrong.

I decided to start again from scratch, but this time with Node as the backend.

node


Getting started with ASP.NET API with MongoLab part 3

This is the sequel to this post
We will be using MongoLab as a backend for our Web Api.

We are using the AddressBook again in this example, but for clarity we’re building it from scratch.

Creating the MongoLab database

Head over to MongoLab and create an account!

  • First create a new database. Make sure you choose the free plan. Name the database ‘addressbook’.
  • Click to create a new user.
  • Add a new collection (equivalent for a table) named persons.

Please take note of the connectionstring:

2013-11-01 08_12_03-MongoLab_ MongoDB-as-a-Service

Setting up the Web Api project in Visual Studio

Fire up Visual Studio 2013 and hit File -> New -> Project.
Choose Empty ASP.NET Web Application with a Web API and name the project AddressBook.

2013-11-01 08_07_48-New ASP.NET Project - AddressBook

We must install the Mongo Csharp Driver:

Install-Package MongoCsharpDriver

Now open App_Start\WebApiConfig.cs and add json formatting to it (see my previous post why):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Http;

namespace AddressBook
{
    public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {
            // Web API configuration and services

            // Web API routes
            config.MapHttpAttributeRoutes();

            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );

//This section you need to add:
            var json = config.Formatters.JsonFormatter;
            json.SerializerSettings.PreserveReferencesHandling = Newtonsoft.Json.PreserveReferencesHandling.Objects;
            config.Formatters.Remove(config.Formatters.XmlFormatter);
        }
    }
}

Connecting to the MongoLab

Let’s not reinvent the wheel if you don’t have to. This article is a great introduction to Mongo and C#. I borrowed their MongoConnectionHandler.

using MongoDB.Driver;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AddressBook
{
    public class MongoConnectionHandler
    {
        public MongoCollection MongoCollection { get; private set; }

 public MongoConnectionHandler()
        {
            const string connectionString = "mongodb://:@ds031108.mongolab.com:31108/addressbook";
            //const string connectionString = "mongodb://localhost:27017";
            //// Get a thread-safe client object by using a connection string
            var mongoClient = new MongoClient(connectionString);

            //// Get a reference to a server object from the Mongo client object
            var mongoServer = mongoClient.GetServer();

            //// Get a reference to the database object 
            //// from the Mongo server object
            const string databaseName = "persons";
            var db = mongoServer.GetDatabase(databaseName);

            //// Get a reference to the collection object from the Mongo database object
            //// The collection name is the type converted to lowercase + "s"
            MongoCollection = db.GetCollection(typeof(T).Name.ToLower() + "s");
        }
    }
}

The Person class

I want the Person_Id to be the same as the document id, so I can retrieve documents easily. I’m not sure though if this is best practice. But it works.

public class Person 
    {
        public Person()
        {
            Person_Id = ObjectId.GenerateNewId().ToString();
        }

        [BsonId]
        public string Person_Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Phone { get; set; }
        public string Email { get; set; }
        public string Blog { get; set; }
        public string Facebook { get; set; }
        public string Twitter { get; set; }
        public string LinkedIn { get; set; }
        public string Googleplus { get; set; }
    }

And here’s the POST method:

public class PersonController : ApiController
    {
        public MongoConnectionHandler collection = new MongoConnectionHandler();

        public HttpResponseMessage Post([FromBody] Person person)
        {
            collection.MongoCollection.Insert(person);
            var response = Request.CreateResponse(HttpStatusCode.Created, person);

          
            string uri = Url.Link("DefaultApi", new { id = person.Person_Id });
            response.Headers.Location = new Uri(uri);
            return response;

        }


    }
      

You can use the API with Fiddler.

2013-11-01 14_33_36-Fiddler Web Debugger

Look at the results it has returned:

2013-11-01 14_35_25-Fiddler Web Debugger

It returned the new URI with the new Id as you can see.

Now let’s check MongoLab, and lo and behold:

2013-11-01 14_39_53-MongoLab_ MongoDB-as-a-Service

Here’s the remainder of the CRUD methods:

        public IEnumerable Get()
        {
            return collection.MongoCollection.FindAll().AsEnumerable();

        }


        public Person Get(string id) {

            //var query = Query.EQ(p => p.Person_Id, id);
            return collection.MongoCollection.FindOne(Query.EQ("_id", id));

        }

        public void Delete(string id)
        {
            collection.MongoCollection.Remove(Query.EQ("_id", id));

        }

        public HttpResponseMessage Put([FromBody] Person person)
        {
            
            var p = collection.MongoCollection.FindOne(Query.EQ("_id", person.Person_Id));

            if (p != null)
            {
                collection.MongoCollection.Save(person);
            }
           
            return Request.CreateResponse(HttpStatusCode.OK, person);
        }

So, that works.
Next time, let’s consume this WebApi with Angularjs.

And here’s all the code: here

I have changed ‘Person’ to ‘Contact’ in the sourcecode


Really really back to basics: Intro to OOP: C# classes. (And intro to Linq to XML as well).

Here’s an intro to OOP in C# for those who are interested. It’s a real world example.
I have a bunch of SQL Servers and I need to execute some SP’s on their databases. The configuration information is stored in an XML file. I want to solve this problem in an OOP way. So we need to create a class and instantiate objects from that class.

Obviously, there should be a class named Database server or something like that, and it has properties (or variables, or fields).

Creating the class

First let’s create a class:

[code lang=”csharp”]
public class DbServer {

}
[/code]

Creating its properties

Then, we will define its properties:

[code lang=”csharp”]
public class DbServer {
private string _name;
private string _instance;
private string _database;
private string _username;
private string _password;
}
[/code]

Why are they private? That is to make sure that these variables can only be manipulated from inside the class.
But what is that good for? These values should be queried, right?
Indeed. That’s why public properties will be exposed to them as well:

[code lang=”csharp”]

public class DbServer {

private string _name;
private string _instance;

public string Name {
get { return _name; }
set { _name = value; }
}

public string Instance{
get { return _instance; }
set { _name = value; }
}
[/code]

Why then, go through all this and not just make the fields public from the start?!
No, this is one of the principles of OOP: Encapsulation. There are some benefits to encapsulation:

  • Preventing unauthorized access to the data
  • Ensuring data integrity through error checking
  • Creating read only properties

Let’s alter the code a bit:

[code lang=”csharp”]
public string Name {
get {
return _name;
}
set {
//should not be empty
if (value.Length < 1 ) {
throw new Exception("Servername cannot be empty");
} else {
_name = value;
}
}
}
[/code]

There, I added a business rule. It would also be nice to add some more regex to the class and throw appropriate exceptions.

here’s how to make the property read-only by omitting the ‘setter’ and for the ‘getter’ to actually fetch the data from a database, or as in this scenario, from an XML file:

[code lang=”csharp”]
public string Instance
{
get
{
_instance = getElement().Element("instance").Value;
return _instance;
}
}
[/code]

Auto-Implemented Properties

In cases when there would be more trivial properties when there is no additional logic required, you can use auto-implemented properties. When you declare a property as shown in the following example, the compiler creates a private, anonymous backing field that can only be accessed through the property’s get and set accessors.

[code lang=”csharp”]

public class DbServer {

public string Name { get; set; }
public string Instance { get; set; }

}
[/code]

In this way, the properties are mutable by other client code.
But what if you would fetch the Instance value from a database or an XML file?

Then the code would be as follows:

[code lang=”csharp”]

public class DbServer {

public string Name { get; private set };

public string Instance
{
get
{
return getElement().Element("instance").Value;
}
}

//the constructor
public DbServer (string name)
{
Name = name;
}
[/code]

Where did the business logic go? In this particular class, there is hardly need for business logic, as it reads values from an XML file. It might be better to separate the business logic in another (partial) class.
The servername (Name) is obligatory, so we set that when instantiating the DbServer object: in the constructor.

The constructor

To find the right SQL server I need to supply the servername. With that servername, we can query the XML file for the rest of the values of the properties.
So, when instantiating the DBserver object, we should provide the servername.
Therefore we need a constructor.

[code language=”csharp”]
public DbServer(string name)
{
Name = name;
}
[/code]

Now we can instantiate a Dbserver instance with a name. Like this:
[code language=”csharp”]
DbServer server = new DbServer("SQLSERVER01");
[/code]

Well.. these are the basics of OOP in C#. Now let’s add a little bonus material. Here is how we actually read the values from the XML file:

Linq to XML

This is the XML file that contains all then servers:

To query this with C#, we need to add the following references:
[code language=”csharp”]
using System.Xml;
using System.Xml.Linq;
[/code]

Querying an XML document is as simple as this:

[code language=”csharp”]
var result = from s in XDocument.Load("Servers.xml").Descendants("server")
where s.Element("name").Value == "SQLSERVER01"
select s).FirstOrDefault();
[/code]
we would get the value of the username field like this:

[code language=”csharp”]
result.Element("username").Value;
[/code]

All the code

Finally, this is all the code from this article:

Hope this has helped you somehow!


Castle Windsor ASP.NET MVC 3 Baby Example part 2

This is part 1. The sourcecode is here.
This article is based on this tutorial.

So I created an IStoryTeller interface, with one method that returns a string. GoodMorning.
Very sophisticated, no?
Next, I created 2 classes that implement the IStoryTeller interface. A French and a Dutch. I’m pretty sure you know where this is going:

Now I have to create an installer for the StoryTeller. Here is where I tell Castle Windsor which StoryTeller to implement:

Now I can create the controller and add the IStoryTeller to the constructor:

Run, Forest!

And now if I change the Installer and inject the FrenchStoryTeller I get:

So this is how easy this is.