607 lines
21 KiB
VB.net
607 lines
21 KiB
VB.net
|
Public Class form_Query
|
|||
|
|
Dim SectionNameList As New ArrayList
|
|||
|
|
Dim TableNameList As New ArrayList
|
|||
|
|
|
|||
|
|
Dim m_ComboDataType(COLS.max - 1) As COMBO_DATA_TYPE '存储下拉列表的类型
|
|||
|
|
Dim m_ConditionCount As Integer = 0
|
|||
|
|
Dim m_EditCol As Integer
|
|||
|
|
Dim m_EditRow As Integer
|
|||
|
|
|
|||
|
|
Dim QueryTable As New DataTable
|
|||
|
|
|
|||
|
|
Public Class SECTION_UNIT
|
|||
|
|
Public name As String
|
|||
|
|
Public check As Boolean
|
|||
|
|
End Class
|
|||
|
|
|
|||
|
|
Enum COMBO_DATA_TYPE
|
|||
|
|
table
|
|||
|
|
section
|
|||
|
|
End Enum
|
|||
|
|
|
|||
|
|
Enum COLS
|
|||
|
|
序号
|
|||
|
|
关联
|
|||
|
|
字段
|
|||
|
|
运算
|
|||
|
|
字段_值
|
|||
|
|
max
|
|||
|
|
End Enum
|
|||
|
|
|
|||
|
|
Enum SECTION_TYPE
|
|||
|
|
无效
|
|||
|
|
值
|
|||
|
|
字段
|
|||
|
|
End Enum
|
|||
|
|
|
|||
|
|
'获取W&T Database数据库中所有的表
|
|||
|
|
Private Function GetTableList() As Boolean
|
|||
|
|
Dim rTable As New Data.DataTable
|
|||
|
|
Dim result As Boolean = True
|
|||
|
|
|
|||
|
|
TableNameList.Clear()
|
|||
|
|
rTable.Columns.Clear()
|
|||
|
|
rTable.Rows.Clear()
|
|||
|
|
rTable.Clear()
|
|||
|
|
If SQL_Query(CAccountManage.COL_RIGHTS.登录, "show tables", rTable) = True Then
|
|||
|
|
If rTable.Rows.Count > 0 Then
|
|||
|
|
Try
|
|||
|
|
For i As Integer = 0 To rTable.Rows.Count - 1
|
|||
|
|
Dim pSectionUnit As New SECTION_UNIT
|
|||
|
|
pSectionUnit.name = rTable.Rows.Item(i).Item(0)
|
|||
|
|
pSectionUnit.check = False
|
|||
|
|
TableNameList.Add(pSectionUnit)
|
|||
|
|
Next
|
|||
|
|
Catch ex As Exception
|
|||
|
|
|
|||
|
|
End Try
|
|||
|
|
End If
|
|||
|
|
Else
|
|||
|
|
result = False
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
Return result
|
|||
|
|
End Function
|
|||
|
|
|
|||
|
|
'获取所有表对应的字段名称列表
|
|||
|
|
Private Function GetSectionNameList() As Boolean
|
|||
|
|
Dim rTable As New Data.DataTable
|
|||
|
|
Dim result As Boolean = True
|
|||
|
|
|
|||
|
|
SectionNameList.Clear()
|
|||
|
|
Dim TableName As String
|
|||
|
|
For Each pTableInfo As SECTION_UNIT In TableNameList
|
|||
|
|
TableName = pTableInfo.name
|
|||
|
|
|
|||
|
|
Dim pItemList As New ArrayList
|
|||
|
|
|
|||
|
|
rTable.Columns.Clear()
|
|||
|
|
rTable.Rows.Clear()
|
|||
|
|
rTable.Clear()
|
|||
|
|
If SQL_Query("SELECT * FROM `" & TableName & "` WHERE 0", rTable) = True Then
|
|||
|
|
For i As Integer = 0 To rTable.Columns.Count - 1
|
|||
|
|
Dim pSectionUnit As New SECTION_UNIT
|
|||
|
|
pSectionUnit.name = rTable.Columns.Item(i).ColumnName
|
|||
|
|
pSectionUnit.check = False
|
|||
|
|
pItemList.Add(pSectionUnit)
|
|||
|
|
Next
|
|||
|
|
|
|||
|
|
SectionNameList.Add(pItemList)
|
|||
|
|
Else
|
|||
|
|
result = False
|
|||
|
|
End If
|
|||
|
|
Next
|
|||
|
|
|
|||
|
|
Return result
|
|||
|
|
End Function
|
|||
|
|
|
|||
|
|
Private Sub form_Query_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
|
|||
|
|
If GetTableList() = False Then
|
|||
|
|
MsgBox("载入数据表错误")
|
|||
|
|
Me.Close()
|
|||
|
|
Return
|
|||
|
|
End If
|
|||
|
|
If GetSectionNameList() = False Then
|
|||
|
|
MsgBox("载入数据表字段错误")
|
|||
|
|
Me.Close()
|
|||
|
|
Return
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
'填充查询表
|
|||
|
|
Fill_TableList()
|
|||
|
|
|
|||
|
|
'初始化Conditions列表
|
|||
|
|
Init_ConditionTable()
|
|||
|
|
End Sub
|
|||
|
|
|
|||
|
|
Private Sub Init_ConditionTable()
|
|||
|
|
Grid_Conditions.Cols = COLS.max
|
|||
|
|
Grid_Conditions.Rows = 2
|
|||
|
|
|
|||
|
|
'填充列名
|
|||
|
|
Grid_Conditions.Cell(0, 0).Text = "序号"
|
|||
|
|
Grid_Conditions.Cell(0, 1).Text = "关联"
|
|||
|
|
Grid_Conditions.Cell(0, 2).Text = "字段"
|
|||
|
|
Grid_Conditions.Cell(0, 3).Text = "运算"
|
|||
|
|
Grid_Conditions.Cell(0, 4).Text = "字段/值"
|
|||
|
|
|
|||
|
|
Grid_Conditions.Cell(1, 0).Text = m_ConditionCount + 1
|
|||
|
|
Grid_Conditions.Cell(COLS.关联, 1).Locked = True
|
|||
|
|
|
|||
|
|
'填入“关联”字段列表值
|
|||
|
|
Grid_Conditions.Column(COLS.关联).CellType = FlexCell.CellTypeEnum.ComboBox
|
|||
|
|
Dim i As Integer
|
|||
|
|
With Grid_Conditions.ComboBox(COLS.关联)
|
|||
|
|
.Locked = True
|
|||
|
|
.AutoComplete = True
|
|||
|
|
For i = 0 To mdl_SQLConst.rel_Conditions.Length / 2 - 1
|
|||
|
|
.Items.Add(mdl_SQLConst.rel_Conditions(i, 0))
|
|||
|
|
Next
|
|||
|
|
End With
|
|||
|
|
|
|||
|
|
'填入“运算”字段列表值
|
|||
|
|
Grid_Conditions.Column(COLS.运算).CellType = FlexCell.CellTypeEnum.ComboBox
|
|||
|
|
With Grid_Conditions.ComboBox(COLS.运算)
|
|||
|
|
.Locked = True
|
|||
|
|
.AutoComplete = True
|
|||
|
|
For i = 0 To mdl_SQLConst.rel_Operator.Length / 2 - 1
|
|||
|
|
.Items.Add(mdl_SQLConst.rel_Operator(i, 0))
|
|||
|
|
Next
|
|||
|
|
End With
|
|||
|
|
|
|||
|
|
'填入“字段”
|
|||
|
|
Grid_Conditions.Column(COLS.字段).Width = 80
|
|||
|
|
Grid_Conditions.Column(COLS.字段).CellType = FlexCell.CellTypeEnum.ComboBox
|
|||
|
|
Fill_ComboList(Grid_Conditions.ComboBox(COLS.字段), "", 0, COMBO_DATA_TYPE.table)
|
|||
|
|
|
|||
|
|
'填入"字段/值"
|
|||
|
|
Grid_Conditions.Column(COLS.字段_值).Width = 80
|
|||
|
|
Grid_Conditions.Column(COLS.字段_值).CellType = FlexCell.CellTypeEnum.ComboBox
|
|||
|
|
Fill_ComboList(Grid_Conditions.ComboBox(COLS.字段_值), "", 0, COMBO_DATA_TYPE.table)
|
|||
|
|
End Sub
|
|||
|
|
|
|||
|
|
Private Sub Fill_TableList()
|
|||
|
|
clb_Table.Items.Clear()
|
|||
|
|
|
|||
|
|
For Each pItem As SECTION_UNIT In TableNameList
|
|||
|
|
clb_Table.Items.Add(pItem.name)
|
|||
|
|
Next
|
|||
|
|
End Sub
|
|||
|
|
|
|||
|
|
Private Function Fill_SectionList(ByVal index As Integer) As Boolean
|
|||
|
|
If index > SectionNameList.Count Then Return False
|
|||
|
|
|
|||
|
|
Dim pItem As ArrayList = SectionNameList.Item(index)
|
|||
|
|
clb_Section.Items.Clear()
|
|||
|
|
|
|||
|
|
Dim sec_idx As Integer
|
|||
|
|
For Each pSec As SECTION_UNIT In pItem
|
|||
|
|
sec_idx = clb_Section.Items.Add(pSec.name)
|
|||
|
|
clb_Section.SetItemChecked(sec_idx, pSec.check)
|
|||
|
|
Next
|
|||
|
|
|
|||
|
|
Return True
|
|||
|
|
End Function
|
|||
|
|
|
|||
|
|
Private Sub chklb_Table_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles clb_Table.SelectedIndexChanged
|
|||
|
|
If clb_Table.SelectedIndex >= 0 Then
|
|||
|
|
Fill_SectionList(clb_Table.SelectedIndex)
|
|||
|
|
End If
|
|||
|
|
End Sub
|
|||
|
|
|
|||
|
|
|
|||
|
|
Private Function GetAnySectionChecked(ByVal currIndex As Integer) As Boolean
|
|||
|
|
Dim checked As Boolean = False
|
|||
|
|
For i As Integer = 0 To clb_Section.Items.Count - 1
|
|||
|
|
If i <> currIndex Then
|
|||
|
|
If clb_Section.GetItemChecked(i) = True Then
|
|||
|
|
checked = True
|
|||
|
|
Exit For
|
|||
|
|
End If
|
|||
|
|
End If
|
|||
|
|
Next
|
|||
|
|
|
|||
|
|
Return checked
|
|||
|
|
End Function
|
|||
|
|
|
|||
|
|
Private Sub clb_Table_ItemCheck(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ItemCheckEventArgs) Handles clb_Table.ItemCheck
|
|||
|
|
Dim pTable As SECTION_UNIT = TableNameList.Item(e.Index)
|
|||
|
|
pTable.check = e.NewValue
|
|||
|
|
SQLGenerateFromCondition()
|
|||
|
|
End Sub
|
|||
|
|
|
|||
|
|
Private Sub clb_Section_ItemCheck(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ItemCheckEventArgs) Handles clb_Section.ItemCheck
|
|||
|
|
If clb_Table.SelectedIndex >= 0 AndAlso e.Index >= 0 Then
|
|||
|
|
Dim pTable As ArrayList = SectionNameList(clb_Table.SelectedIndex)
|
|||
|
|
Dim pSec As SECTION_UNIT = pTable(e.Index)
|
|||
|
|
pSec.check = e.NewValue
|
|||
|
|
|
|||
|
|
If e.NewValue OrElse GetAnySectionChecked(e.Index) = True Then
|
|||
|
|
clb_Table.SetItemChecked(clb_Table.SelectedIndex, True)
|
|||
|
|
Else
|
|||
|
|
clb_Table.SetItemChecked(clb_Table.SelectedIndex, False)
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
SQLGenerateFromCondition()
|
|||
|
|
End If
|
|||
|
|
End Sub
|
|||
|
|
|
|||
|
|
Private Sub Grid_Conditions_LeaveCell(ByVal Sender As System.Object, ByVal e As FlexCell.Grid.LeaveCellEventArgs) Handles Grid_Conditions.LeaveCell
|
|||
|
|
SQLGenerateFromCondition()
|
|||
|
|
End Sub
|
|||
|
|
|
|||
|
|
'填充dgview_Condition中的“字段”下拉框列表
|
|||
|
|
Private Function Fill_ComboList(ByRef pCombo As FlexCell.ComboBox, ByVal PreTableName As String, ByVal index As Integer, ByVal type As COMBO_DATA_TYPE) As Boolean
|
|||
|
|
Dim pSection As SECTION_UNIT
|
|||
|
|
|
|||
|
|
pCombo.Items.Clear()
|
|||
|
|
pCombo.DropDownWidth = 200
|
|||
|
|
If type = COMBO_DATA_TYPE.table Then
|
|||
|
|
For i As Integer = 0 To TableNameList.Count - 1
|
|||
|
|
pSection = TableNameList(i)
|
|||
|
|
If pSection.check = True Then
|
|||
|
|
pCombo.Items.Add(pSection.name)
|
|||
|
|
End If
|
|||
|
|
Next
|
|||
|
|
Else
|
|||
|
|
If index > SectionNameList.Count Then Return False
|
|||
|
|
|
|||
|
|
Dim pTable As ArrayList = SectionNameList.Item(index)
|
|||
|
|
|
|||
|
|
For Each pSec As SECTION_UNIT In pTable
|
|||
|
|
pCombo.Items.Add(PreTableName & "." & pSec.name)
|
|||
|
|
Next
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
Return True
|
|||
|
|
End Function
|
|||
|
|
|
|||
|
|
Private Sub UpdateComboBoxList(ByVal row As Integer, ByVal col As Integer)
|
|||
|
|
'Dim col As Integer = e.Col
|
|||
|
|
'Dim row As Integer = e.Row
|
|||
|
|
|
|||
|
|
If col < 1 OrElse row < 1 Then Return
|
|||
|
|
|
|||
|
|
If col = COLS.字段 OrElse col = COLS.字段_值 Then
|
|||
|
|
Try
|
|||
|
|
Dim pCombo As FlexCell.ComboBox = Grid_Conditions.ComboBox(col)
|
|||
|
|
|
|||
|
|
Dim strContent As String = Grid_Conditions.Cell(row, col).Text
|
|||
|
|
Dim dotPos As Integer = Strings.InStr(strContent, ".")
|
|||
|
|
|
|||
|
|
If dotPos > 0 Then
|
|||
|
|
Dim tablename As String = Strings.Left(strContent, dotPos - 1)
|
|||
|
|
Dim index As Integer = GetTableIndex(tablename)
|
|||
|
|
If index >= 0 Then
|
|||
|
|
If pCombo.Items.Count = 0 OrElse m_ComboDataType(col) <> COMBO_DATA_TYPE.section Then
|
|||
|
|
Fill_ComboList(pCombo, tablename, index, COMBO_DATA_TYPE.section)
|
|||
|
|
m_ComboDataType(col) = COMBO_DATA_TYPE.section
|
|||
|
|
End If
|
|||
|
|
End If
|
|||
|
|
Else
|
|||
|
|
If pCombo.Items.Count = 0 OrElse m_ComboDataType(col) <> COMBO_DATA_TYPE.table Then
|
|||
|
|
Fill_ComboList(pCombo, "", 0, COMBO_DATA_TYPE.table)
|
|||
|
|
m_ComboDataType(col) = COMBO_DATA_TYPE.table
|
|||
|
|
End If
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
pCombo.DropDown()
|
|||
|
|
Catch ex As Exception
|
|||
|
|
|
|||
|
|
End Try
|
|||
|
|
End If
|
|||
|
|
End Sub
|
|||
|
|
|
|||
|
|
|
|||
|
|
'Private Sub Grid_Conditions_ComboClick(ByVal Sender As System.Object, ByVal e As FlexCell.Grid.ComboClickEventArgs) Handles Grid_Conditions.ComboClick
|
|||
|
|
' '更新下拉列表
|
|||
|
|
' UpdateComboBoxList(m_EditRow, m_EditCol)
|
|||
|
|
'End Sub
|
|||
|
|
|
|||
|
|
Private Sub Grid_Conditions_CellChanging(ByVal Sender As System.Object, ByVal e As FlexCell.Grid.CellChangingEventArgs) Handles Grid_Conditions.CellChanging
|
|||
|
|
'更新下拉列表
|
|||
|
|
UpdateComboBoxList(e.Row, e.Col)
|
|||
|
|
|
|||
|
|
'自动添加"序号"
|
|||
|
|
If Grid_Conditions.Cell(e.Row, 0).Text <> e.Row.ToString Then
|
|||
|
|
Grid_Conditions.Cell(e.Row, 0).Text = e.Row
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
'自动增加一行
|
|||
|
|
If Grid_Conditions.Rows = (e.Row + 1) Then
|
|||
|
|
Grid_Conditions.Rows += 1
|
|||
|
|
End If
|
|||
|
|
End Sub
|
|||
|
|
|
|||
|
|
Private Function dgview_Condition() As Object
|
|||
|
|
Throw New NotImplementedException
|
|||
|
|
End Function
|
|||
|
|
|
|||
|
|
'Private Sub Grid_Conditions_EditRow(ByVal Sender As System.Object, ByVal e As FlexCell.Grid.EditRowEventArgs) Handles Grid_Conditions.EditRow
|
|||
|
|
' If Grid_Conditions.Cell(e.Row, 0).Text <> e.Row.ToString Then
|
|||
|
|
' Grid_Conditions.Cell(e.Row, 0).Text = e.Row
|
|||
|
|
' End If
|
|||
|
|
'End Sub
|
|||
|
|
|
|||
|
|
Private Sub Grid_Conditions_RowColChange(ByVal Sender As System.Object, ByVal e As FlexCell.Grid.RowColChangeEventArgs) Handles Grid_Conditions.RowColChange
|
|||
|
|
If e.Col = COLS.字段 OrElse e.Col = COLS.字段_值 Then
|
|||
|
|
bt_FillIn.Enabled = True
|
|||
|
|
Else
|
|||
|
|
bt_FillIn.Enabled = False
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
m_EditCol = e.Col
|
|||
|
|
m_EditRow = e.Row
|
|||
|
|
|
|||
|
|
'更新下拉列表
|
|||
|
|
UpdateComboBoxList(m_EditRow, m_EditCol)
|
|||
|
|
End Sub
|
|||
|
|
|
|||
|
|
Private Sub bt_FillIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_FillIn.Click
|
|||
|
|
Dim table_idx As Integer = clb_Table.SelectedIndex
|
|||
|
|
Dim section_idx As Integer = clb_Section.SelectedIndex
|
|||
|
|
|
|||
|
|
If table_idx >= 0 AndAlso section_idx >= 0 Then
|
|||
|
|
Grid_Conditions.Cell(m_EditRow, m_EditCol).Text = clb_Table.Items(table_idx) & "." & clb_Section.Items(section_idx)
|
|||
|
|
End If
|
|||
|
|
End Sub
|
|||
|
|
|
|||
|
|
'在TableNameList中查找tblName，并返回序号
|
|||
|
|
'如果未找到则返回-1
|
|||
|
|
Private Function GetTableIndex(ByVal tblName As String) As Integer
|
|||
|
|
Dim index As Integer = 0
|
|||
|
|
For Each pTable As SECTION_UNIT In TableNameList
|
|||
|
|
If pTable.name.ToUpper = tblName.ToUpper Then
|
|||
|
|
Return index
|
|||
|
|
End If
|
|||
|
|
index += 1
|
|||
|
|
Next
|
|||
|
|
|
|||
|
|
Return -1
|
|||
|
|
End Function
|
|||
|
|
|
|||
|
|
'在SectionNameList中查找secName，并返回序号
|
|||
|
|
'如果未找到则返回-1
|
|||
|
|
Private Function GetSectionIndex(ByVal tblIndex As Integer, ByVal secName As String) As Integer
|
|||
|
|
Dim pTable As ArrayList = SectionNameList.Item(tblIndex)
|
|||
|
|
|
|||
|
|
Dim index As Integer = 0
|
|||
|
|
For Each pSec As SECTION_UNIT In pTable
|
|||
|
|
If pSec.name.ToUpper = secName.ToUpper Then
|
|||
|
|
Return index
|
|||
|
|
End If
|
|||
|
|
index += 1
|
|||
|
|
Next
|
|||
|
|
|
|||
|
|
Return -1
|
|||
|
|
End Function
|
|||
|
|
|
|||
|
|
'检查指定的行是否为空
|
|||
|
|
Private Function CheckConditionTableLineEmpty(ByVal row As Integer) As Boolean
|
|||
|
|
Dim line_not_empty As Boolean = False
|
|||
|
|
Dim cell_text As String
|
|||
|
|
For col = 1 To Grid_Conditions.Cols - 1
|
|||
|
|
If Not (col = COLS.关联 And row = 1) Then '忽略行1的关联单元
|
|||
|
|
Try
|
|||
|
|
cell_text = Grid_Conditions.Cell(row, col).Text
|
|||
|
|
If cell_text.Length > 0 Then
|
|||
|
|
line_not_empty = True
|
|||
|
|
Exit For
|
|||
|
|
End If
|
|||
|
|
Catch ex As Exception
|
|||
|
|
|
|||
|
|
End Try
|
|||
|
|
End If
|
|||
|
|
Next
|
|||
|
|
Return line_not_empty
|
|||
|
|
End Function
|
|||
|
|
|
|||
|
|
'检查指定字符串是否为有效的字段名
|
|||
|
|
Private Function CheckTextIsValidSectionName(ByVal text As String) As SECTION_TYPE
|
|||
|
|
If text.Length = 0 Then Return SECTION_TYPE.无效
|
|||
|
|
|
|||
|
|
Dim m_section_type As SECTION_TYPE = SECTION_TYPE.无效
|
|||
|
|
Dim cell_split() As String = Split(text, ".")
|
|||
|
|
If cell_split.Length >= 2 Then '如果包含点，则检查是否为有效的字段名
|
|||
|
|
'查找当前表名是否存在
|
|||
|
|
Dim tbl_index As Integer = GetTableIndex(cell_split(0))
|
|||
|
|
If tbl_index >= 0 Then
|
|||
|
|
If GetSectionIndex(tbl_index, cell_split(1)) >= 0 Then
|
|||
|
|
m_section_type = SECTION_TYPE.字段
|
|||
|
|
End If
|
|||
|
|
End If
|
|||
|
|
Else
|
|||
|
|
'否则为其它值,非字段名
|
|||
|
|
m_section_type = SECTION_TYPE.值
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
Return m_section_type
|
|||
|
|
End Function
|
|||
|
|
|
|||
|
|
'对Grid_Conditions列表进行错误检查
|
|||
|
|
'并将错误的单元标记为红色
|
|||
|
|
'返回 True -- 无错误
|
|||
|
|
' False -- 错误
|
|||
|
|
Private Function ConditionCheck() As Boolean
|
|||
|
|
Dim col As Integer
|
|||
|
|
Dim row As Integer
|
|||
|
|
Dim col_count As Integer = Grid_Conditions.Cols
|
|||
|
|
Dim row_count As Integer = Grid_Conditions.Rows
|
|||
|
|
Dim cell_text As String
|
|||
|
|
|
|||
|
|
Dim valid As Boolean = True
|
|||
|
|
Dim line_not_empty As Boolean
|
|||
|
|
Dim line_valid As Boolean
|
|||
|
|
Dim cell_valid As Boolean
|
|||
|
|
For row = 1 To row_count - 1
|
|||
|
|
line_valid = True
|
|||
|
|
'检查当前行是否为空行
|
|||
|
|
line_not_empty = CheckConditionTableLineEmpty(row)
|
|||
|
|
|
|||
|
|
'如果当前行不为空
|
|||
|
|
If line_not_empty = True Then
|
|||
|
|
'检测每个行中的值是否有效
|
|||
|
|
For col = 0 To col_count - 1
|
|||
|
|
cell_valid = True
|
|||
|
|
If Not (col = COLS.关联 And row = 1) Then '忽略行1的关联单元
|
|||
|
|
cell_text = Grid_Conditions.Cell(row, col).Text
|
|||
|
|
'当前单元不能为空
|
|||
|
|
If cell_text.Length > 0 Then
|
|||
|
|
'字段列中的表与字段需要有效
|
|||
|
|
If col = COLS.字段 OrElse col = COLS.字段_值 Then
|
|||
|
|
If CheckTextIsValidSectionName(cell_text) = SECTION_TYPE.无效 Then
|
|||
|
|
cell_valid = False
|
|||
|
|
End If
|
|||
|
|
End If
|
|||
|
|
Else
|
|||
|
|
cell_valid = False
|
|||
|
|
End If
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
If cell_valid = False Then
|
|||
|
|
Grid_Conditions.Cell(row, col).BackColor = Color.Red
|
|||
|
|
line_valid = False
|
|||
|
|
Else
|
|||
|
|
Grid_Conditions.Cell(row, col).BackColor = Color.White
|
|||
|
|
End If
|
|||
|
|
Next
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
If line_valid = False Then
|
|||
|
|
Grid_Conditions.Cell(row, 0).BackColor = Color.Red
|
|||
|
|
valid = False
|
|||
|
|
Else
|
|||
|
|
Grid_Conditions.Cell(row, 0).BackColor = Grid_Conditions.Cell(0, 0).BackColor
|
|||
|
|
End If
|
|||
|
|
Next
|
|||
|
|
|
|||
|
|
Return valid
|
|||
|
|
End Function
|
|||
|
|
|
|||
|
|
'依据条件设置，生成对应的SQL语句
|
|||
|
|
Private Sub SQLGenerateFromCondition()
|
|||
|
|
Dim strSQL_all As String = ""
|
|||
|
|
Dim strSQL_Sections As String = ""
|
|||
|
|
Dim strSQL_Tables As String = ""
|
|||
|
|
Dim strSQL_Conditions As String = ""
|
|||
|
|
|
|||
|
|
Dim pTable As SECTION_UNIT
|
|||
|
|
Dim pSecList As ArrayList
|
|||
|
|
Dim pSec As SECTION_UNIT
|
|||
|
|
Dim tbl_idx As Integer
|
|||
|
|
Dim sec_idx As Integer
|
|||
|
|
|
|||
|
|
'查询字段与表
|
|||
|
|
For tbl_idx = 0 To TableNameList.Count - 1
|
|||
|
|
pTable = TableNameList.Item(tbl_idx)
|
|||
|
|
|
|||
|
|
'如果表被选中
|
|||
|
|
If pTable.check = True Then
|
|||
|
|
If strSQL_Tables.Length > 0 Then
|
|||
|
|
strSQL_Tables &= ","
|
|||
|
|
End If
|
|||
|
|
strSQL_Tables &= "`" & pTable.name & "`"
|
|||
|
|
|
|||
|
|
pSecList = SectionNameList.Item(tbl_idx)
|
|||
|
|
For sec_idx = 0 To pSecList.Count - 1
|
|||
|
|
pSec = pSecList.Item(sec_idx)
|
|||
|
|
If pSec.check = True Then
|
|||
|
|
If strSQL_Sections.Length > 0 Then
|
|||
|
|
strSQL_Sections &= ","
|
|||
|
|
End If
|
|||
|
|
strSQL_Sections &= "`" & pTable.name & "`.`" & pSec.name & "`"
|
|||
|
|
End If
|
|||
|
|
Next
|
|||
|
|
End If
|
|||
|
|
Next
|
|||
|
|
|
|||
|
|
'查询条件
|
|||
|
|
'Dim col As Integer
|
|||
|
|
Dim row As Integer
|
|||
|
|
Dim col_count As Integer = Grid_Conditions.Cols
|
|||
|
|
Dim row_count As Integer = Grid_Conditions.Rows
|
|||
|
|
Dim cell_text As String
|
|||
|
|
|
|||
|
|
Dim valid As Boolean = True
|
|||
|
|
Dim line_not_empty As Boolean
|
|||
|
|
Dim line_valid As Boolean
|
|||
|
|
For row = 1 To row_count - 1
|
|||
|
|
line_valid = True
|
|||
|
|
'检查当前行是否为空行
|
|||
|
|
line_not_empty = CheckConditionTableLineEmpty(row)
|
|||
|
|
|
|||
|
|
If line_not_empty = True Then
|
|||
|
|
If strSQL_Conditions.Length > 0 Then
|
|||
|
|
strSQL_Conditions &= " "
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
'关联
|
|||
|
|
cell_text = Grid_Conditions.Cell(row, COLS.关联).Text
|
|||
|
|
If cell_text.Length > 0 Then
|
|||
|
|
strSQL_Conditions &= GetRelConditionSQL(cell_text) & " "
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
'字段
|
|||
|
|
cell_text = Grid_Conditions.Cell(row, COLS.字段).Text
|
|||
|
|
strSQL_Conditions &= cell_text
|
|||
|
|
|
|||
|
|
'运算符
|
|||
|
|
cell_text = Grid_Conditions.Cell(row, COLS.运算).Text
|
|||
|
|
strSQL_Conditions &= " " & GetRelOperatorSQL(cell_text) & " "
|
|||
|
|
|
|||
|
|
'字段/值
|
|||
|
|
cell_text = Grid_Conditions.Cell(row, COLS.字段_值).Text
|
|||
|
|
If CheckTextIsValidSectionName(cell_text) = SECTION_TYPE.字段 Then
|
|||
|
|
strSQL_Conditions &= " " & cell_text & " "
|
|||
|
|
Else
|
|||
|
|
strSQL_Conditions &= "'" & cell_text & "' "
|
|||
|
|
End If
|
|||
|
|
End If
|
|||
|
|
Next
|
|||
|
|
|
|||
|
|
'合并各个字段
|
|||
|
|
strSQL_all = "SELECT " & strSQL_Sections & " FROM " & strSQL_Tables
|
|||
|
|
If strSQL_Conditions.Length > 0 Then
|
|||
|
|
strSQL_all &= " WHERE " & strSQL_Conditions
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
txt_SQL.Text = strSQL_all
|
|||
|
|
End Sub
|
|||
|
|
|
|||
|
|
'通过“关联”名称获取对应的SQL表示符
|
|||
|
|
Private Function GetRelConditionSQL(ByVal disname As String) As String
|
|||
|
|
For i As Integer = 0 To rel_Conditions.Length / 2 - 1
|
|||
|
|
If rel_Conditions(i, 0) = disname Then
|
|||
|
|
Return rel_Conditions(i, 1)
|
|||
|
|
End If
|
|||
|
|
Next
|
|||
|
|
Return ""
|
|||
|
|
End Function
|
|||
|
|
|
|||
|
|
'通过"运算符"民初获取对应的SQL表示符
|
|||
|
|
Private Function GetRelOperatorSQL(ByVal disname As String) As String
|
|||
|
|
For i As Integer = 0 To rel_Operator.Length / 2 - 1
|
|||
|
|
If rel_Operator(i, 0) = disname Then
|
|||
|
|
Return rel_Operator(i, 1)
|
|||
|
|
End If
|
|||
|
|
Next
|
|||
|
|
Return ""
|
|||
|
|
End Function
|
|||
|
|
|
|||
|
|
Private Sub bt_Query_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_Query.Click
|
|||
|
|
If ConditionCheck() = False Then
|
|||
|
|
MsgBox("条件列表存在错误!")
|
|||
|
|
Else
|
|||
|
|
QueryTable.Columns.Clear()
|
|||
|
|
QueryTable.Rows.Clear()
|
|||
|
|
QueryTable.Clear()
|
|||
|
|
If SQL_Query(txt_SQL.Text, QueryTable) = True Then
|
|||
|
|
grid_Result.SetDataBinding(QueryTable)
|
|||
|
|
End If
|
|||
|
|
grid_Result.Locked = True
|
|||
|
|
|
|||
|
|
lblItemCount.Text = "共 " & QueryTable.Rows.Count & " 条"
|
|||
|
|
End If
|
|||
|
|
End Sub
|
|||
|
|
|
|||
|
|
End Class
|