return sql servername, databasename

sql-server-EF-webapi-example.cs

/*
--#1 query to retrieve SERVER/DB
select (
	select  
		@@SERVERNAME as ServerName, 
		db_name() as DatabaseName 
		-- for XML PATH, root				     --//<== wraps each record with default <row> element, and supply a default root overall.
		-- for XML PATH('item'), root('treetop') --//<== when you want to rename 'row' to 'item', 'root' to 'treetop'
		for XML PATH							 --//<== default <row> element, no root.
		-- yields a dodgy column name, say, XML_F52E2B61-18A1-11d1-B105-00805F49916B
	) 
as DbInfo
--> <row><ServerName>MOON01</ServerName><DatabaseName>MyDB</DatabaseName></row>
*/

//-- #2 to parse the above XmlStr in C#
using System.Xml.Linq; //XDocument, XElement

string xmlStr = "<row><ServerName>MOON01</ServerName><DatabaseName>MyDB</DatabaseName></row>";
try
{
    XElement xmlRow = XElement.Parse(xmlStr);

    //string eltServerName = xmlRow.Descendants().Where(x => x.Name.LocalName == "ServerName").FirstOrDefault().ToString();
    //==> .toString gets the whole tag <ServerName>MOON01</ServerName>
    XElement xElt = xmlRow.Descendants().Where(x => x.Name.LocalName == "ServerName").FirstOrDefault();
    string serverName = xElt.Value; //MOON01

    xElt = xmlRow.Descendants().Where(x => x.Name.LocalName == "DatabaseName").FirstOrDefault();
    string dbName = xElt.Value; //MyDB

    tbOutput.Text = string.Format("serverName={0}|databaseName={1}", serverName, dbName);
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

//-- #3 putting it all together in a WebAPI method, which uses entity framework
namespace JOLI.WebAPI.Controllers
{
    //[Authorize] //<--Authorization has been denied for this request. //REMOVE THIS CRAP, make it free for all...
    public class ValuesController : ApiController
    {

        //place a test method here... to check if WEBAPI is pointing to correct server.
        [Route("api/GetEnvironment/")]
        public dynamic GetEnvironment()
        {

            try
            {
                string xmlStr = "";
                using (SECEntities dbCtx = new SECEntities())
                {
                    string sql = @"select  @@SERVERNAME as ServerName, db_name() as DatabaseName for XML PATH";
                    //-- yields a dodgy column name, say, XML_F52E2B61-18A1-11d1-B105-00805F49916B
                    sql = string.Format("select ( {0} ) as DbInfo", sql); //col name becomes DbInfo;

                    xmlStr = dbCtx.Database.SqlQuery<string>(sql, new object[] {} ).SingleOrDefault(); //NO PARMS TO PASS, but cannot use null; use Empty Array instead.
                    //e.g. <row><ServerName>MOON01</ServerName><DatabaseName>MyDB</DatabaseName></row>
                }

                //xmlStr = "<row><ServerName>MOON01</ServerName><DatabaseName>MyDB</DatabaseName></row>";
                XElement xmlRow = XElement.Parse(xmlStr);

                XElement xElt = xmlRow.Descendants().Where(x => x.Name.LocalName == "ServerName").FirstOrDefault();
                string serverName = xElt.Value; //MOON01

                xElt = xmlRow.Descendants().Where(x => x.Name.LocalName == "DatabaseName").FirstOrDefault();
                string dbName = xElt.Value; //MyDB

                dynamic rtnObj = new { ServerName = serverName, DatabaseName = dbName };
                return rtnObj;
            }
            catch (Exception ex)
            {
                Common.Utils.LogMsg(ex.Message);
                var httpRespMsg = new HttpResponseMessage(HttpStatusCode.InternalServerError) //500
                {
                    ReasonPhrase = "Unexpected error;" + ex.Message.Replace(Environment.NewLine, ";")
                };
                //WARNING: fatal error; ENSURE THAT: "The reason phrase must not contain new-line characters."

                throw new HttpResponseException(httpRespMsg); //THROW, not simply return.
            }
        }
   } //end-Class
}//end=Namespace
    
    
//-- #4 calling fron C# HttpClient
using Newtonsoft.Json;
using Newtonsoft.Json.Linq; //JObject
using System.Net.Http; //HttpClient, StringContent

private async void btnGetEnviron_Click(object sender, RoutedEventArgs e)
{

	string baseURL = @"http://localhost:60123/";
	var C_URL_ValuesController = baseURL + "/api/GetEnvironment/";
	System.Uri endPointAsUri = new Uri(C_URL_ValuesController);


	try
	{
		using (HttpClient httpClient = new HttpClient())
		{
			HttpResponseMessage resp = await httpClient.GetAsync(C_URL_ValuesController); //<== returns here.. then continues below once result is received

			//CONTINUE below once response received... (async/await)
			if (resp.IsSuccessStatusCode) //i.e. 200-OK
			{
				if (resp.Content != null)
				{
					// var rtnVal = resp.Content.ReadAsAsync<int>().Result; //using sync/blocking here, but you could await here too
					var jsonStr = resp.Content.ReadAsStringAsync().Result;
					//LHS: WebAPI method expected to return a json Obj
					if (jsonStr != "" && jsonStr.Contains("{") && jsonStr.Contains("}"))
					{
						var obj = JsonConvert.DeserializeObject(jsonStr); //LHS an object.
						JObject jObj = JObject.Parse(jsonStr);
						var formatted = jObj.ToString(Newtonsoft.Json.Formatting.Indented);
						tbOutput.Text = formatted;
					}
					else
						tbOutput.Text = "ERROR parsing webAPI response: " + jsonStr;
				}
			}
			else //ERROR, without the webAPI actually throwing an error (i.e. dont end in catch)
			{
				MessageBox.Show(resp.StatusCode.ToString());

				if (resp.Content != null) //there CAN BE A RESPONSE ON BOTH SUCCESS (200-OK) or Fail (e.g. InternalServerError)
				{
					string errResp = resp.Content.ReadAsStringAsync().Result; 
					//resp.Content.ReadAsAsync<int>().Result; <= ALT if we expected an int rtn-val
					tbOutput.Text = errResp;
				};
			};
			
		} //end-Using	
	}
	catch (Exception ex)
	{
		var errmsg = ex.Message;
		MessageBox.Show(errmsg);
	};

}

/* -- #5 Deploy remotely
#0a. Publish the VS-Proj, and Copy to IIS Server
================================================
- Publish to a desired local folder;
- Copy all files (FTP, sharedFolder, whatevs) to remote server, say D:\LiveApps\MyProjv2.WebAPI\
- Verify the Web.config for settings appropriate to your server e.g. <connectionStrings />, other key-value pairs in <appSettings />



#a. install WebAPI on IIS
=========================
At the "Default Web Site" node, right clight "Add Application"...
Alias:		   MyRestService	
Physical path: D:\LiveApps\MyProjv2.WebAPI
(leave app pool as DefaultAppPool).
OK
(leave Enable Preload. Check OFF - default)
(BUT if you CHECK ON, it enables the website to be always running; - not when first request is made, but whatevs...)


#b. Test
========
Navigate to 
	http://MOON01/MyRestService/api/GetEnvironment/
this tests two things:
	i) IIS is serving the webAPI (ValuesController gets hit)
	ii) its configured to fetch from the appropriate SQL-SERVER.
	

#c. There is no step c. Profit $$$
==================================
YAI. it worked first time !!
{"ServerName":"MOON01","DatabaseName":"MyDB"}
*/