Search

Custom Search

Monday, March 31, 2008

Conditionally add a control on Markup during Databinding

There are cases that we want to add a control conditionally if the field agrees on some condition. This is common on dataaware controls were we present our data. I come accross with this problem where I need to render a Hyperlink button when HistoryId is not null in the database. We usually write the code to bind controls like as follows:

<asp:LinkButton CommandName = '<%#
string.Format("History{0}",Eval("CustomerHistoryId")) %>' runat= "server"
OnClick= "ViewCustomerHistory"> View ... </asp:LinkButton>


The following code above will just add linkbutton controls on all records fetch, but this is not the desired output. What I did is to use tertiary operator and set the style to none if HistoryId is null. The code below shows how I did it:

<asp:LinkButton CommandName = '<%#
string.Format("History{0}",Eval("CustomerHistoryId")) %>' runat= "server"
Style ='<%# Eval("CustomerHistoryId").ToString() == "" ? "Display:None"
: "Display:Inline"%>'
OnClick= "ViewCustomerHistory"> View ...
</asp:LinkButton>

Tuesday, March 18, 2008

using JSON in C#.Net

I never thought that my objects can be accessed in javascript until I discover JSON. JSON or Javascript Object Notation is a simple data transformation from your serverside objects to clientside scripting.
A string like
'person = {"firstName": "Brett", "lastName":"McLaughlin", "email":
"brett@newInstance.com" } '

can be deserialized into JSON object using eval. Then can be accessed as simple as person.firstName. Below is a sample javascript code:

var myJSON = 'person = {"firstName": "Brett", "lastName":"McLaughlin",
"email": "brett@newInstance.com" } ' ;
eval(myJSON);
alert(person.firsName + ' ' + person.lastName + ', ' + person.email);

the code above will give a pop-up message saying:
Brett McLaughlin, brett@newInstance.com

Now how can we create a JSON object in C#.net and be able to pass it on clientside? Here is the procedure:

1. Create a struct to be serialized as JSON:

public struct person
{
public string firstName;
public string lastName;
public string email;
}

2. Create a method that will return a serialized JSON object

public string SerializedJSON(object toSerialize, string className)
{
System.Web.Script.Serialization.JavaScriptSerializer jss;
jss = new System.Web.Script.Serialization.JavaScriptSerializer();
objectSystem.Text.StringBuilder sbControls = new
System.Text.StringBuilder();
jss.Serialize(toSerialize, sbControls);
return className + "=" + sbControls.ToString();
}

3. then register the clientside method to call with parameter as JSON serialized string. This may be an attributes of controls event or a a method call on clientside

//as attribute
string serialized = SerializedJSON(person, "person");
button1.Attributes.add("onClick",
"parseJSON('"+ serialized +"')");
//as method call on serverside

ScriptManager.RegisterClientScriptBlock(this,this.GetType(),
"JS
Method Call","parseJSON('"+ serialized +"')", true);

4. Now with those server side code, here is how we do the clientside:

function parseJSON(serializedJSON)
{
eval(serializedJSON);
alert(person.firsName + ' ' + person.lastName + ', ' +
person.email);
}

You can even use JSON on array of objects and be able to call it by index on clientside like

person[0].firstName;
or
person[1].firstName;

I usually use list<object> then serialize it to JSON. And there are many things to explore on JSON and that is what I'm doing right now! Thanks JSON!

links: http://www-128.ibm.com/developerworks/web/library/wa-ajaxintro10/

Tuesday, March 11, 2008

Adding Javascript file programmatically in C#

Just got it from ASP.Net Forum and found it very useful for me. Thanks to NC01!!

string resourceName = "YourJavaScriptFile.js";
if ( !this.Page.IsClientScriptBlockRegistered(resourceName) )
{
// If the file is on the server root:
// Ex:
http://localhost/YourJavaScriptFile.js
string filePath = System.Web.HttpContext.Current.Request.Url. GetLeftPart( UriPartial.Authority) + "\\" + resourceName;
// If the file is on the app virtual root:
// Ex: C:/Inetpub/wwwroot/Test/YourJavaScriptFile.js
string filePath = System.AppDomain.CurrentDomain.BaseDirectory + resourceName;
// If the file is in a sub-folder inside of the app virtual root:
// Ex: C:/Inetpub/wwwroot/Test/ScriptFiles/YourJavaScriptFile.js
string filePath = System.AppDomain.CurrentDomain.BaseDirectory + "ScriptFiles/" + resourceName;
string scriptText = string.Format( System.Globalization.CultureInfo.InvariantCulture, "\n<script type='text/JavaScript' src='{0}'></script>\n", filePath);
this.Page.RegisterClientScriptBlock(resourceName, scriptText);
}

Note that RegisterOnSubmitStatement, RegisterStartupScript, RegisterClientScriptBlock, etc have changed since version 1.1 and you will get a compiler warning with the above. See http://msdn2.microsoft.com/en-us/library/system.web.ui.clientscriptmanager.aspx for more info.

I just highlighted the property of getting the BaseURL, System.AppDomain.CurrentDomain.BaseDirectory , i've been looking for it before and finally I got an answer. What I do before is put my baseURL in web.Config, but everytime I deploy it on different test site I have to change the value on web.config before publishing.

Monday, March 10, 2008

Selecting specific Row and Size in a record using ROW_NUMBER()

Sometime we want to select row that is between a record like for example record 10 to 20. We cannot achieve that by using TOP for it will just get the records starting from top. We can write it like this:
SELECT TOP 20 * FROM TABLE1 WHERE FIELDID NOT IN (SELECT TOP 10 FIELDID FROM TABLE1)
But performance wise this is not advisable. I recommend using ROW_NUMBER() OVER function in MSSQL to assign ROWID from the table then selecting the ROW in where clause. Here is the syntax below:

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderField) as row FROM Table1) a WHERE row > (@RecordNo-1) and row <= (@RecordNo + @RecordSize-1)

where:
@RecordNo is the starting row
@RecordSize is the no of rows you want to retrieve starting from @RecordNo
OrderField is the order you want for the record before extraction

So to select records 10 to 20 you write it as:

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderField) as row FROM Table1) a WHERE row > (9) and row <= (19)

Saturday, March 8, 2008

Adding triggers for your Table

Triggers are very useful specially on registering audits on changes on tables.
Triggers has INSERTED and DELETED object that can be used to get the activity
made on the table and be able to save it on your audit tables.
These objects has the same schema as your table so you can join it with other table to get other information you needed

Below is the syntax in adding trigger on your table and how to save it on your audit.

CREATE TRIGGER [Trigger_all] ON [dbo].[SampleTable]
For INSERT, UPDATE, DELETE
AS
IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
BEGIN
--procedure for Update
INSERT INTO SampleTable_Audit VALUES(SELECT *, 'EDITTED', timestamp() FROM DELETED)
END
ELSE IF EXISTS(SELECT * FROM INSERTED)
BEGIN
--Procedure for insert
INSERT INTO SampleTable_Audit VALUES(SELECT *, 'Inserted', timestamp() FROM INSERTED)
END

ELSE IF EXISTS(SELECT * FROM DELETED)
BEGIN
--Procedure for delete
INSERT INTO SampleTable_Audit VALUES(SELECT *, 'EDITTED', timestamp() FROM DELETED)
END


Calling Server Side Method with postback in C#

I've been researching for how to call server side methods over the internet but has been frustrated for all I searched was calling server side method without postback using pagemethods or using the ICallBackEventHandler that works like webservice calls in AJAX. I've been pondering time to solve this and found a solutions for myself.

Here is how I did it:
  • Create a linkbutton on design and write an event for this. This event will be called on client side by some javascript function.
  • Register the Server Side Function by using the following:
StringBuilder jscript = new StringBuilder();
jsscript.append("function callServerEvent(){");
jsscript.append(ClientScript.GetPostBackEventReference (LinkButton1, "") + ";}");
RegisterClientScriptBlock("call server", jsscript.ToString());

  • Do this on Page_Load event, then you can now call callServerEvent() from javascript will do a post back and update controls.
  • To hide the control on rendering the page set the display:none on LinkButon's style.
I usually use this in calling server
side function to update controls inside my updatepanel. Using Pagemethods are used for calling static methods and using ICallBackEventHandler is for calling serverside method but with no postback at all.

Selecting the last record entered on audit trail for each Item

Here is another trick I love and was amazed of what I have achieved!
Audit trail accumulates all activities happened on the system or movements happened on the item. It is not that easy to extract from the audit trail the last movement of all the items, unless you individually query on each item and get the max date then transfer it to another table, a temporary maybe, then go to the next item and repeat the process until you have the last item. But I found a way to query it on just a single query string:

SELECT A.ProductID, B.Description,
LTRIM(RIGHT(MAX(CONVERT(VARCHAR(19), A.AuditTime, 120) +
RIGHT(REPLICATE(' ',100) + Activity,100)),100)) AS LastActivity FROM AuditTrail A INNER JOIN Products B ON A.ProductID= B.ProductID GROUP BY A.ProductID, B.Description

