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.
User avatar
jvierra
Posts: 13681
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Looking for Help with DataGridView

Post by jvierra » Wed Oct 02, 2013 12:41 pm

I still don't see the datagridview.

User avatar
jshew
Posts: 11
Joined: Sun Mar 03, 2013 12:43 pm

Re: Looking for Help with DataGridView

Post by jshew » Wed Oct 02, 2013 2:49 pm

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
Joined: Sun Mar 03, 2013 12:43 pm

Re: Looking for Help with DataGridView

Post by jshew » Wed Oct 02, 2013 3:13 pm

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 174 times

User avatar
davidc
Posts: 5913
Joined: Thu Aug 18, 2011 4:56 am

Re: Looking for Help with DataGridView

Post by davidc » Wed Oct 02, 2013 3:23 pm

Yes, we added support for pff files.

David
David
SAPIEN Technologies, Inc.

User avatar
jshew
Posts: 11
Joined: Sun Mar 03, 2013 12:43 pm

Re: Looking for Help with DataGridView

Post by jshew » Wed Oct 02, 2013 3:29 pm

Thank you. :-)

User avatar
jvierra
Posts: 13681
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Looking for Help with DataGridView

Post by jvierra » Wed Oct 02, 2013 5:20 pm

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.

User avatar
jvierra
Posts: 13681
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Looking for Help with DataGridView

Post by jvierra » Thu Oct 03, 2013 10:25 am

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 167 times

User avatar
jshew
Posts: 11
Joined: Sun Mar 03, 2013 12:43 pm

Re: Looking for Help with DataGridView

Post by jshew » Thu Oct 03, 2013 5:18 pm

Thanks, I am checking it out.

User avatar
jvierra
Posts: 13681
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Looking for Help with DataGridView

Post by jvierra » Fri Oct 04, 2013 10:17 am

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
Joined: Sun Mar 03, 2013 12:43 pm

Re: Looking for Help with DataGridView

Post by jshew » Sun Oct 06, 2013 12:43 pm

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 174 times

Locked