Recursive Call in Stored Procedures

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

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

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

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

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

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:

1. The first Select Statement inserts a ROW ID for the Parts Table
2. The Second Select Statement uses two tables (FROM dbo.[Assembly],dbo.Parts). JOINING two tables using From Table1, Table2 multiplies the number of records.
3. The Second Select Statement was then Grouped By Fields needed for Assembly. This will select the distinct records needed for the query.
4. 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

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>