はじめに
前回の続きです。
地名のComboBox作成
ComboBox作成のために、まずはそこに入れるための地名テーブルを作ります。
これが出来たら次は、ストアドを作っていきます。
まずはストアドを、こんな感じに作ってみました。
USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- プロシージャ定義部
ALTER PROCEDURE [dbo].[国_抽出]
AS
-- 処理部
SELECT *
FROM [dbo]. [ComboBox]
WHERE Combo='国'
ORDER BY ID
USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- プロシージャ定義部
ALTER PROCEDURE [dbo].[地名_抽出]
@国 nvarchar(50)
AS
-- 処理部
SELECT *
FROM [dbo]. [ComboBox]
WHERE Combo='地名' AND 所属=@国
ORDER BY ID
国、地名のテーブルを１まとめにしているので、今回はWhere句で使うConboBoxを指定しました。
これが終わったら、ComboBoxを作っていきます。
ここまで含めて、ほぼ実践編でやったことと同じです。
まずは、Form1のロード時に国のComboBoxが読み込まれるようにして
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
LoadCountry()
End Sub
Private Sub LoadCountry()
' SQL Server 接続文字列
Env.Load()
Dim server As String = Env.GetString("SERVER")
Dim database As String = Env.GetString("DATABASE")
Dim connectionString As String = "Server=" + server + ";Database=" + database + ";Trusted_Connection=True;TrustServerCertificate=True;"
Try
Using conn As New SqlConnection(connectionString)
Using cmd As New SqlCommand("国_抽出", conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
Using reader As SqlDataReader = cmd.ExecuteReader()
Dim prefList As New List(Of String)
While reader.Read()
prefList.Add(reader("名前").ToString())
realcountry = reader("別名").ToString()
End While
' ComboBox にセット
ComboBox_国.DataSource = prefList
End Using
End Using
End Using
Catch ex As Exception
MessageBox.Show("エラー: " & ex.Message)
End Try
End Sub
次に、国が変わった時に地名のコンボボックスが決まる仕様を作ります。
Private Sub ComboBox_国_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox_国.SelectedIndexChanged
ComboBox_地名.Enabled = True
ComboBox_地名.Items.Clear()
Dim selectedPref As String = ComboBox_国.SelectedItem.ToString()
' SQL Server 接続文字列
Dim server As String = Env.GetString("SERVER")
Dim database As String = Env.GetString("DATABASE")
Dim connectionString As String = "Server=" + server + ";Database=" + database + ";Trusted_Connection=True;TrustServerCertificate=True;"
Try
Using conn As New SqlConnection(connectionString)
' ストアドプロシージャ名は市町村抽出用に用意しているもの
Using cmd As New SqlCommand("地名_抽出", conn)
cmd.CommandType = CommandType.StoredProcedure
' IDをパラメータとして渡す
cmd.Parameters.AddWithValue("@国", selectedPref)
conn.Open()
Using reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
ComboBox_地名.Items.Add(reader("名前").ToString())
reallocation = reader("別名").ToString()
End While
End Using
End Using
End Using
Catch ex As Exception
MessageBox.Show("エラー: " & ex.Message)
End Try
ComboBox_地名.SelectedIndex = 0
End Sub
どうせなので、サーバ名、データベース名も環境変数にしてみました。
やり方は前編でやった方法と同じです。
また、今回はComboBoxに記載された値ではなく、先程のテーブルにあった「別名」を、API側に送る値として使っていきたいと思います。
日本語名ではなく、アルファベットでAPIに情報を送る必要があるためです。
なので、名前抽出用のストアドを作ります。
USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- プロシージャ定義部
CREATE PROCEDURE [dbo].[別名_抽出]
@名前 nvarchar(50)
AS
-- 処理部
SELECT 別名
FROM [dbo]. [ComboBox]
WHERE 名前=@名前
ORDER BY ID
これで、名前を渡すと、別名が返ってきます。
最後に、名前を渡したら、別名に書き換わります。
Dim realcountry As String = ""
Dim realcity As String = ""
Dim connectionString As String = "Server=" + server + ";Database=" + database + ";Trusted_Connection=True;TrustServerCertificate=True;"
Try
Using conn As New SqlConnection(connectionString)
Using cmd As New SqlCommand("別名_抽出", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@名前", ComboBox_国.Text)
conn.Open()
Using reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
realcountry = reader("別名").ToString()
End While
End Using
End Using
End Using
Catch ex As Exception
MessageBox.Show("エラー: " & ex.Message)
End Try
これは国用ですが、地名用に同じようなやつをもう一個作りましょう。
realcountry,realcityは、それぞれ実際に送る用の国、地名の名称です。
Dim url As String = $"https://api.openweathermap.org/data/2.5/weather?q={Uri.EscapeDataString(realcity)},{Uri.EscapeDataString(realcountry)}&units=metric&appid={apiKey}"
MessageBox.Show("国：" + realcountry + vbCrLf + "地名：" + realcity+ vbCrLf + url)
これで、URLには英語名が送られます。
実際の挙動を見ていきます。
アメリカ、アラスカを選択すると、こんな感じに別名での表記でURLが書かれて
気温と天気が出ました！
こんな夏場に10℃台なので、アラスカで間違いないでしょう。
履歴テーブルの作成
履歴として最低限必要な情報は、地名、気温、天気、検索日時なので、その４列でテーブルを作りましょう。
一方、Form側はこんな感じで作りました。
SQLのテーブルは４列に対して、Form側のテーブルでは６列で作られているのは違和感がありますが、実はこれで正しいです。
ストアド経由でテーブルを作成する際、別のテーブル（ComboBox）から列を流用しているためです。
詳しく見ていきます。
USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- プロシージャ定義部
ALTER PROCEDURE [dbo].[天気履歴_抽出]
AS
-- 処理部
BEGIN
SET NOCOUNT ON;
SELECT
TH.[名前] AS 名前,
CB.[所属] AS 国,
TH.[気温] AS 気温,
TH.[天気] AS 天気,
TH.[検索日時] AS 検索日時,
CB.[ID] AS ID
FROM [dbo].[天気履歴] TH
INNER JOIN [dbo].[ComboBox] CB
ON TH.[名前] = CB.[名前]
ORDER BY TH.[検索日時] desc
END
実行結果
名前、気温、天気、検索日時は天気履歴から、所属（国）、IDはComboBoxから列を射影していることがわかると思います。
これらを天気履歴を主にINNER JOINすることで、従となるComboBoxの情報が天気履歴側のテーブルの、情報と一致した列が付加されます。
ちなみに、THとかCBは、テーブルの略称みたいなものです。
ASは、ストアドでテーブルを作って渡す際に、その新設された列の名前を示します。
そんな感じで作ったテーブルをDataGridViewに入れます。
Private Sub データ表示()
Dim server As String = Env.GetString("SERVER")
Dim database As String = Env.GetString("DATABASE")
Dim connectionString As String = "Server=" + server + ";Database=" + database + ";Trusted_Connection=True;TrustServerCertificate=True;"
Try
Using conn As New SqlConnection(connectionString)
Using cmd As New SqlCommand("天気履歴_抽出", conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
Using reader As SqlDataReader = cmd.ExecuteReader()
' 既存の行をクリア
DataGridView_天気履歴.Rows.Clear()
' データを1行ずつ追加
While reader.Read()
Dim rowIndex As Integer = DataGridView_天気履歴.Rows.Add()
' reader の列順に従って DataGridView の列に代入
For i As Integer = 0 To reader.FieldCount - 1
' DataGridView の列数に収まるようにチェック
If i < DataGridView_天気履歴.Columns.Count - 1 Then
DataGridView_天気履歴.Rows(rowIndex).Cells(i).Value = If(reader(i) Is DBNull.Value, "", reader(i))
Else
DataGridView_天気履歴.Rows(rowIndex).Cells(i).Value = "削除"
End If
Next
End While
End Using
End Using
End Using
Catch ex As Exception
MessageBox.Show("エラー: " & ex.Message)
End Try
End Sub
基本はこの前と同じなんですが、DataGridViewの最終列を削除ボタンにしたり（いったん削除の列のColumnTypeを「DataGridViewButtonColumn」にする、最終列の値を「削除」にする）してます。
検索履歴を逐次呼び出しているだけなので、とくにこれ以上の操作は不要です。
履歴追加に制限をかける
次に、不要な検索を行うことによるAPIのリクエスト制限を防ぐために、履歴追加に制限を加えていきます。
まずは、簡単にVB.NET側から作っていきます
Try
Using conn As New SqlConnection(connectionString)
Using cmd As New SqlCommand("既存検索_抽出", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@名前", ComboBox_地名.Text)
' 戻り値パラメータ
Dim returnParam As SqlParameter = cmd.Parameters.Add("ReturnValue", SqlDbType.Int)
returnParam.Direction = ParameterDirection.ReturnValue
conn.Open()
cmd.ExecuteNonQuery()
result = Convert.ToInt32(returnParam.Value)
If result = -1 Then
MessageBox.Show("1時間以内に同地域で検索した履歴があります。" + vbCrLf + "履歴を削除するか、同地域の最終検索から1時間以上開けてから再度検索してください。")
Else
End If
End Using
End Using
Catch ex As Exception
MessageBox.Show("エラー: " & ex.Message)
End Try
If result = 1 Then
(ここにその後の登録動作を書き込む)
Else
End If
要するに、ComboBoxの名前でストアドで検索を行い、result=1ならその後の動作（APIを叩き、データ追加）が許可され、result=-1ならMessageを出してスルーすると書いています。
そのストアドの動作がこちら
USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- プロシージャ定義部
ALTER PROCEDURE [dbo].[既存検索_抽出]
@名前 nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @検索日時 datetime;
DECLARE @差分分 int;
-- 最新の検索日時を取得
SELECT TOP 1
@検索日時 = 検索日時
FROM [dbo].[天気履歴]
WHERE 名前 = @名前
ORDER BY 検索日時 DESC;
-- データがなければ Return=1
IF @検索日時 IS NULL
BEGIN
RETURN 1;
END
SELECT *
FROM [dbo].[天気履歴]
WHERE 名前 = N'東京'
AND 検索日時 >= DATEADD(HOUR, -1, GETDATE());
-- 現在との差（分単位）
SET @差分分 = DATEDIFF(MINUTE, @検索日時, GETDATE());
-- 1時間以内なら -1、それ以外は 1
IF @差分分 < 60
RETURN -1;
ELSE
RETURN 1;
END
今回は、最終登録から１時間以内に同じ地名で再登録しようとした際に、RETURN=-1(失敗)、そうでなければ次に進めるように組んでみました。
DECLARE @検索日時 datetime;
DECLARE @差分分 int;
DECLAREで、ストアド内の変数を定義します。
あとはGPTに書かせたので注釈がいろいろ書いてありますので、だいたい理解できるかと思います。
するとやっぱり、登録時にMessageBoxが出てきて、APIへの接続およびテーブルへの追加がスルーされます。
これは実際、連続アクセスを防止するうえでよく使う技術となります。
APIは完全無料じゃないものが多いので、これがあるとかなり安心。
削除機能の実装
最後に、履歴の削除機能を実装していきます。
1時間のアクセス制限を無視したい…という意図もありますが、Form側でデータを削除するという技術は、SQLを使う上で絶対要求される所作です。
Private Sub DataGridView_天気履歴_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView_天気履歴.CellContentClick
' クリックされた列が最終列かチェック
Dim dgv As DataGridView = CType(sender, DataGridView)
If e.ColumnIndex = dgv.Columns.Count - 1 AndAlso e.RowIndex >= 0 Then
' クリックされた行から値を取得
Dim 名前 As String = dgv.Rows(e.RowIndex).Cells(0).Value.ToString() ' 2列目
Dim 検索日時 As DateTime = dgv.Rows(e.RowIndex).Cells(4).Value.ToString() ' 6列目
' ストアド呼び出し
Dim server As String = Env.GetString("SERVER")
Dim database As String = Env.GetString("DATABASE")
Dim connectionString As String = "Server=" + server + ";Database=" + database + ";Trusted_Connection=True;TrustServerCertificate=True;"
Try
Using conn As New SqlConnection(connectionString)
Using cmd As New SqlCommand("天気履歴_削除", conn)
cmd.CommandType = CommandType.StoredProcedure
' パラメータ
cmd.Parameters.AddWithValue("@名前", 名前)
cmd.Parameters.AddWithValue("@検索日時", 検索日時)
conn.Open()
Dim rowsDeleted As Integer = cmd.ExecuteNonQuery()
DataGridView_天気履歴.Rows.RemoveAt(DataGridView_天気履歴.CurrentCell.RowIndex)
データ表示()
MessageBox.Show("以下の情報は削除されました。" & vbCrLf & 名前 & "：" & 検索日時)
End Using
End Using
Catch ex As Exception
MessageBox.Show("エラー: " & ex.Message)
End Try
End If
End Sub
今回は、DataGridViewの最終行を削除ボタンにしていますので、当然最終行をクリックされた時のみ削除が動作するようにします。
また、同行の1行目の「名前」、5行目の「検索日時」をストアドへ送るので、その値も取得します。
そしたらあとは削除用のストアドを起動し、DataGridView側でも削除を押した行自体を消してやるだけです。
ストアド側も見ていきましょう。
USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- プロシージャ定義部
ALTER PROCEDURE [dbo].[天気履歴_削除]
@名前 nvarchar(50),
@検索日時 datetime
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM [dbo].[天気履歴]
WHERE 名前 = @名前
AND 検索日時 BETWEEN DATEADD(SECOND, -5, @検索日時)
AND DATEADD(SECOND, 5, @検索日時);
END
名前（nvarchar）と検索日時(datetime)をセットしたら
WHEREで名前、検索日時が一致する行を探し出し、DELETEで削除してやります。
ここでなんですが、検索日時はBETWEENを使って前後5秒ほど時間の余裕をもたせてあります。
SQLのテーブルの秒数と、Formに表示させているテーブルの秒数が、型の扱いの違いから完全一致しない可能性があるためです。
ということで削除。
念の為メッセージボックスに、削除した地域の名前と、検索した日時も表示しておきました。
まとめ
今回は一例として
を達成してみました。
実践編以上に、いろいろなことを学べたかなと思います。
まだまだできることはあると思うので、色々課題を見つけて自分でやってみましょう。
Discussion