三层结构：实体层

最新推荐文章于 2023-04-13 17:23:06 发布

viewercq

最新推荐文章于 2023-04-13 17:23:06 发布

阅读量948

点赞数

分类专栏： MyOwn 文章标签： string function integer dataset 数据库 insert

本文链接：https://blog.csdn.net/viewercq/article/details/24169

版权

MyOwn 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

实体层类：

Imports System.Data
Imports System.Data.SqlClient
Imports Global_utility

Public Class DataBaseOperate
Private StrSqlConn As String
Private DbConn As SqlConnection

    Public Sub New()
        '构造函数
        MyBase.New()
        Dim gp As Global_params = New Global_params
        Dim ci As CONFIG_INFO = gp.Get_Params("g_info")
        Dim StrServername, StrUser, StrPwd As String
        StrServername = ci.SERVER_NAME
        StrUser = ci.USER_ID
        StrPwd = ci.PASSWORD
        StrSqlConn = "server=" & StrServername & ";Initial Catalog=XJOADATA;User Id=" & StrUser & ";Password=" & StrPwd & ";"
        DbConn = New SqlConnection(StrSqlConn)
    End Sub

    Public Function GetConnection() As SqlConnection
        Return DbConn
    End Function

    Public Function Read(ByVal StrTableName As String, ByVal StrField As String, ByVal StrParameter As String, ByVal StrField2 As String, ByVal StrParameter2 As String) As DataView
        '读取数据库中表StrTableName中字段StrField值等于StrParameter并且字段StrField2值等于StrParameter2的所有记录
        Dim DbComm As SqlDataAdapter
        Dim Ds As New DataSet
        Dim StrSql As String
        StrSql = "select * from " & StrTableName & " where " & StrField & " = '" & StrParameter & "'and " & StrField2 & " = '" & StrParameter2 & "'"
        DbComm = New SqlDataAdapter(StrSql, DbConn)
        DbConn.Open()
        DbComm.Fill(Ds, StrTableName)
        DbConn.Close()
        Return Ds.Tables(StrTableName).DefaultView
    End Function

    Public Function Delete(ByVal DbConnTran As SqlConnection, ByVal Tx As SqlTransaction, ByVal StrTableName As String, ByVal StrField As String, ByVal StrParameter As String, ByVal StrField2 As String, ByVal StrParameter2 As String) As Integer
        '读取数据库中表StrTableName中字段StrField值等于StrParameter并且字段StrField2值等于StrParameter2的所有记录
        Dim DbComm As SqlCommand
        Dim StrSql As String

StrSql = "delete from " & StrTableName & " where " & StrField & " = '" & StrParameter & "'and " & StrField2 & " = '" & StrParameter2 & "'"
DbComm = New SqlCommand(StrSql, DbConnTran, Tx)

DbComm.ExecuteNonQuery()

End Function

    Public Function Insert(ByVal DbConnTran As SqlConnection, ByVal Tx As SqlTransaction, ByVal Dr As DataRow) As Integer
        '把Dr中的数据以事务方式插入到数据库
        Dim DbComm As SqlCommand
        Dim StrField, StrParameter, StrSql As String
        Dim Count As Integer
        Dim DataParam As SqlParameter

        StrField = "" '格式化Sql语句中的字段和参数
        StrParameter = ""
        For Count = 0 To Dr.Table.Columns.Count - 1
            StrField = StrField & Dr.Table.Columns(Count).ColumnName() & ","
            StrParameter = StrParameter & "@" & Dr.Table.Columns(Count).ColumnName() & ","
        Next
        StrField = Left(StrField, Len(StrField) - 1)
        StrParameter = Left(StrParameter, Len(StrParameter) - 1)

StrSql = "insert into " & Dr.Table.TableName & " ( " & StrField & ") values ( " & StrParameter & ")"

DbComm = New SqlCommand(StrSql, DbConnTran, Tx)

        For Count = 0 To Dr.Table.Columns.Count - 1 '给Sql语句中的所有参数赋值
            DataParam = New SqlParameter("@" & Dr.Table.Columns(Count).ColumnName(), Dr(Count))
            DbComm.Parameters.Add(DataParam)
        Next

DbComm.ExecuteNonQuery() '执行插入操作
End Function

    Public Function Update(ByVal DbConnTran As SqlConnection, ByVal Tx As SqlTransaction, ByVal Dr As DataRow) As Integer
        '根据ID修改数据库中数据
        Dim DbComm As SqlCommand
        Dim StrSql As String
        Dim Count As Integer
        Dim DataParam As SqlParameter

        StrSql = ""
        For Count = 1 To Dr.Table.Columns.Count - 1
            StrSql = StrSql & Dr.Table.Columns(Count).ColumnName & "=@" & Dr.Table.Columns(Count).ColumnName & ","
        Next
        StrSql = Left(StrSql, Len(StrSql) - 1)
        StrSql = "update " & Dr.Table.TableName & " set " & StrSql & " where " & Dr.Table.Columns(0).ColumnName & "='" & Dr(0) & "'"

DbComm = New SqlCommand(StrSql, DbConnTran, Tx)

        For Count = 1 To Dr.Table.Columns.Count - 1 '给Sql语句中的所有参数赋值
            DataParam = New SqlParameter("@" & Dr.Table.Columns(Count).ColumnName(), Dr(Count))
            DbComm.Parameters.Add(DataParam)
        Next

DbComm.ExecuteNonQuery() '执行修改操作
End Function

    Public Function ReadSql(ByVal StrSql As String) As DataView
        Dim DbComm As SqlDataAdapter
        Dim Ds As New DataSet
        DbComm = New SqlDataAdapter(StrSql, DbConn)
        DbConn.Open()
        DbComm.Fill(Ds)
        DbConn.Close()
        Return Ds.Tables(0).DefaultView
    End Function

End Class