I’m connecting to a database with multiple tables, one is ‘groups’, ‘doctype’ and ‘catalogs’.
This is necessary as the tables reference each other using the GUIDs, Groups have access to catalogs which contain doctypes… etc
I’ve saved these to datatables using the below code:
#build catalog reference $datagridview1.AutoSizeColumnsMode = 'DisplayedCells' $connectionstring = "Server = SQLSERVER1; Database = ContentCentral; Integrated Security = True" $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionstring $connection.Open() $CatalogReferenceQuery = "SELECT ID,Name FROM [ContentCentral].[dbo].[Catalog]" $command = $connection.CreateCommand() $command.CommandText = $CatalogReferenceQuery $CatalogResults = ($command.ExecuteReader()) $global:CatalogReference = New-Object System.Data.DataTable $CatalogReference.Load($CatalogResults) $connection.Close() #build Doctype reference $connectionstring = "Server = SQLSERVER1; Database = ContentCentral; Integrated Security = True" $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionstring $connection.Open() $DocTypeReferenceQuery = "SELECT ID,CatalogId FROM [ContentCentral].[dbo].[DocType]" $command = $connection.CreateCommand() $command.CommandText = $DocTypeReferenceQuery $DocTypeResults = ($command.ExecuteReader()) $global:DocTypeReference = New-Object System.Data.DataTable $DocTypeReference.Load($DocTypeResults) $connection.Close() #build Group reference $connectionstring = "Server = SQLSERVER1; Database = ContentCentral; Integrated Security = True" $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionstring $connection.Open() $GroupReferenceQuery = "SELECT ID,Name FROM [ContentCentral].[dbo].[Group]" $command = $connection.CreateCommand() $command.CommandText = $GroupReferenceQuery $GroupResults = ($command.ExecuteReader()) $global:GroupReference = New-Object System.Data.DataTable $GroupReference.Load($GroupResults) $connection.Close()
The datatable’s look like…
$GroupReference ID Name -- ---- 20bc949a-0292-4339-8b21-8b42526d4508 Account Analysis b79ac147-3869-4d06-ae71-7336d8e693cc Administration 5ad9fbdd-49fb-4cee-8fe5-4c4b993ce2ca Administrators 30607749-bb26-406d-bc58-db86bfecece9 Banc Services 590f7f60-971a-4fe2-9e2d-7c9043248b6d Cash Management 61740d3f-767b-444a-981c-359d068f1754 Central Files e52b5ee1-f2eb-4a32-8b71-12f59ed6eb94 Central Files View Only e0fb522c-4cd1-4bac-9685-3a0ad659c823 Certificate of Deposits 13906f6b-0615-4882-8081-877f3f71b992 Collateral d9e994ff-0e42-4edf-b647-4f1d8b1a3d76 Compliance d9d1ab4f-b19d-476c-8d97-e1b7dd380ff0 Control 3afda4d1-69c7-46d1-bf36-49da1574c36c DailyMaintenance Logs 9b065cab-43df-4970-b7b6-e65719085941 EVPCOO $CatalogReference ID Name -- ---- 05546661-9a9c-4915-8e38-10907b771c04 Account Analysis d901d0e1-3c5f-459c-8cbb-49b9cf32206f Administration 71ed5e7d-4435-4bc2-8b65-4b39a9bb2c2f Audit 1774b8c0-f759-49bd-8661-abe78e45625f Banc Services 4aa0e0df-13a8-4b3c-9882-bed3ed45f9f7 Cash Management 55ddaecc-2aae-45a5-bdd3-9573e36e4536 Central Files 9814b9be-9177-4f6a-9d01-ad255035dde8 Certificate of Deposits 89a3c0a9-9935-4d67-bbc3-18baa4fa425a Collateral 42d3fc77-c906-4807-8a0a-dbfe287b3021 COMPLIANCE 88b40d2f-e6c1-4dad-9cc3-a2a1ab012e18 Control 9356279e-0ec5-44e8-b3ee-7e360643adee Daily Maintenance Logs 4cac0a34-a140-4fa8-8f10-94b61d22098b EVPCOO $DocTypeReference ID CatalogId -- --------- 03b15dde-1362-4252-b556-19cfc4fc3958 335d98ff-ce07-495e-addd-03f2c06fca06 95ac091c-e976-49f5-b806-31b24bc770e7 335d98ff-ce07-495e-addd-03f2c06fca06 6fc63062-04cd-43f6-8147-369af1fa19ea 335d98ff-ce07-495e-addd-03f2c06fca06 53215a7f-276a-4f0d-9e85-6ddc81760221 335d98ff-ce07-495e-addd-03f2c06fca06 6c138a80-e11c-4406-918e-b3995cea7e6f 335d98ff-ce07-495e-addd-03f2c06fca06 2d12eef0-db56-4aec-9a2a-bc0338f84911 335d98ff-ce07-495e-addd-03f2c06fca06 05bc9f06-73fe-4a61-a63b-be03db0a249d 335d98ff-ce07-495e-addd-03f2c06fca06 6fa51b63-fe82-41dc-b667-f6b3f9a7d1d1 335d98ff-ce07-495e-addd-03f2c06fca06 77afeeb9-632b-4d37-bd86-239e85538814 05546661-9a9c-4915-8e38-10907b771c04 a51a36f0-f0e8-46b6-811a-4b5059b8dcc6 05546661-9a9c-4915-8e38-10907b771c04 24c005de-d1dd-42b4-ad9f-7801a6447fa3 05546661-9a9c-4915-8e38-10907b771c04 1b4625c1-dd90-4edf-b244-91bc70e6fe62 05546661-9a9c-4915-8e38-10907b771c04 81fc91ed-e40a-4ce9-8984-97960afc3eee 05546661-9a9c-4915-8e38-10907b771c04 a2f2d9df-c658-4dee-ba84-a237f2888985 05546661-9a9c-4915-8e38-10907b771c04 54a6831f-bdeb-4c55-b03d-f81cd11a1957 05546661-9a9c-4915-8e38-10907b771c04 20d8c79b-e4ae-41c6-ba0f-f8b2bab71d75 05546661-9a9c-4915-8e38-10907b771c04 69b3e956-60a2-46d6-bd7c-040f1e42cf3a 93b04769-192f-4368-bd8c-150e0b5c3acf 202ea888-a0b5-43b8-9e90-04215258fb41 93b04769-192f-4368-bd8c-150e0b5c3acf 3e599b00-fc6f-43b4-82d7-0a34e9413c3b 93b04769-192f-4368-bd8c-150e0b5c3acf
$groupreference
$groupreference contains 42 rows
$doctypereference contains 266 rows
$catalogreference contains 32 rows
Below is the code I’m using, it pulls into a datagridview in a format like…
Catalog|permission1|permission2|etc
It slows down dramatically on this line, taking about a second or two to produce each line. I’m no longer making database calls but referencing the 3 datatables I created earlier (which i thought would improve speed).
Long story short, is there a way to improve the speed of this process?
"$(($catalogreference | ? { $_.id -eq (($DocTypeReference | ? { $_.id -eq $doctypeid }).catalogid.guid) }).name)"
$connectionstring = "Server = SQLSERVER; Database = ContentCentral; Integrated Security = True"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionstring
$connection.Open()
$PermissionQuery = "SELECT doctypeid FROM [ContentCentral].[dbo].[DocTypePermission_S] where Userid LIKE '$($userHash.get_item($selected))' and type = 'user'"
$command = $connection.CreateCommand()
$command.CommandText = $PermissionQuery
$PermissionResult = ($command.ExecuteReader())
$Permissiontable = New-Object System.Data.DataTable
$Permissiontable.Load($PermissionResult)
$connection.Close()
$DocTypeIDs = ($permissiontable | foreach-object { $_.doctypeid.guid }) | sort-object -unique
$catalognamehash = @{ }
$names = @()
if ($doctypeids)
{
foreach ($DocTypeID in $DoctypeIDs)
{
$connectionstring = "Server = vmibsql1; Database = ContentCentral; Integrated Security = True"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionstring
$connection.Open()
$rightsQuery = "SELECT AllowDocView,AllowDocSearch,AllowDocBrowse,AllowDocAdd,AllowDocEdit,AllowDocMetaEdit,AllowDocDelete,AllowApprovalProcessAssign,AllowWorkQueueAssign,AllowRetentionOverride,AllowDocTypeAdmin,AllowApprovalProcessAdmin,AllowWorkQueueAdmin,AllowDocShare,AllowDocViewInApprovalQueue,AllowAnnotationWrite,AllowAnnotationPrint,AllowDocDownload FROM [ContentCentral].[dbo].[DocTypePermission_S] where doctypeid = '$doctypeid' and Userid LIKE '$($userHash.get_item($selected))' and type = 'user'"
$command = $connection.CreateCommand()
$command.CommandText = $rightsQuery
$RightsResult = ($command.ExecuteReader())
$rightstable = New-Object System.Data.DataTable
$rightstable.Load($RightsResult)
$connection.Close()
##really slows down here...##
"$(($catalogreference | ? { $_.id -eq (($DocTypeReference | ? { $_.id -eq $doctypeid }).catalogid.guid) }).name)"
$names += "$(($catalogreference | ? { $_.id -eq (($DocTypeReference | ? { $_.id -eq $doctypeid }).catalogid.guid) }).name)"
}
#only need the unique rows
$names = $names | Sort-Object -Unique
}
