Author: fransiscuss Page 16 of 20

Paging in Order BY using LINQ

On August 29, 2008

This is a simple way of doing the paging in LINQ. If I do the paging before the ORDER BY then it will give me different result set. The paging is should be after the ORDER BY Statement. So you sort first then you paging it.

        /// 
        /// to get all the have your say using paging based on optional parameter
        /// of isapproved and sorted descending based on the posted date
        /// 
        /// 
        /// 
        /// 
        /// 
        public IQueryable HaveYourSaySelectApproved(bool? IsApproved, int startPage, int pageSize)
        {
            var haveyoursay = from h in HaveYourSayContext.HaveYourSays
                                      orderby h.DatePosted descending
			select h;

            if (IsApproved.HasValue)
            {
                haveyoursay = from h in HaveYourSayContext.HaveYourSays
                              where (h.IsApproved == IsApproved.Value)
                              orderby h.DatePosted descending
                              select h;
            }

	return haveyoursay.Skip(startPage * pageSize).Take(pageSize);
        }

Inserted Table and Deleted Table On SQL Server

By fransiscuss

On August 29, 2008

In SQL Server & Data

this is only a simple trigger and table to monitor the activity on an update activity to “members” table. it tracks the date when the update query is being executed and who execute it, it will be quite useful for a database that has many applications accessing it with different SQL User

Holding table :

