Select with optgroup via ajax and mvc controller

I had a need to bind a select control to a set of json data delievered via a  $ ajax call…

 

C# method

 public List<SelectListItem> changeHistorySelect { get; private set; }

        [AcceptVerbs(HttpVerbs.Get)]

        public ActionResult GetChangeHistorySelect(Int64 Id)
        {
            var selectListGroup_Info = new SelectListGroup() { Name = "Info"};
            var selectListGroup_Allotments = new SelectListGroup() { Name = "Allotments" };
            var selectListGroup_Financial = new SelectListGroup() { Name = "Financial" };
            var selectListGroup_Daily_Status = new SelectListGroup() { Name = "Daily Status" };
            var selectListGroup_Enrollments = new SelectListGroup() { Name = "Enrollments" };

            #region  Misc Code...

            // Build list
            changeHistorySelect = new List<SelectListItem>
            {
              new  SelectListItem
              {
                  Value = "",
                  Text = "- Select -",
                  Selected = true
                 
              },
                new SelectListItem
                {
                    Value = "student",
                    Text = "Student",
                    Group = selectListGroup_Info
                },
                new SelectListItem
                {
                    Value = "altAddresses",
                    Text = "Alternate Addresses",
                    Group = selectListGroup_Info
                },
                new SelectListItem
                {
                    Value = "enrollment",
                    Text = "Enrollment",
                    Group = selectListGroup_Info
                },
                new SelectListItem
                {
                    Value = "enrollmentType",
                    Text = "Enrollment Type",
                    Group = selectListGroup_Info
                },


                new SelectListItem
                {
                    Value = "allotteeInfo",
                    Text = "Allottee Information",
                    Group = selectListGroup_Allotments
                },

                new SelectListItem
                {
                    Value = "earnings",
                    Text = "Earnings",
                    Group = selectListGroup_Financial
                },
                new SelectListItem
                {
                    Value = "deductions",
                    Text = "Deductions",
                    Group = selectListGroup_Financial
                },
                new SelectListItem
                {
                    Value = "reimbursements",
                    Text = "Reimbursements",
                    Group = selectListGroup_Financial
                },
                new SelectListItem
                {
                    Value = "accruals",
                    Text = "Accruals",
                    Group = selectListGroup_Financial
                },
              
            };

            // Now add enrollments to list

            var enrollments = new StudentChangeHistoryService().GetStudentEnrollment(Id);
            foreach (var er in enrollments)
            {
                if (er.sep_dt.ToString() == "1/1/0001 12:00:00 AM")
                {
                    changeHistorySelect.Add(new SelectListItem
                    {
                        Value = er.enr_dt != null ? er.enr_dt.Value.ToString("MM/dd/yyyy") : String.Empty,
                        Text = "Current Enrollment: " + (er.enr_dt != null ? er.enr_dt.Value.ToString("MM/dd/yyyy") : String.Empty) + " - Current",
                        Group = selectListGroup_Daily_Status
                    });
                }
                else
                {
                    changeHistorySelect.Add(new SelectListItem
                    {
                        Value = er.enr_dt != null ? er.enr_dt.Value.ToString("MM/dd/yyyy") : String.Empty,
                        Text = String.Format("Prior Enrollment: {0} - {1} ", er.enr_dt != null ? er.enr_dt.Value.ToString("MM/dd/yyyy") : String.Empty,(er.sep_dt != null ? er.sep_dt.Value.ToString("MM/dd/yyyy"): String.Empty)), 
                        Group = selectListGroup_Daily_Status
                    });
                }


            }

            var jsonResult = Json(changeHistorySelect);
            #endregion

            return Json(changeHistorySelect,JsonRequestBehavior.AllowGet);
        }

