Lib: 2009/04

2009/04/26

Data Paging

Sql Server 2005

DECLARE @PageIndex INT;
DECLARE @PageSize INT;
SET @PageSize = 3;
SET @PageIndex = 1;

DECLARE @StartIndex INT;
DECLARE @EndIndex INT;
SET @StartIndex = @PageSize * (@PageIndex-1) +1;
SET @EndIndex = @StartIndex + @PageSize - 1;


WITH ProductEntires AS(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY [UpdateDate] DESC) AS RowIndex, 
        [ID], 
        [ProductName], 
        [ProductDescription], 
        [ProductImage1], 
        [ProductTitle1], 
        [VotedAmount] 
    FROM 
        [TblProduct] 
)

SELECT * 
FROM ProductEntires
WHERE
    [RowIndex] BETWEEN @StartIndex AND @EndIndex

2009/04/17

Temp table sample

MySQL

-- Procedure "IsActionAllowed" DDL

CREATE DEFINER=`root`@`%` PROCEDURE `IsActionAllowed`(IN roleid CHAR(36), IN functionid CHAR(36))
BEGIN
    DECLARE rid CHAR(36);
    DECLARE base_role CHAR(36);
    DECLARE exist INT;

    CREATE TEMPORARY TABLE TempTable (RID CHAR(36)) TYPE=INNODB;

    INSERT INTO TempTable VALUES(roleid);
    SET base_role = '';
    SET rid = roleid;

    WHILE base_role IS NOT NULL DO
        IF base_role <> '' THEN
            INSERT INTO TempTable VALUES(base_role);
        END IF;

        SELECT
            tbl_roles.BASE_ON_ROLE INTO base_role
        FROM
            tbl_roles
        WHERE
            tbl_roles.ROLE_ID = rid;

        SET rid = base_role;

    END WHILE;

    SELECT
        COUNT(tbl_role_permission.ROLE_ID) INTO exist
    FROM
        tbl_role_permission
    WHERE
        tbl_role_permission.ROLE_ID IN (SELECT * FROM TempTable)
        AND tbl_role_permission.FUNCTION_ID = functionid;

    DROP TEMPORARY TABLE IF EXISTS TempTable;

    IF exist > 0 THEN
        SELECT true AS Allowed;
    ELSE
        SELECT false AS Allowed;
    END IF;
END;

Procedure tutorial : Stored Procedure Parameters

2009/04/13

MyUtil

using System;

