Filter & Sort DataTable using LINQ

You need to add reference to System.Data.DataSetExtensions to be able to use AsEnumerable() on a datatable.

Dim dt As New DataTable

dt.Load(myDataReader)

'get name of distinct root taxonomies

Dim root_names As IEnumerable(Of String) = From myRow In dt.AsEnumerable() _

Select myRow.Field(Of String)("root_name") Distinct

'get a list of the root taxonomy objects based on the name

Dim list As New List(Of Taxon.Taxon)

For Each root_name As String In root_names

Dim t As Taxon.Taxon = Taxon.TaxonDAL.Instance.GetOnName(root_name)

If t IsNot Nothing Then

list.Add(t)

End If

Next

'sort the root taxons by Product_DisplayOrder

list.Sort(Function(p1, p2) p1.Product_DisplayOrder.CompareTo(p2.Product_DisplayOrder))

For Each t As Taxon.Taxon In list

Dim currentRootName As String = t.name

'get the children of the current root taxon

Dim drRows As IEnumerable(Of DataRow) = From myRow In dt.AsEnumerable() _

Where myRow.Field(Of String)("root_name") = currentRootName _

Order By myRow.Field(Of Double)("Product_DisplayOrder") Ascending _

Select myRow

For Each rv As DataRow In drRows

Dim ThisTaxonId As String = rv("taxon_id")

Next

Next

Reference:

LINQ query on a DataTable

While loop in sql

Here is how to do while loop in sql:

--script to copy UserOrganisationId record from User table to User_Organisation

Declare @UserID as int

Declare @MaxUserID as int

Declare @OrgID int

select @UserID = MIN(intuserid) from [user]

select @MaxUserID = MAX(intuserid) from [user]

WHILE (@UserID <= @MaxUserID)

BEGIN

if exists(Select * from [user] where intuserid=@UserID)

begin

select @UserID=intuserid, @OrgID=UserOrganisationId

from [user] where intuserid = @UserID

if @orgID is not null and @orgID > 0

begin

INSERT INTO User_Organisation (UserID, OrganisationID, IsDefaultOrganisation)

VALUES (@UserID, @OrgID, 1)

end

end

set @UserID=@UserID+1

END

-- alternative script

INSERT INTO User_Organisation (UserID, OrganisationID, IsDefaultOrganisation)

SELECT intUserID, UserOrganisationId, 1

FROM [User]

WHERE (UserOrganisationId IS NOT NULL) AND (UserOrganisationId > 0)

Get comma seperated string

This is how you can get comma seperated string:

Public Function GetUserOrganisationIds(ByVal userID As Integer) As String

Dim dbCon As New SqlConnection(ConnectionString)

dbCon.Open()

Dim cmd As New SqlCommand("GetUserOrganisationIds", dbCon)

cmd.CommandType = CommandType.StoredProcedure

Try

Dim dr As SqlDataReader = cmd.ExecuteReader

Dim orgList As New List(Of String)

While dr.Read

orgList.Add(dr("OrganisationID").ToString)

End While

If orgList.Count > 0 Then

Return String.Join(",", orgList.ToArray())

Else

Return ""

End If

Catch ex As Exception

Throw (ex)

Finally

dbCon.Close()

End Try

End Function

Here is how to do it in sql:

--declare holder var

DECLARE @list VARCHAR(8000)

--build comma separated list

SELECT @list = COALESCE(@list + ',', '') + CAST(DZName AS VARCHAR(5) )

FROM ClubDZlocation where ClubId = 201

--show results

SELECT @list AS 'list'

JQuery Validation

Just wanna share some JQuery validation sample code which might be useful:

//if you put CssClass="required" on the dropdownlist,

//then JQuery's default required field validator will valiate the dropdownlist,

//but it only returns false if there is no items in the dropdownlist

// -------------------Custom function: Required Validator for Drop Down List----------------------------------------------------

jQuery.validator.addMethod("Validate_ddls", function(value, element) {

if (null != element) {

var iValue = new Number(element[element.selectedIndex].value);

//if ddlColumns is not populated, selectedIndex will be -1

if (iValue <= 0 iValue == NaN) {

return false;

}

else {

return true;

}

}

else {

return false;

}

}, jQuery.validator.messages.required);

// -------------------Custom function: Positive_Integer_Exclude_Zero----------------------------------------------------

