VB.NET操作SQL Server完全模块

80酷酷网    80kuku.com

  serverModule 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

分享到
  • 微信分享
  • 新浪微博
  • QQ好友
  • QQ空间
点击: