SQL SELECT WHERE NOT EXISTS

SQL SELECT WHERE NOT EXISTS


找出t_SYS_User資料表中，與t_SYS_Group之間沒有相關連的資料

  SELECT * FROM (
 
 SELECT docno,name,ename,goid FROM t_SYS_User
 
  ) u
  WHERE NOT EXISTS (
 
      SELECT oid FROM t_SYS_Group
 
   WHERE oid=u.goid
  )
 

Searching in SQL Multiple Keyword in a Field

Searching in SQL Multiple Keyword in a Field


Filter: OR

string str = "john,jack,alton";
 
string select = "SELECT * FROM [TableName] WHERE [UserName] LIKE ''%' + REPLACE(str , ',' , '%'' OR [UserName] LIKE ''%') + '%''";

Filter: AND

string str = "john,jack,alton";
 
string select = "SELECT * FROM [TableName] WHERE [UserName] LIKE ''%' + REPLACE(str , ',' , '%'' AND [UserName] LIKE ''%') + '%''";

ref url:
http://forums.asp.net/t/1683482.aspx?Sql+query+to+search+database+with+multiple+keywords

MSSQL Update Select

MSSQL Update Select

UPDATE
 
    TableA
 
SET
 
    TableA.docno = TableB.docno,
 
    TableA.name = TableB.name
 
FROM
 
    TableB
 
WHERE TableA.id=TableB.id
 
;
 

MSSQL ROLLBACK & COMMIT

MSSQL ROLLBACK & COMMIT


ROLLBACK:

BEGIN TRANSACTION doUpdate
 
--do insert, update, delete
 
ROLLBACK TRANSACTION doUpdate

COMMIT:

BEGIN TRANSACTION doUpdate
 
--do insert, update, delete
 
COMMIT TRANSACTION doUpdate

ref:
https://msdn.microsoft.com/zh-tw/library/ms188929%28v=sql.120%29.aspx
https://msdn.microsoft.com/zh-tw/library/ms190295%28v=sql.120%29.aspx

若要允許使用 ASP.NET AJAX 從指令碼呼叫此 Web 服務

若要允許使用 ASP.NET AJAX 從指令碼呼叫此 Web 服務

// 若要允許使用 ASP.NET AJAX 從指令碼呼叫此 Web 服務，請取消註解下一行。
[System.Web.Script.Services.ScriptService]

Full Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
 
namespace WebHRM.Service
{
    /// <summary>
    /// WebService1 的摘要描述
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // 若要允許使用 ASP.NET AJAX 從指令碼呼叫此 Web 服務，請取消註解下一行。
    [System.Web.Script.Services.ScriptService]
    public class WebService1 : System.Web.Services.WebService
    {
 
        [WebMethod]
        public string HelloWorld(string name)
        {
            return "Hello World" + name;
        }
    }
}