SafeSQL.vb

出自ProgWiki

用途

• 自組SQL命令字串時，避免帶入的參數導致SQL資料隱碼。

程式碼

Imports System.Data
Imports System.Data.SqlClient
 
''' <summary>
''' Class SafeSQL
'''     手動組合SQL的字串時, 避免發生SQL資料隱碼
''' </summary>
Public Class SafeSQL
    Public Shared Function Quote(ByVal strData As String) As String
        If String.IsNullOrEmpty(strData) Then
            Return "''"
        Else
            Return String.Format("'{0}'", strData.Replace("'", "''"))
        End If
    End Function
 
    Public Shared Function QuoteLike(ByVal strData As String) As String
        If String.IsNullOrEmpty(strData) Then
            Return "'%'"
        Else
            Return String.Format("'%{0}%'", strData.Replace("'", "''"))
        End If
    End Function
 
    Public Shared Function QuoteLikeRight(ByVal strData As String) As String
        If String.IsNullOrEmpty(strData) Then
            Return "'%'"
        Else
            Return String.Format("'{0}%'", strData.Replace("'", "''"))
        End If
    End Function
 
    Public Shared Function QuoteLikeLeft(ByVal strData As String) As String
        If String.IsNullOrEmpty(strData) Then
            Return "'%'"
        Else
            Return String.Format("'%{0}'", strData.Replace("'", "''"))
        End If
    End Function
 
    Public Shared Function NoQuote(ByVal strData As String) As String
        If String.IsNullOrEmpty(strData) Then
            Return ""
        Else
            Return strData.Replace("'", "''")
        End If
    End Function
 
    Public Shared Function CreateInputParam(ByVal paramName As String, ByVal dbType As SqlDbType, ByVal size As Integer, ByVal objValue As Object) As SqlParameter
        Dim parameter As New SqlParameter(paramName, dbType, size)
        If objValue Is Nothing Then
            parameter.IsNullable = True
            parameter.Value = DBNull.Value
        Else
            parameter.Value = objValue
        End If
        Return parameter
    End Function
 
    Public Shared Function CreateInputParam(ByVal paramName As String, ByVal dbType As SqlDbType, ByVal objValue As Object) As SqlParameter
        Dim parameter As New SqlParameter(paramName, dbType)
        If objValue Is Nothing Then
            parameter.IsNullable = True
            parameter.Value = DBNull.Value
        Else
            parameter.Value = objValue
        End If
        Return parameter
    End Function
 
    Public Shared Function CreateOutputParam(paramName As String, dbType As SqlDbType, size As Integer) As SqlParameter
	Dim parameter As New SqlParameter(paramName, dbType)
	parameter.Direction = ParameterDirection.Output
	parameter.Size = size
	Return parameter
    End Function
 
End Class