Learn IT From Vince

Inserting result of Stored Procedure to a table

2011-07-26T11:34:00.004+08:00

I haven't thought that I can use the result of a stored procedure to another stored procedure. If I have this need I usually convert the SP to user define function to return a table then use that inside the SP.

Until I came across to this search where I want to filter the result of the SP_WHO2 by adding where condition on it. SP_WHO2 is a system stored procedure to return record of threads running on the database instance.

Thanks for this code from http://stackoverflow.com/questions/2234691/sql-server-filter-output-of-sp-who2

Declare @Table TABLE(SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProramName VARCHAR(MAX),
    SPID_1 INT,
    REQUESTID INT
)

INSERT INTO @Table EXEC sp_who2

SELECT  *
FROM    @Table
WHERE LOGIN = 'vince'

Simplest way to query XML in MSSQL

2011-02-08T11:11:00.007+08:00

For Accessing XML nodes:

DECLARE @xml xml
SET @xml = N'
         1002008-09-10
         1012008-09-11
       '
SELECT
doc.col.value('ponumber[1]', 'nvarchar(10)') ponumber
,doc.col.value('podate[1]', 'datetime') podate
FROM @xml.nodes('/polist/po') doc(col)

For Accessing element attributes:

DECLARE @xml xml
SET @xml = N'
         
         
       '
SELECT
doc.col.value('@ponumber', 'nvarchar(10)') ponumber
,doc.col.value('@podate', 'datetime') podate

FROM @xml.nodes('/polist/po') doc(col)

For accessing XML arrays:

DECLARE @xml xml
SET @xml = N'
         100
         101
       '
SELECT

Alternate Value of two fields in one column

2010-03-18T20:23:00.004+08:00

I just found a very good tool that can find and replace any files that I selected with the set of old and new values I added all at the same time. I can even select a batch of folders to look for and millions of text that needs to be replaced with just a blink of an eye. This solves our problem with Hyperion where we need to migrate all the reports from an old system to a new system where with an all new sets of account TRULY HORRIBLE. As a programmer we don't want just to sit in my computer and replace each of the account with the new account manually. So when we found a way to automate the conversion we are all so happy that a report that can be converted in one whole day now can be converted in just a few minutes.

The ReplaceText Tool have an 'Import Replace Table' where you can import the set of old value and new value to its table in a text format.

But the problem is the text format is just one column where the old and new values just alternates on each line.

The challenge is how can I write a query where I could have one columns and the value of the old and new alternates on the row.

As fast as the Replace Text tool I have write the solution in SQL script:

SELECT Field FROM
((SELECT '>'+ OldAccount + '<' AS Field,
(((ROW_NUMBER() OVER(Order by ID))-1)*2 + 1) AS FieldOrder
FROM ConvertedAccounts)
UNION ALL
(SELECT '>'+NewAccount + '<' AS Field,
(((ROW_NUMBER() OVER(Order by ID)))*2) AS FieldOrder
FROM ConvertedAccounts)) A
ORDER BY FieldOrder
END

Add abilities to your Objects with Decorator Pattern

2009-01-24T22:53:00.007+08:00

From the word decorator, it adds new functionality (Decoration) to the operations of your instantiated object. If you will try to analyze the design it is like specializing a class through inheritance but here we are doing it on runtime. The Idea is to have an abstract class that will be the base type of all the components. As you instantiate the object you are passing an object of the same type and return a newly instantiated object of the same base type but has additional process on the methods that is being decorated. As you continue on decorating the object by instantiating another decorator object, the functionality accumulates. recursion happens by calling an overidden method until it goes to the first base class that was instantiated.

To understand the concept, imagine += operator in C# which accumulates the sum of numbers:

A = 5;
B = 10;
C = 15;

A += B;
A += C;

on the last line A has a component of B and C (5 + 10 + 15) which we accumalate by the += operator.

Let us see now the design in Action. Below is the UML diagram of the DatabaseProvider Decorator I created:

Click Image to Enlarge

Before I adapted the Decorator Pattern here, the original purpose of the DatabaseProvider is to Provide a Template (IDatabase) of basic Database Execution for all kinds of database. With that, I can use this with my DatabaseProviderFactory without even worrying on what connection I am using. As the need arises, I have thought of a way of discovering Parameters of the stored procedure then extracting the values for that parameter on the Entity being passed on the decorator object. Actually this design is just part of my own MVC implementation which I might also discuss in the future.

Let us see now the codes :

Here is the IDatabase Interface which provides the template for all kind of Database

public interface IDatabase : IDisposable
{
string CommandText { get; set; }
CommandType CommandType { get; set; }
string ConnectionString { get; set; }

void AddParameterWithValue(string parameterName, object value);
void AddParameter(IDataParameter parameter);
IDataReader ExecuteReader();
int ExecuteNonQuery();
object ExecuteScalar();
DataSet ExecuteDataset();
List<T> ExecuteToEntity<T>(T instance) where T : IEntity<T>;

}

This next class is the Decorator Abstract Class which the main functionality is to save an object of IDatabase, which is also his type, that is passed on the constructor.

public abstract class DBDecorator : IDatabase
{
protected IDatabase _database;
public DBDecorator(IDatabase database)
{
_database = database;
}
public string CommandText
{
get{return _database.CommandText;}
set{_database.CommandText = value;}
}
public CommandType CommandType
{
get{return _database.CommandType;}
set{_database.CommandType = value;}
}
public string ConnectionString
{
get{return _database.ConnectionString;}
set{_database.ConnectionString = value;}
}
public virtual void AddParameterWithValue(string parameterName, object value)
{
_database.AddParameterWithValue(parameterName, value);
}

public virtual void AddParameter(IDataParameter parameter)
{
_database.AddParameter(parameter);
}
public virtual IDataReader ExecuteReader()
{
return _database.ExecuteReader();
}
public virtual int ExecuteNonQuery()
{
return _database.ExecuteNonQuery();
}
public virtual object ExecuteScalar()
{
return _database.ExecuteScalar();
}
public virtual DataSet ExecuteDataset()
{
return _database.ExecuteDataset();
}
public virtual List<T> ExecuteToEntity<T>(T instance) where T : IEntity<T>
{
return _database.ExecuteToEntity(instance);
}
public void Dispose()
{
_database.Dispose();
}
}

Now the last class adds two new private methods, DiscoverParameter and AssignParameter which is being called before the execusion of the actual Base Class.

public class SQLDatabaseDecorator<T> : DBDecorator
{
private List _parameterNames = new List();
private T _entity;

public SQLDatabaseDecorator(IDatabase db, T entity)
: base(db)
{
_entity = entity;

}
private void DiscoverParameter()
{
PersistenceManager<list<string>> parameterNamesPM =
new PersistenceManager<list<string>>(PersistIn.Application,
CommandText + "_SQLDatabaseDecorator");
if (parameterNamesPM.Exists())
{
_parameterNames = parameterNamesPM.Get();
}
else
{
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = this.CommandText;
command.CommandType = this.CommandType;

SqlCommandBuilder.DeriveParameters(command);

foreach (SqlParameter param in command.Parameters)
{
_parameterNames.Add(param.ParameterName);
}
parameterNamesPM.Add(_parameterNames);
}
}
}
private void AssignParameters()
{
foreach (string paramName in _parameterNames)
{
if (_entity.GetType().GetProperties().ToList()
.Exists(x => ("@" + x.Name.ToUpper()) == paramName.ToUpper()))
{
PropertyInfo pi = _entity.GetType().GetProperties().ToList()
.Find(x => ("@" + x.Name.ToUpper()) == paramName.ToUpper());
base.AddParameterWithValue(paramName, pi.GetValue(_entity, null));
}
}
}
public override IDataReader ExecuteReader()
{
DiscoverParameter();
AssignParameters();
return base.ExecuteReader();
}
public override int ExecuteNonQuery()
{
DiscoverParameter();
AssignParameters();
return base.ExecuteNonQuery();
}
public override object ExecuteScalar()
{
DiscoverParameter();
AssignParameters();
return base.ExecuteScalar();
}
public override DataSet ExecuteDataset()
{
DiscoverParameter();
AssignParameters();
return base.ExecuteDataset();
}
public override List<U> ExecuteToEntity<U>(U instance)
{
DiscoverParameter();
AssignParameters();
return base.ExecuteToEntity(instance);
}
}

