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

Looking for Help with DataGridView

Post by jshew » Wed Oct 02, 2013 5:14 am

I am looking for help using PowerShell with a DataGridView control. The PrimalForms CE code below assumes that you have a database named "issue" with two tables defined in it. (See below for the SQL table definitions.)

This code attempts do to the following:

A) Read a parent row from SQL Server

B) Read several matching children rows from SQL Server

C) Populate the parent row data on the Windows Form using text boxes

D) Populate the children row data on the Windows Form using a DataGridView control

E) Allow the user to modify the parent and child data on the Windows Form

F) Update the parent row in the database with the user's changes (this works)

G) Update the children rows in the database with the user's changes (I am looking for a way to do this)

-----------------------------------------

Questions:

1) How can I push the user changes in the DataGridView control back to SQL Server?

2) How should I handle the database connection(s) efficiently--I am not a Developer--I am not sure of the best way to handle the database connections--I am thinking that I opening and closing the connections too frequently, which could created unwanted overhead on the database server if several Admins use the application at the same time

3) Should I read the parent and child rows in the same connection? If so, how?

Thanks in advance.

J


-----------------------------------------

Here is the syntax for creating the SQL Server tables

Code: Select all

use issue;

/*******************************************************************************
*
* Create the demo_task table
*
* Usage: 1 row per task
*
********************************************************+**********************/

----- Table

if exists (select name from sysobjects where xtype='U' and name = 'demo_task_status') drop table demo_task_status;
if exists (select name from sysobjects where xtype='U' and name = 'demo_task')        drop table demo_task;
go

create table issue.dbo.demo_task
	(
	demo_task_id		int		not null,
	demo_task_title_dsc	varchar(255)	null,
	);

alter table issue.dbo.demo_task
add
	constraint demo_task_pk primary key
		(
		demo_task_id
		);

insert into issue.dbo.demo_task values ( 1, 'Task One');
insert into issue.dbo.demo_task values ( 2, 'Task Two');
insert into issue.dbo.demo_task values ( 3, 'Task Three');

select
	demo_task_id,
	demo_task_title_dsc
from
	issue.dbo.demo_task
order by
	demo_task_id
	;
go

/*******************************************************************************
*
* Create the demo_task_status table
*
* Usage: 1 row per task status; a given task can have many status rows to
* reflect the status of the task over time
*
********************************************************+**********************/

if exists (select name from sysobjects where xtype='U' and name = 'demo_task_status') drop table demo_task_status;
go

create table issue.dbo.demo_task_status
	(
	demo_task_id		int		not null,
	demo_task_status_dte	datetime	not null,
	demo_task_status_dsc	varchar(max)	null
	);

alter table issue.dbo.demo_task_status
add
	constraint demo_task_status_pk primary key
		(
		demo_task_id,
		demo_task_status_dte
		);

insert into issue.dbo.demo_task_status values (1, '10/05/2012', 'Task One, Status One');
insert into issue.dbo.demo_task_status values (1, '11/01/2012', 'Task One, Status Two');
insert into issue.dbo.demo_task_status values (1, '12/01/2012', 'Task One, Status Three');
insert into issue.dbo.demo_task_status values (1, '01/01/2013', 'Task One, Status Four');
insert into issue.dbo.demo_task_status values (2, '10/05/2012', 'Task Two, Status One');
insert into issue.dbo.demo_task_status values (2, '02/01/2013', 'Task Two, Status Two');
insert into issue.dbo.demo_task_status values (2, '05/01/2013', 'Task Two, Status Three');
insert into issue.dbo.demo_task_status values (3, '10/22/2012', 'Task Three, Status One');

select
	demo_task_id,
	demo_task_status_dte,
	demo_task_status_dsc
from
	issue.dbo.demo_task_status
order by
	demo_task_id,
	demo_task_status_dte
	;
go
-----------------------------------------

Here is the syntax for the PrimalForms CE code:
PowerShell Code
Double-click the code block to select all.
param
	(
	[string]$DatabaseConnection_STR = "SQLServerNetworkName\InstanceName",
	[string]$Database_NME           = "issue",
	[int]$DemoTask_ID               = 1
	) # End of param

	$script:DemoTask_ID = $DemoTask_ID

	<###############################################################
	#
	# Read task row
	#
	############################################+##################>