namespace Bruce.Lib
{
    public class BUtil
    {
        /// <summary>
        /// 測試參數中是否含有null或空值
        /// </summary>
        /// <param name="data">任意物件</param>
        /// <returns>含有null、DBNull或空值則為true，反之為false</returns>
        public static bool ContainsNullOrEmpty(params object[] data)
        {
            foreach (object d in data)
            {
                if (d == null || d == DBNull.Value || string.IsNullOrEmpty(d.ToString()))
                    return true;
            }
            return false;
        }
        /// <summary>
        /// 將任意物件轉成字串，免去再判斷null或DBNull的處理
        /// </summary>
        /// <param name="data">任意物件</param>
        /// <returns></returns>
        public static string StringValue(object data)
        {
            return data == null || data == DBNull.Value ? string.Empty : Convert.ToString(data);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="data"></param>
        /// <param name="length"></param>
        /// <returns></returns>
        public static string Summary(object data, int length)
        {
            if (length < 1) throw new ArgumentException();
            string str = StringValue(data);
            return str.Length > length ? str.Substring(0, length) + "..." : str;
        }
    }
}

2009/04/09

Select row by clicking in a GridView

GridViewSelectRow.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridViewSelectRow.aspx.cs"
    Inherits="GridViewSelectRow" EnableEventValidation="false" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Select row by clicking in a GridView</title>
</head>
<body>
    <form id="form1" runat="server">
    <table border="1">
        <tr>
            <td>
                SELECTED ID
            </td>
            <td>
                <asp:Label ID="sID" runat="server" Text="Label"></asp:Label>
            </td>
        </tr>
        <tr>
            <td>
                SELECTED UNIQUEID
            </td>
            <td>
                <asp:Label ID="sUniqueID" runat="server" Text="Label"></asp:Label>
            </td>
        </tr>
        <tr>
            <td>
                SELECTED PRICE
            </td>
            <td>
                <asp:Label ID="sPrice" runat="server" Text="Label"></asp:Label>
            </td>
        </tr>
        <tr>
            <td>
                SELECTED AMOUNT
            </td>
            <td>
                <asp:Label ID="sAmount" runat="server" Text="Label"></asp:Label>
            </td>
        </tr>
        <tr>
            <td>
                SELECTED CREATEDATE
            </td>
            <td>
                <asp:Label ID="sCreateDate" runat="server" Text="Label"></asp:Label>
            </td>
        </tr>
    </table>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID,UniqueID"
        DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound" OnSelectedIndexChanged="GridView1_SelectedIndexChanged"
        AllowPaging="True">
        <Columns>
            <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
                SortExpression="ID" />
            <asp:BoundField DataField="UniqueID" HeaderText="UniqueID" ReadOnly="True" SortExpression="UniqueID" />
            <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
            <asp:BoundField DataField="Amount" HeaderText="Amount" SortExpression="Amount" />
            <asp:BoundField DataField="CreateDate" HeaderText="CreateDate" SortExpression="CreateDate" />
        </Columns>
    </asp:GridView>
    <div>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:brucedbConnectionString %>"
            SelectCommand="SELECT * FROM [GV]"></asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

GridViewSelectRow.aspx.cs

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class GridViewSelectRow : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Attributes["onclick"] = ClientScript.GetPostBackClientHyperlink(this.GridView1, "Select$" + e.Row.RowIndex);
            e.Row.Style["cursor"] = "hand";
        }
    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {
        GridViewRow row = ((GridView)sender).SelectedRow;
        this.sID.Text = row.Cells[0].Text;
        this.sUniqueID.Text = row.Cells[1].Text;
        this.sPrice.Text = row.Cells[2].Text;
        this.sAmount.Text = row.Cells[3].Text;
        this.sCreateDate.Text = row.Cells[4].Text;
    }
}

2009/04/08

About ACL control.

reference : http://blog.crowe.co.nz/archive/2007/08/25/c---An-example-of-setting-NTFS-Directory--File.aspx

using System.IO;
using System.Security.AccessControl;

namespace Bruce.Lib
{
    public class DirectoryPermissions
    {
        private static void Main(string[] args)
        {
            string ADDomain = "Domain";
            string ADUser = "Chris";
            string Path = @"c:\temp\chris";

            if (Directory.Exists(Path) == false)
                Directory.CreateDirectory(Path);

            // Remove any inheritable permissions from the path 
            RemoveInheritablePermissons(Path);

            // Add the access control entries for the path 
            AddDirectorySecurity(Path, ADDomain + "\\" + ADUser, FileSystemRights.Modify,
                                 InheritanceFlags.ContainerInherit | InheritanceFlags.ObjectInherit,
                                 PropagationFlags.None, AccessControlType.Allow);
            AddDirectorySecurity(Path, ADDomain + "\\Domain Users", FileSystemRights.Delete, InheritanceFlags.None,
                                 PropagationFlags.None, AccessControlType.Deny);
            AddDirectorySecurity(Path, ADDomain + "\\Domain Admins", FileSystemRights.FullControl,
                                 InheritanceFlags.ContainerInherit | InheritanceFlags.ObjectInherit,
                                 PropagationFlags.None, AccessControlType.Allow);
        }


        // Adds an ACL entry on the specified directory for the specified account. 
        public static void AddDirectorySecurity(string FileName, string Account, FileSystemRights Rights,
                                                InheritanceFlags Inheritance, PropagationFlags Propogation,
                                                AccessControlType ControlType)
        {
            // Create a new DirectoryInfo object. 
            DirectoryInfo dInfo = new DirectoryInfo(FileName);
            // Get a DirectorySecurity object that represents the  
            // current security settings. 
            DirectorySecurity dSecurity = dInfo.GetAccessControl();
            // Add the FileSystemAccessRule to the security settings.  
            dSecurity.AddAccessRule(new FileSystemAccessRule(Account,
                                                             Rights,
                                                             Inheritance,
                                                             Propogation,
                                                             ControlType));
            // Set the new access settings. 
            dInfo.SetAccessControl(dSecurity);
        }

