SQLを修正し、TrueやFalseが返ってくるようにする（VB.NET）

この記事の注意事項

この記事の内容はこちらの記事の内容を修正した内容となります。

修正後のコード

Imports System.Data.SqlClient

Public Class clsSqlServerRespoder

    Public Function getAutenticate(ByRef systemErrorFlag As Boolean, ByRef userID As String, ByRef password As String, ByRef isAuthenticated As Boolean) As Boolean
        Dim cn As New SqlClient.SqlConnection

        Try

            Dim devDataSource As String = System.Environment.GetEnvironmentVariable("DEV_DATA_SOURCE")
            Dim devInitialCatalog As String = System.Environment.GetEnvironmentVariable("DEV_INITIAL_CATALOG")
            Dim devUserID As String = System.Environment.GetEnvironmentVariable("DEV_USER")
            Dim devPassword As String = System.Environment.GetEnvironmentVariable("DEV_PASSWORD")
            Dim devTimeout As String = System.Environment.GetEnvironmentVariable("DEV_TIMEOUT")

            Dim connectionString As String = ""
            connectionString &= String.Format("Data Source = {0};", devDataSource)
            connectionString &= String.Format("Initial Catalog = {0};", devInitialCatalog)
            connectionString &= String.Format("User ID = {0};", devUserID)
            connectionString &= String.Format("Password = {0};", devPassword)
            connectionString &= String.Format("Connect Timeout = {0};", devTimeout)

            cn.ConnectionString = connectionString
            cn.Open()

            Dim SQL As String = ""
            SQL &= String.Format("SELECT CASE WHEN EXISTS ")
            SQL &= String.Format("( ")
            SQL &= String.Format("  SELECT 1 ")
            SQL &= String.Format("  FROM USERINFO ")
            SQL &= String.Format("  WHERE user_id = @userID AND password = @password ")
            SQL &= String.Format("  HAVING COUNT(*) = 1 ")
            SQL &= String.Format(") ")
            SQL &= String.Format("THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS isAuthenticated")

            Dim cd As New SqlCommand(SQL, cn)
            cd.Parameters.AddWithValue("@userID", userID)
            cd.Parameters.AddWithValue("@password", password)

            Dim dr As SqlDataReader = cd.ExecuteReader

            While dr.Read
                isAuthenticated = dr("isAuthenticated")
            End While

        Catch ex As Exception
            systemErrorFlag = True
            MessageBox.Show("エラーが発生しました： " & ex.Message)
        Finally
            cn.Close()
            cn.Dispose()
        End Try

        Return systemErrorFlag
    End Function

End Class

修正箇所

SQLを組み換え、SQL Server側からTrueまたはFalseの返り値を得られるようにしています。
※SQLに関しては苦手意識を持っているので、この部分はChatGPTを活用させていただきました。

修正前
SQLは以下の通りでした。

認証の判断の処理は、以下のようにしていました。

修正後
SQLを以下のように変更しました。

SqlDataReaderで、返り値を直接読み取るようにしました。

ソースコードから以下の条件分岐を削除しました。

SQLについて

実行されるSQL

SELECT CASE WHEN EXISTS (   SELECT 1   FROM USERINFO   WHERE user_id = @userID AND password = @password   HAVING COUNT(*) = 1 ) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS isAuthenticated

SQLの説明 ①

USERINFOテーブルからユーザーIDとパスワードに一致するレコードが見つかれば、"1"を取得するというSQLになっています。またHAVINGの部分で、レコードが1つだけの場合に、"1"を取得するように制限しています。"1"とすることで、わざわざレコードを取得する必要がないため、効率がいい良いため、このようなコードになっています。また該当するレコードが見つからなければ、NULLとなります。

SELECT 1   FROM USERINFO   WHERE user_id = @userID AND password = @password   HAVING COUNT(*) = 1

SQLの説明 ②

"1"が存在すれば、Trueとして返し、NULLであれば、Falseとして、返り値を返すというSQLになっています。

SELECT CASE WHEN EXISTS ( 1 または NULLが返ってくる ) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS isAuthenticated

実行結果

問題なく、認証が成功しています。

ユーザー情報

挙動を確認するために、ユーザー情報を以下のように準備しています。
※本来、パスワードをハッシュ化して管理したり、パスワードのテキストフォームは見えないように配慮しなければなりませんが、この時点では実装していません。

注意事項

• この記事では、SQL Server、SSMS、visual studioを使用しているので、試してみたい方は事前に準備が必要です。
• 本記事に掲載されているコードは掲載用にサンプルとして掲載しているものです。したがって本記事を参考にし、コーディングを進める場合は作成しているアプリに応じて、各自で判断して、適応してください。
• 関連する記事の一覧はこちら。
  ※参照先のリンクが切れている場合は、トップページから確認してください。

この記事は誰向けの記事か？

• SQLについて詳しくない初心者

環境

• OS：Windows10
• SQL Server
• SQL Server Management Studio（SSMS）:v19.0.2
• visual studio 2022
• ChatGPT

本記事における注意事項

• 本記事は、備忘録としてまとめたものになります。
• 他の方の参考になる可能性も踏まえて、一般公開も行なっております。
• また記載内容はすべて、正しい内容が記載されているとは限りません。
• 誤った内容を見つけた場合は、ご指摘をお願いいたします。