question

OleMartinGulbrandsen-4027 avatar image
0 Votes"
OleMartinGulbrandsen-4027 asked OleMartinGulbrandsen-4027 commented

MVVM/WPF : How can I set a @Parameter for my Stored Procedure in my ViewModel?

Hello!

Just to give some context:

I have a label in a child-window, which gets its content from a TextBox in the same child-window, which in turn is bound to the selected row of a DataGrid in a parent-window. I am using a framework (Stylet, akin to Caliburn.Micro) which automatically binds View and ViewModel together by name. But to be able to reference the parent-window DataContext, I have set the DataContext for the canvas in my child-window to be that of the parent-window, like so:

 DataContext="{Binding Path=(viewmodel:LicenseHolderViewModel.Selected)}"

This allows me to get the selected value of each column into their respective TextBoxes, by binding the TextBoxes with the propertyname for the columns, like so:

 Text="{Binding Foretaksnummer}"

So, to my question. In the child-window, I have two DataGrids, which I am trying to fill with a collection from my MS Sql table, and sorted based on whos child-window I am currently in (they are essentially business profiles).

It looks like this:

115547-image.png

It gets filled nicely with the contents of the DataGrid selected row, but I am not able to reference anything in order to have a value for the @Parameter I want to pass in to my Stored Procedure in MS Sql.

This is my Stored Procedure:


 USE [Ridel.Hub]
 GO
 /****** Object:  StoredProcedure [dbo].[getLicense]    Script Date: 18.07.2021 00:09:58 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 ALTER PROCEDURE [dbo].[getLicense]
  @Foretaksnavn nvarchar(50)
 AS 
 SELECT tblLicense.ID, LøyvehaverID, KjøretøyID
 FROM tblLicense
 INNER JOIN tblLicenseHolder
 ON tblLicense.LøyvehaverID = tblLicenseHolder.Foretaksnummer
 WHERE tblLicense.LøyvehaverID = @Foretaksnavn


And I am then trying to set value to the parameter in my ViewModel method, like so:


 sqlCmd.Parameters.Add("@Foretaksnavn", SqlDbType.NVarChar).Value = ???

But I have no idea how to reference it.
The value I need is found in the TextBox in the child window with the "Foretaksnavn" label infront of it.
How can I access it without breaking MVVM design principles?

Appreciate any assistance!


dotnet-csharpdotnet-wpf-xaml
image.png (23.6 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Which value or variable is null?

Also make sure that SQL is valid. For example, is it correct to compare LøyvehaverID with @Foretaksnavn?


0 Votes 0 ·

Hello Viorel, thank you for your response.

A bit awkward, but as I was researching your suggestions, I realized I wasn't even using the code I had in my original question.
I will update my question.

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 OleMartinGulbrandsen-4027 ·

Is Foretaksnummer a member of LicenseHolderViewModel.Selected?


0 Votes 0 ·
Show more comments

What happens if you take the SQL and run it without a stored procedure?

Also, you really should write your data operations in a class which does only backend data operations and even better is to have the data class in a class project which now allows separation of frontend and backend code along with the ability to write unit test against the backend code.

Or in SSMS run the SP and see if it works properly.

0 Votes 0 ·

Hello Karen, appreciate your response.
I will take your advice to heart, and research it.
But I had done some mistakes in my original question, please see my updated question.
PS: the SP runs properly in SSMS :-)

0 Votes 0 ·

0 Answers