三层结构:实体层

实体层类:

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值