function ReadTaskRow
	{

	# Create the SqlConnection object and define the connection
	# string

	$conn                  = New-Object System.Data.SqlClient.SqlConnection
	$conn.ConnectionString = "Server = $DatabaseConnection_STR; Database = $Database_NME; Integrated Security = true"

	# Create the first SqlCommand object, define the command text,
	# and set the connection

	$cmd1             = New-Object System.Data.SqlClient.SqlCommand

	$cmd1.CommandText = @"
select
	demo_task_id,
	demo_task_title_dsc
from
	issue.dbo.demo_task
where
	demo_task_id = $script:DemoTask_ID
	;
"@ # End of $cmd1.CommandText

	$cmd1.Connection  = $conn

	# Create the SqlDataAdapter object

	$da = New-Object System.Data.SqlClient.SqlDataAdapter

	# Create DataSet object

	$ds = New-Object System.Data.DataSet

	# Set the first command and populate the DataSet object

	$da.SelectCommand = $cmd1
	$da.Fill($ds, "demo_task_rows") | Out-Null

	# Close the connection

	$conn.close()

	<###############################################################
	#
	# Return the dataset to the calling routine
	#
	###############################################################>

	return $ds

	} # End of function ReadTaskRow

	<###############################################################
	#
	# Read the matching task status row(s)
	#
	############################################+##################>

function ReadTaskStatusRows
	{

	$SQLCommand_STR = @"
select
	demo_task_status_dte as [Status Date],
	demo_task_status_dsc as [Description]
from
	issue.dbo.demo_task_status
where
	demo_task_id = $script:DemoTask_ID
order by
	demo_task_id,
	demo_task_status_dte
	;
"@ # End of $SQLCommand_STR variable

	$bindingSource1            = new-object System.Windows.Forms.BindingSource
	$dataAdapter               = new-object System.Data.SqlClient.SqlDataAdapter

	$dgv_Status.DataSource     = $bindingSource1
	$connString                = "Server=$DatabaseConnection_STR;Database=$Database_NME;Integrated Security=SSPI;"
	$dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($SQLCommand_STR,$connString)
	$commandBuilder            = new-object System.Data.SqlClient.SqlCommandBuilder $dataAdapter
	$dt                        = new-object System.Data.DataTable

	[void]$dataAdapter.fill($dt)
	$bindingSource1.DataSource = $dt

	} # End of function ReadTaskStatusRows

	<###############################################################
	#
	# Populate form
	#
	############################################+##################>

function PopulateForm
	{

	<###############################################################
	#
	# Call the routines to read the task row
	#
	############################################+##################>

	$ds = ReadTaskRow

	<###############################################################
	#
	# Populate "Task ID" text box control
	#
	############################################+##################>

	$tb_TaskID.ReadOnly                    = $true
	$tb_TaskID.Text                        = $ds.Tables["demo_task_rows"].Rows[0]["demo_task_id"]

	<###############################################################
	#
	# Populate "Task Title" text box control
	#
	############################################+##################>

	$tb_TaskTitle.Text                     = $ds.Tables["demo_task_rows"].Rows[0]["demo_task_title_dsc"]

	<###############################################################
	#
	# Call the routine to read the task status row(s)
	#
	# The call to ReadTaskStatusRows also populates the Status
	# scroll box
	#
	############################################+##################>

	ReadTaskStatusRows

	} # End of function PopulateForm

	<###############################################################
	#
	# Save task
	#
	############################################+##################>

