20 Replies Latest reply on Sep 8, 2011 12:53 PM by Christina Seay

    bomlistproc<number> basics...

    Christina Seay

      I am trying to get the BOM out using VB.NET and the BomListProc3 stored procedure in EPDM.  I have the basics of running a stored procedure I think, but I'm lost on where to find the values for the parameters.  (I'm using Visual Studio 2010 and SQL Server Management Studio Express 2005)

       

      This is what I have so far:

       

      <code>

      'Setup SQL Command

              Dim sqlCommand As New SqlCommand("dbol.BomListProc3")

              sqlCommand.Parameters.Add(New SqlParameter("@DocID", SqlDbType.Int).Value = "")

              sqlCommand.Parameters.Add(New SqlParameter("@PrjID", SqlDbType.Int).Value = "")

              sqlCommand.Parameters.Add(New SqlParameter("@Ver", SqlDbType.Int).Value = "")

              sqlCommand.Parameters.Add(New SqlParameter("@CfgID", SqlDbType.Int).Value = "")

              sqlCommand.Parameters.Add(New SqlParameter("@UserID", SqlDbType.Int).Value = "")

              sqlCommand.Parameters.Add(New SqlParameter("@ShellRoot", SqlDbType.NVarChar).Value = "")

              sqlCommand.Parameters.Add(New SqlParameter("@oViewID", SqlDbType.UniqueIdentifier).Value = "")

              sqlCommand.Parameters.Add(New SqlParameter("@bUseLatestVer", SqlDbType.Bit).Value = "")

              sqlCommand.Parameters.Add(New SqlParameter("@bOneLevel", SqlDbType.Bit).Value = "")

              sqlCommand.Parameters.Add(New SqlParameter("@bExcludeRootFromResult", SqlDbType.Bit).Value = "")

              sqlCommand.Parameters.Add(New SqlParameter("@bRespectShowChildrenInResult", SqlDbType.Bit).Value = "")

              sqlCommand.Parameters.Add(New SqlParameter("@bStripItemTags", SqlDbType.Bit).Value = "")

       

       

              Dim sqlConnection As New SqlConnection("Data Source=Astec-sql;Initial Catalog=EPDM_Live_Vault;Persist Security Info=True;User ID=EPDM;Password=EnterpriseQuery...")

              sqlCommand.Connection = sqlConnection

              sqlCommand.CommandType = CommandType.StoredProcedure

       

       

              Dim sqlDataAdapter As New SqlDataAdapter(sqlCommand)

              sqlDataAdapter.SelectCommand.CommandTimeout = 300

       

       

              'Fill the dataset

              Dim sqlDataSet As New DataSet

              sqlDataAdapter.Fill(sqlDataSet)

              sqlConnection.Close()

       

       

              DataGridView_BOM.DataSource = sqlDataSet

      </code>

       

        • Re: bomlistproc<number> basics...
          Wayne Matus

          I think I understand your question, but forgive me if I am wrong.

           

          @DocID would be the ID of the assembly file that has the BOM. @PrjID would be the folder ID that the assembly file is located in. For the other parameters, if may be a trial and error method of finding what it needs. Execute the stured proceedure in SQL Server Managerment Studio, playing with different parameters.

           

          I do not think you will find any documentation on the EPDM stored proceedures since they are really internal queries that most administrators do not need to know about.

          • Re: bomlistproc<number> basics...
            Lee CS Young

            @DocID = DocumentID of the document that has the cutlist that you're trying to get,

            @PrjID = ProjectID of folder containing the document,

            @Ver = Version you require (pass 0 for highest readable version),

            @CfgID = ConfigurationID if you use configs. Pass 0 for the active configuration,

            @UserID = UserID of EPDM user you wish to run the query for. (For user permissions),

            @ShellRoot = Vault root, PASS NULL HERE

            @oViewID = UniqueID of client vault view. PASS NULL HERE

             

            The other parameters can be ignored as they have defaults defined.

              • Re: bomlistproc<number> basics...
                Christina Seay

                Thank-you!  I've got stored procedure part working... for some reason, my access to the procedures changed, and the only one I can run is BomListProc1... so I'm waiting on my IT guy to fix that so I can get to BomListProc3.  The results I got from BomListProc1 didn't really make sense for a BOM... so I'm hoping it's just an issue because I need to use 3 instead of 1.  But this helped me to be able to execute the stored procedure.

                 

                In case anyone else can benefit from this:

                 

                Imports System.Data.SqlClient

                Imports EdmLib 'EPDM Reference File

                 

                    Public Vault As IEdmVault11 'New connection to the EPDM File vault

                    Public VaultFile As IEdmFile5 'File in the Vault

                    Public PathList As EdmStrLst5 'List of files in the Vault

                    Public FilePath As String 'Path to file in the Vault

                    Public ParentFolder As IEdmFolder5 'Parent folder to file

                 

                    'EdmBrowseFlags used in the method IEdmVault5::BrowseForFile

                    Public EdmBws_ForOpen As Integer = 0 'Display an Open (not a Save As) dialog box

                    Public EdmBws_PermitVaultFiles As Integer = 8

                 

                    'SQL Parameters

                    Public DocID As SqlParameter 'Document ID of the document that has the BOM/Cutlist I'm trying to get

                    Public PrjId As SqlParameter 'ProjectID of the folder containing the document (Parent folder)

                    Public Ver As SqlParameter 'Version required (0 for the highest readable version)

                    Public CfgID As SqlParameter 'ConfigurationID if using configurations (0 for the active configuration)

                    Public UserID As SqlParameter 'UserID of EPDM user that I want to run the query for (For user permissions)

                    Public ShellRoot As SqlParameter 'Shell Root - Vault Root, pass a null here

                    Public oViewID As SqlParameter 'UniqueID of client vault view, pass null here

                 

                'Login to the Vault

                Vault = New EdmVault5

                Vault.LoginAuto("Vault", Me.Handle.ToInt32)

                 

                'Get the file

                PathList = Vault.BrowseForFile(Me.Handle.ToInt32, EdmBws_ForOpen + EdmBws_PermitVaultFiles, "", "", "", "", "Select A file")

                        If Not PathList Is Nothing Then 'Make sure they didn't click cancel

                            FilePath = PathList.GetNext(PathList.GetHeadPosition)

                            'Get the interface of the file and it's parent folder

                            VaultFile = Vault.GetFileFromPath(FilePath, ParentFolder)

                        End If

                 

                'Set SQL Parameter values

                        DocID = New SqlParameter("@DocID", SqlDbType.Int)

                        DocID.Value = VaultFile.ID

                        PrjId = New SqlParameter("@PrjId", SqlDbType.Int)

                        PrjId.Value = ParentFolder.ID

                        Ver = New SqlParameter("@Ver", SqlDbType.Int)

                        Ver.Value = 0 'Higest readable version

                        CfgID = New SqlParameter("@CfgID", SqlDbType.Int)

                        CfgID.Value = 0 'active configuration

                        UserID = New SqlParameter("@UserID", SqlDbType.Int)

                        UserID.Value = Vault.GetLoggedInWindowsUserID(Vault.Name)

                        ShellRoot = New SqlParameter("@ShellRoot", SqlDbType.NVarChar)

                        ShellRoot.Value = DBNull.Value

                        oViewID = New SqlParameter("@oViewID", SqlDbType.UniqueIdentifier)

                        oViewID.Value = DBNull.Value

                 

                'Setup SQL Command

                Dim sqlCommand As New SqlCommand("dbo.BomListProc3")

                        sqlCommand.Parameters.Add(DocID)

                        sqlCommand.Parameters.Add(PrjId)

                        sqlCommand.Parameters.Add(Ver)

                        sqlCommand.Parameters.Add(CfgID)

                        sqlCommand.Parameters.Add(UserID)

                        sqlCommand.Parameters.Add(ShellRoot)

                        sqlCommand.Parameters.Add(oViewID)

                 

                        Dim sqlConnection As New SqlConnection("YOUR CONNECTION STRING HERE")

                        sqlCommand.Connection = sqlConnection

                        sqlCommand.CommandType = CommandType.StoredProcedure

                        Dim sqlDataAdapter As New SqlDataAdapter(sqlCommand)

                        sqlDataAdapter.SelectCommand.CommandTimeout = 300

                 

                        'Fill the dataset

                        

                        Dim sqlDataSet As New DataSet

                        sqlDataAdapter.Fill(sqlDataSet)

                        sqlConnection.Close()

                • Re: bomlistproc<number> basics...
                  Christina Seay

                  Ok... I'm probably missing something just incredibly obvious here.  I'm trying to modify my code so that the user's get a list of configurations for a selected part, then they select which configuration they want, and I use the SQL bit to get that configuration.  I worked out how to get a list of the configurations of the selected part:

                   

                          ConfigurationList = VaultFile.GetConfigurations

                          EnumeratorVariable = VaultFile.GetEnumeratorVariable

                          TreePosition = ConfigurationList.GetHeadPosition

                          While Not TreePosition.IsNull

                              ConfigurationName = ConfigurationList.GetNext(TreePosition)

                              ListBox_Configurations.Items.Add(ConfigurationName)

                          End While

                   

                  But the SQL part wants an Int32 and not a string for CfgID.Value  So what I can't figure out is how to retrieve the ConfigurationID of a part instead of the Configuration Name.  Does anyone have any thoughts there?

                    • Re: bomlistproc<number> basics...
                      Wayne Matus

                      You may have to run a query on the dbo.DocumentConfiguration table. It contains the ConfigurationID and ConfigurationName.

                      • Re: bomlistproc<number> basics...
                        Shawn Kelly

                        If you're going after SQL then I think you're going to need to execute a statement something like this to get what you're looking for to get the ID.

                         

                        SELECT     DocumentConfiguration.ConfigurationID

                        FROM         Documents INNER JOIN

                                     DocumentRevisionConfiguration ON Documents.DocumentID = DocumentRevisionConfiguration.DocumentID INNER JOIN

                                     DocumentConfiguration ON DocumentRevisionConfiguration.ConfigurationID = DocumentConfiguration.ConfigurationID

                        Where DocumentConfiguration.ConfigurationName = '<configname here>' AND Documents.DocumentID = '<ID>' AND DocumentRevisionConfiguration.RevisionNo = <Version>

                          • Re: bomlistproc<number> basics...
                            Christina Seay

                            Right now I'm not exactly using SQL... I'm just executing the BomListProc1 and BomListProc3 stored procedures and working through the EPDM API commands to let them select a file and get a list of the available configurations.  I can execute a SQL Query if that's the only way to get the Configuration ID though.  I'll post the code back if I get it working.  Thank-you for your help!

                      • Re: bomlistproc<number> basics...
                        Shawn Kelly

                        You might want to be careful about hardcoding the procedure name into your application or add-in.  Unless something has changed.  The dbo.BomListProc# is not always the same number.  In your case and probably many it's the #3, but if you delete the default BOMs in the admin tool and develop some more then it's not 3 anymore, but rather 7,8,9,10, etc...

                         

                        One way to test that is to open the admin tool, create a new bom layout and it's likely there will be a new procedure with the next sequential number on it.

                         

                        Also, if you open the admin tool and remove all the BOM layouts there would be no procedures at all...