Looking for Help with DataGridView

Ask questions about creating Graphical User Interfaces (GUI) in PowerShell and using WinForms controls.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 10 years and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Looking for Help with DataGridView

Post by jvierra »

I still don't see the datagridview.
User avatar
jshew
Posts: 11
Last visit: Tue Dec 24, 2013 11:42 am

Re: Looking for Help with DataGridView

Post by jshew »

Sorry, I don't understand. It is named dgv_Status in the .pff. It is the big box just above the buttons.
User avatar
jshew
Posts: 11
Last visit: Tue Dec 24, 2013 11:42 am

Re: Looking for Help with DataGridView

Post by jshew »

Looks like the site accepts .pff files now. Here is a second copy in case the first one got corrupted.
Attachments
edit_sql_server_data.pff
(14.91 KiB) Downloaded 261 times
User avatar
davidc
Posts: 5913
Last visit: Mon Jul 08, 2019 8:55 am
Been upvoted: 2 times

Re: Looking for Help with DataGridView

Post by davidc »

Yes, we added support for pff files.

David
David
SAPIEN Technologies, Inc.
User avatar
jshew
Posts: 11
Last visit: Tue Dec 24, 2013 11:42 am

Re: Looking for Help with DataGridView

Post by jshew »

Thank you. :-)
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Looking for Help with DataGridView

Post by jvierra »

Wow!@ Thanks David.

I am looking at it now but fading fast. I still need to eat some dinner. I will mess with it later.

Had to reinstall MSSQLServer on a test system since I am in the middle of converting all machines to WIn8/WS2012.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Looking for Help with DataGridView

Post by jvierra »

Here is a first level refactoring. I have a better one but will take till later to attack it.

See attachment:
Attachments
edit_sql_server_data.Export.ps1.txt
(9.31 KiB) Downloaded 266 times
User avatar
jshew
Posts: 11
Last visit: Tue Dec 24, 2013 11:42 am

Re: Looking for Help with DataGridView

Post by jshew »

Thanks, I am checking it out.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Looking for Help with DataGridView

Post by jvierra »

Here is another quick refactoring to show how to reuse tricky code. This allows us to place logging and tracing code in a single place. With databases we usually want to trace on opens and access for the purpose of troubleshooting client side issues. This factoring prepares us for that.

If we need to expand on or change which database we are using we can easily be certain the code is easy to update. We just need to change code in two functions to move to a differnt database like MSAccess or Oracle.

We should also prep the database so that it can be easily moved without a need to change any code.

You can just replace the custom code in the PS1 by all functions in the custom section and then paste in this code. I will later externalize the connection string settings.
PowerShell Code
Double-click the code block to select all.
$handler_form1_Load={
    $script:Instance='.\SQLExpress'
    $script:Database='issue'
    Add-Type -AssemblyName System.Data
    Get-MasterTable
    Get-DetailTable
}

function Get-Connection{
    Param(
        [switch]$open
    )
    $conn=New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString="Server=$Instance;Database=$Database;Integrated Security=true"
    if($open){$conn.Open()}
    $conn
}

function Get-DataTable{
    Param(
        $control,
        $cmdText,
        $TaskId=0
    )
    $bindingSource=new-object System.Windows.Forms.BindingSource
    $control.DataSource=$bindingSource
    $cmd=New-Object System.Data.SqlClient.SqlCommand
    $conn=Get-Connection -open
    $cmd.Connection=$conn
    $cmd.CommandText=$cmdText
    $da=New-Object System.Data.SqlClient.SqlDataAdapter
    $da.SelectCommand=new-object System.Data.SqlClient.SqlCommand($cmdText,$conn)
    $commandBuilder=new-object System.Data.SqlClient.SqlCommandBuilder($da)
    $dt = New-Object System.Data.DataTable
    [void]$da.fill($dt)
    $da.SelectCommand=$cmd
    $dt=New-Object System.Data.DataTable
    $rows=$da.Fill($dt)
    $bindingSource.DataSource=$dt
    $conn.Close()
}

function Get-MasterTable{
     $cmdText='select * from issue.dbo.demo_task'
     Get-DataTable -cmdText $cmdText -control $dgvMaster
}

function Get-DetailTable{
    $TaskId=$dgvMaster.SelectedRows[0].Cells[0].Value
    $cmdText="select * from issue.dbo.demo_task_status where demo_task_id='$TaskID'"
    Get-DataTable -cmdText $cmdText -control $dgvDetail
}

$dgvMaster_MouseDoubleClick=[System.Windows.Forms.MouseEventHandler]{
    $row=$dgvMaster.SelectedRows[0]
    Get-DetailTable -TaskID $row.Cells[0].Value
}
That's all the code it takes to create a navigable form. Double click on the task and it displays the details. If we had DataControls available (Alex) we could create fully databound forms.

We can actually do that now but it is a big pain if the form design-mode does not know about the controls.
User avatar
jshew
Posts: 11
Last visit: Tue Dec 24, 2013 11:42 am

Re: Looking for Help with DataGridView

Post by jshew »

Thanks for your help so far. I really appreciate your time and suggestions (which I plan to implement). However, I still don't see a way to push the DataGridView changes back to SQL Server.

Am I missing something?
Attachments
code_from_sapien.ps1.txt
(8.66 KiB) Downloaded 261 times
This topic is 10 years and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked