Do you think history is boring? think again!! Here is the list of my favorite documentary that changed my thoughts on histories,. Enjoy
History's Mysteries
Lost City Of Atlantis
Ancient X Files
The Holy Grail
The Pyramid Code
Announcement: SocialShare Starter Kit (v1.0) is Released
SocialShare Starterkit v1.0 Relaesed. SocialShare Starter Kit is a kick start application that illustrate a wide range of features that needed to build a social site. And includes features like Status sharing,Blogs,Forums & more. Any one need a lightweight quick solution for his social portal,SocialShare Starter Kit is the savior.SocialShare Starter Kit uses T-4 templates to build logical layers (i.e. DAL/BLL) . The logical layers implemented here are based on Entity Framework 4.0. Its also a good example to learn T4 template and how to utilize them work with asp.net data bound controls with minimum effort. I am continuously adding and fine tuning the existing feature list and performance. Checkout the app from codeplex SocialShare Starter Kit Home also documentation is available SocialShare Starter Kit documentation
Solution To The MSSQL Server “Suspect”
Background
When you see the your database in Suspect mode that a code red situation. Its not something that you face everyday. It can happen, however, most often due to faulty hardware or operational mistakes (like deleting a transaction log file). This post will show how to recover from suspect mode, but still go through SQL-server error logs and find out the root cause of the error.
Reason
At start-up, SQL Server attempts to obtain an exclusive lock on the device file. If the device is being used by another process or if the file is missing, sql server start displaying error.
In these cases, there is usually nothing wrong with the devices and database. For the database to recover correctly, the device must be made available, and the database status must be reset.
Possible reason for changed to suspect mode in sql server can be
- The system cannot find the file specified.) during the creation/opening of physical device
- Failed to open device where data or log file resides
- SQL server went down/restarted in the middle of a transaction causes transactions log to be corrupted
- SQL server can not access data or log file while coming online , because of you beloved antivirus
Solution
To resolve this issue run the commands listed below,
EXEC sp_resetstatus ‘DATABASE_NAME’;
ALTER DATABASE DATABASE_NAME SET EMERGENCY
DBCC checkdb(‘DATABASE_NAME’)
ALTER DATABASE DATABASE_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘DATABASE_NAME’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DATABASE_NAME SET MULTI_USER
What the above command do is perform a series of step to check the logical & physical consistency of the database and tries to repair. Very first step is to turns off the suspect flag on a database, you can achieve his by using sytem stored procedure sp_resetstatus. Using this procedure change the suspect flag to emergency. Once the database is set to EMERGENCY mode it becomes a READ_ONLY copy and only members of sysadmin fixed server roles have privileges to access it. Then perform a consistency check by executing DBCC command on the master database. Next step is to rollback any transactions if any are present in the database and bring the database into Single User mode. Run the repair and finally bring database to Multi User mode.
Remember sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.
If the database is still marked as suspect after performing these steps, there may be other problems preventing the database from recovering. At this point, only option left is either restore from a good backup or set the database to emergency mode and use the bulk copy program copy the data out.
TSQL Recursive Split Function
Background
In my last post I showed how to split a string in TSQL. Problem with that splitting is it can split a string if resultant split count is <=2, i.e. it can split only string like “a,b” or “first,last” or “java,c”. But while doing the parsing of string of whom resultant split count is >2, it wont work. We need more effort to do so.
Solution
I am gonna show how to split a string of any number of delimiter and we will recursive function to achieve that. Idea is very simple, Create a function that will take the input string and get the first index of delimiter and return the string up to first index and insert that first half into a table. Next is find the rest of the string other then first half and call the same function with it. This loop continues until we find there is no delimiter left in string to do further recursive call, so we return the original caller function and all the way to parent function who initiate the recursive call.
Implementation of the above idea is the function illustrated below,
CREATE FUNCTION [GetName] (@StrVal nvarchar(MAX),@delimeter nvarchar(1))
RETURNS @OldNames TABLE (Name varchar(MAX))
AS
BEGIN
DECLARE @SplitVal nvarchar(MAX);
DECLARE @RestOfTheStrVal nvarchar(MAX);
SET @SplitVal=LTRIM(SUBSTRING(@StrVal, 0,CHARINDEX(@delimeter,@StrVal)));
SET @RestOfTheStrVal=LTRIM(SUBSTRING(@StrVal, CHARINDEX(@delimeter,@StrVal)+1,len(@StrVal)));
IF CHARINDEX(@delimeter,@RestOfTheStrVal)<=0
BEGIN
IF @SplitVal IS NOT NULL
INSERT INTO @OldNames(Name) SELECT @SplitVal ;
IF @SplitVal IS NOT NULL
INSERT INTO @OldNames(Name) select @RestOfTheStrVal;
return;
END
INSERT INTO @OldNames(Name)
SELECT @SplitVal
UNION
SELECT Name FROM dbo.GetName(@RestOfTheStrVal,@delimeter)
RETURN
END
And this time let me remind you this solution is applicable if you have a string with a single delimiter (means you can use only one delimiter). Though its returns resultant strings as a tabular format, its quite handy for situation like I explained above.
TSQL split a string by delimiter
Background
Say we have a registration table which store successful registration info. At that table we have a column “Name” which stores value as combination of first name & last name delimited by ‘ ‘ [space]. Later at some point we need to extract the first name or last name from that column, what to do?
Solution
Solution is a simple technique that will take given input string (or column in my scenario) and split against the given delimiter. Technique is get the first index of delimiter and return the string up to first index and return the first half. For second half start from first index of delimiter and take till end of input string and you will get second half. Simple isnt it !!
– Split and get the first half, delimiter is ‘ ‘ [space]
LTRIM(SUBSTRING([COLUMN_NAME], CHARINDEX( ‘ ‘,[COLUMN_NAME])+1,len([COLUMN_NAME])))
– Split and get the second half, delimiter is ‘ ‘ [space]
LTRIM(SUBSTRING([COLUMN_NAME], 0,CHARINDEX(‘ ‘,[COLUMN_NAME])+1))
And let me remind you this solution is only applicable if you have a string with a single delimiter and resultant split count is <=2. Though is small and wont work for more then one delimiter but its quite handy for situation like I explained above.
Living in mysterious world
How to solve SSIS error code 0xC020801C/0xC004700C/0xC0047017
Background
SSIS is the one of the best ETL tool available in market and it can load large amount of data from any heterogeneous data source whether structured, unstructured,application, cloud or real-time data. This post discuss the solution of a common problem that usually arise while importing data from excel data source.
Problem
For beginners while doing the extract, face some weird issues, which seems very fuzzy to solve at first. Such a problem is while doing extract from excel data source, after running the application shows error,
at Package, Connection manager “Excel Connection Manager”: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.Error: 0xC020801C at Data Flow Task — Excel File, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.Error: 0xC0047017 at Data Flow Task — Excel File, SSIS.Pipeline: component “Excel Source” (1) failed validation and returned error code 0xC020801C.Error: 0xC004700C at Data Flow Task — Excel File, SSIS.Pipeline: One or more component failed validation.
And the extraction failed.
Reason for this run-time error is excel do not have 64-bit driver which is selected by default in SSIS running on 64bit OS. So solution is also hidden behind the reason, change the driver from default and you are good to go. Steps to solve is issue is,
Step 1: Navigate to Project-> [PROJECT_NAME] Properties.
Step 2: Navigate to “Debugging” option from left panel and from Right panel change Run64BitRuntime value to false.
that’s it !! Keep extracting !!
Living in paradise: St. Martin the coral reef island
Jan 3
This gallery contains 3 photos.
Like most other beautiful places in Bangladesh St. Martin’s Island is also under publicized yet one of the most beautiful tourist attraction of bangladesh. The island is situated in a far way from mainland and surrounded by deep blue ocean with totally made of coral. The natural beauty is breath taking for travelers like me. […]
A weekend in jungle over the hill and wetland
“One’s destination is never a place, but a new way of seeing things.” – Henry Miller
I always admire this quote by heart and go back to places that I already traveled and search for something new, and trust me so far i have never disappointed.
Winter season is on in Bangladesh and as usual I am on my feat and searching for perfect gateway from busy life.
Where should I start? From the modest to the most wildest, how about a mix of breathtaking scenery and taste of wildlife. I know exactly a place like this, sreemangal. I went there few years back, that place is truly spectacular.
Sreemangal is mostly land of tea, wherever you see there is tea state, and during sunset the view is marvelous and truly impossible to capture is frames, but you cant blame for trying 
Other spactacular places to explore in sreemangal is
Nilkantha Tea stall where you can enjoy famous 7 layer tea.
The key to live awesome life is to live in the moment. So enjoy the moment.
Embrace reflection and export any List collection to excel
Introduction
Export to MS excel is a common feature for data driven business apps. And its very common that a individual app contains several such export feature that take different data respect to different needs and export to excel. Purpose this blog post is to provide a generic solution using .net Reflation and Generics so same export function can be reusable though entire application disrespect to object type and save our engineers effort.
Problem Statement
Engineers spend several hours to write redundant function contains such export features satisfy different needs. with a little twist codes can be reused. But question is why is we implement different function for same purpose answer is simple each object has different property so excel will contains different column name for each object. So programmers write different export functions that address a specific object type and hard coded the excel column name .
How Reflection can Help
To address this issue we can use .net Generics and Reflection. Lets not stretch the post by introducing them, you will find lots of good post about them, if you just google. Our interest is on a particular feature reflection can do, you will see PropertyInfo.
Use PropertyInfo to discover information such as the name, data type, declaring type, reflected type, and read-only or writable status of a property, and to get or set property values.
So idea is we will use a Generic collection to export and use Reflection to iterate all property via PropertyInfo, thus we can get all property name/type all that whatever the object collection is provided. Once we have property name/ type we can do whatever data formatting or processing we need according to our needs. Say for a particular app specification is whatever date time is exports to excel it should contains only time expressed in terms if milliseconds. As we have property type now , we can do that easily right?
Solution
Here is the method that take Generic List<t> as parameter and use reflection to iterate the type and export accordingly.
/// <summary>
/// Take object List as input and export
///to xls which will be prompt save as dialog
/// </summary>
/// <typeparam name="T">Type of object</typeparam>
/// <param name="listToExport">Object list to export</param>
/// <param name="xlsName">Excel file name to export</param>
public static void ExportListToExcel<T>(List<T> listToExport, string xlsName)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
HttpContext.Current.Response.AppendHeader("Content-disposition", "attachment; filename=" + xlsName + ".xls");
Int32 success = 0;
string sep = "";
try
{
PropertyInfo[] fieldInfo = listToExport[0].GetType().GetProperties();
foreach (PropertyInfo col in fieldInfo)
{
if (col.PropertyType != typeof(EntityKey)
&& col.PropertyType != typeof(EntityState))
{
HttpContext.Current.Response.Write(sep + col.Name);
sep = "\t";
}
}
HttpContext.Current.Response.Write("\n");
foreach (T dataItem in listToExport)
{
PropertyInfo[] allProperties = dataItem.GetType().GetProperties();
sep = "";
foreach (PropertyInfo thisProperty in allProperties)
{
if (thisProperty.PropertyType != typeof(EntityKey)
&& thisProperty.PropertyType != typeof(EntityKey))
{
object value = thisProperty.GetValue(dataItem, null);
String propetyValue = (value == null ? String.Empty : value.ToString());
HttpContext.Current.Response.Write(sep + propetyValue.ToString());
sep = "\t";
}
}
++success;
HttpContext.Current.Response.Write("\n");
}
HttpContext.Current.Response.End();
}
catch (Exception ex)
{
throw ex;
}
}
One thing might you notice above
if (thisProperty.PropertyType != typeof(EntityKey) && thisProperty.PropertyType != typeof(EntityKey))
Why we need this checking? you see if you are using Entity Framework in your app, all entity has these two additional property type predefined and we don’t what them to be exported to csv and confuse end user, do we? So additional checking is required to remove them. If you do not use Entity Framework you don’t need to worry about this checking.
So you can see a simple trick can save lots of development effort, enjoy the free time
Embrace reflection and export collection to csv
Introduction
Its became very common request in recent data driven apps to able to export some certain data as csv format. CSv format became widely used and acceptable. And its very common that a individual app contains several such export feature that take different object collection respect to different needs and export to csv, so here is the problem lies. Goal of this post is to provide a generic solution using .net Reflation and Generics so same export can be reusable though entire application disrespect to object type and save our (programmers: most hardworking creature in this planet after ant 
) effort.
Problem Statement
I have seen programmers invest several hours to build such export features satisfy different needs. So what happens for different object collection programmers write different export method that address particular export feature. Why is that? answer is simple each object has different property so csv will contains different column name for each object. So programmers write different export functions that address a specific object type and arrange the csv column name statically.
How Reflection can Help
To address this issue we can use .net Generics and Reflection. I will not discuss about theses wonderful feature instead of that I will utilize those feature to find a solution. If you navigate to msdn to see what reflection can do, you will see PropertyInfo (Humm this is all we need) .
Use PropertyInfo to discover information such as the name, data type, declaring type, reflected type, and read-only or writable status of a property, and to get or set property values.
So idea is we will use a Generic collection to export and use Reflection to iterate all property via PropertyInfo, thus we can get all property name/type all that whatever the object collection is provided. Once we have property name/ type we can do whatever data formatting or processing we need according to our needs. Say for a particular app specification is whatever date time is exports to csv it should be formatted like ddMMYYY. As we have property type now , its a piece of cake right?
Solution
Here is the method that take Generic List<t> as parameter and use reflection to iterate the type and export accordingly.
/// <summary>
/// Take object List as input and export to csv
///which will be prompt save as dialog
/// </summary>
/// <typeparam name="T">Type of object</typeparam>
/// <param name="listToExport">Object list to export</param>
/// <param name="seperateChar">character to use as scv separator</param>
public static void ExportListToCSV<T>(List<T> listToExport, string seperateChar)
{
Int32 success = 0;
StringBuilder export = new StringBuilder();
try
{
string seperator = "";
StringBuilder builder = new StringBuilder();
PropertyInfo[] fieldInfo = listToExport[0].GetType().GetProperties();
foreach (PropertyInfo col in fieldInfo)
{
if (col.PropertyType != typeof(EntityKey)
&& col.PropertyType != typeof(EntityState))
{
builder.Append(seperator).Append(col.Name);
seperator = seperateChar;
}
}
export.AppendLine(builder.ToString());
foreach (T dataItem in listToExport)
{
PropertyInfo[] allProperties = dataItem.GetType().GetProperties();
seperator = "";
StringBuilder builderTmp = new StringBuilder();
foreach (PropertyInfo thisProperty in allProperties)
{
if (thisProperty.PropertyType != typeof(EntityKey)
&& thisProperty.PropertyType != typeof(EntityKey))
{
object value = thisProperty.GetValue(dataItem, null);
String propetyValue = (value == null ? String.Empty : value.ToString());
builderTmp.Append(seperator).Append(propetyValue);
seperator = seperateChar;
}
}
++success;
export.AppendLine(builderTmp.ToString());
}
}
catch (Exception ex)
{
throw ex;
}
// finally { if (sr != null) { sr.Close(); } }
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/CSV";
HttpContext.Current.Response.AppendHeader("content-disposition",
"attachment;filename=FileName.csv");
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
HttpContext.Current.Response.Write(export.ToString());
HttpContext.Current.Response.End();
}
One thing might you notice above
if (thisProperty.PropertyType != typeof(EntityKey) && thisProperty.PropertyType != typeof(EntityKey))
Why we need this checking? you see if you are using Entity Framework in your app, all entity has these two additional property type predefined and we don’t what them to be exported to csv and confuse end user, do we? So additional checking is required to remove them. If you do not use Entity Framework you don’t need to worry about this checking.
So you can see a simple trick can save lots of development effort, enjoy the free time
Software Architecture Cheat Blog 3: Open Closed Principle
Define Principle
Open Closed Principle (OCP) states that software entities (classes, modules, functions, etc.) should be open for extension but closed for modifications.
Violation Example
Lets consider a simple banking module that is responsible for calculating interest of accounts respect to account type. Say app owner bank offers to their client three types of accounts : savings accounts, basic checking accounts, money market deposit account. Each of the account type has different interest rate, and interest is calculated on different other factors such as amount deposited, average balance per month and so on. Thus based on this business rules we designed this class Accounts which calculates interest based on account types.
public enum AccountTYpe
{
Savings,
Checking,
MoneyMarket
}
public class Accounts
{
private AccountTYpe accountType { get; set; }
public Decimal CalculateInterest()
{
Decimal interest = 0;
if (accountType == AccountTYpe.Savings)
{
//consider business rules and calculate interest of savings account
interest = 2.50m;
}
else if (accountType == AccountTYpe.Checking)
{
//consider business rules and calculate interest of checking account
interest = 3.50m;
}
else
{
//consider business rules and calculate interest of money market account
interest = 5.00m;
}
return interest;
}
}
Above solution work perfectly for our client bank and app went to live and every one is happy. Few months later bank realize other banks are making money from certificates of deposit account , so we should do that, lets change our system to accommodate that. Now our revised system looks like this,
public enum AccountTYpe
{
Savings,
Checking,
MoneyMarket,
CertificatesOfDeposit
}
public class Accounts
{
private AccountTYpe accountType { get; set; }
public Decimal CalculateInterest()
{
Decimal interest = 0;
if (accountType == AccountTYpe.Savings)
{
//consider business rules and calculate interest of savings account
interest = 2.50m;
}
else if (accountType == AccountTYpe.Checking)
{
//consider business rules and calculate interest of checking account
interest = 3.50m;
}
else if (accountType == AccountTYpe.CertificatesOfDeposit)
{
//consider business rules and calculate interest of certificates of deposit account
interest = 6.35m;
}
else
{
//consider business rules and calculate interest of money market account
interest = 5.00m;
}
return interest;
}
}
Considering the above scenario its not hard to guess, it will go through same process each time bank wants to launch another account type or made change on existing account types business rules. This is where open closed principle violates.
Resolution
To solve this We can create an common Interface which will be implemented by all classes represent account types. Benefit is later change can be accommodate better without modifying other corresponding class.
public interface IAccount
{
Decimal CalculateInterest();
}
public class SavingsAccounts:IAccount
{
public decimal CalculateInterest()
{
return 2.50m;
}
}
public class CheckingAccounts : IAccount
{
public decimal CalculateInterest()
{
return 3.50m;
}
}
public class MoneyMarketAccounts : IAccount
{
public decimal CalculateInterest()
{
return 5.00m;
}
}
Through approach we can add as many account type is needed, all we need is add another class for that account type. Also modification of business logic resides in respective account type class. Hence IAccount interface implement the idea of open for extension but closed for modifications.
