コミット: 382 - CTraining (svn)

練習用です。いろんなものがごちゃまぜです。

コミットメタ情報

• リビジョン: 382 (tree)
• 日時: 2017-10-30 17:35:09
• 作者: bellyoshi

ログメッセージ

変更サマリ

• added: vbdb/accdb1/accdb1/DBUtil.vb
• added: vbdb/accdb1/accdb1/SQLBuilder.vb
• modified: vbdb/accdb1/accdb1/accdb1.vbproj
• modified: vbdb/accdb1/accdb1/frmProjectList.vb

差分

--- vbdb/accdb1/accdb1/DBUtil.vb	(nonexistent)
+++ vbdb/accdb1/accdb1/DBUtil.vb	(revision 382)
@@ -0,0 +1,22 @@
+Public Class DBUtil
+    ''' <summary>
+    ''' 条件がすでにあれば文字列ANDを追加する。
+    ''' </summary>
+    ''' <param name="fs">SQL条件 WHERE句</param>
+    Public Shared Sub AppendAnd(fs As System.Text.StringBuilder)
+        If fs.Length <> 0 Then
+            fs.Append(" AND ")
+        End If
+    End Sub
+
+    Public Shared Function CreateSql(tableName As String, fs As Text.StringBuilder) As String
+        Dim sqlBuilder As New Text.StringBuilder
+        sqlBuilder.Append("SELECT * FROM ")
+        sqlBuilder.Append(tableName)
+        If fs.Length <> 0 Then
+            sqlBuilder.Append(" WHERE ")
+            sqlBuilder.Append(fs)
+        End If
+        Return sqlBuilder.ToString
+    End Function
+End Class

--- vbdb/accdb1/accdb1/SQLBuilder.vb	(nonexistent)
+++ vbdb/accdb1/accdb1/SQLBuilder.vb	(revision 382)
@@ -0,0 +1,25 @@
+Imports System.Text
+
+Public Class SQLBuilder
+
+    Private fs As New Text.StringBuilder
+    Private tableName As String
+    Private isfirstCondtion As Boolean = True
+
+    Public Sub New(ByVal tableName As String)
+        Me.tableName = tableName
+    End Sub
+
+    Public Sub Append(ByVal condition As String)
+        If Not isfirstCondtion Then
+            DBUtil.AppendAnd(fs)
+        End If
+        fs.Append(condition)
+        isfirstCondtion = False
+    End Sub
+
+    Public Function CreateSql()
+        Return DBUtil.CreateSql("vw_projectlist_test", fs)
+    End Function
+
+End Class

--- vbdb/accdb1/accdb1/frmProjectList.vb	(revision 381)
+++ vbdb/accdb1/accdb1/frmProjectList.vb	(revision 382)
@@ -25,27 +25,24 @@
     End Sub
 
     Private Sub LoadDatabese()
-        Dim fs As New Text.StringBuilder
+        Dim sb As New SQLBuilder("vw_projectlist_test")
 
         If Trim(txtProjectName.Text) = String.Empty Then
-            fs.Append($"project_name like '%{txtProjectName.Text}%'")
+            sb.Append($"project_name like '%{txtProjectName.Text}%'")
         End If
         If 0 < cmbManager.SelectedIndex Then
-            AppendAnd(fs)
             Dim staffcode = Strings.Left(cmbManager.Text, 6)
-            fs.Append($"staff_code = '{staffcode}'")
+            sb.Append($"staff_code = '{staffcode}'")
         End If
         If 0 < cmbCustomer.SelectedIndex Then
-            AppendAnd(fs)
             Dim customercode = Strings.Left(cmbCustomer.Text, 7)
-            fs.Append($"customer_code = '{customercode}'")
+            sb.Append($"customer_code = '{customercode}'")
         End If
         If chkComplete.Checked Then
-            AppendAnd(fs)
-            fs.Append("complete_flag = true")
+            sb.Append("complete_flag = true")
         End If
 
-        Dim sql = CreateSql("vw_projectlist_test", fs)
+        Dim sql = sb.CreateSql()
 
         Dim da = New Data.OleDb.OleDbDataAdapter(sql, My.Settings.projectManageConnectionString)
         Me.ProjectManageDataSet.vw_projectlist_test.Clear()
@@ -52,26 +49,7 @@
         da.Fill(Me.ProjectManageDataSet.vw_projectlist_test)
     End Sub
 
-    ''' <summary>
-    ''' 条件がすでにあれば文字列ANDを追加する。
-    ''' </summary>
-    ''' <param name="fs">SQL条件 WHERE句</param>
-    Private Sub AppendAnd(fs As System.Text.StringBuilder)
-        If fs.Length <> 0 Then
-            fs.Append(" AND ")
-        End If
-    End Sub
 
-    Private Function CreateSql(tableName As String, fs As Text.StringBuilder) As String
-        Dim sqlBuilder As New Text.StringBuilder
-        sqlBuilder.Append("SELECT * FROM ")
-        sqlBuilder.Append(tableName)
-        If fs.Length <> 0 Then
-            sqlBuilder.Append(" WHERE ")
-            sqlBuilder.Append(fs)
-        End If
-        Return sqlBuilder.ToString
-    End Function
 
     Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
         Me.Close()