Here is how this works:
  1. Convert the Audit Time to YYYY-MM-DD HH:NN:SS format, this will ensure the order of activities according to time it was written.
  2. RIGHT(REPLICATE(' ',100) + Activity,100)) This will put leading spaces on activity to make the string a 100 character. eg. '10 items sold' will become ' 10 items sold' with 87 leading spaces.
  3. Concatenate the 19 character datetime to the 100 character Activity making it a sum of 119 characters
  4. Get the MAX value of the 119 character field that we have created. This will get the Max value we want since it will be alphabetically arranged.
  5. Now its time to remove the date and time that we don't want to appear then trim again the leading spaces we added. this is the function of LTRIM(RIGHT( ---,100)) where the function right gets the 100 character activity we created ant LTRIM removes leading spaces.
Now thats it. We got the Activity of the last movement for each product.

How to Concatenate a record into a single field

Sometimes it is useful to create a column on header that contains a concatenated value of its detail. For example we want to display all available sizes of a specific product like this:
Product: Coca Cola
Sizes: 8oz, 12oz, 500ml, 1 Liter, 1.5 Liter

We can achieve that by joining the details then looping on the detail to write it on a single string. But we can concatenate it using the following:

CREATE FUNCTION Sizes
(
@ProductID varchar(50)
)
RETURNS varchar(2000)
AS
BEGIN
DECLARE @Sizes varchar(2000)

SET @Sizes=''
SELECT @Sizes=@Sizes+','+ ProductSizes.Size FROM ProductSizes
WHERE ProductSizes.ProductID=@ProductID

RETURN @Sizes = RIGHT(@Sizes,LEN(@Sizes)-1)
END

This function returns a concatenated value of sizes according to the ProductID

so we can write a query like this:

SELECT ProductID, Description, dbo.Sizes(ProductID) FROM Products

Ordering a record according to search followed by the rest of the record

When searching from grid, commonly the searched record is left on the grid removing all unsearch record. Sometimes we don't want that style to happen. We want to put the searched item on top of the grid and the rest below it.
Here is a trick to do that:

SELECT *, '0'+ SearchField as OrderBy FROM Tables WHERE SearchField like '[value]%'
UNION ALL
SELECT *, '1'+ SearchField as OrderBy FROM Tables WHERE SearchField not like '[value]%' Order By OrderBy

OR

SELECT field1, field2, SearchField,CASE WHEN SearchField like '[Value]%' THEN '0' ELSE '1' END ORDER BY 4,SearchField

HunterWise Project

1. HunterWise Comodities Trading

Type: Web Application

Tools:
  • C#.Net
  • MSSQL
  • AJAX
  • ASP.Net
Duration: February 1, 2008 to May 1, 2008 (May be extended upon clients request)

Client: Hunterwise, USA

Function:Team Lead / Senior Developer(Software Engineer)

Company Attached with : BlastAsia Inc.

BeeLine Project

1. BeeLine Invoice Payment System

Type: WinForm Application

Tools:

  • C#.Net
  • MSSQL
Duration: January 3, 2008 to January 31, 2008

Client: BeeLine, Florida

Function: Database Administration (Software Engineer)
- Optimizations of Stored Procedures and Queries

Company Attached With: BlastAsia Inc.

TSCERES SQL

Description: A Touch Screen Point-of-Sale (POS) System designed for Fast Food Chains and Restaurants

Type: WinForm Application

Tools:
  • Delphi 6
  • MySQL
  • MSSQL (For Corporate Accounts)
  • Crystal Report
Duration: April 19, 2007 - January 3, 2008

Function: Front-End Developer

Clients :
  • Starbucks
  • YellowCab
  • Karate Kid
  • Brent International School Cafeteria
  • British School Cafeteria
  • etc.
Company Attached with: JIMAC Inc.

SMES POS

Description : A System that handles text messages (SMS) and converts it to sales and/or inventory transaction depending on Provided Text Format. A modem is connected to the head office and works as a bridge to accept messages to be processed from satellite stores.

Type: WinForm Application

Tools:
  • Delphi 6
  • MySQL
  • Crystal Report
Duration: April 19, 2007 - January 3, 2008

Function: Front-End Developer

Clients :
  • Hotdog on Stick
  • Kettle and Corn
  • Fruit Magic
  • etc.
Company Attached with: JIMAC Inc.

