My First Article in Code Project

My First Article in Code Project here

It is talking about managing SSAS Cube Roles from C#

Pivot Viewer

I am new to Silverlight, but I tried the Pivot Viewer to display lists from Sharepoint. the task seems fine unless:

• make a dynamic list
• problem with images

the first one I got this article which is quite powerful . so it solved me Part one.

the second one I tried to understand why my images are not displayed, but when importing some images from the internet it works. I got an answer that these images that works are DZI (Deep Zoom Image). and there is a DZI Composer!! rocks... Hell no. the composer outputs a bunch of files to work for the concept of DZI.What I only need is one picture! Is this Hard :(

*DZI for simplicity, is making the image in layers so that it loads fast, and when zooming we can get the more detailed layers. 

I am trying to google around and there should be any luck.

Pivot Viewer now is good but it should be customeized. they will, but in next release.... 

nettiers dynamic search query

I am using nettiers 2.3.0 now in a project and then i faced a problem where i have to make a search function that takes a string and search in each entity of the string fields of it to get the results.

nettiers has a good functions like GetPaged that takes the where clause and search with it. but come on how to make this search with generic code and powerful!!!!

the solution is a magic word, "Reflection"!!! ta da .... you have to make your development framework ideal enough to do that. How?

As I said i am using nettiers this make me support the following: each entity has an interface describes each property. and a good naming convention that I can follow in naming the UI elements such as typed data sources, that nettiers generates, and so i wrote this code...

List<string> lookFor = new List<string>();

Type t = Type.GetType(string.Format("Entities.I{0}, Entities", EntityName));

foreach (PropertyInfo info in t.GetProperties())

{

if (info.PropertyType == Type.GetType("System.String"))

{

lookFor.Add(string.Format(" [{0}] Collate SQL_Latin1_General_CP1_CI_AS like '%{1}%' ", info.Name, txtName.Text));

}

}

string whereClause = string.Join(" OR ", lookFor.ToArray()) ;

DataSourceControl ds = WebFormUtil.GetControlByID(Page.Controls, EntityName + "DataSource") as DataSourceControl;

PropertyInfo filterProperty = ds.GetType().GetProperty("Parameters");

Object collection = filterProperty.GetValue(ds, null);

if (collection is ParameterCollection)

{

((CustomParameter)((ParameterCollection)collection)["WhereClause"]).Value = whereClause;

}

ds.DataBind();

with just the entity name as a variable, and the well formed structure of the tiers and the good structure of the any form that contains a list and a search box this code rocks. I am able to get the typed data source and attach a value to its attributes and finally get works done and generic.

Thanks to Reflection, it made it possible.

Entity Framework

Entity framework is designed to separate the usage of the data layer elements like table names columns types, database type, and some added functionalities like super-set and subset.

It is good in the aspects mentioned above. But as Microsoft says, it is not a OR mapper. So, you cant get the benefits of the OR mapper like caching and database column validation, custom load, foreign keys values, and some like that.

All you have is the designer that you can do many things in it. and you have to edit this file for further subclassing conditions value - it saves the hexadecimal value and you have to change it manually, hope it will be solved at the final release- also, you can manage whether this table can be accessed via stored procedure or by generated query - if you select a stored procedure for one function like delete, you have to supply *sp for the rest update, insert :S - It is not complete now. Hope that it will be fixed in the final release. I made a comparison between Entity framework and .net Tier, and I found this

Entity Framework	.net Tier
one file contains entities and relations	contains many projects and files to represent the entities and its relations	No. of files generated
you can subclass entities based on condition easily from designer	you have to create the subclass file and modify the super-class manually	Super-class and subclass
entity object references not accessible outside domain [web service, WCF]	entity object accessible anywhere	entity object accessibility
queries accessible via linq and e-sql	accessible via defined functions	data retrieved
select query generated from linq and e-sql	select query defined in sp	select query
using the facade generated class for update and delete and insert, no e-sql	using the sp for all the operations	DML functions

This is all as far as I used the Entity framework. it is good but not as good as hibernate.


*sp: stored procedure

Visual Studio Add-on

From alot of projects I figured out that Microsoft is missing a vital addon for its Solution Explorer Pane! that is expand/collapse feature.

This feature is vital when you are working in solutions each one consisting of more than 15 projects each project has folders and sometimes these folders contain folders :(

the problem when you want to reach a file you will manually collapse all those nodes then find your item. the worst thing when you are debugging and opened a file from outside the solution to read something then close it ......................... Visual studio behavior is miserable, it will open all the projects tree nodes, then you want to collapse all of them to focus on the selected node only.

I searched about this add-on and found one http://www.paraesthesia.com/archive/2004/06/25/solvent---power-toys-for-visual-studio-.net.aspx but it is for Visual Studio 2003.

hmmmm, Ok no deal I created one for VS 2005 ta.da.

the link is here

The Controls collection cannot be modified because the control contains code blocks (i.e. <% ... %>)

If you have this exception, with wired error message and the stack trace is some how freeek.

Simply, this exception occurs when you have a server side call from javascript located at the head tag of the html like that

< head runat="server" >
< script >
var controlClientObject = <%= RadControl1.ClientID %>
...
< / script>


< body >
...

you should do that

< head runat="server" >

< body >
< script >
var controlClientObject = <%= RadControl1.ClientID %>
...
< / script >
...

I googled a lot and this is what concluded.

ref. http://www.telerik.com/help/aspnet/menu//menu_move_codeblocks.html

Excel Export

Image via WikipediaFaced many problems when loading or exporting to excel files! got many confused when loading data from excel file and it seems that the data you have is less than the data in the file. Needed to do many calculations and formatting!

the problem is we don't know how to deal with excel, and how excel deals with data connectors like ODBC.

I faced these issues and searched a lot to get a result and got these guidelines.

1- for loading an excel file make sure you format the excel file to the desired format you want. example: if you only will get the data in number format, so format the column with the suitable numeric format. so that all the data that you will have is the numbers, if you types a word in this column, excel will not transfer this value to the ODBC driver.

If you want it generic so you will format it as text. so whatever gets into the cell excel will transfer it.

here is a snippet of how loading from excel. [the code is in C#]

public DataTable LoadSheet(string sheetName)
{
DataTable dtSheet = new DataTable();

string excelConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";";

string command = "select * from [" + sheetName + "$]";

new System.Data.OleDb.OleDbDataAdapter(command, excelConString).Fill(dtSheet);
return dtSheet;
}

this how to load a sheet of excel into DataTable which you can deal with in the rest of your code.

*Trick: when reading, excel considers the first formatted row as the table header [formatted means bordered] if you did not do that the headers will be F1, F2...etc

2- Writing to Excel. the problem begins.

1. first you have to export to a named sheet
2. the sheet should be in a table format, if you typed in this sheet before exporting the writing process will begin writing after your writing. example: you write in cell A2, when exporting the excel will begin writing from A3, B3,C3...etc.
3. any formats should be applied on the previous cell that you want to write in. example: you to begin writing in cell A4, and you want it to be formated as numeric (#.00). So, you have to apply this format on cell A3 only and not the column.
4. now you need the way to insert the rows.

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties=Excel 8.0;");
connection.Open();
OleDbCommand command;
// insert rows
foreach (object item in items)
{
// insert row
command = CreateInsertCommand(sheetName, fields, connection, item);
command.ExecuteNonQuery();
}
private OleDbCommand CreateInsertCommand(string sheetName, ExcelField[] fields, OleDbConnection connection, object item)
{
StringBuilder builder = new StringBuilder();
string fieldValue;
OleDbParameter parameter = null;
int count = fields.Length;

OleDbCommand command = connection.CreateCommand();

for (int i = 0; i < count; i++)
{
fieldValue = (item is DataRow) ? fields[i].GetDataRowValue(item) : fields[i].GetValue(item);
if (i == 0)
{
builder.AppendFormat("insert into [{0}$] values(?", sheetName);
}
else
builder.Append(", ?");

parameter = new OleDbParameter(string.Format("param{0}", i), GetOleDBType(fields[i].FieldType));
parameter.Value = fieldValue;

command.Parameters.Add(parameter);

if (i == count - 1)
builder.Append(")");
}

command.CommandText = builder.ToString();
return command;
}
and tada it did insert the rows.

*Tricks: make sure you cast the fields values to its proper type.

Now the nightmare part. the calculations and advanced formatting. this is an excel behavior. if you need to do this you have to create a separate sheet for viewing and other sheets to get the data and make a reference from the view sheet to the other sheets.

the calculations will be on the view sheet. your export function will export to those sheets. And you have to open the file to apply the formatting otherwise it will not affected!!!!

Enjooy the excel loading and exporting.

those tips for formatting Excel.

Related articles

• Master the Excel IF Function [Excel Tip] [via Zemanta]
• Hackers seize on Excel vulnerability [via Zemanta]
• Create an Excel Gantt Chart with Conditional Formatting [Excel Tip] [via Zemanta]
• Five easy Excel formatting tricks [via Zemanta]