dboperate.vb

通版开发平台

Imports System.Data.SqlClient

Public Class DbOperate
    Dim _fcUserID As String = "sa"
    Dim _fcPassword As String = "19841013"
    Dim _fcDataSource As String = "."
    Dim _fcInitialCatalog As String = "master"

    Public Shared csb As New SqlConnectionStringBuilder


    Public Property fcInitialCatalog()
        Get
            Return _fcInitialCatalog
        End Get
        Set(ByVal value)
            _fcInitialCatalog = value
        End Set
    End Property


    Public Property fcDataSource()
        Get
            Return _fcDataSource
        End Get
        Set(ByVal value)
            _fcDataSource = value
        End Set
    End Property

    Public Property fcPassword()
        Get
            Return _fcPassword
        End Get
        Set(ByVal value)
            _fcPassword = value
        End Set
    End Property

    Public Property fcUserID()
        Get
            Return _fcUserID
        End Get
        Set(ByVal value)
            _fcUserID = value
        End Set
    End Property
 

    Function getUsers(ByVal fcInitialCatalog As String) As List(Of S_User)

        Dim fcCommandText As String = "SELECT * FROM AS_User"
        Dim sb As New SqlConnectionStringBuilder
        Dim userList As New List(Of S_User)
        Try
            sb.UserID = fcUserID
            sb.Password = fcPassword
            sb.DataSource = fcDataSource
            sb.InitialCatalog = fcInitialCatalog

            csb = sb

            Using conn As New SqlConnection(sb.ToString)
                conn.Open()
                Using cmd As New SqlCommand
                    cmd.CommandText = fcCommandText
                    cmd.Connection = conn
                    Using rdr As SqlDataReader = cmd.ExecuteReader
                        While rdr.Read
                            Dim u As New S_User
                            u.flUserID = rdr.GetInt32(0)
                            If Not rdr.IsDBNull(1) Then
                                u.fcUserCode = rdr.GetString(1)
                            End If
                            If Not rdr.IsDBNull(2) Then
                                u.fcUserName = rdr.GetString(2)
                            End If
                            If Not rdr.IsDBNull(3) Then
                                u.fcFullName = rdr.GetString(3)
                            End If
                            If Not rdr.IsDBNull(4) Then
                                u.fcDescribe = rdr.GetString(4)
                            End If
                            If Not rdr.IsDBNull(5) Then
                                u.fcPass = rdr.GetString(5)
                            End If
                            If Not rdr.IsDBNull(6) Then
                                u.fbSysDefault = rdr.GetBoolean(6)
                            End If
                            If Not rdr.IsDBNull(7) Then
                                u.fbClosed = rdr.GetBoolean(7)
                            End If
                            userList.Add(u)
                        End While
                        rdr.Close()
                    End Using
                    cmd.Clone()
                End Using
                conn.Close()
            End Using
        Catch ex As Exception
            'MsgBox(ex.ToString)
        End Try


        Return userList
    End Function

    Shared Function getDataBases() As List(Of String)
        Dim fcSql As String = "exec sp_MSdbuseraccess N'db', N'%'"
        Dim dataBaseList As New List(Of String)

        Using conn As New SqlConnection(getfcConnection)
            conn.Open()
            Using cmd As New SqlCommand
                cmd.CommandText = fcSql
                cmd.Connection = conn
                Using rdr As SqlDataReader = cmd.ExecuteReader
                    While rdr.Read
                        dataBaseList.Add(rdr.GetString(0))
                    End While
                End Using
            End Using
        End Using

        Return dataBaseList
    End Function

    Shared Function getfcConnection() As String
        Dim sb As New SqlConnectionStringBuilder
        sb.UserID = "sa"
        sb.Password = "19841013"
        sb.InitialCatalog = "master"
        sb.DataSource = "."

        Return sb.ToString
    End Function



    Sub New()
        Dim fcSqlConnectionStringBuilder As New SqlConnectionStringBuilder
        fcSqlConnectionStringBuilder.UserID = fcUserID
        fcSqlConnectionStringBuilder.Password = fcPassword
        fcSqlConnectionStringBuilder.DataSource = fcDataSource
        fcSqlConnectionStringBuilder.InitialCatalog = fcInitialCatalog
    End Sub

    Function getConnection() As SqlConnection
        Dim conn As New SqlConnection(DbOperate.getfcConnection)
        conn.Open()
        Return conn
    End Function

End Class