由預存程序判斷登入錯誤次數及鎖定時間

CREATE PROC Login_OP @SerialNo varchar(13),@IDNO char(10), @passwd char(32),@ErrorChk char(1) ,@RetryTimes int,@LockTime int,@errcnt int output, @RemainderTime int output ,@LogIP varchar(50),@LogIPXFORWARD varchar(50),@LogIPREMOTEADD varchar(50),@MachineName varchar(30)
AS
DECLARE @PAGE VARCHAR(50)
DECLARE @timediff int
DECLARE @pswd char(32) 
DECLARE @isfirstlogin char(1) --取得是否已首次登入
DECLARE @MSG varchar(200)
SET @PAGE='Index.aspx-' + @MachineName
SELECT @pswd=password,@isfirstlogin=IsFirstLogined,@errcnt=Errcnt, @timeDiff=isnull(DATEDIFF(second, LastErrorDate,getdate()),0) FROM StudentWish where SerialNo=@SerialNo AND IDNO=@IDNO

/*無此帳號*/
IF @@ROWCOUNT= 0
BEGIN
       SET @RemainderTime= -1
       SET @Errcnt = -1
       SET @MSG='無此帳號:' + @SerialNo + '/' + @IDNO
       EXEC InsertLogTab  @SerialNo,@PAGE,@LOGIP,@LogIPXFORWARD,@LogIPREMOTEADD,'無此帳號'
       RETURN
END

/*判斷該帳號是目前是否達鎖定次數／是否已過鎖定時間 */
IF @Errcnt>=@RetryTimes  AND @timediff <@LockTime    --未過鎖定時間，回傳尚剩等待幾秒
BEGIN
      SET @RemainderTime=@LockTime - @timediff
      SET @MSG='鎖定狀態中，剩 ' + Convert(varchar(3),@RemainderTime)  + ' 秒'                                     
      EXEC InsertLogTab  @SerialNo,@PAGE,@LOGIP,@LogIPXFORWARD,@LogIPREMOTEADD,@MSG 
      RETURN
 END

/* 已過鎖定時間，需解除，並設等待秒數 為 0 */
SET @RemainderTime=0
--IF @Errcnt >=@RetryTimes AND @timediff >@LockTime    --若錯了3 次，隔一天再試，也應RESET 為 0
IF  @timediff >@LockTime  --若錯了3 次，隔了十分鐘再試，也應RESET 為 0
     SET @Errcnt=0
 
/* 底下驗證密碼 */
IF @pswd<>@passwd 
    BEGIN
         UPDATE StudentWish SET ErrCnt=@ErrCnt+1 ,LastErrorDate=getDate()  WHERE SerialNo=@SerialNo AND IDNO=@IDNO
         SET @MSG='帳密有誤，錯誤次數 ' + Convert(varchar(2),@ErrCnt+1)
         EXEC InsertLogTab  @SerialNo,@PAGE,@LOGIP,@LogIPXFORWARD,@LogIPREMOTEADD,@MSG
         RETURN
    END

/*密碼正確*/

IF @isfirstlogin <>'Y'   --首次登入
     UPDATE [StudentWish] SET ErrCnt=0,FirstLoginedDate=getDate(),IsFirstLogined='Y'  WHERE SerialNo=@SerialNo
IF @Errcnt <>0  --有錯誤再更新
     UPDATE StudentWish SET ErrCnt=0  WHERE  SerialNo=@SerialNo AND IDNO=@IDNO

SET @MSG='登入成功'
EXEC InsertLogTab  @SerialNo,@PAGE,@LOGIP,@LogIPXFORWARD,@LogIPREMOTEADD,@MSG
SELECT  StudName,Sex,TypeNo,IsUP3,IsConsent,IsJoinReview,IsJoinExam,IsConfirm from [StudentWish] WHERE IDNO=@IDNO  and SerialNo=@SerialNo and Password=@passwd

GO

資料分頁SQL指令--OFFSET...FETCH

//SQL 2012
select * from titles order by  title_id
select * from titles order by  title_id offset 5 rows fetch  next 15  rows only

常用SQL (Object Data Source)

Web.config設定

---------------------------------------------

 <connectionStrings>
        <add name="pubsConnectionString" connectionString="Data Source=JAMES;Initial Catalog=pubs;USER ID=AAA;PASSWORD=12345678" providerName="System.Data.SqlClient" />
        
 </connectionStrings>

------------------------------------------------
採用 App_data方式
-------------------
<add key="default_cn" value="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True"/>

'VB.NET讀取app.config
Dim cn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("default_cn"))

'ASP.NET讀取web.config
Imports System.Web.Configuration

 Dim cn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.AppSettings("default_cn"))

Public Class membersDB

    '----------------------------------------------

    'Select ---

    Public Shared Function Get_members() As DataTable

        Dim cn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.AppSettings("default_cn"))


       Try
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If

            Dim sqlString As String = ""

            sqlString += " SELECT * from members "

            Dim cmd As New SqlCommand(sqlString, cn)
            Dim adpt As New SqlDataAdapter
            Dim dt As New DataTable
            adpt.SelectCommand = cmd
            adpt.Fill(dt)
            Return dt

        Catch ex As Exception
           Throw ex
        Finally
            cn.Close()
        End Try
    End Function

    '----------------------------------------------
    'Insert ---

    Public Shared Function insert_members(ByVal name As String, ByVal tel As String, ByVal memo As String) As Integer

        ' Dim cn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("equip_cn"))

        Dim cn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.AppSettings("default_cn"))

        Try

            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If

            Dim sqlstring As String = "insert into [members] ( name, tel, memo  ) values ( @name, @devtype, @memo )"

            Dim cmd As New SqlCommand(sqlstring, cn)

          cmd.Parameters.Add("@name", SqlDbType.Char, 4)
            cmd.Parameters("@name").Value = name
           cmd.Parameters.Add("@tel", SqlDbType.Char, 2)
            cmd.Parameters("@tel").Value = tel
           cmd.Parameters.Add("@memo", SqlDbType.Text, 2147483647)
            cmd.Parameters("@memo").Value = memo


            Dim i As Integer
            i = cmd.ExecuteNonQuery()
            Return i

        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function




    Public Shared Function Update_members(ByVal Name As String, ByVal Tel As String, ByVal ID As Integer) As Integer

        Dim cn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.AppSettings("default_cn"))

        Try

            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If

            Dim sqlString As String = String.Format("Update [members] set NAME=@NAME,TEL=@TEL  where id=@id")

            Dim cmd As New SqlCommand(sqlString, cn)


            cmd.Parameters.Add("@id", SqlDbType.Int)
            cmd.Parameters("@id").Value = ID

            cmd.Parameters.Add("@TEL", SqlDbType.VarChar, 20)
            cmd.Parameters("@TEL").Value = Tel

            cmd.Parameters.Add("@NAME", SqlDbType.NVarChar, 50)
            cmd.Parameters("@NAME").Value = Name

            Dim i As Integer
            i = cmd.ExecuteNonQuery()
            Return i
        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function


    'Delete ---
    Public Shared Function Delete_alldev(ByVal ID As Integer) As Integer
        Dim cn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.AppSettings("default_cn"))

        Try

            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If

            Dim sqlString As String = "Delete from [members] where ID=@ID"
            Dim cmd As New SqlCommand(sqlString, cn)

            cmd.Parameters.Add("@ID", SqlDbType.Int)
            cmd.Parameters("@ID").Value = ID

            Dim i As Integer
            i = cmd.ExecuteNonQuery()
            Return i

        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function

End Class