VB.NET操作SQL Server完全模块

类别:.NET开发 点击:0 评论:0 推荐:

Module ModSql

    Function GetIntByStr(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where  " & ParaName & "='" & ParaValue & "'"

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), 0, sqlCommand.ExecuteScalar)

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

 

    End Function

 

    Function GetIntByInt(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where  " & ParaName & "=" & ParaValue & ""

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), 0, sqlCommand.ExecuteScalar)

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function GetStrByStr(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As String

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where  " & ParaName & "='" & ParaValue & "'"

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As String = ""

        sqlConnection.Open()

        Try

            rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), "", sqlCommand.ExecuteScalar)

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return Trim(rowsAffected)

    End Function

 

    Function GetStrByInt(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As String

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where  " & ParaName & "=" & ParaValue & ""

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As String = ""

        sqlConnection.Open()

        Try

            rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), "", sqlCommand.ExecuteScalar)

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return Trim(rowsAffected)

    End Function

 

    Function GetBitByStr(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As Boolean

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where  " & ParaName & "='" & ParaValue & "'"

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Boolean = False

        sqlConnection.Open()

        Try

            rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), False, sqlCommand.ExecuteScalar)

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function GetBitByInt(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As Boolean

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where  " & ParaName & "=" & ParaValue & ""

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Boolean = False

        sqlConnection.Open()

        Try

            rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), False, sqlCommand.ExecuteScalar)

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function GetDateByInt(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As DateTime

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where  " & ParaName & "=" & ParaValue & ""

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As DateTime

        sqlConnection.Open()

        Try

            rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), DateAdd(DateInterval.Year, -100, Date.Now), sqlCommand.ExecuteScalar)

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function GetDateByStr(ByVal FieldName As String, ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As DateTime

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "select Top 1 " & FieldName & " from " & TableName & " where  " & ParaName & "='" & ParaValue & "'"

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As DateTime

        sqlConnection.Open()

        Try

            rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), DateAdd(DateInterval.Year, -100, Date.Now), sqlCommand.ExecuteScalar)

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function DelByInt(ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As Integer) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "DELETE FROM " & TableName & " WHERE " & ParaName & " =" & ParaValue

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = sqlCommand.ExecuteNonQuery

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function DelByStr(ByVal TableName As String, ByVal ParaName As String, ByVal ParaValue As String) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "DELETE FROM " & TableName & " WHERE " & ParaName & "='" & ParaValue & "'"

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = sqlCommand.ExecuteNonQuery

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

    Function DelBySQL(ByVal StrSQL As String) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = StrSQL

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = sqlCommand.ExecuteNonQuery

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

    Function UpdateBitBySQL(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As Boolean, ByVal ParaSQL As String) As String

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = " & IIf(DataFieldValue = True, 1, 0) & " WHERE " & ParaSQL

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        sqlConnection.Open()

        Try

            sqlCommand.ExecuteNonQuery()

            Return ""

        Catch ex As Exception

            Return ex.Message

        Finally

            sqlConnection.Close()

        End Try

    End Function

    Function UpdateStrByInt(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As String, ByVal Para As String, ByVal ParaValue As Integer) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = '" & DataFieldValue & "' WHERE " & Para & " = " & ParaValue

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = sqlCommand.ExecuteNonQuery

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function UpdateStrByStr(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As String, ByVal Para As String, ByVal ParaValue As String) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = '" & DataFieldValue & "' WHERE " & Para & " = '" & ParaValue & "'"

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = sqlCommand.ExecuteNonQuery

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function UpdateIntByInt(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As Integer, ByVal Para As String, ByVal ParaValue As String) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = " & DataFieldValue & " WHERE " & Para & " = " & ParaValue

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = sqlCommand.ExecuteNonQuery

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function UpdateIntByStr(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As Integer, ByVal Para As String, ByVal ParaValue As String) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = " & DataFieldValue & " WHERE " & Para & " = '" & ParaValue & "'"

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = sqlCommand.ExecuteNonQuery

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

 

    Function UpdateIntBySQL(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As Integer, ByVal ParaSQL As String) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = " & DataFieldValue & " WHERE " & ParaSQL

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = sqlCommand.ExecuteNonQuery

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

    Function UpdateStrBySQL(ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As String, ByVal ParaSQL As String) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "UPDATE " & TableName & " SET " & DataFieldName & " = '" & DataFieldValue & "' WHERE " & ParaSQL

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = sqlCommand.ExecuteNonQuery

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function UpdateBySQL(ByVal TableName As String, ByVal SetSQL As String, ByVal ParaSQL As String) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "UPDATE " & TableName & " SET " & SetSQL & " WHERE " & ParaSQL

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = sqlCommand.ExecuteNonQuery

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function GetDataTableByFormat(ByVal vRecordNumber As Integer, ByVal ItemSQL As String, ByVal TableName As String, ByVal ParaSQL As String) As System.Data.DataTable

 

        Dim RecordNumber As String

        If vRecordNumber = 0 Then

            RecordNumber = ""

        Else

            RecordNumber = "TOP " & vRecordNumber

        End If

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "SELECT " & RecordNumber & " " & ItemSQL & " FROM " & TableName & " where " & ParaSQL

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCommand)

        Dim dataSet As System.Data.DataSet = New System.Data.DataSet

        Try

            dataAdapter.Fill(dataSet)

            Return dataSet.Tables(0)

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

    End Function

 

    Function GetDataTableBySQL(ByVal SuperSQL As String) As System.Data.DataTable

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(SuperSQL, sqlConnection)

 

        Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCommand)

        Dim dataSet As System.Data.DataSet = New System.Data.DataSet

        Try

            dataAdapter.Fill(dataSet)

            Return dataSet.Tables(0)

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

    End Function

 

    Function GetMaxField(ByVal TableName As String, ByVal FieldName As String) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "SELECT MAX(" & FieldName & ") AS MaxField FROM  " & TableName

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), 0, sqlCommand.ExecuteScalar)

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function GetMinField(ByVal TableName As String, ByVal FieldName As String) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "SELECT Min(" & FieldName & ") AS MaxField FROM  " & TableName

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer = 0

        sqlConnection.Open()

        Try

            rowsAffected = IIf(IsDBNull(sqlCommand.ExecuteScalar), 0, sqlCommand.ExecuteScalar)

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function GetFieldCount(ByVal TableName As String, ByVal ParaSQL As String) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "SELECT COUNT(*) AS FieldCount FROM " & TableName & " where " & ParaSQL

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer

        sqlConnection.Open()

        Try

            rowsAffected = sqlCommand.ExecuteScalar

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function GetFieldSumByInt(ByVal TableName As String, ByVal FieldName As String, ByVal ParaSQL As String) As Integer

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "SELECT isnull(sum(" & FieldName & "),0) AS CountNumber FROM " & TableName & " where " & ParaSQL

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Integer

        sqlConnection.Open()

        Try

            rowsAffected = sqlCommand.ExecuteScalar

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function GetFieldSumByDec(ByVal TableName As String, ByVal FieldName As String, ByVal ParaSQL As String) As Decimal

 

        Dim sqlConnection As New SqlClient.SqlConnection(GetConn)

 

        Dim queryString As String = "SELECT isnull(sum(" & FieldName & "),0) AS CountNumber FROM " & TableName & " where " & ParaSQL

        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

 

        Dim rowsAffected As Decimal

        sqlConnection.Open()

        Try

            rowsAffected = sqlCommand.ExecuteScalar

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        Finally

            sqlConnection.Close()

        End Try

 

        Return rowsAffected

    End Function

 

    Function ChgFieldByLeftWord(ByVal TableName As String, ByVal FieldName As String, ByVal ParaWord As String, ByVal NewWord As String) As Integer

        Try

            Return UpdateBySQL(TableName, FieldName & "='" & NewWord & "'+right(" & FieldName & ",len(" & FieldName & ")-" & Len(ParaWord) & ")", "Len(" & FieldName & ")>=" & Len(ParaWord) & " and left(" & FieldName & "," & Len(ParaWord) & ")='" & ParaWord & "'")

        Catch ex As Exception

            Throw New NotSupportedException(ex.Message)

        End Try

    End Function

 

    '数据库连接串

    Private Function GetConn() As String

        Return "server=localhost;database=pubs;uid=sa;pwd="

    End Function

End Module

本文地址:http://com.8s8s.com/it/it43911.htm