- 5月 18 週四 200618:33
Windows security for .NET
- 5月 04 週四 200615:53
批次變更SQL SERVER 欄位的定序
SELECT 'ALTER TABLE '+A.name+' ALTER COLUMN '+B.name+
CASE WHEN B.xusertype = 231 THEN ' nvarchar' WHEN B.xusertype = 175 THEN ' char' END+' ('+
convert(varchar,B.prec)+') COLLATE Chinese_Taiwan_Stroke_BIN '+
CASE WHEN B.isnullable=1 THEN 'NULL' ELSE 'NOT NULL' END
FROM dbo.sysobjects A
INNER JOIN dbo.syscolumns B ON B.id=A.id
WHERE A.type='U' AND B.collation='Chinese_Taiwan_Stroke_CS_AS'
ORDER BY A.name
注意:KEY值欄位得先把索引移除，改完後再加回
定序:
Chinese_Taiwan_Stroke_CS_AS (繁體中文區分大小寫)
Chinese_Taiwan_Stroke_CI_AS (繁體中文不分大小寫)
CASE WHEN B.xusertype = 231 THEN ' nvarchar' WHEN B.xusertype = 175 THEN ' char' END+' ('+
convert(varchar,B.prec)+') COLLATE Chinese_Taiwan_Stroke_BIN '+
CASE WHEN B.isnullable=1 THEN 'NULL' ELSE 'NOT NULL' END
FROM dbo.sysobjects A
INNER JOIN dbo.syscolumns B ON B.id=A.id
WHERE A.type='U' AND B.collation='Chinese_Taiwan_Stroke_CS_AS'
ORDER BY A.name
注意:KEY值欄位得先把索引移除，改完後再加回
定序:
Chinese_Taiwan_Stroke_CS_AS (繁體中文區分大小寫)
Chinese_Taiwan_Stroke_CI_AS (繁體中文不分大小寫)
- 4月 26 週三 200613:25
SQL 2005 預設不支援 *= 或 =* 的語法
SQL 2005 預設不支援 *= 或 =* 的語法
如果要需要的話,在 Sql Server Managment Studio 裡, 需要的DB的屬性->選項->相容性層級
將相容性層級改成 "SQL Server 2000 (80)" .
不過要注意,此後這個DB裡一些 SQL 2005 新增的功能會無法使用 , 例如 XML 的欄位型態
如果要需要的話,在 Sql Server Managment Studio 裡, 需要的DB的屬性->選項->相容性層級
將相容性層級改成 "SQL Server 2000 (80)" .
不過要注意,此後這個DB裡一些 SQL 2005 新增的功能會無法使用 , 例如 XML 的欄位型態
- 4月 19 週三 200600:04
SQL 2005 新語法 "CROSS APPLY"
要在同一個Query中,呼叫 Function 時將另一個 Table 的欄位當做參數傳給 Function
,這在SQL 2000 裡是做不到的.
但是 Sql Server 2005 裡可以使用 " CROSS APPLY " 來達到這個功能.
範例:
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
參考資料
http://msdn2.microsoft.com/en-us/library/ms175156.aspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp
,這在SQL 2000 裡是做不到的.
但是 Sql Server 2005 裡可以使用 " CROSS APPLY " 來達到這個功能.
範例:
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
參考資料
http://msdn2.microsoft.com/en-us/library/ms175156.aspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp
- 4月 14 週五 200615:31
Visual Studio 2005 Team System 相關資源
- 4月 13 週四 200618:17
Row_Number() 的運用 上一筆,下一筆
下一筆
SELECT RowNum,USER_GUID,NAME
FROM (SELECT USER_GUID,NAME , ROW_NUMBER() OVER(ORDER BY USER_GUID) as RowNum
FROM TB_EB_USER ) as DerivedTableNameW
WHERE RowNum =
(SELECT RowNum
FROM
(SELECT USER_GUID,ROW_NUMBER() OVER(ORDER BY USER_GUID) as RowNum
FROM TB_EB_USER
) as DerivedTableName
WHERE USER_GUID ='994669d3-822a-40e2-82fc-622b93279386') + 1
SELECT RowNum,USER_GUID,NAME
FROM (SELECT USER_GUID,NAME , ROW_NUMBER() OVER(ORDER BY USER_GUID) as RowNum
FROM TB_EB_USER ) as DerivedTableNameW
WHERE RowNum =
(SELECT RowNum
FROM
(SELECT USER_GUID,ROW_NUMBER() OVER(ORDER BY USER_GUID) as RowNum
FROM TB_EB_USER
) as DerivedTableName
WHERE USER_GUID ='994669d3-822a-40e2-82fc-622b93279386') + 1
上一筆
SELECT RowNum,USER_GUID,NAME
FROM (SELECT USER_GUID,NAME , ROW_NUMBER() OVER(ORDER BY USER_GUID) as RowNum
FROM TB_EB_USER ) as DerivedTableNameW
WHERE RowNum =
(SELECT RowNum
FROM
(SELECT USER_GUID,ROW_NUMBER() OVER(ORDER BY USER_GUID) as RowNum
FROM TB_EB_USER
) as DerivedTableName
WHERE USER_GUID ='994669d3-822a-40e2-82fc-622b93279386') - 1
FROM (SELECT USER_GUID,NAME , ROW_NUMBER() OVER(ORDER BY USER_GUID) as RowNum
FROM TB_EB_USER ) as DerivedTableNameW
WHERE RowNum =
(SELECT RowNum
FROM
(SELECT USER_GUID,ROW_NUMBER() OVER(ORDER BY USER_GUID) as RowNum
FROM TB_EB_USER
) as DerivedTableName
WHERE USER_GUID ='994669d3-822a-40e2-82fc-622b93279386') - 1
- 4月 13 週四 200618:00
利用 SQL SERVER 2005 新功能達到分頁效果
下列的語法可以SELECT 出第 10筆到第20筆之間的資料,
靠的是SQL 2005 的新功能 ROW_NUMBER()
SELECT RowNum,USER_GUID,NAME
FROM
(SELECT USER_GUID,NAME ,
ROW_NUMBER() OVER(ORDER BY USER_GUID) as RowNum
FROM TB_EB_USER
) as DerivedTableName
WHERE RowNum BETWEEN 10 AND 20
- 4月 11 週二 200612:50
.CHM 打不開
- 4月 04 週二 200615:24
BASE64編碼與解碼
public string base64Encode(string data)
{
try
{
byte[] encData_byte = new byte[data.Length];
encData_byte = System.Text.Encoding.UTF8.GetBytes(data);
string encodedData = Convert.ToBase64String(encData_byte);
return encodedData;
}
catch(Exception e)
{
throw new Exception("Error in base64Encode" + e.Message);
}
}
public string base64Decode(string data)
{
try
{
System.Text.UTF8Encoding encoder = new System.Text.UTF8Encoding();
System.Text.Decoder utf8Decode = encoder.GetDecoder();
byte[] todecode_byte = Convert.FromBase64String(data);
int charCount = utf8Decode.GetCharCount(todecode_byte, 0, todecode_byte.Length);
char[] decoded_char = new char[charCount];
utf8Decode.GetChars(todecode_byte, 0, todecode_byte.Length, decoded_char, 0);
string result = new String(decoded_char);
return result;
}
catch(Exception e)
{
throw new Exception("Error in base64Decode" + e.Message);
}
}
{
try
{
byte[] encData_byte = new byte[data.Length];
encData_byte = System.Text.Encoding.UTF8.GetBytes(data);
string encodedData = Convert.ToBase64String(encData_byte);
return encodedData;
}
catch(Exception e)
{
throw new Exception("Error in base64Encode" + e.Message);
}
}
public string base64Decode(string data)
{
try
{
System.Text.UTF8Encoding encoder = new System.Text.UTF8Encoding();
System.Text.Decoder utf8Decode = encoder.GetDecoder();
byte[] todecode_byte = Convert.FromBase64String(data);
int charCount = utf8Decode.GetCharCount(todecode_byte, 0, todecode_byte.Length);
char[] decoded_char = new char[charCount];
utf8Decode.GetChars(todecode_byte, 0, todecode_byte.Length, decoded_char, 0);
string result = new String(decoded_char);
return result;
}
catch(Exception e)
{
throw new Exception("Error in base64Decode" + e.Message);
}
}
- 3月 08 週三 200617:23
特殊的URL 開頭 MSN , SKYPE , YAHOO
點下去可以開啟MSN的對話框,當然你的電腦要先裝MSN
<a href="msnim:chat?contact=MSN的帳號">MSN</a>
點下去直接撥SKYPE電話
<a href="callto:SKYPE 的帳號">SKYPE</a>
<a href="ymsgr:sendim?ajun1223">YAHOO</a>
<a href="msnim:chat?contact=MSN的帳號">MSN</a>
點下去直接撥SKYPE電話
<a href="callto:SKYPE 的帳號">SKYPE</a>
點下去可以開啟Yahoo messenger的對話框
<a href="ymsgr:sendim?ajun1223">YAHOO</a>