SQL Select with variable and IF statement

Sometimes I need to select from sql based on criteria from another query. In certain situations, you can use the server side scripting to manipulate and call queries, but other times for performance, the best way is to have SQL server handle the workload.

I can declare a variable and set it from one query, and use it in another. For example, I want to check if there are only one record for an RID code passed in as a parameter, and then update the record if so:

declare @RecordCount int

    set @RecordCount = (select  count(*) FROM [My].[dbo].[Table]  where RID= @RID)
IF @RecordCount == 1
UPDATE [My].[dbo].[Table] SET myBitField= 1 WHERE RecordId= @RecordId

Compare alphanumeric strings for greater/less than equality C#

I have some alphanumeric strings to compare. I need to know if A1234 is before B5678 when sorted alphanumerically. I also want to ignore the case of the alphabetic characters.

int comparison = String.Compare(A1234, B5678, comparisonType: StringComparison.OrdinalIgnoreCase);

The integer returned will be -1 if lower in order, 0 if equal and 1 if greater.

Split a comma delimited string into an array in C#

The ability to split a string is a common feature in programming. The way to implement this in c# is a bit different than most languages.

We have a string such as:

string myVars = “var1, var2, var3, var4”;

We need to split this string into an array of individual variables. That’s where our String.Split() method comes into play. Its pretty easy once you know the syntax. The Microsoft docs don’t mention this way:

string[] myArray = myVars.Split(‘,’);

The key is the single quotes around your delimiter in the split method.

Skagit Valley College Student Document Repository

If the Financial Aid Department is the heart of the college institution, then the Skagit Valley College was suffering from a stroke. The heart was clogged up from the result of years of managing paperwork using tried and true ancient techniques: the in person delivery of hand filed paper forms.

And then corona virus came to deliver the death blow. Due to the pandemic, the college campus was forced to close. Unable to allow students to come to campus and process in person financial aid meant that our college might suffer the same fate as several other Washington State institutions and be shut down completely.

They came to me with this problem desperate for a solution. They needed a document repository that could be integrated with our current Legacy student management system data, but that will also be able to transition with our data over the next few years as the school completes its move to a new student data management system. They needed a way to assign the forms to the students, and a portal where students could securely upload their documents.

The college had a legacy document storage system in place, aging and in need of replacement. The system had fallen out of development and was no longer being supported. The decision was to either purchase an expensive out of the box system, or develop one in-house. Due to the success of previous projects, the President of SVC decided to have me develop a custom in-house document storage and management solution that would be easily integrated into our student data management system.

During the planning phase, the project management documents were drawn up, key deliverables determined, and the use case scenarios developed.

A screen shot of the student document repository user interface.
A screen shot of the student document repository user interface.

Throughout the software development life cycle, those documents were referred to in order to prevent feature / scope creep from setting back the projected completion date. Using Agile project management I completed several sprints of user story backlogs, and the scrum retrospectives revealed valuable lessons learned about the techniques used to program the application.

 In the midst of the pandemic, forced into working remotely, I was under the pressure to save the college by finishing my application well ahead of the projected schedule.

The project close was met with very satisfied stake holders, and the project sponsor was happy to sign off the completed product.

Compare old and new record for logging c#

You have an old record, and the new one to be saved to database. Generate a log of the changes made using this snipped.

string logmessage = "The Doc Repo Category " + oldDocCatTitle + " has been edited.";
                logmessage += (oldDocCatTitle != DocCatTitle) ? "The title changed from " + oldDocCatTitle + " to " + DocCatTitle + "." : "";
                logmessage += (oldDocCatDesc != DocCatDesc) ? "The description changed from " + oldDocCatDesc + " to " + DocCatDesc + "." : "";
                logmessage += (oldDocCatShared != DocCatShared) ? "The shared changed from " + oldDocCatShared.ToString() + " to " + DocCatShared.ToString() + "." : "";
                logmessage += (oldDocCatShowOnHome != DocCatShowOnHome) ? "The description changed from " + oldDocCatShowOnHome.ToString() + " to " + DocCatShowOnHome.ToString() + "." : "";
                if (oldDocCatTitle != DocCatTitle || oldDocCatDesc != DocCatDesc || oldDocCatShared != DocCatShared || oldDocCatShowOnHome != DocCatShowOnHome || oldDocCatLink != DocCatLink)
                {
                    bool addLog = dataProcessing.addLog(Session["adminSID"].ToString(), "", logmessage, out string logMessage);
                }

Return Post Variables from c# api as JSON array

Call to c# api using ajax and recieve an array of variables in JSON format.

javascript call to api: In aspx page, you can access a posted in variable such as an id…

const NewsID = ‘<%=Request.Form[“NewsID”]%>’;

… then call to api and get record from that id…

 $.ajax({
type: 'POST',
url: 'update.aspx/loadRecord',
data: JSON.stringify({ NewsID: NewsID }),
contentType: 'application/json; charset=utf-8',
dataType: 'json',
success: function (msg) {
var obj =  jQuery.parseJSON(msg.d)
console.log("I have successfully called to the api.");

 // set the heading value
 $("#Heading").val(obj.Heading);
 // set the body value
 $("#Body").val(obj.Body);
}
});


The C# api receives the record id and returns a record…

... call to database and get record ...

var message = new {Heading=Heading, Body= Body};
return JsonConvert.SerializeObject(message);

Append options to a select element using javascript

//dynamically add 20 years from this year to drop-down

      var select = document.getElementById(“ccexp”);

      var startYear = new Date().getFullYear();

      var option, currentYear, currentYearString, currentYearTwoDigit;

      for (var i = 0; i <= 20; i++) {

          currentYear = startYear + i;

          currentYearString = currentYear.toString();

          currentYearTwoDigit = currentYearString.substring(2);

          option = document.createElement(“option”);

          option.setAttribute(“value”, currentYearTwoDigit);

          option.appendChild(document.createTextNode(currentYear));

          select.appendChild(option);

      }

Javascript to append options to a select element

select multiple elements by class, and loop through them with javascript

I have a table with elements in it. I need to target them and change the css class.

<div class="dwtooltip"><img src="note_icon.png" /><span class="dwtooltiptext">my tooltip text here.</span></div>

const items = document.querySelectorAll(‘.dwtooltip’);
if(items){

}
for (var i=0; i < items.length; i++) {
console.log(i);
items.forEach(function(el){
el.classList.add(“notooltip”);
el.classList.remove(“dwtooltip”);
});
}