.NET开发

本类阅读TOP10
·NHibernate快速指南(翻译)
·vs.net 2005中文版下载地址收藏
·【小技巧】一个判断session是否过期的小技巧
·VB/ASP 调用 SQL Server 的存储过程
·？dos下编译.net程序找不到csc.exe文件
·通过Web Services上传和下载文件
·学习笔记（补）《.NET框架程序设计（修订版）》--目录
·VB.NET实现DirectDraw9 (2) 动画
·VB.NET实现DirectDraw9 (1) 托管的DDraw
·建站框架规范书之——文件命名

→ 分类导航
VC语言Delphi
VB语言ASP
PerlJava
Script数据库
其他语言游戏开发
文件格式网站制作
软件工程.NET开发
VB.NET操作SQL Server完全模块

作者：未知 来源：月光软件站 加入时间：2005-2-28　月光软件站

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

相关文章：

相关软件：