classes/sqlfunctions.vb - main - ivan/ModMas

classes/sqlfunctions.vb
393 строки14 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Collections
Imports System.Net
Imports System.IO

Namespace dbFunctions
    Public Class SqlFunctions
        Implements IDisposable

        'Simple class for basic DB operations
        Public Shared ConString As String = "Data Source=serkweb2.serk.lan;Initial Catalog=SerkIntranet;Persist Security Info=True;User ID=sa;Password=OrchestraSQL"
        Public lastException As String = ""
        Public Event ErrorHappaned(source As String, ex As Exception)
        Private _con As SqlConnection
        Private _defaultDb As String = ""

        Public Sub New(Optional ByVal sConString As String = "", Optional defaultDb As String = "") '
            If sConString <> "" Then
                ConString = sConString
            End If
            If defaultDb <> "" Then
                _defaultDb = "Use " & defaultDb & " "
            End If
        End Sub

        Private Sub Open()
            Try
                _con = New SqlConnection(ConString)
                _con.Open()
            Catch ex As Exception
            End Try
        End Sub

        Private Sub Close()
            Try
                _con.Close()
                _con = Nothing
            Catch ex As Exception
            End Try
        End Sub

        Public Function GetScalar(ByVal sqlScript As String) As String

            Dim result As String = ""
            Try
                Open()
                Dim com As New SqlCommand(_defaultDb & sqlScript, _con)
                result = com.ExecuteScalar
                If IsNothing(result) Then
                    result = ""
                End If
                com = Nothing
            Catch ex As Exception
                lastException = ex.Message
            Finally
                Close()
            End Try
            Return result
        End Function

        Public Function GetScalar(ByVal com As SqlCommand) As String

            Dim result As String = ""
            Try
                Open()
                com.Connection = _con
                com.CommandText = _defaultDb & com.CommandText
                result = com.ExecuteScalar
                If IsNothing(result) Then
                    result = ""
                End If
            Catch ex As Exception
                lastException = ex.Message
            Finally
                Close()
            End Try
            Return result
        End Function

        Public Function NonQuery(ByVal Query As String, Optional ByRef ProcessedRecords As Integer = 0, Optional ByRef ExMessage As String = "") As Integer
            ExMessage = ""
            lastException = ""
            Try

                Open()
                Dim cmd As New SqlCommand(_defaultDb & Query, _con)
                cmd.CommandTimeout = 180
                ProcessedRecords = cmd.ExecuteNonQuery()
            Catch ex As Exception
                ExMessage = ex.Message
                lastException = ex.Message
            Finally
                Close()
            End Try
            If ExMessage <> "" Then
                Return -1
            Else
                Return ProcessedRecords
            End If
        End Function

        Public Function orchestraSql(queryId As Integer, params As Hashtable) As DataTable
            Try
                Dim url As String = "http://serkweb.serk.lan:8080/modulemaster/Handlers/getsqldata.ashx?id=" & queryId
                Dim f As Boolean = True
                For Each k As DictionaryEntry In params
                    url &= "&"
                    url &= k.Key & "=" & k.Value
                Next
                Dim wb As WebRequest
                wb = WebRequest.Create(url)
                Dim res As WebResponse = wb.GetResponse
                Dim sr As New StreamReader(res.GetResponseStream)
                Return Newtonsoft.Json.JsonConvert.DeserializeObject(Of DataTable)(sr.ReadToEnd)
            Catch ex As Exception
                Return Nothing
            End Try
        End Function

        Public Function Insert(ByVal Query As String) As Integer
            Dim identity As Integer = -1
            Try
                Open()
                Dim cmd As New SqlCommand(_defaultDb & Query, _con)
                cmd.CommandTimeout = 180
                cmd.ExecuteNonQuery()
                cmd.CommandText = "SELECT @@IDENTITY"
                identity = cmd.ExecuteScalar
            Catch ex As Exception
                lastException = ex.Message
            Finally
                Close()
            End Try
            Return identity
        End Function

        Public Function Insert(ByVal cmd As SqlCommand) As Integer
            Dim identity As Integer = -1
            Try
                Open()
                cmd.Connection = _con
                cmd.CommandTimeout = 180
                cmd.ExecuteNonQuery()
                cmd.CommandText = "SELECT @@IDENTITY"
                identity = cmd.ExecuteScalar
            Catch ex As Exception
                lastException = ex.Message
            Finally
                Close()
            End Try
            Return identity
        End Function


        Public Function NonQuery(ByVal com As SqlCommand, Optional ByRef ProcessedRecords As Integer = 0, Optional ByRef ExMessage As String = "") As Integer

            ExMessage = ""
            Try
                Open()
                com.Connection = _con
                com.CommandText = _defaultDb & com.CommandText
                ProcessedRecords = com.ExecuteNonQuery()
            Catch ex As Exception
                lastException = ex.Message
                ExMessage = ex.Message
            Finally
                Close()
            End Try
            If ExMessage <> "" Then
                Return -1
            Else
                Return ProcessedRecords
            End If
        End Function

        Public Function NonQueryWithTransaction(ByVal Query As String, Optional ByRef ProcessedRecords As Integer = 0, Optional ByRef ExMessage As String = "") As Boolean

            Dim transaction As SqlTransaction

            'Dim cmd As New SqlCommand(Query, con)

            ExMessage = ""
            Try
                Open()
                Dim cmd As SqlCommand = _con.CreateCommand
                transaction = _con.BeginTransaction("NewTransaction")
                cmd.Connection = _con
                cmd.Transaction = transaction
                cmd.CommandText = _defaultDb & Query
                ProcessedRecords = cmd.ExecuteNonQuery()
                transaction.Commit()

                Return True
            Catch ex As Exception
                ExMessage = ex.Message
                lastException = ex.Message

                Try
                    transaction.Rollback()
                Catch exx As Exception

                End Try
            Finally
                Close()

            End Try

            If ExMessage <> "" Then
                Return False
            Else
                Return True
            End If
        End Function

        Public Function GetRow(ByVal Query As String) As DataRow
            Dim ds As New DataSet
            Try
                Open()
                Dim cmd As New SqlCommand(_defaultDb & Query, _con)
                Dim adapter As New SqlDataAdapter(cmd)
                adapter.Fill(ds)

            Catch ex As Exception
                lastException = ex.Message
            Finally
                Close()
            End Try
            If Not IsNothing(ds) Then
                If ds.Tables.Count > 0 Then
                    If ds.Tables(0).Rows.Count > 0 Then
                        Return ds.Tables(0).Rows(0)
                    End If
                End If
            End If
            Return Nothing
        End Function

        Public Function GetRow(ByVal com As SqlCommand) As DataRow
            Dim ds As New DataSet
            Try
                Open()
                com.Connection = _con
                Dim adapter As New SqlDataAdapter(com)
                adapter.Fill(ds)

            Catch ex As Exception
                lastException = ex.Message
            Finally
                Close()
            End Try
            If Not IsNothing(ds) Then
                If ds.Tables.Count > 0 Then
                    If ds.Tables(0).Rows.Count > 0 Then
                        Return ds.Tables(0).Rows(0)
                    End If
                End If
            End If
            Return Nothing
        End Function

        Public Function GetData(ByVal Query As String) As DataTable
            ' Query = " " & Query.ToLower

            'If Query.Contains(" delete ") Or
            '    Query.Contains(" update ") Or
            '    Query.Contains(" drop ") Or
            '    Query.Contains(" truncate ") Then
            '    Return Nothing
            'End If

            Dim ds As New DataSet
            Try
                Open()
                Dim cmd As New SqlCommand(_defaultDb & Query, _con)
                Dim adapter As New SqlDataAdapter(cmd)
                adapter.Fill(ds)
            Catch ex As Exception
                lastException = ex.Message
            Finally
                Close()
            End Try
            If Not IsNothing(ds) Then
                If ds.Tables.Count > 0 Then
                    Return ds.Tables(0)
                End If
            End If
            Return Nothing
        End Function

        Public Function GetData(ByVal com As SqlCommand) As DataTable
            'com.CommandText = " " & com.CommandText.ToUpper

            'If com.CommandText.Contains(" delete ") Or
            '    com.CommandText.Contains(" update ") Or
            '    com.CommandText.Contains(" drop ") Or
            '    com.CommandText.Contains(" truncate ") Then
            '    Return Nothing
            'End If

            Dim ds As New DataSet
            Try
                Open()
                com.Connection = _con
                com.CommandText = _defaultDb & com.CommandText
                Dim adapter As New SqlDataAdapter(com)
                adapter.Fill(ds)
            Catch ex As Exception
                lastException = ex.Message
            Finally
                Close()
            End Try
            If Not IsNothing(ds) Then
                If ds.Tables.Count > 0 Then
                    Return ds.Tables(0)
                End If
            End If
            Return Nothing
        End Function



        Public Function GetParameter(ByVal ParameterName As String) As String
            Dim sql As String = "SELECT DISTINCT Param_Value" & vbNewLine & _
                                "           FROM   [SERKIntranet].[dbo].Psi_Parameters" & vbNewLine & _
                                "           WHERE  Param_Name = '" & ParameterName & "'"
            Return GetScalar(sql)
        End Function

        Public Function SetParameter(ByVal ParameterName As String, ByVal ParameterValue As String) As Boolean
            Dim sql As String = "SELECT isnull(count(DISTINCT Param_Value),0) AS Param_Value" & vbNewLine & _
                                    "           FROM   [SERKIntranet].[dbo].Psi_Parameters" & vbNewLine & _
                                    "           WHERE  Param_Name = '" & ParameterName & "'"
            If Int(GetScalar(sql)) = 0 Then
                Return NonQuery("INSERT INTO [SERKIntranet].[dbo].Psi_Parameters (PARAM_NAME, PARAM_VALUE, plant) VALUES ('" & ParameterName & "','" & ParameterValue & "')")
            Else
                Return NonQuery("UPDATE [SERKIntranet].[dbo].Psi_Parameters SET param_value = '" & ParameterValue & "' WHERE param_name = '" & ParameterName & "' ")
            End If
            Return (False)
        End Function

        Public Sub SendEmail(_recepient As String, _subject As String, _body As String)
            Dim com As New System.Data.SqlClient.SqlCommand
            com.CommandText = "msdb.dbo.sp_send_dbmail"
            With com.Parameters
                .AddWithValue("profile_name", "Orchestra")
                .AddWithValue("recipients", _recepient & "@samsung.com")
                .AddWithValue("body_format", "HTML")
                .AddWithValue("subject", _subject)
                .AddWithValue("body", _body)
            End With
            com.CommandType = Data.CommandType.StoredProcedure
            Open()
            com.Connection = _con
            com.ExecuteNonQuery()
            Close()
        End Sub

#Region "IDisposable Support"
        Private disposedValue As Boolean ' To detect redundant calls

        ' IDisposable
        Protected Overridable Sub Dispose(disposing As Boolean)
            If Not Me.disposedValue Then
                If disposing Then
                    ' TODO: dispose managed state (managed objects).
                End If

                ' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
                ' TODO: set large fields to null.
            End If
            Me.disposedValue = True
        End Sub

        ' TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
        'Protected Overrides Sub Finalize()
        '    ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        '    Dispose(False)
        '    MyBase.Finalize()
        'End Sub

        ' This code added by Visual Basic to correctly implement the disposable pattern.
        Public Sub Dispose() Implements IDisposable.Dispose
            ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub
#End Region

    End Class
End Namespace