Fransiscus Setiawan | EV Charging & Azure Solution Architect | Sydney - Page 19 of 19 - Technical Insights: Azure, .NET, Dynamics 365 & EV Charging Architecture

Cursor Tutorial in SQL Server

On August 29, 2008

This is the sample of cursor solutions, i will add the explanation a bit later

CREATE PROCEDURE AutoSumEventMembers
AS
SET NOCOUNT ON;

/*This stored procedure is used to update the number of players played on the particular event*/
/*Event selected only based on the completed one or the one where the result has been uploaded*/

/*Declare the variable  that is used to store the value coming from cursor*/
DECLARE @EventID int
DECLARE @TotalResult int

/*Declare the cursor for that particular select statement*/
/*You can fetch more that one column into cursor*/
/*You need to make sure that you have the same number of variables when you start fetching it*/
/*Make sure the variable has the same type as the column as well*/
DECLARE Cur_Events CURSOR
FOR
SELECT
EventID
FROM
Events
WHERE
Finalised = 1

/*Open the cursor*/
OPEN  Cur_Events

/*Start fetching from the cursor and dump it into the variable*/
FETCH     Cur_Events
INTO     @EventID

/*Iterating through the cursor until the end of the cursor*/
WHILE @@Fetch_Status = 0
BEGIN
SET @TotalResult = 0

/*Count how many results have been uploaded for particular event*/
SELECT @TotalResult = COUNT(*) FROM EventResults WHERE EventID = @EventID

/*Update the record*/
UPDATE Events SET NumberOfResults = @TotalResult WHERE EventID = @EventID

/*Fetch the next cursor*/
FETCH     Cur_Events
INTO     @EventID
END

/* Clean up - deallocate and close the cursors.*/
CLOSE     Cur_Events
DEALLOCATE Cur_Events

Controlling PDF document through parameter

By fransiscuss

On August 29, 2008

In Others

It’s a simple thing but might be essential. Sometimes you would like to have a link to your pdf document into a particular page. This is how you can do it. Add “page=” and the page no. This is the example

http://www.adobe.com/prodlist.pdf#page=3

For the full documentation about controlling the page size, you can download it from Adobe directly by clicking here

Querystring in Javascript

By fransiscuss

On August 29, 2008

In Javascript

This is a function to capture querystring in javascript. I used this javascript to control my menu dynamically.


function getQueryString(strParamName){
var strReturn = "";
var strHref = window.location.href;
if ( strHref.indexOf("?") > -1 ){
var strQueryString = strHref.substr(strHref.indexOf("?")).toLowerCase();
var aQueryString = strQueryString.split("&");
for ( var iParam = 0; iParam < aQueryString.length; iParam++ ){
if (
aQueryString[iParam].indexOf(strParamName.toLowerCase() + "=") > -1 ){
var aParam = aQueryString[iParam].split("=");
strReturn = aParam[1];
break;
}
}
}
return unescape(strReturn);
}

Register Javascript to hide Div

By fransiscuss

On August 29, 2008

In C#, Javascript

Sometimes people add javascript into the aspx page instead of using code behind but there is a case when we need to add javascript in the runtime using code behind. This is the example where javascript is used to provide switching of visibility based on the table visibility status. I used this technique to hide another user control when user click expand button. Client ID is required to know the real ID of the user control after rendering. In here we also add onclick event to the image tag.

if (!this.Page.IsStartupScriptRegistered("tableScript"))
{
string script = @"function checkVisibility() "
+ @"{"
+ @"    var element = document.getElementById('" + this.OuterTableDetailSearch.ClientID + @"');"
+ @"    var elementBtn = document.getElementById('" + this.btnExp.ClientID + @"');"
+ @"    if (element.style['visibility'] == 'hidden')"
+ @"{"
+ @"element.style['visibility'] = 'visible';"
+ @"elementBtn.src = '/Images/button_expand.gif';"
+ @"}"
+ @"else"
+ @"{"
+ @"element.style['visibility'] = 'hidden';"
+ @"elementBtn.src = '/Images/image_casestudy_search.gif';"
+ @"}"
+ @"}";

this.Page.RegisterStartupScript("tableScript", script);
}

btnExp.Attributes.Add("OnClick",
               "javascript:checkVisibility()");

Row filter in Dataset

By fransiscuss

On August 29, 2008

In C#

Here it will be useful for someone who would like to filter a particular record in datagrid. You need to create a dataview and filter it. Here, i would like to filter a particular record where the field name is “metakey” and i don’t want to show it when its value is “thumbnail”. So a record where metakey is thumbnail will not be shown at all. It’s a simple thing but it will be useful.

DataSet metaData = new DataSet();
dv = new DataView(metaData.Tables[0]);

gridMeta.DataSource = dv;
gridMeta.DataBind();

dv.RowFilter = ("metakey  'thumbnail'");
//bind it to datagrid

Using wild card to look up in a string C#

By fransiscuss

On August 29, 2008

In C#

This is the class that can be used to look up a keyword from a string. This class is inherited from Regex class.
Base Class

public class ProductLookupRegex : Regex
{
public ProductLookupRegex()
{
//
// TODO: Add constructor logic here
//
}
///
/// Initializes a wildcard with the given search pattern.
///
///
The wildcard pattern to match.
public ProductLookupRegex(string pattern) : base(WildcardToRegex(pattern))
{
}

///
/// Initializes a wildcard with the given search pattern and options.
///
///
The wildcard pattern to match.
///
A combination of one or more
/// .
public ProductLookupRegex(string pattern, RegexOptions options): base(WildcardToRegex(pattern), options)
{
}

///
/// Converts a wildcard to a regex.
///
///
The wildcard pattern to convert.
/// A regex equivalent of the given wildcard.
public static string WildcardToRegex(string pattern)
{
return "^" + Regex.Escape(pattern).
Replace("\\*", ".*").
Replace("\\?", ".") + "$";
}
}

How to use it

ProductLookupRegex productlookup =
                 new ProductLookupRegex(productName,
                                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

foreach (DictionaryEntry de in prodList)
{

if (productlookup.IsMatch(de.Key.ToString().Trim()))
{
productLink = LinkGenerator(de.Value.ToString());
productName = de.Key.ToString();
break; // (sn)
}
}