C# Developer Blogs

C# LinqToSql Nullable where Clause

2010-07-24T00:02:00.016+08:00

I will discuss for this blog is how to query using LinqToSql with a Nullable WHERE Clause.

PROBLEM:

I have a tree-structured database table with tight relationships. That means, all top/parent nodes are a NULLABLE parentID. How can we query the same level nodes using LinqToSql?

TABLE STRUCTURE:

IdTreeNode	int	PK (Identity Field)
IdParentNode	int	NULLABLE, FK (Relates to IdTreeNode)
Name	nvarchar(50)

EXAMPLE DATA:

Node Name (IdTreeNode/IdParentNode)

Top node 1 (1/null)
Top node 2 (2/null)
- Middle node 1 (3/2)
- Middle node 2 (4/2)
  - Bottom node 1 (5/4)
  - Bottom node 2 (6/4)
  - Bottom node 3 (7/4)
Top node 3 (8/null)

LINQ QUERY:

MyDataContext db = new MyDataContext();
int? idParentNode = 2;
int anyvalue = 0; //use anyvalue that you are sure will not be a value of IdParentNode
var result = (from item in db.TreeViews where object.Equals(item.IdParentNode ?? anyvalue, idParentNode ?? anyvalue select item)

RESULTS:

Middle node 1
Middle node 2

Simple c# Linq query

2009-02-06T00:24:00.003+08:00

//This is the extension used to call list functions.
using System.Linq;
using System.Collections.Generic;

public class Person
{
public string FirstName { get; set; }
public string Lastname { get; set; }
public int Age { get; set; }
}

public class LinqImplementation
{
List Persons = new List();
public LinqImplementation()
{
//This is the old .Net 2005 Implementation of Person class
Person eve = new Person();
eve.FirstName = "Eve";
eve.Lastname = "Davis";
eve.Age = 39;
Persons.Add(eve);

//This is .Net 2008 Implementation of Person class
Person adam = new Person(){
FirstName = "Adam",
Lastname="Sandler",
Age = 42
};
Persons.Add(adam);

//To Simplify things up, I created method to generate list of person
CreatePerson(Persons, "Bill", "Gates", 58);
CreatePerson(Persons, "Rob", "Thomas", 44);
CreatePerson(Persons, "Kobe", "Bryant", 29);
CreatePerson(Persons, "George", "Bush", 65);
CreatePerson(Persons, "Barak", "Obama", 59);
CreatePerson(Persons, "George", "Washington", 59);
}

private void CreatePerson(List Persons, string firstname, string lastname, int age)
{
Person person = new Person() { FirstName = firstname, Lastname = lastname, Age = age };
Persons.Add(person);
}
//This is the query to get the list of person with firstname equal to George
public List GetPersonFirstName()
{
return (from person in Persons
where person.FirstName == "George"
select person).ToList();
}
//This is the query to get the list of person with the age of 40 up to 60
public List GetPersonAge()
{
return (from person in Persons
where person.Age >= 40 && person.Age <= 60
select person).ToList();
}
//This is the query to get the list of person with the age of 40 up to 60
//And adding order as descending.
public List GetPersonAgeDescending()
{
return (from person in Persons orderby person.Age descending
where person.Age >= 40 && person.Age <= 60
select person).ToList();
}
//This is the query to get the list of person with the age of 40 up to 60
//And adding order as ascending.
public List GetPersonAgeAscending()
{
return (from person in Persons
orderby person.Age ascending
where person.Age >= 40 && person.Age <= 60
select person).ToList();
}
}
//Test this in your console application.
//Hoping that this helps a lot

Div Panels

2008-12-29T22:14:00.026+08:00

Hello folks. If you're searching for the div panel source codes, you won't be disappointed in this page.

Here are some samples of div panels:

Standard Div Panel

Source code:
<div style="border: 1px solid blue; height: 100px;">
Put your content here...
</div>
Sample Result:

Put your content here...

Top

Scrollable Div Panel (Vertical)

Source code:
<div style="border: 1px solid blue; height: 100px; overflow-y: scroll;">
Put your content here...
</div>
Sample Result:

Put your content here...
Put your content here...
Put your content here...
Put your content here...

Top

Scrollable Div Panel (Horizontal)

Source code:
<div style="border: 1px solid blue; overflow-x: scroll; background-color: rgb(238, 238, 238); width: 400px; white-space: nowrap;">
Put your content here....
</div>
Sample Result:

Put your content here....
Put your content here....
Put your content here....

Top

C# Interface usage

2008-12-27T09:52:00.009+08:00

This is how to create an interface.

public interface IPerson {
string Firstname {get;set;}
string Lastname {get;set;}
DateTime BirthDate {get;set;}
DateTime GetCurrentAge();
}

There are two ways to implement the interface you've created. It is either you implement it implicitly and or you implement it explicitly.

This is how to implement it Implicitly.

public class Engineer : IPerson {
public string firsname;
public string lastname;
public DateTime birthDate;

public Engineer(string _firstname, string _lastname, DateTime _birthdate){
Firstname = _firsname;
Lastname = _lastname;
BirthDate = _birthdate;
}

public string Firstname {
get { return firstname; }
set { firsname = value; }
}

public string Lastname {
get { return lastname; }
set { lastname = value; }
}

public DateTime BirthDate {
get { return birthDate; }
set { birthDate = value; }
}

public DateTime GetCurrentAge () {
return DateTime.Now.Subtract(BirthDate);
}

}

This is how to implement it Explicitly.

public class Engineer : IPerson {
public string firsname;
public string lastname;
public DateTime birthDate;

public Engineer(string _firstname, string _lastname, DateTime _birthdate){
Firstname = _firsname;
Lastname = _lastname;
BirthDate = _birthdate;
}

IPerson.string Firstname {
get { return firstname; }
set { firsname = value; }
}

IPerson.string Lastname {
get { return lastname; }
set { lastname = value; }
}

IPerson.DateTime BirthDate {
get { return birthDate; }
set { birthDate = value; }
}

IPerson.DateTime GetCurrentAge () {
return DateTime.Now.Subtract(BirthDate);
}

}

Creating Controller ASP.Net MVC Framework

2008-08-27T23:09:00.024+08:00

If you have installed the ASP.NET MVC Framework correctly, you would probably have an easy time on creating a new MVC Controller. (Quick Link) However, there are times that you need to create new controllers without using the wizard. One of the most particular reason is... you just want to create it your self. So, if you are one of those guys that are not lazy or just curious, then you are in the right site.

Creating Controller

First of all, you need to add a code file in your project.

In the file you just created add this code:

public class ProductController : Controller
{
 public ActionResult Index()
{
//Add action logic here
return View();
}
}

For those who have very little knowledge on creating MVC Controller, don't worry. I'll explain it to you.

public class ProductController : Controller
{
public ActionResult Index(int parameter1, string parameter2)
{
//Add action logic here
return View();
}
}

Color Coded (above) Explanation:

RED: The Controller Name. In making a controller you must keep this format, ControllerName + Controller.
Examples:
Controller Name Controller Class Name
Profile ProfileController
Department
DepartmentController
Request RequestController

You should also remember that if you create a view on this controller you must create a new folder under the views with the same name of your Controller Name.
Green: The Action Method. This is the method in which your logic will be implemented. As default, Action Method Name (with return value of ActionResult) should be always the same as the ViewPage Name you want to render. If you want to render a ViewPage that is not the same as Action Method's Name, then you should specify it in the View() Method.

EXAMPLE:
return View("AnotherViewPage");

(Click this for the Reference)
BLUE: Action Method return value.

ActionResult Return Type

All action methods must return an instance a class that is derived from ActionResult. The ActionResult class is the base for all action results. However, there are different action result types, depending on the action that the action method is taking. For example, the most common action is to call the View method. The View method returns an instance of the ViewResult class, which is derived from ActionResult.

The following list shows the built-in action result types.

ViewResult. Returned by the View method.
RedirectToRouteResult. Returned by the RedirectToAction and RedirectToRoute methods.
RedirectResult. Returned by the Redirect method.
ContentResult. Returned by the Content method.
JsonResult. Return by the Json method.
EmptyResult. Returned if the action method must return a null result.

ASP.Net MVC Framework Introduction

2008-08-27T22:04:00.023+08:00

MVC stands for Model - View - Controller and is an architectural pattern used in software engineering. Successful use of the pattern isolates business logic from user interface considerations, resulting in an application where it is easier to modify either the visual appearance of the application or the underlying business rules without affecting the other.

Model

Used to accessing, retrieving, using, and maintaining data.

View

Used for displaying all the data.

Controller

Used to handle things from View to Model and From Model to View. The interaction between View and Model happens in the Controller.

Release History:

ASP.NET MVC Framework ( December 10, 2007)
ASP.NET MVC Preview 2 ( March 5, 2008 )
ASP.NET MVC Preview 3 ( May 1, 2008 )
ASP.NET MVC Preview 4 ( July 16, 2008 )
ASP.NET MVC Preview 5 ( August 28, 2008 )
ASP.NET MVC Beta ( October 16, 2008 )
ASP.NET MVC Release (expected to be released on, January, 2009)

ASP.NET MVC Official Website

User Controls for Reusable Forms

2008-03-03T14:39:00.012+08:00

ASP.NET Web User Controls are very useful to minimize the development time of a project. It helps to reuse the codes all over again without recoding it. Image if you created a code that has 1000 lines and used in two tables. This would take you to change about 50%-80% of your codes in the second table. However, if you used Web User Controls, you will only need to change/set the property you want and takes you more or less 10% total time to put it in the second table. Another advantage of User Controls is that if you change anything in the code, you don't need to change all the other similar codes, all the others will automatically changed.

STANDARDIZATION NOTES:

You must use User Controls if that code can still be used in other forms.
If the query varies but the results are still the same usage of User Control is Recommended.

Example:
Create a blogging table. Blogs will be differentiated as Draft and Published. All Unregistered Users can only view all the blogs published by all Registered Users. Registered user can still view all published blogs by all users but has a management page where he/she can view all his blog work may it published or still a draft. Draft Blogs are in one table and Published Blogs are on the other table.

In this example there are three queries, the All Published (AP), Published by User (PU), and Drafted by User (DU). There is also one table, the blogging table (BT) but will be use for three times, in the Blog page, Management Page Draft area, and Management Page Published area. To save development time it is obvious to use the ASP.NET Web User Control. However there three different queries that we must follow. To solve this we must have a User Control Property that determines what query to be used. You can create an Enumeration to be represent those queries.

public enum QueryType{ AllPublished, Drafted, Published, None } //None Represents nothing was selected and returns nothing.

To set the Property:

public QueryType ViewBlog{
set{

if (value == QueryType.AllPublished){
//Do the query here
}

else if (value == QueryType.Published){
//Do the query here
}

else if (value == QueryType.Drafted){
//Do the query here
}

}
}

If you add this User Control in the web page and look at its property, you will see another property (ViewBlog) in the MISC property. Use this property to set the values of the User Control.

ASP .Net Database Access Layer Standards

2008-02-27T18:35:00.006+08:00

In a professional ASP.Net programming world, an IT company should have standards on how to access the database on every programming tasks they have. We have already determined the SQL Database Standards. Now we need to determine how to access and use it in our ASP.Net Projects. But first we need to point out the following words:

Datasets - a collection of DataTable objects that you can relate to each other with DataRelation objects. (MSDN)
Database Factory - it is a compilation of codes which is used to access the database. Those functions/methods involved in the codes can be used all over again, thus, boosting the productivity of a programmer.
Data Sources - is a representation of data.

SQL Database Standards

2008-02-26T17:56:00.029+08:00

Table Basics

Table Names - Should alway be in the same meaning with the referred object/item.

Example: Create a table that stores the basic information of a person.
So, the table name should be named as Persons.

Field Names - Should always be in the same meaning with the referred object/item.

Example: Create a table that stores the basic information of a person.
Informations: Given Name, Family Name, Birth Date, Home Address
So, the table fields must be like this:

personID (Primary Key)
firstname (Given Name)
lastname (Family Name)
birthDate (Birth Date)
homeAddress (Home Address)

CRUD Methods (Create, Read, Update, and Delete) - All SQL queries should be in the Stored Procedures. However, if the query involves two or more tables and is used not only for Reading, you can create another Stored Procedure provided it has proper naming conventions (See: Stored Procedures Section). If the query involves two or more tables and is only used for Reading (SELECT Command) it should be created as Views and should by named as format Vw_[database name or initials]_[table name].

Example: Create a Views for Persons Table in SocietyInformation database. So, the Views name would be: Vw_SocietyInformation_Persons

Stored Procedures

Creation - there should only be one stored procedure for each table.

Naming - Should always be in the same name as the table referred plus "Sp_" (Stands for Stored Procedure) and the database name or initials , as the first letters, followed by the underscore symbol. The format should be like this: Sp_[database name or initials]_[table name]

Example: Create a stored procedure for Persons Table in SocietyInformation database.
So, the Store Procedure name would be: Sp_SocietyInformation_Persons

However, if the Store Procedure involves two or more tables and is used not only for Reading, the Stored Procedure's last section name should be the name of the process made. The format should be like this: SP_[database name or initials]_[process name]

Example: Create a stored procedure for Adding, Viewing, Editing, and Deleting a Family Tree.
So, the Store Procedure name would be: Sp_SocietyInformation_FamilyTree

Variable Names - Stored Procedure must always have @option variable, for selecting the section, and @ReturnValue, as the result return value (commonly used to get SCOPE_IDENTITY()) and will be the Reserved VariableNames. Other variables must be in the same meaning of its field name. Use synonyms for the variable whose field name is the same as the two Reserved Variable Names.

Sections - Stored Procedures must be divided into sections for every CRUD method involved. Sections are selected by @option variable. These sections are as follows:

Creating (INSERT Command) - @option = 1 to 200
Reading (SELECT Command) - @option = 601 up
Updating (UPDATE Command) - @option = 201 to 400
Deleting (DELETE Command) - @option = 401 to 600

Note: Reading Section has 601 up sections because SELECT Command is the most used Command in SQL.

---------------------------------------------------------------------------------------------------

SAMPLE

---------------------------------------------------------------------------------------------------

Situation:

Create a database so that a company can see its old and new employees. The database name is CompanyInformation.

Tables:

Persons

personID (PK)
firstname
lastname
homeAddress

Company

companyID (PK)
companyName
address
businessType

Employments

employmentID (PK)
employmentDate
position
companyID (FK)
personID (FK)

Stored Procedures:

Sp_CompanyInformation_Persons

Variables

@option
@ReturnValue
@personID
@firstname
@lastname
@homeAddress

Query

IF @option = 1
BEGIN
INSERT INTO Persons(firstname, lastname, homeAddress)
VALUES(@firstname, @lastname, homeAddress)

SET @ReturnValue = SCOPE_IDENTITY()
Return @ReturnValue
END

ELSE IF@option = 201
BEGIN
UPDATE Persons SET
firstname = @firstname,
lastname = @lastname,
homeAddress = @homeAddress
WHERE personID = @personID

SET @ReturnValue = @personID
Return @ReturnValue
END

ELSE IF @option = 401
BEGIN
DELETE Persons WHERE personID = @personID

SET @ReturnValue = @personID
Return @ReturnValue
END

ELSE IF @option = 601
BEGIN
SELECT * FROM Persons
END

Sp_CompanyInformation_Company

Variables

@option
@ReturnValue
@companyID
@companyName
@address
@businessType

Query

IF @option = 1
BEGIN
INSERT INTO Company(companyName, address, businessType)
VALUES(@companyName, @address, @busineessType)

SET @ReturnValue = SCOPE_IDENTITY()
Return @ReturnValue
END

ELSE IF@option = 201
BEGIN
UPDATE Company SET
companyName = @companyName,
address = @address,
businessType = @businessType
WHERE companyID = @companyID

SET @ReturnValue = @companyID
Return @ReturnValue
END

ELSE IF @option = 401
BEGIN
DELETE Company WHERE companyID = @companyID

SET @ReturnValue = @companyID
Return @ReturnValue
END

ELSE IF @option = 601
BEGIN
SELECT * FROM Company
END

Sp_CompanyInformation_Employment

Variables

@option
@ReturnValue
@employmentID
@employmentDate
@position
@companyID
@personID

Query

IF @option = 1
BEGIN
INSERT INTO Employments(employmentDate, position, companyID, personID)
VALUES(@employmentDate, @position, @companyID, @personID)

SET @ReturnValue = SCOPE_IDENTITY()
Return @ReturnValue
END

ELSE IF@option = 201
BEGIN
UPDATE Employments SET
employmentDate = @employmentDate,
position = @position,
companyID = @companyID,
personID = @personID
WHERE employmentID = @employmentID

SET @ReturnValue = @employmentID
Return @ReturnValue
END

ELSE IF @option = 401
BEGIN
DELETE Employment WHERE employmentID = @employmentID

SET @ReturnValue = @employmentID
Return @ReturnValue
END

ELSE IF @option = 601
BEGIN
SELECT * FROM Employment
END

My Very First Produced Game

2008-01-25T07:46:00.001+08:00

At last I have finished my very own game. Its called the "Super Sweeper". It is a minesweeper, winmine as windows called it, like games that you need to defuse mines that are created randomly. Colorful numbered clues are also provided so that you can pin-point the location of a mine. In addition to the minesweeper game, there are new features that the gamer would really loved. These are the Following:

Point System - points are based by the clues opened, mines defused, mistakes committed, time elapsed, round bonus, and the remaining life in a round.
High Scores - high score will be based in points, not in time.
Level-based mine count - number of mines increase as level increases.
Life Points - has 10 Max. life in the game.
Bonus Life Rewards - a bonus life will be rewarded if there will be no mistake in a round.
Round Bonus - round bonus is a point reward that will be given if you will have 3, at the most, mistakes.

To those who love to play windows minesweeper, you can try this at home. I hope you'll loved it. I'll post soon the address where you can download it.

Controller Name	Controller Class Name
Profile	ProfileController
Department	DepartmentController
Request	RequestController