Now the classes are ready, here is how we use the decorator objects :

Company InsertCompany = new Company();
InsertCompany.CompanyName = "My Company";
InsertCompany.Address = "Global City Taguig";
InsertCompany.PhoneNumber = "111-11-11";
InsertCompany.UserId = 1;

string connectionString = "Data Source=.;Initial Catalog=GE;
Integrated Security=false;User Id=sa;Password=password;";

IDatabase target = new SQLDatabaseProvider(connectionString);

using (target = new SQLDatabaseDecorator(target, InsertCompany))
{

target.CommandText = "AddCompany";
target.CommandType = System.Data.CommandType.StoredProcedure;

InsertCompany.CompanyId = Convert.ToInt32(target.ExecuteScalar());
System.Console.WriteLine("New Company Id was inserted with Company Id {0}",
InsertCompany.CompanyId);

}

If you notice, I did not pass any parameters on the DatabaseProvider. The Decorator is doing it for us. It reads the parameter in the database then locate the values on the entity which in this case is the Company Object.

Happy Programming!!!

Integrated Information Management System

2009-01-17T13:02:00.005+08:00

Description: Monitors chassis movement and produce inventory report and billing statements per client.

Type: Web Application

Tools:

C#.Net
MSSQL
AJAX
ASP.Net
Crystal Report

Duration: October

Client: Transcom Cargo Services

Function: Freelance Project

Global Experts

2009-01-17T11:44:00.002+08:00

Description: The Global Expert (GE) online platform is a web based collaboration package that targets consultancy service providers and gives them a convenient and intelligent means to service customers with real-time tools that include voice and imaging. As a complete collaboration package, the GE platform also offers sophisticated features like desktop and file sharing, remote desktop capabilities, white boarding, presence, and event notifications. Customers only have to use their internet browsers and go to the GE website to avail of the service.

Type: Web Application

Tools:

C#.Net
MSSQL
AJAX
ASP.Net

Duration: November 5, 2008 to Present

Function: Senior Web Developer

Company Attached with : Venzo Business Solution

Online Vessel Sounding Report

2009-01-17T11:36:00.004+08:00

Description: A Web application that generates report of the measurements of vessels during and after loading of oil products. It also tracks vessels of there voyage.

Type: Web Application

Tools:

C#.Net
MSSQL
AJAX
ASP.Net
Crystal Report

Client: Petron, Philippines

Involvement: Free lance project

Recursive Call in Stored Procedures

2009-01-17T08:52:00.006+08:00

Recursion is a way of allowing a function to call itself. It results to an iteration of process. Let us have the simple example below that computes for the result of an exponent function:

static void Main(string[] args)
{
double result = exponent(3,4);
Console.WriteLine(result);
Console.ReadKey();
}

private static double exponent(double _base, double power)
{
double result= _base;
if (power > 0)
{
result = result * exponent(_base, power - 1);
}
else
{
result = 1;
}

return result;
}

This is the sample recursive call function in C#. Take note that in recursion there should be a way to terminate the recursion through conditions or else it will bring you to an infinite loop.

Now how can we implement this in SQL? It is allowed to call a stored procedure within a stored procedure. Unfortunately Stored procedure does not return values directly. In case of User Defined Function(UDF), it is not allowed to call a function within a function. Recursions should have a return value in order to determine how the loop will terminate.

The trick here is to use Temporary table to communicate with the calling stored procedure. For simplicity, let's use the same example in getting the result of an exponent:

ALTER PROCEDURE [dbo].[RecursiveCallExponent] (
@base float,
@power float
)
AS
BEGIN

IF(@power > 0)
BEGIN
UPDATE #Result SET Result = Result * @Base
SET @power = @power -1
exec RecursiveCallExponent @base, @power
END
END
GO
-------------------------

ALTER PROCEDURE GetExponent (
-- Add the parameters for the stored procedure here
@base float,
@power float
)
AS
BEGIN
CREATE TABLE #Result (Result float)
insert into #Result (Result) values (1 )
exec RecursiveCallExponent @base, @power

