All requests must be validated whether the given site URL exists in a database. If it exists, the connection string will be built based on a given URL and an SQL connection will be opened. I created a static cache for these URLs (key: site URL, value: database name) because I don't want to query the database every time I need to build a connection string for a specific URL. When a connection string is successfully created, an API controller checks if any car is available.
Controller
public class CarController : ApiController
{
[HttpPost]
public IHttpActionResult Available(SiteUrl model)
{
if (ModelState.IsValid)
{
var conStr = ResolveClientConnectrionString(model.Url);
if (conStr != "")
{
return Ok(CarHelper.CheckIfAnyAvailable(conStr));
}
}
return StatusCode(System.Net.HttpStatusCode.NoContent);
}
private static string ResolveClientConnectrionString(string url)
{
if (CarHelper.DBs.ContainsKey(url))
{
var clientConnectionString = GetConnectionStringForDatabaseName(ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString, CarHelper.DBs[url]);
return clientConnectionString;
}
else
{
var databaseName = "";
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString))
{
con.Open();
var query = "Select Top(1) url, dbName from sites where url=@url";
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.Parameters.AddWithValue("@url", url);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
databaseName = (string)rdr["dbName"];
if (databaseName != "")
{
CarHelper.DBs.Add((string)rdr["url"], (string)rdr["dbName"]);
var clientConnectionString = GetConnectionStringForDatabaseName(ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString, databaseName);
return clientConnectionString;
}
}
}
}
}
return "";
}
}
private static string GetConnectionStringForDatabaseName(string mainConnectionString, string databaseName)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(mainConnectionString)
{
InitialCatalog = databaseName
};
var clientConnectionString = builder.ConnectionString;
return clientConnectionString;
}
}
Static helper class
public static class CarHelper
{
// key: url | value: database name
public static Dictionary<string, string> DBs { get; set; }
static CarHelper()
{
DBs = new Dictionary<string, string>();
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString))
{
con.Open();
var query = "select url, dbName from Sites";
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (!DBs.ContainsKey((string)rdr["url"]))
{
DBs.Add((string)rdr["url"], (string)rdr["dbName"]);
}
}
}
}
}
}
public static Car CheckIfAnyAvailable(string connectionString)
{
var result = new Car();
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
string query = @"Select TOP 1 Id, Name from Cars where Available=@available";
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.Parameters.AddWithValue("@available", true);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
result.Id = (long)rdr["Id"];
result.Name = (string)rdr["Name"];
}
}
}
}
return result;
}
}