User:Sasquatcher/SQL

MS SQL

 * 1)
 * 2)

Discovering Duplicates
This is a standard way of discovering duplicates. Use it to find rows that contain set number of instances of data in a particular column.

For instance, the example below would return occurrences of more than in column1.

select column1, count(*) as x from tbl_Data group by column1 having count(*) > 1 order by column1

Randomly Select a Record from a Table
Use the following code to get 5 randomly selected items.

SELECT Top 5 * FROM tbl_offers ORDER BY NewID

Get Top X Items
This is a standard way of discovering duplicates. Use it to find rows that contain set number of instances of data in a particular column.

For instance, the example below would return occurrences of more than in column1.

select column1, count(*) as x from tbl_Data group by column1 having count(*) > 1 order by column1

Redirect to Another Page
Response.Redirect(sURL, True)

Retrieving App Settings
ConfigurationSettings.AppSettings("Field Name Here")

Encode URL Content
The following code is used to encode a string to be passed in the Query String of a URL.

Public Shared Function Encode(ByVal sSource As String) As String

Dim i As Integer Dim strTemp As String Dim strChar As String Dim strOut As String Dim intAsc As Integer

strTemp = Trim(sSource) For i = 1 To Len(strTemp) strChar = Mid(strTemp, i, 1) intAsc = Asc(strChar) If (intAsc >= 48 And intAsc <= 57) Or _ (intAsc >= 97 And intAsc <= 122) Or _ (intAsc >= 65 And intAsc <= 90) Then strOut = strOut & strChar Else strOut = strOut & "%" & Hex(intAsc) End If           Next i

Return strOut End Function

Load an Object
The following code loads and instantiates a class based on its assembly and class name.

Imports System.Reflection

Public Shared Function LoadObject(ByVal sAssembly As String, ByVal sClass As String) As Object Try Dim oAssembly As System.Reflection.Assembly Dim oType As Type Dim oObject As Object

'* load the assembly to use. oAssembly = System.Reflection.Assembly.Load(sAssembly) If oAssembly Is Nothing Then Throw New Exception("Invalid Assembly: '" & sAssembly & "'") End If

'* load the Type from the classname oType = oAssembly.GetType(sAssembly & "." & sClass) If oType Is Nothing Then Throw New Exception("Invalid Class: '" & sClass & "' for Assembly: '" & sAssembly & "'") End If

'* create the object oObject = Activator.CreateInstance(oType) Return oObject Catch ex As Exception // do whatever Return Nothing End Try

End Function

Given the class, SSNLDLL.vips.ssnl.controls.DatabaseControl, you would specify the assembly as "SSNLDLL" and the class name as "vips.ssnl.controls.DatabaseControl".

Call LoadObject("SSNLDLL", "vips.ssnl.controls.DatabaseControl")

Read Content from File
To do anything with XML, you need to include the following namespace:

Imports System.Xml

Use the following code to instantiate a new XML Document object.

'* english question XML Dim _xDoc As XmlDocument = New XmlDocument

Use the following code to load an XML file into the object. Note that the MapPath attribute is used from a System.Web.UI.Page to get a folder name whose root is the calling folder.

'* load XML _xEnglish.Load(Me.MapPath("Templates") & "\" & sFilename)

XPath
XPath is a way to find information in an XML file. Below is a tutorial.

http://www.w3schools.com/xpath/

Run Code Only in Debug Mode
Use the following if statement to conditionally run code in the designer versus when the application is actually executing. This code makes sure the contents of the if block only executes during application time, not during design time.

If Me.DesignMode = False Then ' do your business here End If

This code is useful in an inherited form that has uses a base classs that has a Load event.

DB2

 * 1)
 * 2)
 * 3)

Create a DB2 Function
Displayed below is an example of a declaration of a function:

CREATE FUNCTION GetPhone(iID INT) RETURNS VARCHAR(10)

READS SQL DATA DETERMINISTIC NO EXTERNAL ACTION BEGIN ATOMIC

DECLARE sPhone VARCHAR(10);

-- set 10 digit or less phone number SET sPhone = RTRIM(CHAR(BIGINT(RAND * 10000000000)));

-- if number < 10 digits, pad with 0's   SET sPhone = sPhone || REPEAT('0', 10 - LENGTH(sPhone));

-- phone number RETURN SELECT sPhone FROM SYSIBM.SYSVERSIONS; END

Displayed below is how to delete the function definition:

DROP FUNCTION GetPhone;

Create a DB2 Stored Procedure
CREATE PROCEDURE Z SPECIFIC Z  LANGUAGE SQL MODIFIES SQL DATA DYNAMIC RESULT SETS 0

BEGIN

-- Declare variables DECLARE iCount INT;

-- Declare cursors SET iCount = (SELECT COUNT(*) FROM TBLZIPCODE);

UPDATE TMP_MEMBER_ID_CROSSWALK2 SET ZIP_ID = iCount;

END

CALL Z

DROP PROCEDURE Z

Database

 * 1)
 * 2)
 * 3)
 * 4)

Oracle DB

 * 1)

Get Top n Items
When wanting return only a set number of rows similar to the Top n function in MS SQL add the following where statement.

where rownum <= 10

Full example: select Member_ID from member where rownum <= 10

ASP .NET

 * 1)
 * 2)
 * 3)

XML

 * 1)
 * 2)

Reflection

 * 1)

Debugging

 * 1)