unplug1.6

Monday, July 23, 2012

SQL parse string

To parse a value from a column, PARSENAME and REPLACE can be used.
For instance, if the variable portion of a URL needs to be parsed, it can be done like this:

SELECT PARSENAME(REPLACE('http://www.theurl.com?var=1234', 'var=', '.'), 1) //Returns 1234

Thursday, June 28, 2012

C# deserialize JSON post

[Serializable]
public class Person
{
public int personId;
public int appId;
public string name;
}

string input = new StreamReader(Request.InputStream).ReadToEnd();
JavaScriptSerializer deserializer = new JavaScriptSerializer();

Person _person = deserializer.Deserialize(input);
Console.Writeline(string.Format("Person delivered is: {0}", _person.name));

You can test this with the code below --------------------------------------------------:

string url = "the_test_url";
HttpWebRequest httpReq = (HttpWebRequest)WebRequest.Create(url);
StringBuilder jsonData = new StringBuilder();

jsonData.AppendLine("{");
jsonData.AppendLine("\"personId\" : 1234,");
jsonData.AppendLine("\"appId\" : 2334,");
jsonData.AppendLine("\"name\" : \"John\"");
jsonData.Append("}");

byte[] dataArray = Encoding.UTF8.GetBytes(jsonData.ToString());
httpReq.Method = @"POST";
httpReq.ContentType = @"application/json";
httpReq.ContentLength = dataArray.Length;
httpReq.Timeout = 60000; // 1 minute (in milliseconds)
httpReq.KeepAlive = false;

using (Stream requestStream = httpReq.GetRequestStream())
{
requestStream.Write(dataArray, 0, dataArray.Length);
requestStream.Flush();
requestStream.Close();
}
HttpWebResponse _resp = (HttpWebResponse)httpReq.GetResponse();
_resp.Close();

Wednesday, June 27, 2012

Using SQL recursion

This example will generate an employee/manager heirarchy.

DECLARE @employeeId INT
SET @employeeId = 123
WITH emp_hier AS
(
SELECT e.employeeId , e.managerEmployeeId, l.JobLevel as employeeLevel
FROM EmployeeData e
INNER JOIN EmployeeJobLevel l ON l.JobLevel=e.JobLevel
WHERE e.employeeId = @employeeid
UNION ALL
SELECT e2.employeeId , e2.managerEmployeeId, c2.JobLevel as employeeLevel
FROM EmployeeData e2
INNER JOIN EmployeeJobLevel l2 ON l2.JobLevel=l2.JobLevel
INNER JOIN emp_hier ecte ON ecte.managerEmployeeId= g2.employeeId
WHERE ecte.employeeId <> ISNULL(g2.managerEmployeeId, -1) --This clause prevents maximum recursion error when two employees share each other as their supervisors
)

SELECT *
FROM emp_hier

Monday, April 23, 2012

One way to Hide/Show information without postback

This is a simple method of hiding or showing information based on a users action. It utilizes CSS, Javascript, and ASP.NET controls

1.) Define CSS for .visible and .hidden
.visible
{
visibility: visible;
position: relative;
}
.hidden
{
visibility: hidden;
position: absolute;
}

2.) Create separation of content using divs and set onchange event

<div>
Do you want to show more info?
<asp:dropdownlist id="Question" onchange="javascript:HideShow(this, 'Detail')" runat="server">
<asp:listitem> </asp:listitem>
<asp:listitem value="True">Yes</asp:listitem>
<asp:listitem value="False">No</asp:listitem>
</asp:dropdownlist> </div>
<div id="Detail">
Some additional information

</div>

3.) Set onclick method to hide/show inline or via Javascript function call

HideShow(var ctl, var detailsSection){
if (document.getElementById)
{
var e = document.getElementById(detailsSection);
if (e != null)
{
e.className = (ctl.selectedIndex != 1 ? 'hidden' : 'visible');
}
}
}

Wednesday, April 4, 2012

Auto redirect a form when page loads

To redirect a form onload, set the onload event of the body

<html>
  <body onload="document.TheForm.submit();">
    <form name="TheForm" action="redirectURL" method="post">
      <input type="hidden" name="ID" value="valueToPass" />
    </form>
  </body>
</html>

*Note: setting a hidden input name/value pair will allow the receiving URL to pull and use that info being sent.

Saturday, February 25, 2012

Detect line break in SQL query

To detect a line break within a SQL query, the below script will replace line breaks with a tilde (~).

SELECT REPLACE(Notes, CHAR(10), '~')
FROM Table

Other characters you may want to replace:
Tab -> char(9)
Line feed -> char(10)
Carriage return -> char(13)

Wednesday, February 8, 2012

SQL Update column name

exec sp_RENAME 'table.column', 'newName', 'COLUMN';

example:
exec sp_RENAME 'Employee.notes' , 'payNotes', 'COLUMN';

Caution: Changing any part of an object name could break scripts and stored procedures.