jQuery.validator.addMethod("Positive_Integer_Exclude_Zero", function(value, element) {

return this.optional(element) /^[1-9]\d*$/.test(value);

}, "Positive Integer Only");

// -------------------Custom function: Positive_Integer----------------------------------------------------

jQuery.validator.addMethod("Positive_Integer", function(value, element) {

return this.optional(element) /^[0-9]\d*$/.test(value);

}, "Positive Integer Only");

// -------------------Custom function: Positive_Decimal_Exclude_Zero----------------------------------------------------

jQuery.validator.addMethod("Positive_Decimal_Exclude_Zero", function(value, element) {

return this.optional(element) /(^\d*\.?\d*[1-9]+\d*$)(^[1-9]+\d*\.\d*$)/.test(value);

}, "Invalid Amount");

// -------------------Custom function: Positive_Decimal----------------------------------------------------

jQuery.validator.addMethod("Positive_Decimal", function(value, element) {

return this.optional(element) /(^\d*\.?\d*[0-9]+\d*$)(^[0-9]+\d*\.\d*$)/.test(value);

}, "Invalid Amount");

// -------------------Custom function: Validate_Email----------------------------------------------------

jQuery.validator.addMethod("Validate_Email", function(value, element) {

return this.optional(element) /^[\w-\.]+@([\w-]+\.)+[\w-]{2,4}$/.test(value);

}, "Invalid Email address");

// -------------------Validate the whole Form----------------------------------------------------

$(document).ready(function() {

$("#aspnetForm").validate({

rules: {

<%=txtTag.UniqueID %>:{

required: true

},

<%=ddlTables.UniqueID %>:{

Validate_ddls: true

},

<%=ddlColumns.UniqueID %>:{

Validate_ddls: true

}

},

messages: {

<%=txtTag.UniqueID %>:{

required: "Tag is required"

},

<%=ddlColumns.UniqueID %>:{

required: "Field is required"

}

}

});

});

<script type="text/javascript" src="../include/APF_js/jquery-1.3.2.js"></script>

<script type="text/javascript" src="../include/APF_js/jquery.validate.js"></script>

DesignMode - fix body tag as literal control

Sometimes, you might want to put html body tag in a literal control. Then you lost intellisense from Visual Studio. You can use DesignMode property to fix it:

<% If DesignMode Then%><body>

<%

Else%>

<asp:Literal ID="litBodyTag" runat="server" />

<% End If%>

Load balancing issues for ASP.NET applications

Rencently, our company started to change our hosting to adapt cluster servers and Mirrored SQL Servers. We just used DFS replication, we did not use a load balancer server.

There are a couple of issues we need to address because of the change:

• when put a object in session, make sure it is Serializable, so later in the clustered environment, if you manage session in sql server, it can be stored in sql server
• We were using a third party control for uploading images called I-Load which created some issues with temp image folder, luckily we fixed it. So when buying third-party control like ILoad, make sure it supports web farm
• We were using a third party control called DynamicControlsPlaceholder which does not work in cluster environment until we modified it a bit to make it work
• view state needs to have shared machinekey set up in web.config for web farm environment (in iis7 use sharekey section to share key with other web servers in the farm, then use iis7 to generate unique machinekey for every application in the iis, so that applications do not interrupt with each other)
• Cache & Application variable
• The cached object is stored in process, which means it doesn’t persist if the application domain is restarted and it can’t be shared between computers in a web farm.
• Once a worker process recycles, the entire Cache is lost because it was within this process.
• Microsoft has tried to fix this a bit with SQL Server cache invalidation (ASP.NET Cache SqlCacheDependency), but it could have potential performance issue and according to Scott Watermasysk he has not seen many be successful with this approach.
• There are a couple third party tools to fix this: Memcached ScaleOut NCache SharedCache
• The good news is Microsoft is making some strides here. They are working on an out of processing caching component called Velocity.
• We have to get rid of cache and application variables to address the problem

References:

ASP.Net Caching Is Too Easy

Delete files older than certain period

Public Shared Sub ClearTemporaryFiles(ByVal folderPath As String, ByVal maxLifeTime As Integer)

Dim timeLimit As DateTime = DateTime.Now.AddMinutes(0 - maxLifeTime)

Dim filePaths() As String = System.IO.Directory.GetFiles(folderPath, "*.jpg")

For i As Integer = 0 To filePaths.Length - 1