Friday, March 7, 2008

Student's Form Generator

Description:
A complete Computerized Grading System that involves the following modules:
  1. Class Record - Excel Program that computes raw scores entered by teachers and summarizes all component for each grading. Complete averaging from 1st grading to 4th grading
  2. Summary Sheet - Excel Program that summarizes all grades acquired by the student on each subject and computes his final average. It also has a summary of Attendance for each month. It is linked to the Main Database for record keeping.
  3. Registrar Module - Handles all record keeping of students information and grades for the whole curriculum. Can generate forms like Transcript of records, classlists and other forms needed by DepEd (Department of Education). It also has sectioning facilities to assign and trace students sections on each grade/year level.
Tools:
  • Visual Basic 6
  • MSSQL
  • MSExcel
  • Crystal Report

Duration: May 2004 - May 2005

Function: Created the whole System Development Life Cycle

Client: Paco Catholic School

Company Involve with : Paco Catholic School

Nice Hotel Cashiering System

Description: Hotel Point-of-Sales Accommodation system

Type : WinForm Application

Tools:
  • Visual Basic 6
  • MSSQL
  • Crystal report
Duration: January 2006 - January 2007

Involvement: Created the whole System Development Life Cycle (Free Lance)

Client: Nice Hotel, Philippines

Paco Catholic School's Website

Type: Static Website

Tool:
  • Front Page
  • XML
  • Adobe Photoshop
  • Macromedia Flash
Duration: August 2006 - October 2006

Client: Paco Catholic School, Manila, Phillipines

Company involve with: Paco Catholic School

Electronic Quiz Bee

Description: A Client/Server system, used in a Quiz Bee Programs, that sends question to each team (client) and registers answer back to server to verify answer and accumulate scores.

Tools:
  • Visual Basic 6
  • MSExcel
Used in the following Programs:
  • Paco Catholic School's Young Mind Quiz Whiz
  • Paco Catholic School's Computer Practical Arts Quiz Bee
Company involved with: Paco Catholic School


Online Quiz

Description: A web based quiz which is used by teachers when giving exams

Tools:
  • Visual Basic.Net
  • MSSQL
Client: Paco Catholic School

Company involve with: Paco Catholic School

Opening a new window without toolbars in C#

Here is window.open syntax:

winRef = window.open( URL, name [ , features [, replace ] ] )

The parameters URL, name, features, replace have the following meaning:

URL
String specifying the location of the Web page to be displayed in the new window. If you do not want to specify the location, pass an empty string as the URL (this may be the case when you are going to write some script-generated content to your new window).

name
String specifying the name of the new window. This name can be used in the same constructions as the frame name provided in the frame tag within a frameset .

features
An optional string parameter specifying the features of the new window. The features string may contain one or more feature=value pairs separated by commas.

replace
An optional boolean parameter. If true, the new location will replace the current page in the browser's navigation history. Note that some browsers will simply ignore this parameter.

The following features are available in most browsers:
toolbar=0/1
-Specifies whether to display the toolbar in the new window.
location=0/1
-Specifies whether to display the address line in the new window.
directories=0/1
-Specifies whether to display the Netscape directory buttons.
status=0/1
-Specifies whether to display the browser status bar.
menubar=0/1
-Specifies whether to display the browser menu bar.
scrollbars=0/1
-Specifies whether the new window should have scrollbars.
resizable=0/1
-Specifies whether the new window is resizable.
width=pixels
-Specifies the width of the new window.
height=pixels
-Specifies the height of the new window.
top=pixels
-Specifies the Y coordinate of the top left corner of the new window. (Not supported in version 3 browsers.)
left=pixels
-Specifies the X coordinate of the top left corner of the new window. (Not supported in version 3 browsers.)


Calling the window.open on serverside:

RegisterClientScriptBlock("Open Window","<script> window.open('sample.aspx','sample','toolbar=No, width=300, height=190, resizable=No, top=400, left=600')</script>");

To Create a page on the fly:

StringBuilder jscript = new StringBuilder();
jscript.append("<script>");
jscript.append("win=window.open('','login',");
jscript.append("'toolbar=No, width=300, height=190, resizable=No, top=400, left=600');");
jscript.append("win.response.write('<BODY>');");
jscript.append("win.response.write('this page is created on the fly!');");
jscript.append("win.response.write('</BODY>');");
jscript.append("</script>");
RegisterClientScriptBlock("Open Window", jsscript.ToString());


reference: http://www.javascripter.net/faq/openinga.htm

Adsense Banner