function SaveTask
	{

	<###############################################################
	#
	# Handle form values that contain nulls and single quotes
	#
	############################################+##################>

	if ($tb_TaskTitle.Text -eq "")
	{
		$form_TaskTitle = "null"
	} else {
		$form_TaskTitle = "'" + $($tb_TaskTitle.Text).Replace('''', '''''') + "'"
	}

	<###############################################################
	#
	# Write the parent row back to the database
	#
	############################################+##################>

	# Create SqlConnection object, define connection string, and open connection

	$conn                  = New-Object System.Data.SqlClient.SqlConnection
	$conn.ConnectionString = "Server = $DatabaseConnection_STR; Database = $Database_NME; Integrated Security = true"
	$conn.Open()

	# Create SqlCommand object, define command text, and set the connection

	$cmd                   = New-Object System.Data.SqlClient.SqlCommand

	$cmd.CommandText = @"
update issue.dbo.demo_task
	set
		demo_task_title_dsc   = $form_TaskTitle
	where
		demo_task_id = $script:DemoTask_ID
		;
"@ # End of $cmd.CommandText

	$cmd.Connection        = $conn

	$RowsUpdated           = $cmd.ExecuteNonQuery()

	# Close the connection

	$conn.Close()

	return

	} # End of function SaveTask

	<###############################################################
	#
	# Close the form
	#
	############################################+##################>

function CloseForm
	{

	$form1.Close()

	} # End of function CloseForm











#Generated Form Function
function GenerateForm {
########################################################################
# Code Generated By: SAPIEN Technologies PrimalForms (Community Edition) v1.0.10.0
# Generated On: 10/1/2013 8:38 PM
# Generated By: jshewbridge
########################################################################

#region Import the Assemblies
[reflection.assembly]::loadwithpartialname("System.Drawing") | Out-Null
[reflection.assembly]::loadwithpartialname("System.Windows.Forms") | Out-Null
#endregion

#region Generated Form Objects
$form1 = New-Object System.Windows.Forms.Form
$btn_Cancel = New-Object System.Windows.Forms.Button
$btn_DeleteTask = New-Object System.Windows.Forms.Button
$btn_NextTask = New-Object System.Windows.Forms.Button
$btn_PreviousTask = New-Object System.Windows.Forms.Button
$btn_SaveTask = New-Object System.Windows.Forms.Button
$label9 = New-Object System.Windows.Forms.Label
$dgv_Status = New-Object System.Windows.Forms.DataGridView
$tb_TaskTitle = New-Object System.Windows.Forms.TextBox
$label3 = New-Object System.Windows.Forms.Label
$tb_TaskID = New-Object System.Windows.Forms.TextBox
$label1 = New-Object System.Windows.Forms.Label
$InitialFormWindowState = New-Object System.Windows.Forms.FormWindowState
#endregion Generated Form Objects

#----------------------------------------------
#Generated Event Script Blocks
#----------------------------------------------
#Provide Custom Code for events specified in PrimalForms.
$btn_PreviousTask_OnClick=
{
#TODO: Place custom script here

	$script:DemoTask_ID = $script:DemoTask_ID - 1

	PopulateForm
}

$btn_Cancel_OnClick=
{
#TODO: Place custom script here

	CloseForm
}

$btn_NextTask_OnClick=
{
#TODO: Place custom script here

	$script:DemoTask_ID = $script:DemoTask_ID + 1

	PopulateForm
}

$btn_SaveTask_OnClick=
{
#TODO: Place custom script here

	SaveTask
}

$btn_DeleteTask_OnClick=
{
#TODO: Place custom script here

}

$handler_form1_Load=
{
#TODO: Place custom script here

	PopulateForm
}

$OnLoadForm_StateCorrection=
{#Correct the initial state of the form to prevent the .Net maximized form issue
	$form1.WindowState = $InitialFormWindowState
}

#----------------------------------------------
#region Generated Form Code
$form1.AcceptButton = $btn_SaveTask
$form1.CancelButton = $btn_Cancel
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 259
$System_Drawing_Size.Width = 854
$form1.ClientSize = $System_Drawing_Size
$form1.DataBindings.DefaultDataSourceUpdateMode = 0
$form1.Name = "form1"
$form1.Text = "Edit Task"
$form1.add_Load($handler_form1_Load)


$btn_Cancel.DataBindings.DefaultDataSourceUpdateMode = 0
$btn_Cancel.DialogResult = 2

$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 753
$System_Drawing_Point.Y = 225
$btn_Cancel.Location = $System_Drawing_Point
$btn_Cancel.Name = "btn_Cancel"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 23
$System_Drawing_Size.Width = 85
$btn_Cancel.Size = $System_Drawing_Size
$btn_Cancel.TabIndex = 8
$btn_Cancel.Text = "Cancel"
$btn_Cancel.UseVisualStyleBackColor = $True
$btn_Cancel.add_Click($btn_Cancel_OnClick)

$form1.Controls.Add($btn_Cancel)


$btn_DeleteTask.DataBindings.DefaultDataSourceUpdateMode = 0

$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 294
$System_Drawing_Point.Y = 225
$btn_DeleteTask.Location = $System_Drawing_Point
$btn_DeleteTask.Name = "btn_DeleteTask"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 23
$System_Drawing_Size.Width = 85
$btn_DeleteTask.Size = $System_Drawing_Size
$btn_DeleteTask.TabIndex = 7
$btn_DeleteTask.Text = "Delete Task"
$btn_DeleteTask.UseVisualStyleBackColor = $True
$btn_DeleteTask.add_Click($btn_DeleteTask_OnClick)

$form1.Controls.Add($btn_DeleteTask)


$btn_NextTask.DataBindings.DefaultDataSourceUpdateMode = 0

$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 203
$System_Drawing_Point.Y = 225
$btn_NextTask.Location = $System_Drawing_Point
$btn_NextTask.Name = "btn_NextTask"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 23
$System_Drawing_Size.Width = 85
$btn_NextTask.Size = $System_Drawing_Size
$btn_NextTask.TabIndex = 6
$btn_NextTask.Text = "Next Task"
$btn_NextTask.UseVisualStyleBackColor = $True
$btn_NextTask.add_Click($btn_NextTask_OnClick)

$form1.Controls.Add($btn_NextTask)


$btn_PreviousTask.DataBindings.DefaultDataSourceUpdateMode = 0

$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 112
$System_Drawing_Point.Y = 225
$btn_PreviousTask.Location = $System_Drawing_Point
$btn_PreviousTask.Name = "btn_PreviousTask"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 23
$System_Drawing_Size.Width = 85
$btn_PreviousTask.Size = $System_Drawing_Size
$btn_PreviousTask.TabIndex = 5
$btn_PreviousTask.Text = "Previous Task"
$btn_PreviousTask.UseVisualStyleBackColor = $True
$btn_PreviousTask.add_Click($btn_PreviousTask_OnClick)

$form1.Controls.Add($btn_PreviousTask)


$btn_SaveTask.DataBindings.DefaultDataSourceUpdateMode = 0

$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 20
$System_Drawing_Point.Y = 225
$btn_SaveTask.Location = $System_Drawing_Point
$btn_SaveTask.Name = "btn_SaveTask"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 23
$System_Drawing_Size.Width = 85
$btn_SaveTask.Size = $System_Drawing_Size
$btn_SaveTask.TabIndex = 4
$btn_SaveTask.Text = "Save Task"
$btn_SaveTask.UseVisualStyleBackColor = $True
$btn_SaveTask.add_Click($btn_SaveTask_OnClick)

$form1.Controls.Add($btn_SaveTask)

$label9.DataBindings.DefaultDataSourceUpdateMode = 0

$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 12
$System_Drawing_Point.Y = 64
$label9.Location = $System_Drawing_Point
$label9.Name = "label9"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 23
$System_Drawing_Size.Width = 70
$label9.Size = $System_Drawing_Size
$label9.TabIndex = 0
$label9.Text = "Status"
$label9.TextAlign = 16

$form1.Controls.Add($label9)

$dgv_Status.DataBindings.DefaultDataSourceUpdateMode = 0
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 112
$System_Drawing_Point.Y = 64
$dgv_Status.Location = $System_Drawing_Point
$dgv_Status.Name = "dgv_Status"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 155
$System_Drawing_Size.Width = 726
$dgv_Status.Size = $System_Drawing_Size
$dgv_Status.TabIndex = 3

$form1.Controls.Add($dgv_Status)

$tb_TaskTitle.DataBindings.DefaultDataSourceUpdateMode = 0
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 112
$System_Drawing_Point.Y = 36
$tb_TaskTitle.Location = $System_Drawing_Point
$tb_TaskTitle.Name = "tb_TaskTitle"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 20
$System_Drawing_Size.Width = 726
$tb_TaskTitle.Size = $System_Drawing_Size
$tb_TaskTitle.TabIndex = 2

$form1.Controls.Add($tb_TaskTitle)

$label3.DataBindings.DefaultDataSourceUpdateMode = 0

$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 12
$System_Drawing_Point.Y = 36
$label3.Location = $System_Drawing_Point
$label3.Name = "label3"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 23
$System_Drawing_Size.Width = 94
$label3.Size = $System_Drawing_Size
$label3.TabIndex = 0
$label3.Text = "Task Title"
$label3.TextAlign = 16

$form1.Controls.Add($label3)

$tb_TaskID.DataBindings.DefaultDataSourceUpdateMode = 0
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 112
$System_Drawing_Point.Y = 13
$tb_TaskID.Location = $System_Drawing_Point
$tb_TaskID.Name = "tb_TaskID"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 20
$System_Drawing_Size.Width = 100
$tb_TaskID.Size = $System_Drawing_Size
$tb_TaskID.TabIndex = 1

$form1.Controls.Add($tb_TaskID)

$label1.DataBindings.DefaultDataSourceUpdateMode = 0

$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 11
$System_Drawing_Point.Y = 13
$label1.Location = $System_Drawing_Point
$label1.Name = "label1"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 23
$System_Drawing_Size.Width = 94
$label1.Size = $System_Drawing_Size
$label1.TabIndex = 0
$label1.Text = "Task ID"
$label1.TextAlign = 16

$form1.Controls.Add($label1)

#endregion Generated Form Code

#Save the initial state of the form
$InitialFormWindowState = $form1.WindowState
#Init the OnLoad event to correct the initial state of the form
$form1.add_Load($OnLoadForm_StateCorrection)
#Show the Form
$form1.ShowDialog()| Out-Null

} #End Function

#Call the Function
GenerateForm

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 9:00 am

I moved this post to the PowerShell GUIs forum.

David
David
SAPIEN Technologies, Inc.

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

Re: Looking for Help with DataGridView

Post by jvierra » Wed Oct 02, 2013 9:34 am

Firstly - you ar3e asking for a lot of help that touches on design and implementation issues.

I can answer a few basic questions.

Opening and losing a connection is not an issue. It is easier to do that if you are just doing reads. If you are also doing updates it is normal two have to connections. One trimmed for read performance and one for updates although this is an advanced issue and would only affect applications and not admin tools.

Not sure what you men by updating child rows. If they are in a datatable you would have to supply the update method and use a batch update.

Look for C# or VB.Net examples of how to use a datagrid and how to manage relations in a form. You will have to perform the same code calls as the code you find that does this the way you want. Much depends on your database design and what you are trying to update.

I will give a quick look at your code but I do not feel like trying to analyze a 100 lines of code but a quick review may give me an idea of what you are trying to do.

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

Re: Looking for Help with DataGridView

Post by jvierra » Wed Oct 02, 2013 9:41 am

First issue = you are not loading the sqlclient assembly.

It would be better to have your PFF file as an attachment.

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 9:54 am

Here is the .pff file

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

Re: Looking for Help with DataGridView

Post by jvierra » Wed Oct 02, 2013 10:11 am

I think I see what you are doing.

Can you upload a copy of the original PFF file. I think I can set you up part way to make this easier.

It should not take s much code to access a database from a form. Form Controls are "database smart". They can be data bound. You are converting everything to text and putting it in the form then taking it out and converting it back into data.

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 10:43 am

Cannot upload pff: The extension pff is not allowed.

What can I rename the file to so that the web site will allow me to upload it?

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

Re: Looking for Help with DataGridView

Post by jvierra » Wed Oct 02, 2013 10:45 am

Add .txt to the end of the name - filename.pff.txt

David. Can someone set the Mime type to allow PFF files? Please?

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 10:49 am

Please let me know if this works or not.

Thanks.
Attachments
edit_sql_server_data.pff.txt
(14.91 KiB) Downloaded 169 times

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

Re: Looking for Help with DataGridView

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

Sorry - I had to go out. Give me a bit to stage the script.

Locked