Now I need to have the UI call and build selector in javascript


 function getChangeHistorySelect(studentId) {

            $.getJSON('@Url.Action("GetChangeHistorySelect")', { Id: studentId }).done(function (data) {

                console.log(data);
                var result = data;

                var changeHistorySelect = $('#changeHistorySelect');
                changeHistorySelect.empty();
                var groupName = '';
                var groupPreviousName = '';
                $.each(result,
                    function () {
                        // Need an outloop for the option group
                       // debugger;
                        if (this.Group != null) {
                            // Has the group already been added?
                            var group = this.Group;
                            if (groupName === '') {

                                groupName = group.Name;
                                groupPreviousName = groupName;
                                // First Add...
                                changeHistorySelect.append(
                                    $('<optgroup>',
                                        {
                                            label: groupName,
                                            id: groupName
                                        }));


                            } else {
                                groupName = group.Name;
                                // A group has already been added... does it match?
                                if (groupPreviousName === groupName) {
                                    // They Match skip (added select under this group
                                } else {
                                    // They don' match, add new optGroup and contiue on to add selects under it
                                    groupName = group.Name;
                                    groupPreviousName = groupName;

                                    changeHistorySelect.append(
                                        $('<optgroup>',
                                            {
                                                label: groupName,
                                                id: groupName.replace(/\s/g, "")
                                            }));
                                }
                            }
                            // Add Option Group
                            var custom = $('#' + groupName.replace(/\s/g, ""));
                            var option = $("<option></option>");
                            option.val(this.Value);
                            option.text(this.Text);
                            option.appendTo(custom);


                        } else {
                            changeHistorySelect.append(
                                                           $('<option>',
                                                               {
                                                                   value: this.Value
                                                               }).text(this.Text)
                                                       );
                        }

                    });

            });
        }


.

 

 

 

 

 

 

IdentityServer3 Custom Views for login

So in my endeavors to support Facebook, Google, Outlook and etc login providers the middle ware IdentityServer3 on git has proven worth its weight in gold.

If you need to customize the views for a client, let us suppose a customer wants the login page to be branded for their company logo….What??… the nerve of them.. lol… Seriously, it is extremely easy… See this link: [ branding ]  Perhaps they want to use a bootstrap theme, different layout or various validation rules… See that link…

UP NEXT:

My next post will be about using this with an Angular2 application. Much easier than I even thought!

IdentityServer3 Login Form + Anonymous Auth on IIS

When you deploy to IIS be sure to edit the web sites Authentication settings

Anonymous Authentication: Enabled

Basic Authentication: Disabled

Windows Authentication: Disabled

Digest Authentication: Disabled

Forms Authentication: Disabled

ASP.NET Authentication: Disabled

With this setup, you will not automatically be logged in via your Domain Account, but instead prompted to login via the Middleware Login Page.

If you want your users to use their windows login, you can enable Windows Authentication.

 

IdentityServer3 + MVC Login Infinite loop

I upgraded an existing MVC3 Project that once used Web Form login and Active Directory as a means to authenticate a user,  to now  use a login process similar to sites that let you use OAUTH2 but pick either facebook, google or other identity service provider.

When I finished the upgrade process I was getting the login form via the IdentityServer3 middle-ware, but when it tried to redirect to the original site (client) it was just loop and loop… and … well you get the point by now….

To fix the issue: (found numerous solutions here…)

The short is that I needed to either add the session_onstart in global aspx or add a CallbackPath.

GLOBAL.ASAX file fix:

 protected void Session_Start(object sender, EventArgs e)
 {
 /// When using cookie-based session state, ASP.NET does not allocate storage for session data until the Session object is used. 
 /// As a result, a new session ID is generated for each page request until the session object is accessed. 
 /// If your application requires a static session ID for the entire session, 
 /// you can either implement the Session_Start method in the application's Global.asax file and store data in the Session object to fix the session ID, 
 /// or you can use code in another part of your application to explicitly store data in the Session object.
 base.Session["init"] = 0;
 }