CREATE TABLE [dbo].[MemberUpdateTracking](
	[MemberTrackingID] [int] IDENTITY(1,1) NOT NULL,
	[MemberID] [int] NULL,
	[BeforeMemberCode] [varchar](50) NULL,
	[BeforeFirstName] [varchar](255) NULL,
	[BeforeSurname] [varchar](255) NULL,
	[BeforeUserName] [varchar](255) NULL,
	[BeforePassword] [varchar](255) NULL,
	[BeforeEmailAddress] [varchar](255) NULL,
	[AfterMemberCode] [varchar](50) NULL,
	[AfterFirstName] [varchar](255) NULL,
	[AfterSurname] [varchar](255) NULL,
	[AfterUserName] [varchar](255) NULL,
	[AfterPassword] [varchar](255) NULL,
	[AfterEmailAddress] [varchar](255) NULL,
	[ModifiedDate] [datetime] NOT NULL,
	[SQLUser] [varchar](255) NOT NULL,
 CONSTRAINT [PK_MemberUpdateTracking] PRIMARY KEY CLUSTERED
(
	[MemberTrackingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Triggers:

CREATE TRIGGER [dbo].[tr_Member_UPDATE]
	ON [dbo].[Members]
	FOR UPDATE
AS
BEGIN
	SET	NOCOUNT ON

	DECLARE @MemberID			int
	DECLARE @AfterFirstName		varchar(255)
	DECLARE @AfterSurname		varchar(255)
	DECLARE @AfterMemberCode	varchar(50)
	DECLARE @AfterUserName		varchar(255)
	DECLARE @AfterPassword		varchar(255)
	DECLARE @AfterEmailAddress	varchar(255)

	DECLARE @BeforeFirstName	varchar(255)
	DECLARE @BeforeSurname		varchar(255)
	DECLARE @BeforeMemberCode	varchar(50)
	DECLARE @BeforeUserName		varchar(255)
	DECLARE @BeforePassword		varchar(255)
	DECLARE @BeforeEmailAddress	varchar(255)


	--get information what will been inserted
	SELECT @MemberID = MemberID,
		   @AfterFirstName = FirstName,
		   @AfterSurname = Surname,
		   @AfterUserName = Username,
		   @AfterPassword = [Password],
		   @AfterMemberCode = MemberCode,
		   @AfterEmailAddress = EmailAddress
	FROM
		Inserted

	--only need for strange behaviour issue that we had before otherwise not necessary
	IF (@AfterFirstName = '' OR @AfterSurname = '' OR @AfterFirstName IS NULL OR @AfterSurname IS NULL)
	BEGIN
		--get the original information
		SELECT  @BeforeFirstName = FirstName,
				@BeforeSurname = Surname,
				@BeforeUserName = Username,
				@BeforePassword = [Password],
				@BeforeMemberCode = MemberCode,
				@BeforeEmailAddress = EmailAddress
		FROM
			Deleted

		--track the changes
		INSERT INTO MemberUpdateTracking(MemberID, BeforeMemberCode, BeforeFirstName,
					BeforeSurname, BeforeUserName, BeforePassword, BeforeEmailAddress,
					AfterMemberCode, AfterFirstName, AfterSurname, AfterUserName, AfterPassword,
					AfterEmailAddress, ModifiedDate, SQLUser)
		VALUES
			(@MemberID, @BeforeMemberCode, @BeforeFirstName, @BeforeSurname,
					@BeforeUserName, @BeforePassword, @BeforeEmailAddress, @AfterMemberCode,
					@AfterFirstName, @AfterSurname,
					@AfterUserName, @AfterPassword, @AfterEmailAddress,
					GETDATE(), SYSTEM_USER)
	END
END

Undo compressing old files by windows

By fransiscuss

On August 29, 2008

In Uncategorized

My computer was losing its graphic card driver and somehow losing sound as well and I realised that i’ve been compressing the old files via disk clean up.

I thought i want to do reformat my PC, I tried to google to undo the compressing old files by windows and I’ve found that you need to run this from the command prompt. It will uncompress all the old files that has been compressed through windows disk clean up.I wonder why they don’t create a functionality to undo this.

C:\>compact /u /s /a /q /i *.*

Find a control in ContentPlaceHolder

By fransiscuss

On August 29, 2008

In ASP.NET, VB.NET

I have a user control and I would like to hide a control on the page container that host this user control. I try to use

Page.FindControl("litContent")

But It throws an error of object not found. The solution is you need to find the contentplaceholder first on the master page then you find the control that you want from that placeholder. It’s a bit tricky since i thought once you get a Page instance then you can get all control hosted on the Page instance itself.

1. Place this tag on your asp.net page where it hosts your control

2. go to the page behind of the user control/page where you want to place your logic to hide/show it

'hide the dynamic page from the parent page
Dim mainContent As ContentPlaceHolder = CType(Page.Master.FindControl("ContentPlaceHolder1"), ContentPlaceHolder)

If Not (mainContent Is Nothing) Then
   Dim contentPlaceHolder As Literal = CType(mainContent.FindControl("litContent"),Literal)
   If Not (contentPlaceHolder Is Nothing) Then
       contentPlaceHolder.Visible = False
   End If
End If

AJAX Update Panel Timeout Issue

By fransiscuss

On August 29, 2008

In ASP.NET, JavaScript

I have an asp.net page where the page will query the report from the database. To query the report itself might cause timeout exception on the page itself before the report result is being returned from database.

You need to do it in two steps, first you need to override the script timeout only for the pageitself. put this code in your code behind

    Dim pageTimeOut As Integer
Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
        pageTimeOut = Server.ScriptTimeout

        'this is in seconds
        Server.ScriptTimeout = 2500
End Sub

Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload

Server.ScriptTimeout = pageTimeOut

End Sub

The next step is required only if you use AJAX Update panel. Put this script on the aspx page


   Sys.WebForms.PageRequestManager.getInstance().add_endRequest(
        function (sender, args)
        {
             if (args.get_error() && args.get_error().name == 'Sys.WebForms.PageRequestManagerTimeoutException')
            {
                  // remember to set errorHandled = true to keep from getting a popup from the AJAX library itself
                 args.set_errorHandled(true);
             }
        });

Simple LINQ Tutorial

By fransiscuss

On August 29, 2008

In C#

I’ve started learning about LINQ because I need to keep up to date with the latest technology out there. I’ve created a simple Business Layer which interact directly with Linq to SQL(DBML). It includes Insert, Update, Delete, Select and paging with LINQ.

Hopefully this tutorial will be useful enough for someone who is going to learn about LINQ. With LINQ we can really simplify/integrate the stored procedure into our Code base but it doesn’t mean LINQ does not support Stored Procedure.

It supports Stored Procedure as well since we might use Stored Procedure for complex calculation. You don’t need to create a table adapter anymore since LINQ does everything the same as table adapter.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LinqCore.Factories
{
    public class GroupFactory
    {

        /// 
        /// this is used to get all the groups
        /// 
        /// 
        public IQueryable GetAllGroups()
        {
            GroupsDataContext db = new GroupsDataContext();
            var groups = from g in db.Groups
                         select g;

            return groups;
        }

        /// 
        /// this is used to get the group based on the group id
        /// 
        /// 
        /// 
        public Group GetGroup(int groupid)
        {
            GroupsDataContext db = new GroupsDataContext();

            var groups = from g in db.Groups
                         where (g.GroupID == groupid)
                         select g;

            return groups.SingleOrDefault();
        }

        /// 
        /// this is used to insert Or Update which determined by the nullable
        /// integer value of the groupID
        /// 
        /// 
        /// 
        /// 
        public void GroupUpdate(int? groupID, string groupName, bool valid)
        {
            GroupsDataContext db = new GroupsDataContext();
            Group group = new Group();

            if (groupID.HasValue)
            {
               group = db.Groups.Single(p => p.GroupID == groupID.Value);
            }

            group.GroupName = groupName;
            group.valid = valid;

            if (!groupID.HasValue)
            {
                db.Groups.InsertOnSubmit(group);
            }
            db.SubmitChanges();
        }

        /// 
        /// this is used to delete a group based on its ID
        /// 
        /// 
        public void GroupDelete(int groupID)
        {
            GroupsDataContext db = new GroupsDataContext();

            Group group = db.Groups.Single(p => p.GroupID == groupID);
            db.Groups.DeleteOnSubmit(group);
            db.SubmitChanges();
        }

        /// 
        /// this is used to do paging for the returned result
        /// 
        /// 
        /// 
        /// 
        public IQueryable GetAllGroups(int startIndex, int pageSize)
        {
            GroupsDataContext db = new GroupsDataContext();
            var groups = from g in db.Groups
                         select g;

            return groups.Skip(startIndex).Take(pageSize);
        }
    }
}

Using temp table and SQL stored procedure in Table Adapter

By fransiscuss

On August 29, 2008

In SQL Server & Data

I was having a problem when adding a stored procedure into table adapter. The error was “Invalid object” of my temporary table name inside stored procedure.

It’s a bit strange since what i thought the table adapter only care about the results being returned but what it is actually checking up the existence of the table it self. FMTONLY is used to “Returns only metadata to the client. Can be used to test the format of the response without actually running the query”.

--uncomment this to regenerate it in table adapter
--comment it once you are done with generating the table adapter
SET FMTONLY OFF

File Stream/Stream response in AJAX Update Panel

By fransiscuss

On August 29, 2008

In ASP.NET, C#

Last few months, I got a problem to place a button to generate a CSV file in the update panel. I was having a problem related with response error. So what i did was to place the button outside the Update Panel. Today, I found a simple solution to place this button inside the AJAX Update panel

1. You need to set the page mode on the page load event

protected void Page_Load(object sender, EventArgs e)
{
    this.Page.Form.Enctype = "multipart/form-data";
}

2. set the update Panel Children as trigger to true

3. set the trigger to your button

Self Recursive Deleting On self foreign key sql server

By fransiscuss

On August 29, 2008

In SQL Server & Data

I’ve been assigned a task to delete data from a table with a subtree structure which means the foreign key can be contained in another rows in the same table. It’s a self contained foreign key on the same table.

Theoritically when it comes to my mind, i was thinking of recursive delete. But how????I’ve tried to google about it, I found this article from Microsoft. These are coming from Microsoft Article as well.

These are the dummy data that you can play around with in your DB

CREATE TABLE Employees
(empid int NOT NULL,
 mgrid int NULL,
 empname varchar(25) NOT NULL,
 salary money NOT NULL,
 CONSTRAINT PK_Employees_empid PRIMARY KEY(empid))

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 1, NULL, 'Nancy',  $10000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 2,    1, 'Andrew',  $5000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 3,    1, 'Janet',   $5000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 4,    1, 'Margaret',$5000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 5,    2, 'Steven',  $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 6,    2, 'Michael', $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 7,    3, 'Robert',  $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 8,    3, 'Laura',   $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 9,    3, 'Ann',     $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(10,    4, 'Ina',     $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(11,    7, 'David',   $2000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(12,    7, 'Ron',     $2000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(13,    7, 'Dan',     $2000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(14,   11, 'James',   $1500.00)

I’m going to delete employee no 3(Janet) which means it need to delete 7(Robert),8(Laura),9(Ann) and 11(David),12(Ron),13(Dan), 14(James) based on the managerial structure level.

What you need to do is to create a trigger for cascading delete

CREATE TRIGGER trg_d_employees_on_delete_cascade ON Employees FOR DELETE
AS

IF EXISTS(SELECT *
          FROM
              Employees AS E
            JOIN
              deleted   AS D ON E.mgrid = D.empid)

  DELETE FROM Employees
  FROM
      Employees AS E
    JOIN
      deleted   AS D ON E.mgrid = D.empid

  GO

It will self join based on the deleted table. It’s not finish yet, you need to set the recursive trigger to active by executing command like

ALTER DATABASE DMO
SET RECURSIVE_TRIGGERS ON

Action Time:

DELETE FROM employees WHERE empid = 3

and you will see that it deletes all the subordinates of Janet

How to remove checked item in Checked Box List

By fransiscuss

On August 29, 2008

In .NET & C#

At the moment, I’m learning towards win development. I got a very simple problem which is “Removing checked item on checked box list”. The first code below is throwing an error since you try to delete an item then on the postback it will reset the index. The real solution is on the second code

//this code is throwing an error about the index position
 private void btnRemove_Click(object sender, EventArgs e)
        {
            foreach (SMSData item in chkSmsList.CheckedItems)
            {
                chkSmsList.Items.Remove(item);
            }
        }

Fix

//this is the code which is working perfectly, basically this code always looking into the first item on the "CheckedItems" Stack
  private void btnRemove_Click(object sender, EventArgs e)
        {
            while (chkSmsList.CheckedItems.Count > 0)
            {
                chkSmsList.Items.Remove(chkSmsList.CheckedItems[0]);
            }
        }