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

# この記事の注意事項
この記事の内容は[こちらの記事](https://qiita.com/portfoliokns3/items/dac90ed7556ea242d608)の内容を修正した内容となります。

# 修正後のコード
```VB
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は以下の通りでした。
![AddWithValue.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/54e2d5b2-c5f7-941a-bf17-3abf8d274190.png)
認証の判断の処理は、以下のようにしていました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/9389d340-369e-edd2-c059-ee591143a824.png)

**修正後**
SQLを以下のように変更しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/66e267e1-592d-4566-301c-05199d48e7cf.png)
SqlDataReaderで、返り値を直接読み取るようにしました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/c9bd5a02-9e19-3850-5412-3bbf867557cd.png)
ソースコードから以下の条件分岐を削除しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/554d5fcd-0b75-ae92-cdf7-98ba57a9c6e8.png)

# SQLについて
## 実行される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となります。
```SQL
SELECT 1   FROM USERINFO   WHERE user_id = @userID AND password = @password   HAVING COUNT(*) = 1
```

## SQLの説明 ②
"1"が存在すれば、Trueとして返し、NULLであれば、Falseとして、返り値を返すというSQLになっています。
```SQL
SELECT CASE WHEN EXISTS ( 1 または NULLが返ってくる ) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS isAuthenticated
```

# 実行結果
問題なく、認証が成功しています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/ff2a49ba-7db0-f6c0-44b3-96e342665212.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/2e321159-27a6-c8c1-4246-438d681459ef.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/ec10383a-c3a3-f2a1-4dd7-0f3ab3528bcc.png)

# ユーザー情報
挙動を確認するために、ユーザー情報を以下のように準備しています。
**※本来、パスワードをハッシュ化して管理したり、パスワードのテキストフォームは見えないように配慮しなければなりませんが、この時点では実装していません。**
![データベース.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/9dc4daa5-627a-a2c8-5c91-fc7368b10761.png)

# 注意事項
- この記事では、SQL Server、SSMS、visual studioを使用しているので、試してみたい方は事前に準備が必要です。
- 本記事に掲載されているコードは掲載用にサンプルとして掲載しているものです。したがって本記事を参考にし、コーディングを進める場合は作成しているアプリに応じて、各自で判断して、適応してください。
- 関連する記事の一覧は[こちら](https://qiita.com/portfoliokns3/items/7c789b7e1ba40500abbb#%E3%81%93%E3%82%8C%E3%81%8B%E3%82%89%E6%89%B1%E3%81%A3%E3%81%A6%E3%81%84%E3%81%8D%E3%81%9F%E3%81%84%E4%BA%BA%E5%90%91%E3%81%91%E3%81%AE%E8%A8%98%E4%BA%8B)。
※参照先のリンクが切れている場合は、[トップページ](https://qiita.com/portfoliokns3)から確認してください。

# この記事は誰向けの記事か？
- SQLについて詳しくない初心者

# 環境
- OS：Windows10
- [SQL Server](https://www.microsoft.com/ja-jp/sql-server/sql-server-downloads)
- [SQL Server Management Studio（SSMS）](https://learn.microsoft.com/ja-jp/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16):v19.0.2
- [visual studio 2022](https://visualstudio.microsoft.com/ja/vs/)
- [ChatGPT](https://chat.openai.com/chat)

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