Saturday November 9th, 2019

Web Service and SQL Connection

By Ebubekir Sezer

Hello, In my previous article, i wrote about the how to create Web App and SQL Service in the Azure. In this article, I create a ASP.NET Web Service and try to communicate with the services which we created at Azure. Also I will try to keep data in SQL.

Firstly, I create a blank solution and then I create an ASP.NET Core and choose the template as API. After project created, create a Models folder and inside of the Models folder i create two classes Entity.cs and User.cs. I created Entity because i do not want to create id for the all models, i create just one and inherited this to the other class.

namespace XamarinDBService.Models
{
    public class Entity
    {
        public string ID { get; set; } = Guid.NewGuid().ToString();
    }
}
namespace XamarinDBService.Models
{
    public class User : Entity
    {
        public string UserName { get; set; }
        public string Password { get; set; }
    }
}

After the adding our models, now we have to add some Nuget Packages to our project. You can see the packages in the below.

After the adding packages, we need to add DBContext to our project. I create a class which name is XamarinDBContext and i inherited this class from the DBContext. I create a structure which gets a DBContextOptions and i added the our models as DBSet.

namespace XamarinDBService
{
    public class XamarinDBContext : DbContext
    {
        public XamarinDBContext(DbContextOptions<XamarinDBContext> options) : base(options)
        {

        }
        public DbSet<User> Users { get; set; }
    }
}

After the making all process in the above, we need to make sql connection. We should go to appsettings.json and add the connection strings. To get Connection Strings go to the Azure Portal and click the Sql Server and look at the left menu, there is a connection strings and copy it. Add the connection strings to the appsettings.json. You have to control the information are correct and make sure that you enter the password correct.

{
  "ConecttionStrings": {
    "XamarinDBContext": "Server=tcp:dbxamarin.database.windows.net,1433;Initial Catalog=xamarindb;Persist Security Info=False;User ID={your_user_name};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

Now go to the Startup.cs and make some arranges in the ConfigureServices.

 public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
            services.AddDbContext<XamarinDBContext>(optionsBuilder => optionsBuilder.UseSqlServer(Configuration.GetConnectionString(nameof(XamarinDBContext))));
        }

After the making all process, we have to make migration process and create our tables in the database. We need to make migration for that we use this commands Add-Migration and then write some migration name . After the migration added, we need to update database and for the make that we need to write this command Update-Database.  Now we can see the our models in the sql database. Open the SQL Server Management and control it.

To make interactions, we need to add controller. Click right on the Controller folder and create a new controller. This controller should be API Controller with actions, using Entity Framework.

We can make the Update,Post,Delete and Get process after the adding controller. We still did not publish the our service. For the publish click right and click publish on the project. Click Import Profile and get the profile from the Azure App Service then import the file. Then click the publish and wait to be process done. If the process done successfully we can connect the our web services whatever we want.

In the next article, I will try to make Xamarin.Forms application which gets and posts data to the this web service.

If you ask your questions via e-mail or comments, i will be happy.