Dim filePath As String = filePaths(i)

If (System.IO.File.GetLastWriteTime(filePath) < timeLimit) Then

Try

System.IO.File.Delete(filePath)

Catch

End Try

End If

Next

End Sub

Handle file upload timeout

Put the following in the Global.asax.vb, it will check the MaxRequestLength and send you to the error page before uploading the file.

Sub Application_BeginRequest(ByVal sender As Object, ByVal e As EventArgs)

' Fires at the beginning of each request, session not available here

'check for file upload size,

'MaxRequestLength: Int32, the maximum request size in kilobytes. The default size is 4096 KB (4 MB).

'ContentLength: Int64, the number of bytes of data to send to the Internet resource.

If ConfigurationManager.GetSection("system.web/httpRuntime").MaxRequestLength IsNot Nothing Then

Dim maxSize As Integer = CInt(ConfigurationManager.GetSection("system.web/httpRuntime").MaxRequestLength)

maxSize = maxSize * 1024

If Request.ContentLength > maxSize Then

'do not redirect to itself, it will create an infinite loop

Response.Redirect("~/ErrorPage.aspx?ErrMsg=You have reached the maximum upload file limit, please try again!")

End If

End If

End Sub

Reference:
Dealing With the ASP.Net Upload File Size Problem

Javascript: Clear Form Elements

function ClearValue() {

var myContainer = document.getElementById("myFormElement");

var options = myContainer.getElementsByTagName('input');

for (i = 0; i < options.length; i++) {

var opt = options[i];

if (opt.type == 'text') {

opt.value = "";

}

else if (opt.type == 'checkbox' opt.type == 'radio') {

if (opt.checked) {

opt.checked = false;

}

}

}

var options = myContainer.getElementsByTagName('textarea');

for (i = 0; i < options.length; i++) {

var opt = options[i];

opt.value = "";

}

var options = myContainer.getElementsByTagName('select');

for (i = 0; i < options.length; i++) {

var opt = options[i];

opt.selectedIndex = -1;

}

}

Reference:
Using JavaScript to reset or clear a form

sort table randomly

The following sample shows you on how to sort sql table randomly:

select * from(

select top 20 firstname, lastname from [user]

) as result order by NEWID()

identity_insert

The following example shows you on how to insert explicit value into the identity column of a table:

set identity_insert [MyModuleElement] on

Insert into MyModuleElement

(intID, strName, strDescription, intControllerID)

Values

(48, 'test name', 'desc', 46)

set identity_insert [MyModuleElement] off

Get the current page name in javascript

<script type="text/javascript">

var sPath = window.location.pathname;

var sPage = sPath.substring(sPath.lastIndexOf('/') + 1);

alert(sPage);

</script>

Reference:

Get the current page name

Javascript refresh page

If you want to use javascript to refresh the page, you can use

• location.reload(); => post request, same as click the refresh button on the browser
• window.location=window.location; => get request
• self.location=self.location; => get request

If you want to use javascript to redirect to another page, you can use

• location.replace('webform1.aspx'); => get request, does not create a history entry so browser back button disabled if no history before
• location.href='webform1.aspx'; => get request, create a history entry so browser back button works
• window.location='webform1.aspx'; => get request, create a history entry so browser back button works

IE buttons

• Back button => get request
• Forward button => post request
• Refresh button => post request

If you get an IE error saying 'To display the webpage again, Internet Explorer needs to resend the information you've previously submitted', you can use window.location=window.location; to refresh the page instead of location.reload();

Sample code:

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="PostMethod_Test._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title></title>

</head>

<body>

<form id="form1" runat="server">

<div>

<asp:Literal ID="litJavascript" runat="server"></asp:Literal>

<div>

Post:

<div>

<asp:TextBox ID="txt" runat="server"></asp:TextBox>

Post Method:<asp:Button ID="btnPost" runat="server" Text="PostBack" />

</div>

</div>

<hr />

<div>

Redirect:

<div>

Get Method:<asp:Button ID="btnRedirect" runat="server" Text="ResponseRedirect" />

</div>

<div>

Get Method:<a href="javascript:location.replace('webform1.aspx');">JS Location Replace</a>

(does not create a history entry so browser back button disabled if no history before)

</div>

<div>

Get Method:<a href="javascript:location.href='webform1.aspx';">JS Location Href</a>

