VB.NET操作SQL Server完全模块

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