CSDN博客

img viewercq

三层结构:实体层

发表于2004/6/23 17:16:00  646人阅读

分类: MyOwn

实体层类:

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

0 0

相关博文

我的热门文章

img
取 消
img