Operation SQL Server Full Module

zhaozj2021-02-16  71

Module Modsql

Function GetInTBystr (Byval FieldName 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.EXECUTESCALA

Catch exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function getIntbyint (Byval Fieldname As String, Byval Paraname As String, BYVAL Paravalue As Integer) 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.EXECUTESCALA

Catch exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function GetStrBystr (Byval Fieldname As String, Byval Paraname As String, Byval Paravalue As String) AS STRINGDIM 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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return Trim (RowsAffected)

END FUNCTION

Function GetStrbyint (byval fieldname as string, byval paramentame 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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return Trim (RowsAffected)

END FUNCTION

Function getBitBYSTR (Byval Fieldname As String, Byval Paraname As String, Byval Paravalue As String) AS Booleandim 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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function getBitbyint (byval fieldname as string, byval paramename 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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function getdateByint (byval fieldname as string, byval parablename as string, byval paravalue as string, byval paravalue as integer) AS datetimedim 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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function getdateBystr (Byval Fieldname 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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function Delbyint (Byval Paraname As String, Byval Paravalue As Integer) AS INTEGERDIM 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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function Delbystr (Byval Tablename 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 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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function updatebitBysql (Byval Tablename 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 exception

Return EX.MESSAGE

Finally

SqlConnection.Close ()

END TRY

END FUNCTION

Function UpdateStrByint (Byval Tablename 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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

End Tryreturn RowsAffected

END FUNCTION

Function UpdateStrBystr (Byval Tablename 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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function UpdateIntbyint (byval Tablename As String, Byval DatafieldName As "AS Integer, Byval Para As String, BYVAL Para As String

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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

End FunctionFunction UpdateInTBystr (Byval Tablename 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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function updateInTBysql (Byval Tablename 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 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 IntegerDim 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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function updateBysql (Byval Tablename 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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function getDataTablebyformat (byval vrecordnumber as integer) as system.data.DataTable

Dim RecordNumber As String

IF VRecordNumber = 0 THEN

Recordnumber = ""

Else

RecordNumber = "TOP" & VRecordNumbendiff

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 exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

END FUNCTION

Function getDataTableBysql (Byval Supersql as string) as system.data.dataable

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 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.EXECUTESCALA

Catch 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.EXECUTESCALA

Catch exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function getfieldcount (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.EXECUTESCALARARARARAR

Catch exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

End FunctionFunction GetfieldsumByint (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.EXECUTESCALARARARARAR

Catch exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function getfieldsumbydec (Byval Tablename as string, byval fouthname 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.EXECUTESCALARARARARAR

Catch exception

Throw new NotSupportedException (ex.Message)

Finally

SqlConnection.Close ()

END TRY

Return RowsAffected

END FUNCTION

Function ChgfieldByleftword (Byval Tablename As String, Byval Paraword As String, Byval Newword As String) AS Integer

Try

Return UpdateBysql (Tablename, FieldName & "" & NewWord & "' Right (" & FieldName & "," & Len (Paraword) & ")", "Len (" & " FieldName & ")> =" & Len ("& FieldName &") Catch EX AS Exception

Throw new NotSupportedException (ex.Message)

END TRY

END FUNCTION

'Database connection string

Private function getConn () AS String

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

END FUNCTION

End module

转载请注明原文地址:https://www.9cbs.com/read-14683.html

New Post(0)