(create a history entry so browser back button works)

</div>

<div>

Get Method:<a href="javascript:window.location='webform1.aspx';">JS Window Location</a>

(create a history entry so browser back button works)

</div>

<div>

Get Method: IE Back button

</div>

<div>

Post Method: IE Forward button

</div>

</div>

<hr />

<div>

Refresh:

<div>

Get/Post Method:<a href="javascript:location.reload();">JS Location Reload</a> (if

there is any control in the form then after the form was submitted for the first

time, if you click this, it will be a post method coming with the IE alert)

</div>

<div>

Get Method:<a href="javascript:window.location=window.location;">JS Window Location</a>

(does not create a history entry) (if there is any control in the form then after

the form was submitted for the first time, if you click this, it will still be a

get method which means the form will not be submitted again)

</div>

<div>

Get Method:<a href="javascript:self.location=self.location;">JS Self Location</a>

(Same as above)

</div>

<div>

Get/Post Method: IE Refresh button - same as location.reload()

</div>

</div>

<hr />

<div>

Open New Window:

<div>

No Method:<a href="javascript:var a = window.open('webform1.aspx');">JS Window Open</a>

(just open)

</div>

<div>

Post Method for parent page:<asp:Button ID="btnOpen" Text="Open Window" runat="server" />

</div>

</div>

</div>

</form>

</body>

</html>

Partial Public Class _Default

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub

Private Sub btnRedirect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRedirect.Click

Response.Redirect("Default.aspx")

End Sub

Private Sub btnPost_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPost.Click

End Sub

Private Sub btnOpen_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnOpen.Click

Dim mstrMessage As New StringBuilder("")

mstrMessage.AppendFormat("<script type=""text/javascript"" >{0}", ControlChars.CrLf)

mstrMessage.AppendFormat(" window.open(""webform1.aspx"");{0}", ControlChars.CrLf)

mstrMessage.AppendFormat(" </script>")

litJavascript.Text = mstrMessage.ToString

End Sub

End Class

Reasons to Use Proxy Server

Increased internet security since:

• Users are authenticated
• Client requests are filtered
• All content, inbound and outbound, is validated
• All access is logged
• All of the internal network details are hidden
• Internet access performance is improved

Create a screenshot image of a web page

I use HtmlCapture to capture a screenshot of a web page:

Imports HtmlCapture

Module Module1

Sub Main()

CreatePreviewImage()

End Sub

Private Sub CreatePreviewImage()

Dim theURL As String = "http://www.google.com.au/"

Dim snap As New SnapShooter

Dim snapResult As HtmlCaptureResult

snap.SetRegInfo("username", "licencekey")

snap.SetDelayTime(6000)

snap.SetTimeOut(15000)

snap.SetMinBrowserSize(SnapDimensions.BrowserWidth, SnapDimensions.BrowserHeight)

snap.SetClipRect(0, 0, SnapDimensions.ClipWidth, SnapDimensions.ClipHeight)

snap.SetThumbSize(SnapDimensions.ThumbWidth, SnapDimensions.ThumbHeight, True)

snap.SetJpegQuality(CByte(SnapDimensions.JpegQuality))

snapResult = snap.SnapUrl(theURL)

If snapResult = HtmlCaptureResult.HCR_SUCCESS Then

' success, save the image as a JPEG

snap.SaveImage("screenshot.jpg")

Else

' timeout, so throw error

Dim ex As New Exception("Timeout on capturing.")

Throw ex

End If

End Sub

Public Enum SnapDimensions As Integer

ThumbWidth = 200

ThumbHeight = 150

BrowserWidth = 1024

BrowserHeight = 768

ClipWidth = 1024

ClipHeight = 768

JpegQuality = 70

End Enum

End Module

Reference:

HtmlCapture Support

Clear form values

The following code will clear values entered in the form, currently only works with drop down list and textbox, you can expand it to cater for more controls.

Private Sub btnClear_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClear.Click

ClearValues(Me.FindControl("MyControlID"))

End Sub

Private Sub ClearValues(ByVal container As Control)

For Each ctl As Control In container.Controls

Dim textBox As TextBox = TryCast(ctl, TextBox)

If textBox IsNot Nothing Then

If textBox.Text.Trim().Length <> 0 Then

textBox.Text = ""

End If

End If

If TypeOf ctl Is UI.WebControls.DropDownList Then

Dim ddl As UI.WebControls.DropDownList = DirectCast(ctl, UI.WebControls.DropDownList)

Dim li As ListItem = ddl.Items.FindByText("(none)")

If li IsNot Nothing Then

ddl.SelectedValue = li.Value

End If

li = ddl.Items.FindByText("Select")

If li IsNot Nothing Then

ddl.SelectedValue = li.Value

End If

End If

If ctl.Controls.Count > 0 Then

ClearValues(ctl)

End If

Next

End Sub

Save a CSV File in Browser

Private Sub lbCSVTemplate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbCSVTemplate.Click

If File.Exists(Server.MapPath("~/CSVUploaded/template.csv")) Then

Response.ContentType = "text/csv"

'Some browsers open the file directly in browser, so this will cause a file "Save as" dialogue to appear.

Response.AppendHeader("Content-Disposition", "attachment; filename=template.csv")

Response.TransmitFile(Server.MapPath("~/CSVUploaded/template.csv"))

Response.End()

Else

lblMsg.Text = MessagesDAL.Instance.Get_MessageByID(Messages.MessageCodes.CSVTemplateNotAvailable)

lblMsg.ForeColor = Drawing.Color.Red

End If

End Sub

Reference:
Downloading a File with a Save As Dialog in ASP.NET

robocopy

• robocopy, or "Robust File Copy", is a command-line directory replication command.
• It was available as part of the Windows Resource Kit, and introduced as a standard feature of Windows Vista and Windows Server 2008.
• Robocopy is notable for capabilities above and beyond the built-in Windows copy and xcopy commands.
• You can script the commands and save them as .cmd files to deploy asp.net files to staging site or live site.

Command Examples:

• Copy test.dll from bin folder under folder1 to bin folder under folder2, if test.dll has already existed in folder2, then it will be overwritten. It will not delete any files in folder2 which do not extist in folder1.
• robocopy C:\folder1\bin C:\folder2\bin test.dll
• Copy content from test to test, including subfolders, excludes folders named .svn, excludes files with .vb extension. If test folder does not exist in folder2, it will create one for you.
• robocopy C:\folder1\test C:\folder2\test /S /XD .svn /XF *.vb
• Create test folder only
  • robocopy C:\folder1\test C:\folder2\test /xf *.*

Reference:

robocopy

Disable Table Constraints

The following code will:

• Disable/Enable All Table Constraints
• E.g. if you have an user table and an UserLog table, UserLog have a foreign key which reference the UserID in the user table.
• Now you can disable the foreign key Constraint on the UserLog table and insert a userid which does not exist in the user table into the UserLog table. After that you can enable the constraint.

CREATE PROCEDURE [dbo].[procDisableEnableAllTableConstraints]

@TblName VARCHAR(128),

@IsCheck BIT = 1

AS

DECLARE @SQLState VARCHAR(500)

IF @IsCheck = 0

BEGIN

SET @SQLState = 'ALTER TABLE [' + @TblName + '] NOCHECK CONSTRAINT ALL'

END

ELSE

BEGIN

SET @SQLState = 'ALTER TABLE [' + @TblName + '] CHECK CONSTRAINT ALL'

END

EXEC (@SQLState)

Public Shared Sub DisableEnableAllTableConstraints(ByVal tableName As String, ByVal isCheck As Boolean)

Dim dbCon As New SqlConnection(clsConfig.BaseConnectionString)

dbCon.Open()

Dim cmd As New SqlCommand("[procDisableEnableAllTableConstraints]", dbCon)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddWithValue("@TblName", tableName)

cmd.Parameters.AddWithValue("@IsCheck", IIf(isCheck, 1, 0))

Try

cmd.ExecuteNonQuery()

Catch ex As Exception

Throw (ex)

Finally

dbCon.Close()

End Try

End Sub

Reference:

Stored Procedure to Disable/Reenable All Constraints on a Given Table

Get Client IP address

Public Shared Function GetClientIPAddress() As String

Dim strIpAddress As String

Try

strIpAddress = HttpContext.Current.Request.ServerVariables("HTTP_X_FORWARDED_FOR")

If strIpAddress = "" Then

strIpAddress = HttpContext.Current.Request.ServerVariables("REMOTE_ADDR")

End If

Catch

strIpAddress = ""

End Try

Return strIpAddress

End Function