กำหนด Format Autonumber ใน Access 2003 แล้วแสดงข้อมูลใน VB.net

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Text
Imports System.Collections
Imports System.Collections.Generic

Imports System.Runtime.CompilerServices

'' Simple MsAccess Data Access Layer
'' Develop by Proud PNK.R. U.
'' Date 21-06-2010
'' Purpose : Provide neccessary method for manipulate Ms Access database through OleDB.
'' Some suggestion feature : Can be connect ACCESS 2007 (*.ACCDB) and common ACCESS (*.MDB) with auto detection.
''                           But i not yet to test with .MDB ,'coz my machine no have it. ~> <~

Namespace OleDbWithMSAccess

    Public Enum MSAccessVersionInfo
        AccessCommon = 1
        Access2007 = 2

    End Enum

    Public Class MSAccessConnectionArgs

        Private Const constAccess2007ConnectionStringFrame = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};" _
            & "Persist Security Info=False;"
        Private Const constAccess2007ConnectionStringFrameWithUserNameAndPassword = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};" _
            & "Jet OLEDB:Database Password={1};"

        Private Const constAccessCommonConnectionStringFrame = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" _
            & "Persist Security Info=False;"
        Private Const constAccessCommonConnectionStringFrameWithUserNameAndPassword = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" _
            & "User Id=admin;Password={1};"

        Private Const constMsAccessCommandParameterPrefix As String = "@"

        Private _VersionInfo As MSAccessVersionInfo = MSAccessVersionInfo.AccessCommon
        Private _DataBaseFile As String = String.Empty
        Private _HasUserName As Boolean = False
        'Private _UserName As String = String.Empty
        Private _Password As String = String.Empty

        Public Property VersionInfo() As MSAccessVersionInfo
            Get
                Return _VersionInfo
            End Get
            Set(ByVal value As MSAccessVersionInfo)
                _VersionInfo = value
            End Set
        End Property
        Public Property DataBaseFileName() As String
            Get
                Return _DataBaseFile
            End Get
            Set(ByVal value As String)
                _DataBaseFile = value
                If ((_UserName.Length = 0) And (_Password.Length = 0)) Then
                    _HasUserName = False
                Else
                    _HasUserName = True
                End If

                'Auto detect version from file extension
                Dim fileExtension As String = _DataBaseFile.Substring(_DataBaseFile.LastIndexOf("."c) + 1).ToUpper()
                If (fileExtension.Equals("ACCDB")) Then
                    _VersionInfo = MSAccessVersionInfo.Access2007
                Else
                    _VersionInfo = MSAccessVersionInfo.AccessCommon
                End If

            End Set
        End Property
        'Public Property UserName() As String
        '    Get
        '        Return _UserName
        '    End Get
        '    Set(ByVal value As String)
        '        _UserName = value
        '        If ((_UserName.Length = 0) And (_Password.Length = 0)) Then
        '            _HasUserName = False
        '        Else
        '            _HasUserName = True
        '        End If

        '    End Set
        'End Property
        Public Property Password() As String
            Get
                Return _Password
            End Get
            Set(ByVal value As String)
                _Password = value
                If (_Password.Length = 0) Then
                    _HasUserName = False
                Else
                    _HasUserName = True
                End If
            End Set
        End Property
        Public Property HasUserNameAndPassword() As Boolean
            Get
                Return _HasUserName
            End Get
            Set(ByVal value As Boolean)
                _HasUserName = value
            End Set
        End Property

        Public Sub New()
        End Sub

        Public Sub New(ByVal argVersionInfo As MSAccessVersionInfo)
            Me.VersionInfo = argVersionInfo
        End Sub
        Public Sub New(ByVal argVersionInfo As MSAccessVersionInfo, ByVal argDatabaseFileName As String)
            Me.VersionInfo = argVersionInfo
            Me.DataBaseFileName = argDatabaseFileName
        End Sub

        Public Function IsReadyToConnect() As Boolean
            Return (Me.DataBaseFileName.Length = 0)
        End Function

        Public Function GetConnectionString() As String
            Dim retStr As String = String.Empty
            Dim ConnStr As String = String.Empty

            If (Me.VersionInfo = MSAccessVersionInfo.AccessCommon) Then

                If (Me.HasUserNameAndPassword) Then
                    retStr = String.Format(constAccessCommonConnectionStringFrameWithUserNameAndPassword _
                                           , Me.DataBaseFileName, Me.Password)
                Else
                    retStr = String.Format(constAccessCommonConnectionStringFrame _
                                           , Me.DataBaseFileName)
                End If
            Else

                If (Me.HasUserNameAndPassword) Then
                    retStr = String.Format(constAccess2007ConnectionStringFrameWithUserNameAndPassword _
                                           , Me.DataBaseFileName, Me.Password)
                Else
                    retStr = String.Format(constAccess2007ConnectionStringFrame _
                                           , Me.DataBaseFileName)
                End If

            End If

            Return retStr
        End Function

    End Class

    Public Class MsAccessSQLCommandArgs

        Public ConnectionArgs As MSAccessConnectionArgs = New MSAccessConnectionArgs()
        ''' <summary>
        ''' Command String with SQL Statement [If you would like to use 'LIKE' 
        ''' operator only wildcard charactor '%' works for any charactor]
        ''' </summary>
        ''' <remarks></remarks>
        ''' 
        Public CommandStr As String = String.Empty
        Public CommandParameters As Hashtable = New Hashtable()

        Public Sub New()

        End Sub

        Public Sub ClearAll()
            CommandStr = String.Empty
            CommandParameters = New Hashtable()
        End Sub

        Public Sub ClearParameters()
            CommandParameters = New Hashtable()
        End Sub

        Public Sub AddParameter(ByVal argKey As String, ByVal argValue As Object)
            CommandParameters.Add(argKey, argValue)
        End Sub

    End Class

    Module MsAccessExtension

        Private Const constMsAccessCommandParameterPrefix As String = "@"

        <Extension()> _
        Public Sub ExecuteCommand(ByVal e As MsAccessSQLCommandArgs)
            Using myConnection As OleDbConnection _
                = New OleDbConnection(e.ConnectionArgs.GetConnectionString())

                Dim myCommand As OleDbCommand _
                    = New OleDbCommand(e.CommandStr, myConnection)

                myCommand.CommandType = CommandType.Text

                If (e.CommandParameters.Count > 0) Then

                    For Each keyStr As String In e.CommandParameters.Keys
                        Dim tempKey As String = keyStr
                        If (Not keyStr.Substring(0, 1).Equals(constMsAccessCommandParameterPrefix)) Then
                            tempKey = constMsAccessCommandParameterPrefix & tempKey
                        End If
                        myCommand.Parameters.AddWithValue(tempKey, e.CommandParameters(keyStr))
                    Next

                End If

                Try
                    myConnection.Open()
                    myCommand.ExecuteNonQuery()
                Catch ex As Exception
                    Throw New Exception(ex.Message)
                Finally
                    If Not (myConnection Is Nothing) Then
                        myConnection.Close()
                    End If
                End Try

            End Using
        End Sub

        <Extension()> _
        Public Function ExecuteCommandScalar(ByVal e As MsAccessSQLCommandArgs) As Object

            Dim oRet As Object = Nothing

            Using myConnection As OleDbConnection _
                = New OleDbConnection(e.ConnectionArgs.GetConnectionString())

                Dim myCommand As OleDbCommand _
                    = New OleDbCommand(e.CommandStr, myConnection)

                myCommand.CommandType = CommandType.Text

                If (e.CommandParameters.Count > 0) Then

                    For Each keyStr As String In e.CommandParameters.Keys
                        Dim tempKey As String = keyStr
                        If (Not keyStr.Substring(0, 1).Equals(constMsAccessCommandParameterPrefix)) Then
                            tempKey = constMsAccessCommandParameterPrefix & tempKey
                        End If
                        myCommand.Parameters.AddWithValue(tempKey, e.CommandParameters(keyStr))
                    Next

                End If

                Try
                    myConnection.Open()
                    oRet = myCommand.ExecuteScalar()
                Catch ex As Exception
                    Throw New Exception(ex.Message)
                Finally
                    If Not (myConnection Is Nothing) Then
                        myConnection.Close()
                    End If
                End Try

                Return oRet

            End Using
        End Function

        ''' <summary>
        ''' Return DataTable from MsAccessSQLCommandArgs
        ''' </summary>
        ''' <param name="e"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        <Extension()> _
        Public Function GetDataTable(ByVal e As MsAccessSQLCommandArgs) As DataTable

            Dim dtRet As DataTable = New DataTable()

            Using myConnection As OleDbConnection _
                = New OleDbConnection(e.ConnectionArgs.GetConnectionString())

                Dim myCommand As OleDbCommand _
                    = New OleDbCommand(e.CommandStr, myConnection)

                myCommand.CommandType = CommandType.Text

                If (e.CommandParameters.Count > 0) Then

                    For Each keyStr As String In e.CommandParameters.Keys
                        Dim tempKey As String = keyStr
                        If (Not keyStr.Substring(0, 1).Equals(constMsAccessCommandParameterPrefix)) Then
                            tempKey = constMsAccessCommandParameterPrefix & tempKey
                        End If
                        myCommand.Parameters.AddWithValue(tempKey, e.CommandParameters(keyStr))
                    Next

                End If
                Dim myDataAdaptor As OleDbDataAdapter = New OleDbDataAdapter(myCommand)

                Try
                    myConnection.Open()
                    myDataAdaptor.Fill(dtRet)

                Catch ex As Exception
                    Throw New Exception(ex.Message)
                Finally
                    If Not (myConnection Is Nothing) Then
                        myConnection.Close()
                    End If
                End Try

                Return dtRet

            End Using
        End Function

    End Module

End Namespace

Imports VB.OleDbWithMSAccess

Public Class Form1

    Private AppConnectionArg As MSAccessConnectionArgs = New MSAccessConnectionArgs()
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        AppConnectionArg.VersionInfo = MSAccessVersionInfo.Access2007
        AppConnectionArg.DataBaseFileName = "D:\BIN\Northwind 2007.accdb"


        Dim cmd As MsAccessSQLCommandArgs = New MsAccessSQLCommandArgs()
        cmd.ConnectionArgs = AppConnectionArg
        cmd.CommandStr = "SELECT MAX(ID) FROM [Employees] "
       
        Dim Counter As Integer =  CInt(cmd.ExecuteCommandScalar())
        Counter += 1
        
        Dim IDStr As String = "E" & Counter.ToString("00000")

    End Sub

End Class

Imports VB.OleDbWithMSAccess

Public Class Form1

    Private AppConnectionArg As MSAccessConnectionArgs = New MSAccessConnectionArgs()
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        AppConnectionArg.VersionInfo = MSAccessVersionInfo.Access2007
        AppConnectionArg.DataBaseFileName = "D:\BIN\Northwind 2007.accdb"       
        
        Dim IDStr As String = "E" & Counter.ToString("00000")

    End Sub

    Function NewAutoNumber (ByVal c As MSAccessConnectionArgs ) As String
        
        Dim Counter As Integer = 0 
        Dim cmd As MsAccessSQLCommandArgs = New  MsAccessSQLCommandArgs()
        cmd.ConnectionArgs = c
        'Field ID มี Type เป็น TEXT นะคะ
        cmd.CommandStr = "SELECT MAX(ID) FROM [TableName] "
        Dim tempStr As String = cmd.ExecuteCommandScalar().ToString()
        
        Counter = CInt(tempStr.SubString(1)) 'ตัดตัวอักษรออก แล้วเปลี่ยนเป็น integer
        Counter += 1

        tempStr = "E" + Counter.ToString("000000")
      
        Return tempStr         

    End Function
    


End Class