Imports System.Data.SqlClient
Imports System.Data #Region "IBogartToolbar,請勿隨便更改" Interface IBogartToolbar
Sub j_menuPrint()
Sub j_LoadData()
'Sub j_setToolsBar()
Sub j_ChangeRecord()
Sub j_AddRow()
Sub j_EditRow()
Property EnabledPrint() As Boolean
Sub SetToolsControl(ByVal g As myDataGrid.myDatagrid)
Property CurrentGrid() As myDataGrid.myDatagrid
End Interface
#End Region Module DataAccess #Region "資料庫常用方法"
Public SystemBackColor As Color = Color.FromArgb(, , )
Public SystemBackColor2003 As Color = Color.FromArgb(, , )
Public SystemBackColorMenu As Color = Color.FromArgb(, , ) Public Function ExectueSQL(ByVal strSQL As String, ByVal objConn As OleDb.OleDbConnection) As DataSet
Dim ds As New DataSet
Dim dap As New OleDb.OleDbDataAdapter
Try
dap = New OleDb.OleDbDataAdapter(strSQL, objConn)
dap.Fill(ds)
Catch ex As Exception
MsgBox(ex.ToString())
Finally
dap.Dispose()
End Try
Return ds
End Function
Public Function ExectueSQL(ByVal strSQL As String, ByVal objConn As SqlClient.SqlConnection) As DataSet
Dim ds As New DataSet
Dim dap As New SqlClient.SqlDataAdapter
Try
dap = New SqlClient.SqlDataAdapter(strSQL, objConn)
dap.Fill(ds)
Catch ex As Exception
MsgBox(ex.ToString())
Finally
dap.Dispose()
End Try
Return ds
End Function
Public Function ExectueSQL(ByVal SqlCmd As SqlClient.SqlCommand) As DataSet
Dim ds As New DataSet
Dim dap As New SqlClient.SqlDataAdapter
Try
dap = New SqlClient.SqlDataAdapter(SqlCmd)
dap.Fill(ds)
Catch ex As Exception
MsgBox(ex.ToString())
Finally
dap.Dispose()
End Try
Return ds
End Function
Public Function ExectueSQL(ByVal SqlCmd As OleDb.OleDbCommand) As DataSet
Dim ds As New DataSet
Dim dap As New OleDb.OleDbDataAdapter
Try
dap = New OleDb.OleDbDataAdapter(SqlCmd)
dap.Fill(ds)
Catch ex As Exception
MsgBox(ex.ToString())
Finally
dap.Dispose()
End Try
Return ds
End Function Public Function ExectueCmdAS400(ByVal strSQL As String, ByVal objConn As OleDb.OleDbConnection) As Boolean
Dim ret As Boolean = False
Try
Dim retCount As Integer = ExectueCmd(strSQL, objConn)
ret = IIf(retCount >= , True, False)
Catch ex As Exception
MsgBox("ExectueCmd " & vbCrLf & ex.ToString())
ret = False
End Try
Return ret
End Function Public Function ExectueCmd(ByVal strSQL As String, ByVal objConn As OleDb.OleDbConnection) As Integer
Dim cmd As OleDb.OleDbCommand
Dim ret As Integer =
Try
cmd = New OleDb.OleDbCommand(strSQL, objConn)
cmd.CommandType = CommandType.Text
ret = cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox("ExectueCmd:" & ex.ToString() & vbCrLf & strSQL)
ret = -
Finally
cmd.Dispose()
End Try
Return ret
End Function
Public Function ExectueCmd(ByVal strSQL As String, ByVal objConn As SqlClient.SqlConnection) As Integer
Dim cmd As SqlClient.SqlCommand
Dim ret As Integer
If objConn.State = ConnectionState.Closed Then
OpenConn(objConn)
End If
Try
cmd = New SqlClient.SqlCommand(strSQL, objConn)
ret = cmd.ExecuteNonQuery()
Catch ex As Exception
ret = -
Finally
cmd.Dispose()
End Try
Return ret
End Function
Public Function ExectueCmd(ByVal SqlCmd As SqlClient.SqlCommand) As Integer
Dim ret As Integer = -
Try
ret = SqlCmd.ExecuteNonQuery()
Catch ex As Exception
ret = -
Finally
SqlCmd.Dispose()
End Try
Return ret
End Function Public Function ExectueCmd(ByVal SqlCmd As OleDb.OleDbCommand) As Integer
Dim ret As Integer = -
Try
ret = SqlCmd.ExecuteNonQuery()
Catch ex As Exception
ret = -
Finally
SqlCmd.Dispose()
End Try
Return ret
End Function Public Function CDecs(ByVal strValue As String) As Decimal
If strValue.Trim() = "" Then
Return
Else
Return CDec(strValue)
End If
End Function Public Function IsALL(ByVal strValue As String) As String
strValue = strValue.Trim()
If strValue = "" Then
Return "All"
Else
Return strValue
End If
End Function
#End Region '請勿隨便更改 #Region "讀取Logo的方法" ''' <summary>
''' 添加公用報表Logo方法
''' </summary>
''' <param name="ds" type="Data.DataSet"> 包含 Logo(Table) 的DataSet </param>
''' <param name="AliasName" type="String">可選參數,資料庫字段的別名,默認:CompanyLogo,不區分大小寫.</param>
Public Sub GetLogo(ByRef ds As DataSet, Optional ByVal AliasName As String = "CompanyLogo")
Try
Dim dt As DataTable = GetLogo(AliasName)
ds.Tables.Add(dt)
ds.AcceptChanges()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub ''' <summary>
''' 添加公用報表Logo函數
''' </summary>
''' <param name="AliasName" type="String">可選參數,資料庫字段的別名,默認:CompanyLogo,不區分大小寫.</param>
''' <param name="tabName" type="String">可選參數,返回 Logo的表的名稱,默認:Logo,分大小寫.</param>
''' <returns> A System.Data.DataTable value </returns>
Public Function GetLogo(Optional ByVal AliasName As String = "CompanyLogo", Optional ByVal tabName As String = "Logo") As DataTable
Try
Dim strSQL As String = ""
Dim strCompanyCode As String = ""
If tabName.Trim = "" Then
tabName = "Logo"
End If If AliasName.Trim = "" Then
AliasName = "companylogo"
End If
Select Case g.gLocation
Case LocationType.CHINA_SHENZHEN
strCompanyCode = "SHS"
Case LocationType.THAILAND
strCompanyCode = "Thailand"
Case LocationType.HONGKONG
strCompanyCode = "AFT"
Case LocationType.BRUNE
strCompanyCode = "Brunet"
Case Else
strCompanyCode = "Bogart"
End Select
Dim dt As DataTable = GetLogo(strCompanyCode, AliasName, tabName)
Return dt.Copy
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Function
''' <summary>
''' 添加公用報表Logo函數
''' </summary>
''' <param name="strCompanyCode" type="String">公司編號,即:Logo的編號</param>
''' <param name="AliasName" type="String">資料庫字段的別名,不區分大小寫.</param>
''' <param name="tabName" type="String"> 返回 Logo的表的名稱,分大小寫.</param>
''' <returns> A System.Data.DataTable value </returns>
Public Function GetLogo(ByVal strCompanyCode As String, _
ByVal AliasName As String, _
ByVal tabName As String) As DataTable
Try
Dim strSQL As String = ""
Dim dt As DataTable = Nothing
If tabName.Trim = "" And AliasName.Trim = "" Then
Return dt
End If
If AliasName.ToLower = "companylogo" Then
strSQL = "select CompanyLogo from CompanyProfile where CompanyCode='" & strCompanyCode & "'"
Else
strSQL = "select CompanyLogo as " & AliasName & " from CompanyProfile where CompanyCode='" & strCompanyCode & "'"
End If
dt = DataAccess.ExectueSQL(strSQL, SqlConnect).Tables()
dt.TableName = tabName
Return dt.Copy
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Function
#End Region '請勿隨便更改 Public tmp_PHFRMT As DataTable Public Sub SetCheckBox(ByVal MyGrid As myDataGrid.myDatagrid, ByVal strColName As String)
strColName = strColName.Trim
If strColName = "" Then
Exit Sub
End If
Dim items As C1.Win.C1TrueDBGrid.ValueItems = MyGrid.Columns(strColName).ValueItems With items
' we're going to translate values - the datasource needs to hold at least 3 states
.Translate = True
' each click will cycle thru the various checkbox states
.CycleOnClick = True
.Presentation = C1.Win.C1TrueDBGrid.PresentationEnum.CheckBox
' now associate underlying db values with the checked state
.Values.Clear()
.Values.Add(New C1.Win.C1TrueDBGrid.ValueItem("", False)) ' unchecked
.Values.Add(New C1.Win.C1TrueDBGrid.ValueItem("", True)) ' checked
''.Values.Add(New C1.Win.C1TrueDBGrid.ValueItem("2", "INDETERMINATE")) ' indeterminate state
End With
End Sub ''' <summary>
''' 返回本地IP
''' </summary>
Public Function IPAdress() As String
Return System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList().ToString().Trim()
End Function Public Function ToDataTable(ByVal MyGrid As myDataGrid.myDatagrid) As DataTable
Dim ObjDataTable As DataTable
Try
If TypeOf (MyGrid.DataSource) Is DataView Then
Dim obDataView As DataView = CType(MyGrid.DataSource, DataView)
ObjDataTable = obDataView.Table.Clone()
Dim idx As Integer =
Dim strColNames(ObjDataTable.Columns.Count) As String
For Each col As DataColumn In ObjDataTable.Columns
strColNames(idx) = col.ColumnName
idx +=
Next
Dim IEnrator As IEnumerator = obDataView.GetEnumerator()
While IEnrator.MoveNext()
Dim drv As DataRowView = CType(IEnrator.Current, DataRowView)
Dim dr As DataRow = ObjDataTable.NewRow
Try
For Each strName As String In strColNames
dr(strName) = drv(strName)
Next
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
ObjDataTable.Rows.Add(dr)
End While
ElseIf TypeOf (MyGrid.DataSource) Is DataTable Then
ObjDataTable = CType(MyGrid.DataSource, DataTable)
End If
Catch oE As System.Exception
ObjDataTable = Nothing
ErrorMsg.Show(oE)
End Try
Return ObjDataTable
End Function
''' <summary>
''' 返回一個字符串
''' </summary>
''' <param name="o" type="Object"> </param>
''' <returns> A System.String value </returns>
Public Function Trims(ByVal o As Object) As String
If TypeOf (o) Is DBNull Then
Return ""
Else
Return Convert.ToString(o).Trim()
End If
End Function End Module