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