The  CallBackPath solution goes in your code where you are configuring your owin process app.UseOpenIdConnectAuthentication  — do this in the client application.

 app.UseOpenIdConnectAuthentication(new OpenIdConnectAuthenticationOptions
 {
 ClientId = "app_jcrl",
 Authority = Constants.BaseAddress,
 RedirectUri = "https://jcrl3g.jcdev.org/home/",
 PostLogoutRedirectUri = "https://jcrl3g.jcdev.org/",
 ResponseType = "code id_token",
 Scope = "openid profile read write offline_access",
 CallbackPath = new PathString("/home/index/"), // Critical to prevent infinite loop 
 TokenValidationParameters = new TokenValidationParameters
 {
 NameClaimType = "name",
 RoleClaimType = "role"
 },

SSIS DTS + MVC API

I recently had a need to do a deep dive into SSIS DTS packages to import customer data from a MVC WEB API site. While I have done this many times via a windows services or other such similar code, I really wanted to manage this process via SQL Server jobs and data transformation services.

After experimenting and looking about the web for more information on this I quickly determined it was fairly easy to accomplish. I will post some demo code on how I did this… Hope this helps someone!

I know I needed to store a list of customers and their unique URLs for each WEB API end point for retrieving data. My specific business domain is in education so I knew I would be calling data via district, school, students and teachers by schools, schedules by schools and etc… To help manage this I created a simple database that tracks the various data points I need. This database is nothing more than a dumping ground for the API data and uses varchar to hold most of the data before I parse it into our actual production systems. Each time the SSIS package runs I truncate the tables, which I recommend to reduce log file entries.

The first table I created in my SSISImport database was my customer table. This table is used to store a list of customers who use this MVC API and their security certs as needed.

Table Name: Customer

The Cert field for our implementation is unique to each customer and I recommend in your production system to obfuscate this information via encryption.

Then I created a table to hold each customer’s API URL formatted to allow string.format (c#) to insert the school and student into the url.

For example when I need a list of students for a particular school my API URL looks like this in the database: https://www.schooldistrict.us/ssisapi/api/schools/{0}/students

The {0} parameter is replaced with a value in my code as it iterates over each school.

Table Name: APIUrl

Entries in this table looks like this:

This table is called in the code for each customer by each DTS Task that is belongs to and etc.

The rest of the tables in the database are nothing more than copies of the POCO object format in a database structure so I can desterilize it from json into a flat table in sql server. You will see this in the c# code coming up next.

The first DTS task sets are nothing more than the truncate code:

EXECUTE SQL TASK…

The task (Data Flow Task – Get Customers) grabs all of the customers and stores them in a variable being passed to the DTS Foreach loop task.

The foreach look task then loops over each customer and calls their APIs one by one until the operation is complete…

Now the meat of the code… I have simplified this example for this demonstration. I would recommend adding in more error trapping and exception handling…

Unfortunately, LinkedIn Blog does not have a nice code format tool…

<code>

/* Microsoft SQL Server Integration Services Script Component

* Write scripts using Microsoft Visual C# 2008.

* ScriptMain is the entry point class of the script.*/

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Data.SqlClient;

using SC_5baebd7e1dfe4a73abb38025972f5ed7.csproj;

using System.Collections.Generic;

using SC_5baebd7e1dfe4a73abb38025972f5ed7.csproj.Models;

using System.Net;

using System.IO;

using System.Web.Script.Serialization;

using System.Windows.Forms;

using Microsoft.SqlServer.Dts.Runtime;

using System.Collections;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

public override void PreExecute()

{

base.PreExecute();

/*

Add your code here for preprocessing or remove if not needed

*/

}

public override void PostExecute()

{

base.PostExecute();

/*

Add your code here for postprocessing or remove if not needed

You can set read/write variables here, for example:

Variables.MyIntVar = 100

*/

}

public override void CreateNewOutputRows()

{

/*

Add rows by calling the AddRow method on the member variable named “<Output Name>Buffer”.

For example, call MyOutputBuffer.AddRow() if your output was named “MyOutput”.

*/

ProcessData();

}

private void ProcessData()

{

try

{

var myConnectionString = this.Connections.StudentInfoManager.ConnectionString;

using (SqlConnection conn = new SqlConnection(this.Connections.StudentInfoManager.ConnectionString))

{

if (conn.State != ConnectionState.Open) conn.Open();

using (SqlCommand command = conn.CreateCommand())

{

int customerId = this.Variables.CustomerID;

string Cert = this.Variable.Cert;

var customerUrl = GetCustomerWebServiceUrls(command, customerId);

// Recommend error trap

var CourseList = GetWebServiceResult(customerUrl.WebServiceUrl, Cert);

// Recommend error trap

if (CourseList.Length > 0) // possible null exception

{

foreach (Courses item in CourseList)

{

Output0Buffer.AddRow();

Output0Buffer.CustomerID = item.CustomerId;

Output0Buffer.LongDescription = item.LongDescription;

Output0Buffer.StateCourseCode = item.StateCourseCode;

Output0Buffer.Title = item.Title;

}

}

}

if (conn.State != ConnectionState.Closed) conn.Close();

}

}

catch (Exception ex)

{

FailComponent(“ProcessData”, “Error: ” + ex.Message);

}

}

private APIUrl GetCustomerWebServiceUrls(SqlCommand command, int customerId)

{

APIUrl apiURL = new APIUrl();

try

{

command.CommandText = String.Format(“Select * from APIUrl Where customerID = {0} And APIType = ‘Courses’ Order By CallOrder”, customerId);

command.CommandType = CommandType.Text;

using (SqlDataReader reader = command.ExecuteReader())

{

if (!reader.HasRows)

{

throw new NoRecordFoundException(String.Format(“Table APIUrl for customer {0} does not have a Courses entry.”, customerId));

}

int rowsInDataReader = 0; //Should always be 1

while (reader.Read())

{

rowsInDataReader++;

//Cast to APIUrl object

apiURL = new APIUrl()

{

Id = (int)reader.GetValue(0),

customerId = (int)reader.GetValue(1),

APIType = (string)reader.GetValue(2),

WebServiceUrl = (string)reader.GetValue(3),

CallOrder = (int)reader.GetValue(5)

};

}

if (rowsInDataReader != 1)

{

FailComponent(“GetCustomerWebServiceUrl”, String.Format(“More than one URL entry was found in the database for Customer: {0} See table APIUrl”, customerId));

return null;

}

}

}

catch (Exception ex)

{

FailComponent(“GetCusotmerWebServiceUrl”, “Error: ” + ex.Message);

}

return apiURL;

}

private Courses[] GetWebServiceResult(string WebServiceUrl, string Cert)

{

string wsUrl = WebServiceUrl;

//MessageBox.Show(wsUrl);

Courses[] jsonResponse = null;

try

{

HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wsUrl);

httpWReq.Headers.Add(“CERT”, Cert);

HttpWebResponse httpWResp;

httpWReq.MaximumResponseHeadersLength = 10000;

httpWResp = (HttpWebResponse)httpWReq.GetResponse();

//MessageBox.Show(“Waiting for response”);

//Test the connection

if (httpWResp.StatusCode == HttpStatusCode.OK)

{

//MessageBox.Show(“Waiting for response : OK”);

Stream responseStream = httpWResp.GetResponseStream();

string jsonString = null;

//Set jsonString using a stream reader

using (StreamReader reader = new StreamReader(responseStream))

{

//MessageBox.Show(“Stream reader”);

jsonString = reader.ReadToEnd();

reader.Close();

}

//MessageBox.Show(“WStream read”);

//Deserialize our JSON

JavaScriptSerializer sr = new JavaScriptSerializer();

sr.MaxJsonLength = Int32.MaxValue;

jsonResponse = sr.Deserialize<Courses[]>(jsonString);

}

else

{

string errorMessage = “Web service response Failed with Status Code: ” + httpWResp.StatusCode;

FailComponent(“GetWebSerbiceResult”, “Web Service Url: ” + wsUrl + ” ” + errorMessage);

//MessageBox.Show(errorMessage);

}

}

catch (WebException ex)

{

//MessageBox.Show(“WebException ” + ex.ToString());

FailComponent(“GetWebServiceResult”, “WebException: ” + ex.Message);

}

catch (Exception ex)

{

//MessageBox.Show(“Generic EX ” + ex.ToString());

FailComponent(“GetWebServiceResult”, “General Exception: ” + ex.Message);

}

return jsonResponse;

}

private void FailComponent(string SubComponent, string message)

{

bool fail = false;

IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;

compMetadata.FireError(1, SubComponent, message, “”, 0, out fail);

}

}

public class NoRecordFoundException : Exception

{

public NoRecordFoundException() : base() { }

public NoRecordFoundException(string message) : base(message) { }

public NoRecordFoundException(string message, Exception innerException) : base(message, innerException) { }

}

</code>

MVC-API with Windows CE Embedded 6

WinCEMVC

Previously I posted about using a Web-API to import data from another vendor’s products into our database. One of my goals for our team has been to move away from using direct calls into our database be it by either .NET ADO, Linq or SQL commands and instead expose a means for all products to get their data needs via a service. When we built a mobile application; which is a Single Page Application, I rolled out a MVC-API using Microsoft’s framework for this. Our first big pay off on adaptability and cross platform use proved out when we needed to support a RFID Device made by Motorola. This device uses a Windows CE Embedded operating system. Below I will quickly cover how I coded to connect to the API from this device.

First things first…

I had tried to store the API url’s on the device in a XML file, but watch out because xml does not like the & symbol in any value. You can use things like &amp; or %26 inline, but Windows CE in-application-browser does not seem to like this very much… My work around was to store the values on the device’s SQL Lite instance.

Now moving on, as you know an API will have an end point you send your query to.
For example:

https://www.mysite.org/api/schedule/{0}

The above api, requires a school id so you can pull the correct schedule for that school/site.

In my example, I will show how I pulled a set of inventory tags down from the API site.

I have a basic method called GetTags

The api is called, several helper methods help pull the data and parse it and eventually inflate a List with POCO objects called TagItem which is a simple class with Getters and Setters.

public static List<TagItem> GetTags(string ApiURL,int SiteUId)

{

string uri = String.Format(“{0}/api/Inventory/GetTags?siteUId={1}”, ApiURL, SiteUId);

return JsonConvert.DeserializeObject<List<TagItem>>(GET(uri));

}

As you can see we pass in the GENERIC API URL and pre-pend it to the API Signature.

The real meat here is the GET method which in turn uses the GetBaseRequest and other helper methods. I will post the code below since it should be fairly obvious what it is doing…

private static string GET(string pUri, int pTimeOut)

{

HttpWebRequest request = null;

string retValue = “”;

try

{

request = GetBaseRequest(pUri, “GET”, “application/json; charset=utf-8”, pTimeOut);

retValue = ReadRequestResponse(request);

}

catch (Exception)

{

throw;

}

return retValue;

}

private static HttpWebRequest GetBaseRequest(string pUri, string pMethod, string pContentType, int pTimeout)

{

System.Net.ServicePointManager.CertificatePolicy = new TrustAllCertificatePolicy();

HttpWebRequest request = (HttpWebRequest)WebRequest.Create(pUri);

request.Headers.Add(“Authorization”, Token);

request.Method = pMethod;

request.Proxy = null;

request.KeepAlive = false;

request.Timeout = pTimeout;

request.ContentType = pContentType;

request.UserAgent = “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)”;

return request;

}

private static string ReadRequestResponse(HttpWebRequest pRequest)

{

string retval;

HttpWebResponse response = (HttpWebResponse)pRequest.GetResponse();

using (Stream responseStream2 = response.GetResponseStream())

using (StreamReader reader = new StreamReader(responseStream2))

retval = reader.ReadToEnd();

response.Close();

return retval;

}

That is the meat of it, hope that helps someone else. If you have questions please let me know.