VB.net 2005 操纵SQL SERVER 完全模块

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

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