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;
        }
    }
}