        // Removes an ACL entry on the specified directory for the specified account. 
        public static void RemoveDirectorySecurity(string FileName, string Account, FileSystemRights Rights,
                                                   AccessControlType ControlType)
        {
            // Create a new DirectoryInfo object. 
            DirectoryInfo dInfo = new DirectoryInfo(FileName);
            // Get a DirectorySecurity object that represents the  
            // current security settings. 
            DirectorySecurity dSecurity = dInfo.GetAccessControl();
            // Add the FileSystemAccessRule to the security settings.  
            dSecurity.RemoveAccessRule(new FileSystemAccessRule(Account,
                                                                Rights,
                                                                ControlType));
            // Set the new access settings. 
            dInfo.SetAccessControl(dSecurity);
        }

        // Removes an ACL entry on the specified directory for the specified account. 
        public static void RemoveInheritablePermissons(string FileName)
        {
            // Create a new DirectoryInfo object. 
            DirectoryInfo dInfo = new DirectoryInfo(FileName);
            // Get a DirectorySecurity object that represents the  
            // current security settings. 
            DirectorySecurity dSecurity = dInfo.GetAccessControl();
            // Add the FileSystemAccessRule to the security settings. 
            const bool IsProtected = true;
            const bool PreserveInheritance = false;
            dSecurity.SetAccessRuleProtection(IsProtected, PreserveInheritance);
            // Set the new access settings. 
            dInfo.SetAccessControl(dSecurity);
        }

    }
}

2009/04/05

Get the column name which is a primary key

Sql server

DECLARE @TableName VARCHAR(128)
SELECT @TableName = 'test_table'

SELECT c.COLUMN_NAME 
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE
    pk.TABLE_NAME = @TableName
    AND CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND c.TABLE_NAME = pk.TABLE_NAME
    AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

Get all table's name from db

Sql server

SELECT * FROM INFORMATION_SCHEMA.TABLES

Access

SELECT * FROM MSYSOBJECTS

MySQL

SHOW TABLES

Oracle

SELECT * FROM USER_OBJECTS

2009/04/04

Window service sample

First, create a new window service project.

For schedule service, we need to add System.Timers.Timer to toolbox, and drag it into design mode.

Then, code something.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.IO;

namespace WindowServiceSample
{
    public partial class Service1 : ServiceBase
    {
        public Service1()
        {
            InitializeComponent();
        }

        protected override void OnStart(string[] args)
        {
            // 服務啟動時執行
            this.EventLog.WriteEntry(System.AppDomain.CurrentDomain.BaseDirectory);  // write current directory to event log
        }

        protected override void OnStop()
        {
            // 服務結束時執行
        }

        private void timer1_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            // 計時器, 依據設定間隔執行
        }
    }
}

After coding, we need to add a installer for Window Service Installer.

There are two components needed to set, serviceProcessInstaller and serviceInstaller

Build the project and find the executable binary, enter the command below for installing.

%windir%\Microsoft.NET\Framework\v2.0.50727\installutil WindowServiceSample.exe

There will be a new service listed in Services.

For unstalling, enter command below:

%windir%\Microsoft.NET\Framework\v2.0.50727\installutil /u WindowServiceSample.exe

2009/04/01

Permutation

package bruce.math;

import java.math.BigDecimal;

public class Permutation {
    public static BigDecimal H(int n, int m) throws IllegalArgumentException {
        return C(n + m - 1, m);
    }

    public static BigDecimal P(int n, int m) throws IllegalArgumentException {
        return C(n, m).multiply(factorial(m));
    }

    public static BigDecimal C(int n, int m) throws IllegalArgumentException {
        if (n < m)
            throw new IllegalArgumentException("n must great equal than m.");
        return factorial(n).divide(factorial(n - m).multiply(factorial(m)));
    }

    public static BigDecimal factorial(int num) throws IllegalArgumentException {
        if (num < 0)
            throw new IllegalArgumentException("num must great than zero.");
        return num <= 1 ? new BigDecimal(1) : factorial(num - 1).multiply(new BigDecimal(num));
    }
}