Create a simple C# Web Service API to insert a record into a SQL Server database

Creating a Web Service API in C# that talks to a SQL Server database in Visual Studio is relatively straightforward. It comes with a number of wizards that guide through the process. First, create a project by

1. Start Visual Studio. Select New Project. Choose Templates | Visual C# | Windows | Web. Choose ASP.NET Web Application.
   
   The New Project dialog box appears.
   
   
2. Type in a new Name, Solution Name and/or Browse to a new Location. Click OK
   
   The project is created.
   
3. In the Solution Explorer pane on the right, double click on the file Web.config.
   
   The editor shows the contents of Web.config.
   
4. Add in a new section for connectionString, as shown in the screenshot below.
   
   Note: obviously, the connection string values will vary depending on the server name, database, and user name, so they need to be changed accordingly.
   
   
   
5. In the Solution Explorer pane, right click on the Controllers folder node and choose Add Controller.
   
   The Add Scaffold dialog box appears.
   
   
6. Select an appropriate controller e.g. Web API 2 Controller with read/write action. Click Add. Type in the new Controller name, e.g. LogController. Click Add.
   
   
   A skeleton controller is created.
   
7. Open the newly created file e.g. LogController.cs in the editor. Type in a method e.g. Get. An example is shown below. 

Note: in this example, when a user calls the address in a browser with the Route pattern http://some.server.com/api/log/[some latitude number]/[some longitude number], the second Get method is called. If the call is successful, the latitude and longitude values would have been inserted into the SQL Server database.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace ProductsAPP.Controllers
{
    public class LogController : ApiController
    {
        // GET: api/Log
        public IEnumerable<string> Get()
        {
            return new string[] { "value1", "value2" };
        }

        [Route("api/Log/{lat}/{lon}")]
        public string Get(double lat, double lon)
        {
            //Get the SQL Server database connection string from
            //the Web.config file
            ConnectionStringSettings settings;
            settings = System.Configuration.ConfigurationManager.ConnectionStrings["Database1Connection"];
            string connectionString = settings.ConnectionString;

            //Create a new SQL Server database connection
            SqlConnection conn;
            conn = new SqlConnection(connectionString);
            try
            {
                //Open a connection
                conn.Open();

                //Create a parameterized SQL command to insert
                string query =
                    "INSERT INTO point_datatable (latitude, longitude) ";
                query += " VALUES (@latitude, @longitude)";
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.AddWithValue("@latitude", lat);
                cmd.Parameters.AddWithValue("@longitude", lon);

                //Run the insert statement
                cmd.ExecuteNonQuery();

                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception:" + ex.Message);
            }
            return "ok";
        }
    }
}