SELECT Result FROM #Result
DROP TABLE #Result
END
GO

GetExponent here is the main stored procedure call. Observe that The Temporary table is created in the main stored procedure to be used by the recursive procedure (RecursiveCallExponent). We cannot create it inside the recursive procedure for it will raise "Object exists" error since we are iterating inside it. Now we can run
"EXEC GetExponent(3,4)"
and will give you a result of 81.

I used recursive on my application to get all consultant with a specific skill in the Heirarchy of skillsets. The scenario is this, I have a Hierachical table which represents the Heirarchy of Skills, e.g. Words, Excel and Powerpoint under MSOffice and MSOffice, Explorer and Media Player under Windows. When I choose specific skills like MSOffice, I should be able to get all consultant under MSOffice including those consultant who has special skills for Powerpoint, Excel and Word and even those under it if it still have sub categories.

Think Generic

2008-06-06T15:26:00.001+08:00

One feature I’m starting to love with C# is the use of generic classes. We usually use some classes like List<T>, Dictionary<key, Value> which uses generic types. It expands the usage of your class through polymorphism. A simple way of how Generic works, think of adding a number or adding a string or adding fractions, what if we add objects? They all have the same verb ADD but have different implementation. A sample code is written below:

public interface GenericMath<T>
{
T Add(T Operand1, T Operand2);
T Subtract(T Operand1, T Operand2);
T Multiply(T Operand1, T Operand2);
T Divide(T Operand1, T Operand2);

}

public class IntegerMath : IGenericMath<int>
{
public int Add(int Operand1, int Operand2)
{
return Operand1 + Operand2;
}

public int Subtract(int Operand1, int Operand2)
{
return Operand1 - Operand2;
}

int Multiply(int Operand1, int Operand2)
{ … }
int Divide(int Operand1, int Operand2)
{ … }

}

Although the example below is not that useful but I hope I have explained clearly how generic works.

As I explore more on generics, I found out that it is so useful on database frameworks like DEV Force, CSLA.Net or ADO.Net Entity Framework. This frameworks uses entities as their records holders. Entities are classes defined automatically by those frameworks to make tables a strong type class. Meaning fields can be access directly as lastName = Customer.LastName unlike before that we uses string then cast it to its typle like this : lastName = (string)Row[“LastName”]. Since every classes that uses the Models returns different entities it is difficult to create a base class that will manipulate Different Entity. A Code below simplifies the Manipulation of data for Dev Force:

public class EntityManagerBase<T>: IEntityManager<T> where T : IdeaBlade.Persistence.Entity
{
private Entity defaultEntity = null;
private EntityColumn primaryKeyField = null;

private PersistenceManager data;
private T entityProperty= null;
private int numLength = 6;
private string uniqueIdPrefix;
private string uniqueNoField;
private bool explicitSave = false;

#region [Properties]
public EntityColumn PrimaryKeyField
{
get { return primaryKeyField; }
set { primaryKeyField = value; }
}
public bool ExplicitSave
{
get { return explicitSave; }
set { explicitSave = value; }
}
public string UniqueNoField
{
get { return uniqueNoField; }
set { uniqueNoField = value; }
}
public int NumLength
{
get { return numLength; }
set { numLength = value; }
}
public T EntityProperty
{
get { return entityProperty;}
set { entityProperty = value;}
}
public PersistenceManager Data
{
get { return data; }
set { data = value; }
}
public EntityManagerBase(PersistenceManager pData)
{
data= pData;
EntityProperty = (T)pData.CreateEntity(typeof(T));
}
#endregion
#region [Virtual Methods]
public virtual bool UpdateEntity(object pPrimaryKey)
{
PrimaryKey primaryKey = new PrimaryKey(typeof(T), pPrimaryKey);
T entity = data.GetEntity<T>(primaryKey);
Initialize();
AssignValues(entity);
return CheckAndSave();
}

public virtual bool UpdateEntity(params object[] pPrimaryKeys)
{
PrimaryKey primaryKey = new PrimaryKey(typeof(T), pPrimaryKeys);
T entity = data.GetEntity<T>(primaryKey);
Initialize();
AssignValues(entity);
return CheckAndSave();
}
public virtual T CreateEntity()
{
T entity = (T)data.CreateEntity(typeof(T));
uniqueNoField = "";
uniqueIdPrefix = "";
ProcessCreate(entity);
return entity;
}
public virtual T CreateEntity(string pUniqueNoField, string pUniqueIdPrefix)
{
T entity = (T)data.CreateEntity(typeof(T));
uniqueNoField = pUniqueNoField;
uniqueIdPrefix = pUniqueIdPrefix;

ProcessCreate(entity);
return entity;
}
public virtual bool DeleteEntity(object pPrimaryKey)
{
SaveResult result;
PrimaryKey pk = new PrimaryKey(typeof(T), pPrimaryKey);
Entity ent = Data.GetEntity(pk);
ent.Delete();
result = Data.SaveChanges();
Data.Clear();
return result.Ok;
}
public virtual bool DeleteEntity(params object[] pPrimaryKeys)
{
SaveResult result;
PrimaryKey pk = new PrimaryKey(typeof(T), pPrimaryKeys);
Entity ent = Data.GetEntity(pk);
ent.Delete();
result = Data.SaveChanges();
Data.Clear();
return result.Ok;
}
public virtual T GetEntityByPrimaryKey(object pPrimaryKey)
{
PrimaryKey primaryKey = new PrimaryKey(typeof(T), pPrimaryKey);
T entity = data.GetEntity<T>(primaryKey);
return entity;
}
public virtual T GetEntityByPrimaryKey(params object[] pPrimaryKeys)
{
PrimaryKey primaryKey = new PrimaryKey(typeof(T), pPrimaryKeys);
T entity = data.GetEntity<T>(primaryKey);
return entity;
}
public virtual EntityList<T> GetEntityList(EntityColumn pColumn, EntityQueryOp pQueryOp, object pValue)
{
RdbQuery rdbQ = new RdbQuery(typeof(T));
EntityList<T> aList;
rdbQ.AddClause(pColumn, pQueryOp, pValue);
aList = data.GetEntities<T>(rdbQ);
return aList;
}
public virtual EntityList<T> GetEntityList(List<Clause> Clauses)
{
RdbQuery rdbQ = new RdbQuery(typeof(T));
EntityList<T> aList;

int counter = Clauses.Count;
for(counter = 0; counter < Clauses.Count; counter ++)
{
rdbQ.AddClause(Clauses[counter].FilterColumn, Clauses[counter].QueryOperator, Clauses[counter].PassedValue);
if(counter < Clauses.Count -1 )
{
rdbQ.AddOperator(Clauses[counter].ClauseOperator);
}
}
aList = data.GetEntities<T>(rdbQ);
return aList;
}
private bool CheckAndSave()
{
if(!explicitSave)
{
if (Data.HasChanges())
{
Data.SaveChanges();
return true;
}
}
return false;
}
public bool SaveChanges()
{
if (Data.HasChanges())
{
Data.SaveChanges();
return true;
}

return false;
}
#endregion
private void AssignValues(T entity)
{
foreach (EntityColumn column in Data.GetEntityColumns(typeof(T)))
{
if (!column.IsPrimaryKeyColumn && EntityProperty[column.ColumnName] != defaultEntity[column.ColumnName])
{
entity[column.ColumnName] = EntityProperty[column.ColumnName];
}
}
}
public string GenerateNo(string prefix )
{
EntityList<T> aList = data.GetEntities<T>();
string maxNo;
if (aList.Count > 0)
{
maxNo = (Convert.ToString(Convert.ToInt32(((aList[aList.Count - 1])[primaryKeyField.ColumnName])) + 1));
}
else
{
maxNo = "1";
}

int len = maxNo.Length;
int padLength = numLength - len;
string padZero = new string('0',padLength);

return string.Format("{0}-{1}{2}", prefix, padZero, maxNo);
}
private void ProcessCreate(T entity)
{
foreach (EntityColumn column in Data.GetEntityColumns(typeof(T)))
{
if (column.IsPrimaryKeyColumn)
{
primaryKeyField = column;
break;
}
}
if(uniqueNoField != "")
{
entity[uniqueNoField] = GenerateNo(uniqueIdPrefix);
}
entity.AddToManager();
Initialize();
AssignValues(entity);
CheckAndSave();
}
private void Initialize()
{
defaultEntity = data.CreateEntity(typeof(T));
}
}
public struct Clause
{
public EntityColumn FilterColumn;
public EntityQueryOp QueryOperator;
public object PassedValue;
public EntityBooleanOp ClauseOperator;
}
}

We used it on MAN Project and it works!

The Wonder of SQLCacheDependency Class

2008-05-09T18:06:00.002+08:00

The Wonder of SQLCacheDependency Class

Here’s a new exciting lesson I learned when working with my recent project. The challenge is to create a Price Screen for Commodity Trading that will update on real time. This should be done as optimize as possible for this will be shown over the Net. We usually save reusable data in Application variable or in Session variable for optimization purpose. Here is a sample code that we usually did to persist data.

public static DataTable ExecuteDataTable(persistIn persistence, string
storedProcedureName, params object[] parameterValues)
{
DataTable
returnTable = null;
switch (persistence)
{
case persistIn.Session:
returnTable = (DataTable)HttpContext.Current.Session
[storedProcedureName.ToUpper()];
if (returnTable == null)
{
returnTable = db.ExecuteDataSet(storedProcedureName,
parameterValues).Tables[0];
HttpContext.Current.Session
[storedProcedureName.ToUpper()] = returnTable;
}
break;
case
persistIn.Application:
returnTable =
(DataTable)HttpContext.Current.Application [storedProcedureName.ToUpper()];
if (returnTable == null)
{
returnTable =
db.ExecuteDataSet(storedProcedureName, parameterValues).Tables[0];
HttpContext.Current.Application [storedProcedureName.ToUpper()] =
returnTable;
}
break;
case persistIn.NoPersistence:
returnTable
= db.ExecuteDataSet(storedProcedureName, parameterValues).Tables[0];
break;
default:
break;
}
return returnTable;

}

Here, the result returned by a call to a stored procedure is saved in Server Memory, either in Session of Application. When the Data already exists there is no need to call for the stored procedure again, it just fetches the data in Server Memory and return it. The problem here is when there are changes on the data from the database the application won’t fetch the new item unless you force to change the value stored on the Server Variable, this can be done by calling this function and passing persistIn.NoPersistence as parameter.

The solution here is use SQLCacheDependency. SQLCacheDependency has the capability to detect changes on Database and delete specific data on memory that depends on it. What you need to do is to add dependency on table that will tell the application that its time to fetch a new data. So when someone updates that table the application will fetch a new data for the variable is lost on the memory.

But you need to configure your database and add some code for this to work. I listed below the steps to do to be able to use SQLCacheDependency:

1. Update your database by Running the script in SQL.
ALTER DATABASE YourDatabase SET ENABLE_BROKER; GO
This will enable Service Broker that will raise notification to ASP for changes on database

2. On your Global.asax, you should add one if you don’t have this yet, add the following line:

1: string connectionString = ConfigurationManager.
ConnectionStrings[0].ConnectionString;
2:
System.Data.SqlClient.SqlDependency.Start(connectionString);
3:
SqlCacheDependencyAdmin. EnableNotifications(connectionString);
4:
SqlCacheDependencyAdmin. EnableTableForNotifications (connectionString,
"TableName");

Line 1 will retrieve the connection string that you are using in your web app. The user you used on the Connection string should have the full access in your database.
Line 2 Starts the listening in the event
Line 3 Adds some stored procedure needed for notifications
Line 4 Adds Trigger to table that you are watching

3. Add the following on your web.config inside <system.web>:

<caching>
<sqlCacheDependency enabled = "true" pollTime
= "1000" >
<databases>
<add name="Database"
connectionStringName="MyConnection"
pollTime="1000"/>
</databases>
</sqlCacheDependency>
</caching>

The Name attribute will be used by the SQLCacheDependency to know how it will connect to the database.
The ConnectionStringName attribute will map the connection you are using in web.config.

4. Now you are ready to use SQLCacheDependency on your code as written bellow:

public static DataSet ExecuteDataTable(string storedProcedureName, params
object[] parameterValues)
{
DataSet myDataset = new DataSet();
if
(HttpContext.Current.Cache[storedProcedureName.ToUpper()] == null)
{
SqlCommand command = new SqlCommand();
command.CommandType =
CommandType.StoredProcedure;
command.CommandText = " MyStoredProcedure";
SqlCacheDependency dependency = new SqlCacheDependency("Database",
"TableName");
myDataset = db.ExecuteDataSet(storedProcedureName,
parameterValues);
HttpContext.Current.Cache.Insert(storedProcedureName.ToUpper(),
myDataset,
dependency);
}
return myDataset;
}

Then that’s it. You will experience the magic of it! If you will check your database once you have run your application, there are several new stored procedures and tables added by ASP. This is use for notification purpose, don’t erase those.

Now what I did on my Project is Add an AJAX Timer that will check if the Cache still exists, if not it will update the screen to display new data. It is a real-time base on web. Amazing!

When To Disable and Enable The Viewstate on a control

2008-04-26T06:35:00.001+08:00

Viewstate has a big impact on web performance. If you will not pay attention to it, your website will become a large junk.

How Viewstate does affect performance? Remember that Client and Server Communicates via a postback. And posting a web page as large as 50KB is like uploading a file on youtube or attaching a file on yahoo mail, imagine that. You can check how large is your viewstate when you look at the rendered page by clicking View Source for IE, the part that is garbage looking that occupies almost the whole part of your notepad. Just look at the tag saying __VIEWSTATE.

What really is the function of viewstate? Viewstate is used by ASP.Net to remember the state of the control before a postback occur. One good example is how ASP.Net know that a text has changed when a postback occur or if you have enabled autopostback of the control. It is very simple with the use of viewstate, the value of the textbox is saved on the viewstate, when user changes the value of it and a postback occurs it compares the new value of the textbox and the viewstate if it matches if it does not it raises onChange event.

Now imagine how many controls you have and all of them have viewstate. Each control occupies at list 20 bytes multiply it by the number of your controls. Aside from those, templated and bounded controls like Gridview, Datalist or dropdownlist occupies a very large size of viewstate. But wait we cannot just disable all of them or put enableViewState = false on page directives. What we can do is to use Viewstate wisely.

Here are some pointers I think would help you decide when to disable viewstate.

Dynamically inserted value on the controls (By binding or programmatically assigning) – The values of this controls will not retain when it is rerendered, e.g. Switching from view1 to view2. But you have to consider two things, if you think repopulating the values for every render is to heavy to implement then don’t disable the viewstate, if not then you may disable it and reinitialize your controls on render event. Why am I suggesting this? It’s because processing serverside code is much faster than transferring a large junk of data back to the server and unto the client on roundtrips.
On Datalist and DropDownList – If you are not using the OnSelectedIndex Change event then you may disable the viewstate.
On Gridviews – This is the hardest part to decide whether to disable viewstate or retain it. If you are just displaying data on it or even using it just for selection, then disable the viewstate. If you are using paging, edit or delete functionality then don’t. Gridview has the largest viewstate capacity so you should use it wisely. If you have to update as many as 5 columns then why not just open another view then set the values there to be updated rather than updating it on the gridview directly.

These are just some of my tips, but you should always test the effect of it so you can see if controls behaves as expected even without viewstate.

Calling A Method By Name

2008-04-18T14:36:00.003+08:00

Sometimes we are developing a system that is very much dependent in the database. I call this style as Data Driven Development where everything depends on the database. Even for Buttons that should be available on screen. I have develop a Touch Screen System before using Delphi where Buttons are created Dynamically depending on the set of data returned by the database. This means that each buttons calls its method by function name. To achieve this we set a centralize Event Handler for all Buttons Created Dynamically and then call the routine by Name. That is easy in Delphi for you can call routines of a protected method by Name.
When I shifted to C# I wonder how will I achieve that same functionality. Atlast with some few trials and hard work, I have created a class to handle this. The code is as follows:

public delegate void methodDelegate();
public class FunctionByName
{
private ArrayList
ListOfMethodAddress = new
ArrayList();
private ArrayList
ListOfMethodName = new ArrayList();
public void
AddMethod(methodDelegate
MethodAddress)
{
ListOfMethodName.Add(MethodAddress.Method.Name);
ListOfMethodAddress.Add(MethodAddress);
}

public void ExecMethod(string
MethodName)
{
if
(ListOfMethodName.Contains(MethodName))
{
int MethodIndex =
ListOfMethodName.IndexOf(MethodName);
methodDelegate toExec = new
methodDelegate((methodDelegate)ListOfMethodAddress[MethodIndex]);
toExec();

}
}

}

The difference is that you should add all delegates that you want to be called by name using AddMethod, Then to execute it you call ExecMethod("MyFunction"), Here you are calling MyFunction Method but it should be added initially before you can call it.

Updated: Below is a new version of my CallFunctionByName Class it. It uses Dictionary rather than ArrayList:

public delegate void methodDelegate();
public class
FunctionByName
{
private Dictionary
ListOfMethod = new Dictionary();
public void
AddMethod(methodDelegate
MethodAddress)
{
ListOfMethod.Add(MethodAddress.Method.Name,
MethodAddress);
}
public void ExecMethod(string MethodName)
{
if
(ListOfMethod.ContainsKey(MethodName))
{
methodDelegate toExec =
(ListOfMethod[MethodName]);
toExec();
}
else
{
throw new
Exception("Not yet Implemented");
}
}
}

Adding a ROWID() for tables that are linked by UNION ALL

2008-04-15T11:21:00.002+08:00

ROW_NUMBER() inserts a virtual ROWID for your query. But how can we create a ROWID for two tables continuously when they are linked using UNION ALL?

Here is How:

(SELECT PartsID AS ItemID, PartsDesc as ItemDesc, ROW_NUMBER()OVER (ORDER
BY PartsID)FROM dbo.Parts)
UNION ALL
SELECT AssemblyID as ItemID, AssemblyDesc,count(PartsID) + ROW_NUMBER() OVER (ORDER BY AssemblyID)FROM dbo.[Assembly],dbo.Parts group by
AssemblyID, AssemblyDesc

Here is how it works:

The first Select Statement inserts a ROW ID for the Parts Table
The Second Select Statement uses two tables (FROM dbo.[Assembly],dbo.Parts). JOINING two tables using From Table1, Table2 multiplies the number of records.
The Second Select Statement was then Grouped By Fields needed for Assembly. This will select the distinct records needed for the query.
count(PartsID) gives the last number of the first query then add it to the ROW_NUMBER() of the second select statement to continue the numbering.

Another way to optimize this is to create a view for the UNION ALL statement then treat it as a single table in your ROW_NUMBER statement.

Conditionally add a control on Markup during Databinding

2008-03-31T12:40:00.004+08:00

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>

using JSON in C#.Net

2008-03-18T18:31:00.002+08:00

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/

Adding Javascript file programmatically in C#

2008-03-11T09:44:00.006+08:00

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.

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

2008-03-10T12:01:00.003+08:00

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)

Adding triggers for your Table

2008-03-08T23:22:00.003+08:00

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#

2008-03-08T19:04:00.008+08:00

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

2008-03-08T17:55:00.003+08:00

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:

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.
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.
Concatenate the 19 character datetime to the 100 character Activity making it a sum of 119 characters
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.
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

2008-03-08T16:56:00.003+08:00

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

2008-03-08T16:16:00.003+08:00

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

2008-03-08T03:00:00.001+08:00

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

2008-03-08T02:00:00.000+08:00

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

2008-03-08T01:00:00.000+08:00

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

2008-03-08T00:00:00.000+08:00

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.

Student's Form Generator

2008-03-07T23:27:00.000+08:00

Description:
A complete Computerized Grading System that involves the following modules:

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
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.
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

2008-03-07T23:00:00.000+08:00

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

2008-03-07T22:00:00.000+08:00

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

2008-03-07T21:00:00.000+08:00

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

2008-03-07T20:00:00.000+08:00

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#

2008-03-07T14:53:00.002+08:00

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